GA4 BigQuery Export for Small Businesses

What the BigQuery export gives you

Google's BigQuery Export overview describes BigQuery as "a cloud data warehouse that lets you run highly performant queries of large datasets." You can export "all of your raw events" from a Google Analytics property to it and query them with SQL. Google adds that "When you export data to BigQuery, you own that data", and you control access with BigQuery's own permissions.

That matters in three situations. First, the GA4 interface keeps event-level data for a limited time: explorations only reach back as far as your data retention setting, 2 or 14 months on a standard property, as our GA4 explorations guide explains. Exported tables stay in your own BigQuery project (outside the sandbox, which expires them after 60 days, see below). Second, the daily export is unsampled, so large queries are not estimated. Third, you can join analytics events with other data, such as orders or CRM records, which the interface cannot do.

The export only contains what the property collects. If your enquiries are not set up as key events, fix that first: our GA4 basics guide covers the setup.

Daily, streaming and Fresh Daily export

Google offers three export types. You can turn on daily, streaming or both; Fresh Daily is for Analytics 360 only.

• Daily (standard and 360): "all the raw, unsampled event data once per day from the previous day". Google says the time is not guaranteed; it typically arrives mid-afternoon in your property's time zone but can be delayed to later in the day or the next day. Standard properties can export up to 1 million events per day.
• Streaming (standard and 360): "near realtime data (within minutes)" for the current day, with no volume limit. It is a "Best-effort service" that "may contain data gaps", and it leaves out the user source, medium and campaign of new users. Google recommends taking user attribution from the daily export instead, because that data needs about 24 hours to process.
• Fresh Daily (360 only): batched updates through the day, typically within 60 minutes, with the same schema as the daily export.

Streaming has its own charge. Google says you "will incur additional BigQuery costs for using streaming export at the rate of $0.05 per gigabyte of data", and that 1 gigabyte is about 600,000 events, depending on event size. For most small business sites, the daily export alone is enough.

The 1 million event daily limit

Google's setup page says standard properties "have a BigQuery Export limit of 1 million events for Daily (batch) exports", with no limit for streaming. If a property "consistently exceeds the export limit, the daily BigQuery export will be paused and previous days' exports will not be reprocessed." Editors and administrators get an email each time the limit is exceeded, and Google may pause the export straight away if a property goes far over it.

The fix Google gives is filtering. When you link, or later in the link settings, you can choose which data streams to export and add events to an exclusion list. A site that sends many scroll or video events, for example, could exclude those to keep the important ones flowing. Each page view, scroll, click or form submission counts as an event, so check your daily event count in GA4 before you rely on the export.

Sandbox, free tier and costs

You can export to the BigQuery sandbox, which Google says works "without providing a credit card or creating a billing account". The sandbox limits are the catch:

• "a lifetime limit of 10 GiB of storage", which is "not refunded upon data deletion";
• 1 TiB of processed query data each month, the same as the free tier;
• all tables and partitions "automatically expire after 60 days";
• streaming data is not supported.

The 60-day expiry means a sandbox does not give you a long-term archive: older daily tables disappear. It is a good place to try the export and learn the schema, not to keep history. Google's setup page also warns that switching a project from free to paid can cause failures if it is already over the 10GB sandbox limit.

With billing enabled, BigQuery charges for storage and query processing. Google's BigQuery pricing page says "The first 1 TiB of query data processed per month is free" under on-demand pricing, and the first 10 GiB of storage is free each month. Check that page for current rates in your region before you estimate a bill. Google Analytics Help adds that you need "a valid form of payment on file" for the export to continue, and if it stops because of an invalid payment method, the missing data cannot be re-exported. A dashboard that reads BigQuery also runs queries each time it refreshes, a cost our Data Studio dashboard guide mentions.

How to link GA4 to BigQuery

1. Create a Google Cloud project and enable the BigQuery API. In the Google Cloud Console, create or pick a project, then go to APIs & Services, Library, BigQuery API and click Enable. Google says you must be an Editor or above to do this.
2. Choose sandbox or billing. Decide whether to start in the free sandbox or to attach a billing account, keeping the 60-day expiry in mind.
3. Open the link in GA4. In Admin, under Product Links, click BigQuery Links, then Link. You need the Editor role on the Analytics property and an email address with Owner access to the BigQuery project.
4. Pick the project and location. Select the project and a data location. Changing the region later means deleting the link and moving the data, and Google warns this can leave a gap, so choose carefully.
5. Choose streams and exclusions. Under Configure data streams and events, pick the streams to export and any events to exclude.
6. Choose the export frequency. Select Daily, Streaming or both, then review and submit.

Google says data "should start flowing to your BigQuery project within 24 hours". The export runs from the link onward, and Google's setup pages describe no way to export the days before it, so link early if you think you will need the raw data later. Google also notes that "Once you export data from Analytics to BigQuery, you cannot re-export it", and that changing the property's time zone can shorten or lengthen one day's export.

What the export schema looks like

The BigQuery Export schema page describes the layout. Each linked property gets one dataset named "analytics_<property_id>". With daily export on, a table named events_YYYYMMDD is created each day. With streaming on, an events_intraday_YYYYMMDD table fills during the day and "is deleted at the end of each day once events_YYYYMMDD is complete."

Each row is an event. The parts you will use most:

• event_date, event_timestamp and event_name, such as page_view or your enquiry event;
• user_pseudo_id, the pseudonymous ID of the browser or app instance, plus user_id if you send one;
• event_params, a repeated record with a key and a value in string_value, int_value, double_value or float_value, which holds parameters such as page_location;
• user_properties, traffic_source, device and geo records;
• items, a repeated record for ecommerce items.

Because parameters are nested, you read them with UNNEST rather than as plain columns. Late data also matters: the schema page says Analytics updates daily tables "for up to three days after the dates of the events", and events that arrive after that window are not recorded.

A first query

Google's basic queries page uses a public sample dataset, bigquery-public-data.ga4_obfuscated_sample_ecommerce, so you can practise before your own data arrives. To query a date range, you use the events_* wildcard with the _TABLE_SUFFIX pseudo column. This example, adapted from that page, counts page views, sessions and purchases per day:

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

To use your own data, replace the table with your project and dataset, such as my-project.analytics_123456789.events_*. Always filter on _TABLE_SUFFIX: on-demand pricing charges for the data a query scans, and a narrow date range keeps that small.

Why BigQuery and GA4 numbers differ

Expect differences. The overview says the export holds raw data, "excluding any value additions that Google Analytics makes to the data found in standard reports and explorations." Google's developer article Bridge the gap between the Google Analytics UI and BigQuery export lists the usual causes:

• modelling, Google signals and attribution are added in the interface but not fully present in the export;
• "Users" in GA4 usually means active users, while counting distinct user_pseudo_id values gives total users;
• standard reports estimate some counts with HyperLogLog++, while a SQL COUNT(DISTINCT) is exact;
• time zone differences, export limits and event exclusions;
• late events: daily tables can change for up to 72 hours, so compare data older than that.

With consent mode, the overview notes that cookieless pings appear in the export, while the interface may add modelled data. When you report numbers to a client or manager, say which source each one comes from. Our guide to website analytics reporting covers the numbers most monthly reports need.

Is it worth it for a small business?

For many small business websites, the standard GA4 reports, explorations and a dashboard cover the useful questions, and BigQuery adds a Google Cloud project, SQL and possible costs. It is worth setting up when:

• you need more history than the 14-month retention allows, for year-on-year comparisons of event-level data;
• you hit sampling or thresholds in explorations and need exact counts;
• you want to join website events with sales, bookings or CRM data;
• someone on your team or an agency will actually write queries.

If that may apply within a year, linking early on the daily export with billing enabled is low-effort insurance, since the export does not cover days before the link. Keep an eye on the event count and exclude noisy events if you approach the limit.

Frequently asked questions

Is the GA4 BigQuery export free?

Linking is free and Google allows export to the BigQuery sandbox at no charge, within its limits: 10 GiB lifetime storage, 1 TiB of queries a month, and tables that expire after 60 days. Beyond that, BigQuery charges for storage and queries, and streaming export costs $0.05 per gigabyte.

What is the BigQuery export limit for standard GA4 properties?

1 million events per day for the daily export. Streaming export has no volume limit. If a property consistently exceeds the daily limit, Google pauses the daily export and does not reprocess the missed days.

Can I export historical GA4 data to BigQuery?

Google says data starts flowing within 24 hours of linking, and its setup pages describe no way to export the days before the link. Link early if you may need raw data later.

Should I use daily or streaming export?

Daily gives a complete, unsampled set of the previous day's events. Streaming gives current-day data within minutes but is best effort, may have gaps, leaves out new-user traffic source data and costs extra. Most small sites need only daily.

Why don't my BigQuery numbers match GA4?

The export holds raw collected events without GA4's modelling, Google signals and attribution. User definitions, estimation methods, time zones and late events also cause differences. Compare data older than 72 hours.

Want a website where every enquiry is tracked and the data is yours to analyse? See our website development service and all our services, or email [email protected].