Decoding Customer Loyalty: Analyzing the Gap Between First and Second Purchases

/ Bigquery/GA4

One of the most valuable insights a marketing team can uncover is the speed at which a first-time buyer becomes a repeat customer. This gap—the time between the first and second purchase—is a critical metric for understanding product stickiness, customer satisfaction, and the effectiveness of your post-purchase engagement strategies.

By leveraging Google Analytics 4 (GA4) data exported to a powerful data warehouse like BigQuery, we can use simple SQL to calculate this crucial time difference and unlock actionable intelligence.

📊 The Power of the Metric: Why the Gap Matters

The time-to-second-purchase metric is often a strong indicator of customer loyalty and lifetime value (LTV) potential.

  • For Marketing Teams: Understanding the typical gap allows you to optimize your retargeting campaigns. If customers typically repurchase around the 45-day mark, you can schedule your “second purchase incentive” email sequence to begin around day 30.
  • For Product Teams: A short gap suggests high product satisfaction and utility. A long or non-existent gap might signal an issue with the product itself or the post-purchase experience (e.g., shipping, onboarding).
  • For Finance/Strategy: A high volume of customers with a short gap points to a healthy, sustainable business model driven by strong customer retention.

💻 The SQL Query Explained

The following BigQuery SQL query, adapted for a generic GA4 User dataset (here named your_ga4_dataset.users_*), efficiently calculates the time difference in days.

In this query I’ve used GA4 user data populated through user_id, we can also do this using user_pseudo_id(device_id) as well. I’ll create a new article using user_pseudo_id as well, stay tuned for that.

screenshot 2025 11 10 at 21.01.37

This query two Common Table Expressions (CTEs, or temporary named result sets) to isolate the dates of the first and second purchases for the same group of users.

SQL

WITH first_purchase_users AS ( 
  -- CTE to find the date of the FIRST purchase for each user
  SELECT 
    DISTINCT user_id,
    MIN(PARSE_DATE('%Y%m%d', occurrence_date)) AS first_purchase_date
  FROM 
    `your_ga4_dataset.users_*` -- Replace with your actual BigQuery table path
  WHERE 
    user_ltv.purchases = 1 
  GROUP BY 1
),

second_purchase_users AS ( 
  -- CTE to find the date of the SECOND purchase for each user
  SELECT 
    DISTINCT user_id,
    MIN(PARSE_DATE('%Y%m%d', occurrence_date)) AS second_purchase_date
  FROM 
    `your_ga4_dataset.users_*` -- Replace with your actual BigQuery table path
  WHERE 
    user_ltv.purchases = 2 
  GROUP BY 1
)

SELECT 
  a.user_id,
  a.first_purchase_date,
  b.second_purchase_date,
  ABS(DATE_DIFF(b.second_purchase_date, a.first_purchase_date, DAY)) AS purchase_difference 
FROM 
  first_purchase_users a 
INNER JOIN 
  second_purchase_users b 
ON 
  a.user_id = b.user_id 
-- The INNER JOIN ensures we only look at users who have completed BOTH a first AND a second purchase.

Key Steps in the Query:

  1. first_purchase_users (prep1): Selects the minimum purchase date (occurrence_date) for all users where the LTV purchase count is 1. This reliably captures their very first transaction date.
  2. second_purchase_users (prep2): Similarly, selects the minimum purchase date for all users where the LTV purchase count is 2. This captures the date of their second transaction.
  3. Final SELECT: An INNER JOIN merges these two lists only on matching user_ids.
  4. DATE_DIFF: The core calculation uses the DATE_DIFF function to subtract the first_purchase_date from the second_purchase_date and returns the result in DAY units. The ABS() function is used for safety to ensure a positive day count, though with the current logic, the second purchase date should always be later.

🚀 Taking Actionable Steps

Once you run this query, you’ll have a dataset of users and the days between their first and second purchase. What do you do next?

  1. Calculate the Average: Find the mean and median purchase difference. The median is often a better representation, as the mean can be skewed by a few long-gap outliers.
  2. Segment by Channel: Group the results by the user’s acquisition channel (e.g., Paid Search, Social, Organic). Does one channel produce faster second-time buyers than others? This indicates where your most loyal customers come from.
  3. Optimize Communication: Use the median gap to schedule your retention marketing efforts. If the median is 35 days, launch a personalized promotion at 25–30 days to encourage a repeat purchase before the typical timeframe.
  4. A/B Test Incentives: Test different types of incentives (e.g., free shipping, $10 off, gift with purchase) at the critical time-to-second-purchase window to see which drives the highest conversion rate.

By transforming raw GA4 user data into this critical metric, you’re not just reporting on past behavior—you’re predicting and influencing future customer loyalty. Happy analyzing!