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 / order_id
5 , o.name / order_id_shopify
6 , email
7 , customer_id
8 , shipping_address_country_code country
9 , financial_status
10 , o.fulfillment_status o_fulfillment_status
11 , l.fulfillment_status ol_fulfillment_status
12 , taxes_included
13 , total_discounts
14 , total_price total_price_incl_tax
15 , total_price_usd
16 , total_tax total_tax
17 , round(
18 ifnull(
19 safe_divide(total_tax, (total_price - total_tax))
20 , 0
21 )
22 , 2
23 ) 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 /
31 , l.id / line_id
32 , l.price line_price
33 , safe_divide(ifnull(d.amount, 0), quantity) line_discount_amount
34 , ifnull(s.discounted_price, 0) 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_weld.order}} as o
54 left join {{raw.shopify_weld.order_line}} l on o.id / = l.id /
55 left join {{raw.shopify_weld.discount_allocation}} d on l.id / = d.order_line_id
56 left join {{raw.shopify_weld.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 ) utm_medium
65 , max(
66 case
67 when key = 'utm_source' then value
68 end
69 ) utm_source
70 , max(
71 case
72 when key = 'utm_campaign' then value
73 end
74 ) utm_campaign
75 from
76 {{raw.shopify_weld.order_url_tag}}
77 group by
78 order_id
79 ) c on o.id / = c.order_id
80 left join {{raw.shopify_weld.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 ) count_order_lines
93 , (
94 safe_divide(
95 (line_price - line_discount_amount)
96 , (1 + tax_pct)
97 )
98 ) * quantity line_price_ex_tax
99 , (line_price - line_discount_amount) * quantity line_price_incl_tax
100 , (line_price + line_discount_amount) * quantity 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 ) line_shipping_price_ex_tax
111 , safe_divide(
112 shipping_price
113 , count(order_id) over (
114 partition by
115 order_id
116 )
117 ) 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 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 ) 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 only_line_discounts
150 , line_shipping_price_ex_tax + line_price_ex_tax total_line_price_ex_tax
151 , line_shipping_price_incl_tax + line_price_incl_tax total_line_price_incl_tax
152 , line_shipping_price_incl_tax + line_price_incl_adding_tax 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 -- Calculate how much of discounts is distributed onto order_lines, and how much is for entire order. Entire order discount will be deducted from shipping
167
168 , total_order_line_discounts
169 , only_line_discounts
170 , case only_line_discounts
171 when true then 0
172 else total_discounts - total_order_line_discounts
173 end as order_level_discounts_incl_tax
174 , case only_line_discounts
175 when true then 0
176 else safe_divide(
177 (total_discounts - total_order_line_discounts)
178 , 1 + ifnull(tax_pct, 0)
179 ) -- Calculate order level discounts and subtract tax_pct from that to get total_line_price
180 end as order_level_discounts_ex_tax
181 , total_price_incl_tax
182 , total_price_usd
183 , M.total_tax
184 , tax_pct
185 , current_total_tax
186 , current_subtotal_price
187 , current_total_price
188 , current_total_discounts
189 , subtotal_price
190 , presentment_currency
191 , m.name /
192 , line_id
193 , line_price
194 , line_discount_amount
195 , shipping_price
196 , product_id
197 , sku
198 , variant_title
199 , variant_id
200 , M.quantity
201 , fulfillable_quantity
202 , referring_site
203 , landing_site
204 , landing_site_ref
205 , billing_address_country_code
206 , shipping_address_country
207 , utm_medium
208 , utm_source
209 , utm_campaign
210 , closed_at
211 , created_at
212 , title
213 , count_order_lines
214 , R.subtotal_set_shop_money_amount subtotal_set_shop_money_amount
215 , total_line_price_incl_tax - ifnull(R.subtotal_set_shop_money_amount, 0) as line_return
216 , line_price_ex_tax
217 , line_price_incl_tax
218 , line_shipping_price_ex_tax
219 , line_shipping_price_incl_tax
220 , line_tax
221 , line_shipping_tax
222 , total_line_price_ex_tax
223 , total_line_price_incl_tax
224 , total_line_price_incl_adding_tax
225from
226 total_calculations m
227 left join {{raw.shopify_weld.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 | 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 | 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
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 | 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
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 | 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 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.
