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:
These are the columns available in each of the 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:
We’re going to query the table
This table has one row for every unique combination of
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:
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.
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
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
impressions columns as well as a new generated column which we named
keyword_category from 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
We need a new query which we ran below:
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
We now have a query we can use to build a bar chart!
Click on “EXPLORE DATA”
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.
What a beautiful chart!
Click save on the top right.
Rename your report.
Now share with your colleagues!
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!