PipeloomGoogle Analytics 4 to BigQuery
Load GA4 report data into BigQuery with the Data API connector, including custom reports, on a schedule you control.
The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.
What to know about Google Analytics 4 into BigQuery
This is report data, not raw events
The connector pulls reports from the Google Analytics Data API: dimensions and metrics such as users by city or views by page. It does not deliver raw event rows. If you need raw events in BigQuery, use GA4's own BigQuery export, and check Google's documentation for its current limits. Use this connector for aggregated report tables and custom reports in the same pipeline as your other sources.
Avoid sampled numbers
GA4 estimates data above Google's thresholds. Setting Data Request Interval to 1 day reduces the data per request and the chance of sampling, so the BigQuery tables match GA more closely than with larger intervals up to 364 days.
Turn on One Stream per Report for several properties
By default each report and property gets its own stream, about 3,990 with all 57 reports and 70 properties. One Stream per Report gives one table per report, with property_id in the primary key. Set it before the first sync, because toggling it later renames tables and resets state.
Custom reports become tables
A custom report you define with a name, dimensions and metrics, plus optional cohorts and pivots, becomes its own stream and its own BigQuery table.
Set it up
- Create the Google Analytics 4 sourceAuthenticate with Google authentication with access to the GA4 property. You need: A Google Analytics account with access to the GA4 property and its property ID.
- Create the BigQuery destinationAuthenticate with A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, using a JSON key. You need: A Google Cloud project with BigQuery turned on, and a dataset to write to. Create it in the same location as the datasets you will join against. A service account with the BigQuery User and BigQuery Data Editor roles. For production loads, a Cloud Storage bucket for staging with an HMAC key, and the Storage Object Admin role for the service account.
- Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Google Analytics 4 supports full refresh and incremental, in append or append + deduped; BigQuery supports every mode: full refresh overwrite, append and overwrite deduped, incremental append and incremental append deduped.
- Run, then scheduleThe first sync loads each stream in full. Then set the schedule.
Step-by-step guides: Google Analytics 4 and BigQuery.
What lands in BigQuery
- A table per stream with typed columns plus _airbyte_raw_id, _airbyte_generation_id, _airbyte_extracted_at and _airbyte_meta.
- Tables partitioned by day on _airbyte_extracted_at and clustered by that column and the primary key, so filtering on the partition column scans less data.
Type mapping
- object and array → JSON
- string → STRING
- integer → INT64
- number → NUMERIC
- timestamp with time zone → TIMESTAMP
- timestamp without time zone → DATETIME
- boolean → BOOL
When Google Analytics 4's schema changes
The destination tables are updated as the source changes. Namespaces map to BigQuery datasets, and invalid characters in names are replaced with underscores.
Streams you can sync
Includes Built-in reports such as devices, pages and traffic sources, Custom reports you define with dimensions and metrics and more. The full list is in the reference.
What it costs
A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 6 minutes uses 15 credits. Run 4 times a day, that is about 1,800 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.
Questions and fixes
Does the connector count against GA4 API quotas?
Yes. It is subject to Google Analytics Data API quotas, and syncs make the same number of API requests with or without One Stream per Report.
Why do my GA4 numbers differ slightly from the GA interface?
GA4 estimates (samples) data above Google's compute thresholds. Set Data Request Interval to 1 day to reduce the chance of sampling. Larger intervals up to 364 days sync faster but risk less accurate numbers.
Why are there thousands of streams?
By default the connector creates one stream per report per property. With 57 built-in reports and 70 properties that is roughly 3,990 streams. Turn on One Stream per Report to get one consolidated stream per report, each row carrying a property_id. Changing it on an existing connection renames streams and resets incremental state, so refresh the schema and expect a full backfill.
I added a property but its history is missing.
With One Stream per Report on, a new property starts from the stream's current cursor, not your Start Date, and no error is raised. Clear the affected streams after adding the property to backfill.
Why does a sync fail with "Quota exceeded for concurrent script queries per project"?
That quota is shared with everything else in the project. Set Job Execution Project ID to a separate project so the connector's jobs count against that quota, or sync fewer streams at once. Data still lands in the dataset under Project ID.
Why does a load fail with "Fail to complete a load job in big query"?
BigQuery load jobs time out after 30 minutes of waiting, and two syncs loading into the same table at once are not supported. Make sure each table is written by one sync, and use an incremental mode to load less per sync.
Should I use the Cloud Storage bucket or standard inserts?
Use the bucket for production because it is faster for large volumes. Standard inserts need nothing to stage and suit small volumes and quick tests. Buckets using customer-managed encryption keys are not supported.
Sync Google Analytics 4 to BigQuery today
Free forever, no card. Starter is $19 a month for three seats.