Sales over time
Recreates WooCommerce Analytics' Revenue report (gross sales, coupons, refunds, net sales, taxes, shipping, total sales) at a grain you can slice.
- Grain
- day x store x order x row type
- Built from
- 3Raw tables
- 3Staging
- 1Core
- 1Analytics
- Reads from WooCommerce
- wp_optionswp_wc_order_product_lookupwp_wc_order_stats
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.woocommerce.sales_over_time
2-- Recreates WooCommerce Analytics' Revenue report (gross sales, coupons, refunds,
3-- net sales, taxes, shipping, total sales) at a grain you can slice.
4--
5-- Grain: day x store x order x row type. row_type is SALE for the order itself and
6-- RETURN for its refunds, so a day on which an order both sells and refunds gives
7-- two rows and each side stays auditable. Aggregate to day in BI.
8--
9-- Event-based: a refund is its own row in wc_order_stats, dated when the refund
10-- was made, so returns land on the refund date and history does not restate when
11-- an old order is refunded. order_id is always the ORIGINAL order, also on RETURN
12-- rows.
13--
14-- Matches Analytics' own rules, read from the store's settings:
15-- * Report date: the "Date type" analytics setting (date paid by default). Rows
16-- with no value for that date, such as unpaid orders under "date paid", are
17-- left out, as Analytics does.
18-- * Statuses: the "Excluded statuses" analytics setting (pending, failed and
19-- cancelled by default), plus auto-draft and trash, which Analytics always
20-- excludes. A refund carries its parent order's status, so it is excluded
21-- together with its order.
22--
23-- Discounts: Analytics reads coupons from wc_order_coupon_lookup, which the
24-- connector does not sync. They are taken from the per-line coupon_amount in
25-- wc_order_product_lookup instead, which is the same coupon value allocated to
26-- product lines. Expect cent-level rounding differences against Analytics.
27--
28-- Currency: the store currency (store_settings.currency, the CURRENT setting).
29-- Time: store-local, as WooCommerce writes it; no conversion needed.
30--
31-- Depends on: staging.woocommerce.order_stats, .order_product_lookup,
32-- .store_settings
33
34WITH settings AS (
35 SELECT
36 woocommerce_store,
37 currency,
38 COALESCE(analytics_date_type, 'date_paid') AS date_type,
39 ARRAY_CONCAT(
40 ['wc-auto-draft', 'wc-trash'],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.
Event-based: a refund is its own row in wc_order_stats, dated when the refund was made, so returns land on the refund date and history does not restate when an old order is refunded. order_id is always the ORIGINAL order, also on RETURN rows. Matches Analytics' own rules, read from the store's settings: * Report date: the "Date type" analytics setting (date paid by default). Rows with no value for that date, such as unpaid orders under "date paid", are left out, as Analytics does. * Statuses: the "Excluded statuses" analytics setting (pending, failed and cancelled by default), plus auto-draft and trash, which Analytics always excludes. A refund carries its parent order's status, so it is excluded together with its order. Discounts: Analytics reads coupons from wc_order_coupon_lookup, which the connector does not sync. They are taken from the per-line coupon_amount in wc_order_product_lookup instead, which is the same coupon value allocated to product lines. Expect cent-level rounding differences against Analytics. Currency: the store currency (store_settings.currency, the CURRENT setting). Time: store-local, as WooCommerce writes it; no conversion needed.
Raw tables it reads
- raw.woocommerce.wp_options
- raw.woocommerce.wp_wc_order_product_lookup
- raw.woocommerce.wp_wc_order_stats
More WooCommerce models
Put WooCommerce in front
of your agents.
Connect WooCommerce, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.