BigQuery GA4 Conversions Tutorial: How to Count Events with Simple SQL

Are you looking to track how many conversions your website gets by analyzing raw Google Analytics 4 (GA4) data directly in BigQuery? You don’t need complicated models or advanced machine learning techniques to do this. With just a simple condition in your SQL queries, you can efficiently count your key conversions and gain valuable insights into your marketing performance.

If you want a step-by-step video tutorial on this topic, feel free to watch it here: How to Count Conversions in BigQuery from GA4 Data.

Step 1: Understand What GA4 Conversions Are

In GA4, conversions are essentially just events that you mark as important. Unlike Universal Analytics where conversions were tied to goals, GA4 treats conversions as any event you want to track closely — like purchases, signups, or form submissions. When you export GA4 data to BigQuery, these conversions appear as events, and you simply need to filter and count the ones you care about.

This simplicity means you don’t need to build complex attribution models or complicated SQL queries. Instead, you can use a straightforward COUNTIF condition to count events that match your conversion events.

Step 2: Write a Basic Query to Count Conversions

Let’s jump into the SQL query you’ll use in BigQuery. The key function here is COUNTIF, which counts rows that meet a specific condition.

Here’s a simplified version of the query:

SELECT
  COUNTIF(event_name IN ('purchase', 'subscribe', 'form_submit')) AS conversions
FROM
  `your_project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'

In this example:

• event_name IN ('purchase', 'subscribe', 'form_submit') lists the events you consider conversions. Replace these with your own conversion event names.
• _TABLE_SUFFIX filters the date range of your GA4 export tables.

This query returns the total count of conversion events within the specified date range.

Step 3: Customize Your Conversion Event Names

One challenge is knowing exactly which event names correspond to conversions in your GA4 data. Event names can vary between sites or setups. To identify the conversion event names you want to track, you can first query the distinct event names in your dataset.

Try this query:

SELECT
  event_name,
  COUNT(*) AS event_count
FROM
  `your_project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'
GROUP BY
  event_name
ORDER BY
  event_count DESC

This will give you a list of all event names along with how many times each event occurred during the period. From this list, you can spot the key events that matter most for your conversions.

Step 4: Refine Your Conversion List and Run the Query

Once you identify your key conversion events, add them to the COUNTIF condition in your main query. For example, if you want to track purchase, subscribe, and form_submit as conversions, your query will look like this:

SELECT
  COUNTIF(event_name IN ('purchase', 'subscribe', 'form_submit')) AS conversions
FROM
  `your_project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'

Running this query will give you the total number of conversion events in your data for the selected period.

Step 5: Additional Tips for Working with GA4 Data in BigQuery

Here are some additional tips to help you get the most out of your BigQuery GA4 users sessions tutorial:

• Customize your export script: When you set up GA4 export to BigQuery, you can customize the data script to include only the events you care about. This can make your queries faster and cleaner.
• Use date filtering wisely: GA4 exports data into daily tables (e.g., events_20230501), so use the _TABLE_SUFFIX filter to query specific date ranges.
• Explore other event parameters: GA4 events come with many parameters that can be useful for deeper analysis, such as user properties, traffic source, or page location.
• Leverage Looker Studio: For visualizing your GA4 data and conversions, consider using Looker Studio (formerly Data Studio) with ready-made templates. This can help you build marketing dashboards quickly.

Summary

Counting conversions from GA4 raw data in BigQuery doesn’t have to be complicated. By understanding that GA4 conversions are simply important events you mark, you can use straightforward SQL queries with COUNTIF to track your key conversions such as purchases, subscriptions, or form submissions.

Start by listing all distinct events in your dataset to identify your conversion events. Then, write a simple query that counts how many times those conversion events occurred during your chosen timeframe. This approach gives you a clear and accurate picture of your site’s conversion performance using raw GA4 data.

For a hands-on walkthrough, watch the full video tutorial here: BigQuery GA4 Conversions Tutorial.

Recommended Articles to Expand Your Marketing Analytics Skills

• Marketing Reports Made Easy with Looker Studio Pro & Gemini AI Slides
• From Data to Insight: How to Use Looker Studio’s Conversational AI
• How to Calculate GA4 Sessions in BigQuery: A Simple Step-by-Step Guide

Mastering BigQuery with GA4 data will unlock deeper insights into your marketing performance. Start counting your conversions today with these simple techniques and build from there!