Shopify Customer Analytics

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.

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 bracketing your query with just one line of code at either end, you’ve got a reusable view and you’ll never have to figure out those joins again.

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.

/* SHOPIFY CUSTOMER ANALYTICS VIEW */
CREATE VIEW shopify_customer_analytics AS (
	/* SHOPIFY CUSTOMER METRICS */
	SELECT
		/* Customer dimensions */
		c.id,
		c.email,
		c.tags,
		upper(cda.city) city,
		cda.province_code,
		cda.country_code,
		c.created_at,
		min(o.created_at) first_order_date,
		max(o.created_at) recent_order_date,
		/* Standard metrics */
		count(o.id) AS total_orders,
		sum(o.total_line_items_price) AS gross_sales,
		sum(- o.total_discounts) AS discounts,
		sum(- nvl (r.refunds, 0)) AS "returns",
		gross_sales + discounts + "returns" AS net_sales,
		sum(o.total_tax) - sum(nvl (r.tax_returned, 0)) AS taxes,
		sum(nvl (sl.shipping_charge, 0)) AS shipping_cost,
		gross_sales + discounts + "returns" + taxes + shipping_cost AS total_sales
	FROM
		shopify_customers c
	LEFT JOIN shopify_customers_default_address cda ON c.id = cda.shopify_customers_id
	LEFT JOIN shopify_orders_customer_default_address ocda ON c.id = ocda.customer_id
	LEFT JOIN shopify_orders_customer oc ON ocda.shopify_orders_customer_id = oc.id
	LEFT JOIN shopify_orders o ON oc.shopify_orders_id = o.id 
	LEFT JOIN (
		/* LINE ITEMS TABLE */
		SELECT
			li.shopify_orders_id,
			sum(lipsm.amount * li.quantity) gross_price,
			count(li.id) AS number_of_items_sold,
			sum(li.total_discount) AS line_item_discount
		FROM
			shopify_orders_line_items li
			LEFT JOIN shopify_orders_line_items_price_set lips ON li.id = lips.shopify_orders_line_items_id
			LEFT JOIN shopify_orders_line_items_price_set_shop_money lipsm ON lips.id = lipsm.shopify_orders_line_items_price_set_id
		GROUP BY
			1) li ON o.id = li.shopify_orders_id
	LEFT JOIN (
		/* REFUNDS TABLE */
		SELECT
			r.shopify_orders_id,
			count(r.id) number_of_items_returned,
			sum(rli.subtotal) AS refunds,
			sum(rli.total_tax) tax_returned
		FROM
			shopify_orders_refunds r
			LEFT JOIN shopify_orders_refunds_refund_line_items rli ON r.id = rli.shopify_orders_refunds_id
		GROUP BY
			1) r ON o.id = r.shopify_orders_id
	LEFT JOIN (
		/* SHIPPING LINES TABLE */
		SELECT
			sl.shopify_orders_id,
			sum(sl.price) shipping_charge,
			sum(sl.discounted_price) shipping_discount
		FROM
			shopify_orders_shipping_lines sl
		GROUP BY
			1) sl ON o.id = sl.shopify_orders_id
	WHERE o.cancelled_at isnull
	GROUP BY 1, 2, 3, 4, 5, 6, 7
);

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:

sum(o.total_line_items_price) + sum((- o.total_discounts))
) + (sum((- COALESCE(r.refunds, (0) :: bigint)))) :: double precision
    ) 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.