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.154088952.-1392753435.1676655686