The secret to finding the real Google Analytics 4 Conversion Paths in BigQuery is to use the last non-funnel touch approach.
In GA4, the ‘first_visit’ event represents the user’s first interaction with your website.
The ‘first_visit’ event is automatically triggered the first time a user interacts with your website.
Extracting the ‘page_location’ (which corresponds to the URL of the page the user landed on) from this event provides the first meaningful touchpoint, aligning with GA4’s “First Touch” attribution logic.
Extracting the ‘page_location’ from the key event (aka conversion event) identifies the “Last Touch” point leading to the conversion.
But if ‘page_location’ is one of the funnel pages (like shopping cart, order confirmation page, etc), then it is not really the last touch point leading to conversion.
Funnel pages are typically part of the checkout or post-decision flow, and earlier interactions often determine the user’s intent to convert. Therefore, these pages may not represent the “last meaningful touchpoint.”
In that case, you will need to retrieve the last non-funnel ‘page_location’ that leads to conversion.
Using the last non-funnel touch approach to correctly determine the last meaningful touchpoint in GA4 BigQuery.
Step-1: Maintain a list of funnel pages (e.g., /cart, /checkout, /order-confirmation).
Defining and maintaining a list of funnel pages is essential to identify and exclude them from being considered as meaningful touchpoints.
Step-2: Query the events for the same ‘user_pseudo_id’ leading up to the conversion event. Order them by ‘event_timestamp’.
The ‘user_pseudo_id’ uniquely identifies a user, and ordering events by ‘event_timestamp’ ensures that you can reconstruct the sequence of interactions.
Step-3: Skip events where ‘page_location’ is in the funnel page list.
Filtering out funnel pages prevents them from being incorrectly classified as the last meaningful touchpoint.
Step-4: Extract the most recent non-funnel ‘page_location’ before the conversion event.
By excluding funnel pages and looking at prior interactions, this approach accurately identifies the last meaningful page (‘page_location’) before the conversion.
Use the following SQL (automatically generated via a text prompt in ChatGPT) to implement these steps in BigQuery: