Shopify Buyer Segmentation for Digital Consultancy

bigquery dashboard for ecommerce

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

It’s easy to get lost in a wormhole of analysis in your Shopify store – merchandising, content, UI, checkout funnels, you name it.

We teamed up with the eCommerce experts at Growth Engines to build a simple data pipeline + dashboard to help them answer 1 simple question:

“Who are our best customers, and what marketing channels are helping them find us?”

We built a custom, beautiful dashboard in Google Data Studio to answer that question.

Click to view sample dashboard

buyer segmentation tool

The problem

When managing a portfolio of clients, it’s easy to get lost in data analysis. The team at Growth Engines was looking for a simple, easy to digest dashboard that delivered the data they needed.

They find buyer segmentation to be a critical analysis step before and during marketing campaigns – as Drew Sanocki of Growth Engines puts it:

“Customers are not all alike. In a typical retail business, 20% of the customers drive 80% of the revenue. The quicker retailers realize this, identify that 20%, and build the business around that 20%, the faster they can impact revenue.”

Together, we produced an analysis template for any Shopify store with Google Analytics enabled.

The solution

Analyzing Shopify will crash a Google Sheet (2 million cell limit), so we used our Agency Data Pipeline stack.

 

Using Stitch to push data into BigQuery

A code-free (and often cost-free) tool to pipe your data from Shopify + Google Analytics into the database (check out Stitch).

bigquery-shopify

 

Open-source SQL models

Written in the DBT framework, these models can be pulled off the shelf to transform your raw data into answers.

dbt-sql-model

 

A Data Studio dashboard template

To visualize your answers and share them with colleagues.

shopify-data-studio-template

The results

Buyer segmentation is not a prescriptive analysis – it doesn’t output one ‘magic bullet’ to improve your revenue.

Instead, it’s a way of seeing the shape and flow of your customer base – the current running through your business.

Here’s a few key insights Drew and his team rely on this report for.

 

Metrics

For most Ecommerce shops, a handful of core metrics can drive decision-making. For this analysis recipe, we’ll focus on three:

Ultimately it’s up to you, using the intuition you’ve built around your store, to decide which of these metrics to focus on.

If you’re selling a consumer staples like razors, you’ll want to focus on frequency. If you’re selling a rarely-purchased items like mattresses, maximizing AOV will be your bread and butter.

Either way, you can easily mine that data from this report.

buyer metrics reporting

 

Segments

Generally it’s necessary to segment buyers in a few different ways, to discover which method exposes the true distinction in your customer base.

In this analysis recipe, we slice by three different segments:

 

By revenue percentile

By breaking down buyers into spend buckets, you can glean insights about your customer base. From this example, we see that buyers who spend in the 90th+ percentile drove over ⅓ of revenue last month.

 

By frequency

Do one-time buyers drive most of your revenue, is does your shop depend more on repeat business?

Knowing the answer will help you allocate resources across retention (customer support) or acquisition (marketing) efforts – or it might expose a gaping hole in either of them.

buyer segmentation by frequency

 

By marketing channel (from Google Analytics)

How you spend time and money across channels might be the primary tool in our revenue-growth toolbelt.

First, we want to check the overall health of each marketing channel – roughly how many buyers + revenue are each driving, and how do they stack up across metrics?

health check by shopify channel

In the demo shop above, we see that Organic Search is outperforming both Adwords and FB Ads in terms of retention rate, frequency and AOV.

We can also mashup our marketing channel segments against the revenue and frequency segments, to see which channel brings in our best (or worst) customers.

shopify data

This buyer index normalizes the success of each channel to a score of 1.

A value greater than 1 means the channel drove, relative to its total revenue, a higher percentage of ‘Frequency = 2’ (or any segment) buyers than you’d expect.

A value less than 1 means the channel drove less revenue through that segment than you’d expect.

So looking at the above example, we again see that Organic Search did a solid job of driving top 10%-spending buyers, whereas FB Ads did a less-than-stellar job.

 

Taking action on the data

Thanks to Data Studio’s CSV export functionality, we can use our buyer segments to build email lists for campaigns.

Say you wanted to build a lookalike audience of buyers who purchased twice or more via FB Ads. From the second page of the recipe’s Data Studio template, you can do that with a few dropdown clicks:

shopify buyer list export

Dive in for yourself

You can grab this Recipe for free from the CIFL Template Vault here.

The recipe includes a few ingredients:

If you’d like help setting up this Shopify buyer segmentation analysis on your store’s behalf, just drop a note to help@codingisforlosers.com.

Thanks again to Michael and Drew at Growth Engines for their inspiration and support in building out this analysis recipe.

Ready for BigQuery?

Get off the ground quickly, with quickstart Recipes for BigQuery.

GET COOKING