Why should you opt for Google Analytics 4 BigQuery export integration?
Google BigQuery is Google’s fully managed, scalable over petabyte, PaaS data warehouse. BigQuery is NoOps so there is no need to manage any infrastructure and you do not need a database administrator. All you need to do is focus on analyzing data and getting meaningful insights by executing highly performant queries using familiar ANSI SQL.
By exporting raw data to BigQuery, you own your data, and you can later export it to your favourite warehouse, local drive, data studio or any other external storage and you can also import other marketing platform data for combining it with your analytics data.
You can choose between daily or streaming export option. For daily export option a full export of data takes place once a day and a table gets created each day.
With streaming export option data is exported continuously throughout the day and table named gets created each day. This table is deleted when is complete. BigQuery streaming export makes data for the current day available within a few minutes via BigQuery export. When you use this export option, BigQuery has more recent information that you can analyze about your users and their traffic on your property.
You should query rather than ‘events_intraday_YYYYMMDD’ as you get a stable dataset for the day.
A dataset named is added to your BigQuery project, for each GA4 property that is linked to BigQuery. Property_id refers to your analytics property id. Each Google Analytics 4 property for which BigQuery exporting is enabled will export its data to that single dataset.
There are no billing charges associated with exporting data from a Google Analytics 4 property to BigQuery. You can also export to a free instance of BigQuery ( BigQuery sandbox), but exports that exceed the sandbox limits incur charges. Standard properties have a daily BigQuery Export limit of 1 million events.
How to set up BigQuery export integration for GA4 property?
Prerequisite – Use an email address that has owner access to the BigQuery project and must have editor role for the Analytics property to be linked. Also make sure both this account is for same user id.
Following are the quick steps to link GA4 property to BigQuery
Step 1. Select Admin from bottom left corner of your GA account
Step 2. Choose the GA4 property to be linked and under PRODUCT LINKS, click BigQuery Links.
Step 3. Click on Link to create the integration.
Step 4. Click on ‘Choose a BigQuery Project’
Step 5. Choose a BigQuery Project to which you want to link, then click ‘Confirm
Step 6. Select a location for the data and click ‘Next’.
Step 7. Select either or both a Daily (once a day) or Streaming (continuous) export of data and click ‘Next’.
Step 8. Review your settings, then click Submit.
Following are the detailed steps to link GA4 property to BigQuery
Step 1: Create a Google-APIs Console project
Step 1: Log in to the Google APIs Console.
Step 2: Create a Google APIs Console project or select an existing project.
Step 3: A new pop-up that appears, click on ‘New Project’.
Step 4: Provide Project Name of your choice and click on ‘Create’.
If you do not have an organization, creation window looks like below
If you have an organization, creation window looks like below where you must also provide location option.
Congratulations! You have successfully created a new project.
Step 2: Enable BigQuery API
Step 1: Open the Navigation menu in the top-left corner, click ’APIs & Services’, then click ’Library’.
Step 2: Search BigQuery
Step 3: Choose ‘BigQuery API’
Step 4: Enable BigQuery.
Congratulations! You have successfully enabled the BigQuery API.
Step 3: Link BigQuery to a Google Analytics 4 property
Sign in to Google Analytics. Make sure to use an email address that has owner access to the BigQuery project, and must have editor role for the Analytics property to be linked.
Step 1: Click on ‘Admin’, in lower left corner of your screen
Step 2: Make sure that you have the desired account and property selected. In the Property column, under PRODUCT LINKS, click ‘BigQuery Links’.
Step 3: Click on ‘Link’.
Step 4: Click ‘Choose a BigQuery project’ to display a list of projects.
Step 5: Select a project from the list to which you want to link, then click ‘Confirm’.
Step 6: Select a location for the data and click ‘Next’.
Note: If by any chance you later want to change the region, then you will have to delete the existing link, existing dataset in BigQuery and do the entire process of linking again.
Step 7: Select ‘Configure data streams and events’
By default, if you have more than one stream then all are selected.
You can edit selection of data streams to include for the export and specific events to exclude from the export.
You can exclude events by either clicking ‘Add’ (to select from a list of existing events) or by clicking ‘Specify event by name’ (to choose existing events by name)
Step 8: Select Include advertising identifiers for mobile app streams if you want to include advertising identifiers.
Step 9: Select either or both a Daily (once a day) or Streaming (continuous) export of data and click ‘Next’.
Step 10: Review your settings, then click Submit.
Congratulations! You have successfully set up the BigQuery Export.
You are good to go
Now that the linkage is complete, data should start flowing to your BigQuery project within 24 hours. If you enable daily export, then 1 file will be exported each day that contains the previous day’s data in in the time zone you set.
Delete a link to BigQuery
1. Click Admin and select the desired account and property.
2. In the Property column, under PRODUCT LINKS, click BigQuery Links.
3. Click the row for the link.
4. In the top right, click Delete.
Looking for hassle free BigQuery export integration?
Electrik.AI has lot of customers who are using our fully automated BigQuery export integration for GA3. Very soon we are coming up with our BigQuery export integration for GA4. Unlike Google’s BigQuery integration we have no limitations on maximum export limit nor the user id of GA4 and GCP should be same. You don’t have to bother about setting a link or deleting a link, you can keep on exporting or pausing your export whenever you want. Also get email notifications when your data is ready. Just sit tight, we will keep you posted.
Before upgrading to GA4, if you want to secure your universal analytical data then try our data pipelines for GA3 to a database/data warehouse/File destination. To know more about exporting universal analytics data, read here.
BigQuery Export Schema Definition
GA4 exported schema column details.
Column Name | Data Type | Description |
---|---|---|
App | ||
app_info | RECORD | A record of information on the app. |
app_info.id | STRING | The package name or bundle ID of the app. |
app_info.firebase_app _id | STRING | The Firebase App ID associated with the app |
app_info.install_source | STRING | The store that installed the app. |
app_info.version | STRING | The app's versionName (Android) or short bundle version. |
Device | ||
device | RECORD | A record of device information. |
device.category | STRING | The device category (mobile, tablet, desktop). |
device.mobile_brand_ name | STRING | The device brand name. |
device.mobile_model _name | STRING | The device model name. |
device.mobile_marketing _name | STRING | The device marketing name. |
device.mobile_os_hardware _model | STRING | The device model information retrieved directly from the operating system. |
device.operating_system | STRING | The operating system of the device. |
device.operating_system _version | STRING | The OS version. |
device.vendor_id | STRING | IDFV (present only if IDFA is not collected). |
device.advertising_id | STRING | Advertising ID/IDFA. |
device.language | STRING | The OS language. |
device.time_zone_offset _seconds | INTEGER | The offset from GMT in seconds. |
device.is_limited_ad _tracking | BOOLEAN | The device's Limit Ad Tracking setting. |
On iOS14+, returns false if the IDFA is non-zero. | ||
device.web_info.browser | STRING | The browser in which the user viewed content. |
device.web_info.browser _version | STRING | The version of the browser in which the user viewed content. |
device.web_info. hostname | STRING | The hostname associated with the logged event. |
Stream and platform | ||
stream_id | STRING | The numeric ID of the stream. |
platform | STRING | The platform on which the app was built. |
User | ||
user_first_touch_ timestamp | INTEGER | The time (in microseconds) at which the user first opened the app or visited the site. |
user_id | STRING | The user ID set via the setUserId API. |
user_pseudo_id | STRING | The pseudonymous id (e.g., app instance ID) for the user. |
user_properties | RECORD | A repeated record of user properties set with the setUserProperty API. |
user_properties.key | STRING | The name of the user property. |
user_properties.value | RECORD | A record for the user property value. |
user_properties.value .string_value | STRING | The string value of the user property. |
user_properties.value .int_value | INTEGER | The integer value of the user property. |
user_properties.value .double_value | FLOAT | The double value of the user property. |
user_properties.value .float_value | FLOAT | This field is currently unused. |
user_properties.value .set_timestamp_micros | INTEGER | The time (in microseconds) at which the user property was last set. |
user_ltv | RECORD | A record of Lifetime Value information about the user. This field is not populated in intraday tables. |
user_ltv.revenue | FLOAT | The Lifetime Value (revenue) of the user. This field is not populated in intraday tables. |
user_ltv.currency | STRING | The Lifetime Value (currency) of the user. This field is not populated in intraday tables. |
Campaign | Note: traffic_source attribution is based on cross-channel last click. traffic_source values do not change if the user interacts with subsequent campaigns after installation. | |
traffic_source | RECORD | Name of the traffic source that first acquired the user. This field is not populated in intraday tables. |
traffic_source.name | STRING | Name of the marketing campaign that first acquired the user. This field is not populated in intraday tables. |
traffic_source.medium | STRING | Name of the medium (paid search, organic search, email, etc.) that first acquired the user. This field is not populated in intraday tables. |
traffic_source.source | STRING | Name of the network that first acquired the user. This field is not populated in intraday tables. |
Geo | ||
geo | RECORD | A record of the user's geographic information. |
geo.continent | STRING | The continent from which events were reported, based on IP address. |
geo.sub_continent | STRING | The subcontinent from which events were reported, based on IP address. |
geo.country | STRING | The country from which events were reported, based on IP address. |
geo.region | STRING | The region from which events were reported, based on IP address. |
geo.metro | STRING | The metro from which events were reported, based on IP address. |
geo.city | STRING | The city from which events were reported, based on IP address. |
Event | ||
event_date | STRING | The date on which the event was logged (YYYYMMDD format in the registered timezone of your app). |
event_timestamp | INTEGER | The time (in microseconds, UTC) at which the event was logged on the client. |
event_previous_ timestamp | INTEGER | The time (in microseconds, UTC) at which the event was previously logged on the client. |
event_name | STRING | The name of the event. |
event_params | RECORD | A repeated record of the parameters associated with this event. |
event_params.key | STRING | The event parameter's key. |
event_params.value | RECORD | A record of the event parameter's value. |
event_params.value .string_value | STRING | The string value of the event parameter. |
event_params. value.int_value | INTEGER | The integer value of the event parameter. |
event_params.value .double_value | FLOAT | The double value of the event parameter. |
event_params.value .float_value | FLOAT | The float value of the event parameter. This field is currently unused. |
event_value_in_usd | FLOAT | The currency-converted value (in USD) of the event's "value" parameter. |
event_bundle_sequence _id | INTEGER | The sequential ID of the bundle in which these events were uploaded. |
event_server_timestamp _offset | INTEGER | Timestamp offset between collection time and upload time in micros. |
Ecommerce | ||
ecommerce | RECORD | A record of information about ecommerce. |
ecommerce.total_item _quantity | INTEGER | Total number of items in this event, which is the sum of items.quantity. |
ecommerce.purchase _revenue_in_usd | FLOAT | Purchase revenue of this event, represented in USD with standard unit. Populated for purchase event only. |
ecommerce.purchase _revenue | FLOAT | Purchase revenue of this event, represented in local currency with standard unit. Populated for purchase event only. |
ecommerce.refund _value_in_usd | FLOAT | The amount of refund in this event, represented in USD with standard unit. Populated for refund event only. |
ecommerce.refund_value | FLOAT | The amount of refund in this event, represented in local currency with standard unit. Populated for refund event only. |
ecommerce.shipping _value_in_usd | FLOAT | The shipping cost in this event, represented in USD with standard unit. |
ecommerce.shipping _value | FLOAT | The shipping cost in this event, represented in local currency. |
ecommerce.tax_value _in_usd | FLOAT | The tax value in this event, represented in USD with standard unit. |
ecommerce.tax_value | FLOAT | The tax value in this event, represented in local currency with standard unit. |
ecommerce.transaction _id | STRING | The transaction ID of the ecommerce transaction. |
ecommerce.unique _items | INTEGER | The number of unique items in this event, based on item_id, item_name, and item_brand. |
Items | ||
items | RECORD | A repeated record of items included in this event. |
items.item_id | STRING | The ID of the item. |
items.item_name | STRING | The name of the item. |
items.item_brand | STRING | The brand of the item. |
items.item_variant | STRING | The variant of the item. |
items.item_category | STRING | The category of the item. |
items.item_category2 | STRING | The sub category of the item. |
items.item_category3 | STRING | The sub category of the item. |
items.item_category4 | STRING | The sub category of the item. |
items.item_category5 | STRING | The sub category of the item. |
items.price_in_usd | FLOAT | The price of the item, in USD with standard unit. |
items.price | FLOAT | The price of the item in local currency. |
items.quantity | INTEGER | The quantity of the item. |
items.item_revenue _in_usd | FLOAT | The revenue of this item, calculated as price_in_usd * quantity. It is populated for purchase events only, in USD with standard unit. |
items.item_revenue | FLOAT | The revenue of this item, calculated as price * quantity. It is populated for purchase events only, in local currency with standard unit. |
items.item_refund_in_usd | FLOAT | The refund value of this item, calculated as price_in_usd * quantity. It is populated for refund events only, in USD with standard unit. |
items.item_refund | FLOAT | The refund value of this item, calculated as price * quantity. It is populated for refund events only, in local currency with standard unit. |
items.coupon | STRING | Coupon code applied to this item. |
items.affiliation | STRING | A product affiliation to designate a supplying company or brick and mortar store location. |
items.location_id | STRING | The location associated with the item. |
items.item_list_id | STRING | The ID of the list in which the item was presented to the user. |
items.item_list_name | STRING | The name of the list in which the item was presented to the user. |
Items.item_list_index | STRING | The position of the item in a list. |
items.promotion_id | STRING | The ID of a product promotion. |
items.promotion_name | STRING | The name of a product promotion. |
items.creative_name | STRING | The name of a creative used in a promotional spot. |
items.creative_slot | STRING | The name of a creative slot. |
Web | ||
web_info | RECORD | A record of information for web data. |
Privacy info | The following fields are based on the consent status of your users when you are using consent mode. | |
privacy_info.ads_storage | STRING | Whether ad targeting is enabled for a user. |
Possible values: Yes, No, Unset | ||
privacy_info.analytics_storage | STRING | Whether Analytics storage is enabled for the user. |
Possible values: Yes, No, Unset | ||
privacy_info.uses_transient_token | STRING | Whether a web user has denied Analytics storage and the developer has enabled measurement without cookies based on transient tokens in server data. |
Possible values: Yes, No, Unset |