Keyword Tracking Report Using Looker Studio & Google Search Console Data

Free Template Newsletter: exponentialgro...
Consulting: johnreinesch.com/
Marketing Agency: exponentialgro...
We recently updated our keyword tracking report using Looker Studio to help give us a more comprehensive view of how our top keywords are performing.
This has been a great alternative to traditional keyword rank tracking tools that only give a snapshot of rankings but not our click and impression data.
We are going to continue to add to this keyword report over time.
#lookerstudio #seoreport #keywordresearch

[@user-pw7ed9id1y]

Many thanks, John! Very useful report

[@johnreinesch]

Thank you!

[@ShariAzama]

Hi, I'm curious to know if you offer a way to purchase your report system via a one-time fee type of purchase, as opposed to an ongoing contract subscription for consulting? I really like how your report on Looker Studio works.

[@clemchooi]

same to me too

[@johnreinesch]

Shari, thanks for the call yesterday. Going to update a few things in the report shortly.

[@johnreinesch]

@clemchooi shoot me an email and I can get you setup. [e-mail]

[@ShariAzama]

@@johnreinesch Amazing, thank you so much. I really appreciate all your help!!

[@FernandaAlva-rl9fp]

Excellent template thanks! Why am I seeing different metrics here than in GSC or SEMrush. For example when I filter by keywords below position 4 in GSC it gives me 397, SEMrush shows 534 keywords in top 3 and when I add my data to your template it shows 2,964 keywords in the top 3. Can you explain why this might be?

[@johnreinesch]

In the GSC interface they often don't show you all of your keyword data. When you use the API with the Looker Studio connection, you typically unlock more keywords than the GSC interface will show you. Semrush doesn't track keywords in the same way. Google has your query and click data, but they block some of it. Semrush will scan the search results for all of the keywords in that they are tracking and note when they see your website rank. As long as you use one source consistently, you'll be able to see the trends over time. If you are on the Semrush Guru plan, they actually have a Looker Studio connection, and you can pull the keyword data from there and make some additional reports. I'll do a video on that soon. Thanks!

[@IJ-fo7kf]

This is amazing! thank you! I'm just curious what formula or setting you used to make it possible to click on each keyword and get the data based on the individual keyword? it would be very helpful! thanks :)

[@CesarToloza-t6i]

AMAZING, THANKS

[@johnreinesch]

Thank you!

[@milochamp1586]

This would have been good if you had a column for the search volume for targeted keywords either imported from google sheets or keyword planner

[@johnreinesch]

The goal of this template was to use only one free data source (Google Search Console) so it is accessible to everyone without paid tools and less complication. You can use impressions from GSC to get a gauge on how often the keywords are being searched on the "Keyword Trends" slide. I have another version of this report that connects to keyword hero to show keyword conversion data and then also to ahrefs for additional keyword metrics. I might release that one at some point if I can find a way to make it so people can use it without having to pay for subscriptions. If you have any other template ideas let me know and I'll see if I can build it. Thank you!

[@milochamp1586]

@@johnreinesch Thanks for the prompt reply. Just a question. Since impressions won't determine the power or popularity of a keyword if the page is on 6th or 7th page of SERP, is there a way to add a static column to your templates to have certain targeted keywords manually imported to your Looker studio template at the start of the campaign from one of the Google sheets column with volume used at the initial keyword research phase and all keywords picked up by GSC to appear N/A in that column. So a client can see we are on track with what we promised at start when it comes to keyword ranking. Hope this makes sense.

[@khushalbhadra]

Can you help us how to set this up for our domains? I tried and Blend data showed some errors

[@ryangreene6022]

Thank you, how can I get that report? I don't see any link to make a copy of it.

[@raycodefixer]

Great dashboard John. Is there a way to filter out brand queries from the "Keyword Position Overview" table?

[@johnreinesch]

Great question, yes you can! Here is a quick video: www.loom.com/share/dd2ad2c6440043089f0a3b2e95eed51c

[@raycodefixer]

@@johnreinesch Brilliant, thanks for the reply. This worked perfectly.

[@anshulmady2211]

My Impression by Position is giving error. Which dimension or metric do we need to use in there?

[@beawareff3515]

Hello plz reply, John

[@GSicKz]

Thanks a lot for this template John! At 1:56 I do find your data strange though, how can your keywords on position 1 only give you 843 impressions while your keywords positioned 51-60 give you 8258 impressions? This must mean that you rank very well for very low traffic keywords and rank in these higher positions for very high traffic keywords, right? For my clients, this graph looks quite different! :P

[@johnreinesch]

Yes exactly, for this website we are only doing well for low volume keywords. And we rank for a higher quantity of keywords in the page 2-10 area, that we need to drive up to page one. Great to hear your chart looks more full in the top positions, that’s a good sign you are crushing it. Keep it up!

[@louiseamor3299]

Love this, thank you so much for posting. For the page 4 chart , Keywords with Low CTR how do you get the Average Position to show? Are you blending the URL and Domain GSC data sources? Then filter for page 1 positions?

[@johnreinesch]

Great question. I recorded a video for you here: www.loom.com/share/f2b8bc4c10d24c249129bc8c4e6542cc Here are the resources I mentioned: exponentialgrowthmarketing.substack.com/p/seo-ctr-analysis-template docs.google.com/spreadsheets/d/1THy7hMkFt_R0OEVTaYhCWfq3-IMdiomin8TseXmloLU/copy

[@daveb7735]

Hi John, @5:20 the formulas all show up as invalid (position without index, impressions and sort)

[@johnreinesch]

Hi Dave, shoot me an email if you can: [e-mail]. I’ll record a video for you that shows how to fix that. That formula and chart doesn’t get copied automatically, you have to manually fix it.

[@RasmusHentila]

how did you tackle the 1000 query limit in gsc?

[@johnreinesch]

That's one of the advantages of Looker Studio, when you connect Google Search Console, there is no 1,000 query limit. But you don't have to do anything special. Just connect GSC using the standard connector and you'll unlock more than 1,000 keywords.

[@RasmusHentila]

@@johnreinesch fantastic, did not know, thx👍

[@johnreinesch]

@@RasmusHentila no problem!

[@RasmusHentila]

@@johnreinesch Since you answered so quick here comes another one😂, regarding the incorporation of business goals/actions in the content you create for your clients. How do you achieve this? with CTA's/lead magnets and internal linking further down the funnel?, we are currently setting up a business goals section for in looker studio.

[@johnreinesch]

​@@RasmusHentila Yes good question. I like to determine the goal of the content first. Some content may be more bottom-funnel like "How to choose the best software/service" or "top 10 software/service". In those types of content, I'll typically do a meeting CTA for services or a free trial/demo if it is SaaS. In the rest of the content that is more informational, I like to experiment with lead magnets and build up an email list. Then with the email list you are building up, you can try to nuture them in different ways. You can also have SDRs reach out to some of the most qualified ones if this is a B2B business. So for KPIs for content, I would first think about the goal for that piece of content. Is it bottom funnel leads or more lead magnet focus or maybe it is just awareness and traffic focused. Then once you know the goals you can make some charts that show some of the metrics (typically bottom funnel form submissions, lead magnet form submissions, and traffic) and filter the charts to the content in that category. This is a great question so I am going to add this to my list to make a video about. Thanks!

[@isagive]

what i really want to know is keyword > page > conversion count got a tutorial for that?

[@johnreinesch]

Let me know if this one solves that: kzbin.info/www/bejne/l4mUpnV3hauosJY

[@MeesOoms]

Is it still possible to get the template? Ofcourse i can subscribe to the newsletter if it isn't out yet :).

[@johnreinesch]

Here are the instructions and the template: exponentialgrowthmarketing.substack.com/p/keyword-tracking-report-template If you want to sign up for the newsletter you can do that here: exponentialgrowthmarketing.substack.com/

[@FunkoToys]

Hi John, I'm trying to download report in your substack, and I'm getting an error that the template is not available. Could you share? It will be of great value. Thanks =)

[@johnreinesch]

Let me check why it’s not working. Shoot me an email and I’ll email it to you directly: [e-mail]

[@АлексейДмитренко-й7ю]

Hi. Great video! When we can try this fantastic report?)

[@johnreinesch]

Sign up here: exponentialgrowthmarketing.substack.com The keyword tracker is the next template we are giving away. Should be going out in just a couple of weeks.

[@richardsilva3532]

@@johnreinesch Just following up. Any chance you're sharing this template before the end of the month? It looks great! :)

[@KK-qc2qs]

can this be used for ecommerce website with millions of users

[@AhmadChaidir]

Hi John, this is really good, this something i'm looking for. Are you sharing this template for free? would love to use this. Thanks

[@johnreinesch]

This one should be sent out through the newsletter later this month: exponentialgrowthmarketing.substack.com/

[@AhmadChaidir]

Oh great! Already subscribe to the newsletter. Cant wait

[@GSicKz]

Hi John, I copied the report and added the data source but I cant get the keyword position overview to work - i think it has to do with the fact that the data source needs to be a blend of some sort - could you also please make an explanation on how to do that?

[@johnreinesch]

Try this: www.loom.com/share/225128bb2e154a04b0cb0d18e5bf5f5e Here is the formula as well: CASE WHEN Average Position =4 and Average Position =11 and Average Position =21 and Average Position =30 THEN "5. 30+" END

[@GSicKz]

@@johnreinesch Hi John, it worked now, thank you!

[@FaberHuyse]

Hi John, are you still sharing this template? :)

[@johnreinesch]

Yes I am! exponentialgrowthmarketing.substack.com/p/keyword-tracking-report-template If that doesn’t work, let me know.