Sales over time
Recreates the Adobe Commerce admin "Orders" sales report (Reports > Sales > Orders: orders, sales items, sales total, invoiced, refunded, sales tax, sales shipping, sales discount, canceled) and adds a sales equation on top.
- Grain
- day x store x order
- Built from
- 2Raw tables
- 3Staging
- 1Core
- 1Analytics
- Reads from Adobe Commerce
- sales_ordersales_order_item
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.adobe_commerce.sales_over_time
2-- Recreates the Adobe Commerce admin "Orders" sales report (Reports > Sales >
3-- Orders: orders, sales items, sales total, invoiced, refunded, sales tax, sales
4-- shipping, sales discount, canceled) and adds a sales equation on top.
5--
6-- Grain: day x store x order. Aggregate to day, store view or status in BI.
7--
8-- ORDER-DATED, NOT EVENT-BASED. Read this before using `returns`. Only sales_order
9-- is synced, and its refunded and canceled amounts are running totals on the order
10-- row. Without the credit memo tables there is no refund date, so a refund made in
11-- March reduces the January order it belongs to, and past days change every time
12-- an old order is refunded. This is also how the admin report behaves when run by
13-- order creation date. Shopify-style "returns on the return date" needs
14-- sales_creditmemo, which is not synced.
15--
16-- Matches the admin report's rules:
17-- * Orders in state new or pending_payment are left out, as the report does.
18-- Canceled orders stay in, with their canceled amounts subtracted, so a fully
19-- canceled order counts as an order with zero sales.
20-- * Money is base currency x base_to_global_rate, i.e. the global currency.
21-- For a single-website store the rate is 1 and nothing changes.
22-- * The date is created_at converted to the time zone in
23-- staging/store_timezone.sql. Set it, or days will not match.
24--
25-- The sales equation (discounts and returns negative, so net = gross + discounts
26-- + returns is plain addition):
27-- gross_sales subtotal ordered, net of cancellations, before discount and tax
28-- discounts discounts given, net of cancellations
29-- returns refunded amount excluding refunded tax and shipping; includes
30-- credit memo adjustments (adjustment refunds and fees)
31-- shipping, taxes charged, net of cancellations and refunds
32-- total_sales net_sales + shipping + taxes
33-- With prices entered including tax, Magento books a small "discount tax
34-- compensation" into the grand total that is in none of these columns, so
35-- total_sales can differ from sales_total by that amount.
36--
37-- Depends on: staging.adobe_commerce.sales_order, .sales_order_item,
38-- .store_timezone
39
40WITH orders AS (This model and the 4 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
ORDER-DATED, NOT EVENT-BASED. Read this before using `returns`. Only sales_order is synced, and its refunded and canceled amounts are running totals on the order row. Without the credit memo tables there is no refund date, so a refund made in March reduces the January order it belongs to, and past days change every time an old order is refunded. This is also how the admin report behaves when run by order creation date. Shopify-style "returns on the return date" needs sales_creditmemo, which is not synced. Matches the admin report's rules: * Orders in state new or pending_payment are left out, as the report does. Canceled orders stay in, with their canceled amounts subtracted, so a fully canceled order counts as an order with zero sales. * Money is base currency x base_to_global_rate, i.e. the global currency. For a single-website store the rate is 1 and nothing changes. * The date is created_at converted to the time zone in staging/store_timezone.sql.
Raw tables it reads
- raw.adobe_commerce.sales_order
- raw.adobe_commerce.sales_order_item
More Adobe Commerce models
Put Adobe Commerce in front
of your agents.
Connect Adobe Commerce, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.