Since Moz released the new Google Data Studio Connectors for STAT, you may be wondering how best to implement them for your reporting strategy. My colleagues at Path Interactive and I really love how detailed your reports can be in STAT, and it is a godsend to finally pull these reports clean and effective into Data Studio (the tool we use for our own reports).
While the connector "Historical Keyword Rankings" reports about it Rank over timeIt may not be as obvious as it should be reported Change of rank over time. In this post, I'm going to walk you through step by step reporting on rank changes, as well as some other filtering and reporting tips, while using the connectors in Google Data Studio.
If you're new to STAT but want to know how it fits into your SEO toolkit, you can take a tour of the product. Click the button below to set one up!
Learn more about STAT
Connect your data source
Before you begin, there are a few things you need to identify in order to set up the connector: your STAT Keyword API Key, Project ID, and Site ID. If you don't already know how to identify these using the STAT API, see the STAT documentation here for more information. Now that you've identified these, it's time to connect your data source.
We're going to do something extraordinary here, but stay with me – you'll see why in a moment!
In this step we are connecting two instances of the same source. Since our goal is to compare the change in rank over time, we use the same source twice to identify these deltas.
When you set up your connector, name the source so that you can easily identify it:
In my case, I usually go with something simple like "(client name) STAT Keyword Connector". When this is done, repeat the above step but rename it differently e.g. "(Client name) STAT Keyword Connector 2."
Finally, make sure that the metrics you want to compare have unique names for each connector. To do this, go to your data source. Click on the name of the metric so you can rename it and then rename it to something unique. In this case we do this for "Google Base Rank" since we are comparing the ranks, but it can also be done for "Google Rank" if we want to compare that. Again, I want to keep it simple: Name it "Google Base Rank 1" for the first data source and "Google Base Rank 2" for the second data source. When all is said and done it should look something like this:
Build your spreadsheet and mix up data
Now we're getting a little more technical. By mixing the data from the two connectors, you can compare two instances of rankings. Your final result will create a table that shows the ranks of two specified dates and their change in rank. The five-step process is as follows:
- Mix the data from keyword connectors one and two
- Add your common metrics for the two sources (keyword at least, but you can also add location, device, market and search volume).
- Add the metric you want to report (Google Base Rank and / or Google Rank).
- Set date range
- Apply the "No Null" filter
1. Mix the data from keyword connectors one and two
The first step is to mix up the two connectors so you can compare two instances of ranks.
First you need to create a new report or switch to one that has already been set up. Next, choose your data source. Here you select the first instance of the source that you set up earlier (when you start a new report you will be prompted to add a data source immediately). Once selected, click Merge Data on the right side of Google Data Studio under the data source.
This will take you to the data source blending tool. From here you can add another data source which will be your second instance of the connector.
2. Add your general metrics
Now that you've mixed both connectors, it's time to set your metrics. Above you can see "Join Keys". This refers to what will be the same for both instances. Therefore you want to insert at least "Keyword" here. Feel free to play around here and add different metrics.
Note: We'll get into this later. However, if you plan to filter different charts by a specific day or location, add them here.
3. Add the rank type you want to report
After you have set your metrics under "Join Key", select the metrics that are unique for each date. Depending on what you want to compare, select under "Metrics" "Google Base Rank", "Google Rank" or both. You can also insert "date" here if you want. When you're done, click "SUM" next to the metric name and change it to "MIN". You will see why in a moment.
At this point, your mixed data should look something like this:
4. Set the date range
Now you need to define the two date ranges that you are going to compare.
To do this, set your first date under the first connection: Under "Date range", click "Custom" and then click the field to select your date. Here you might see that there is an option for two dates, but for this solution we are using the same date for each connector.
In the end, it will be something like "Connector 1" selected as the "Start Date" and "End Date" as the first of the month and for "Connector 2" as the "Start Date" and "End Date". will be the last of the month. This essentially increases the rank for both the first and second instance so you can compare the two.
5. Set the filter "No Null"
The last step in setting up your blended data is creating a "no null" filter. If the Keyword Connector reports ranks that your website is not ranking for, it will return as "zero". To avoid flooding your data with fluff, you need to create a filter that removes instances of "null".
First, click on "Add Filter" below where you have selected the date range. Then click on "Create filter" below. Set the filter's parameters to "Exclude"> "Google Base Rank 1 (2)"> "Is Zero". Make sure you give the filter an identifiable name, e.g. B. "No Null". It should look like this:
Apply a rank change to your report
Now you can create a new field that reports the change in rank by creating a calculated field to find the difference between the two ranks.
Under "Dimensions", select "Add Dimension" and click "Create Field". You can call it a "change of rank". To create the field, enter "Google Base Rank" and your instances will be shown by each connector. To create the calculated field, select "Google Base Rank 1" and subtract it from "Google Base Rank 2". So it should look something like this:
Click Apply, and now your rank change should be calculated!
There is also an additional way to achieve the same result, but with some drawbacks, e.g. For example, you cannot name the header and you cannot filter or sort your change in rank. The advantage of this approach is that it is easier to set up at first because you don't really have to mix the data. However, if you do not set up the mixed dates, the visible starting rank also expires. Set a custom date range under "Default Date Range" in your editing view that you will report on. Here you can then set a comparison date: If you look back on a month, you can set this on the first. If you choose this option, it should look like this:
Go to the "Style" tab where you can change the comparison under "Metrics" to "Show absolute change". You can also change the colors of your positive and negative arrows to show the movement more accurately (you can see from above that the “negative” change is a green arrow that is red by default).
Applying filters to your dataset can be extremely useful in understanding your data! Using filters with the connector, you can segment rankings for a specific location or create charts showing rankings for a specific set of keywords that you have set up using keyword tags.
Check out this report I made as an example. Within STAT, I created keyword tags for destinations determined by zip code. Then I was able to create a filter for each chart targeting that keyword tag:
Setting up filters is extremely easy. First switch to edit mode. Next, scroll down the side until you find "Filters". Then click "Add Filter" under Filters> Table Filters. This will take you to the filter picker. Click on "Create Filter" below. Here you can set the parameters for the filter you want to display.
Some of my other favorites include filtering to see only the top pages (filters out non-relevant and high ranks), using the keyword tag filter shown earlier, and filtering by location. But you don't have to stop here! As you add the additional dimensions available to you in the connector, you can use the filter to show desktop or mobile features, for example, or to see the performance of your keyword rankings in different markets.
Mix your Google Analytics, Google Search Console and STAT data
One of my favorite uses for the connectors is the ability to mix the data with your Google Analytics and Google Search Console data. By merging this data, you can directly link keyword rankings to various metrics such as clicks or target achievement.
At this point, you are probably a professional with mixed data, but for reference only, the mixed data should look like this:
A couple of things to keep in mind: The order in which you insert the connectors is important. I've found adding the STAT connector works the best (i.e. if you put Google Analytics first, you will get a report with the infamous "not found". Keyword). Also, when you are pulling Search Console data to match against your other Connectors, using "Query" has the same effect as "Keyword".
The result would look something like this, but you can always edit the design to your liking!
Now you can go further and match URLs, but doing so requires some RegEx.
You rename the Google URL field to STAT and the Landing Page field in Google Search Console to match the convection of the URL structure in Google Analytics by removing the domain part of the URL. To do this, go to your data source for every STAT connector and every Google Search Console and click on "Add field" in the upper right corner.
Next, enter the following RegEx for the STAT connector:
REGEXP_REPLACE (Google URL, ". * (\.) Com", "")
And for the Google search console:
REGEXP_REPLACE (Landing Page, ". * (\.) Com", "")
Remember to give them a name to distinguish them from the standard field. I use "Landing Page (no domain)".
When creating a report, use these new fields to ensure consistency throughout the URL structure so that if you select them when you merge data, they will match.
Use this method in the same way as above to get the results you want in getting data from all three ports to match each other! In the end, you should be able to figure out which keywords are ranking for which URL, and have lots of sessions or clicks, and target achievements, or some other combination.
Well there you have it! Hope this was helpful to you. If you have any further questions, feel free to comment below or find me on Twitter @ianpfister. Have fun reporting!
Take the 2020 Moz Blog Reader Survey, which asks who you are, what challenges you are facing, and what you want to see more of on the Moz blog.
Take the poll