--- id: agencias/ecommerce-do-cliente industry: Marketing agencies usage: specific effort: medium grain: Store x channel x day in the store's time zone; order for auditing --- # Client e-commerce Paid orders, net revenue, average order value and conversion for each client's store in a daily fact table that matches the store dashboard, with the GA4 funnel alongside and no expired Pix (Brazil's instant payment) counted as a sale. ## Executive summary **For:** Agencies running media, CRM or store management for e-commerce clients. **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. **Why it matters:** E-commerce clients compare the agency report with the store dashboard right away. If revenue includes expired Pix or lands on the wrong day because of the time zone, the conversation stops being about media and becomes about the number. It is also the basis for real ROAS, which divides spend by paid revenue rather than by the platform's conversion value. **Business questions it answers** - Why did revenue drop this week? - What is the checkout conversion rate this month? - How much was refunded this month, and on which days? - How much did we sell on the site and on each marketplace? - What is the average order value by source channel? - How many orders stayed pending or expired on Pix and boleto (bank slip)? **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 ## Detailed ### The problem 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. ### Business rules 1. **Paid order by financial status.** 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. (Avoid: Summing every store order with a value in the cart.) 2. **Net revenue subtracts the actual refund.** 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. (Avoid: Using the order total as revenue, with shipping and tax and without refunds.) 3. **The day is the store's day.** 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. (Avoid: Cutting the day in UTC and pushing evening sales into the next day.) 4. **Sessions come from GA4; orders and revenue from the store.** 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. (Avoid: Computing conversion with store sessions and GA4 orders.) 5. **Rates are recomputed for the period.** 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. (Avoid: Averaging daily conversion rates for the month.) 6. **Funnel on fixed events, declared per client.** 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. (Avoid: Building the funnel from whatever events show up in the report, changing month to month.) 7. **GA4 purchase above the store means duplication.** 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)'. (Avoid: Reporting GA4 revenue when it is higher than store revenue.) 8. **GA4 reports keep the latest extraction.** 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. (Avoid: Summing every extraction of the same day.) 9. **A month with load gaps is not compared.** 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. (Avoid: Comparing a month with two loaded days against a full month.) 10. **Each marketplace with its own rule, without multiplying orders.** 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. (Avoid: Expanding the order's items in the join and summing the order value per item.) ### Order status in the daily fact By displayFinancialStatus and the order flags in the store. - **PAID** → Revenue (`cancelledAt IS NULL AND test = false`) - **PARTIALLY_REFUNDED** → Revenue, minus the refund (`transactions.kind = REFUND, status = SUCCESS`) - **REFUNDED** → Refund, separate line - **PENDING, AUTHORIZED, PARTIALLY_PAID** → Not a sale yet, separate line - **EXPIRED, VOIDED** → Expired Pix or boleto, voided: out - **cancelledAt filled** → Canceled, separate line - **test = true** → Test order: out ### Definitions | Metric | Definition | |---|---| | Paid order | order with displayFinancialStatus PAID or PARTIALLY_REFUNDED, not canceled and not a test, on the store's day | | Gross revenue | sum of subtotal + discounts of the day's paid orders | | Net revenue | sum of subtotal - refunds of the day's paid orders, without shipping and tax | | Refund | sum of the order's transactions with kind = REFUND and status = SUCCESS | | CVR | store paid orders / GA4 sessions in the period | | AOV (average order value) | net revenue / paid orders in the period | | RPV | net revenue / GA4 sessions in the period | | Step rate | step eventCount / previous step eventCount in the period | | GA4 coverage | days with a load in the period / days in the period (or days elapsed in the current month) | ### Pitfalls - **Revenue from expired Pix.** Orders whose Pix or boleto expired unpaid stayed in the store with a cart value and were counted as revenue. How to avoid: Filter by financial status and show expired orders separately. - **Sale on the wrong day.** Stores abroad had their day cut in UTC and evening sales fell on the next day, not matching the store dashboard. How to avoid: Convert to the store's time zone before grouping by day. - **Bot sessions.** The store's native dashboard showed session spikes from countries with no relation to the audience. They were bots scraping the catalog. How to avoid: Sessions from GA4 only; orders from the store only. - **Double purchase.** A client with several tag containers and several GA4 properties had the purchase event firing twice. How to avoid: Compare purchase with paid orders and deduplicate by transactionId. - **A rise of thousands of percent.** A month with two days of GA4 loaded was compared with a month with eight, and the change in sessions looked enormous. How to avoid: Count loaded days and blank out months under 80%. - **Order multiplied by its items.** Expanding the item list in the join repeated the order value on each item and inflated revenue for orders with more than one product. How to avoid: Count items in a per-order subquery. ### Acceptance test - [ ] 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. ### Context document ```text 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 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: - : custom funnel with events , , and purchase. - : store in , time zone