Chapter 17: BigQuery: Hands-on Workshop
17.1. BigQuery & Looker Studio: A Hands-on Workshop
In our last session, we explored BigQuery and its various applications. Today, we're diving in headfirst and creating an end-to-end data pipeline for analysis, data extraction, and reporting from scratch in under 90 minutes.
Our goal is to perform search query analysis using data from both Google Search Console and Google Analytics. We will then combine this information to assess the impact of search queries on conversions and value creation on our website.
Search Query Data
While Google doesn't provide us with keyword-based traffic information for each session in Google Analytics, we can obtain search query data from Google Search Console. This includes metrics like the number of impressions and clicks for each query.
Combining Data Points
To gain more insights from this search query data, we need to combine it with landing page sessions, conversions, and goal conversion values from Google Analytics. By doing so, we'll be able to see how much each search query contributes to traffic acquisition, goal conversion, and goal value.
Calculating Performance Rank
Once we have all the necessary data points combined, it's time to calculate a performance rank for each search query. To do this, we'll create a metric called "value per thousand impressions." This metric will show us how much value (as defined by goal values in Google Analytics) is generated by every 1000 impressions of a particular search query in the search result pages.
The final result within Looker Studio should display:
- Search Query
- Number of Impressions (from Google Search Console)
- Number of Clicks (from Google Search Console)
- Sessions
- Goal Completions
- Goal Value (from Google Analytics)
We will also calculate the "value created per thousand impressions" by dividing the goal value for each search query by the number of impressions.
Setting Values & Goals in Google Analytics
For this exercise, we'll be using a personal website account with three goals and no transactions. Here's a brief overview of the goals and their values:
- Page Views Goal: 3 or more pages viewed per session, valued at $0.10.
- Affiliate Link Click Goal: Clicking on an affiliate outbound link, valued at $1 per click (due to expected conversions and commissions).
- Session Duration Goal: Staying on the site for more than 5 minutes during a session, valued at $0.10.
With these values in place, we can better understand the performance of different search queries and improve our overall strategy accordingly.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn