F**k It - Here are 12 Copy & Paste Impactful SQL Queries for Querying Data from the GA4 to BQ Export
Richard Harris Jr.
VP, Head of Data & AI | Advanced Analytics, AI/ML, Cloud, Data Science, & Engineering | Business Insider's 50 Rising Stars
NO MORE GATEKEEPING FOR ME. This is for those folks who have the GA4 to BQ link already established, and you have data flowing in. If you have explored the BQ exports and found out that your data looks like a chaotic mess, you are not alone. Honestly, if you don’t understand why the connection from GA4 to BQ is important for skirting those pesky quota limits or giving you the ability to work with raw data before GA4 modeling, then this ain’t the post for you. Let's say you’re in the position that you already know that this is important, but need some help querying some of the most important slices of data, stick around or scroll down; I've got your back. What I’m going to do is, help you slice through the mess of the BQ export and start writing some queries that you can immediately start taking action on. So… let's just get right into it.
Some Quick Notes
First of all, all of my queries below would be getting data for all date ranges, so you can just schedule that query and leverage your fave visualization platform to visualize your data as you see fit. I do understand why you would want to look for specific date ranges, so I’ll start there and continue down the path of getting the data you need, or should be considering when evaluating performance.
Secondly, Channel groupings are more complex in the GA4 to BigQuery export, so I’ll give a quick overview on how to get this right for channel categorization on the session level.
#0 How do I query all of my GA4 data in BigQuery
This doesnt count as one of the 12, so consider it a bonus.
Look, we all know that querying your data one day at a time is about as efficient as watering your grass with a teaspoon. This query cuts through that tedious crap, grabbing your entire GA4 dataset in one smooth swoop. No more jumping through hoops or manually stitching data together—because life's too short to deal with fragmented bullshit. Here is just the foundational query to write to get all of your data that is passing through to BQ.
SELECT * FROM `{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
While this query just returns all of the data for all dates that are in the BQ export you’re likely going to need to know how to segment this data by date.
#1 How do I segment the GA4 data in BigQuery to a specific date or date range
Ever tried sifting through a mountain of irrelevant data looking for a single week of data? Yeah, screw that. The following queries help you with slicing and dicing your GA4 data by exact dates or date ranges, giving you the precision to find exactly what matters—no more drowning in a sea of useless numbers, lets make date ranges your new best friend.
The dimensions that I’m using for this one is just date and total sessions.
Query all data with date and total sessions for the last week:
SELECT
DATE AS Date,
COUNT(DISTINCT session_id) AS Total_Sessions
FROM
(
SELECT
event_date AS DATE,
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'ga_session_id'
)
) AS session_id
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
/* Replace this portion with the specific date range that you want */
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE(
'%Y%m%d',
DATE_TRUNC(
DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK),
WEEK(MONDAY)
)
) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(
DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)),
INTERVAL 1 DAY
)
)
/* End of portion to replace with a specific date range that you want */
GROUP BY
DATE,
session_id
)
GROUP BY
DATE
Don't want to query by last week? Try Last Month by replacing the query in between my comments above.
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE(
'%Y%m%d',
DATE_SUB(
DATE_TRUNC(CURRENT_DATE(), MONTH),
INTERVAL 1 MONTH
)
) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
)
How about for Month to date, (or yesterday if you are using daily exports)
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), MONTH)) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
How about for the last X amount of days. Heres an example for the last 14 days
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE(
'%Y%m%d',
DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
What about when I get a request for year to date. Easy enough, here you go:
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), YEAR)) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
Okay, but now I want to see what this looked like for the whole of last year. No problem
WHERE
TABLESUFFIX BETWEEN FORMAT_DATE(
'%Y%m%d',
DATE_SUB(DATE_TRUNC(CURRENT_DATE(), YEAR), INTERVAL 1 YEAR)
) AND FORMAT_DATE(
'%Y%m%d',
DATE_SUB(DATE_TRUNC(CURRENT_DATE(), YEAR), INTERVAL 1 DAY)
)
Last, if you want a custom date range, just modify this dates below formatted as YYYMMDD
WHERE
TABLESUFFIX BETWEEN '20250122' AND '20250227'
The importance of this includes, but is obviously not limited to:
Some immediate use case for querying all of your data is:
Stop guessing about your data trends, grab this query, choose your dates, and start handling your analytics like a boss. At this point, I’m wondering if that counts as 6 queries or just 1. Don’t worry, I’m not going to cheat you, I’ll count that all as my second one.
#2 How about we get those pesky channel groupings added to our tables
Let’s face it, without channel groupings, your traffic sources look like a chaotic Jackson Pollock piece of abstract art. This query clears that mess right up, neatly labeling every channel so you instantly know where quality visits come from and what channels are just blowing smoke. Say goodbye to guessing games and hello to clarity.
The following query is just for channel categorization into default channel groupings. I’ll be pulling the session channel along with session medium, source, and campaign. You will likely need to customize this based on your naming conventions in utms, but this is a great start.
SELECT
CASE
WHEN session_source IS NULL THEN 'Direct'
WHEN REGEXP_CONTAINS(session_campaign_name, 'cross-network') THEN 'Cross-network'
WHEN (
REGEXP_CONTAINS(
session_source,
'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart'
)
OR REGEXP_CONTAINS(
session_campaign_name,
'^(.*(([^a-df-z]|^)shop|shopping).*)$'
)
)
AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Shopping'
WHEN REGEXP_CONTAINS(
session_source,
'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex'
)
AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Search'
WHEN REGEXP_CONTAINS(
session_source,
'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp'
)
AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Social'
WHEN REGEXP_CONTAINS(
session_source,
'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube'
)
AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Video'
WHEN session_medium IN (
'display',
'banner',
'expandable',
'interstitial',
'cpm'
) THEN 'Display'
WHEN REGEXP_CONTAINS(
session_source,
'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart'
)
OR REGEXP_CONTAINS(
session_campaign_name,
'^(.*(([^a-df-z]|^)shop|shopping).*)$'
) THEN 'Organic Shopping'
WHEN REGEXP_CONTAINS(
session_source,
'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp'
)
OR session_medium IN (
'social',
'social-network',
'social-media',
'sm',
'social network',
'social media'
) THEN 'Organic Social'
WHEN REGEXP_CONTAINS(
session_source,
'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube'
)
OR REGEXP_CONTAINS(session_medium, '^(.*video.*)$') THEN 'Organic Video'
WHEN REGEXP_CONTAINS(
session_source,
'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex'
)
OR session_medium = 'organic' THEN 'Organic Search'
WHEN REGEXP_CONTAINS(session_source, 'email|e-mail|e_mail|e mail')
OR REGEXP_CONTAINS(session_medium, 'email|e-mail|e_mail|e mail') THEN 'Email'
WHEN session_medium = 'affiliate' THEN 'Affiliates'
WHEN session_medium = 'referral' THEN 'Referral'
WHEN session_medium = 'audio' THEN 'Audio'
WHEN session_medium = 'sms' THEN 'SMS'
WHEN session_medium LIKE '%push'
OR REGEXP_CONTAINS(session_medium, 'mobile|notification') THEN 'Mobile Push Notifications'
ELSE 'Unassigned'
END AS Session_Channel,
COALESCE(session_medium, '(none)') AS Session_Medium,
COALESCE(session_source, '(direct)') AS Session_Source,
COALESCE(session_campaign_name, '(not set)') AS Session_Campaign_Name,
COUNT(DISTINCT session_id) AS Total_Sessions
FROM
(
SELECT
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'ga_session_id'
)
) AS session_id,
ARRAY_AGG(
(
CASE
WHEN collected_traffic_source.gclid IS NOT NULL THEN 'google'
ELSE collected_traffic_source.manual_source
END
) IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS session_source,
ARRAY_AGG(
(
CASE
WHEN collected_traffic_source.gclid IS NOT NULL THEN 'cpc'
ELSE collected_traffic_source.manual_medium
END
) IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS session_medium,
ARRAY_AGG(
collected_traffic_source.manual_campaign_name IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS session_campaign_name
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
session_id
)
GROUP BY
session_channel,
session_medium,
session_source,
session_campaign_name
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Quit the channel confusion and start clearly defining what's making your business money/driving conversions and what's just burning cash and wasting our time. I don’t have a good transition here, so I’ll just keep it moving on.
#3 How do I calculate average session duration from the GA4 to BQ export
Average session duration is like measuring how long your guests stick around at your party before they bail. Too short? Your content might suck. Too long? Congrats, you're hosting the digital equivalent of an open bar. This query helps you quickly size up your site's ability to hold attention, without the endless guessing game.
SELECT
DATE,
COUNT(DISTINCT session_id) AS Total_Session,
SAFE_DIVIDE(
SUM(end_time-start_time) / 1000000,
COUNT(DISTINCT session_id)
) AS Average_Session_Duration
FROM
(
SELECT
event_date AS DATE,
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'ga_session_id'
)
) AS session_id,
MIN(event_timestamp) AS start_time,
MAX(event_timestamp) AS end_time
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
DATE,
session_id
)
GROUP BY
DATE
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Stop flying blind and let this query tell you if your site is keeping visitors glued or pushing them straight to the exits.
#4 How do I get engaged sessions from the BigQuery Export
Let’s be honest, not every visitor gives a shit about your site. Some just click, glance, and dip out faster than your last a bad date. This query identifies sessions where users actually stuck around long enough to matter, did something noteworthy, or at least pretended to care. No more vanity metrics, just real, valuable engagement.
SELECT
DATE,
COUNT(DISTINCT session_id) AS Total_Sessions,
SUM(CAST(engaged_sessions AS INT)) AS Engaged_Sessions
FROM
(
SELECT
event_date AS DATE,
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'ga_session_id'
)
) AS session_id,
MAX(
(
SELECT
value.string_value
FROM
UNNEST (event_params)
WHERE
key = 'session_engaged'
)
) AS engaged_sessions
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
DATE,
session_id
)
GROUP BY
DATE
The importance of this includes, but is obviously not limited to:
Some immediate use case for querying all of your data is:
If you’re serious about growth, start focusing on engagement, because counting meaningless sessions is a waste of your time. Now that we have engaged sessions, lets get into how to bring avg engagement time and engagement rates into this data
#5 How do I get average engagement time and engagement rate and engagement time from my GA4 BigQuery export
Let’s stop pretending all visitors are equal, some stick around and actually read your content, while others vanish quicker than free donuts in a conference room. This query reveals how long your visitors actually pay attention (average engagement time), and gives you a percentage (engagement rate) to clearly separate the truly interested from the bored-to-death group. Time to finally see if your visitors are fans or just accidental visitors.
SELECT
DATE,
COUNT(DISTINCT session_id) AS sessions,
SUM(CAST(engaged_sessions AS INT)) AS engaged_sessions,
SAFE_DIVIDE(
SUM(CAST(engaged_sessions AS INT)),
COUNT(DISTINCT session_id)
) AS engagement_rate,
SAFE_DIVIDE(
SUM(engagement_time_seconds),
COUNT(DISTINCT session_id)
) AS average_engagement_time_per_session
FROM
(
SELECT
event_date AS DATE,
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'ga_session_id'
)
) AS session_id,
MAX(
(
SELECT
value.string_value
FROM
UNNEST (event_params)
WHERE
key = 'session_engaged'
)
) AS engaged_sessions,
SAFE_DIVIDE(
SUM(
(
SELECT
value.int_value
FROM
UNNEST (event_params)
WHERE
key = 'engagement_time_msec'
)
),
1000
) AS engagement_time_seconds
FROM
` {{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
DATE,
session_id
)
GROUP BY
DATE
The importance of this includes, but is obviously not limited to:
Some immediate use case for querying all of your data is:
Stop guessing if users care, this query tells you exactly how interested visitors are, so you can fix what's boring and amplify what works.
#6 How can I unnest an event and get the count of event that happened based on a filter in the GA4 BigQuery Export
GA4 events are buried deeper than your motivation on a Monday morning. Well, forget all that, this query digs straight into those nested event parameters, extracts what matters, and tells you exactly how often users actually clicked, scrolled, or converted. Time to unnest that data mess and find out what is really happening on your site.
SELECT
DATE,
event_name,
SUM(event_count) AS event_count,
SUM(event_value) AS event_value
FROM
(
SELECT
event_date AS DATE,
event_name AS event_name,
COUNT(event_name) AS event_count,
SUM(
(
SELECT
COALESCE(
value.int_value,
value.float_value,
value.double_value
)
FROM
UNNEST (event_params)
WHERE
key = 'value'
)
) AS event_value
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
DATE,
event_name
)
GROUP BY
DATE,
event_name
/* Replace this below with whatever event or events that you want to segment */
HAVING
event_name = 'form_submission'
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
When you need clear details on user behavior, this query slices straight to the point. Stop drowning in vague events and get hyper-focused on the actions that actually matter.
#7 How can I get all of my ecommerce item data such as item name, id, category, variant, brand etc. paired with item viewed in list, item clicked in list, item viewed, items added to cart, item checked out, item purchased, and item quantity from by BigQuery GA4 Export
Ecom tracking without detailed item data is like hosting a garage sale blindfolded, you’ll never know what's actually selling or what's just gathering dust. This query grabs every juicy detail about your products (from name and brand to variant to category) and pairs them with specific user actions—views, clicks, cart adds, checkouts, and purchases. Finally, you can stop guessing and start cashing in.
SELECT
item_name,
item_id,
item_brand,
item_variant,
item_category,
item_category2,
item_category3,
item_category4,
item_category5,
item_list_name,
item_list_position,
SUM(items_viewed_in_list) AS items_viewed_in_list,
SUM(items_clicked_in_list) AS items_clicked_in_list,
SUM(items_viewed) AS items_viewed,
SUM(items_added_to_cart) AS items_added_to_cart,
SUM(items_checked_out) AS items_checked_out,
SUM(items_purchased) AS items_purchased,
SUM(item_revenue) AS item_revenue,
SUM(item_quantity) AS item_quantity
FROM
(
SELECT
items.item_name AS item_name,
items.item_id AS item_id,
items.item_brand AS item_brand,
items.item_variant AS item_variant,
items.item_category AS item_category,
items.item_category2 AS item_category2,
items.item_category3 AS item_category3,
items.item_category4 AS item_category4,
items.item_category5 AS item_category5,
items.item_list_name AS item_list_name,
items.item_list_index AS item_list_position,
SUM(
CASE
WHEN event_name = 'view_item_list' THEN items.quantity
END
) AS items_viewed_in_list,
SUM(
CASE
WHEN event_name = 'select_item' THEN items.quantity
END
) AS items_clicked_in_list,
SUM(
CASE
WHEN event_name = 'view_item' THEN items.quantity
END
) AS items_viewed,
SUM(
CASE
WHEN event_name = 'add_to_cart' THEN items.quantity
END
) AS items_added_to_cart,
SUM(
CASE
WHEN event_name = 'begin_checkout' THEN items.quantity
END
) AS items_checked_out,
SUM(
CASE
WHEN event_name = 'purchase' THEN items.quantity
END
) AS items_purchased,
SUM(items.item_revenue) AS item_revenue,
SUM(items.quantity) AS item_quantity
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
UNNEST (items) AS items
GROUP BY
item_name,
item_id,
item_brand,
item_variant,
item_category,
item_category2,
item_category3,
item_category4,
item_category5,
item_list_name,
item_list_position
)
GROUP BY
item_name,
item_id,
item_brand,
item_variant,
item_category,
item_category2,
item_category3,
item_category4,
item_category5,
item_list_name,
item_list_position
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Stop settling for half-assed product insights just giving you total, this query delivers the clarity you need to sell smarter and scale faster by incorporating steps and all unnested items parameters. If you’re just looking to get high level data, check out the next query.
#8 How can I get total purchases and revenue by date from the BQ GA4 export
Revenue tracking without clear daily totals is about as useful as a calculator with dead batteries. This query hands you daily totals for purchases and revenue, neatly organized so you can quickly see when your business is thriving and when it's barely surviving. Stop squinting at confusing reports and start seeing exactly how much cash you're actually pulling in each day.
SELECT
DATE,
transaction_id,
SUM(purchases) AS purchases,
SUM(revenue) AS revenue
FROM
(
SELECT
event_date AS DATE,
ecommerce.transaction_id AS transaction_id,
SUM(
CASE
WHEN event_name = 'purchase' THEN 1
ELSE 0
END
) AS purchases,
SUM(ecommerce.purchase_revenue) AS revenue
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
GROUP BY
DATE,
transaction_id
)
GROUP BY
DATE,
transaction_id
The importance of this includes, but is not limited to:
Some immediate use case for querying all of your data is:
Stop guessing when you're making sales, use this query to keep your finger on the pulse of your profitability every single day.
#9 How can build the ecommerce checkout funnel in the BQ export of GA4 data with completion rate between steps
Wondering where your checkout funnel leaks worse than that garden hose I just got from Amazon? I got you. This query meticulously tracks each step of your ecommerce checkout funnel, clearly highlighting where customers are confidently moving forward, or abruptly ghosting your cart. Time to plug the leaks and transform window shoppers into buyers.
WITH funnel AS (
SELECT
user_pseudo_id,
event_name,
PARSE_DATE('%Y%m%d', event_date) AS event_date,
TIMESTAMP_MICROS(event_timestamp) AS event_timestamp
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'add_shipping_info', 'add_billing_info', 'purchase')
),
view_item AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'view_item'
),
add_to_cart AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'add_to_cart'
),
begin_checkout AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'begin_checkout'
),
add_shipping_info AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'add_shipping_info'
),
add_billing_info AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'add_billing_info'
),
purchase AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM funnel
WHERE event_name = 'purchase'
),
funnel AS (
SELECT
vi.event_date,
COUNT(DISTINCT vi.user_pseudo_id) AS view_item,
COUNT(DISTINCT atc.user_pseudo_id) AS add_to_cart,
COUNT(DISTINCT bc.user_pseudo_id) AS begin_checkout,
COUNT(DISTINCT asi.user_pseudo_id) AS add_shipping_info,
COUNT(DISTINCT abi.user_pseudo_id) AS add_billing_info,
COUNT(DISTINCT p.user_pseudo_id) AS purchase
FROM view_item vi
LEFT JOIN add_to_cart atc
ON vi.user_pseudo_id = atc.user_pseudo_id
AND vi.event_date = atc.event_date
AND vi.event_timestamp < atc.event_timestamp
LEFT JOIN begin_checkout bc
ON atc.user_pseudo_id = bc.user_pseudo_id
AND atc.event_date = bc.event_date
AND atc.event_timestamp < bc.event_timestamp
LEFT JOIN add_shipping_info asi
ON bc.user_pseudo_id = asi.user_pseudo_id
AND bc.event_date = asi.event_date
AND bc.event_timestamp < asi.event_timestamp
LEFT JOIN add_billing_info abi
ON asi.user_pseudo_id = abi.user_pseudo_id
AND asi.event_date = abi.event_date
AND asi.event_timestamp < abi.event_timestamp
LEFT JOIN purchase p
ON abi.user_pseudo_id = p.user_pseudo_id
AND abi.event_date = p.event_date
AND abi.event_timestamp < p.event_timestamp
GROUP BY 1
)
SELECT
event_date,
view_item,
add_to_cart,
begin_checkout,
add_shipping_info,
add_billing_info,
purchase,
1 AS view_item_rate,
ROUND(COALESCE(add_to_cart / NULLIF(view_item,0), 0), 2) AS add_to_cart_rate,
ROUND(COALESCE(begin_checkout / NULLIF(add_to_cart,0), 0), 2) AS begin_checkout_rate,
ROUND(COALESCE(add_shipping_info / NULLIF(begin_checkout,0), 0), 2) AS add_shipping_info_rate,
ROUND(COALESCE(add_billing_info / NULLIF(add_shipping_info,0), 0), 2) AS add_shipping_info_rate,
ROUND(COALESCE(purchase / NULLIF(add_billing_info,0), 0), 2) AS purchase_rate
FROM funnel
ORDER BY 1 ASC
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Quit guessing why your checkout isn't performing, use this query to pinpoint the exact friction and boost your conversion rates like a pro.
#10 How to get the top landing pages by channel group from the GA4 export
Wondering which landing pages are bringing home the bacon and which ones are just burning your marketing dollars? This query doesn't just show your best pages; it pairs them neatly with each traffic channel, so you know exactly where to double down and where to pull the plug. Stop throwing darts in the dark, and finally see what’s working and what's straight-up tanking.
WITH events AS (
SELECT
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
key = 'ga_session_id'
)
) AS unique_session_id,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS entrances,
event_timestamp
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
)
SELECT
unique_session_id,
/* The sessions first page from pageview events */
first_value(
IF(
event_name = 'page_view',
page_location,
NULL
) IGNORE NULLS
) OVER(
PARTITION BY unique_session_id
ORDER BY
event_timestamp asc
) AS session_landing_page1,
/* The sessions first page from entrance events */
first_value(
IF(
entrances > 0,
page_location,
NULL
) IGNORE NULLS
) OVER(
PARTITION BY unique_session_id
ORDER BY
event_timestamp ASC
) AS session_landing_page2
FROM
events
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Quit guessing how your pages and channels mix, this query puts clear, actionable insights right at your fingertips. While this is relatively important and definitely actionable, this next one is wayyyy better.
#11 How to get the the previous and next pages from the BQ export
Let’s face it, knowing only what page users landed on without seeing where they clicked next is about as useful as half a treasure map. This query helps you peek behind the curtain, clearly showing which pages lead visitors deeper into your site and which ones send them sprinting for the exit. Stop relying on gut feelings, and start seeing the full user journey.
WITH user_pathing AS (
SELECT
CONCAT(
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
key = 'ga_session_id'
)
) AS unique_session_id,
event_name,
event_timestamp,
REGEXP_REPLACE(
REGEXP_REPLACE(
(
SELECT
p.value.string_value
FROM
UNNEST(event_params) AS p
WHERE
p.key = 'page_location'
),
r'^https?://[^/]+',
''
),
r'[\?].*',
''
) AS page_path
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
)
SELECT
unique_session_id,
event_name,
page_path,
event_timestamp,
/* Pulling the previous page path */
IF(
event_name = 'page_view',
COALESCE(
LAST_VALUE(
IF(event_name = 'page_view', page_path, NULL) IGNORE NULLS
) OVER(
PARTITION BY unique_session_id
ORDER BY
event_timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
),
'(entrance)'
),
NULL
) AS previous_page,
/* Pulling the next page path */
IF(
event_name = 'page_view',
COALESCE(
first_value(
IF(event_name = 'page_view', page_path, NULL) IGNORE NULLS
) OVER(
PARTITION BY unique_session_id
ORDER BY
event_timestamp ASC ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING
),
'(exit)'
),
NULL
) AS next_page
FROM
user_pathing
The importance of this includes, but is not limited to:
Some immediate use case for querying all of your data is:
Quit guessing how users move—use this query to turn vague navigation paths into clear routes to conversion.
#12 How to unnest user properties
GA4’s nested user properties can feel like those Russian dolls, cool at first, but a pain when you actually need the data. This query cracks open those nested properties, clearly surfacing the valuable details hiding inside. Stop wasting time digging through layers of complexity and start easily segmenting your users by what really matters.
SELECT
CAST(event_date AS DATE FORMAT 'YYYYMMDD') as DATE,
event_name,
last_value (
(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = '{{insert your parameter here}}') IGNORE NULLS
) OVER (
PARTITION BY CONCAT(user_pseudo_id, (select value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) ORDER BY event_timestamp
)
AS {{insert name of custom parameter here}}
FROM
`{{Your BQ Project ID}}.analytics_{{Your GA4 Property ID}}.events_*`
The importance of this includes, but obviously isn’t limited to:
Some immediate use case for querying all of your data is:
Quit struggling through nested nightmares—this query hands you clear user insights, ready to go.
Yeah I’m pretty sure a conclusion paragraph is supposed to go here, but whatever if you’ve got this far I appreciate it. If you didn’t I hate you and you didn’t deserve this content ;P. Keep an eye out for some more impactful queries for different data sources. If there is something additional you want to see or do, just add it to the comments and I may or may not get to it. Just being honest. So, if you're ready to kick your data analysis into high gear, run these queries and let the numbers do the talking. Happy querying, and remember: only give a damn about what truly matters!
Project Delivery Manager | Strategy & Analytics | Digital Transformation | Leadership
4moDropping GOLD for freee!!! Go Richard Harris Jr. 💪🏽
great Share Richard Harris Jr.
GTM Lead - Data & AI | Engineering | Strategy | Architecture | Consulting
4moOpen source Data analysis before it goes 'AI will replace Data analysts' 😀
Director of Marketing, VisitGreenvilleSC
4moMan, I used to schedule client disguised meetings to learn more about data, now you’re just giving it away for free? Looking forward to these dumps!