Challenges on exporting data in Google BigQuery

Google BigQuery, which bills users based on query processing and table storage, is not a data warehouse everyone opts for. BigQuery Pricing

Linking Google Analytics 4 (GA4) with BigQuery and storing data in BigQuery is not entirely free. Although Google provides a restricted free tier with specific usage allocations, there are charges for exceeding these limits. The initial 1 TB of requests per month and 10 GB of stored data is offered as a complimentary service to all GA4 users.

Let’s say you’re looking to gather the total page views and unique users for a specific event category. This could be for the past month, the last three months, or even for an entire year. Keep in mind that conducting queries over longer time spans comes with noticeably higher costs. To provide some context, running a query for a full year can result in approximately twelve times the cost compared to a one-month query, assuming all other factors remain constant.

For many the exported data can be confusing, as it is spread across numerous distinct tables, each featuring complex nested, denormalized, and array fields. Therefore, comprehending and effectively working with this data, particularly for analytical tools, can be extremely challenging.

Some fields are structured as repeated records or arrays in Google Analytics 4 (GA4) export data to BigQuery. This is done to capture multiple occurrences of certain events or parameters associated with a single user interaction.

For example, consider an e-commerce website where a user can view multiple product categories in a single session. In this case, the GA4 BigQuery table may have a field like “event_params” that is structured as repeated data. This allows for capturing all the different product categories viewed by a user in one session.

Here’s a simplified example of what the data might look like in BigQuery:

Now since the GA4 data schema is a complex denormalized structure so to start to query your Google Analytics 4 Properties data, you’ll often need to flatten the nested and repeated data in BigQuery. To do so we need to make use of unnest function. It essentially transforms an array or a nested structure into a table, which allows for easier querying and analysis.

Using the UNNEST function in BigQuery, while powerful for working with nested and repeated data, does come with some potential disadvantages, particularly in terms of query optimization and costing:

Electrik.AI (Marketing Intelligence tool) is a well-equipped tool that address these challenges. The people are more accustomed with using standard and popular databases such as Azure SQL Server, MySQL, PostgreSQL, Snowflake, and others.

Numerous data professionals are proficient in working with spreadsheets, with a substantial portion favouring file-based storage for analytics data. To cater to their preferences, Electrik.AI also has seamless extraction and uploading to widely used file storage platforms like Azure Blob Storage,

Here are other databases and data warehousing options, apart from BigQuery, where you have the option to export your GA4 data.

The beauty of exporting in your preferred database or file storage is that the data schema is completely normalized and easy to understand and transform. By exporting to your preferred database or file storage you not only gain ownership of the data but also the freedom to transform and utilize it according to the preferences of your chosen analysis tools.