How to Backfill Google Analytics 4 Data into BigQuery

So, you’ve connected Google Analytics 4 to BigQuery, ready to unlock raw, unsampled data for deeper analysis - only to find that your historical data is missing. It’s a common and frustrating moment, but the fix is completely achievable. This "gap" happens because the native GA4 link only sends data forward from the moment you enable it, not retroactively.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This tutorial will walk you through exactly why this happens and explore the most effective methods to backfill your historical Google Analytics 4 data into BigQuery. We’ll cover everything from the more technical, hands-on approaches to user-friendly automated solutions.

Why Is My Historical GA4 Data Not Showing Up in BigQuery?

It’s the first question everyone asks when they see empty tables where months of valuable data should be. The reason is simple and fundamental to how the integration works: the native GA4 to BigQuery export is not retroactive. When you establish the link between your GA4 property and your BigQuery project, Google begins exporting data from that day forward - that’s it.

There is no built-in "backfill historical data" button within the GA4 or Google Cloud interface. Google isn't holding your past data hostage, the system is just designed as a live data stream, not a one-time historical dump. This means that if you want a complete historical dataset in BigQuery, you need to bring in that past data yourself using other methods.

Having all your data in one place is essential for:

• Year-over-Year Analysis: Comparing Q1 performance this year to last year requires having last year's data.
• Training Machine Learning Models: Accurate models require large, comprehensive historical datasets to learn from.
• Understanding Long-Term Trends: Identifying seasonal patterns or the long-term impact of marketing campaigns is impossible without a complete history.

Fortunately, you have a few solid options to get that mission-critical historical data into BigQuery where it belongs.

Option 1: Use the Google Analytics Data API (The Hands-On Approach)

If you have some coding experience or a developer on your team, using the Google Analytics Data API is the most direct way to fetch historical data. This approach gives you maximum control over exactly what data you pull, but it also requires the most technical effort.

At a high level, the process involves writing a script (Python is a popular choice for this) to request data from the API and then load it into BigQuery.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Overview

Here’s a simplified breakdown of the steps involved in this process:

1. Set Up Your API Access & Authentication

First, you need to enable the Google Analytics Data API V1 within your Google Cloud Project. You will then have to create a service account, which acts as a non-human user that your script can use to authenticate securely. You’ll generate a JSON key file for this service account, which your code will use to prove it has permission to access your analytics data.

2. Build Your API Request

Your script will need to define the parameters for the data you want to retrieve. The key components of a request are:

• Property ID: The ID of the GA4 property you are pulling data from.
• Date Ranges: The specific start and end dates for your historical pull. You'll likely need to loop through dates in chunks (e.g., one day at a time) to avoid hitting API limitations.
• Dimensions: The attributes of your data. Think of them as the “what,” “who,” and “where.” Examples include date, campaignName, country, and pagePath.
• Metrics: The quantitative measurements. These are the numbers you want to analyze, such as sessions, activeUsers, conversions, and totalRevenue.

3. Handle Pagination and Quotas

The API will rarely, if ever, return all your requested data in a single response, especially for large date ranges or complex queries. It "paginates" the response, meaning it sends back a certain number of rows and a token to use in the next request to get the next page of results. Your script must be designed to handle this - to keep requesting data until all pages have been fetched.

You also need to be mindful of API quotas. Google limits the number of requests you can make in a given timeframe to prevent abuse. A good script introduces small delays between requests to stay within these limits.

4. Process and Load the Data into BigQuery

Once you’ve successfully fetched the data, your script will have it, likely in JSON format. The next step is to transform this data into a structured format, like a CSV file or a Pandas DataFrame. Then, you can use a BigQuery client library (available for Python, Node.js, and other languages) to create a new table in your dataset and upload the data.

Pros:

• Complete Control: You decide precisely what dimensions and metrics to pull.
• Cost-Effective: Beyond your time and BigQuery's storage/query costs, using the API is free within its quota limits.

Cons:

• High Technical Barrier: Requires programming knowledge and familiarity with APIs.
• Time-Consuming: Writing, testing, and debugging the script can take significant time.
• Maintenance: APIs change, and your script might need updates in the future.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Option 2: Use a Third-Party ETL/ELT Tool (The Automated Approach)

For most marketing teams and businesses, the most practical and efficient way to backfill Google Analytics 4 data into BigQuery is by using a third-party ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) tool. These platforms are built specifically to move data between different systems without any coding required.

Think of them as a robust data pipe. You simply provide the login details for your source and destination (BigQuery), define how far back you want to sync data, and the tool handles everything else - including API authentication, pagination, rate limits, schema management, and scheduling.

Popular tools in this space include Fivetran, Stitch, Supermetrics, and Airbyte.

How It Works

While the interface varies between platforms, the general workflow is remarkably consistent and simple:

1. Create an Account: Sign up for your chosen ETL/ELT service.
2. Set Up a Connection (or "Connector"):
3. Configure the Replication: This is the key step. The tool will ask you to select which pre-built reports or custom combinations of metrics and dimensions you want to sync. Most importantly, you will see an option for “Historical Sync” or “Data Start Date,” where you can specify how far back in time you want to pull data from. Here, you could choose to pull the last one, two, or even more years of data.
4. Run the Sync: After configuring, you’ll trigger the initial sync. The tool will begin making API calls to GA4, pulling your historical data, and loading it into managed tables in your BigQuery dataset. This initial load can take anywhere from a few minutes to several hours, depending on the volume of data.

Once the initial backfill is complete, the tool will automatically switch to an incremental refresh schedule (e.g., every hour or every day) to keep your BigQuery data up to date with fresh GA4 data.

Pros:

• Incredibly Easy: The process is point-and-click, no coding is needed.
• Fast to Set Up: You can have a data pipeline running in less than 30 minutes.
• Reliable and Maintained: The service provider handles all the complexity of API changes and error handling.

Cons:

• Cost: These are paid services, typically priced based on data volume.
• Less Granular Control: While most tools offer customization, you're still working within their pre-defined framework, which may not cover every niche dimension or metric.

Option 3: Manual CSV Export and Upload (The "In-a-Pinch" Approach)

This method is worth mentioning but should be considered a last resort for very specific, small-scale backfilling tasks. It involves manually creating reports in the GA4 interface, exporting them as CSV files, and then uploading those files to BigQuery.

The Process:

1. Navigate to the Explore section in Google Analytics 4.
2. Build a free-form report, selecting the date range, dimensions, and metrics you need.
3. Click the share/export icon and choose "Download File" → "CSV."
4. Repeat this for all the different data cuts and date ranges you need. Be extremely aware of GA4 data sampling - if your report is sampled, the data you export will be an estimate, not the raw, precise truth.
5. In the BigQuery UI, select your dataset, click "Create Table," choose "Upload" as your source, and select your CSV file. BigQuery will try to auto-detect the schema, but you may need to manually adjust it.

Pros:

• Completely free.

Cons:

• Not Scalable: Terribly inefficient for more than a few days' worth of data.
• Sampling and Data Cardinality Limits: Highly susceptible to sampling, defeating one of the main purposes of using BigQuery.
• Extremely Tedious and Prone to Human Error: It's a manual, repetitive process that is easy to mess up.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Joining Your Backfilled Data with the Live Stream

This is a critical final step. Your backfilled data (from the API or an ETL tool) and your live data (from the native GA4 connection) will live in different tables. The native connection creates daily tables named events_YYYYMMDD, while your historic data might be in a single table you named ga4_historical_data.

To analyze your entire dataset, you need to combine them. You can do this in your queries using a UNION ALL statement, or even better, create a VIEW in BigQuery that permanently combines them for easier querying.

Here’s a conceptual SQL snippet to illustrate:

CREATE VIEW `your-project.your-dataset.ga4_unified_events` AS (
-- Select data from your backfilled historical table
SELECT
event_date,
event_name,
-- Add other common columns, casting types if necessary
FROM `your-project.your-dataset.ga4_historical_data`
UNION ALL
-- Select data from a wildcard table of your native, daily exports
SELECT
event_date,
event_name,
-- Add other common columns
FROM `your-project.your-dataset.events_*`
)

Now, you can just query ga4_unified_events and automatically get a seamless view of both your historical and live data without thinking about where it came from.

Final Thoughts

Backfilling your historical GA4 data into BigQuery is essential for creating a complete and reliable source of truth for your business analytics. While the native connector doesn't support it, you can successfully populate past data using the GA4 Data API for technical control, third-party ETL tools for speed and automation, or manual CSV uploads for very small, one-off needs.

We built Graphed because we believe getting answers from your data shouldn't require you to become an impromptu data engineer. Instead of grappling with API scripts or vetting expensive ETL tools just to analyze your GA4 history, you can connect Google Analytics in a single click and have all your data - historical and live - ready for analysis instantly. Just ask questions in plain English like, "show me a chart of US traffic vs. UK traffic for the last 18 months," and get an interactive dashboard in seconds, skipping the hassle of BigQuery setup entirely.