I really like Google Data Studio because it’s a versatile tool that can be used to visualize and partially manipulate data. In many cases, I replaced a classic Excel report with a Data Studio dashboard, because I find it easier to share a report within an organization.
I also use the official Google Search Console connector for SEO analysis and today I’m gonna show you how I manipulate Search Console data using some of the functions of Data Studio.
If you try to use the Search Console connector you’ll see that you can choose between two different datasets:
- Site Impression
- URL Impression
The main difference between the two datasets is that the Site Impression dataset contains the Average Position metric, while the URL Impression has the Landing Page dimension.
1 Calculate the accurate amount of Impressions
If you decide to use the URL Impression table you could notice some discrepancies in the data. This is can happen because clicks, impressions and CTR data are aggregated on different levels. URL CTR and URL Clicks are aggregated by page while the column Impressions are aggregated by Query.
E.g. if you two URLs are ranking for the same query you’ll have these data:
URL Clicks: 1
URL CTR: 100%
In this case, you can’t calculate CTR by multiplying URL Clicks and Impressions because two URLs generated an Impression. But there is a simple workaround to calculate the number of Impressions generated by every single page.
You have to add a custom field to your data source and divide the URL Clicks by the URL CTR and you’ll get a new column that you can name “URL Impressions“.
2 Branded vs Non-Branded traffic (+ folders grouping)
If you want to have a clear overview of the organic visibility of your website you should split the brand and non-brand traffic. You can easily do that using the Custom Fields.
Open the data source dashboard by clicking on the pencil near the data source name. Then click on Add a Field and there you have to paste the code you can find below.
Basically, I combine a CASE statement with regular expressions to “label” automatically every single query. Inside the regex, I put all the brand names and the misspelled versions.
WHEN REGEXP_MATCH(Query,'.*brand.*|.*misspelling.*') THEN "Brand"
This custom field should be a dimension with a text format and it can be used as filter or breakdown dimension.
You can use the same logic to group different pages of the same folder. In this case, you have to replace Query with Landing Page and write all the regular expressions (!!you can use multiple WHEN in a CASE statement!!).
3 # of unique Search Queries
The COUNT_DISTINCT() function counts the number of unique values in a column. It’s very helpful when I want to know for how many different queries a URL is shown.
In the screenshot below, I created a pivot table (Raw = Landing Page, Columns = Date, Metric = # unique queries)who shows the development of the # unique search queries per page.
4 Detect Keyword cannibalization
I also use COUNT_DISTINCT() to detect queries where two or more pages of the website are competing against each other (Keyword Cannibalization).
The formula is exactly the same we used for the number of unique search queries. The new filed must be a number and it can be used as a filter (e.g. you could list in a dropdown menu al the queries who are affected by keyword cannibalization).
This function isn’t perfect yet, because sitelinks are counted as a different page (you could at least exclude html anchor using a REGEXP_MATCH function nested inside the COUNT_DISTINCT)
5 From daily to monthly data
If you want to aggregate the data per month and not day, you have to open the data source dashboard.
Then you have to make a copy the actual date column and change the date format from YYYYMMDD to YYYYMM. With this method, you can keep both options available and decide later on which one you want to use.