Shopify
Orders with tax, shipping , refunds and discounts
Retrieves detailed information about orders, including tax, shipping, refunds, and discounts, from a Shopify integration. It uses several subqueries to calculate various metrics such as total order line discounts, order level discounts, and line return.
1with
2 staging as (
3 select
4 o.id as order_id
5 , o.name as order_id_shopify
6 , email
7 , customer_id
8 , shipping_address_country_code as country
9 , financial_status
10 , o.fulfillment_status as o_fulfillment_status
11 , l.fulfillment_status as ol_fulfillment_status
12 , taxes_included
13 , total_discounts
14 , total_price as total_price_incl_tax
15 , total_price_usd
16 , total_tax as total_tax
17 , round(
18 ifnull(
19 safe_divide(total_tax, (total_price - total_tax))
20 , 0
21 )
22 , 2
23 ) as tax_pct
24 , current_total_tax
25 , current_subtotal_price
26 , current_total_price
27 , current_total_discounts
28 , subtotal_price
29 , presentment_currency
30 , l.name as line_name
31 , l.id as line_id
32 , l.price as line_price
33 , safe_divide(ifnull(cast(d.value as float64), 0), l.quantity) as line_discount_amount
34 , ifnull(s.discounted_price, 0) as shipping_price
35 , l.product_id
36 , l.sku
37 , l.variant_title
38 , l.variant_id
39 , l.quantity
40 , l.fulfillable_quantity
41 , o.referring_site
42 , o.landing_site
43 , o.landing_site_ref
44 , o.billing_address_country_code
45 , o.shipping_address_country
46 , c.utm_medium
47 , c.utm_source
48 , c.utm_campaign
49 , o.closed_at
50 , cast(o.created_at as date) as created_at
51 , p.title
52 from
53 {{raw.shopify.order}} as o
54 left join {{raw.shopify.order_line}} l on o.id = l.order_id
55 left join {{raw.shopify.discount_application}} d on l.id = d.order_id
56 left join {{raw.shopify.order_shipping_line}} s on o.id = s.order_id
57 left join (
58 select
59 order_id
60 , max(
61 case
62 when key = 'utm_medium' then value
63 end
64 ) as utm_medium
65 , max(
66 case
67 when key = 'utm_source' then value
68 end
69 ) as utm_source
70 , max(
71 case
72 when key = 'utm_campaign' then value
73 end
74 ) as utm_campaign
75 from
76 {{raw.shopify.order_url_tag}}
77 group by
78 order_id
79 ) c on o.id = c.order_id
80 left join {{raw.shopify.product}} p on l.product_id = p.id
81 )
82 , calculations as (
83 select
84 *
85 , sum(line_discount_amount * quantity) over (
86 partition by
87 order_id_shopify
88 ) as total_order_line_discounts
89 , count(order_id) over (
90 partition by
91 order_id
92 ) as count_order_lines
93 , (
94 safe_divide(
95 (line_price - line_discount_amount)
96 , (1 + tax_pct)
97 )
98 ) * quantity as line_price_ex_tax
99 , (line_price - line_discount_amount) * quantity as line_price_incl_tax
100 , (line_price + line_discount_amount) * quantity as line_price_incl_adding_tax
101 , safe_divide(
102 safe_divide(
103 shipping_price
104 , count(order_id) over (
105 partition by
106 order_id
107 )
108 )
109 , (1 + tax_pct)
110 ) as line_shipping_price_ex_tax
111 , safe_divide(
112 shipping_price
113 , count(order_id) over (
114 partition by
115 order_id
116 )
117 ) as line_shipping_price_incl_tax
118 , ((line_price - line_discount_amount) * quantity) - (
119 safe_divide(
120 (line_price - line_discount_amount)
121 , (1 + tax_pct)
122 )
123 ) * quantity as line_tax
124 , safe_divide(
125 shipping_price
126 , count(order_id) over (
127 partition by
128 order_id
129 )
130 ) - safe_divide(
131 safe_divide(
132 shipping_price
133 , count(order_id) over (
134 partition by
135 order_id
136 )
137 )
138 , (1 + tax_pct)
139 ) as line_shipping_tax
140 from
141 staging
142 )
143 , total_calculations as (
144 select
145 *
146 , case
147 when round(total_order_line_discounts, 2) = round(total_discounts, 2) then true
148 else false
149 end as only_line_discounts
150 , line_shipping_price_ex_tax + line_price_ex_tax as total_line_price_ex_tax
151 , line_shipping_price_incl_tax + line_price_incl_tax as total_line_price_incl_tax
152 , line_shipping_price_incl_tax + line_price_incl_adding_tax as total_line_price_incl_adding_tax
153 from
154 calculations
155 )
156select
157 m.order_id
158 , order_id_shopify
159 , email
160 , customer_id
161 , country
162 , financial_status
163 , o_fulfillment_status
164 , ol_fulfillment_status
165 , taxes_included
166 , total_discounts
167 , total_order_line_discounts
168 , only_line_discounts
169 , case
170 when only_line_discounts then 0
171 else total_discounts - total_order_line_discounts
172 end as order_level_discounts_incl_tax
173 , case
174 when only_line_discounts then 0
175 else safe_divide(
176 (total_discounts - total_order_line_discounts)
177 , 1 + ifnull(tax_pct, 0)
178 )
179 end as order_level_discounts_ex_tax
180 , total_price_incl_tax
181 , total_price_usd
182 , m.total_tax
183 , tax_pct
184 , current_total_tax
185 , current_subtotal_price
186 , current_total_price
187 , current_total_discounts
188 , subtotal_price
189 , presentment_currency
190 , line_name
191 , line_id
192 , line_price
193 , line_discount_amount
194 , shipping_price
195 , product_id
196 , sku
197 , variant_title
198 , variant_id
199 , m.quantity
200 , fulfillable_quantity
201 , referring_site
202 , landing_site
203 , landing_site_ref
204 , billing_address_country_code
205 , shipping_address_country
206 , utm_medium
207 , utm_source
208 , utm_campaign
209 , closed_at
210 , created_at
211 , title
212 , count_order_lines
213 , r.subtotal_set_shop_money_amount as subtotal_set_shop_money_amount
214 , total_line_price_incl_tax - ifnull(r.subtotal_set_shop_money_amount, 0) as line_return
215 , line_price_ex_tax
216 , line_price_incl_tax
217 , line_shipping_price_ex_tax
218 , line_shipping_price_incl_tax
219 , line_tax
220 , line_shipping_tax
221 , total_line_price_ex_tax
222 , total_line_price_incl_tax
223 , total_line_price_incl_adding_tax
224from
225 total_calculations m
226 left join {{raw.shopify.order_line_refund}} r on m.line_id = r.order_line_idExample of output from model:
| order_id | order_id_shopify | email | customer_id | country | financial_status | o_fulfillment_status | ol_fulfillment_status | taxes_included | total_discounts | total_order_line_discounts | only_line_discounts | order_level_discounts_incl_tax | order_level_discounts_ex_tax | total_price_incl_tax | total_price_usd | total_tax | tax_pct | current_total_tax | current_subtotal_price | current_total_price | current_total_discounts | subtotal_price | presentment_currency | line_name | line_id | line_price | line_discount_amount | shipping_price | product_id | sku | variant_title | variant_id | quantity | fulfillable_quantity | referring_site | landing_site | landing_site_ref | billing_address_country_code | shipping_address_country | utm_medium | utm_source | utm_campaign | closed_at | created_at | title | count_order_lines | subtotal_set_shop_money_amount | line_return | line_price_ex_tax | line_price_incl_tax | line_shipping_price_ex_tax | line_shipping_price_incl_tax | line_tax | line_shipping_tax | total_line_price_ex_tax | total_line_price_incl_tax | total_line_price_incl_adding_tax |
|----------|------------------|-------------------------|-------------|---------|------------------|----------------------|-----------------------|----------------|-----------------|----------------------------|---------------------|-------------------------------|-----------------------------|----------------------|-----------------|-----------|---------|-------------------|-----------------------|---------------------|------------------------|----------------|----------------------|------------------|---------|------------|----------------------|----------------|-------------|--------|-----------------|------------|----------|-----------------------|----------------|--------------|-----------------|--------------------------------|-------------------------|------------|------------|-----------------|------------|--------------|-----------------|-------------------|--------------------------------|-------------|-------------------|--------------------|----------------------------|-----------------------------|----------|-------------------|------------------------|--------------------------|----------------------------|
| 12345 | 1001 | john.doe@example.com | 123 | US | paid | fulfilled | fulfilled | true | 10.00 | 5.00 | false | 5.00 | 4.72 | 100.00 | 80.00 | 10.00 | 0.10 | 10.00 | 50.00 | 60.00 | 5.00 | USD | Example Product | 1 | 20.00 | 2.00 | 5.00 | 5.00 | 123 | ABC123 | Example Variant | 456 | 2 | 2 | example.com | example.com | example.com | US | US | cpc | google | example_campaign | 2022-01-01 | 2022-01-01 | Example Product | 2 | 0.00 | 18.00 | 2.50 | 3.00 | 0.40 | 0.50 | 36.00 | 42.00 | 48.00 | 48.00 | |
| 23456 | 1002 | jane.smith@example.com | 456 | CA | paid | fulfilled | fulfilled | true | 5.00 | 2.50 | true | 2.50 | 2.36 | 50.00 | 40.00 | 5.00 | 0.10 | 5.00 | 25.00 | 30.00 | 2.50 | CAD | Another Product | 1 | 15.00 | 1.50 | 3.00 | 3.00 | 456 | DEF456 | Another Variant | 789 | 3 | 3 | example.com | example.com | example.com | CA | CA | cpc | google | example_campaign | 2022-01-02 | 2022-01-02 | Another Product | 1 | 0.00 | 13.50 | 1.87 | 2.25 | 0.30 | 0.38 | 40.50 | 45.00 | 51.00 | 51.00 | |
| 34567 | 1003 | mike.johnson@example.com| 789 | GB | paid | unfulfilled | unfulfilled | true | 0.00 | 0.00 | false | 0.00 | 0.00 | 80.00 | 60.00 | 20.00 | 0.25 | 20.00 | 80.00 | 100.00 | 0.00 | GBP | Third Product | 1 | 30.00 | 0.00 | 0.00 | 0.00 | 789 | GHI789 | Third Variant | 123 | 1 | 1 | example.com | example.com | example.com | GB | GB | cpc | google | example_campaign | 2022-01-03 | 2022-01-03 | Third Product | 1 | 0.00 | 30.00 | 0.00 | 0.00 | 0.00 | 0.00 | 30.00 | 30.00 | 30.00 | 30.00 | |This SQL template is designed to integrate with Shopify data. It uses a common table expression (CTE) to create a staging table that joins several Shopify tables together, including order, order line, discount allocation, order shipping line, order URL tag, and product. The CTE then performs several calculations to derive additional columns, such as total order line discounts, line price ex tax, line price incl tax, line price incl adding tax, line shipping price ex tax, line shipping price incl tax, line tax, and line shipping tax. Finally, the CTE joins with the order line refund table to calculate the line return. The resulting SQL code generates a table with a wide range of order-related data, including order ID, customer ID, country, financial status, fulfillment status, taxes included, total discounts, total order line discounts, only line discounts, order level discounts incl tax, order level discounts ex tax, total price incl tax, total price USD, total tax, tax pct, current total tax, current subtotal price, current total price, current total discounts, subtotal price, presentment currency, line ID, line price, line discount amount, shipping price, product ID, SKU, variant title, variant ID, quantity, fulfillable quantity, referring site, landing site, landing site ref, billing address country code, shipping address country, UTM medium, UTM source, UTM campaign, closed at, created at, title, count order lines, subtotal set shop money amount, total line price incl tax, line return, line price ex tax, line price incl tax, line shipping price ex tax, line shipping price incl tax, line tax, line shipping tax, total line price ex tax, total line price incl tax, and total line price incl adding tax.
