FREE Keyword Tracking Report - CUSTOM Built in Google Sheets
Get 300 checks per monthabsolutely FREE!
No credit card needed. No strings attached.
As I mentioned there's two parts to this report one is the data studio template...
as I mentioned there's two parts to this report one is the data studio template which I'm going to get to in a minute the other one is a Google sheets automation file which pulls data from SCM Rusch and Google Analytics and what it does the crosswalks organic traffic data with keyword ranking data there's no other report on the web that does this it's very powerful and I apologize because this video is going to be a little bit dry but I have to show you how to use it and how to run it in order for you to do it effectively so that's what this video is going to be about from here on out so this first page here walks you through how to do it the getting started tab if you don't have SCM RS you really needed I'm going to be using the API but if you don't want to get the API because it's 400 bucks a month you just need to get it it's an incredible tool I strongly suggest that you can buy through this link right here you also need Google Analytics and then super metrics which is a free tool that an add-on to Google sheets which is going to allow us to automatically pull api's from analytics and SEM rush so anything outlined down here in red these cells you do not need to touch it's
Automatically populated by this sheet but anything in green any of these...
automatically populated by this sheet but anything in green any of these things highlighted in green like these here you need to fill in manually so the first thing that we're going to do is we're going to put well if you don't have again if you don't have super metrics installed install it once you have installed what we're going to do is we're going to navigate here to the ga mo m tab and we're going to go to cell b1 so I highlighted here in green for you and then what you're going to do is you're going to come up here to add-ons you're going to go to super metrics and you're going to click launch the sidebar and you can see here if you're running this report month over month or for multiple websites you're going to have data in here that's fine you can ignore that and what you can do is make sure that you're on the right source and again if you have data in here this screen is going to pop up you're just going to go to modify make sure that it's pulling from the right source make sure that's a website I'm going to do this for web wrist so you can see here all the other websites just select the analytics property that you need and then the date so I'm going to run this for March and then you're going to make sure that it's set to previous period of of same length and then percentage change
Make sure the metric is sessions here sorry I'm skipping around split by...
make sure the metric is sessions here sorry I'm skipping around split by landing page passion that make the share the segment is for organic traffic no filter and then all this is good and then we're just going to click apply changes and going to run here on the side and that's also going to run up here I'm just going to quickly fast-forward this video while it does it so you can see up here this query was completed successfully I'm just going to close this you can see the sheet is still loading because there's a formula over here in a rave filled formula which is taking the landing page path from analytics and mapping into the full URL but we'll talk about that in a second too next we're going to go to GA year-over-year so this is looking at year-over-year data we're going to do the same thing we're going to open up super metrics launch sidebar and again what this is doing this is pulling data from Google Analytics and putting it into the spreadsheet here calculating the sessions and the percentage change so we're going to do the exact same thing here except and click modify on the dates I'm going to have it the same day 301 for marks of 331 and then same dates a year ago and then I'm going to click apply changes and I'm going to let this run query completed successfully
I'm going to come back here to the getting started tab that's done you can...
I'm going to come back here to the getting started tab that's done you can see here it pulled through all the pages with organic traffic we do not need to change that now we're going to get the SEM rush data so if you've run this report before this tab here in red called SEM rush or organic if you are using the API you need to clear this out so anything from cell let me just close this anything from cell to and down we need to delete okay so I just deleted that I'm going to let it load now again this is a little bit complicated if you have the esta key down here you're going to fill in the screen you're going to put the a peak API key in here if you do not have the API you're going to select manual and if you select manual I'll show you how to do that one second but all you're going to do is you're going to put in your domain name here the home page with no trailing slash and then the start date to run in the end date to run so I'm going to run this for let's say the campaign began in January if I'm running the server client the end date is March again I'm using the API so now what I'm going to do is I'm just going
To come up here to where it says monthly reporting and I'm just going to click...
to come up here to where it says monthly reporting and I'm just going to click import SCM rush and it's going to say running script up here just leave it until this is completed and everything is pulled through so this script is still running but I'm just going to quickly show you if you do not have the API which I'm sure a lot of people will not then your what you're going to do is you're over here going to select manual I'm not going to do it because the script is still running but then what you're going to do is so if you're running this for a three-month period then you're going to slide January February and March here from the drop-down and what you're going to do is you're going to put in the website into SDM rush and then click on organic research over here one in the UK let me just get back to the US and then what I'm going to do is I have to manually export this data so what I'm first going to do is I'm going to go to the first month which was what I say January I'm going to click here and what this does this pulls down the historical data from January and then you're just going to go to export export all as a CSV and then once this pulls down what you're going to do is you're just going
To open it and you're going to copy it and you're going to paste it all the way...
to open it and you're going to copy it and you're going to paste it all the way in here into the campaign start month so again so the campaign start month one January I'm just going to paste over this then what I'm going to do is I'm gonna get the data from the last month so if I'm running this for March I'm going to pull the data for February and I'm going to download that and I'm going to export it and I'm going to paste it in here and then I'm going to do for the current month so I'm running the support for March I'm just going to click on March I'm going to export that I'm going to pull it out from the CSV I'm gonna copy it and I'm going to paste it I'm just going to cancel his cousin slow and mush it down okay so the script is still running here I'm just going to give it ok so you can see the script is finished it's still doing some calculations up here but we can go ahead and start moving on to the next section so the next thing you're going to do is you're going to come up here to monthly reporting and we're going to aggregate the Google Analytics in SEM rush data so I'm just going to click this where it says aggregate data and I'm going to let it run the script now that that's done what we want to do is we want to enter
Some target pages so what I have here is you just have to enter the path which...
some target pages so what I have here is you just have to enter the path which means without the URL in here so what these target pages are is these are the pages that we really want to focus on for SEO these are the pages that we really want to track in terms of traffic in terms of keyword rankings so you're just going to enter the page path and here you can enter as many as you want and then it'll pull through here how many you have in there and then finally what you're going to do is you're just going to go ahead and select the month that you want this to be run for and after that all the key all everything is calculated so everything in green here these are the reports that come through so this is a visibility report this is looking at keyword rankings top three top ten total this month last month campaign start really cool stuff you can check it out visibility this is looking at page URL keyword top keywords keywords that are in trouble this one is looking at top landing pages month over month and then it's crosswalked with the data from sem rush and then these are the target pages this is month over month and year over year changes and what these are looking at is your target
Pages and how those are performing and it's pulling through keyword ranking...
pages and how those are performing and it's pulling through keyword ranking data if there's any so some of these pages you can see have no keyword rankings because they're not really getting any traffic and they're not ranking but the ones that are you can see where they're going and how they're performing over time so this alone in itself is a really good standalone report but how we use this is we actually feed this through into our data studio template and we push them into the target pages report here we push them into the organic visibility report which I'll talk about in a second so now let's come over here let's talk about this data studio template and how you can run it so the first you
Get 300 checks per month absolutely FREE!
No credit card needed. No strings attached. 👍