Consolidated marketplaces

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.