How to set up your Shopify metrics in Weld

Shopify metrics in Weld

Looking to optimize your Shopify reporting? With our Shopify connector and user-friendly SQL editor for data Transform, supported by our AI assistant Ed, you can seamlessly integrate your Shopify data, whether it comes from different stores or all your other data sources. This enables you to create a holistic view of your business metrics, enhance your analytics, and make data-driven decisions with ease. In this post, we’ll walk you through setting up your Shopify metrics in Weld and show you how you can derive actionable insights from your data.

Why use Weld for your Shopify metrics?

Shopify is a powerful e-commerce platform that helps you build and manage your online stores. By integrating Shopify with Weld, you can combine your Shopify data with other data sources to get a holistic view of your business performance, allowing you to merge the data from the different stores and other data sources like Google Analytics, Facebook Ads, and more. This gives you a comprehensive view of your business metrics, allowing you to make more informed decisions and optimize your e-commerce operations.

Things to remember

Before you start setting up your Shopify metrics in Weld, there are a few things to keep in mind:

• First you'll need to set up your Shopify connector in Weld. If you haven't done this yet, you can follow the guide on our documentation.
• If you have multiple Shopify stores, you'll need to set up a connection for each of them. Remember to name them differently so you can easily identify them in Weld.
• Timezones: Shopify data is, by default, in UTC. We will show you how to convert it to your local timezone when creating your models.
• Currencies: You might have stores in different currencies and want to convert them to a single one. We will show you how to use Weld's Forex connector to perform this conversion.

A step by step example

Let's say you have two stores, one in Denmark and one in the UK, and you want to have an overview of the order data from both stores. You have connected them to Weld, named them shopify_dk and shopify_uk, and you are ready to start creating your metrics.

1. Staging your data

Shopify data is stored in different tables, such as order, product, customer, and more. To see how they interact with each other, especially across different stores, you can create staging models to combine all the data you need in one place. Let's look at the order and product tables as examples for our staging.

Order

The order table contains information about your orders, such as the order number, customer ID, status... Let's create a staging model for this data.

1with
2    all_stores as (
3        select
4            'DK' as store
5          , *
6        from
7            {{raw.shopify_dk.order}}
8        union all
9        select
10            'UK' as store
11          , *
12        from
13            {{raw.shopify_uk.order}}
14    )
15select
16    name as order_name
17  , customer_id
18  , id as order_id
19  , created_at as order_created_at_utc
20  , processed_at as order_processed_at_utc
21  , processing_method
22  , financial_status
23  , fulfillment_status
24  , note as order_note
25  , order_number
26  , presentment_currency as currency
27  , total_discounts
28  , total_price
29  , total_tax
30  , device_id
31  , landing_site
32  , landing_site_ref
33  , reference
34  , referring_site
35  , source_name
36  , source_url
37  , location_id as order_location_id
38  , email
39  , store
40  , shipping_address_country
41  , shipping_address_country_code
42  , billing_address_city
43  , billing_address_company
44  , shipping_address_city
45  , shipping_address_zip
46  , rank() over (
47        partition by
48            email
49        order by
50            created_at
51    ) as customer_order_seq_number
52from
53    all_stores

You can always ask Ed, Weld's AI assistant, to help you add all the information you want to include. Let's look at a second example with the product table.

Product

The product table contains information about your products, such as the product ID, title, price, and more. Let's create a staging model for this data.

1with
2    all_stores as (
3        select
4            'DK' as store
5          , *
6        from
7            {{raw.shopify_dk.product}}
8        union all
9        select
10            'UK' as store
11          , *
12        from
13            {{raw.shopify_uk.product}}
14    )
15select
16    id as product_id
17  , published_at as product_published_at_utc
18  , created_at as product_created_at_utc
19  , published_scope
20  , status as product_status
21  , vendor
22  , updated_at as product_updated_at_utc
23  , body_html
24  , product_type
25  , handle
26  , title as product_title
27  , store
28from
29    all_stores

You can publish these two models into a subfolder called shopify inside a staging folder, keeping all the models organized. This way, you can easily identify them and use them in your metrics. Let's jump forward and assume you also created similar staging models for the other tables you need for your metrics, such as customer, location, discount_allocation, order_line, and more.

2. Converting currencies

To convert the different currencies your stores use into a single one, you can use Weld's Forex connector. This connector allows you to convert currencies in real-time and get the latest exchange rates. You can add the Forex connector to your model and use it to convert the currencies in your data. Let's create a model to convert all the currencies to EUR, which you once again publish into staging with an appropriate name.

1select
2    cast(timestamp as date) as date
3  , `EUR` as source_currency
4  , target_currency as target_currency
5  , exchange_rate
6from
7    {{raw.forex.EUR_rates}}

3. Creating your metrics

Now that you have all your data staged and ready to go, you can start creating your metrics. Let's create a few queries based on the typical Shopify metrics you might want to track. For that, you will need to use a few core models that take into consideration the specific data available in Shopify and leverage the previous staging models. Let's look at examples of these models below.

Order Adjustment

Orders in Shopify can be adjusted for various reasons, such as refunds, discounts, or taxes. This model will allow you to track these adjustments and calculate the net and total refunds for each order. The order staging model exemplified before is one of the few models used in this query.

1select
2    oa.store
3  , order_refund_created_at_utc
4  , oa.order_id
5  , null as order_line_id
6  , o.order_name
7  , o.fulfillment_status
8  , o.customer_id
9  , concat(c.customer_first_name, ' ', c.customer_last_name) as customer_name
10  , o.email
11  , cast(null as string) as sku
12  , cast(null as string) as product_title
13  , cast(null as string) as product_variant_title
14  , billing_address_city
15  , o.billing_address_company
16  , o.shipping_address_city
17  , o.shipping_address_country
18  , o.shipping_address_zip
19  , null as quantity_returned
20  , o.currency
21  , oa.adjustment_amount_local - adjustment_tax_amount_local as net_refund
22  , oa.adjustment_amount_local as total_refund
23  , round(
24        (
25            oa.adjustment_amount_local - adjustment_tax_amount_local
26        ) * 1.0 / ex.exchange_rate
27      , 2
28    ) as net_refund_eur
29  , round(
30        oa.adjustment_amount_local * 1.0 / ex.exchange_rate
31      , 2
32    ) as total_refund_eur
33  , case
34        when customer_order_seq_number = 1 then 'First-time'
35        else 'Returning'
36    end as customer_type
37  , 0 as orders
38  , l.location_name
39from
40    {{staging.shopify.order_adjustment}} as oa
41    left join {{staging.shopify.order_refund}} r on oa.refund_id = r.order_refund_id
42    left join {{staging.shopify.order}} as o on oa.order_id = o.order_id
43    left join {{staging.shopify.location}} as l on o.order_location_id = l.location_id
44    left join {{staging.shopify.customer}} c on c.customer_id = o.customer_id
45    left join {{staging.forex_exchange_rates}} as ex on o.currency = ex.target_currency
46    and cast(o.order_created_at_utc as date) = ex.date

Order Items

To track the items in each order, such as the product ID, title, price, and quantity, you can use a model such as this one. You can see how it refers to many different staging models you created earlier, including the order and product models from before.

1with
2    discount_order_line as (
3        -- If a discount was applied to an order, we here get it on an order_line level
4        select
5            store
6          , order_line_id
7          , sum(amount) as discount_amount
8        from
9            {{staging.shopify.discount_allocation}}
10        group by
11            1
12          , 2
13    )
14  , order_items as (
15        select
16            ol.store
17          , o.order_name
18          , o.fulfillment_status
19          , o.customer_id
20          , o.email
21          , concat(customer_first_name, ' ', customer_last_name) customer_name
22          , ol.order_line_id
23          , ol.order_id
24          , ol.order_line_index
25          , billing_address_city
26          , billing_address_company
27          , shipping_address_city
28          , shipping_address_country
29          , shipping_address_zip
30          , o.order_created_at_utc order_created_at
31          , ol.order_line_name
32          , o.currency
33          , ol.order_line_price as order_line_price
34          , ol.order_line_price / (1 + ifnull(tx.tax_rate, 0)) as order_line_price_net
35          , round(ol.order_line_price * 1.0 / ex.exchange_rate, 2) as order_line_price_eur
36          , round(
37                (
38                    ol.order_line_price / (1 + ifnull(tx.tax_rate, 0))
39                ) * 1.0 / ex.exchange_rate
40              , 2
41            ) as order_line_price_eur_net
42          , ol.gift_card as is_gift_card
43          , ol.product_id
44          , ol.sku
45          , ol.quantity
46          , p.product_title
47          , v.product_variant_id
48          , v.title product_variant_title
49          , p.product_type
50          , ifnull(tx.tax_local, 0) as tax
51          , round(
52                ifnull(tx.tax_local, 0) * 1.0 / ex.exchange_rate
53              , 2
54            ) as tax_eur
55          , dol.discount_amount
56          , round(dol.discount_amount * 1.0 / ex.exchange_rate, 2) as discount_amount_eur
57          , dol.discount_amount / (1 + ifnull(tx.tax_rate, 0)) as discount_amount_net
58          , round(
59                (
60                    dol.discount_amount / (1 + ifnull(tx.tax_rate, 0))
61                ) * 1.0 / ex.exchange_rate
62              , 2
63            ) as discount_amount_eur_net
64          , case
65                when customer_order_seq_number = 1 then 'First-time'
66                else 'Returning'
67            end as customer_type
68          , l.location_name
69        from
70            {{staging.shopify.order_line}} as ol
71            inner join {{staging.shopify.order}} as o on ol.order_id = o.order_id
72            left join {{staging.shopify.location}} as l on o.order_location_id = l.location_id
73            left join {{staging.shopify.customer}} c on c.customer_id = o.customer_id
74            left join {{staging.shopify.product_variant}} as v on ol.product_variant_id = v.product_variant_id
75            left join {{staging.shopify.product}} as p on ol.product_id = p.product_id
76            left join {{staging.forex_exchange_rates}} as ex on o.currency = ex.target_currency
77            and cast(o.order_created_at_utc as date) = ex.date
78            left join {{staging.shopify.order_tax_line}} as tx on ol.order_line_id = tx.order_line_id
79            left join discount_order_line as dol on ol.order_line_id = dol.order_line_id
80            and ol.store = dol.store
81    )
82  , calculations as (
83        -- We now add the fulfillment and discount details to the order_items
84        select
85            order_items.store
86          , order_items.order_id
87          , order_items.order_name
88          , order_items.order_line_id
89          , order_items.order_line_index
90          , order_items.order_created_at
91          , order_items.quantity
92          , email
93          , customer_id
94          , customer_name
95          , fulfillment_status
96          , billing_address_city
97          , billing_address_company
98          , shipping_address_city
99          , shipping_address_country
100          , shipping_address_zip
101          , currency
102          , sku
103          , product_title
104          , product_variant_title -- Issueing of gift cards is not included in the sales report. When a customer pays with a gift card, the amount is included at the point of sale.
105          , location_name
106          , customer_type
107          , case
108                when is_gift_card then 0
109                else order_items.order_line_price_eur
110            end as order_line_price_eur
111          , case
112                when is_gift_card then 0
113                else order_items.order_line_price_eur_net
114            end as order_line_price_eur_net
115          , case
116                when is_gift_card then 0
117                else order_items.tax_eur
118            end as tax_eur
119          , case
120                when is_gift_card then 0
121                else order_items.discount_amount_eur
122            end as discount_amount_eur
123          , case
124                when is_gift_card then 0
125                else order_items.discount_amount_eur_net
126            end as discount_amount_eur_net
127          , case
128                when is_gift_card then 0
129                else order_items.order_line_price
130            end as order_line_price
131          , case
132                when is_gift_card then 0
133                else order_items.order_line_price_net
134            end as order_line_price_net
135          , case
136                when is_gift_card then 0
137                else order_items.tax
138            end as tax
139          , case
140                when is_gift_card then 0
141                else order_items.discount_amount
142            end as discount_amount
143          , case
144                when is_gift_card then 0
145                else order_items.discount_amount_net
146            end as discount_amount_net
147        from
148            order_items
149    )
150select
151    store
152  , order_created_at
153  , order_id
154  , order_line_id
155  , order_name
156  , email
157  , customer_id
158  , customer_name
159  , sku
160  , fulfillment_status
161  , product_title
162  , product_variant_title
163  , billing_address_city
164  , billing_address_company
165  , shipping_address_city
166  , shipping_address_country
167  , shipping_address_zip
168  , quantity
169  , currency
170  , quantity * order_line_price_eur_net as gross_sales_eur
171  , discount_amount_eur_net
172  , quantity * order_line_price_eur - ifnull(discount_amount_eur, 0) - ifnull(tax_eur, 0) as net_sales_eur
173  , tax_eur
174  , quantity * order_line_price_eur - ifnull(discount_amount_eur, 0) as total_sales_eur
175  , quantity * order_line_price_net as gross_sales
176  , discount_amount_net
177  , quantity * order_line_price - ifnull(discount_amount, 0) - ifnull(tax_eur, 0) as net_sales
178  , tax
179  , quantity * order_line_price - ifnull(discount_amount, 0) as total_sales
180  , order_line_index
181  , customer_type
182  , location_name
183from
184    calculations

Order Items Refund

What about the refunds? This model will allow you to track the refunded items in each order, such as the product ID, title, price, and quantity. Just like before, we make use of staging models.

1select
2    r.store
3  , orr.order_refund_created_at_utc
4  , r.order_line_refund_id
5  , r.location_id
6  , r.order_line_id
7  , o.fulfillment_status
8  , o.order_name
9  , c.email
10  , c.customer_id
11  , concat(c.customer_first_name, ' ', c.customer_last_name) as customer_name
12  , o.billing_address_city
13  , o.billing_address_company
14  , o.shipping_address_city
15  , o.shipping_address_country
16  , o.shipping_address_zip
17  , r.quantity as quantity_returned
18  , r.order_refund_id as refund_id
19  , r.restock_type
20  , r.subtotal as total_refund
21  , r.subtotal - r.total_tax as net_refund
22  , round(r.subtotal * 1.0 / ex.exchange_rate, 2) as total_refund_eur
23  , r.total_tax
24  , round(r.total_tax * 1.0 / ex.exchange_rate, 2) as total_tax_eur
25  , round(
26        (r.subtotal - r.total_tax) * 1.0 / ex.exchange_rate
27      , 2
28    ) as net_refund_eur
29  , o.order_id
30  , o.currency
31  , ol.order_line_price
32  , ol.product_id
33  , ol.sku
34  , p.product_title
35  , v.product_variant_id
36  , v.title as product_variant_title
37  , p.product_type
38  , gift_card as is_gift_card
39  , case
40        when customer_order_seq_number = 1 then 'First-time'
41        else 'Returning'
42    end as customer_type
43  , location_name
44from
45    {{staging.shopify.order_line_refund}} as r
46    left join {{staging.shopify.order_refund}} orr on orr.order_refund_id = r.order_refund_id
47    left join {{staging.shopify.order_line}} as ol on r.order_line_id = ol.order_line_id
48    left join {{staging.shopify.order}} as o on ol.order_id = o.order_id
49    left join {{staging.shopify.location}} as l on o.order_location_id = l.location_id
50    left join {{staging.shopify.customer}} c on c.customer_id = o.customer_id
51    left join {{staging.shopify.product_variant}} as v on ol.product_variant_id = v.product_variant_id
52    left join {{staging.shopify.product}} as p on ol.product_id = p.product_id
53    left join {{staging.forex_exchange_rates}} as ex on o.currency = ex.target_currency
54    and cast(o.order_created_at_utc as date) = ex.date

4. The final model

Now that you have all the data ready to use, you can create a final model that combines all of it to track your Shopify metrics. This model will combine the core models from the previous step to create an overarching order_master model.

1select
2    store
3  , order_created_at_utc as date
4  , order_id
5  , order_line_id
6  , order_name
7  , fulfillment_status
8  , customer_id
9  , customer_name
10  , email
11  , sku
12  , product_title
13  , product_variant_title
14  , billing_address_city
15  , billing_address_company
16  , shipping_address_city
17  , shipping_address_country
18  , shipping_address_zip
19  , quantity
20  , currency
21  , net_sales_eur as net_sales
22  , total_sales_eur as total_sales
23  , net_sales_eur as net_sales_eur
24  , total_sales_eur as total_sales_eur
25  , customer_type
26  , location_name
27  , 1 as orders
28from
29    {{core.order_items}}
30union all
31select
32    store
33  , order_refund_created_at_utc
34  , order_id
35  , order_line_id
36  , order_name
37  , fulfillment_status
38  , customer_id
39  , customer_name
40  , email
41  , sku
42  , product_title
43  , product_variant_title
44  , billing_address_city
45  , billing_address_company
46  , shipping_address_city
47  , shipping_address_country
48  , shipping_address_zip
49  , quantity_returned
50  , currency
51  , - net_refund_eur as net_sales
52  , - total_refund_eur as total_sales
53  , - net_refund_eur as net_sales_eur
54  , - total_refund_eur as total_sales_eur
55  , customer_type
56  , location_name
57  , 0 as orders
58from
59    {{core.order_items_refund}}
60union all
61select
62    store
63  , order_refund_created_at_utc
64  , order_id
65  , order_line_id
66  , order_name
67  , fulfillment_status
68  , customer_id
69  , customer_name
70  , email
71  , sku
72  , product_title
73  , product_variant_title
74  , billing_address_city
75  , billing_address_company
76  , shipping_address_city
77  , shipping_address_country
78  , shipping_address_zip
79  , quantity_returned
80  , currency
81  , net_refund_eur as net_sales
82  , total_refund_eur as total_sales
83  , net_refund_eur as net_sales_eur
84  , total_refund_eur as total_sales_eur
85  , customer_type
86  , location_name
87  , 0 as orders
88from
89    {{core.order_adjustment}}

5. Creating your report

Now that you have your final model ready, you can create a report to track your Shopify metrics. Let's look at one final orders model that shows your key metrics for orders, such as the date, customer type, billing and shipping addresses, store, net sales, items, and sales in EUR. We can also order them by order_id to have a better view of the data.

1select
2    date as date
3  , order_id
4  , email as customer_email
5  , customer_name
6  , customer_type
7  , customer_id
8  , billing_address_city as billing_city
9  , billing_address_company as billing_company
10  , shipping_address_city as shipping_address_city
11  , shipping_address_country as shipping_country_region
12  , shipping_address_zip as shipping_postal_code
13  , store as account_name
14  , location_name
15  , min(orders) as orders
16  , sum(total_sales) as total_sales
17  , sum(net_sales) as net_sales
18  , sum(quantity) as items
19  , sum(total_sales_eur) as total_sales_eur
20  , sum(net_sales_eur) as net_sales_eur
21from
22    {{core.order_master}}
23group by
24    date
25  , order_id
26  , email
27  , customer_type
28  , customer_name
29  , customer_id
30  , billing_address_city
31  , billing_address_company
32  , shipping_address_city
33  , shipping_address_country
34  , shipping_address_zip
35  , store
36  , location_name
37order by
38    order_id asc

As always, you can customize this report to include other metrics or data sources, such as Google Analytics, Facebook Ads, and many others, to get a more comprehensive view of your business performance and make more informed decisions.

Conclusion

With Weld you can easily set up your Shopify metrics and get actionable insights from your data. Simple to use and with powerful features, our connectors and AI assistant Ed make it easy to integrate your Shopify data with other data sources, create custom metrics, and generate reports that help you optimize your e-commerce operations and make data-driven decisions.