E-commerce and marketplaces: orders, products, and channel attribution
The standard for placing orders, products and sales source in a model consumable, with the pitfalls of cancellation and assignment.
- See typical e-commerce sources
- Model orders and channel attribution
- Have an end-to-end implementation checklist
In e-commerce, the data that matters is spread out: the order is in the store platform, the confirmed money is at the gateway, and the origin of the sale is in the media pixel and analytics. Each system has a piece of the same sales story, and none alone answers the questions that businesses ask every day. This playbook shows the pattern that Nekt uses to gather orders, products and source channel in a unique model and consumable, treating from the beginning the two things that most distort e-commerce numbers: cancellations and attribution.
1. Context: what an e-commerce needs to measure
Few metrics guide almost every decision in a sales operation online. They answer different questions, but rely on the same well-modeled order base.
- Net revenue: how much actually came in, after taking out canceled orders, returns and discounts. It's the number that the board looks at first, and what most people miscalculate when adding gross order.
- Average ticket: the average value per valid order. Say whether the strategy is bringing larger or smaller purchases, and changes reading according to the channel.
- Best-selling products: what comes out, in volume and in revenue. Stock guide, curation and what to push in the media.
- Channel that brings sales: Where did each order come from? (paid media, organic, direct). It’s what connects marketing spend revenue and decide where to invest.
- Cancellations and returns: how much of the gross sales escape later. It's a leak in the bucket, and it needs to appear separately to avoid inflating the revenue.
Each one points to a different lever: revenue measures the size, the ticket measures the quality of the purchase, the product mix guides the inventory, the channel connects marketing to revenue, and cancellations measure the leak. The problem is that, without a modeled base, each team calculates your way, marketing adds the gross order and the financial order add up the net, and no one closes the number. It is this work that modeling at Nekt solves it.
2. Typical e-commerce sources
Most operations have data divided between the store, payment gateway, media platforms and analytics. Each source delivers a piece, and the value appears when they come together in a modeled layer. They all enter raw Bronze.
- Store platform (e.g. Shopify, VTEX, Nuvemshop). The source of truth about the order: items, customer, values, shipping, status, and the product catalog. It is the heart of the model, almost everything revolves around it.
- Payment gateway (e.g. Stripe, Pagar.me, Mercado Paid). Confirmation of money: charge approved, rejected, reversed. It is which separates the order placed from the order actually paid, and brings fees that the liquid needs to consider.
- Media (e.g. Meta Ads, Google Ads). The expense and campaign performance by channel, in addition to the source parameters that link the click to the order. It is half of the allocation account.
- Analytics (e.g. GA4). The visitor's journey to the purchase: session origin, first and last interaction. Help to resolve which channel the sale actually came from.
Store platform and gateway often have overlapping information about order value and status. Define from the beginning what the source of truth for each field: normally the store sends items, customer and shipping, and the gateway sends payment confirmed and fees. Documenting this avoids conflict when calculate billing.
3. Modeling recommended at Gold
The objective is to reach a large table of orders in the layer Gold: one line per order, already with product, customer, channel, gross value, discounts, shipping and status together. The raw store supplies and from the gateway enter the Bronze, the cleaning and standardization take place in Silver, and the business model end, the wide request with assignment, lives in the Gold.
- Store platform orders and catalog
- Gateway charges, media spend
- As it came, unedited
- Values and dates still as text
- Typed values, truth dates
- One table per entity
- Standardized status in a single vocabulary
- Source of truth defined by field
- One line per order, all together
- Product, customer, channel and values
- Cancellations and returns handled
- This is where dashboard and MCP read from
Why a wide order table
The temptation is to leave everything standardized, the order in a table, the items
in another, the customer in another, the channel in another, and ask who
It consumes making the joins. In an e-commerce, this slows down both BI and
AI. A table wide, with one line per order and the
most used dimensions already resolved in the line itself (product
main, customer, channel, gross value, discount, shipping, status),
leave most questions to a GROUP BY away.
The dashboard adds and filters directly, without joining. And the AI agent, who makes mistakes
when chaining joins, it gets it right when reading a flat, well-described table. One
modeling, two easy consumptions.
One line per order, with product, customer, channel and breakdown of the value already resolved:
| order | date | customer | product | channel | gross | discount | freight | status | liquid |
|---|---|---|---|---|---|---|---|---|---|
| a83f | 08/03/2026 | Silva Store | P15 Filter | R$ 240 | R$ 20 | R$30 | delivered | R$ 250 | |
| a840 | 08/03/2026 | M. Souza | P20 Filter | R$ 180 | R$0 | R$ 25 | delivered | R$ 205 | |
| a851 | 09/03/2026 | R. Costa | P15 Filter | Direct | R$ 240 | R$0 | R$30 | canceled | R$0 |
| a860 | 09/03/2026 | Ana Dias | P30 Filter | R$320 | R$30 | R$0 | returned | R$0 |
Adding liquido by canal, you have the
billing per channel. Grouping by produto, the mix
of sales. Note that requests cancelado and
devolvido they already come in with zero liquid, so they never
inflate revenue, even though they remain visible for analysis
losses.
Wide doesn't mean "throw everything in one table". Keep the clear granularity: one line per order. If an order has several products and you need to analyze item by item, create a second item table (one row per order item) instead of stretching the of orders. Two clean granularities are easier to maintain and to explain to the AI than an ambiguous table.
4. channel attribution
Attribution is linking each sale to the channel that originated it: paid media,
organic search, direct traffic, referral. In practice, you use the
source parameters that the store platform stores in the order (the
famous UTMs) and crosses it with the analytics session to fill in the
column
canal of the wide table. Once this is done, add revenue per
channel becomes one GROUP BY.
The challenge is that the purchasing journey is almost never a one-touch process. O customer sees an ad on Instagram, searches on Google days later, and Return by entering the direct address to purchase. Which channel does it belong to? this sale? The answer depends on attribution window that you choose. The most common is the last click, which credits the last channel before the purchase (in the example, direct), but it deletes the Instagram role that generated the discovery. Other models credit the first touch, or divide the credit between the channels of the journey. There is no right model, there is the model that the business combines and uses consistently.
No attribution model is the absolute truth. Each one is a way to simplify a real journey that went through several channels, devices and days. The value isn't in hitting the "correct" channel of each isolated sale, but rather in use the same model consistently for Compare channels to each other over time. Make it explicit in modeling which window and which rule (last click, first click, etc.) you used, and document this in the Semantic Layer so that the dashboard and agent respond with the same definition.
5. Traps
Two pitfalls appear in almost every e-commerce data project. They don't break the pipeline, what's worse: they deliver numbers plausible but wrong. It's worth knowing both before modeling.
Billing has to be dealt with canceled and returns. A canceled or returned order never turned into revenue, so adding it in revenue inflates the number, sometimes dramatically in operations with a lot of returns. The rule of thumb: the table revenue wide should only reflect what was actually paid and maintained. Keep canceled and returned orders visible in the table (with net zero and explicit status), because loss analysis need them, but ensure that billing only adds up to orders valid. Never calculate income from the gross value without discounting cancellation, return and discount.
The status of an order is not fixed: it starts as "waiting payment", becomes "paid", then "sent", "delivered", and can become "cancelled" or "returned" weeks later. If the modeling captures the status of a moment and never update again, the table lies: a order that was returned continues to appear as delivered and counting as income. The wide table needs reflect the current state of each order, reprocessing the status with each source update. Without this, the today's billing changes the reading of yesterday's billing, and no one trusts history.
6. Use case: sales dashboard + AI agent
With the wide order table in Gold, two consumptions appear almost for free on the same model.
A sales dashboard by channel and product read straight wide table: net revenue by channel, product mix plus sold, average ticket, evolution per day. How to cancel and returns already enter with net zero and the status reflects the current state, the numbers close with finance without rework, and the marketing team you can instantly see which channel is actually bringing in revenue, not just gross order.
An AI agent, via MCP, answers questions in natural language on the same table: "which product sold the most in the Instagram this week", "which channel had the most returns this month", "which the average ticket on Google Ads". Instead of waiting for an analyst to assemble the query, whoever needs the answer asks and receives the number immediately.
What makes the agent trustworthy is the Semantic Layer describing the table: what is each column, that billing uses the net and not the gross, that canceled and returned count as zero, which attribution model feeds the channel column. Without this description, the AI guesses and gets it wrong. With her, the AI responds with the same rules that the dashboard uses, and the two match. One model, two consumptions that never contradict each other.
An e-commerce selling filters through its own store and through marketplaces, with payment at the gateway and media on Meta and Google. The reported revenue never matched the financial because it added up to the gross order, without taking into account cancellation and return, and the status was frozen at the time of loading. When redoing the modeling in a wide order table in Gold, with liquid treating canceled and returns statuses reflecting the current state and channel resolved by last click attribution, the number became a metric the team trusted. On top of the same layer, they plugged a sales dashboard by channel and product and an agent who Answers sales questions on a daily basis.
7. Implementation checklist
The end-to-end path, in the order that usually makes sense. Each step is based on the previous one, so it's worth following it from top to bottom.