Quick answer:
A customer analytics view is a saved SQL query returning one row per Shopify customer with location, first and most recent order dates, order count, discounts, returns, net sales and total sales.
Totalling refunds, shipping and line items per order before summing per customer prevents double counting. For basic Shopify customer analytics without warehouse SQL, Shopify’s ShopifyQL customers schema covers spending, order counts and recency.
If you’re serious about growing your Shopify store, customer analytics is the place to start. By digging deep into your customer data, you can discover important insights around buying behavior and key benchmarks for your Shopify store. That data enables you to make better decisions about how to increase customer loyalty, reduce marketing spend, and deliver the right messaging to the right customer segments.
But before we get into the specifics around creating a customer analytics view for Shopify, let’s take a moment to explore why it’s worth creating a view and walk through the easy-peasy one step process.
This tutorial features SQL queries written in Postgres-style SQL, which also runs on Amazon Redshift, Snowflake and DuckDB. If you’re on a Google BigQuery data warehouse, put a dataset name in front of each table and view name.
How and why to create a view
We’ve talked about views before, but the message is worth repeating: When you repeatedly use the same bit of SQL for your work, it’s probably worth saving that query as a view.
Views are saved SQL queries that create virtual tables you can include in a select statement instead of having to rewrite (or copy and paste) those same bits of code over and over again. Along with streamlining your workflow, views make your SQL easier to read and create consistency by ensuring that you pull the same fields from the same data source and transform them using the same business logic every time you run a query.
Given how powerful views are, creating them is surprisingly simple. Just write a query you plan to use often and then wrap the query in a few extra lines of SQL:
/* VIEW CREATION TEMPLATE */
CREATE VIEW name_your_view AS
--Insert your SQL query here
;
That’s it: by adding just one line of code above your query, you’ve got a reusable view and you’ll never have to figure out those joins again.
Does Shopify Already Give You Customer Analytics?
Shopify’s ShopifyQL customers schema returns one row per customer, organized by first purchase. Its metrics include total_amount_spent, total_number_of_orders and days_since_last_order for spending, order counts and recency.
Dimensions include customer_country, customer_tags, rfm_group and predicted_spend_tier for grouping customers by location or buying behavior. The GraphQL Admin API’s Customer object also provides amountSpent and numberOfOrders.
This query from Shopify’s reference ranks the 100 highest-spending customers over the last 30 days:
FROM customers
SHOW total_number_of_orders, total_amount_spent
GROUP BY customer_name, customer_email_subscription_status,
customer_first_order_date WITH TOTALS
SINCE startOfDay(-30d) UNTIL today
ORDER BY total_amount_spent DESC
LIMIT 100
VISUALIZE total_amount_spent TYPE horizontal_bar
A view in your own data warehouse is useful when Shopify’s definitions don’t match yours: you decide which orders count, how refunds are treated and which address represents a customer.
You can also join it to data outside Shopify, such as ad spend or support tickets. Other tools can read the same virtual table, so reports share one definition of customer spending.
How to create a customer analytics view for Shopify
Why do you need a customer analytics view for Shopify? Convenience. Having that view ready to go creates a virtual table you can query against for all sorts of purposes. For example, you can use this view to find customer info like email address and location as well as purchase details like total orders, discounts, returns, and net sales.
The query looks like a doozy, but remember that to get started, all you have to do is copy, paste, and explore.
The query uses generic table names: customers, customer_addresses, orders, order_line_items, refunds, order_line_refunds and order_shipping_lines. Connectors such as Fivetran and Airbyte name their tables their own way, so map these names to yours. The Shopify data model chapter explains their relationships.
/* SHOPIFY CUSTOMER ANALYTICS VIEW */
CREATE VIEW shopify_customer_analytics AS
WITH valid_orders AS (
/* Completed purchases only: no test or cancelled orders */
SELECT *
FROM orders
WHERE NOT test
AND cancelled_at IS NULL
),
items_by_order AS (
/* LINE ITEMS: units sold per order */
SELECT order_id,
sum(quantity) AS items_sold
FROM order_line_items
GROUP BY 1
),
refunds_by_order AS (
/* REFUNDS: value, tax and units returned per order */
SELECT r.order_id,
sum(rli.subtotal) AS refunds,
sum(rli.total_tax) AS tax_returned,
sum(rli.quantity) AS items_returned
FROM refunds r
JOIN order_line_refunds rli ON rli.refund_id = r.id
GROUP BY 1
),
shipping_by_order AS (
/* SHIPPING LINES: charges after shipping discounts */
SELECT order_id,
sum(discounted_price) AS shipping_charge
FROM order_shipping_lines
GROUP BY 1
),
order_metrics AS (
/* One row per order, so customer totals never double count */
SELECT o.id,
o.customer_id,
o.created_at,
o.total_line_items_price AS gross_sales,
- coalesce(o.total_discounts, 0) AS discounts,
- coalesce(r.refunds, 0) AS sales_returns,
coalesce(o.total_tax, 0) - coalesce(r.tax_returned, 0) AS taxes,
coalesce(s.shipping_charge, 0) AS shipping,
coalesce(i.items_sold, 0) AS items_sold,
coalesce(r.items_returned, 0) AS items_returned
FROM valid_orders o
LEFT JOIN items_by_order i ON i.order_id = o.id
LEFT JOIN refunds_by_order r ON r.order_id = o.id
LEFT JOIN shipping_by_order s ON s.order_id = o.id
)
SELECT
/* Customer dimensions */
c.id AS customer_id,
lower(c.email) AS email,
c.tags,
upper(a.city) AS city,
a.province_code,
a.country_code,
c.created_at,
min(om.created_at) AS first_order_date,
max(om.created_at) AS recent_order_date,
/* Standard metrics */
count(om.id) AS total_orders,
coalesce(sum(om.items_sold), 0) AS items_sold,
coalesce(sum(om.items_returned), 0) AS items_returned,
coalesce(sum(om.gross_sales), 0) AS gross_sales,
coalesce(sum(om.discounts), 0) AS discounts,
coalesce(sum(om.sales_returns), 0) AS sales_returns,
coalesce(sum(om.gross_sales + om.discounts + om.sales_returns), 0) AS net_sales,
coalesce(sum(om.taxes), 0) AS taxes,
coalesce(sum(om.shipping), 0) AS shipping,
coalesce(sum(om.gross_sales + om.discounts + om.sales_returns
+ om.taxes + om.shipping), 0) AS total_sales
FROM customers c
LEFT JOIN customer_addresses a ON a.customer_id = c.id
AND a.is_default
LEFT JOIN order_metrics om ON om.customer_id = c.id
GROUP BY c.id, c.email, c.tags, a.city, a.province_code, a.country_code, c.created_at;
The query totals each child table per order before joining, because several child rows repeat an order’s totals. Test and cancelled orders are filtered out in the first step, so customers without a qualifying order still appear, with zeros. Joining only the default address keeps one row per customer, and emails are lowercased.
In the sample store, Sarah’s non-default second address leaves her with one row. Hanna’s cancelled order and Nadim’s test order don’t count. Nadim’s $330 refund reduces his net sales; Alys signed up without buying and appears with zeros.
| total_orders | gross_sales | discounts | sales_returns | net_sales | total_sales | |
|---|---|---|---|---|---|---|
| sarah@example.com | 2 | 3,030.00 | -150.00 | -300.00 | 2,580.00 | 2,787.70 |
| eli@example.com | 1 | 1,250.00 | 0.00 | 0.00 | 1,250.00 | 1,337.50 |
| hanna@example.com | 1 | 240.00 | -24.00 | 0.00 | 216.00 | 241.07 |
| jordan@example.com | 3 | 124.80 | -6.24 | 0.00 | 118.56 | 146.75 |
| nadim@example.com | 1 | 1,980.00 | 0.00 | -330.00 | 1,650.00 | 1,805.50 |
| alys@example.com | 0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
You can slice and dice the data in this view to do all kinds of analysis, from finding simple info like most recent purchase date to more complex analysis like customer lifetime value (CLV). But before you start, it’s important to know that we’ve embedded a bit of business logic into this customer analytics view.
For example, here’s how we calculate net_sales:
coalesce(sum(om.gross_sales + om.discounts + om.sales_returns), 0) AS net_sales
That means net_sales factors in price, discounts, and refunds, but we haven’t included other potential elements like shipping. While we’re confident that our approach works for our (entirely hypothetical) Shopify store, we’d encourage you to work through the SQL to make sure our business logic makes sense for your shop before you use it.
total_sales adds taxes, minus refunded tax, and shipping to net_sales. Shopify defines net sales as sales after discounts and sales reversals, including refunds, returns, cancellations and order edits, with returns dated when processed. This view excludes cancelled orders and subtracts refunds from the ordering customer, so period totals can differ even when customer totals agree.
Putting the customer view to work
Once the view exists, most customer questions become short queries. This one counts customers, repeat buyers and net sales by country, along with net sales per order:
/* CUSTOMERS, REPEAT BUYERS AND NET SALES BY COUNTRY */
SELECT country_code,
count(*) AS customers,
sum(CASE WHEN total_orders >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
sum(net_sales) AS net_sales,
round(sum(net_sales) / nullif(sum(total_orders), 0), 2) AS net_sales_per_order
FROM shopify_customer_analytics
WHERE total_orders > 0
GROUP BY country_code
ORDER BY net_sales DESC;
| country_code | customers | repeat_customers | net_sales | net_sales_per_order |
|---|---|---|---|---|
| CA | 2 | 0 | 2,900.00 | 1,450.00 |
| US | 2 | 2 | 2,698.56 | 539.71 |
| GB | 1 | 0 | 216.00 | 216.00 |
The two Canadian customers bring in the most net sales, but neither has ordered twice. Both US customers are repeat buyers with smaller orders. Net sales per order isn’t Shopify’s average order value, which uses gross sales minus discounts. The next query finds customers who signed up but never bought, for a first-purchase email:
/* SIGNED UP, NEVER ORDERED */
SELECT email,
tags,
created_at
FROM shopify_customer_analytics
WHERE total_orders = 0
ORDER BY created_at DESC;
The RFM segments chapter scores recency, frequency and monetary value, the same three facts this view holds, and the CLV chapter turns order history into value per customer.
BI tools such as Metabase or Looker Studio read the view from your warehouse. Each read reruns its query; if that gets slow, a scheduled dbt model can store the result as a table.
Common questions
Why don’t the view’s totals match Shopify’s reports?
The view and Shopify use different rules for sales reversals. Shopify includes cancellations and edits and dates returns when processed; this view drops cancelled orders and attaches refunds to the original customer. Both exclude test orders. Timezone and date boundaries also affect period comparisons.
Should I use a view or a table?
A view calculates results from current warehouse data each time you query it. For large stores, a table rebuilt on a schedule, for example through a dbt model, can be faster to query. Its results reflect the latest rebuild rather than subsequent changes.
How do I change the view later?
Most warehouses support CREATE OR REPLACE VIEW to update the saved query. In Postgres, the replacement must keep existing columns’ names, order and types; new columns may only be added at the end. Other structural changes require dropping and recreating the view.
Can I analyze Shopify customers without a data warehouse?
Yes. Shopify’s ShopifyQL customers schema and customer segments cover spending, order counts and recency, with rfm_group available for grouping customers by buying behavior. A warehouse becomes useful when you need your own metric definitions or want to join customer records to data outside Shopify.
Where to next
The Shopify data model chapter explains the tables behind this view and is the next chapter in this guide. To turn these columns into customer segments, continue to the RFM segments chapter. You can also return to the Shopify analytics guide for the full list of chapters.
Connectors, warehouses and BI tools that speak Shopify, with pricing and free tiers.
Browse the tools