A simple RFM analysis and Customer segmentation with BigQuery

RFM analysis, which stands for Recency, Frequency, and Monetary analysis, is a popular method offering a data-driven approach that to understanding customer behaviour and tailoring marketing efforts for optimal impact. In this article, we will dive into the concept of RFM analysis, its significance, and provide a simple example using BigQuery.

So what is RFM ?

RFM analysis is a technique that classifies customers based on three key dimensions:

1. Recency (R): Recency measures how recently a customer made a purchase. Customers who have made recent purchases are often more responsive to marketing efforts.
2. Frequency (F): Frequency measures how often a customer makes purchases. High-frequency customers tend to be loyal and valuable to the business.
3. Monetary Value (M): Monetary value represents the amount of money a customer has spent. Customers who spend more are typically more profitable and may warrant tailored marketing strategies.

The power of RFM analysis

RFM offers several benefits to the business, such as:

1. Precise Segmentation: RFM Analysis classifiers customers based on their purchasing behaviour. It allows business to adapt their marketing strategy according the segment of customer.
2. Customer Understanding: RFM provides a clear insight into customers, thus help business to understand their demands, and to identify most engaged and loyal customers.
3. Retention improvement: RFM analysis help business to identify at-risk customers, who are potentially leaving their business. So business can launch some retention campaign in order to retain their customers.

RFM Analysis using BigQuery / SQL

In this article, we will go through an exemple of RFM Analysis with a simple BigQuery query (the same idea can be applied in different query).

First we need to retrieve the sale data from sale table.

WITH sale_data AS (
 SELECT
  order_date,
  order_id, 
  customer_id,
  revenue,
 FROM SALE_TABLLE
),

Next, we calculate the last purchase date (recency), the total number of purchase (frequency), and the total revenue (monetary).

user_data AS (
  SELECT
    customer_id,
    max(order_date) AS recency,
    count(order_id) AS total_orders,
    sum(revenue) AS total_revenue,
  FROM sale
  GROUP BY 1
),

Next we segment customer to group by using NTILE function in BigQuery

SELECT
 customer_id,
 recency,
 total_orders,
 total_revenue,
 NTILE(5) OVER (ORDER BY recency DESC) AS rfm_recency_segment,
 NTILE(5) OVER (ORDER BY total_orders DESC) AS rfm_frequency_segment,
 NTILE(5) OVER (ORDER BY total_revenue DESC) AS rfm_monetary_segment,
FROM user_data

This query will assign each customer to a group from 1 to 5 to each category Recency, Frequency and Monetary. The higher group’s number, the better customer engages into the business.

Depending on the objective and business model , we can combine these segmentation number representing each group to provide a global RFM score for each customer. For exemple, a business attributes their priority 40%-30%-30% to Recency-Frequency-Monetary, then a customer with score for each category 3,4,5 respectively will have the final score of 3.9.

Conclusion

RFM analysis is more than just categorizing customers; it’s about uncovering insights that lead to targeted marketing strategies. By showcasing an RFM analysis example using BigQuery, we’ve demonstrated how this technique can revolutionize customer segmentation and engagement.

For more detail on RFM analysis and deeper customer analysis, feel free to contact us at Hanalytics. We have many experience working with marketing data and client segmentation for different business. We’re delighted to assist you in gaining deeper data-driven insights, ultimately bolstering decision-making and expanding your business reach.