From Zero to SQL Hero: A Beginner’s Guide to Analyzing Google Analytics Data in BigQuery

If you’re using Google Analytics to track website or app performance, you probably know how valuable the data can be. But with so much information available, it can be challenging to extract meaningful insights.

This is where SQL queries come in handy. By using SQL to query your Google Analytics data in BigQuery, you can analyze your data more efficiently and gain insights that can help you improve your website or app performance.

Below are some SQL queries that you can use to get started with analyzing your Google Analytics data.

These SQL queries are designed to be used with Google Analytics data that has been exported to BigQuery.

To get this data, you’ll need to follow these steps:

1. Set up a Google Analytics account for your website or app.
2. Link your Google Analytics account to BigQuery by following the instructions provided by Google. This will involve creating a BigQuery dataset and linking it to your Google Analytics account.
3. Once your data is exported to BigQuery, you can run SQL queries like the ones I provided to analyze your data.

Note that exporting your Google Analytics data to BigQuery may incur additional charges, so be sure to check the pricing and billing details before proceeding.

1. Total Sessions by Traffic Source:

SELECT
  trafficSource.source AS Traffic_Source,
  COUNT(*) AS Sessions
FROM
  `project.dataset.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20220101' AND '20220331'
GROUP BY
  trafficSource.source

2. Pageviews by Country:

SELECT
  geoNetwork.country AS Country,
  SUM(totals.pageviews) AS Pageviews
FROM
  `project.dataset.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20220101' AND '20220331'
GROUP BY
  geoNetwork.country
ORDER BY
  Pageviews DESC

3. Top Landing Pages by Device Category:

SELECT
  deviceCategory AS Device_Category,
  hits.page.pagePath AS Landing_Page,
  COUNT(*) AS Sessions
FROM
  `project.dataset.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '20220101' AND '20220331'
  AND hits.type = 'PAGE'
  AND hits.isEntrance = TRUE
GROUP BY
  Device_Category,
  Landing_Page
ORDER BY
  Sessions DESC

4. Average Session Duration by Channel Grouping:

SELECT
  channelGrouping AS Channel_Grouping,
  ROUND(AVG(totals.timeOnSite / 60), 2) AS Avg_Session_Duration
FROM
  `project.dataset.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20220101' AND '20220331'
GROUP BY
  Channel_Grouping
ORDER BY
  Avg_Session_Duration DESC

Note: Please replace project.dataset with your own BigQuery project and dataset, and update the date range accordingly.