How To Query And Calculate Google Analytics Data In Bigquery

by

Google BigQuery is an effective instrument for investigating and examining expansive datasets. A prominent dataset utilized by numerous organizations is Google Analytics. In this blog entry, we will examine how you can request and compute Google Analytics information through BigQuery.

Setting Up Google Analytics Data in BigQuery

Before you can start writing queries, you need to ensure your Google Analytics data is being exported to BigQuery. This is a feature available in Google Analytics 360.

Once your Analytics 360 account is linked with BigQuery, the data will be automatically exported into a dataset in BigQuery. The dataset will have a table for each day of export, labeled with the date (for example, “ga_sessions_20210501”).

Querying Google Analytics Data

Now that your data is in BigQuery, you can start writing SQL queries to extract insights.

For example, if you want to know the total number of users for a specific date, you can use the following SQL query:

        SELECT COUNT(DISTINCT fullVisitorId) as total_users
        FROM `project_id.dataset.ga_sessions_20210501`

This query calculates the total number of unique users (represented by fullVisitorId) from the table for May 1, 2021.

Calculating Google Analytics Data

BigQuery allows you to perform calculations directly in your SQL queries. For instance, if you want to calculate the average session duration for users, you can do so with the following SQL command:

        SELECT AVG(totals.timeOnSite) as average_session_duration
        FROM `project_id.dataset.ga_sessions_20210501`

This query calculates the average time on site (represented by totals.timeOnSite) from the table for May 1, 2021.

Conclusion

By exporting your Google Analytics data to BigQuery, you can leverage the powerful SQL querying capabilities of BigQuery to extract insights and make data-driven decisions. Whether you’re querying for specific data or calculating averages and totals, BigQuery makes working with large datasets easy and efficient.