Weld
Start free
Shopify model

Sales by Product

Recreates Shopify's "Total sales by product": units, orders and revenue for every product, variant and SKU per day, so you can rank top sellers and break the numbers down by product type or vendor.

See the SQLAll Shopify models
Grain
product x variant (SKU) x day, on the ORDER date
Built from
  1. 3
    Raw tables
  2. 3
    Staging
  3. 1
    Core
  4. 1
    Analytics
Reads from Shopify
orderorder_lineproduct
Ships as
Weld and dbt SQL, open source on GitHub
core.shopify.sales_by_product
1-- shopify_sales_by_product - recreates Shopify's "Total sales by product"
2-- Grain: product x variant (SKU) x day, on the ORDER date. Currency: shop money.
3-- Depends on: staging.shopify.order, .order_line, .product
4--
5-- SCOPE: net of discounts, NOT net of returns. Does not reconcile to
6-- shopify_sales_over_time - returns stay on the original order date. Right for
7-- ranking top sellers, wrong for SKU revenue that has to tie out.
8
9WITH orders AS (
10    SELECT shopify_store, order_id, DATE(processed_at) AS date
11    FROM {{staging.shopify.order}}
12)
13
14SELECT
15    o.date,
16    l.shopify_store,
17    l.product_id,
18    -- Canonical product attributes beat the order_line snapshot, which was taken
19    -- at purchase time and goes stale if a product is renamed.
20    COALESCE(p.product_title, l.product_title) AS product_title,
21    p.product_type,
22    COALESCE(p.vendor, l.vendor)               AS vendor,
23    l.variant_id,
24    l.variant_title,
25    l.sku,
26    SUM(l.quantity)                            AS units,
27    COUNT(DISTINCT o.order_id)                 AS orders,
28    ROUND(SUM(l.gross_sales), 2)               AS gross_sales,
29    ROUND(SUM(l.discounts), 2)                 AS discounts,
30    ROUND(SUM(l.gross_sales - l.discounts), 2) AS net_sales
31FROM {{staging.shopify.order_line}} l
32-- shopify_store in every join key: order IDs are only unique within a store.
33JOIN orders o USING (shopify_store, order_id)
34LEFT JOIN {{staging.shopify.product}} p USING (shopify_store, product_id)
35-- Gift cards are deferred revenue, not product revenue. staging.shopify.order_line
36-- keeps them, so exclude them here.
37WHERE NOT l.is_gift_card
38-- Ordinals, not names: product_title and vendor exist on both joined tables, so
39-- a bare column name here would be ambiguous.
40GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

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().

Notes

How it counts. And where it stops.

Rolls order lines up to product, variant and SKU per day, joined to the product table for canonical titles, product type and vendor. Use it to rank top sellers, chart a single product over time, or compare categories and suppliers. One scope note worth understanding: this model reads order_line on the order date, so it reports gross and net of discounts but not net of returns. That is the right trade-off for ranking - relative ranking barely moves whether or not returns are netted out, and order_line is where product identity actually lives - but it means the numbers will not reconcile day-by-day with the sales report, because returns stay attached to the original order date rather than reducing the SKU that came back. For product-level revenue that has to tie out, you need the agreement event grain at line level.

Raw tables it reads

  • raw.shopify.order
  • raw.shopify.order_line
  • raw.shopify.product

More Shopify models

Put Shopify in front
of your agents.

Connect Shopify, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us