The integration of Google Analytics 4 and Big Query to understand time lag from first visit to first purchase

Working with a business providing an experience in wellbeing and health, they had a re-brand and re-launched a new website and experience. Previously the insights they got from Google Analytics 3 were useful, it was unable to answer key business and marketing questions. With a new e-commerce website, it was the perfect time to go live with Google Analytics 4 and the integration with Big Query is a game changer.

One of the questions the brand struggled to answer is to understand time lag when users first visit the site to make their first purchase. Google Analytics 3 provided a time lag report which was useful at best, but it did not provide any actionable insights or great visualisations.

(Image from Google Merch Shop GA3 demo account)

Some of the key questions the brand wanted to answer:

• Which channels are driving those first visits that result in a first purchase.
• From the first visit, how long does it take to make a first purchase.
• How do seasonality or campaign periods impact first visit to first purchase.
• What is the contribution of revenue i.e., for February 2023 first visit / first purchases v total revenue.

This is where Google Analytics 4 event model and the integration with Big Query was invaluable being able to answer the question of time lag from first visit to purchase. The core components of building the query were based around:

• 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
• first_visit – is an event that is generated by Google Analytics 4 when someone visits the website for the first time
• purchase – is an event that is generated when someone buys a product, and this is triggered by a tag built in Google Tag Manager.

This would generate an output with the column called days_to_purchase which is the key column in calculating how many days it took for a user to make their first purchase after their first visit.

To enrich the data to provide greater context also added:

• traffic_source.source – name of the traffic source i.e., facebook
• traffic_source.medium – name of the medium i.e., paidsocial
• traffic_source.name – name of the campaign i.e., summer2024
• ecommerce.purchase_revenue – the revenue generated for that purchase

The caveat was understood that that the first visit may not necessarily be the users first touch point with the brand. The first purchase may not necessarily be the users first purchase. With the re-launch of the website and experience and new analytics the insights generated would provide more of an accurate view. 

Building the Query

To measure the time lag from a user’s initial site visit to their first purchase, we constructed a CTE-based SQL query (first_visits and first_purchases). By isolating each customer’s true first visit alongside their first transaction, we calculate exact latency in both days and hours while preserving initial acquisition channel attribution.

Key Features of the Query

• Cross-Device Stitching: Unifies visitor identity via COALESCE(user_id, user_pseudo_id) to accurately track users returning across different browsers or devices.
• First-Touch Integrity: Captures the acquisition source (source, medium, campaign) from the customer’s first visit, ensuring marketing attribution isn’t lost if they convert during a direct or secondary campaign session.
• Dual Time-Lag Granularity: Evaluates both days_to_purchase (DATE_DIFF) and hours_to_purchase (TIMESTAMP_DIFF) to provide high-resolution insights into same-day and multi-day conversions.
• Performance & Hygiene: Employs _TABLE_SUFFIX partition pruning to minimize BigQuery byte scans and filters for valid transaction IDs.

Breakdown of the Code 

Lines 3 & 24: User Stitching (COALESCE): Stitches authenticated user IDs with anonymous client IDs (COALESCE(user_id, user_pseudo_id)), ensuring cross-device journey continuity.

COALESCE(user_id, user_pseudo_id) AS customer_id,
TIMESTAMP_MICROS(event_timestamp) AS first_visit_timestamp,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS first_visit_date,
traffic_source.source AS first_visit_source,
traffic_source.medium AS first_visit_medium,
traffic_source.name AS first_visit_campaign,

-- Pick the earliest first_visit per customer
ROW_NUMBER() OVER (
PARTITION BY COALESCE(user_id, user_pseudo_id) 
ORDER BY event_timestamp ASC
) AS visit_rn
FROM
`enter.tablename_123456.events_*`
WHERE
_TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH))
AND event_name = 'first_visit'
),

Lines 11–14 & 31–34: Chronological Event Ranking: Applies ROW_NUMBER() OVER (PARTITION BY COALESCE(…) ORDER BY event_timestamp ASC) independently across first_visit and purchase events to identify each user’s exact initial touchpoint and initial order.

ROW_NUMBER() OVER (
PARTITION BY COALESCE(user_id, user_pseudo_id) 
ORDER BY event_timestamp ASC
) AS visit_rn

.........................................................................

ROW_NUMBER() OVER (
PARTITION BY COALESCE(user_id, user_pseudo_id) 
ORDER BY event_timestamp ASC
) AS purchase_rn

Lines 18–19 & 38–40: Partition Pruning & Hygiene Filter: Uses _TABLE_SUFFIX >= FORMAT_DATE(…) to restrict BigQuery scans to the last 24 months, and excludes null or broken transaction IDs (ecommerce.transaction_id IS NOT NULL).

 _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH))
AND event_name = 'first_visit'

........................................................................

_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

Lines 54–55: Intra-Day Latency Calculation: Computes DATE_DIFF(p.purchase_date, v.first_visit_date, DAY) alongside TIMESTAMP_DIFF(p.purchase_timestamp, v.first_visit_timestamp, HOUR) to differentiate same-day micro-conversions from longer multi-day consideration cycles.

DATE_DIFF(p.purchase_date, v.first_visit_date, DAY) AS days_to_purchase,
TIMESTAMP_DIFF(p.purchase_timestamp, v.first_visit_timestamp, HOUR) AS hours_to_purchase

Lines 60–64: Unbiased Identity Join & Safeguards: Joins exclusively on v.customer_id = p.customer_id and enforces p.purchase_timestamp >= v.first_visit_timestamp so conversions are never dropped if traffic source tags change between the first visit and first purchase.

 ON v.customer_id = p.customer_id
WHERE
v.visit_rn = 1
AND p.purchase_rn = 1
AND p.purchase_timestamp >= v.first_visit_timestamp

Modifying the query for the business model

The query built is for an e-commerce business model, any business model would want to understand the time lag from a user first visiting the website to their first “conversion”. With the way the query is built it can be modified depending on the use case. (link to GitHub to download SQL code)

• For lead generation it may be getting a user to complete a registration form so within the query the purchase event would need to be replaced with the registration
• 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 visualisation 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 illustrates weekly revenue generated specifically from customers converting after their initial visit, highlighting clear sales velocity peaks during key seasonal campaign windows (such as Valentine’s Day and Mother’s Day).

This treemap breaks down the proportion of first-visit revenue driven by specific acquisition campaigns, identifying which channels successfully attract immediate buyers.

Looking ahead

Being able to analyse customer purchasing velocity and understand how users buy is a critical piece of the data puzzle for modern e-commerce brands. Achieving this level of clarity requires high-quality historical data, a sophisticated Google Tag Manager and Google Analytics 4 implementation, and a well-engineered event pipeline flowing into BigQuery. Most importantly, it demands a clear overarching strategy centered on asking the right business questions to turn raw event logs into actionable growth strategies.

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