Store and marketplace orders, GA4 and media
Paid order by financial status, day in the store's time zone
Daily fact with net revenue, CVR, AOV and funnel
Dashboard, recurring report and alerts
What it solves
- Report revenue matches the store dashboard and excludes expired, pending and test orders.
- Conversion and average order value computed the same way for every store in the portfolio.
- The site funnel shows where the client lost sales, with GA4 coverage declared.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- The client's store and marketplaces connected to Nekt
- The store's GA4 connected (daily reports and events)
- The store's time zone and currency, and the event funnel agreed with the client
What you get
- A daily fact by store and channel with paid orders, gross and net revenue and refunds
- A view_item, add_to_cart, begin_checkout and purchase funnel with step rates
- A dashboard with CVR, AOV and RPV recomputed for any period
- Alerts for revenue drops and GA4 load gaps
Where to activate
Revenue, orders, CVR, AOV and funnel by day and channel, with GA4 coverage alongside.
Revenue = sessions x conversion x order value, to explain the week's change by the factor that moved.
Revenue drop against the average of recent weeks, and GA4 purchase out of line with orders.
The rules are closed and the package replicates per store. The effort goes into each marketplace, which has its own statuses and grain, and into each site's custom funnel. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
The store records the order as soon as the cart is closed, paid or not, in UTC, with the refund buried in a list of transactions. GA4 counts events, not orders, and sometimes counts the same purchase twice. Whoever sums store orders without a filter counts expired Pix and test orders; whoever cuts the day in UTC pushes evening sales into the next day; whoever averages daily conversion rates delivers a rate nobody can reproduce.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
Revenue comes only from orders with displayFinancialStatus PAID or PARTIALLY_REFUNDED, empty cancelledAt and test = false. PENDING and AUTHORIZED are not a sale yet; EXPIRED (expired Pix or boleto) and VOIDED never will be. Keep each status as its own line in the fact, so the client can see how much stayed pending or expired.
Gross revenue = subtotal + discounts. Net revenue = subtotal - refunds. A refund is the sum of the order's transactions with kind = REFUND and status = SUCCESS. Shipping and tax stay out of product revenue and only enter the total. Declare whether the refund sits on the order date or on the date it happened, and do not change it.
Convert the order date (processedAt or createdAt, one of them, declared) to the store's time zone before cutting the day: America/Sao_Paulo in Brazil, the local zone for stores abroad. The GA4 date already comes in the property's time zone.
The store's sessions panel counts bots scraping prices and catalog, with spikes from countries unrelated to the audience. Sessions, users and funnel come from GA4. Orders and revenue always come from the store; GA4 purchase is for cross-checking. CVR = store paid orders / GA4 sessions.
CVR, AOV and RPV are ratios of sums over the chosen period: orders / sessions, revenue / orders, revenue / sessions. Never an average of daily rates. The daily fact stores numerators and denominators; the rate is computed on read.
The standard funnel is view_item, add_to_cart, begin_checkout and purchase, by each event's eventCount, with each step's rate over the previous one. Sites with their own journey (choose route, seat and payment method) declare the event names as data in the client's context. An event count is not a count of people.
Several Google Tag Manager containers or two properties firing purchase double the event. Compare GA4 purchase with the store's paid orders by day; a systematic upward gap means duplicated tracking. In the GA4 transactions table, keep one row per transactionId and drop '(not set)'.
GA4 reports are extracted again and the same day shows up more than once. Keep the row from the most recent extraction per store and day. The date may come as YYYYMMDD or YYYY-MM-DD in the same series: accept both formats.
Before comparing sessions across months, count each month's loaded days. A month with less than 80% of its days gets no number (for the current month, against days elapsed). Every traffic number carries its coverage.
Each marketplace has its own statuses and grain: in Amazon the report is per item and an approved order is a shipped one; in Shopee the grain is the order and CANCELLED and UNPAID stay out. Count items in a per-order subquery, never by expanding the item list in the join, which repeats the order value. A store in another currency does not add up with reais.
Order status in the daily fact
By displayFinancialStatus and the order flags in the store.
Definitions
Pitfalls that already happened
Orders whose Pix or boleto expired unpaid stayed in the store with a cart value and were counted as revenue.
Filter by financial status and show expired orders separately.
Stores abroad had their day cut in UTC and evening sales fell on the next day, not matching the store dashboard.
Convert to the store's time zone before grouping by day.
The store's native dashboard showed session spikes from countries with no relation to the audience. They were bots scraping the catalog.
Sessions from GA4 only; orders from the store only.
A client with several tag containers and several GA4 properties had the purchase event firing twice.
Compare purchase with paid orders and deduplicate by transactionId.
A month with two days of GA4 loaded was compared with a month with eight, and the change in sessions looked enormous.
Count loaded days and blank out months under 80%.
Expanding the item list in the join repeated the order value on each item and inflated revenue for orders with more than one product.
Count items in a per-order subquery.
Acceptance test
The case is done when every item passes.
- For one store and one closed month, paid orders and net revenue match the store's sales report (difference under 1%).
- No EXPIRED, VOIDED, canceled or test order enters revenue.
- The fact has one row per store, channel and day, with no repeats.
- Monthly CVR = sum of paid orders / sum of sessions for the month, not the average of daily rates.
- Monthly GA4 purchase stays within 10% of the store's paid orders, or the gap shows up as a tracking alert.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Client e-commerce - Paid order: displayFinancialStatus PAID or PARTIALLY_REFUNDED, empty cancelledAt and test = false. PENDING, AUTHORIZED, EXPIRED and VOIDED are not revenue. - Net revenue = subtotal - refunds (transactions with kind = REFUND and status = SUCCESS). Shipping and tax out. - The day is the store's day: convert the order date to <store time zone> before grouping. - Sessions, users and funnel come from GA4. Orders and revenue come from the store. Do not use store sessions: they count bots. - CVR, AOV and RPV are recomputed as a ratio of sums for the period. Never an average of rates. - Standard funnel: view_item, add_to_cart, begin_checkout, purchase (eventCount). - A month with less than 80% of GA4 days loaded is not compared. Report coverage. - Marketplaces have their own rules and different currencies are never summed. Client exceptions: - <client>: custom funnel with events <event 1>, <event 2>, <event 3> and purchase. - <client>: store in <currency>, time zone <time zone>; do not sum with stores in reais.
Build the "Client e-commerce" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/agencias/ecommerce-do-cliente.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
Join spend from the portfolio media table to the store's paid revenue on the same day and store, instead of using the platform's conversion value.
Use the order's last external session (ignoring the return from the checkout itself) and, alongside it, the first paid visit within 30 days, never one in place of the other.
Customer with 2 or more paid orders, with no time limit; expired and pending orders do not count.