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 email
48 ORDER BY
49 created_at
50 ) AS customer_order_seq_number
51FROM
52 all_storesYou 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_storesYou 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.dateOrder 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 discount_order_line AS (
2 -- If a discount was applied to an order, we here get it on an order_line level
3 SELECT
4 store,
5 order_line_id,
6 SUM(amount) AS discount_amount
7 FROM
8 {{staging.shopify.discount_allocation}}
9 GROUP BY
10 1,
11 2
12),
13order_items AS (
14 SELECT
15 ol.store,
16 o.order_name,
17 o.fulfillment_status,
18 o.customer_id,
19 o.email,
20 CONCAT(customer_first_name, ' ', customer_last_name) customer_name,
21 ol.order_line_id,
22 ol.order_id,
23 ol.order_line_index,
24 billing_address_city,
25 billing_address_company,
26 shipping_address_city,
27 shipping_address_country,
28 shipping_address_zip,
29 o.order_created_at_utc order_created_at,
30 ol.order_line_name,
31 o.currency,
32 ol.order_line_price AS order_line_price,
33 ol.order_line_price / (1 + IFNULL(tx.tax_rate, 0)) AS order_line_price_net,
34 ROUND(ol.order_line_price * 1.0 / ex.exchange_rate, 2) AS order_line_price_eur,
35 ROUND((ol.order_line_price / (1 + IFNULL(tx.tax_rate, 0))) * 1.0 / ex.exchange_rate, 2) AS order_line_price_eur_net,
36 ol.gift_card AS is_gift_card,
37 ol.product_id,
38 ol.sku,
39 ol.quantity,
40 p.product_title,
41 v.product_variant_id,
42 v.title product_variant_title,
43 p.product_type,
44 IFNULL(tx.tax_local, 0) AS tax,
45 ROUND(IFNULL(tx.tax_local, 0) * 1.0 / ex.exchange_rate, 2) AS tax_eur,
46 dol.discount_amount,
47 ROUND(dol.discount_amount * 1.0 / ex.exchange_rate, 2) AS discount_amount_eur,
48 dol.discount_amount / (1 + IFNULL(tx.tax_rate, 0)) AS discount_amount_net,
49 ROUND((dol.discount_amount / (1 + IFNULL(tx.tax_rate, 0))) * 1.0 / ex.exchange_rate, 2) AS discount_amount_eur_net,
50 CASE
51 WHEN customer_order_seq_number = 1 THEN 'First-time'
52 ELSE 'Returning'
53 END AS customer_type,
54 l.location_name
55 FROM
56 {{staging.shopify.order_line}} AS ol
57 INNER JOIN {{staging.shopify.order}} AS o ON ol.order_id = o.order_id
58 LEFT JOIN {{staging.shopify.location}} AS l ON o.order_location_id = l.location_id
59 LEFT JOIN {{staging.shopify.customer}} c ON c.customer_id
60 = o.customer_id
61 LEFT JOIN {{staging.shopify.product_variant}} AS v ON ol.product_variant_id = v.product_variant_id
62 LEFT JOIN {{staging.shopify.product}} AS p ON ol.product_id = p.product_id
63 LEFT JOIN {{staging.forex_exchange_rates}} AS ex ON o.currency = ex.target_currency
64 AND CAST(o.order_created_at_utc AS date) = ex.date
65 LEFT JOIN {{staging.shopify.order_tax_line}} AS tx ON ol.order_line_id = tx.order_line_id
66 LEFT JOIN discount_order_line AS dol ON ol.order_line_id = dol.order_line_id
67 AND ol.store = dol.store
68),
69calculations AS (
70 -- We now add the fulfillment and discount details to the order_items
71 SELECT
72 order_items.store,
73 order_items.order_id,
74 order_items.order_name,
75 order_items.order_line_id,
76 order_items.order_line_index,
77 order_items.order_created_at,
78 order_items.quantity,
79 email,
80 customer_id,
81 customer_name,
82 fulfillment_status,
83 billing_address_city,
84 billing_address_company,
85 shipping_address_city,
86 shipping_address_country,
87 shipping_address_zip,
88 currency,
89 sku,
90 product_title,
91 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.
92 location_name,
93 customer_type,
94 CASE
95 WHEN is_gift_card THEN 0
96 ELSE order_items.order_line_price_eur
97 END AS order_line_price_eur,
98 CASE
99 WHEN is_gift_card THEN 0
100 ELSE order_items.order_line_price_eur_net
101 END AS order_line_price_eur_net,
102 CASE
103 WHEN is_gift_card THEN 0
104 ELSE order_items.tax_eur
105 END AS tax_eur,
106 CASE
107 WHEN is_gift_card THEN 0
108 ELSE order_items.discount_amount_eur
109 END AS discount_amount_eur,
110 CASE
111 WHEN is_gift_card THEN 0
112 ELSE order_items.discount_amount_eur_net
113 END AS discount_amount_eur_net,
114 CASE
115 WHEN is_gift_card THEN 0
116 ELSE order_items.order_line_price
117 END AS order_line_price,
118 CASE
119 WHEN is_gift_card THEN 0
120 ELSE order_items.order_line_price_net
121 END AS order_line_price_net,
122 CASE
123 WHEN is_gift_card THEN 0
124 ELSE order_items.tax
125 END AS tax,
126 CASE
127 WHEN is_gift_card THEN 0
128 ELSE order_items.discount_amount
129 END AS discount_amount,
130 CASE
131 WHEN is_gift_card THEN 0
132 ELSE order_items.discount_amount_net
133 END AS discount_amount_net
134 FROM
135 order_items
136)
137SELECT
138 store,
139 order_created_at,
140 order_id,
141 order_line_id,
142 order_name,
143 email,
144 customer_id,
145 customer_name,
146 sku,
147 fulfillment_status,
148 product_title,
149 product_variant_title,
150 billing_address_city,
151 billing_address_company,
152 shipping_address_city,
153 shipping_address_country,
154 shipping_address_zip,
155 quantity,
156 currency,
157 quantity * order_line_price_eur_net AS gross_sales_eur,
158 discount_amount_eur_net,
159 quantity * order_line_price_eur - IFNULL(discount_amount_eur, 0) - IFNULL(tax_eur, 0) AS net_sales_eur,
160 tax_eur,
161 quantity * order_line_price_eur - IFNULL(discount_amount_eur, 0) AS total_sales_eur,
162 quantity * order_line_price_net AS gross_sales,
163 discount_amount_net,
164 quantity * order_line_price - IFNULL(discount_amount, 0) - IFNULL(tax_eur, 0) AS net_sales,
165 tax,
166 quantity * order_line_price - IFNULL(discount_amount, 0) AS total_sales,
167 order_line_index,
168 customer_type,
169 location_name
170FROM
171 calculationsOrder 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((r.subtotal - r.total_tax) * 1.0 / ex.exchange_rate, 2) AS net_refund_eur
26 , o.order_id
27 , o.currency
28 , ol.order_line_price
29 , ol.product_id
30 , ol.sku
31 , p.product_title
32 , v.product_variant_id
33 , v.title AS product_variant_title
34 , p.product_type
35 , gift_card AS is_gift_card
36 , CASE
37 WHEN customer_order_seq_number = 1 THEN 'First-time'
38 ELSE 'Returning'
39 END AS customer_type
40 , location_name
41FROM
42 {{staging.shopify.order_line_refund}} AS r
43 LEFT JOIN {{staging.shopify.order_refund}} orr ON orr.order_refund_id = r.order_refund_id
44 LEFT JOIN {{staging.shopify.order_line}} AS ol ON r.order_line_id = ol.order_line_id
45 LEFT JOIN {{staging.shopify.order}} AS o ON ol.order_id = o.order_id
46 LEFT JOIN {{staging.shopify.location}} AS l ON o.order_location_id = l.location_id
47 LEFT JOIN {{staging.shopify.customer}} c ON c.customer_id = o.customer_id
48 LEFT JOIN {{staging.shopify.product_variant}} AS v ON ol.product_variant_id = v.product_variant_id
49 LEFT JOIN {{staging.shopify.product}} AS p ON ol.product_id = p.product_id
50 LEFT JOIN {{staging.forex_exchange_rates}} AS ex ON o.currency = ex.target_currency
51 AND CAST(o.order_created_at_utc AS date) = ex.date4. 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}}
23
24
25GROUP BY
26 date
27 , order_id
28 , email
29 , customer_type
30 , customer_name
31 , customer_id
32 , billing_address_city
33 , billing_address_company
34 , shipping_address_city
35 , shipping_address_country
36 , shipping_address_zip
37 , store
38 , location_name
39 ORDER BY order_id ASCAs 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.



