Google recently announced their support for exporting Google Search Console data into BigQuery. As a beta tester, I had the opportunity to test this new export. I decided to put together a demo report in Looker Studio that shows what can be done.

Google Search Console report in Looker Studio using the BigQuery export

A long time ago, I built a report to show Click Through Rate based on SERP position using data exported from the Google Search Console. I’ve added that to this report.

SERP Click Through Rate based on position

I’ve always been interested in combining Search Console data with Analytics and have developed some in-house reports that dig deep into that connected data. I decided to include some GA4 data in this report. Later I plan to include things like average revenue for a page or query.

Google Search Console connected to GA4

Here’s how to set it up for your site. The demo data comes from my Classy Schema website.

Exporting from Google Search Console into BigQuery

Google’s announcement includes full instructions on how to start the export. There’s also official documentation on the process. When completed, you should see a ‘searchconsole’ dataset in your BigQuery project that will get populated in about a day.

If you have already set up other exports, like GA4, select the same data location.

The searchconsole dataset in BigQuery

You should complete this as soon as possible since it only exports from the day it was set up.

Exporting from GA4 to BigQuery

Do this if you want the GA4 part of the report to work.

Note: our BigCommerce Tag Rocket customers who have set up The Tag Rocket Report have already completed this section including the setting up of the user_sessions query.

Google also provides instructions on how to set up the GA4 export. This report only requires the Daily export. Ensure you select the same data location as the one you used for the Search Console data.

In about a day, you should see an analytics dataset in your project that includes the GA4 property id in its name.

GA4 export to BigQuery

Setting up the scheduled query

This report does not directly access the GA4 data as that can be expensive (most people will probably never go beyond the free allowance), and it is not in the structure we want. The report is interested in sessions, so we will run a daily SQL query that generates a table with the data we want.

This query is taken from the queries we use to generate the Tag Rocket Report for our BigCommerce customers. It creates a ‘user_sessions’ table in a ‘tag_rocket’ dataset.

The query is designed to insert the last 3 days of data into the table on a daily basis. This minimises the data being processed to reduce the chance of any cost.

In BigQuery, open up a new query and copy our sessions query into the editor. You then need to replace all occurrances of ${ProjectID} with your BigQuery project id. In my case, it is classy-schema-2. And then replace all occurrances of ${DatasetID} with the dataset ID for GA4. In my case, it is analytics_253835071. The editor has a search and replace feature that you can open with Ctrl-F.

You can then test your query by pressing the RUN button. It will take 10 seconds or so.

If it runs fine, you want to then save it as a scheduled query. Click on SCHEDULE, then “Create new scheduled query”. You may have to enable a few things if it’s the first time you have used it.

Creating a scheduled query

Name it something like “Tag Rocket sessions table”, pick a time for it to run each day and Save.

Scheduled user_sessions table query

Your user_session table will now update every day.

Creating the Report

Once your initial tables have been populated (say, after a day), you can create a copy of the demo report that points to your data.

Open the Google Search Console to BigQuery demo report and click “Make a copy”.

Copying the demo report

Here you will be prompted to select two datasources, one for the Search Console data and one for the user_sessions data. In both cases, you want to select “Create a new data source”, then Big Query, then tunnel down to the correct table.

For the searchdata_url_impression data source ,you want to find that table in your searchconsole dataset, check the partition option, connect, then select the ADD TO REPORT button.

searchdata_url_impression data set connection

For the user_sessions data source, you want to find that table in the tag_rocket dataset, check the partition option, connect and ADD TO REPORT.

user_sessions datasource

Note that sometimes the data source editor does not show the selected datasource, but it is still selected. Click Copy Report, wait, and your report should show up. Switch to the view mode to see the report in its full glory.

In edit mode, you may want to rename the report, decide who you want to share it with, and then publish it.

Updating the report

Summary

The current report is designed to show the potential of using BigQuery to create reports using both Google Search Console and GA4. I’d love to hear some ideas on what you think would be valuable in a report like this.

Leave a Reply

Your email address will not be published. Required fields are marked *