Organic Keywords Trend Report – Google Search Console

TL;DR

• Knowing how many queries you are ranking for over time can be a great measurement of Organic Success.
• Google Looker Studio was struggling to natively do this, so BigQuery to the rescue!
• Checkout this free process/template to create your own Google Search Console Query Trend Chart.

I’ve written a ton about how much I like Google Data Studio (not Looker). There is so much you can do to slice and dice data to get the answers you want. I recently was trying to get a report set up to show # of ranking queries over time. This should be simple, but like lots of times with Data Studio you run into data limits and sampling. Plus when playing with too much data and filtering it can be painfully slow. I kept running into an issue of not being able to filter the Queries by Position and I believe that is because it wasn’t actively in the table I was showing. Is that a thing? Idk but it works this way I’m gonna roll with it.

As always, BigQuery to the rescue. I’ve written how to set up the integration for Google Search Console Bulk Data Export. It’s much faster with the integration accelerator and you can have so much more functionality. SQL may seem complicated, but over time it becomes natural. There are tons of solid SEO SQL resources out there and ChatGPT can be super helpful.

Right now this is set up just to show the total number of queries ranking in Google Search Console from positions 1-30. It’s broken down by 1-10, 11-20, 21-30. The reason I did it this way is because the more you query the more it costs in BigQuery, even though BQ is very cheap. I like to keep it focused and as inexpensive as possible. If you want more queries, just adjust the parts below that I’ll call out.

As for further filtering, I will get working on that, but for now, I wanted to share this because I think it can be a nice way to show true keyword ranking trends over time. Basically the SEMrush Organic Keyword Trends Report but with real queries.

Setup BigQuery – Google Search Console Ranking Queries

This is dependent on you already having the Google Search Console Bulk Data Export (linked above) setup.

1. Navigate to BigQuery
2. In the side nav -> BigQuery -> BigQuery Studio.
3. Find your project (what you named it in GSC), expand it and look for the ‘search console dataset‘.
4. Then you will see 2 tables (URL & Site) – More info on URL vs. Site data.
5. Create a ‘New SQL Query‘ using the code below.
6. Run that and preview the data to ensure it’s working as expected (see image below).
7. Click the Schedule button at the top of the editor and then Create a New Scheduled Query. (See my setup below).
8. Choose how frequently you want it to run (I chose on demand just to be in more control) but the beauty is you can fully automate this and have it set to daily or weekly!
9. Set a Destination Table
   • Choose your Datasset (ID we have been working with).
   • Create a TableID (name it whatever, we’ll need this for Looker Studio).
10. Choose to Overwrite Table
11. Save

SELECT
  FORMAT_DATE('%Y%m', data_date) AS SortableYearMonth, -- Adds a sortable YYYYMM format
  COUNT(DISTINCT CASE WHEN sum_position BETWEEN 1 AND 10 THEN query END) AS NumberOfQueries1_10,
  COUNT(DISTINCT CASE WHEN sum_position BETWEEN 11 AND 20 THEN query END) AS NumberOfQueries11_20,
  COUNT(DISTINCT CASE WHEN sum_position BETWEEN 21 AND 30 THEN query END) AS NumberOfQueries21_30
FROM
  `INSERTPROJECTID.searchconsole.searchdata_url_impression`
WHERE
  query IS NOT NULL
  AND sum_position <= 30 -- Adjusted to capture the upper limit for the last group
GROUP BY
  SortableYearMonth
ORDER BY
  MIN(data_date) ASC;

Setup Google Data Studio – Google Search Console Rankings Stacked Trend

Now that you have the query running as expected and getting the number of queries by month sorted by position grouping you can pretty it up in Google Looker Studio. Visit this template and choose the Query Position page. You can make a copy of that page or the whole thing, I think it’s sorta useful.

1. In the top right 3 dots, click and make a copy.
2. From there you can add your data sources, examples below.
   • Under Resources -> Manage Added Data Sources.
   • Add New Data Source.
   • Select BigQuery.
   • Choose Your Project, Dataset & Scheduled Table that you set up above.
   • Save data.
   • Ensure that the Looker Table is using this new data source.
3. You should now see your data from BigQuery populating.
   • If the metrics from below have changed you may have to manually update them similar to what I have below. I haven’t figured out why changing the data source sometimes changes the table metrics.
4. I changed the names of the Metrics in Looker Studio just to clean it up. Probably should have done that in BQ, but oh well.
5. Customize this table any way you want it!

What’s Next?

So we set this as ‘On Demand’ to control costs from BQ. If you don’t care or aren’t worried you can schedule it to run automatically. But if you want to be safe you can visit BigQuery anytime and go to the ‘Scheduled Queries’ section and run it whenever you want the data updated. Since this is pulling in data from the current month it will look like you aren’t performing compared to previous months. Don’t worry, when you update the data over time it will look better.

As I mentioned I will start digging into being able to filter this even more. Like ‘section of site’ or ‘query contains’ so be on the lookout for that.

If you have any edits or tweaks to this definitely reach out to me on Twitter or LinkedIn. And be sure to check out my other Google Looker Studio Templates, I think they’re pretty useful!