Learn SQL with Real GA4 Data: A Practical Guide for Beginners

Curious how real e-commerce data looks in SQL? In this article, you'll analyze Google Analytics 4 (GA4) events from the Google Merchandise Store using BigQuery — and learn practical SQL skills along the way.

Want to improve your SQL skills with real e-commerce data? In this article, you’ll explore Google Analytics 4 (GA4) data using SQL in BigQuery — Google’s cloud-based data warehouse. GA4 is Google’s web analytics platform used by millions of websites and apps to track how users interact with their content. It’s widely adopted in digital analytics, making GA4 skills highly valuable in the job market and across many businesses. If you connect your GA4 property to BigQuery, you can store this raw event data and query it using SQL. This lets you go beyond the built-in GA4 reports and analyze user behavior in much more detail.

Google offers a free GA4 dataset in BigQuery: ga4_obfuscated_sample_ecommerce.events_20210113. It contains real event data from the Google Merchandise Store — Google’s official online shop for branded products like t-shirts, mugs, and accessories — captured on January 13, 2021. You’ll find product views, cart additions, purchases, and user info like location or device type. It’s a lightweight, real-world dataset perfect for learning SQL.

Need a quick SQL refresher? Try our interactive SQL for Data Analysis track. It’s the fastest way to learn the basics you’ll use throughout this guide.

Get to Know the Dataset

In this guide, we’ll use a free public dataset in BigQuery:
 bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113.

GA4 uses an event-based model, where every user interaction — such as viewing a product, adding it to the cart, or completing a purchase — is tracked as a separate event. Each row in the dataset represents one of these interactions and contains both standard fields (like event name and timestamp) and nested structures (like product details or event parameters).

Here are some key columns you’ll work with:

• event_name: the type of user action, such as page_view, view_item, add_to_cart, begin_checkout, or purchase
• user_pseudo_id: anonymized user identifier
• event_timestamp: when the event happened
• event_params: details related to the event (stored as key-value pairs)
• items: nested product data for events like view_item or purchase

Which Events Are Most Common?

Let’s start by seeing what users are doing on the site. This query shows the most common event types and how many users triggered them:

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS unique_users,
  COUNT(*) AS total_events
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
GROUP BY event_name
ORDER BY total_events DESC;

This query shows the most common user actions. As expected, page_view leads with over 15,000 events. Only 34 users completed a purchase, while many more viewed products or added them to the cart — a clear drop-off typical of e-commerce funnels.

Track the Purchase Funnel

Next, let’s build a basic funnel to understand how many users move from browsing to buying. We’ll count how many unique users triggered each of the key e-commerce events:

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
GROUP BY event_name
ORDER BY users DESC;

This shows you how users drop off at each stage of the shopping journey. You’ll likely notice more users viewing items than actually completing purchases — just like in most online stores.

Analyze What People Bought

Want to know which products users are buying the most? Many e-commerce events include product details in a nested field called items. To access that, we’ll use the UNNEST() function.

Here’s how to find the top 10 purchased products:

SELECT
  item.item_name,
  COUNT(*) AS purchases
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY purchases DESC
LIMIT 10;

And here is the result:

You can also analyze product prices and calculate total or average revenue.

For example, this query returns the total revenue per product:

SELECT
  item.item_name,
  ROUND(SUM(item.price * item.quantity), 2) AS total_revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY total_revenue DESC
LIMIT 10;

It’s a great way to find your store’s bestsellers.

Segment Users by Country

GA4 data also includes information about each user’s location and device. You can segment traffic by geo.country.

Here’s a query that shows where users are buying from:

SELECT
  geo.country,
  COUNT(DISTINCT user_pseudo_id) AS unique_buyers
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name = 'purchase'
GROUP BY geo.country
ORDER BY unique_buyers DESC;

Practice More on Your Own

Now that you’ve worked through the guided examples, it’s time to practice on your own. The best way to build your SQL skills — and think like a data analyst — is by exploring the data with your own questions in mind.

• Break down purchases by traffic source (traffic_source.source)
• Compare average cart size between mobile and desktop users
• Analyze product views that didn’t lead to a purchase
• Build a session-level path for a single user

Here are a few hands-on challenges to try next:

• Break down purchases by traffic source. See which sources (like Google, direct, or referral) are driving the most purchases using traffic_source.source.
• Compare average cart size between mobile and desktop users. Use device data to find out if user behavior changes depending on platform.
• Analyze product views that didn’t lead to a purchase. Identify drop-off points in the funnel by comparing view_item and purchase events.
• Build a session-level path for a single user. Pick a user_pseudo_id and reconstruct their journey step by step using event_timestamp.

The more you experiment with queries like these, the better you’ll understand the data — and the more confident you’ll become in applying SQL to real-world business problems.

Final Thoughts

Learning SQL is a lot more engaging — and practical — when you’re working with real data. Especially when that data reflects real user behavior in an actual online store, like the Google Merchandise Store. It’s not just theory; it’s how data analysts, marketers, and product teams work every day.

In this article, you learned how to:

• Write queries using real GA4 e-commerce data
• Work with nested fields using UNNEST()
• Explore events, products, user segments, and funnels

By working directly with GA4 data in BigQuery, you’ve taken a big step toward thinking like a data analyst — asking questions, writing meaningful queries, and finding insights that matter.

Want to go further? Check out our SQL From A to Z track to build a strong foundation, or dive into more advanced topics with our interactive SQL courses.

Keep practicing, stay curious — and happy querying!