Shopify Report Dashboard

1with
2    all_orders as (
3        select
4            'dk' as store
5          , o.id as order_id
6          , o.created_at as order_date
7          , o.customer_id
8          , case
9                when c.order_count > 1 then 'Returning'
10                else 'New'
11            end as customer_type
12          , o.source_name as sales_channel
13          , o.total_price as order_amount
14          , o.total_discounts as discount_applied
15          , o.financial_status as payment_status
16          , o.fulfillment_status
17          , fx.exchange_rate
18          , (o.total_price * fx.exchange_rate) as total_sales_tgt
19        from
20            {{raw.shopify.order}} o
21            left join {{raw.shopify.customer}} c on o.customer_id = c.id
22            left join {{raw.forex.EUR_rates}} fx on cast(o.created_at as date) = cast(fx.timestamp as date)
23            and fx.target_currency = o.currency
24    )
25  , all_refunds as (
26        select
27            'dk' as store
28          , r.order_id
29          , sum(rl.subtotal + rl.total_tax) as refund_amount_local
30          , sum((rl.subtotal + rl.total_tax) * fx.exchange_rate) as refund_amount_tgt
31        from
32            {{raw.shopify.order_refund}} r
33            left join {{raw.shopify.order_line_refund}} rl on r.id = rl.refund_id
34            left join {{raw.forex.EUR_rates}} fx on cast(r.created_at as date) = cast(fx.timestamp as date)
35            and fx.target_currency = 'DKK'
36        group by
37            r.order_id
38    )
39  , orders_with_refunds as (
40        select
41            o.store
42          , o.order_id
43          , o.order_date
44          , o.customer_id
45          , o.customer_type
46          , o.sales_channel
47          , o.order_amount
48          , o.discount_applied
49          , coalesce(r.refund_amount_local, 0) as refund_amount
50          , coalesce(r.refund_amount_tgt, 0) as refund_amount_tgt
51          , o.payment_status
52          , o.fulfillment_status
53          , o.exchange_rate
54          , o.total_sales_tgt
55        from
56            all_orders o
57            left join all_refunds r on o.order_id = r.order_id
58            and o.store = r.store
59    )
60  , products_per_order as (
61        select
62            ol.order_id
63          , ol.title as product_sold
64          , ol.price as product_price
65          , ol.quantity as product_quantity
66        from
67            {{raw.shopify.order_line}} ol
68    )
69  , metrics_with_products as (
70        select
71            owr.order_id
72          , owr.store
73          , owr.order_date
74          , owr.customer_id
75          , owr.customer_type
76          , owr.sales_channel
77          , owr.order_amount
78          , owr.discount_applied
79          , owr.refund_amount
80          , owr.refund_amount_tgt
81          , owr.payment_status
82          , owr.fulfillment_status
83          , owr.exchange_rate
84          , owr.total_sales_tgt
85          , ppo.product_sold
86          , ppo.product_price
87          , ppo.product_quantity
88          , (ppo.product_price * ppo.product_quantity) as product_sales_local
89          , (
90                (ppo.product_price * ppo.product_quantity) * owr.exchange_rate
91            ) as product_sales_tgt
92          , row_number() over (
93                partition by
94                    owr.order_id
95                order by
96                    ppo.product_sold
97            ) as row_num
98        from
99            orders_with_refunds owr
100            join products_per_order ppo on owr.order_id = ppo.order_id
101    )
102select
103    order_id
104  , store
105  , order_date
106  , customer_id
107  , customer_type
108  , sales_channel
109  , product_sold
110  , product_price
111  , product_quantity
112  , product_sales_local
113  , product_sales_tgt
114  , case
115        when row_num = 1 then order_amount
116        else null
117    end as total_order_amount
118  , case
119        when row_num = 1 then discount_applied
120        else null
121    end as total_discounts
122  , case
123        when row_num = 1 then refund_amount
124        else null
125    end as total_refunds_local
126  , case
127        when row_num = 1 then refund_amount_tgt
128        else null
129    end as total_refunds_tgt
130  , case
131        when row_num = 1 then total_sales_tgt
132        else null
133    end as total_sales_tgt
134  , case
135        when row_num = 1 then (order_amount - refund_amount)
136        else null
137    end as net_sales_local
138  , case
139        when row_num = 1 then (total_sales_tgt - refund_amount_tgt)
140        else null
141    end as net_sales_tgt
142from
143    metrics_with_products;