TL;DR
Keyword-level data isn’t gone, it’s just harder to get to. By using Google Sheets to marry the data from Search Console and Google Analytics into a sheet, you’ll have your top keywords and landing page engagement metrics together (for free!). It’s not perfect keyword-level data, but in 7 steps you can see the keywords that drove clicks to a page and the organic engagement metrics for that page, all together in one place. The Google Analytics Add-on for Google Sheets will pull organic landing page engagement metrics, and the Search Analytics for Sheets Add-on will pull the top queries by landing page from Search Console. Then, use VLOOKUP and an Array Formula to combine the data into a new tab that has your specified landing pages, the keywords that drove clicks there, and the specified engagement metrics.
What do you mean you don’t know which keyword drove that conversion?
Since the disappearance of keyword-level data in Google Analytics, SEOs have been struggling to tie keyword strategies to legitimate, measurable metrics. We put much of our time, resources, and research efforts into picking the perfect keyword theme, full of topically relevant terms that leverage new semantic strategies. We make sure to craft the perfect metadata, positioning our top keywords in the right place in the title tag and integrating them seamlessly into the meta description, but then what? We monitor rankings and look to landing page metrics, but all of our data is disjointed and we’re left to extrapolate insights based on a limited understanding of how our themes are truly performing.
There is good news, though! Keyword-level data is still there — it’s just much harder to get to given the structure of existing platforms. If you’re like me, you have your landing page metrics in Google Analytics, your keyword click data in Search Console, and your keyword themes in a manual program (probably Excel). Given the way Google Analytics exports data, the way Search Console separates keywords and landing pages, and the nuances you’ve applied to your own keyword theme documents, it’s difficult to marry all of the data in a way that gives you actionable insights and real-time data monitoring capabilities.
Difficult… but not impossible. Enter: Google Sheets. In 7 easy steps you can pull all of this data into one sheet so you can see your keyword theme, the keywords you’re getting clicks for, the page ranking, and any organic metric for that page (think engagement metrics, conversion metrics, revenue metrics, etc.), all in one place! You can monitor keyword opportunities within striking distance, whether the keywords you want to rank for are actually ranking, and what terms and themes are driving the majority of your revenue or conversions. At the end of the day all of this works to give you actionable metrics you can monitor and change through keyword strategies. It’s much easier than you may think, and the steps below will get you started.
Follow this guide to build out a basic Google Sheet that ties Search Console, Google Analytics, and your keyword theme into one place for a few pages, and then you’ll be well on your way to building out automated sheets that give you greater insight into keyword-level data!
Step 1: Get the Google Analytics and Search Analytics for Sheets Add-ons
The Google Analytics Add-on will allow you to pull any metric from Google Analytics into your spreadsheet and Search Analytics for Sheets will pull data from Search Console. Pulling from these two sources will be the key to combining the data from Google Analytics and the Search Analytics report in a meaningful way. Once you have a new sheet open and you’re in the add-on feature, finding and installing Google Analytics and Search Analytics for Sheets should be pretty straightforward. Also, both add-ons are free.
Step 2: Create Google Analytics reports
Once you’ve installed the Google Analytics add-on, you'll find "Google Analytics" in your menu. Hover over Google Analytics and select Create new report to get started. After the sidebar menu pops in, select the Account, Property, and View that you want to pull data from. You will also be able to name your report (see note below) and then select Create Report. You do not have to worry about the metrics and dimensions at this point, but that will come later.
Note: At the end of this article I have a template you can use to combine the data from Google Analytics and Search Analytics. If you want to use the template, make sure you name this first report Organic Landing Pages Last Year. I will also walk through the formulas and functions used in this article, so you don’t have to rely on the template, but the nomenclature of each tab must be consistent to use my exact formulas. There are plenty of opportunities to rename the report and tabs, so don’t stress if you miss this part and name your report something different; just know that if at the end the template isn’t working, you should double-check the tab names.
Step 3: Configure your Google Analytics reports
The Report Configuration tab you now see as the first tab in your sheet is where you can configure the data you want to pull. I highly recommend familiarizing yourself with this functionality by watching this quick, five-minute video from Google as an overview on how to generate reports from Google Analytics in Google Sheets. Listed below are the fields being used for this report, and you can find an extensive overview of what all of these fields mean and the metrics you can use within them here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on.
Note: If you prefer to simply fill in your sheet and read the details on each field configuration later, you can paste the cells below into your table at cell B5 (just double-check it looks like the screenshot above) and skip down to the last paragraph in this section, right after Segments.
| 395daysAgo |
| 365daysAgo |
| ga:sessions, ga:bounces, ga:goalCompletionsAll |
| ga:landingPagePath |
| -ga:sessions |
| sessions::condition::ga:medium==organic |
Report Name:
The name you set when you created the report. This can be changed, but note that when you run your report, the tab with your report will use this report name.
Type:
This will automatically fill in “core” for you, meaning we are pulling from the Core Reporting API.
View:
This will also automatically fill in your Profile ID, which you set when you created the report.
Start Date:
To compare the last 30 days to the same 30 days the previous year, we will set the Start Date as 395daysAgo
End Date:
To compare a full 30 days last year to a full 30 days this year, we will set the End Date as 365daysAgo
Metrics:
This refers to the metrics you want to pull and will dictate the columns you see in your report. For this report we want to look at sessions, bounces, and goal completions, so we are using the metrics ga:sessions, ga:bounces, ga:goalCompletionsAll. Google has an excellent tool for searching possible metrics here (https://developers.google.com/analytics/devguides/reporting/core/dimsmets) if you want to eventually test and pull anything other than sessions, bounce rate, and goal completions.
Dimensions:
Dimensions refers to the dimensions you want to see specific metrics for; in this case, landing pages. We’re using landing pages as the dimension because this will allow us to match Search Analytics landing page query data with landing page Google Analytics. To pull the metrics you selected above by landing page, use ga:landingPagePath
Sort:
The Google Analytics API will default to sort your metrics in ascending order. For me, it’s more valuable to see the top landing pages in descending order so I can get a quick look at the pages driving the most traffic to my site. To do this, you simply place a minus (-) sign before the metric you want to sort your date by: -ga:sessions. You can learn more about sorting metrics through the Google Analytics API here: https://developers.google.com/analytics/devguides/reporting/core/v3/reference#sort.
Segments:
The last field we’re going to be adding to is Segments so we can look at just organic traffic. This is where you could put in new organic users, return organic users, or any special segment you’ve created in Google Analytics. However, for this report we’re going to use the primary organic traffic segment that’s standard in Google Analytics: sessions::condition::ga:medium==organic.
As mentioned, we want to see organic traffic to each page during the last 30 days compared to the previous year. To do this, we need to generate two reports: one with our session data for the last 30 days, and one for the session data for the same span of time one year ago. We have 2015 ready to go, so simply paste that into column C, rename the Report Name to Organic Landing Pages This Year and change Start Date to 30daysAgo and End Date to yesterday. Double-check the screenshot above matches your configurations before moving on.
Step 4: Run your Google Analytics report
You will run the report you just created by selecting Run reports under the Google Analytics add-on. We won’t be reviewing scheduling reports in this article, but it can be useful to time these to run on a specific day to align with any ongoing reporting you have. You can learn more about scheduling reports here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on#scheduling-reports.
If everything has been completed correctly so far, you should see this popup:
If, for some reason, you see a popup noting that you have an error, Google Analytics is great at letting you know exactly which field has been implemented incorrectly. Double-check your segments here (https://developers.google.com/analytics/devguides/reporting/core/v3/reference) and as long as you’re using valid formatting, you should be able to fix any issues.
Assuming everything went according to plan, you’ll see a spreadsheet that looks like this:
Step 5: Run your Search Analytics for Sheets report
Running a Search Analytics for Sheets report is really simple. Click to your empty sheet (Sheet1), and in the same place you were able to launch Google Analytics, launch the sidebar for Search Analytics for Sheets. From there, you’ll authorize the app and set the parameters of your report. Any metrics that I updated are highlighted in the screenshot below, but you want to group by query and page, aggregate by page, and have the results display on the active sheet. The default for Search Analytics for Sheets is to pull from the previous 90 days, but you can adjust this to display whatever makes sense for your website.
As long as everything runs correctly, you’ll see your top search queries, landing pages, clicks, impressions, CTR, and average position in descending order by clicks. Rename Sheet1 to Search Console Data, and your sheet should look like this:
Step 6: Remove the domain name from Search Analytics landing pages
Hopefully you can see where this is going now. We have one tab with all of our Google Analytics data by landing page, and one with our Search Analytics data by landing page, so all that’s left is to marry the data.
First, we just need to strip the domain name from the Search Console data. You’ll notice the data from Google Analytics pulls the top landing pages excluding the https://domain-name.com, and Search Console pulls the entire domain.Therefore, we have to format them identically in order to combine the data. To do this, you’ll need to execute a "find and replace" on your Page column in the Search Console tab in Google Sheets and replace https://domain-name.com with no replacement (eliminating the domain name from the URL).
Step 7: Combine the data
Download the Keyword Level Data template here. This template has the proper formulas in place to pull landing page sessions year over year, bounce rate, and total goal conversions. I've also set Column C up as “Target Keywords” to type in the terms you’re actively targeting on each page. This way, you can see if what you’re targeting is similar to what you’re ranking for in Google. Once the template is up, copy the Keyword Data tab to your worksheet.
After you copy the sheet over, you should see a new sheet with a tab called Keyword Data. From here, select the Keyword Data tab and click Copy to...
Select the sheet you have built with your data, and a copy of the Keyword Data tab will populate at the end of your sheet.
If you’ve done everything correctly so far, you will be able to update your URLs and the data will automatically appear within the template for your specific pages. When adding your page URL, be sure not to include the domain name. For example, if you wanted to see data for https://www.domain-name.com/products/, you would type /products/ in cell B6 and see the data populate. Also make sure everything is matching up with trailing slashes between your Google Analytics data and your Search Console data. If you have issues with duplicate URL structures, you may need to work with the data a bit to make the URL structure formatting consistent (and also you should fix that on the server side!). Your results should look something like this:
How is the template working?
If you’re interested in looking at more than two pages and really building this out into a more robust report, you probably want to understand what formulas are controlling the results so you can expand the data.
The majority of this template utilizes VLOOKUP to pull the Google Analytics data into the sheet. If you’re not sure how VLOOKUP works, you can read more on that here.
The year-over-year percent change column and bounce rate column are simple calculations. For example, the percent change in cell G6 is calculated using =(E6-F6)/F6 and the bounce rate in cell I6 uses =(H6/E6). You’re probably familiar with these common Excel functions already.
The more complicated formula is the array formula that’s being used to pull the keyword data from Search Analytics. Due to the fact that a VLOOKUP will stop after the first match, and we want to see up to five matches for queries, we’re utilizing an array formula instead to pull the matches in up to 5 cells. There are other functions that will do this as well (pull all possible matches in a sheet, that is); however, the array formula is unique in that it lets us limit the results to five rows (otherwise, if you have 10 matches for one term but 4 for another, you wouldn’t be able to structure your sheet in way that displays multiple pages within one tab).
Here is the array formula that’s used in cell D6:
=ArrayFormula(IF(ISERROR(INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$A$1:$B$5000)),ROW(2:2)),1)),"",INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$B$1:$B$5000)),ROW(2:2)),1)))
This formula is allowing multiple values to pull for the value in B6, but also allows the formula to drag down and expand through cell D11. The array formula in cell D11 is:
=ArrayFormula(IF(ISERROR(INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$A$1:$B$5000)),ROW(7:7)),1)),"",INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$B$1:$B$5000)),ROW(7:7)),1)))
You can learn more about array formulas here, but the way they are executed in Google Sheets is a bit different than Excel. From my research, this formula gave the results I wanted (multiple matches controlled in a specific set of cells), but if you know of a function in Google Sheets that does something similar, feel free to share in the comments!
Conclusion
Keyword-level data isn’t gone! Google is giving us valuable insights into what terms are leading users to our sites — we just need to combine the data in a meaningful way. Google Sheets is a powerful way to connect to various APIs and pull loads of data from multiple sources. There are some limitations to the Search Analytics report (see this great post from Russ Jones on some inaccuracies he found in Search Console Search Analytics data), so hopefully this small sheet will inspire you to expand the data and include more engagement metrics from Google Analytics, additional click data from Search Console, rankings data, data for traffic outside of organic, and more. Not to mention all of this can be scheduled, so you can have your Search Analytics and Google Analytics data ready when you open your sheets and automate almost this entire process.
We don’t have to use tools like Search Console and Google Analytics in a vacuum simply because they exist that way. Experiment with ways to combine the data on your own to gain more valuable insights into your campaigns!
Also, if you loved this, if any of this doesn’t work for you, if you know paid tools that do this, you’re doing this a different way, you’re doing this in a bigger way, or this just didn’t make sense to you — comment! I would love to hear how other SEOs are gleaning insights into keyword data in the new days of (not provided) and improve on this process with your help!
Shout outs
A special shout out goes to @mihaiaperghis for publishing this blog post on How to Use Search Analytics in Google Sheets for Better SEO Insights as I was finishing up this post. Thanks to your post, I was able to find a free, easy way to pull from the Search Analytics API into sheets. Before reading, I was utilizing and wrote about a paid add-on that was ~$30/month, so thanks to your post I can call this entire process free. Also thanks to @SWallaceSEO for reviewing this article, testing the sheet, and helping me with edits and debugging!
Hi Sarah,
For me Search Console isn't acurrate at all, specially rankings, I usually use another tool for that purpose, why Google doesn't print more accurate data for us?
I can see that - I'm sure Google has their reasons :) I would be interested to hear more about the tools you feel are more accurate - is it a rankings tool?
I find rankings in Search Console are pretty good but you need to make sure you select your country.
If you don't select your country, average ranking position can be thrown out a lot because you'll be including rankings in countries you're not actively targeting - which are usually in a much worse position.
Great post, Sarah :)
Ohh, great point! I just took a quick look and my CTR for several sites is significantly higher when I filter to just the target country. Thanks for the tip!
Cheers David - good point
Hi Sergio, I don't really know how you did configure your Google Search Console account, but for my point of view, it's currently one of the best metric to see the real results of your pages on Google. May, as David pointed out, you didn't select a country, or something similar, bur if you do everything well, you won't face any issue to get accurate results.
Excellent post! I've been using SAS ever since Mihai's post on Moz announcing the tool, and it's been amazing. I have the Analytics add-on as well but haven't dug into it yet, so this is a great recipe.
As far as other APIs - I would say a powerful backlink tool's API that pulls in other metrics would be very useful, one that ties in URL ratings as well as title tags, etc.
Google recently opened up it's Data Studio to the public which ties in nicely with Sheets and Search Console, so there's potential there. I think clients need to see what will move the needle for them. If they understand the deep metrics, then show them, if it's too much then distill down the important points.
As far as what's driving traffic - it's gotta be clicks essentially, the others aren't gonna correlate to actual traffic. But I do use all 3 metrics to decide what other keywords are within range of going for!
One interesting thing is that there's a certain % of Search Console data that's hidden. If you look in Google Analytics and then go to the Search Console section of GA, it shows a small % of SC queries that are (not set) - so it's good to account for those when making estimations.
I agree on the backlink API - I think integrating off-page metrics into this would help get a better picture on the page's overall performance. I also saw the latest Data Studio updates, it looks like that will help significantly with the visualization of Search Console data. That's an excellent point on the hidden keyword data in SC queries. It's sad to think about Google hiding even more data though.....
Hello Sarah thanks for sharing this info. I never thought about this and I am trying the process, it looks interesting.
Great - let me know how it goes for you!
It feels like I found small mistake in formula
When counting position of keyword formula starts from
=ArrayFormula(IF(ISERROR(INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$A$1:$B$5000)),ROW(2:2)),1)),"",INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$B$1:$B$5000)),ROW(2:2)),1)))
I suspect that using this formula we miss or top 1 keyword. I think formula should look like: =ArrayFormula(IF(ISERROR(INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$A$1:$B$5000)),ROW(1:1)),1)),"",INDEX('Search Console Data'!$A$1:$B$5000,SMALL(IF('Search Console Data'!$B$1:$B$5000=$B$6,ROW('Search Console Data'!$B$1:$B$5000)),ROW(1:1)),1)))
The same works for 2,3,4,5 and 6th rows.
Could you please check this?:)
And thanks again for such a wonderful template!
Thank you! That worked - I updated the template. It was still pulling the first keyword for me in my test, but only in the first section and not the second. Great catch, thanks for letting me know!
Thank you! I couldn't get any data to show up in the Search Console section, and this formula fixed that!
Thank you for the great post, Sarah. For anyone who wants to see Impressions, clicks, etc... on the Keyword Data tab for each of the 6 keywords that gets pulled through, I found this formula helpful:
=sumifs('Search Console Data'!D:D,'Search Console Data'!A:A,D6,'Search Console Data'!B:B,$B$6)
D:D is the impressions column. So, that exact formula would go into whichever column you want impressions retrieved. Then, change it accordingly for other Search Console data (C:C for clicks, etc...). $B$6 is your URL string. So, if you copy and paste that formula lower in the sheet for additional pages, be sure to change that for all cells relative to the URL. Everything else should update accordingly when you copy/paste or drag. So far this has been working for me.
Thanks, Logan! It will be really interesting to compare click data to traffic data. It would also be interesting to monitor % changes in impressions to see if that correlates with traffic trends. This is super helpful!
Hey Sarah, thanks for mentioning my Search Analytics for Sheets add-on! It's awesome to see how folks apply it to create awesome SEO tools and templates.
In fact, uncovering the 'not provided' data was one of my first uses of the Search Analytics API, which we used in our agency to better analyze and report data to the client. It's pretty neat, though not very robust when using spreadsheets (especially on very large sites). That's one of the reasons we moved to a paid platform (seomonitor.com) that does this automatically, along with keyword analysis and rank checking.
I'm always looking for more features to include in the add-on, throw me an email / dm me if you have more ideas (or even if you'd like to just chat SEO stuff :)
Cheers!
Awesome - I will check out SEO Monitor. Thanks for reaching out!
Hi Sarah, really interesting post with a lot of new information for me.
I'll try your method in the future. To get more insight, I'm currently generating some custom reports to cross all the data I want from Google Analytics. However, It's only possible when your Google search console and Google Analytics are connected.
Definitely - the custom reports you're creating sound awesome. Are there any other sources of data you find are especially useful to tie into GA data? I was thinking some sort of off-page metric, like links, would be interesting to pull in with the landing page data to monitor any changes after you obtain/lose any solid, quality links.
Not yet, the idea sounds pretty awesome, it might help to get more insights on ranking based on real results. The method of crossing data from GA, Search Console Keyword, and links (the one discover by Google through Google Search Console) and probably anchor text could probably provide more information; however, as the search results on Google don't depend only on your website's performance (competition are still out there), I'm not really sure if it'll be accurate for a conclusion.
I'll spend the next few days to try and mix my custom reports with your Google Sheets options, It might probably end up with a lot of new information. I'll keep you update.
Please do!
Hi Sarah,
Awesome article, very useful!
Great post, Sarah!
Do you think there are ways to link page to final table table automatically? It would be great if this tables were updated automatically even whene new pages are added to the site.
Oh great idea! The process can definitely be automated/scheduled. Google has a really great tutorial on how to schedule the reports here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on#scheduling-reports.
For "when new pages are on the site" you would still have to add the URL of the page to the final table so the VLOOKUP could execute, but potentially there is a way to automatically pull the URLs from your GA export into the final final table so you don't have to manually add them in. This spreadsheet is definitely just a starting point but it sounds like you have some great ideas for how to make it more effieicent!
Thank you very much Sarah. Great work and usefull explanation. I read it a few times until I could do it :) Time to work, thank you
Thank you! Glad it worked for you :)
Great article! Don't forget to share it on LinkedIn. I'm looking forward to sharing it there too! (And would love to give you the attribution there)
Ah of course - I will do that now! Thanks, AJ!
Regarding your question on visualising the trends and make data accessible more easily, at my former employer we used to write csv exports of Google sheets in our data warehouse and then visualise the reports in Tableau. I think if you schedule a regular upload of search console and GA data you can create something really valuable there combining it with other data from your data warehouse especially in an eCommerce or B2B sales company
I've heard good things about Tableau but haven't gotten to test it out yet. I will definitely test that out - thanks!
As someone who practically lives in Google Sheets, I'm astonished that I didn't even know this was possible... Definitely something that I'll have to have a play with. Thanks for opening my eyes!
Glad you liked it! The scheduling capabilities really blew my mind as well. I didn't realize how easy it was to automate the reports until I dug more into the API resources, and I've also been using sheets forever.
I have gone through with your posts and I never think before that we can pull the data like in one go. Previously I was wondering that how can we get the data from Google console and analytics that should tell us some meaningful statistics including transnational keywords with the landing page. Now I can use this for finding these data by your suggested way. Thank you very much for this insightful steps.
That's great - I'm glad you found it useful. I never considered pulling multiple sources to see transnational keywords - great tip!
Wow, Sarah! Amazing post! I didnt' know that it was possible to combine Google Sheets with Analytics, and it can be very useful! In my opinion, the most accurate metric to determinate the most likely keyword is the click, because it's an action that depends on the client, not on Google. Thank you for the info!
I agree! I've been interested to hear more on Google's thoughts on the click discrepancies Russ Jones identified.. that did shake my confidence a bit, but the click still feels like the best way to determine the most likely keywords that generated traffic to a page.
Hi Sarah,
Thanks for sharing this good article. I just had a few clarifications.
When we state keyword level data has completed disappeared in Analytics. What to do we exactly mean by it.
What we attain ultimately is keywords that drove clicks to the page & the organic engagement metrics for it. Please correct me if I'm wrong.
Thanks & Looking forward to your reply.
Good question. When we say "keyword level data has disappeared in Analytics", what I mean is that several years ago Google Analytics would report keywords on the session level. Meaning you could see a goal conversion tied to a keyword, a landing page tied to a keyword, anything you can see tied to a session, you could see the keyword that generated that session. Now, the session indicates keyword "(not provided)", so that keyword that led the session to your site is no longer available, which I'm referring to as "disappeared" because it was there in the past.
You are correct in what we attain; when you connect Search Console data with Google Analytics data you essentially have a list of keywords that drove clicks to a page - and then the organic metrics for that page. So still not able to drill down on the session level, unfortunately, but this helps you glean a bit more insight into the keywords driving traffic to a page in a more efficient way.
Let me know if that doesn't answer your question!
Thank you for your feedback. That somewhat has answered my question. I did try segmenting page-wise organic metrics at keyword level through google custom reports. What i did realize is that the data (organic metrics) at keyword level per page is much more presentable. Thanks for researching & sharing this valuable hack to us.!
Will be interesting to give this a try. Thanks!
Sarah,
I got the first two parts, but I'm not getting keyword data in the end, on the template you created. Just info on the page in total. Do I put the keywords in column c? I tried it to no effect. Then I tried putting them in the search console column, nuthing.
I'm sure I read to fast and missed something. If anything occurs to you please let me know.
Thanks
Jen
HI Jen - I would love you help you out. Can you shoot me an email and share the sheet to [email protected]. I will take a look and we can figure it out!
I'm getting errors when attempting to run the report.
Organic Landing Pages Last Year: Invalid value '-ga:sessions'. Values must match the following regular expression: '(ga:.+)?'
If I delete the minus I get.
Organic Landing Pages Last Year: Invalid value 'sessions::condition::ga:medium==organic'. Values must match the following regular expression: '(-)?ga:.+'
It took me a while to notice that I can't just copy and paste from the values from this website but there are blank cells in between some rows. Thanks.
Oh good catch, I apologize for that! I thought having the rows in here would make it easier, I can see how the pasting can be off. Thanks for pointing that out!
Hi Sarah, no matter what date range I put in the Reports Configuration the same number of sessions. So it is pulling the same session for This Year and Last Year. Could you help me troubleshoot this?
Sure thing! Can you shoot me an email at [email protected] and share your sheet with that address? It's easier to figure out the problem if I can see into the sheet.
We've been working hard at creating tools that leverage all the systems that we have in place to centralize all that data. Google Drive and Google Sheets has allowed us to do that despite the "tin foil" hat crowd that says Google is reading out stuff.
Anyway, I've used several different solutions to do what you're saying and by far this one the easiest to set up once we actually sat down and started it. Hopefully, we can figure out a way to pull all this data using Google Data Studio.
Glad it worked, Clint! Please let me know how your Google Data Studio test goes. I couldn't tell if you can pull GA and SC data into the same reports, or what the limitations were with the free/beta version, but I'd be interested to know the capabilities.
Hi there, thanks so much for the share! I see the potential for sure. One thing though I seem to have missed: I did everything as you said, copied the template to the sheet I created, but can't figure out how I bring the data from the other tabs into that KW Data one... Help please :)
Absolutely! Share your sheet with me at [email protected] and I'd be happy to take a look and help you :)
This is a great piece of work Sarah.. Makes things a lot easier.. Thank you :)
I am keep running into same issue. Perhaps you can help..
When I run the Analytic Reports, the two tabs are not generated "Organic Landing Pages This Year" and "Organic Landing Pages Last Year" and because of this I am unable to see the visit numbers..
The rest are OK though..
Any ideas?
Thank you in advance..
edit: Above is sorted, however there is another problem with formulas;
The first part of my problem is that there is no separate target keyword click data so we can see which keyword drives the most traffic to that particular landing page.
Second is that the Array formula doesn’t pull out the main seed keyword into the list i.e.
URL – “ /help-to-buy/ “
The “help to buy” keyword does not get pulled, all others get pulled but not the one that holds the most traffic (in my case).
Is this something you could help with?
Hi Hasan - I'd love to help sort this out.. can you send me an email to [email protected]? I can pop into your sheet and see if I can tell what the issues are!
Thanks! Nothing new, because you can't still link single keyword data with single convesrion data (you merge all keywords together). But you've written a beautiful post Sarah, full of details and useful how to's .. thanks a lot for sharing it with us!
Absolutely! That's true, you can't link the specific keyword still.. that would be nice to see again! At least the keywords sending traffic to a page are generally going to be topically related, so it's interesting to take the thematic approach and see which "themes" are most likely driving conversions, but we're definitely taking a "best guess" this way!
Thank you Sarah for that amazing post.
I am currently working on integrating Salesforce data with GA via Adwords & conversion uploads in GA and will look into if I can link final sales directly to the keywords & landing pages then which would be a great lever for one of our clients.
One quick question as I have been working with the Supermetrics plug in a lot and saw that in your first screenshot, it should work with that plugin as well right?
Ohh SalesForce data is a great idea!! And yes, overall this will work with Supermetrics. You get a similar export with their tool that has keywords and clicks by page. I think the order of the columns is different in the Supermetrics pull though so you'll just have to double check that the formulas or column labels don't need to be adjusted for that.
This a very useful article for me. I like how easy it is to understand. Most articles I have read on this topic are a little difficult to comprehend. Thanks for the share and for simplifying a complicated topic!
I agree this stuff can be super complicated - I'm happy you found it easy to understand!
Hi Sarah,
It is probably just something I have missed but I just can't seem to get the Search Console column to populate in the "Copy of Keyword Data" sheet :(
Managed to sort it. For some reason I had to change the Search Console Data Worksheet to "Search Console Data1" and alter the VLookup accordingly.
Thanks for this, it looks good. I will have a little play around with it.
Oh good, glad you worked it out. I originally had it so the Keyword Data sheet had to be opened, right click on the tab, paste - and then figured out how to make the link point to a direct copy - that may have made something weird happen in the move!
I too am having issues with getting the Search Console to populate. Any idea why? Nice article btw! Also, should E7:J11 and E15:J19 have anything in them? They are blank.
Disregard the 2nd part of my question above...I understand now.
RE: populating the Search Console. The formula provided in the "Keyword Data" sheet was not working for me. In order to get the top 6 instances (keywords) from the Search Console Data sheet that correlated to my Page URL #1 url, I used the following formula:
=if(iserror($E$6),"",index(filter('Search Console Data'!A:A, 'Search Console Data'!B:B = $B$6),1))
I just incremented the last number in the formula by 1 for each row and it worked well. Please let me know if this seems to not perform the same intended function as your default formula (i.e. returns the top 6 search console query terms based on the URL provided in cell B6. Thanks!
That's a great solution, Joshua! I actually did a lot of research and kept finding threads that stated index filter formulas would accomplish what I wanted, but I couldn't get it to work for me so I went with the array formula. I'm glad you were able to find a work around - this works great. Thank you for taking the time to share!
This might be too much, but if you insert the following formula next in column E (next to the "Search Console" column on the Keyword Data sheet) then you can see each query's clicks, impressions, CTR, and position all in the same sheet. I believe this helps in the long run, but you'll need to do some adjusting and paste the formula where in each row that is next to a Search Console Query.
Formula - "=if(isblank($B$6),"",index('Search Console Data'!C:F,match(D8,'Search Console Data'!A:A,0)))"
Result - https://www.screencast.com/t/ZpJ6ypy48m6
Thanks again for this article, Sarah!
I can never have too much data when it comes to organic keywords - this is great! Thanks, Joshua!
Sarah-- Very nice & informative post. Helped me collate lot of technical data in 1 go. However there is some issue I am facing it. For 1 URL I have nearly 15-20 keywords pulling the clicks however in the combined sheet only 4 are coming. I would love to have all the keywords pulled in for 1 specific URL. Shall appreciate if you could help me resolve the above issue
Hi! The way I set this up was to pull in the top 5 keywords for each page. I found when I had every single keyword pulled in you couldn't have more than one page on a single sheet since the amount of keywords varied for each page. That being said, there is a way to do what you want! Instead of the ARRAY formula, you can use FILTER in Google Sheets. When I was playing around with it, I found this article helpful: https://productforums.google.com/forum/#!topic/doc...
Let me know if that doesn't work!
Great Work Sarah!! Loved your content and it open the scope for more research and action for me. thanks for sharing it. You explained it very well and I have done this and got some good keywords which I was not targeting. thanks for sharing this knowledge.
Wow - thanks for the feedback! I'm glad you've already been able to identify some new target terms. I am consistently surprised by some of the phrases I find pop in search console data - it's definitely a great source for new keyword ideas!
I too am receiving the following message at the final step, how can we solve this?
"Error! VLOOKUP evaluates to an out of bounds range."
For the record in case anyone reads this with a similar issue - in this case the tab names had an extra space in them that made the template formula not work. If the tab name in the VLOOKUP doesn't match any of the labels in the tabs, you see this error.
"Error! VLOOKUP evaluates to an out of bounds range."
How can I fix this?
Oh no! Can you shoot me an email at [email protected] and I can take a look at your sheet in a bit? It's a little easier to see the issue when I can see the formulas :)
Pretty awesome post. I will be using this going forward instead of always relying on GWT and Analytics.After copying your template and putting in the page url 1 and 2, the data populated on the side including the visits in the last 30 days, visits last year, etc.. However, where it says target keyword 1, target keyword 2, etc.. -- this data did not change, should it have changed or do we have to manually enter in these target keywords based on what we are trying to target?
Exactly right - this was to manually enter based on the keywords you're trying to target. I found it helpful to see what I knew I was trying to target compared to what was pulling from Search Console.
thank you
Hi Sarah, when the sampling rate in analytics gets quite high, is this data analysis method still relevant? I spent quite a bit of time working with segments only to realise that the sampling causes too many data consistency issue at a sampling rate of 10%. I assume this method of data analysis will also be affected. Is this data analysis method relevant to everyone or will it start have accuracy issues with certain types of websites?
Ohh great question! I think the sample data would definitely impact this... that being said, if you're running into extreme sample data I would assume you're running into data quality issues across any analysis you're putting together using GA. What are some of the ways you work around the sample data when putting together other GA reports? Do you use alternative analytics platforms, or have you considered GA Premium?
I have found when dealing with sampling issuing that pulling directly from the API seems to give me more accurate metrics. I think it's because you can pull a smaller timeframe and that sometimes helps with the sampling issues. In general though when working with accounts with extreme sample data I like to speak more to trends than specific metrics. So if things are +/- 5-10% year over year I would say they're flat, and anything +/- more than 10% I would say it's trending up or down, and that's really how I work to personally gain insights from sample data - it's tough though!
Google Sheets for keywords data... Really!!!
I just had a very meaningful addition to my SEO knowledge. Thanks Sarah for sharing this. That's the beauty of SEO community - Share, Learn, Grow.
Happy to share - glad you found the concept useful!