Why recreate Shopify's reports?
Shopify's dashboard looks great, but it's a walled garden: you can't redefine a metric, extend a report, or blend in data from outside Shopify. Worse, the underlying data never reaches your warehouse - it stays locked inside Shopify.
Once it's in your warehouse, every report becomes SQL you own and can extend. We built four ready-made templates - three rebuild native Shopify reports, one goes beyond them - shown on BigQuery but portable to any warehouse.
What you'll need
- Shopify synced to your warehouse via Weld. The templates read from a handful of required raw streams:
order,order_line,product,product_variant,customer,transaction, andorder_refund/order_line_refund. Connect them with the Shopify connector. - A warehouse. Weld connects seamlessly to BigQuery, Snowflake and Redshift (all destinations are listed here). The examples are written in BigQuery SQL; the logic ports cleanly to the other two.
- Optional: a store to test against. You won't be able to reproduce our exact demo data, but you don't need to - point the templates at your own Shopify development or test store to see them working before you run them against production.
Our reusable templates
Each template below follows the same layout: what it is, the native report it replaces, the full SQL, its lineage, and the result visualised.
1. Total sales over time
What it is: the daily revenue bridge - gross sales, then discounts, then returns, to net sales, then plus shipping and taxes to reach total sales - alongside orders and average order value.
Replicates: Native - Shopify's "Total sales over time".
The SQL (view in the repo):
1-- shopify_sales_over_time - recreates Shopify's "Total sales over time"
2-- Grain: day. Daily sales bridge. Currency: shop money.
3-- Sales land on processed_at; returns on the refund date.
4with
5 order_lines as (
6 select
7 order_id
8 , sum(quantity) as units
9 , sum(
10 coalesce(price_set_shop_money_amount, price) * quantity
11 ) as gross_sales
12 , sum(
13 coalesce(
14 total_discount_set_shop_money_amount
15 , total_discount
16 , 0
17 )
18 ) as discounts
19 from
20 {{raw.shopify.order_line}}
21 where
22 coalesce(gift_card, false) = false
23 group by
24 order_id
25 )
26 , orders as (
27 select
28 id as order_id
29 , date(processed_at) as order_date
30 , coalesce(total_shipping_price_set_shop_money_amount, 0) as shipping
31 , coalesce(total_tax_set_shop_money_amount, total_tax, 0) as taxes
32 from
33 {{raw.shopify.order}}
34 where
35 coalesce(test, false) = false
36 )
37 , sales_by_day as (
38 select
39 o.order_date as date
40 , count(distinct o.order_id) as orders
41 , sum(ol.gross_sales) as gross_sales
42 , sum(ol.discounts) as discounts
43 , sum(o.shipping) as shipping
44 , sum(o.taxes) as taxes
45 from
46 orders o
47 join order_lines ol using (order_id)
48 group by
49 o.order_date
50 )
51 , returns_by_day as (
52 select
53 date(coalesce(r.processed_at, r.created_at)) as date
54 , sum(
55 coalesce(
56 olr.subtotal_set_shop_money_amount
57 , olr.subtotal
58 , 0
59 )
60 ) as returns
61 from
62 {{raw.shopify.order_line_refund}} olr
63 join {{raw.shopify.order_refund}} r on r.id = olr.refund_id
64 group by
65 1
66 )
67 , combined as (
68 select
69 coalesce(s.date, r.date) as date
70 , coalesce(s.orders, 0) as orders
71 , coalesce(s.gross_sales, 0) as gross_sales
72 , coalesce(s.discounts, 0) as discounts
73 , coalesce(r.returns, 0) as returns
74 , coalesce(s.shipping, 0) as shipping
75 , coalesce(s.taxes, 0) as taxes
76 from
77 sales_by_day s
78 full outer join returns_by_day r on r.date = s.date
79 )
80select
81 date
82 , orders
83 , round(gross_sales, 2) as gross_sales
84 , round(discounts, 2) as discounts
85 , round(returns, 2) as returns
86 , round(gross_sales - discounts - returns, 2) as net_sales
87 , round(shipping, 2) as shipping
88 , round(taxes, 2) as taxes
89 , round(
90 gross_sales - discounts - returns + shipping + taxes
91 , 2
92 ) as total_sales
93 , round(
94 safe_divide(
95 gross_sales - discounts - returns + shipping + taxes
96 , nullif(orders, 0)
97 )
98 , 2
99 ) as average_order_value
100from
101 combined
102order by
103 dateTip: Use
processed_at, notcreated_at, for the true sale date - it's when the order was actually processed, which is what Shopify counts. And keep pending and refunded orders: they're real sales. The only thing you want to exclude istest = TRUE.
The Shopify connector lands the raw order, order_line, order_refund and order_line_refund tables in your warehouse; shopify_sales_over_time reads all four to build the daily gross-to-total sales bridge.
The all-time sales bridge: gross sales, less discounts and returns, gives net sales; adding shipping reaches $2,030,650 in total sales across 12,983 orders (AOV $156.41). The deductions are a thin sliver of gross - most of it survives to total.
2. Total sales by product
What it is: units 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.
Replicates: Native - Shopify's "Total sales by product".
The SQL (view in the repo):
1-- shopify_sales_by_product - recreates Shopify's "Total sales by product"
2-- Grain: product x variant (SKU) x day. Currency: shop money.
3with
4 orders as (
5 select
6 id as order_id
7 , date(processed_at) as date
8 from
9 {{raw.shopify.order}}
10 where
11 coalesce(test, false) = false
12 )
13 , lines as (
14 select
15 order_id
16 , product_id
17 , variant_id
18 , sku
19 , title as line_product_title
20 , variant_title
21 , vendor as line_vendor
22 , quantity as units
23 , coalesce(price_set_shop_money_amount, price) * quantity as gross_sales
24 , coalesce(
25 total_discount_set_shop_money_amount
26 , total_discount
27 , 0
28 ) as discounts
29 from
30 {{raw.shopify.order_line}}
31 where
32 coalesce(gift_card, false) = false
33 )
34select
35 o.date
36 , l.product_id
37 , coalesce(p.title, l.line_product_title) as product_title
38 , p.product_type
39 , coalesce(p.vendor, l.line_vendor) as vendor
40 , l.variant_id
41 , l.variant_title
42 , l.sku
43 , sum(l.units) as units
44 , count(distinct o.order_id) as orders
45 , round(sum(l.gross_sales), 2) as gross_sales
46 , round(sum(l.discounts), 2) as discounts
47 , round(sum(l.gross_sales - l.discounts), 2) as net_sales
48from
49 lines l
50 join orders o using (order_id)
51 left join {{raw.shopify.product}} p on p.id = l.product_id
52group by
53 o.date
54 , l.product_id
55 , product_title
56 , p.product_type
57 , vendor
58 , l.variant_id
59 , l.variant_title
60 , l.sku
61order by
62 o.date
63 , net_sales desc
shopify_sales_by_product joins the raw order and order_line tables to product, rolling revenue up to product, variant and SKU per day.
Net sales for the top products - all footwear, led by Timberland and Dr Martens - with units alongside each. The same model lets you re-rank by units, product type or vendor.
3. Returning customer rate
What it is: each month's split of new versus returning customers, the returning-customer rate, and the revenue each group brings in.
Replicates: Native - Shopify's "Returning customer rate" / "First-time vs returning".
The SQL (view in the repo):
1-- shopify_returning_customer_rate - recreates Shopify's "Returning customer rate"
2-- Grain: month. A customer is NEW in the month of their first-ever order, RETURNING after that.
3with
4 orders as (
5 select
6 id as order_id
7 , customer_id
8 , date(processed_at) as order_date
9 , date_trunc(date(processed_at), month) as order_month
10 from
11 {{raw.shopify.order}}
12 where
13 coalesce(test, false) = false
14 and customer_id is not null
15 )
16 , order_revenue as (
17 select
18 order_id
19 , sum(
20 coalesce(price_set_shop_money_amount, price) * quantity - coalesce(
21 total_discount_set_shop_money_amount
22 , total_discount
23 , 0
24 )
25 ) as net_sales
26 from
27 {{raw.shopify.order_line}}
28 where
29 coalesce(gift_card, false) = false
30 group by
31 order_id
32 )
33 , first_order as (
34 select
35 customer_id
36 , min(order_date) as first_order_date
37 from
38 orders
39 group by
40 customer_id
41 )
42 , tagged as (
43 select
44 o.order_month
45 , o.customer_id
46 , o.order_id
47 , coalesce(r.net_sales, 0) as net_sales
48 , (
49 date_trunc(fo.first_order_date, month) < o.order_month
50 ) as is_returning_customer
51 from
52 orders o
53 join first_order fo on fo.customer_id = o.customer_id
54 left join order_revenue r on r.order_id = o.order_id
55 )
56select
57 order_month as month
58 , count(distinct customer_id) as total_customers
59 , count(
60 distinct if(not is_returning_customer, customer_id, null)
61 ) as new_customers
62 , count(
63 distinct if(is_returning_customer, customer_id, null)
64 ) as returning_customers
65 , round(
66 safe_divide(
67 count(
68 distinct if(is_returning_customer, customer_id, null)
69 )
70 , count(distinct customer_id)
71 ) * 100
72 , 1
73 ) as returning_customer_rate_pct
74 , count(distinct order_id) as orders
75 , round(sum(net_sales), 2) as net_sales
76 , round(
77 sum(if(not is_returning_customer, net_sales, 0))
78 , 2
79 ) as new_customer_net_sales
80 , round(sum(if(is_returning_customer, net_sales, 0)), 2) as returning_customer_net_sales
81 , round(
82 safe_divide(
83 sum(net_sales)
84 , nullif(count(distinct order_id), 0)
85 )
86 , 2
87 ) as average_order_value
88from
89 tagged
90group by
91 month
92order by
93 month
shopify_returning_customer_rate reads the raw order and order_line tables to split each month's customers into new versus returning.
New versus returning customers each month (left), and the returning-customer rate climbing from 0% at launch to about 46% as the base matures (right).
Bonus: Payment authorisation rates
What it is: daily card authorisation and decline rates by gateway - gross versus net auth - plus the top reasons payments get declined. Failed payments are silent lost revenue, and Shopify's dashboard never shows them.
Replicates: Weld custom template - not a native Shopify report. Standardised from a production customer model.
The SQL (view in the repo):
1-- shopify_payment_auth_rates - CUSTOM (no native Shopify report)
2-- Grain: day x gateway. gross auth = success / all attempts;
3-- net auth = success / (attempts - cardholder errors).
4with
5 attempts as (
6 select
7 cast(id as int64) as transaction_id
8 , cast(order_id as int64) as order_id
9 , date(processed_at) as day
10 , lower(cast(status as string)) as status
11 , lower(cast(gateway as string)) as gateway
12 , lower(cast(error_code as string)) as error_code
13 , cast(currency as string) as currency
14 , cast(amount as numeric) as amount
15 from
16 {{raw.shopify.transaction}}
17 where
18 lower(cast(kind as string)) in ('sale', 'authorization')
19 and lower(cast(status as string)) in ('success', 'failure', 'error')
20 and coalesce(test, false) = false
21 )
22 , classified as (
23 select
24 *
25 , case
26 when status = 'success' then 'success'
27 when error_code in (
28 'incorrect_cvc'
29 , 'invalid_cvc'
30 , 'expired_card'
31 , 'incorrect_number'
32 , 'incorrect_pin'
33 , 'incorrect_zip'
34 ) then 'cardholder_error'
35 else 'decline'
36 end as attempt_class
37 from
38 attempts
39 )
40select
41 day
42 , gateway
43 , currency
44 , count(*) as total_attempts
45 , countif(status = 'success') as successful_attempts
46 , countif(status != 'success') as failed_attempts
47 , countif(attempt_class = 'decline') as declines
48 , countif(attempt_class = 'cardholder_error') as cardholder_errors
49 , round(
50 safe_divide(countif(status = 'success'), count(*)) * 100
51 , 2
52 ) as gross_auth_rate_pct
53 , round(
54 safe_divide(
55 countif(status = 'success')
56 , countif(attempt_class != 'cardholder_error')
57 ) * 100
58 , 2
59 ) as net_auth_rate_pct
60 , round(
61 sum(
62 case
63 when status = 'success' then amount
64 else 0
65 end
66 )
67 , 2
68 ) as authorized_amount
69 , round(sum(amount), 2) as attempted_amount
70 , countif(error_code = 'card_declined') as card_declined_count
71 , countif(error_code = 'insufficient_funds') as insufficient_funds_count
72 , countif(error_code = 'expired_card') as expired_card_count
73 , countif(error_code = 'incorrect_cvc') as incorrect_cvc_count
74 , countif(error_code = 'processing_error') as processing_error_count
75from
76 classified
77group by
78 day
79 , gateway
80 , currency
81order by
82 day desc
83 , total_attempts descTip: Gross and net auth answer different questions. Gross auth (successful / all attempts) is what your customers actually experience. Net auth (successful / attempts minus cardholder errors like a wrong CVC or expired card) isolates the failures you might be able to fix on the payments side. Watching both by gateway is how you spot a processor quietly underperforming.
shopify_payment_auth_rates is built from a single source - the raw transaction table - turning every card attempt into daily authorisation and decline rates by gateway.
Gross and net authorisation by gateway on an 80-92% axis: all three gateways cluster around 84-85% gross and ~88% net. The gap between them is fixable cardholder errors, not bank declines.
How we built this (AI-native Weld: MCP + GitHub Sync workflow)
The interesting part isn't just the SQL - it's how fast and low-friction it was to build and ship. This is the workflow Weld is built for, and it's one you can copy today.
A local repo, push to deploy. These models are just .sql files in a local Git repo. You write them in your own IDE, git push, and GitHub Sync deploys them to Weld. No copy-pasting into a web editor, full version control and review on every change, and only the models that actually changed re-materialise. Idea to live model in minutes.
AI removes the SQL friction. You don't have to be a SQL expert. An AI agent, working through the Weld MCP, inspects your real synced data, writes each model, and validates it against the live warehouse before anything ships. It catches the gotchas a beginner would miss - using processed_at instead of created_at, keeping pending and refunded orders as real sales - so the agent does the heavy lifting and you just review the diff and push.
Put together, local repo plus GitHub plus AI is the whole point: the AI writes the SQL, Git ships it, Weld runs it, and a beginner moves at an expert's pace. For the deeper walkthroughs:
Make it yours
Here's the short path from this post to your own live dashboards:
- Grab the templates from the public repo - the source of truth for all four models.
- Get them into Weld. Connect the repo with GitHub Sync (push to deploy), or paste each model into a new Weld transform.
- Point them at your data. The models already read your raw Shopify tables (
raw.shopify.*), so once your Shopify streams are synced they run exactly as they are - no rewriting required. Each model's header lists the tables it reads, so you can check the fit at a glance. - Visualise. Point your BI tool at the published models, or hand a model to an AI agent and let it build the dashboard for you.
Conclusion
You now own four of Shopify's core reports instead of renting them - and that's just the start. Because the models live in your warehouse, you can join them to everything else Weld connects to (your ad platforms, your accounting and ERP data, your subscription and support tools) and turn today's reports into the foundation your whole analytics stack builds on.
