Order Items Sales

1with
2    -- CTE: Aggregate the discount allocations by order line
3    discount_order_line as (
4        select
5            order_line_id
6          , sum(amount) as discount_amount
7        from
8            {{raw.shopify.discount_allocation}}
9        group by
10            order_line_id
11    )
12  , -- CTE: Filter out successful fulfillment orders with their created timestamp
13    fulfillments_success as (
14        select
15            order_id
16          , id as fulfillment_id
17          , created_at as fulfillment_created_at
18        from
19            {{raw.shopify.fulfillment}}
20        where
21            status = 'success'
22    )
23  , -- CTE: Join fulfillment order lines with successful fulfillment details
24    fulfillment_success_order_lines as (
25        select
26            foi.order_id
27          , foi.order_line_id
28          , foi.fulfillment_id
29          , fs.fulfillment_created_at as order_line_fulfilled_at
30        from
31            {{raw.shopify.fulfillment_order_line}} as foi
32            inner join fulfillments_success as fs on foi.fulfillment_id = fs.fulfillment_id
33    )
34  , -- CTE: Combine order line details with orders, customers, products, variants, tax, discount, and fulfillment info
35    order_items as (
36        select
37            o.name as order_name
38          , o.fulfillment_status
39          , o.customer_id
40          , o.email
41          , concat(first_name, ' ', last_name) as customer_name
42          , ol.id as order_line_id
43          , ol.order_id
44          , ol.index as order_line_index
45          , billing_address_city
46          , billing_address_company
47          , shipping_address_city
48          , shipping_address_country
49          , shipping_address_zip
50          , o.created_at as order_created_at
51          , ol.name as order_line_name
52          , o.currency
53          , ol.price as order_line_price
54          , ol.price / (1 + ifnull(tx.rate, 0)) as order_line_price_net
55          , ol.gift_card as is_gift_card
56          , ol.product_id
57          , ol.sku
58          , ol.quantity
59          , o.financial_status
60          , p.title as product_title
61          , v.title as product_variant_title
62          , p.product_type
63          , ifnull(tx.price, 0) as tax
64          , dol.discount_amount
65          , dol.discount_amount / (1 + ifnull(tx.rate, 0)) as discount_amount_net
66          , case
67                when c.order_count = 1 then 'First-time'
68                else 'Returning'
69            end as customer_type
70          , -- Fulfillment information
71            fulfillment_success_order_lines.fulfillment_id
72          , fulfillment_success_order_lines.order_line_fulfilled_at
73          , rank() over (
74                partition by
75                    ol.order_id
76                order by
77                    fulfillment_success_order_lines.order_line_fulfilled_at nulls last
78            ) as fulfillment_rank
79        from
80            {{raw.shopify.order_line}} as ol
81            inner join {{raw.shopify.order}} as o on ol.order_id = o.id
82            left join {{raw.shopify.customer}} c on c.id = o.customer_id
83            left join {{raw.shopify.product_variant}} as v on ol.variant_id = v.id
84            left join {{raw.shopify.product}} as p on ol.product_id = p.id
85            left join {{raw.shopify.order_tax_line}} as tx on ol.id = tx.line_item_id
86            left join discount_order_line as dol on ol.id = dol.order_line_id
87            left join fulfillment_success_order_lines on ol.id = fulfillment_success_order_lines.order_line_id
88    )
89  , -- CTE: Finalize calculations by handling gift cards and applying net price conversions for sales metrics
90    calculations as (
91        select
92            order_items.order_id
93          , order_items.order_name
94          , order_items.order_line_id
95          , order_items.order_line_index
96          , order_items.order_created_at
97          , order_items.quantity
98          , email
99          , customer_id
100          , customer_name
101          , fulfillment_status
102          , billing_address_city
103          , billing_address_company
104          , shipping_address_city
105          , shipping_address_country
106          , shipping_address_zip
107          , currency
108          , sku
109          , financial_status
110          , product_title
111          , product_variant_title
112          , customer_type
113          , case
114                when is_gift_card then 0
115                else order_items.order_line_price
116            end as order_line_price
117          , case
118                when is_gift_card then 0
119                else order_items.order_line_price_net
120            end as order_line_price_net
121          , case
122                when is_gift_card then 0
123                else order_items.tax
124            end as tax
125          , case
126                when is_gift_card then 0
127                else order_items.discount_amount
128            end as discount_amount
129          , case
130                when is_gift_card then 0
131                else order_items.discount_amount_net
132            end as discount_amount_net
133        from
134            order_items
135    )
136    -- Final SELECT: Calculate the sales metrics with gross sales, net sales, tax, and total sales
137select
138    order_id
139  , order_created_at
140  , order_line_id
141  , order_name
142  , email
143  , customer_id
144  , customer_name
145  , sku
146  , fulfillment_status
147  , product_title
148  , product_variant_title
149  , billing_address_city
150  , billing_address_company
151  , shipping_address_city
152  , shipping_address_country
153  , shipping_address_zip
154  , quantity
155  , currency
156  , quantity * order_line_price_net as gross_sales
157  , discount_amount_net
158  , quantity * order_line_price - ifnull(discount_amount, 0) - ifnull(tax, 0) as net_sales
159  , tax
160  , quantity * order_line_price - ifnull(discount_amount, 0) as total_sales
161  , order_line_index
162  , customer_type
163  , financial_status
164from
165    calculations