16.15. Shopify, Klavyio RFM Segmentation with BigQuery ML

Introduction

In this lesson, we'll explore a real-life example of how to use Looker Studio and BigQuery ML to analyze customer segmentation for a Shopify store using Klaviyo data. The goal is to understand different customer segments based on Recency, Frequency, and Monetary (RFM) value.

RFM Segmentation Concept

RFM segmentation is a method that helps businesses identify different types of customers based on their:

1. Recency: How recently they made a purchase
2. Frequency: How often they make purchases
3. Monetary Value: How much they've spent over their lifetime with the business

Using these three values, we can segment customers into various clusters like high-value spenders or frequent recent customers.

Problem: Undefined Customer Segments

In our example, the business had about 40,000 customers in their database but didn't know how to define the segments or which types of people to target.

Solution: K-means Clustering Algorithm

To solve this problem, we used the K-means clustering algorithm available in BigQuery ML. This algorithm helps find similar clusters of entities within a dataset.

The raw data available for this project included client ID, email address from Shopify, date of purchase, and transaction amount. Using this data:

1. We created a list of all purchases for every client.
2. Calculated RFM values for each customer.
3. Created an intermediary table with these RFM values.
4. Provided this table as input to the K-means clustering algorithm.

This process allowed us to explore the data without prescribing specific segments beforehand.

Results: Five Different Customer Clusters

The K-means clustering algorithm managed to find five distinct customer clusters based on their RFM values:

1. More than six months since the last purchase, low spenders
2. At least 1.5 years since the last purchase, very low monetary value
3. Recent customers (about six months), high spenders with more than 11 purchases on average
4. Loyal customers
5. Churning or close-to-churn customers

With these clusters identified and labeled, the business could export these lists and target each segment with a different marketing strategy.

Conclusion: Accessible Machine Learning with BigQuery ML

This example demonstrates that machine learning algorithms like K-means clustering can be learned and implemented quickly using Looker Studio and BigQuery ML without hiring a data scientist. The processing cost was minimal, making this approach accessible for businesses of all sizes.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn