Migrate from UA to GA4 BigQuery Export: End-to-End Tutorial

Universal Analytics (UA) is officially going away. If you haven’t already, it’s time to switch to Google Analytics 4 (GA4). Don’t panic! We’ll walk you through how to migrate your data export from UA to GA4 with BigQuery. And yes, we’ll make it fun, simple, and clear.

This guide covers everything — from setting up GA4 to getting your data into BigQuery. You’ll be exporting data like a pro in no time!

Why the Big Deal?

Google Analytics 4 is different. Not just a little different. Like, totally new platform different.

• It has a new data model — event-based, not session-based.
• Reporting is more flexible — but also less intuitive at first.
• BigQuery export is FREE for everyone with GA4 (yay!).

So moving your export over is not just important — it’s essential.

Step 1: Say Goodbye to UA (Respectfully)

If you’ve used Universal Analytics with BigQuery, you’re probably familiar with:

• Daily exports of session-based data
• Tables like ga_sessions_YYYYMMDD
• Needing GA360 for BigQuery access

Well, GA4 changes that. Data is structured around events. Each hit is an event. Page views, clicks, purchases — all events. Much richer. Much more flexible.

But because it’s different, you can’t just copy-paste your old setup. You need to re-think your export strategy.

Step 2: Set Up GA4 (If You Haven’t Already)

Here’s how:

1. Log into Google Analytics.
2. Click on “Admin”.
3. Under your current account, choose “Create Property”.
4. Select “GA4 Setup Assistant”.
5. Follow the prompts. It’s mostly just clicking “Next” and “Create”.

Done? Great. Your GA4 property is now ready to track data. But we’re here for BigQuery, right? Let’s go there next.

Step 3: Link GA4 to BigQuery

This part is super exciting because — unlike UA — GA4 lets everyone export data to BigQuery for free!

1. Go to the GA4 property you created.
2. Click Admin > BigQuery Links.
3. Click Link.
4. Choose the BigQuery project you want to connect to.
5. Pick the data location (US or EU).
6. Choose Daily and/or Streaming Export.
7. Click Submit.

That’s it! Now GA4 will automatically create tables in BigQuery inside your project.

Step 4: Understand the New GA4 Data Structure

This part is crucial. GA4 data looks different from UA data.

In BigQuery, you’ll now see:

• Tables named like: events_YYYYMMDD
• Each row is an event. Not a session.
• Data is nested — you’ll need to use UNNEST() in your SQL to look inside arrays.

Key fields to know:

• event_name: Type of event (e.g., page_view, user_engagement)
• event_params: Custom fields attached to events
• user_pseudo_id: Anonymous user ID
• geo, device, traffic_source: Metadata about users

It takes a bit of SQL magic to get useful reports, but the power you unlock is next-level.

Step 5: Sample SQL Queries

Ready to crunch some numbers? Here are a few basic queries to get you started.

Query 1: Daily Pageviews

SELECT
  event_date,
  COUNT(*) AS pageviews
FROM
  `your-project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'page_view'
GROUP BY
  event_date
ORDER BY
  event_date;

Query 2: Most Popular Pages

SELECT
  ep.value.string_value AS page_path,
  COUNT(*) AS views
FROM
  `your-project.your_dataset.events_*`,
  UNNEST(event_params) AS ep
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'page_view'
  AND ep.key = 'page_location'
GROUP BY
  page_path
ORDER BY
  views DESC
LIMIT 10;

Query 3: Users by Country

SELECT
  geo.country,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `your-project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  geo.country
ORDER BY
  users DESC;

These are just starters. Once you get comfy, you can build all kinds of cool reports.

Step 6: Validate Your Data

Now it’s time to make sure the new data is correct. You should:

• Compare your GA4 numbers with tracker UI to ensure they match.
• Keep in mind: GA4 filters out bot traffic differently than UA.
• Expect some differences in user and session counts.

Don’t worry if numbers don’t match exactly. The models are different. Focus on getting trend accuracy, not perfect copies.

Step 7: Archive Your UA Data

Even though UA is shutting down, you’ll still want to hold on to that data.

What you can do:

• Export your historical UA BigQuery data to a new table or project.
• Use scheduled queries to keep daily snapshots, if necessary.
• Store summaries in Google Sheets or CSV exports if BigQuery isn’t your thing.

Whatever you choose, make sure important performance metrics are backed up.

Bonus: Visualization Tools

Want to make your GA4 data shine? You can use:

• Looker Studio (fka Data Studio)
• Tableau
• Google Sheets with BigQuery connector

Just connect BigQuery as a data source and let your dashboards fly.

Final Thoughts

Moving from UA to GA4 may seem like a big leap. But it’s worth it. You get richer data, better export access, and a more future-proof analytics setup.

To recap:

• Create a GA4 property
• Link to BigQuery
• Understand the new data model
• Test your SQL queries
• Validate and archive your UA data

GA4 is here to stay — and it’s powerful. BigQuery makes that power even greater.

Now go, play with data, and unlock the insights waiting for you!