---
id: varejo/marketplaces-consolidados
industry: Retail and e-commerce
usage: common
effort: medium
grain: Order per channel (plus order line item to break down by SKU)
---

# Consolidated marketplaces

Mercado Livre, Amazon, Shopee and the other channels in a single table, reconciled with the ERP or hub, without counting the same order twice and with the right date.

## Executive summary

**For:** Retailers and manufacturers that sell on more than one marketplace and also record sales in a hub or ERP.

**What it solves**

- Each marketplace sale appears once, even though it exists in the marketplace, the hub and the ERP.
- It becomes clear how much each marketplace, each account and each store sold, consolidated and separately.
- Channel orders with no match in the ERP (and vice versa) appear in a list, with a confidence level.

**Why it matters:** The same order is born in the marketplace, flows down to the hub and becomes an invoice in the ERP. Adding the three sources triples sales; using only the ERP misses the sales the channel started invoicing directly. Without a consolidated base, channel share, marketplace targets and SKU margin come from numbers that do not reconcile with each other.

**Business questions it answers**

- How much did we sell on each marketplace and each account this month?
- What is each marketplace's share of revenue, and how did it change from last month?
- Which Mercado Livre orders do not appear in the ERP, and which ERP invoices have no order in the channel?
- What is the cancellation rate by marketplace?
- How much was invoiced this month, and how much of it has already been released for payout?
- Which SKUs explain a marketplace's shortfall against target?

**What you need**

- The marketplaces and the hub or ERP connected to Nekt
- The list of hub stores or channels that correspond to each marketplace and account
- The dates when any channel changed systems or started invoicing without an order

**What you get**

- A table by order and channel with value, status, date and source of origin
- A marketplace x hub or ERP reconciliation table with confidence level
- Dashboard by marketplace, account and store, with individual filter and consolidated view
- A coverage indicator per source that separates a day with no sales from a day with no data

## Detailed

### The problem

The marketplace order is copied to the hub, which creates the order in the ERP, which issues the invoice. Each layer has its own identifier: the order ID and the pack on Mercado Livre, the store order number in the hub, the order number and the invoice in the ERP. Channels change operations mid-year (they start invoicing without an order, switch hub accounts) and each source has its own date: purchase, confirmation, invoicing, balance release. Adding everything duplicates; choosing just one source loses sales.

### Business rules

1. **One source of truth per channel and period.** For each marketplace, declare which source defines the sale (the marketplace itself via API, the hub or the ERP) and from when. The other sources are for reconciling, never for adding. Store this choice in a channels table with start and end dates, so the rule is read by the dashboard and by the agent. (Avoid: UNION of the marketplace order, the hub order and the ERP invoice.)
2. **The channel comes from the hub store identifier.** In the hub, each marketplace and each account is a store with its own ID. Classify the channel by store ID in a mapping table (store → marketplace → account), not by free text. In the ERP, when there is no store, the channel comes from a documented combination: order origin, integration salesperson, project or a note that mentions the marketplace. Write the criteria in the context document. (Avoid: Searching for "MERCADO" in the order description and calling everything else the own store.)
3. **Order, pack and shipment are different things.** On Mercado Livre, a cart with several items generates several orders grouped by pack_id, and one shipment can cover the whole pack. The hub may store either the order ID or the pack_id in the store order number. Count orders by the channel's order ID, and use the pack to match with the hub and the shipment. The channel's orders table comes with repeated versions: keep the latest version of each ID. (Avoid: Counting each pack as one order, or adding every version of the same ID.)
4. **Reconciliation with a confidence level, never invented certainty.** Match the hub order to the marketplace order in order of strength: store order number equals order ID (high), store order number equals pack_id (high), same value and close date in the same account (medium), no match (pending). Store the rule that matched and the evidence in columns. Reports state how much is reconciled; the AI never claims a pair is confirmed when confidence is medium. (Avoid: JOIN on value and date and treating every pair as the same sale.)
5. **Sales invoiced without an order are still sales.** Some channels, from a certain date, start being invoiced directly via NF-e (e-invoice, usually in a dedicated series) with no sales order in the ERP. The channel's base is the union of identified orders with outbound invoices in that series issued without an order. An invoice that already has an order does not go in again. A sales CFOP (fiscal operation code) flag separates sales from shipments, transfers or stock sent to the marketplace warehouse: filter sales only. (Avoid: A channel report built only from ERP orders, which zeroes out the channel on the date of the change.)
6. **A system change is a cutoff by date.** When the channel migrates from one hub account to another, or from the hub to the marketplace API, each source contributes only its own period. Declare the cutoff date (last day in the old source, first in the new one) and make sure no day appears in both or in neither. (Avoid: Combining both hub accounts over the same period and doubling the migration week.)
7. **Invoicing and payout have different dates.** The reference date for revenue is the order confirmation date. Payout follows each marketplace's balance release rule, which can land weeks later. Keep both timelines in separate columns and never compare a month's revenue with the same month's payout as if they were the same money. (Avoid: Using the payout date as the sale date, or the other way around.)
8. **Order count declared per channel.** Some channels' reports count each line item as an order (Amazon's orders report replicates the "Quantity Ordered" of e-commerce BI tools). Define per channel whether "orders" means distinct orders or line items and write it in the column description. Amazon revenue is itemPrice excluding tax; tax comes separately. A SKU without a product mapping goes to "Uncategorized" with a flag, never disappears. (Avoid: Comparing Amazon "orders" with Mercado Livre distinct orders.)
9. **Order status separates valid from cancelled.** Count valid and cancelled orders in separate columns, by order date. Cancelled orders do not go into revenue, but they do go into the cancellation rate. Load incrementally by the order's update date (in the hub, the modified field), not by creation date: old orders change status, and this is the only way the change comes through. (Avoid: Incremental load by creation date, with orders cancelled weeks later still counted as sales.)
10. **Missing data never becomes zero.** For each source and account, record the period covered, days with data, days with no records and the latest load. A day with no rows in an account that sells every day is a load failure until proven otherwise. The dashboard shows the channel as "no data", not as zero, and the AI warns when the source is late. (Avoid: LEFT JOIN on the calendar with COALESCE to zero for every channel.)

### How to match each channel with the hub or ERP

Reconciliation key and caveat for each source.

- **Mercado Livre · order** → Latest version of each ID (`orders.id, pack_id`)
- **Hub · marketplace order** → Store order number = order ID or pack (`numeroLoja, loja.id`)
- **Hub · channel** → Hub store mapped to marketplace and account (`loja.id → marketplace`)
- **ERP · sale without an order** → Outbound NF-e in the channel's series, without an order (`series + sales CFOP`)
- **ERP · shipment to the channel warehouse** → Outside revenue (`CFOP not in the sales list`)
- **Amazon · orders** → One row per item; revenue excluding tax (`itemPrice, SKU → ASIN → product`)
- **All · revenue** → Order confirmation date
- **All · payout** → Balance release date in the channel

### Definitions

| Metric | Definition |
|---|---|
| Marketplace revenue | sum of the value of valid orders, by order confirmation date, by marketplace, account and day, from the channel's source of truth for the period |
| Valid orders | distinct count of non-cancelled channel order IDs, by marketplace and day (on Amazon, per the declared order definition) |
| Cancellation rate | cancelled orders / (valid + cancelled), by marketplace and month, by order date |
| Marketplace share | marketplace revenue / revenue of all marketplaces in the same period |
| Channel average ticket | revenue / valid orders, recomputed at every cut |
| % reconciled | channel orders with a high-confidence match in the hub or ERP / valid channel orders, by month |
| Payout | amount released by the marketplace, by balance release date, by account and day |
| Source coverage | days with data / expected days, by source, account and month |

### Pitfalls

- **Channel zeroed overnight.** The marketplace started being invoiced by invoice only, with no order in the ERP. The order-based report showed the channel collapsing from that date, while sales had not dropped. How to avoid: Union of channel orders with the channel series invoices issued without an order.
- **Doubled week during migration.** The channel switched hub accounts and both accounts stayed active for a few days. Adding both duplicated sales for the week of the switch. How to avoid: Cutoff date declared per source, with an overlap test.
- **Match by value treated as certain.** Orders with the same value on the same day were matched to the wrong hub order, and the confirmed conversion in the report did not exist. How to avoid: Reconcile by ID or pack first; a value match stays at medium confidence and is shown as such.
- **Shipment counted as a sale.** Invoices for stock sent to the marketplace warehouse went in as channel sales and inflated the month in which stock was replenished. How to avoid: Sales CFOP flag; shipments and transfers outside revenue.
- **Load failure read as a drop.** An account went days without syncing and the dashboard showed zero sales. Leadership pressed the channel team before anyone looked at the load. How to avoid: Coverage table per source and a "no data" state in the dashboard and in AI answers.

### Acceptance test

- [ ] For one marketplace, one account and one closed month, valid orders and revenue match the marketplace's own sales report (difference below 1%).
- [ ] Each channel order ID appears exactly once in the consolidated table.
- [ ] The sum by marketplace equals the consolidated total for the same period, and the individual store filter adds up to the consolidated total.
- [ ] In the days around each system cutoff date, no order appears in two sources and no day is left without a source.
- [ ] The month's % reconciled is reported, and every pending order appears in the pending items list with the rule attempted.

### Context document

```text
Consolidated marketplaces
- Grain: one row per channel order (order ID in the marketplace). Items in the order line item table.
- The source of truth per channel and period is in the channels table. Do not add marketplace, hub and ERP orders together: the other sources only reconcile.
- Channel = hub store mapped to marketplace and account. In the ERP, channel by the documented criteria (origin, integration salesperson, project, note).
- Mercado Livre: order = ID, latest version; pack_id groups orders and is used to match with the hub.
- Reconciliation with the hub by store order number = ID or pack_id (high confidence); by value and date (medium confidence). Never claim a confirmed conversion at medium confidence.
- Channels invoiced without an order: add the outbound invoices in the channel's series issued without an order. Sales CFOP only.
- Revenue by order confirmation date. Payout by balance release date. Do not treat them as the same date.
- Amazon: "orders" follows the definition declared on the column; revenue = itemPrice excluding tax.
- Cancelled orders stay out of revenue, but count toward the cancellation rate.
- A day with no data is not a day with no sales: check the coverage table before answering zero, and say when the source is late.
- Commissions and fees per channel follow the rules of the contribution margin case.
Client exceptions:
- <client>: channel <marketplace> invoiced without an order since <date>, series <series>.
- <client>: hub accounts <account A> until <date>, <account B> from <date>.
- <client>: hub stores <list of ids> = <marketplace> account <account>.
```

### Variations

- **Performance by listing:** Listing x day grain with visits, valid and cancelled orders, units and Ads, aggregable by account, family and SKU without double counting.
- **Listing history:** A snapshot that writes a new row only when title, price, listing type or logistics changed, to link changes to results.
- **Proposal to order:** Candidate pairs of sales proposal → order from the same contact within a 90-day window, with a confidence level.