The integration of Google Analytics 4 and Big Query to understand customer segmentation with RFM

Working with an E-commerce selling supplements that launched in 2019 the growth in the category driven by e-commerce has accelerated post 2020. Launching the new e-commerce website for Jan 2021, it meant investing in a better infrastructure around analytics and one of the areas of focus was getting more out of Google Analytics 4. They had a good understanding of their core metrics but did not have an advanced set-up to get more sophisticated with analytics. With Google Analytics 4 and the integration with Big Query it provided the perfect opportunity to generate richer insights.    

The insights they were missing was a deeper understanding of customer purchase behaviour and customer segmentation. Google Analytics 4 provided insights on purchase behaviour that was focused on the purchase journey from visiting the website to making a purchase that provided good insights, but they wanted more.

(Image from Google Merch Shop GA4 demo account)

Some of the key questions they wanted to be able to answer:

• Who are my most loyal customers.
• Which of my customers are most likely to churn.
• Who are the customers that cannot be lost.
• Which customers are likely to be loyal.

What the E-commerce business required was RFM to understand customer segmentation.   

This is where Google Analytics 4 event model and the integration with Big Query was invaluable being able to understand customer purchase behavior. The event_name = purchase (this is triggered by a tag built in Google Tag Manager) is what the code is built around. The core components of building the query:

• user_id – is a unique ID generated when a user logs-in to the website. The user_id will be recognised when the user logs into the site again from any device or browser.
• user_pesudo_id – is generated when the user first visits the website. The user_pseudo_id will be recognized when the user visits the site again from the same device and browser.
• event_date – the date on which the event was logged.
• transaction_id – the unique order ID of the customer’s purchase
• purchase_revenue – the revenue generated for that purchase associated to the ecommerce.transaction_id

The ID variable becomes the key data that RFM is built around.

Building the Query

To understand customer segmentation, it’s built around RFM:

• Recency: how frequently a customer made a purchase
• Frequency: how often a customer made a purchase
• Monetary: how much has a customer spent

To be able to understand RFM, constructed a SQL query that employs a Common Table Expression (CTE) that provides a modular approach but also readability and efficiency.  

In total there were 4 CTE’s used. Could have constructed less but in terms of workflow to be able to understand the code it became about efficiency.

First CTE

• Lower Total Customer Count (De-duplication)
  • user_pseudo_id only: If “John” buys on his laptop on Monday and his phone on Tuesday, GA4 generates two separate pseudo-IDs. He gets counted as two distinct customers, both appearing as one-time buyers (frequency = 1).
  • COALESCE(…): Both orders link to his single account user_id. He gets counted as one customer with frequency = 2.
• Accurate Recency & Revenue
  • Under user_pseudo_id, his phone profile might show recency = 2 days and his laptop profile recency = 30 days.
  • Under COALESCE(…), his true recency evaluates to 2 days across all his combined spend, accurately capturing his total customer value.
• Higher Shift into Repeat-Buyer Segments
  • De-duplicating users elevates customers out of single-purchase buckets (recent customers, lost) and moves them into repeat-buyer segments (potential loyalist, loyal customers, or champions).

WITH base_purchases AS (
SELECT
COALESCE(user_id, user_pseudo_id) AS customer_id,
ecommerce.transaction_id,
ecommerce.purchase_revenue,
TIMESTAMP_MICROS(event_timestamp) AS event_timestamp
FROM
`enter.tablename_123456.events_*`
WHERE
_TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH))
AND event_name = 'purchase'
AND ecommerce.transaction_id IS NOT NULL
),

-- Aggregate purchase metrics per user and fetch global max date in a single pass
rfm_data AS (
SELECT
customer_id AS user_id,
SUM(purchase_revenue) AS total_revenue,
ROUND(AVG(purchase_revenue), 2) AS average_revenue,
COUNT(DISTINCT transaction_id) AS frequency,
MAX(event_timestamp) AS most_recent_purchase,
MAX(MAX(event_timestamp)) OVER() AS max_order_date
FROM
base_purchases
GROUP BY
customer_id
),

Second CTE

In the second CTE (rfm_segments), customer segmentation rankings (1 to 5) are generated for Recency, Frequency, and Monetary metrics:

• Frequency Bucket Fix (CASE Statement): Instead of using NTILE(5)—which arbitrarily splits the large volume of single-purchase customers into different buckets—discrete CASE rules ensure all 1-time buyers receive a score of 1, maintaining scoring integrity across identical buyer behaviors.
• Recency & Monetary Rankings (NTILE Function): NTILE(5) ranks recency (1 = least recent, 5 = most recent) and monetary value (1 = lowest spend, 5 = highest spend) into equal 20% quintiles.
• Clean Window Sorting: The OVER(ORDER BY …) functions operate directly on customer-level aggregations without unnecessary secondary sorting parameters.

-- Calculate recency in days
rfm_calculated AS (
SELECT
user_id,
total_revenue,
frequency,
average_revenue AS monetary,
DATE_DIFF(DATE(max_order_date), DATE(most_recent_purchase), DAY) AS recency
FROM
rfm_data
),

-- Assign scores 1-5 (NTILE for Recency/Monetary, discrete rules for Frequency)
rfm_segments AS (
SELECT
user_id,
total_revenue,
recency,
frequency,
monetary,
-- Recency: Lower days ago = higher score
NTILE(5) OVER (ORDER BY recency DESC) AS rfm_recency,

 -- Frequency: Discrete logic ensures identical 1-time buyers don't get randomly split
CASE
WHEN frequency = 1 THEN 1
WHEN frequency = 2 THEN 2
WHEN frequency = 3 THEN 3
WHEN frequency = 4 THEN 4
ELSE 5
END AS rfm_frequency,

-- Monetary: Higher average spending = higher score
NTILE(5) OVER (ORDER BY monetary ASC) AS rfm_monetary
FROM
rfm_calculated
),

Third CTE

In the third CTE (rfm_strings), the individual numeric scores from the previous step are concatenated into a single 3-character string (e.g., ‘555’, ‘511’).

• String Conversion: Because the calculated scores (rfm_recency, rfm_frequency, and rfm_monetary) are generated as integers, SQL’s CAST(… AS STRING) converts them prior to concatenation.
• Concatenation: BigQuery’s CONCAT() joins the three individual string values into a single rfm_string column.
• Scoring Context: A score of 5 across all metrics indicates a top-tier customer (recent purchase, high frequency, and high monetary spend), whereas 1 across all metrics indicates a long-lapsed, low-value, single-purchase customer.

-- Concatenate individual RFM scores into 3-character string
rfm_strings AS (
SELECT
*,
CONCAT(
CAST(rfm_recency AS STRING),
CAST(rfm_frequency AS STRING),
CAST(rfm_monetary AS STRING)
) AS rfm_string
FROM
rfm_segments
),

Fourth CTE

In the fourth and final CTE (rfm_mapping), human-readable segment names (such as champions, loyal customers, or at risk) are mapped directly to the concatenated 3-digit RFM strings using a CASE statement.

• Lookup Logic: The CASE statement checks the concatenated rfm_string against pre-defined combinations of Recency, Frequency, and Monetary scores.
• Exact Matching: For example, string ‘555’ matches a customer with top scores across all three dimensions, assigning them to the champions segment.
• Handling Unmapped Combinations: An ELSE ‘NA’ clause serves as a catch-all for any unmapped or unexpected score combinations.

rfm_mapping AS (
SELECT
*,
CASE
WHEN
rfm_string IN ('555', '554', '544', '545', '454', '455', '445')
THEN 'champions'
WHEN
rfm_string IN ('543', '444', '435', '355', '354', '345', '344', '335')
THEN 'loyal customers'
WHEN
rfm_string IN ('553', '551', '552', '541', '542', '533', '532', '531', '452', '451', '442', '441', '431', '453', '433', '432', '423', '353', '352', '351', '342', '341', '333', '323')
THEN 'potential loyalist'
WHEN
rfm_string IN ('512', '511', '422', '421', '412', '411', '311')
THEN 'recent customers'
WHEN
rfm_string IN ('525', '524', '523', '522', '521', '515', '514', '513', '425', '424', '413', '414', '415', '315', '314', '313')
THEN 'promising'
WHEN
rfm_string IN ('535', '534', '443', '434', '343', '334', '325', '324')
THEN 'customers needing attention'
WHEN
rfm_string IN ('331', '321', '312', '221', '213')
THEN 'about to sleep'
WHEN
rfm_string IN ('255', '254', '245', '244', '253', '252', '243', '242', '235', '234', '225', '224', '153', '152', '145', '143', '142', '135', '134', '133', '125', '124')
THEN 'at risk'
WHEN
rfm_string IN ('155', '154', '144', '214', '215', '115', '114', '113')
THEN 'cannot lose them'
WHEN
rfm_string IN ('332', '322', '231', '241', '251', '233', '232', '223', '222', '132', '123', '122', '212', '211')
THEN 'hibernating'
WHEN
rfm_string IN ('111', '112', '121', '131', '141', '151')
THEN 'lost'
ELSE 'NA'
END AS rfm_segment
FROM rfm_strings
)

Final Code

The final code shows:

• Customer ID: The unique user identifier stitched using COALESCE(user_id, user_pseudo_id).
• Total Revenue: The total revenue generated by the customer across all purchases.
• Total Transactions (Frequency): The total number of distinct purchase transactions completed by the customer.
• Recency: The number of days elapsed between the customer’s most recent purchase and the dataset’s overall latest order date.
• Monetary Value: The average revenue generated per transaction for that customer.
• Individual RFM Scores: The 1 to 5 numeric rankings for rfm_recency, rfm_frequency, and rfm_monetary.
• RFM Score (Combined): The concatenated 3-digit score string (e.g., ‘555’, ‘511’).
• Segment: The assigned customer segment label derived from the lookup table mapping.

SELECT
user_id AS customer_id,
total_revenue,
frequency AS total_transactions,
recency,
monetary,
rfm_recency,
rfm_frequency,
rfm_monetary,
rfm_string AS rfm_score,
rfm_segment AS segment
FROM
rfm_mapping
ORDER BY
rfm_score DESC;

Alternative Implementation (Option B)

While the query above (Option A) uses fixed integer scores and a hardcoded lookup table, there is another production-ready approach.

Instead of categorical lookup strings, Option B uses continuous mathematical percentiles (PERCENT_RANK()) combined with flexible score thresholds.

Why Use Option B?

• Handles Skewed Distributions: Adapts dynamically to e-commerce stores with high one-time buyer rates or non-standard purchasing behaviors (e.g., subscription models or luxury goods).
• Eliminates Unmapped Strings: Prevents edge-case score combinations from resulting in NULL or ‘NA’ values.
• Automated Maintenance: Segments scale automatically as your customer transaction volume grows.

Modifying the query for the business model  

The query is built are for an e-commerce business model, but any business would want to better understand customer segmentation through the lens of recency, frequency and monetary (RFM). The context of the insights will change depending on the business model. (link to GitHub to download SQL code)

• For publishers where the goal may be to get users to access the app and read content, RFM may be focused on an event related to content engagement so within the query the purchase event would need to be replaced with the content_engaged
  • It will provide insights; the recency and the frequency of the app is being used which will relate to a monetary value of the content engaged with.
• For a specific business model, it may require the user to log-in the user_pseudo_id can be replaced with user_id.

Visualisation

Having a rich data set is the first step, then it’s all about visualisations to provide insights to those burning questions. There are many ways to build out visualization using Big Query, there is a connector to Looker Studio (Data Studio) or Python or Tableau all depending on the ecosystem in place.  

(Data in the charts has been modified for this article)

This chart looks at the % revenue generated by the different segments.

This chart looks at the % of customers in the different segments.

Looking ahead

To Provide Richer Insights & Bring RFM to Life.

Once an RFM foundation is established, brands can push their analysis further by evaluating:

• Discount Code Dependency: Are certain segments (like At Risk or Promising) only purchasing when heavy discount codes are applied?
• Gross Profit per Customer: Moving beyond top-line revenue to evaluate true profitability per segment by factoring in product margins and fulfillment costs.
• Tailored Messaging & Activation: Differentiating marketing campaigns between heavy bargain hunters and full-price VIP Champions.

Enhancing the Data Quality & Infrastructure.

Understanding customer segmentation is a critical piece of the data puzzle for brands. Unlocking its full potential requires:

• Server-Side Tracking (sGTM): Implementing a server-side Google Tag Manager setup alongside GA4 to bypass ad blockers, extend cookie lifespans, and ensure near-100% purchase capture.
• A Robust GA4 & BigQuery Integration: Clean, well-structured event schema connected to BigQuery with long-horizon historical data.
• Strategic Alignment: Most importantly, a clear business strategy with the right questions driving how these segment insights are activated across email, paid media, and retention channels.

This provides a forward-looking conclusion that transitions seamlessly from your technical BigQuery code into strategic, real-world marketing value!

Worked on this project with Jeffrey Ohene who built the SQL code in Big Query.