We previously set up BigQuery and loaded our data into it in the last two tutorials:

In this tutorial I’m going to show you how to build your first ever chart with SQL. It will be a short one because it’s actually really simple.

start sales pitch

If you want to quickly skill-up in SQL to learn how to analyse data, hmm… check out supercooldata.com :)

end sales pitch

The first step is to understand what data we’re going to use. We will use the Google Search Console data we loaded with Fivetran.

It’s super simple to set up with Fivetran. Once you’ve done that you should have the following tables available in BigQuery and these will update with the schedules that you’ve set in the Fivetran UI.

These are the tables you will see in BigQuery: Fivetran Google Search Console Tables

These are the columns available in each of the tables:

Fivetran Google Search Console Tables

This is called an Entity Relationship Diagram (ERD). It pretty much just tells you all the tables and columns available and how they link together.

Fivetran gives you an ERD for each connector you set up. It is very useful for understanding how to analyse your data and what table to query to get different answers.

You can access this in the documentation for any connector. Here is the Google Search Console connector documentation:

https://fivetran.com/connectors/google-search-console

We’re going to query the table keyword_site_report_by_page.

This table has one row for every unique combination of country, date, device, keyword, search_type and site. This is known as the composite key of the table.

The first step is just to see the data and what it looks like:

Query all columns

The query used is below:

select  
  *
from
  fivetran_google_search_console.keyword_site_report_by_page
limit 1000;

The query returns 1000 rows of all columns (the * symbol selects all columns) from the keyword_site_report_by_page table within the fivetran_google_search_console dataset or schema.

The question we want to answer from this dataset is which categories of keywords produce the most impressions for us.

The data is for the supercooldata.com domain and so common keywords to arrive at this page are “SEO” and “SQL” as well as the combination of the two. You might also get to this page by searching for ‘supercooldata’ or some variant which we categorise as ‘Branded Search’ in our SQL query.

We will create an if/else statement to categorise our keywords into a handful of groups. In SQL, this is known as a case/when and does the same thing as an if statement in Excel or other tools and languages.

Categorise keywords

The query result set is showing multiple rows for the same keyword because the composite key (definition of a unique row) of the table is still the combination of country, date, device, keyword, search_type and site.

select 
  keyword,
  case 
    when lower(keyword) like '%sql%' and lower(keyword) like '%seo%'
    then 'SQL & SEO'
    when lower(keyword) like '%sql%'
    then 'SQL'
    when lower(keyword) like '%seo%'
    then 'SEO'
    when lower(keyword) like '%supercooldata%'
    then 'Branded search'
    else 'Other'
    end as keyword_category,
  impressions
from fivetran_google_search_console.keyword_site_report_by_page
limit 1000;

The query above returns 1000 rows of the keyword and impressions columns as well as a new generated column which we named keyword_category from the keyword_site_report_by_page table.

The keyword_category column is a simple if statement which puts each keyword into a group. The first when condition is:

when lower(keyword) like '%sql%' and lower(keyword) like '%seo%'.

This says if the lowercase of the keyword column contains the value sql and it contains the value seo then put this row in the category SQL & SEO.

We need to aggregate the table so it shows us a single row for every unique keyword_category. We also need to tell BigQuery how to combine multiple rows, to do this we will use the sum function.

We need a new query which we ran below:

Categorise keywords

select 
  case 
    when lower(keyword) like '%sql%' and lower(keyword) like '%seo%'
    then 'SQL & SEO'
    when lower(keyword) like '%sql%'
    then 'SQL'
    when lower(keyword) like '%seo%'
    then 'SEO'
    when lower(keyword) like '%supercooldata%'
    then 'Branded search'
    else 'Other'
    end as keyword_category,
  sum(impressions) as impressions_total
from fivetran_google_search_console.keyword_site_report_by_page
group by 1;

This query groups on the keyword_category column and sums the impressions column to produce a new column called impressions_total.

We now have a query we can use to build a bar chart!

Click on “EXPLORE DATA”

Create a chart

Choose the bar chart option on the top right. Move the impressions_total field to replace the Record Count field in the Metric panel on the right.

Datastudio make a chart

What a beautiful chart!

Categorise keywords

Click save on the top right.

Categorise keywords

Click share. Categorise keywords

Click add. Categorise keywords

Rename your report. Categorise keywords

Now share with your colleagues! Categorise keywords

That’s it.

In this blog post series, we set up BigQuery, learnt how to load data into BigQuery with Fivetran (with no coding), we wrote a SQL query, made a plot and then learnt how to share it!