Google Webmaster Tools

How to capture the ‘not provided’ data – xxx – 201309xx


It’s official. The last nail on the ‘not provided’ data is hammered in. And now all of us have to live with not having access to keyword data. It was coming, for years now. Some of us took note of it. Many didn’t.

A few months ago, Niswey did. When we saw we were dealing with well over half of a client’s website data being under ‘not provided’, we decided we had to do something. Whatever we did, the campaigns we ran, the relevant content we wrote, the webinars we did, would not amount to much if we didn’t know what the big chunk of the traffic was really doing at the site.

Even if we didn’t get to the actual figures, we really needed to see what the trends were. That’s when we hunted for how to get to know the trends hidden behind ‘not provided’. We set it up in Google Analytics, and we now have a very good picture of the keywords and their impact.

Our strategies for customers are not driven by keywords, our focus is on creating relevant customer experience at the website. But the keyword data would give us a pretty good analytics picture to support our strategies.

Here’s how we set up access to the ‘not provided’ data.

1. Log into your Analytics API. Select the required profile. Now click on the Admin tab. You need to have administrator privileges for this.

2. Under the Profile section, click on Filters.

3. Now click on Create New Filter and enter the filter name. In the Filter Type field, select Custom Filter. Now select the Advanced radio button.

4. Now fill up the values in the boxes as mentioned here:

a. Against Field A->Extract A, select Campaign Term and (.not provided.)

b. Against Field B->Extract B, select Request URI and (.*)

c. Against Output To -> Constructor , select Campaign Term and np – $B1

Select the radio buttons as shown in the image: Yes for Field A Required, Field B Required and Override Output Field, and No for Case Sensitive.

5. Click Save.

Now you will find the ‘not provided’ data in the keywords section in Google Analytics.

After the setup, you will see that the ‘not provided’ data drops to zero.

Do remember that this will not give you the exact keywords. But it will show you the pages on which the visitors are landing. If you look at the data carefully, you will be able to clearly tell the keywords they have searched for.

If you think keyword data from Google Analytics was the best thing ever for your work, the process described above will give you the next best thing.

Update: As pointed out by David on Twitter, the credit for the process goes to Dan Barker, while we learnt it from SearchEngineWatch. Thanks for letting us know David :)

Tagged on: ,

By Premnath321 | September 27, 2013 | Blog | 16 Comments

16 thoughts on “How to capture the ‘not provided’ data”

    1. Premnath321 Post author
      @Dheeraj: pls do share results too :)Says: Hi I set everything as you mentioned in your, post but its not working for meReply: the data will start appearing in future, once you have completed the process. You cannot find previous data.

      Google Keyword ‘(Not Provided)’: How to Move Forward – Ray Comstock – 20131016

      STAT Search Analytics

      not provided Counter

      Without a doubt, Google’s recent changes make performance reporting less accurate. SEO professionals and marketers no longer have the raw data that we once used to measure SEO results. We will need to use different KPIs and trending metrics to approximate the data that is now lost.

      However these changes aren’t a surprise. It has been widely assumed by the SEO community for some time that this change was going to happen (although few expected it to be so soon).

      Google isn’t the only company making “secure search” a priority. Browsers such as IE10, Firefox 14+, and Mobile Safari have put measures in place to mask keyword referral data.

      Fortunately, many SEO professionals and organizations have been preparing for this eventuality. It starts with having a solid plan in place to report on data that we know historically has a high correlation to the success that we were once able to directly measure.

      The good news is, unlike Google’s Panda and Penguin updates, this change doesn’t affect our approach to optimization for the most part other than performance reporting (with the exception of being able to use analytics data for keyword research).

      As Google’s Distinguished Engineer Matt Cutts has said: “Succeeding in SEO will be the same as it’s always been if you’re doing it right – give the users a great experience.”

      By developing user-centered content that is valuable and informative, and publishing to the web using best practices, you will see positive business results – assuming that you’re coupling that with other SEO best practices.

      Let’s dive into a new approach to SEO performance reporting using the metrics we still have in conjunction with a couple new KPIs. This extrapolation should provide quite accurate organic search performance results and allow you to understand if you’re successfully driving more visits based on your optimization activities.

      Understanding the Background

      LostTo SEO and digital marketing professionals alike, the “(not provided)” or “keyword unavailable” issue has been going on sincelate 2011. Since that time, Google has been redirecting a growing number of users to a secure page for their search activity ( The end result is that all search referring data that traditional analytics tools have used to understand which keywords drove visitors from Google is now blocked.

      When Google initially launched their secure search, many marketers began seeing that a percentile portion of their keyword data in Google Analytics fell into a “(not provided)” category. In fact, Google estimated at that time that keyword unavailable searches wouldn’t exceed 10 percent.

      Initially, a searcher had to be logged into one of their respective Google accounts in order to produce any sort of keyword “(not provided)” data. This meant that referring keyword data was no longer being fully displayed in analytics as Google aimed to provide their users an amount of privacy when searching.

      However, the percentage of organic search keyword traffic coming from keywords that were “(not provided)” grew steadily in past years, to the point that many sites were accumulating more than 50 percent of keyword “(not provided)” data (and in some cases upwards of 80 percent or more).

      Things changed again in late September when Google rolled out major changes toward encrypting search activity altogether. Now, when any user goes to Google to search they are automatically redirected to the https:// version of Google or, an SSL encrypted search.

      This update only affects organic search data. Paid search data from Google continues to report on keyword referrals.

      There is no doubt that secure search will be the trend going forward and we should assume for the sake of planning and scalability that keyword referral data is a thing of the past from an analytics perspective.

      What the Loss of Keyword Data Really Means

      Quick summary:

      • Changes only affect how we measure and report SEO performance.
      • Organic traffic from Google can no longer be tracked at a keyword level via analytics.
      • There will be a limited amount of keyword referral data available in Google Webmaster Tools.
      • No longer have visibility into traffic numbers:
        • Brand / Non-Brand.
        • Long-Tail Performance.
        • By Keyword Group.
      • Decrease in visibility for new keyword opportunities based on analytics data.
      • We need to use a different metric set to understand SEO performance.
      • We should expand the number of keywords we check rankings for in Google that correlate to high performance URLs.

      Again, SEO still works the same way. But, not having keyword performance data affects SEO practitioners and digital marketers in two distinct ways.

      1. How to Measure Success and Performance

      SEO professionals have historically used a combination of ranking, traffic, and conversion metrics as the primary KPIs to measure SEO performance.

      Now, based on the new Google change, the following metrics are still available:

      • Overall Organic Search Traffic By Engine
      • Total conversions from Organic Traffic / By URL
      • Search Rankings for Critical Terms
      • Search Rankings by Page Tags / Types
      • Search Rankings by Keyword Tag

      These are no longer available:

      • Year-Over-Year Brand / Non Brand Total SEO Traffic
      • Year-Over-Year SEO Traffic by Keyword Tag
      • Conversions by Keyword / Keyword Tag
      • Keyword Traffic Patterns by URL
      • Long-Tail Keyword Traffic Patterns

      2. How to Research Keyword Opportunities in the Era of “Keyword Unavailable” Performance Data

      This is a much smaller issue but still deserves attention. Historically, analytics data has been an excellent source of uncovering additional keyword opportunities and long-tail permutations that had a propensity to drive traffic. However this data was used largely in conjunction other keyword data sources like:

      • Google Keyword Planner
      • PPC / Paid Search Data
      • Competitive Analysis
      • Intuitive Understanding of the Market / User Personas
      • Third Party Tools (SEMRush, Keyword Discovery, Wordtracker, etc.)

      Going forward, greater emphasis will be placed on these data sets as the foundation of keyword research, especially PPC impression data, which will be the most accurate source of information to identify opportunity.

      How to Report on SEO Performance if Keyword Data is ‘(Not Provided)’

      What KPI set should be used as the primary gauge of SEO success going forward? Earlier we identified the historical KPIs we’ve used to measure SEO success as well as which of those KPIs are still available.

      Let’s take a more detailed look at how to use data that’s still available, and which other KPIs you should incorporate into your reporting methodology. Below are four primary metrics to measure search performance going forward.

      1. Total Organic Search Visitors

      This will still be your primary metric. “Did traffic go up or down in comparison to a previous time period and is that change substantial relative to our goal?”

      Unfortunately, because brand and non-brand segmentation of this traffic is no longer feasible, it’s less clear if SEO efforts were primarily responsible for a shift in performance or if it was mainly due to a shift in demand across keywords that have remain consistent in ranking. This is especially true for brand related searches where typically a company will rank number one for their brand.

      Therefore any change to brand traffic levels aren’t usually considered a result of SEO activities when the ranking doesn’t change for the brand terms. This isn’t as true for large companies that have multiple brands or sub-brands where they are less likely to own the number one spot for all brand related terms.

      2. URL Level Traffic

      Although we can no longer see the keywords that drive traffic to a website from Google, we can see what pages that traffic lands on. By identifying the pages that drive the most organic search traffic to the site and correlating which keywords those pages are ranking for, we can correlate changes to both traffic and rankings to see if we can identify positive or negative changes.

      In many cases this will be difficult since we no longer have visibility into the keywords driving traffic (with the exception of Google Webmaster Tools data). However, we can get greater context around these traffic and ranking numbers by analyzing them in conjunction with the Google Webmaster Tools keyword data.

      A sample SEO URL performance reporting structure might look like:

      Total Traffic: Last Period: xxxx Current Period: xxxx Change: +/- xxxx
      Keyword 1:
      Rankings Last Period: #4 Current Period: #1 Change: +3
      Traffic Last Period: xxx Current Period: xxx Change: xxx
      (traffic numbers only if they exist in GWT)
      Keyword 2:
      Rankings Last Period: #10 Current Period: #6 Change: +4
      Traffic Last Period: xxx Current Period: xxx Change: xxx
      (traffic numbers only if they exist in GWT)

      3. Use Webmaster Tools

      You can still get keyword referral data in Google Webmaster Tools. It also gives you impression versus click data so you have visibility into the keywords people are using and where your site got an impression in the search results. Note that this data isn’t 100 percent accurate and is typically only available for a relatively small overall percentage of search queries for most larger companies.

      Comparing keyword traffic volumes over time will give you a trending direction for your SEO program, especially for competitive non-brand keywords.

      Therefore, using this data in conjunction with the other data points as part of a trending performance report will show the effects of the SEO program. This will be especially telling when coupled with the URL level traffic and rankings for those keywords that have data in Google Webmaster tools.

      Since the number of keywords reported on is not comprehensive and the data is not 100 percent accurate, the analysis of the data derived from GWT will be considered trending data and is a KPI that will need to be considered in conjunction with total traffic, URL traffic, and search rankings in order to form a comprehensive view of the overall effectiveness of the SEO program during any particular time period.

      4. Search Rankings

      Search rankings will actually gain in importance (contrary to what Google has historically said they want) because of this update since marketers can no longer see which keywords have driven traffic to their site. Therefore, it will be important to check rankings for keywords that have historically driven traffic to your site since you won’t be able to directly measure changes in traffic levels for those keywords anymore. Analyzing ranking changes across keywords that have historically driven traffic will now be a critical tool in identifying and reacting to negative traffic changes.

      It will also be important to carefully track which URLs are ranking for which keywords in order to correlate ranking changes to traffic changes. This insight will allow us to better understand what is happening to traffic at the URL level.

      Using these four primary data sets in conjunction with one another can help you develop a comprehensive overview of your SEO performance and begin to answer questions about what happened and why.

      Here are four additional data sets that will add context to the four primary metrics:

      5. Use Google Adwords

      AdWords impression data can be used in conjunction with Google Keyword Planner data to identify new keyword opportunities.

      6. Look at Non-Google Keyword Data

      While Bing and Yahoo don’t provide nearly the amount of traffic that Google does, insights can still be made about the keywords that are driving traffic to your site, in particular at the URL level. This is especially true for those sites that have a significant amount of traffic.

      7. Look at Historical Data and Trends

      You still have all your historical keyword data in your analytics platform prior to this secure search update. This data will be extremely valuable for identifying campaigns and keywords that have consistently been performing well. This is important information for keyword opportunity identification standpoint as well as understanding URL level traffic trends.

      We’re now using page-level data in conjunction with ranking data to understand performance changes (since we don’t know exactly what’s happening anymore in terms of which keywords are driving traffic and which keywords have declined in traffic).

      By researching historical trends for the URLs that are being reported on, you can get a better idea of the keywords that have historically driven traffic and whether those keywords were primarily brand or non-brand keywords. This allows you to better understand the cause and effect of traffic changes to those URLs.

      Historical data also gives insights into the seasonality of your market. This allows you to better understand the potential causes of performance changes.

      8. Google Trends

      Google Trends can give you insights into what is trending and thus what is bringing you traffic (especially as it relates to understanding how your brand traffic might be performing).


      Using data analysis to understand and identify performance changes is critical for SEO professionals so that they can quickly and effectively respond to negative changes, prioritize resources and accurately report performance to executives and other team members.

      In the past, keyword level analytics data has been the focus of this type of analysis and therefore has been critical in accomplishing these goals. In the absence of this data, based on the new Google changes, new metrics will need to be prioritized for these purposes.

      While these new metrics aren’t as accurate as keyword level data, they do provide a solid alternative to understanding SEO performance.

      Learn More: “(not provided)”

      Read more on Analytics

      Nikki Johnson says:  Solid post, Ray … I really like the punch list of metrics you’ve put together. As much as I cursed the day when Google made the announcement about 100% encrypted organic searches, we were all increasingly forced to use these types of metrics to deal with the gaping hole of “not provided” keywords as it grew wider and wider with each passing month — even before the official pronouncement was made. (I address that sentiment in a little greater detail here: Given how many keywords were falling into that “not provided” category, I’m almost relieved that it’s out of its misery, based on how crippled it had become toward the end. It was becoming increasingly difficult to work with an incomplete data set. Thanks again for the thoughtful coverage of where to turn now.

      Says: - Hi, thanks for the post.

      If we use the search term exclusion list with Universal Analytics, do you know if the keyword is excluded even though it is not provided – or not ?

      For example let’s say I want to exclude my own brand : will the search terms report include in the not provided section people who came from organic traffic, but using my brand as a keyword ?

      I am finding the Search Queries Report in Google Webmaster Tools most useful now for getting organic keyword data. I had almost completely forgotten about this old and once-fairly-useless report! Although the data is made up of Google approximations, it gives a good overview of which keywords are performing well and which might be slipping. You might find my post on using this report useful:

      CidJeremy – A lot of good information. How my team and I have adapted to the lack of keyword data from Google is to focus on visitor behavior patterns. Meaning, where they enter the site, then what actions they take next. I’ve always believed this to be a more accurate indicator as to the value your website provided anyway. If you’re able to accurately target your ideal customer and direct them to the correct page based on the their search query, you can take them by the virtual hand and guide them through the “sales cycle’ of your website. Entry and exit pages should be a key metric to follow

      PrashantJain – It was very much expected that Google will take out keywords data out of equation. This change is definitely going to impact strategy planning for SEO webmasters. I agree that now focus will shift on URL’s rather than keywords driving traffic to the site.

      We all will have to analyze our webpages and improve user experience there.

      I don’t mind that keyword data wont be available in the UK at some point in the near future, I think it puts the focus on creating better content in the long term. However I do feel that performing some of the suggested methods above is going to be rather time consuming. I certainly don’t have time to trawl through historical data to try and spot trends. If you know your market and are already doing a good job, using best practice SEO techniques, then just continue on as you are, this shouldn’t really affect you. When it come to reporting most of my clients are concerned with results directly in relation to sales and enquiries.

      I don’t think you get my point Dean. Firstly as it relates to reporting results related to sales, how are you going to prove whether a sale was generated from a brand or non brand term? If your client gives you credit for sales that originate from a previously held number one listing on a brand related term then you are fortunate but most people do not have said luxury. Secondly, if your traffic to a particular URL goes down, how are you going to figure out why it went down and whether or not the problem is something that can be fixed based on either application of best practices or correcting an inadvertent error (which happens all too frequently with larger clients who have IT teams that constantly make changes that marketing is not always aware of). Without reporting against some of the metrics I have described, you will be at a significant disadvantage to address either concern. Also I would challenge you that you don’t have time to analyze data to spot trends. I would argue that all SEO professionals should use data analysis to drive their strategy. Seems like you might need a better reporting and analysis solution. Good luck.

      Tom Slage says:
      Yep, thing’re lookin’ bleak, ma.

      But that’s what this industry is about, and I’d rather bootstrap SOMETHING and make it better whenever possible than just sit around and bellyache. Thanks for helping with that Catfish.

      That said, the biggest conundrum is the URLs with traffic dominated by branded terms. If we’re getting tiny incremental gains from long tail terms we’ll never know. Measuring SEO is now about the “big play” then, as far as effective measurement is concerned. As such, maybe it makes sense to extend reporting frequencies to look at periods that encompass more SEO activity. So instead of monthly reports, what about quarterly reports, coupled with reports of what SEO tactics took place in that period. And of course proving gains ABOVE previous trending is really what we’re after.

      The unfortunate part of it Tom is that it puts more focus back on rankings which don’t really account for long tail traffic which, especially at the enterprise level, account for a significant if not majority of total traffic.

      Having said that, we all know what we need to be doing from an SEO perspective and reporting on the results which important, doesn’t change what we should do.

      Reading what you typed below, Remind me of a story about my Great Grandma state fair winning apple pies which she made every year for 20 years winning the blue ribbon every time. It’s been 5 years since she passed away, my older sister pickup the pie making. Do you know, my sister hasn’t won a blue ribbon for the past 5 years, no matter what she does Its NOT THE SAME! Do I need to connect the dots for you?
      It is what it is….”While these new metrics aren’t as accurate as keyword level data, they do provide a solid alternative to understanding SEO performance.”

      How do I calculate accurate ROI on non-brand performance when the top 50 pages of my site are dominated by brand traffic?

      Unfortunately Dave there is no way to do that anymore. but you can look at the non brand keywords that have driven traffic to those pages in the past and continue to track rankings for those keywords. Then see if there are correlations between ranking changes and traffic changes in context with changes you see for those keywords if they exist in Google Webmaster tools. Make no mistake, its more about estimated trends now than actually measuring performance and that is unfortunately the world that we live in now. But on the flip side, it is no different than the challenge that social media folks have in understanding their performance and ROI. But it will be impossible to shows gains and losses for long tail, non brand keyword traffic which is also unfortunate.

      First off, great post!

      In some cases, using the new advanced segments to create groups based on referral source/medium and landing page can give you a pretty accurate idea of band vs non-brand performance.

      As long as your brand traffic usually lands on pages that are different from non-brand traffic you can effectively calculate ROI.

      I have an example showing how to do this analysis that is a bit lengthy so I will just link to it:

      Keyword estimator tools were always off; and not by just 30-40% but several hundred % points.

      Take a look at the youtube case study we did awhile back at

      Best best. Fire up the largest keyword list you can muster (with local geos), test live in Google on the PPC adwords program, bucket those keywords into target and non-target, then get to work!

      Excellent, thorough summary!

      Our most fruitful SEO analytics have been about the long tail and most of the fallbacks in this article will work well, if they work at all, for mainly the big head words. What a mess.

      We have tried most of these ideas but have decided that analyzing Bing and Yahoo traffic is by far the most useful if we need “visit quality” in the equation, which we emphatically do. So, we’re applying what we learn from Bing and Yahoo directly to our Google SEO, with a little help, but not much, from Google’s remaining tools.

      I would love to see a summary of what is known about the demographics, lifestyles and online behavior of Google users vs Bing and Yahoo users. There are a lot of glib stereotypes floating around, but what does the research really say?

      How to Use PPC Data to Guide SEO Strategy in a ‘(Not Provided)’ World – Ben Goodsell – 20131021

      We can no longer precisely track traffic for Google in organic search at the keyword level. As “(not provided)” creeps its way up to 100 percent, so does the lack of our ability to track Google organic keyword conversions.

      Tell your friends, family, loved ones, the boss. Then if you haven’t immediately lost their attention with the use of acronyms and jargon, also let them know that we’re still able to measure our efforts and gain strategic insight in many ways.

      This article is an attempt to explain what we see in keyword reports currently, show how PPC data can help guide SEO efforts, and finally a consolidation of initial thoughts and ideas to assist inmoving forward.

      Smart SEO professionals will still prove their worth. Together we can overcome this daunting hurdle.

      What Do We See in Google Organic Keyword Reports?

      Short answer: We aren’t seeing an accurate representation of keywords people are using to get to our sites.

      The easiest way to look at this is by visualizing the browser versions that are still passing keyword referral data.

      Google Organic Visit Share vs Provided Query Share

      Above, the light green color is the percent of keywords that are still passing keywords next to the darker Google organic visits.

      In essence, we’re mostly seeing keywords from outdated versions of Safari and MSIE (Internet Explorer). So the search behavior associated with the demographics using outdated browsers is what we see coming from Google in analytics packages like Google Analytics. Probably not a comprehensive picture into what is actually happening.

      Using PPC Data to Guide SEO Strategy

      Google needs marketers to be able to quantify their efforts when it comes to AdWords. Therefore, keyword data is passed and there to take advantage of.

      The thought here is that if a page performs well in a PPC campaign, it will translate to performing well at the top of organic listings, though people clicking ads versus organic listings probably behave differently to some degree.

      There are many ways PPC data could be used to help guide SEO strategy, this is just one to get the juices flowing.

      Step 1: Identify Top Performing PPC Landing Pages

      If using Google Analytics, from Dashboard click Acquisition > Adwords > Destination URLs. Assuming you have sufficient conversion tracking set up here, it should give you all the information you need to understand which pages are doing the best.

      After filtering out the homepage, sorting by the conversion metric of your choice, adding Keyword as a secondary dimension, then exporting 100 rows you will have the top performing 100 landing page/keyword combinations for PPC. Revenue is always a good indication that people like what they see.

      Using PPC data for SEO strategy

      Step 2: Pull Ranking Data

      Next, pull in Google Webmaster Tool Ranking data for the associated keywords. You can access this data in Google Analytics from Dashboard > Acquisition > Search Engine Optimization > Queries, or in Google Webmaster Tools.

      Specify the largest date range possible (90 days) and download the report. Then use VLOOKUP to pull in ranking data into the spreadsheet containing the top PPC landing page/keyword combinations.

      Using PPC data and SEO Rankings strategy

      Step 3: Form SEO Strategy

      Now that we know where our site shows up in organic for the top PPC keyword/landing URL combinations, we can begin forming strategy.

      One obvious strategy is to make sure that the PPC and organic landing pages are the same. Sending PPC traffic to organic canonical pages can only increase the possibilities of linking and social sharing, assuming the organic page converts well.

      Another option is to filter the Average Rank column to only include first page rankings, in an attempt to identify low-hanging fruit. Once an opportunity is identified, compare SEO metrics to determine where focus should be placed and how best to meet and beat your competitors.

      Additional Thoughts on SEO Strategy in a 100% ‘(Not Provided)’ World

      1. ‘(Not Provided)’ Still Counts as Organic

      Conversion information is still applied to the organic channel, don’t forget! We no longer have the ability to say someone who Googled [hocus pocus] bought $1,000 worth of “hocus pocus” stuff. But we can say that someone clicked an organic listing, landed on the hocus pocus page, and bought $1,000 of stuff.

      Note: “(not provided)” shouldn’t be confused with the issue of iOS 6 organic traffic showing up as direct. Last we checked this was hiding about 14 percent of Google searches, but is becoming less of an issue with the adoption of iOS7.

      2. Bing Still Has Organic Keyword-Level Tracking

      Bing doesn’t use secure search, so we can still see what people are searching to get to our sites, conversions, sales, etc. Bing data could help quantify SEO efforts, but it’s still only 9.1 percent of organic search share.

      Note: People searching Bing versus Google probably behave differently to some degree.

      3. Google Webmaster Tool Search Query Data Provides Partial Insight

      Google gives us access to the top 2,000 search queries every day. After understanding limitations, the search query report can be invaluable as it gives a glimpse of how your site performs from Google’s side of the fence. Google also recently mentioned they will be increasing the amount of data available to a year!

      By linking Google Webmaster Tools with AdWords, Google also has given us a report using the same search query data except with more accurate numbers (not rounded).


      Clearly, page-level tracking is more important than ever. Google has forced SEO professionals to look at what pages are ranking and where, and then pull in other sources to guess on performance and form strategies.

      Google will most likely respond to the outcry by giving us access to more detailed search query data in Google Webmaster Tools. As mentioned before, they have already announced an increase of data from 90 days to a year. This may be a sign of how they might help us out in the future.

      Hi Ben! - I believe that there are actually many ways PPC data could be used to help guide SEO strategy. I totally agree with you on your conclusion. Google has forced SEO professionals to look at what pages are ranking and where, and then pull in other sources to guess on performance and form strategies.”

      2 ways to get around this on a budget:
      1. set up campaigns and focus on optimizing Quality Scores (which accounts for keyword relevance and landing page experience) as this information is displayed without spending a large budget to advertise certain keywords
      2. Like “cgrantski” previously mentioned, utilize Bing Webmaster Tools but also no guarantee it’s proportional to Google

      Good article, thank you. - This is not a dig at this article, just a comment about SEO in general. I am disappointed that the SEO industry, particularly those who are making quite a bit of money in it, are not doing any recent research (or publishing it, anyway) to support/disconfirm the following:

      “…people clicking ads versus organic listings probably behave differently to some degree.”

      “People searching Bing versus Google probably behave differently to some degree.”

      In an industry where people spend a lot of time decoding the Google black box, it would be nice to see similar effort going into understanding human questions. I’m referring to real research, not opinions based on experience.

      If there is such research out there, please post a link!

      Great point cgrantski (I realize now PROBABLY is said a lot in this article … ), if you have any particular research in mind I’d definitely be interesting in reading!

      How to Find Keyword Conversions by URL Using Google Webmaster Tools – Ben Goodsell – 20140221

      In January Google announced that numbers will no longer be rounded in Google Webmaster Tool Search Query reports. With that announcement these reports became 20 to 30 percent more accurate.

      Not even available from the API, the Top Pages report is the only place you can find page-level search query data. Does this make it the most valuable report around?

      This article walks through how to get keyword to landing page data by using the Top Pages report as a template. Then consolidating analytics conversions and trending over time in a very basic way.

      Tools used:

      • Google Webmaster Tools
      • Google Analytics
      • Excel

      Capture Top Pages Report Data

      Google Webmaster Tools Search Query reports are the only way to can get decently comprehensive keyword data (we have to take what we can get from Google).

      • Set the dates to the first week of February and expand page entries (figure 2 below) to reveal all keywords.

      Tips: Toggling from the bottom up is quicker and Noah Haibach at Lunametrics has a niceJavaScript workaround for doing all of this automatically.


      • Select, copy, then paste all data into Excel.

      Note: Excel took awhile to think about wanting to paste.


      After pasting, format to remove all links, insert a column to the left of Impressions, add new column headers, and save as a new .xlsx file.

      Note: If pages contain a trailing space be sure to remove otherwise they won’t match up when we use VLOOKUP later.


      • Use the same process to create a similar tab for week 2 of February.

      We now have the template to begin consolidating data from other sources, specifically Google Webmaster Tool Search Query Rankings and analytics Visits and Conversions.

      Consolidate Data

      Using Excel’s VLOOKUP function we’re going to begin to add data from the Google Webmaster Tool Search Query report and Google Analytics (see link if you don’t know how to use VLOOKUP, also quick run through here).

      Tip: Keep the downloads for reference later.

      • Pull Average Rank from Google Webmaster Tool Search Query report.

      Make sure you have the date set properly (this is set for piping in data to the week 1 tab).


      Change 25 rows to 10, then change the grid.s parameter in the URL to the total rows given, in the case 2453.


      Hit enter and then click “Download this table”. Open the file so that you have it and your report .xlsx in accessible windows. We’re going to use this file to pull in Average Position data per keyword.

      In the week 1 report tab (make sure you pulled week 1 GWT data), enter =vlookup and arrow over to the cell you want to use as the lookup_value, then enter a comma.


      In the Google Webmaster Tool Search Query download, highlight the data you want to use for thetable_array and add a comma. We want column H (8th column from left) values to be returned, add an 8, a comma, and finally a zero then hit enter.


      The full formula looked like this:


      • lookup_value – B3
      • table_array – ‘[www-yoursite-com_20140218T230012Z_TopSearchQueries_20140201-20140207.csv]www-yoursite-com_20140218T23′!$A$2:$H$2454
      • col_index_num – 8
      • [range_lookup] – 0

      Drag the column to all applicable cells, making sure not to override the Average rank that already exists for pages. It is normal that #N/A will show up with queries that have less clicks. Search and replace all instances with 1, since keywords can’t be registered in Google’s system without a click.

      Repeat this process for week 2.

      • Pull Organic Conversion data for URLs from Google Analytics.

      Ensuring the proper dates are used, navigate to Customization (upper navigation) -> Create a New Custom Report -> Fill it out so it looks like the image below. Goal Starts can be any conversion data you want to include.


      Change Show rows (bottom right) to 25 then find the explorer-table.rowCount parameter in the URL, substitute the number after %3D with the number of rows in the GA result set. Hit enter thenExport -> CSV.


      Use the VLOOKUP process described previously to add conversions to both the week 1 and week 2 tab.

      The final product should be two tabs with Google Webmaster Tool Top Page report used as a framework, combined with analytics visits and conversion data. Next step, taking this information and creating Ultimate Google Webmaster Tool Dashboard.


      The Ultimate Google Webmaster Tool Dashboard

      Note: Most / all of you are probably better than me at putting together reports and visuals.

      The only thing ultimate about this solution is that is that it’s a way to visualize correlation between URL conversion rate and keyword clicks and impressions.

      What we’re looking at is only the top 25 URLs, expanding this process to include more URLs is simple as noted earlier. This here represents about 60 percent of the site’s Google organic search traffic.

      Highlighted in green is the homepage of the site. We can see that our homepage was presented in search results 28.92 percent less, but our CTR is up almost 40 percent and our conversions up 33.05 percent.

      Tip: Percent change formula is: =(new # – old #)/old #


      Looking at our week tabs we can see it’s because Google listed our page in many new searches that were not made in week 1. So while our page was not presented in search results as much, it was listed in 36 searches not made the previous week. By pulling in what we have in keyword level conversion data from GA we can really start to narrow down which keywords were responsible for this conversion increase and begin using it to form new strategies.



      Unfortunately since the introduction of SSL/”(not provided)” we are no longer able to directly tie conversion data to a search someone used to enter our site. We can now only correlate.

      This article merely scratches the surface of what could and should be done with this data. Enterprise level tools are doing what is shown and more on a massive scale. The key is finding ways to trend over time.

      Read more on Analytics

      • Google Webmaster Tools Now Provides More Specific Search Query Data

        January  8, 2014
      • Google Webmaster Tools Adds Debugging Support in Structured Data Dashboard

        December 16, 2013
      • Social Media ROI: 11 FREE Tools for Measuring Social Media Success

        November 24, 2013
      • Claim 2014s Biggest Easy Traffic Opportunity in 3 Easy Steps

        November 18, 2013
      • How to Use PPC Data to Guide SEO Strategy in a ‘(Not Provided)’ World

        October 21, 2013
      • Google Fixes Webmaster Tools Bug, Missing Search Query Data to Return

        I can’t seem to get this to work. Anyone have any advice? Like why does the saved xlsx contain different fields to the fields saved from the top pages report in webmaster tools?

        Note: If pages contain a trailing space after pasting from Google Webmaster Tools to Excel, be sure to remove otherwise values won’t match up when VLOOKUP is used later.

        I’ve had trouble too. The google analytics screenshots in these instructions look nothing like the google analytics dashboard I see when I log in. Any tips?

        Using ALL of Google Webmaster Tools data to tackle (not provided) – Noah Haibach – 20140123


        I couldn’t believe it when I saw the January 7, 2014th Webmaster Tools update,

        “data in the search queries feature will no longer be rounded / bucketed.”

        At first I thought, why would Google go through all that trouble to obfuscate keyword data in Google Analytics, when they planned on handing all that data back through the search query reports in Webmaster Tools? And of course, they didn’t plan on anything of the sort. The relatively minor update only removes bucketing, and does not address the big issue, that they display only 20% to 25% of search query data. I held out hope that, as it appears in the before and after pictures, the sampling rate had been increased from around 20% to around 35%. But while I’ve noticed small changes in some accounts, it does not appear they’ve made this improvement.

        webmaster tools graph before updateWebmaster Tools before January 7th, 2014 Update


        webmaster tools graph after updateWebmaster Tools after January 7th, 2014 Update

         So, how much of a boon IS the retraction of bucketing in GWT’s search queries? There definitely isn’t anyone complaining. It’s great to no longer see “<10 clicks” for our long tail queries. Of course, the biggest cost of (not provided) to the digital marketing community is the new-found powerlessness to relate search intent with landing page and overall site performance. While much energy and creativity is channeled towards addressing this issue with third party tools, I believe there is yet untapped insight inside Google Webmaster Tools.

        Patch Analytics with Webmaster Tools

        Before we get into the scope of this article, it is worth a shout out to Ben Goodsell who came up with a nice way to beat the bucketing over a year ago. Now that we no longer have to worry about bucketing, we can use an easier variation of his method to combat (not provided). After downloading the organic keyword data from Google Analytics and the search query data from Google Webmaster Tools, I used the latter (now accurate) data to correct the former. I won’t go into the details of my Excel setup, but I included a screenshot below. I can post the setup if there is interest. In this case, we went from 2283 visits with defined keywords in GA to 6802, using the GWT data. Of course when you only start with 4% of your organic visits as not (not provided), a 198%  increase is not as impressive. Still, it is better than nothing.

        Combining GWT Search Query Data with GA Keywords

        Re-connecting Queries with Landing Pages

        Short of using Google Tag Manager to import optimized keywords to your Google Analytics (which everyone should also do, by the way) Webmaster Tools still provides the last in-house way of connecting search queries with your site content. Below is the Search Query->Top Pages report from GWT    Top Page Report in GWT

        The Top Pages Report in GWT

        Notice the number of clicks, circled in green. When I first saw this, I did another impression of the Toronto Raptor, thinking I had discovered a loophole in GWT’s sampling methods. But of course, the ‘displaying 136,552 of 153,511 clicks’ means that nearly 90% of clicks are accounted for in terms of landing page. When you drill down into Keyword by Page, observe that only the standard 20% to 30% of search queries are accounted for. Still pretty neat, though, huh? You can now get an (exact) number of clicks for a given page for any search queries that made it past Google’s sampling method. What could we do with that data? Well it would be great to export it, play around with it, and see what types of additional insights we can draw. Which brings us to the next point of our review of GWT.

        Poor API Support!

        The only part of Google Webmaster tools as frustrating as the (former) bucketing and (ongoing) sampling, is the lack of official API support. There is a an official Java API that cannot return search query data; only crawled keywords,crawl issues, etc. And the unofficial APIs that I have seen (PHP and Python) do not support easy OAuth integration, and have only limited support for search queries. Even the Google Analytics integration is lacking. The search query data cannot be combined with any meaningful GA metric, and, to make things worse, the imported data is still being bucketed! So, to access the Search Queries->Top Pages report without any heavy coding, we need to use the GWT user interface.

        Search Queries->Top Pages Export

        Unlike the standard Top Queries report, we cannot export the complete Top Pages report via the UI. The best we can do is export the summarial table with a breakdown only by pages (and not search queries). We could also technically scroll down  the page, expanding each of the links by hand, but that would be painful. I wrote a couple JavaScript functions to automate the process. The code is rough, but it does download ‘page’, ‘search query’,  and ‘clicks’ columns for each search query entry, in TSV format for Excel. The code is available from GitHub, and is also included below. I have only used it in Chrome. Exporting Top Page Reports

        Steps to export your Search Query->Top Page report from Google Webmaster Tools:

        1.  Log into GWT and navigate to Search Traffic->Search Queries->Top Pages.
        2.  Set the grid.s=25 parameter in the URL to however many pages you want to download. You should also order the pages by clicks if you are downloading less than the maximum number of rows. hl=en&siteUrl= &type=urls&prop=WEB&region&grid.s=719

        1. Set your desired date range. Up to three months prior is available in GWT. As a side note, it might be a good idea to backup your data every three months.
        2. Press F12 to open the JavaScript Developer Tools. Select ‘Console’
        3. First, copy and paste the below JavaScript code into the Developer Tools console. Hit enter. You will be presented with an alert for each page entry in the table that Google is unable to expand. Simply hit enter to cycle through the alerts. When it appears all alerts are done, and all the page entries that Google can access have been expanded, proceed to the next step.
        //expand page entries
           pages = document.getElementsByClassName('goog-inline-block url-detail');
        1. Second, copy and paste the below JavaScript into the Developer Tools console. Hit enter. As long as your pop-ups are not disabled, you will be prompted to download a TSV with your GWT search queries->page data.
        //generate download link
          //make index for page rows
          //getting page rows separate from query rows
          //ordering them, storying 2-item array for
          //each page row, page path and index in
          temp = document.getElementById('grid').children[1].children;
          indices = new Array();
          tableEntries = temp)
          pageTds = document.getElementsByClassName('url-expand-open');
            temp = tableEntries.indexOf(pageTds[i]);
          pageTds = document.getElementsByClassName('url-expand-closed');
          for(i=0;i< pageTds.length;i++){
            temp = tableEntries.indexOf(pageTds[i]);
          indices.sort(function(a,b){return a[0]-b[0]});
          // this is complicated. need to mess with with index of
          // table rows since the aggregate page listing
          // is row just like expanded query rows
          for(i=indices.length-1;i> 0;i--){
            test = indices[i][0]-indices[i-1][0];
          thisCSV = "";
          queries = document.getElementsByClassName("url-detail-row");
          //use count to know when to update the page
          //column for the TSV. sorry if convoluted,
          //did this quickly not elegantly
          count = 0;
              thisPage = indices[count][1];
              do {
              test = indices[count][0]-indices[count-1][0];
              } while(test === 1);
            //because the pages and keywords are all in
            //tags, and were counted as the same level in the index
            thisCSV += thisPage+"\t";
            l = queries[i].children[0].children.length
            if(l > 0) thisCSV+= queries[i].children[0].children[0].text+"\t";
              else thisCSV+= queries[i].children[0].innerHTML+"\t";
            thisCSV += queries[i].children[3].children[0].innerHTML+"\n";
          //create href and click it as means to save tsv
          encodedUri = "data:text/csv;charset=utf-8,"+encodeURI(thisCSV);
          link = document.createElement("a");
          link.setAttribute("href", encodedUri);
          //update name w timestamp if you want
          link.setAttribute("download", "GWT_data.tsv");

        Delving into the Data

        Now that we’ve downloaded the data, let’s talk about what we can do with it. Why did we even download it in the first place? Well, as we mentioned in step 3, GWT data is only available for the past three months. If you regularly backup your data, you will have access to more than three months, and may be able to conduct better keyword analysis. In addition to maintaining historical data, we may be able to glean insight by sorting it and comparing to other data sets. I’ll outline how I used Excel for such a project. My approach was to increase the proportion of total data accounted for by the data displayed in Google Webmaster tools, based on the following assumption.

         the process by which Google filters (chooses which queries are displayed in GWT) is not dependent on the keywords themselves. In other words, while Google might, for example, tend to display less long-tail keywords to us, they are not always blocking the same keywords on a weekly or monthly basis. If the above assumption holds true, we can partition data into weekly or monthly segments, and then estimate clicks for queries that appear in some time segments, but not in others. This technique would be likely be safer when working with monthly data, as there is a better chance the above assumption is met. For sake of demonstration, I download the last three months’ Search Query->Top Pages data  and partition it into six two-week segments. After importing into excel, I create a master list, previewed below.

        pivot table of GWT page-level search queries

        Exported TSV of GWT page-level search queries

         The fourth column is an index that represents the the two-week time period. Next I create a pivot chart with the data, and I am able to display a chart with query parameters as rows and the two-week time periods as columns. The values listed as visits are actually clicks. This method is most applicable to the search queries with a medium-level of clicks. These queries are common enough that they can be expected to be searched every two-weeks or month, but not so common that they need to be regularly included in the GWT reports (or else be conspicuously absent).

        Pivot Chart of Page-Level Search Queries, with Data Filled-In

        Left: Pivot Charts of Page-Level Search Queries.

        Right: With Missing Clicks Estimated


        Using this method, I’ve accounted for 13% more clicks (visits) without introducing new keywords. Further, I’ve only used:

        1. three months of search query data, and
        2. a small website with
        3. quickly changing web pages (the vast majority of landing pages are blog articles).

        This method will be even more useful for:

        1. Those with more than three months historic data
        2. larger websites
        3. websites with more-static web pages.


        1. Scale the monthly estimated corrections using aggregate search volume data. This will help to determine whether the absence of a search query is due to filtering by Google or just a lack of interest by searchers.
        2. Use Dimension Widening to import the refined search query data into Google Analytics, associating it with the landing page dimension.

        Assumptions Revisited

        I had reasoned that between the two-week periods, there are keywords that are sometimes displayed to us in Google Webmaster Tools, and that are sometimes blocked. For any search queries where some two-week periods have zero clicks, I average how many clicks/two-weeks they received over the three-month period, and assign that value to the given query. While there are certainly legitimate cases where a search query had no clicks for a given week, I reason that the error of wrongly assigning a search query click to a given page is less than the gain netted in terms of better understanding our search queries (and on a page-by-page basis at that!)

        And what if Google is consistently hiding the same keywords over the three-month period? I would argue that this would be very hard for Google to achieve while still displaying a relatively consistent percentage of total queries. (what happens if site traffic drops so much on a small web site that Google would be forced to display more than 20% or 30% of keywords?) They probably need to display keywords that have before been classified as hidden, even if they do not admit it.

        Anna N. says:
        Great post! Can you provide more details how to use the Dimension Widening and import the refined search query data into Google Analytics?
        Noah Haibach says:
        I probably should have expanded on what I meant by using dimension widening. We of course have no way of matching organic search visits with their actual search queries. What we might do, however, is probabilistically assign search queries to visits based on landing page. I admit the process is a bit tricky, and requires more initial setup in excel. Here are the steps as I see them:

        1. Download the GWT Top Pages report as I outlined in the article.
        2. Create a new view for your property in GA.
        3. Set a visit-level custom dimension to serve as a key for the dimension widening. Its value should be set to a random number between 1 and n where n is larger than the largest number of organic visits for a landing page displayed (in GWT) for a two week period (for us, it might be 500-1000). Use Math.random() to set this value.
        4. Set up dimension widening in GA based on two keys. The first key would join on landing page, and the second would join on the random value you set with the previous step.
        5. Use the GWT Top Pages report downloaded in step 1. Use the prior two weeks’ search queries, and distribute the random numbers (from 1 to n) so they represent the distribution of visits among the search queries. Do this for each page.
        6. Generate a CSV from the updated GWT Top Pages report. This CSV will have to be updated every two weeks, or as quickly as search trends change for your content.
        7. The Dimension Widening, along with a filter, could be used to rewrite all (not provided) entries as a custom dimension ‘keyword or estimated if not provided’.

        The initial setup is a bit complicated for this method. And there would be upkeep involved to update the Dimension Widening CSV every two weeks or month or so. While it does not reassign ACTUAL search queries to their respective visits, it could provide a more granular understanding of search intent than current landing page reports. It would be especially helpful for large websites that are less impacted by the error involved with our estimations. Let me know if I can provide more specifics or clarification.

        Also, we might wish to simplify the data we downloaded from GWT by removing stop words and grouping similar search queries (before using it for GA dimension widening).

        Chris says:
        Very clever stuff.

        If I understood correctly, the crux is:

        If there’s a time-frame (eg two week period) when a keyword doesn’t appear, and it usually does, then add the keyword with an estimated value (instead of no keyword).

        For this method, how many two week periods in a row can a keyword be missing and still get an estimated value assigned to it?

        As the data set gets bigger you’d need to account for this or you may be adding keywords (eg seasonal keywords) when they don’t exist!

        Noah Haibach says:
        Thanks Chris, and great question!

        To answer your second question first, yes, you definitely need to consider the seasonality of your site/the keyword. I think I mentioned as possible extension, you could try to put certain keywords in context by checking them against Google Web Trends and other services.

        Your first question needs a more nuanced answer. How many two-week periods in a row can a keyword be missing and still get an estimated value assigned? We can never be 100% sure; we must always take a probabilistic approach. I’ll give a rough example where we use a non-parametric estimation. Suppose we have four two-week periods and we have a keyword with the following appearances:

        weeks 1-2 – 1 query
        weeks 3-4 – 0 queries
        weeks 5-6 – 2 queries
        weeks 7-8 – 1 query

        We can thus say that over the 2-month period, the rough chance the keyword appears on any given day is (1+0+2+1)/60 = 1/15. According to the binomial theorem, the probability that we observe no queries for a 2-week period is:

        P(x=0 | n=14,p=1/15)=0.38

        So there’s a good chance the 0 queries was due to chance. Note that this is by no means an accurate model of the probability (the real deal would be more complicated), but it’s a good enough approach to give us an idea of the probability that 0 queries in a 2-week period is due to chance.

        MIke Sullivan says:
        Hey Noah, just released a new product you might like — it downloads all that data you can’t get at through the non-existent API using a simple Excel add-in (Windows only, sorry). First release…more to come…

        Tommy says:
        I have been trying and trying to get this code to download via Chrome, but so far no luck. The first part of the code, when it runs, returns undefined. Yet it looks good in Webmaster Tools. However, when I run the second part of the code to generate a download link, it is returned with an error of “TypeError: Cannot read property ’0′ of undefined” – any advice? Thank you!!!!

        Adam says:
        I had the same problem as you had when copying the script straight from the article into my browser. But when I copied it to a notepad first it worked fine.

        Gerhard Angeles says:
        It worked for me when I refreshed the page.

        Anyways, is there any way to include impressions in the tsv file?

        Thanks so much for this Noah. A great contribution to SEO in today’s (not provided) world.

The following two tabs change content below.


Latest posts by allenpg (see all)

Leave a Reply