K-means Clustering for Customer Segmentation.

1. Introduction to Customer Segmentation

Marketing professionals need to understand their customers. Data can be collected from numerous sources, including online storefronts, mobile apps, email campaigns, CRM systems, website analytics, social media channels, surveys, market research, and in-store transactions. Once the data is gathered, it is organized and analyzed. Selecting a customer segmentation method is crucial for tailoring marketing strategies to specific population segments.

1.1. Customer Segmentation Methods

2. Big Query

Before diving into the specifics of customer segmentation methods with machine learning, we must select a data warehouse to store and manage data. According to Gartner’s 2023 Magic Quadrant for Cloud Database Management Systems, the top companies in the cloud data warehouse field are Google with BigQuery, Amazon with Redshift, Microsoft with Azure Synapse Analytics, and Oracle. Their 2023 data warehouse report compares different data warehouses.
For our segmentation exercise, we’ve chosen BigQuery. BigQuery is a data management solution on Google Cloud. It uses standard SQL for querying and has built-in machine learning (BigQuery ML), allowing us to create and run machine learning models using SQL. SQL (Structured Query Language) was developed at IBM in the early 1970s. SQL helps us organize, retrieve, insert data, and modify databases.

2.1 Pull and Prepare the Data

To conduct the segmentation, we should connect our data sources to the data management solution we selected. The customer data we will use for our WordPress websites is in Woocommerce, the e-commerce plugin installed on our websites. We also need Google Analytics data to gather additional behavioral data.

To automate the extraction of WooCommerce data, we need to pull in the following variables for segmentation: customer_id, name, age, city, country, order_id, order_date, order_amount, and product_id. We can use tools like Supermetrics, which provides pre-built connectors to pull the data into BigQuery. Alternatively, we can use the WooCommerce REST API to connect WooCommerce with BigQuery directly, or we can set up cron jobs (automated scripts) on a server or cloud service to fetch and load the data into BigQuery on a daily or weekly basis.
Google Analytics is more straightforward to connect because BigQuery has a native connector for GA4 (Google Analytics 4) that can automatically import data.
We can combine both datasets once the data is imported into one table. SQL can do that for us. We need to use an SQL joins query that connects the WooCommerce table with the Google Analytics table using the customer_id field. This will combine the main fields from both sources and create one complete table. To keep this combined table up to date with the most recent data, we can schedule the SQL query to run automatically in BigQuery.

3. Recency, Frequency, Monetary Value

We can cluster customer groups based on their purchasing behavior. This segmentation is often based on the Recency, Frequency, and Monetary (RFM) model. This model takes into account how much money a customer spends on purchases (Monetary), how recently a customer made a purchase (Recency), and how often a customer makes a purchase (Frequency). We must calculate each customer’s Recency, Frequency, and Monetary (RFM) values to analyze customer behavior. To calculate Recency, we will find the number of days since each customer’s last purchase by subtracting their most recent order date from the current date. For Frequency, we will count the total number of purchases made by each customer. Finally, to calculate the Monetary value, we will sum up the total amount spent by each customer over a specific period. We can use a combined SQL query in BigQuery to gather the necessary data for calculating the RFM values.

WITH recency AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency
  FROM
    `your_project.your_dataset.woocommerce_table`
  GROUP BY
    customer_id
),
frequency AS (
  SELECT
    customer_id,
    COUNT(order_id) AS frequency
  FROM
    `your_project.your_dataset.woocommerce_table`
  GROUP BY
    customer_id
),
monetary AS (
  SELECT
    customer_id,
    SUM(order_amount) AS monetary
  FROM
    `your_project.your_dataset.woocommerce_table`
  GROUP BY
    customer_id
)

SELECT
  r.customer_id,
  r.recency,
  f.frequency,
  m.monetary
FROM
  recency r
JOIN
  frequency f ON r.customer_id = f.customer_id
JOIN
  monetary m ON r.customer_id = m.customer_id;

3.1 Standardize the RFM variables.

Before using K-means clustering, make sure your data is standardized. This is important because RFM values have different scales. For example, monetary values could range in the thousands, while frequency could be a single-digit number. Standardizing the data, usually by converting it to z-scores, helps the K-means algorithm perform better by treating all variables equally. The z-score shows how far a data point is from the average of a dataset, measured in standard deviations. It helps determine if a data point is typical or unusual compared to the rest. A positive z-score means the value is above average, and a negative z-score is below average.

Standardization SQL Query:

WITH standardized_rfm AS (
  SELECT
    customer_id,
    (recency - AVG(recency) OVER()) / STDDEV(recency) OVER() AS recency_z,
    (frequency - AVG(frequency) OVER()) / STDDEV(frequency) OVER() AS frequency_z,
    (monetary - AVG(monetary) OVER()) / STDDEV(monetary) OVER() AS monetary_z
  FROM
    `your_project.your_dataset.rfm_table`
)
SELECT
  customer_id,
  recency_z,
  frequency_z,
  monetary_z
FROM
  standardized_rfm;

4. K-means Clustering

To improve segmentation efficiency, we can use machine learning to help us identify customer groups. K-means clustering is an unsupervised learning algorithm that is particularly useful when dealing with datasets that do not have predefined labels. The K-means algorithm identifies fraud, detects cyberattacks, helps with medical image analysis, and optimizes delivery routes. K-means is also a popular method for customer segmentation. In k-means clustering, data is divided into a set number of clusters (k). The value of k is chosen beforehand, often randomly. For example, if we choose four clusters, the algorithm places four initial points (centroids) in a multidimensional space where each variable is a dimension. Each data point is assigned to the nearest centroid, forming four clusters. After all the points are assigned to a cluster, the algorithm calculates the average of all the points in each cluster to find a new center (or centroid) for that cluster. This new centroid now represents the center of that cluster. The data points are then reassigned based on their proximity to the new centroids, and the process repeats. This process repeats until the centroids no longer move, resulting in the final clusters. The simplified method is illustrated below.

View the interactive version of these graphs: K-means steps.