Weld logo
shopify logo
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_id
Example 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.

Ready to start modeling your own shopify data?

Get started building your data warehouse with shopify and connect all your apps and databases.

shopify logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.