GA4 backfill - data transfer service

[PA_admin]

Hello,

Like many GA4-BigQuery users I've misunderstood the GA4 data "backfilling" capabilities.  This is partially based on documentation that referenced the UA360 enterprise service. 

The only service provider that I've been able to find that specifically offers to set up GA4 historical data backfill is Supermetrics.  (Correction: Supermetrics uses the GA4 API with all the associated limitations.  I was mislead into thinking that Supermetrics was operating differently from windsor.ai or analyticscanvas.com, but they are not.)

Does anyone know of any other options?  Analytics Canvas also has an option but it uses the GA4 API and that, strictly speaking, is not the same thing as a true GA4-BigQuery data backfill.  

Is there no paid Google option? Don't they realize that users would pay $$$$ for this?

Any thoughts are appreciated! (Helpful thoughts preferred.)

Thanks!  

[Aris_O]

Hi @PA_admin,

Welcome back to Google Cloud Community.

Here are some possible solutions to your encountered problem.

You can utilize the Google Analytics API to load historical data into BigQuery: The Google Analytics Reporting API to load historical GA4 data into BigQuery. You would be able to backfill historical data without relying on a third-party supplier, but this method would involve new work to extract the data and load it into BigQuery. Be aware that the Google Analytics Reporting API has some restrictions, including a cap of 100,000 rows per request and a daily cap of 10,000 requests per view (property).

You may also utilize a third-party data integration tool that may assist with backfilling GA4 data into BigQuery, as you have already suggested. Compared to the Google Analytics API, these technologies often provide more sophisticated data transformation and integration capabilities. However, they are more expensive.

A backfilling feature will eventually be released. It has acknowledged the need for it in GA4 and working on a solution. The release date for this feature is not yet known.

Here are some documentations that might help you:
https://cloud.google.com/bigquery/docs/dts-introduction?_ga=2.109009858.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/google-ads-transfer?_ga=2.109009858.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/samples/bigquerydatatransfer-schedule-backfill?_ga=2.15408895...

[Vicky_miele]

@Aris_O 

Do we have any update from google for backfilling of GA4 Data in to Big query,

if not is there any way can i back fill GA4 Data in to BQ as like current BQ table(events_(xxx)) structure which means i need populate all columns of BQ table from GA4 history data.

most of the 3rd party solutions are offering the custom query export, 

if i am not wrong even GA4 api request also give the data based on dimension and metrics.

pls let me know is it possible to export GA4 history data to BQ table "events_(xxx)" which currently linked to GA4 property.

[PA_admin]

Thank-you for your very helpful response to this issue.  

Would it be possible to check and see if there is a possible update on a release date for any data backfill option in Big Query for GA4 users that are not in the GA360 tier?

Thanks!

[Shafique110]

Thanks for sharing Aris,  I was wondering if you could guide or share any documentation to load historical data into BigQuery using Google Analytics API 

[Michael10]

Working on a solution for nearly a year now, is the release date already known or was it more of an empty promise?

[OlegS]

@PA_admin  windsor.ai also has backfilling capabilities. To my understanding, all these third party tools use the same GA4 API that provides pivot tables with 7 metrics limit but never digs deep into the user level that you get with native GA4-BQ import. Not sure why Supermetrics stands out in your post.

[PA_admin]

@OlegS thanks for your note.  I have corrected the original post.  You are correct that Supermetrics is doing nothing different from any of the other service providers that use the GA4 API.  Thank-you again for your correction.  

[sampsa]

Hey @Aris_O.

Lot of water has already flowed in the rivers since this comment of yours:
"A backfilling feature will eventually be released"

Would you be able to give us any update regarding the possible release date of backfilling feature?

[aliasoblomov]

One alternative to Supermetrics and similar services is a community-developed solution available on GitHub. This script, found in the "Backfill-GA4-to-BigQuery" repository, uses the Google Analytics Data API to fetch historical GA4 data, which it then uploads to BigQuery. This method isn't a native Google solution, but it offers a way to backfill GA4 data using API scripts. It's important to note that this approach may require some technical proficiency to set up and customize according to your needs.

This repository might be a useful resource for those looking to implement a DIY solution for backfilling GA4 data into BigQuery.

Visit the GitHub repository to explore this option further.

[sushantmittal]

@aliasoblomov This also uses the GA4 API and has the same limitations of 9 dimensions and 10 metrics. It won't provide the data in the format as GA4-BQ import does.

[dorianfora]

Glad we've celebrated over a year of this post! Can't wait for a backfill feature one day.

[Roderick]

Hey all - this feedback is helpful! Let me see if I can track down the team that owns this feature and see if I can get you all an update. Curious - have any of the workarounds/alternate solutions helped at all? 

[PA_admin]

Thank-you for your attention to this Roderick.  I have not been satisfied with the workarounds that are available and have been hoping that there would eventually be a paid solution for GA4-->Big Query similar to how Google Ads provides more or less turn-key data backfill using the same schema as ongoing daily uploads.  

[aliasoblomov]

The GitHub repository solution saved my life, even though it did not have the same schema. However, I was able to create my important reports using Retrivid tables +  data form in bigquery studio

[aliasoblomov]

This repository has been updated with a colab notebook .
It includes 𝟭𝟯 𝗿𝗲𝗮𝗱𝘆-𝘁𝗼-𝘂𝘀𝗲 𝗚𝗔𝟰-𝗹𝗶𝗸𝗲 𝘁𝗮𝗯𝗹𝗲 𝗲𝘅𝗽𝗼𝗿𝘁𝘀, designed to replicate key parts of the GA4 UI reports in BigQuery and CSV. This solution is similar to the GA4 UI and allows you to have a complete overview of your data. Additionally, it is completely free, and no coding is required.
https://github.com/aliasoblomov/Backfill-GA4-to-BigQuery/blob/main/GA4_tables_backfill.ipynb 

[sampsa]

Thanks @Roderick for coming back to this. Our whole company and many of our clients are still waiting for this feature. There is no proper workaround as far as I know.

[Roderick]

Heard @PA_admin @sampsa @aliasoblomov @dorianfora @sushantmittal! I found the team! Working through some information now and will find out what I can share publicly (and when!) Thanks for staying on us and letting us know this feature's value to your businesses. 

[ClaireCox207]

Any updates? The sunset data of UA and GA4 rolling 14 months is looming (next month). A little concerning Google has not come out with more info!

[stormbearker]

This is a very important feature for our company. Can you give us an update?

[dtbt]

What's the latest update with this very critical need for GA4 backfill?

[ericpecharki]

Replying to keep this in the first page

[Flo262]

Any news on this @Roderick?

[DLAQ]

Hello, I'm new to BigQuery. We started using it this year. But we want to retrieve data, at least from last year, from Google Analytics 4 to BigQuery, with a quick method and without too much code if that's possible? Thanks in advance for your help.

[stormbearker]

Hello, I was searching and I saw this. I don't know if it works but I'm going to try it to retrieve the data.
This is the link : https://github.com/aliasoblomov/Backfill-GA4-to-BigQuery

[dtbt]

It's now mid-October 2024:  16 months since this question was asked. Any updates @Roderick or @Aris_O? We really need this BigQuery<>GA4 backfill option ASAP.

[itsme93]

Well, i'd like to try to build this. Will report back once I have a testable version

[dtbt]

I will happily eat my words if you build this, but I don't believe there's any possible way to do this without Google opening up the option on their end. Because the GA4<>BigQuery integration is not retroactive, you inherently can't backfill a raw/native GA4 export. All other solutions that access retroactive data rely on the very limited GA4 APIs. There's no raw backfill solution available to "build" without access to the raw historical data. I wonder if you're looking to build a limited API-based export, as other vendors have already built (but inherently fall short).

[itsme93]

You're absolutely right - I should have been clearer. I can't magically bypass Google's limitations or access raw historical data that Google doesn't make available. My tool, like others, works within the constraints of GA4's APIs.

What I'm building is essentially an automation layer to make the API-based export process more user-friendly. It's not a true raw data backfill solution - that's technically impossible without Google enabling it.

I appreciate you calling this out. Would you be interested in testing my current approach and helping me understand what specific data points would make it more useful for your needs, even within GA4's API constraints? I'm trying to build something practical despite the limitations.

[vuyoung191]

Replying to share the concerns

[Peptide]

@Roderick Any updates from the team working on this feature?

We would love to have this feature available. 

[itsme93]

Hey Peptide, 

I've built a solution that helps backfill GA4 data to BigQuery. While it uses the GA4 API (meaning same limitations others have mentioned), we've focused on making it super easy to use with a simple UI - no coding or GitHub pulls needed.

Try it with one GA4 property free for 7 days at DataBackfill. Just log in, connect your accounts, and start syncing. Happy to answer any questions about how it works or discuss limitations in more detail.

Hope this helps add another option to the conversation! Let me know if you have any questions.

[itsme93]

https://databackfill.com/ backfill GA4 data to BigQuery. While it uses the GA4 API (meaning same limitations others have mentioned), we've focused on making it super easy to use with a simple UI - no coding or GitHub pulls needed.

[dtbt]

That's cool you whipped this up. Looks like you've tapped into content AI generation for the host of new blog posts on the site (downside: a lot of frothy/repetitive content).

I'm guessing the tool still requires choosing what sacrifices to make in the combination of metrics and dimensions that can be associable. The site mentions "Complete GA4 Data Synchronization", but doesn't mention the inherent limitations. This seems a bit misleading. The documentation page doesn't give much info either:  "the schema is designed to capture essential GA4 event data". Given there are thousands of possible dimension|metric combinations, how does it work? Does the user have to choose which dimensions they want to include, and which ones they'll forfeit?

[itsme93]

Hey, thanks for taking the time to share your thoughts! Just wanted to clear something up - if you check out our documentation page, we actually lay out exactly what we sync in the BigQuery Setup section. You can see the full table schema there with all the fields. We specifically built around the core event metrics available in the GA4 API to make sure the syncs are rock-solid and reliable.

But you know what? Rather than me explaining it all, why not take it for a spin yourself? You can sync one GA4 property with 60 days of historical data totally free - no credit card needed.

[itsme93]

Has anyone tried my tool yet? Would be great to get some feedback 

[kulka]

Wish you had other destinations other than BigQuery. Thought of trying it for personal use.

[itsme93]

We have plans to add Snowflake and Redshift this year. What data sources did you have in mind?