Easy Market Opportunity and Gap Analysis With Recovered “Not Provided” Queries

Keylime Toolbox recovers as much “not provided” query data as possible and turns that data into useful insights about your audience and your site’s performance in Google organic search. For instance:

  • What percentage of your site’s traffic is branded?
  • What topics are your audiences most interested in?
  • How does ranking and click through rate differ for each segment?
  • Are bounce rates higher for one segment vs. another?

Keylime Branded and Unbranded Segmentation

We found that in addition to tracking these metrics over time, as Keylime’s Query Analytics reports do, our customers also wanted to slice this data in lots of different ways in one-time reports. So, we’ve launched the custom segmentation tool. For details on how this works, it’s useful to provide a little background on how Keylime recovers query data and what the Query Analytics reports track.

Keylime-Recovered “Not Provided” Query Data

Google is no longer passing search referrer data to analytics programs, but it’s still storing that data in its query logs and it’s from there that it makes the data available in Google Search Console (formerly Webmaster Tools). Keylime Toolbox uses Google’s API to import this query data into your account each nightly and provides detailed analysis from that data.

Google Search Console reports only top queries (which is often not all queries), but also reports top queries for each subfolder, so when you add your site’s top subfolders to Google Search Console, Keylime Toolbox can recover even more query data.

Keylime Toolbox deduplicates any overlapping data between the subfolders and aggregates that data with all query data available in Google Analytics. You can read more about how Keylime recovers “not provided query data here.

With that foundation, Keylime Toolbox can do lots of analysis and provides all kinds of ongoing reporting.

Ongoing Monitoring of SEO Metrics vs. Generating One-Time Analysis

Segmenting queries for ongoing SEO monitoring is most valuable when segmentation isn’t too granular. Most sites find that around five segments are most valuable. But sometimes, you might also want to slice the query data in additional ways to investigate issues or research new content opportunities. For instance:

  • Should we expand a particular topic area?
  • We’ve done keyword research for a topic. What queries in the set do we already get traffic for?
  • We want to write more articles about a particular topic. What kinds of questions are our audiences asking about?

You can always access detailed query data about ongoing monitoring segments as an Excel file. In the example below, you can see Google Analytics and Google Search Console data for individual queries in a particular segment (for a specified time period):

Keylime - Google organic search query data

Using the New Custom Segmentation Tool

The new Custom Segmentation Tool enables you to create custom filters (using regular expressions) and generate queries and associated data based on those filters (for any date range your choose). The resulting Excel reports look exactly like the file shown above, filtered to only the queries that match your filter.

Custom Query Segmentation

Using Excel VLOOKUP + Keylime’s Custom Segmentation Tool For Detailed Query Analysis

Keylime Toolbox’s custom segmentation reports alone provide great data about your site’s Google organic search traffic. But you can combine other data for even cooler insights. Below are just a couple of examples:

Ranking Over Time

Ranking data is problematic for lots of reasons covered lots of other places, but I get that sometimes you really want to take a peek at rankings over time.

Keylime Toolbox reports individual keyword ranking data from Google’s query logs, which means it’s an average of where all searchers saw that keyword ranked. That helps eliminate the problem of skewed ranking data due to personalization that can sometimes happen with scraped rankings.

Since looking at just one keyword in isolation can also provide a skewed view, Keylime Toolbox provides rankings over time at the query segment level rather than the individual keyword level.

Google Rankings Over Time

Ranking By Segment

But sometimes, aggregate ranking data by segment isn’t enough. The detailed Excel reports list all keywords with associated rankings for the selected period, but you can easily use Excel’s VLOOKUP functionality to chart rankings for each keyword over time.

      1. Generate a Query Details Excel file based on a custom filter. In this example, I want to see data about all queries related to ratings and reviews. I’ve also set the date range as a single day, because I want to know how rankings have changed over time (so aggregated rankings across a week or month aren’t as useful in this case).
      2. Use that custom filter to generate additional reports for previous time periods.In this example, I want to see how rankings have changed over the last three months, so I’m going to generate reports for the last Friday of each month. The dates I’ve ended up with are: 6/27/14, 7/25/14, 8/29/14.
      3. If you don’t do a lot of vlookups, here’s an overview of how I did it, but you should really check out Annie Cushing’s great VLOOKUP video tutorial.

Here’s what I did:

      1. Combined the data as three tabs (June, July, August) of a single Excel file.
      2. Selected all of the data in the July tab and named it July (and the same for August).
      3. Added two columns after the June ranking column (one for July ranking data and one for August ranking data).
      4. Put the following formula in the first July column cell: =VLOOKUP(A6,July,10,0) and the following formula in the first August column cell: =VLOOKUP(A6,August,10,0). Then double clicked each cell to copy the formulas to the rest of the columns.

Now I’ve got ranking data for all keywords for all three dates!

Using vlookup for Google rankings

But I want to do some additional analysis. First, I can add conditional formatting to the columns to surface any rankings increases or drops.

Excel conditional formatting

Now I can scan the file and see right away what keywords have gained or lost ranking.

I can do additional calculations from here to, for instance, count how many cells are green or red to see the percentages  of increases/decreases, or highlight only queries that have dropped more than 2 positions.

Ranking Changes

I also can use the same VLOOKUP technique to add traffic (any any other) data for all three dates to see if rankings changes impacted traffic to the site.

Google Traffic Ranking

Keyword Research + Query Data = Market Opportunity of Expanded Content

As you’re doing keyword research, you may want to know what queries the site already ranks for. This can help you drill into what queries perform well and which don’t. (Depending on the situation, you may either want to focus more on topics that resonate with your audience or do a better job engaging them on topics for which you don’t do as well.) You can also identify gaps in content. What topics does your site not get traffic for at all right now?

All of this analysis is problematic (to say the least) in the days of “not provided”.  But with Keylime Toolbox, you can get answers to all of this and more.

For this example, I want to look into expanded my coverage of Lamborghinis (why not?!).

        1. First, I’m going to generate a Query Details Excel file based on a custom filter again, but this time, I’m going to choose a month date range, since that’s the range of data I get from the Google’s AdWords Keyword Planner.
        2. Next, I’m going to generate a keyword research file from AdWords.

I find that my site gets traffic for 976 related queries (a lot of which are long tail and don’t show up in keyword research). Google AdWords shows me 801 queries, and when I use a VLOOKUP, I find that the overlap is only 17 queries. I’ve got a lot of market opportunity!

I can do further analysis on this data. For instance, how much traffic am I getting for the overlapping keywords vs. the average monthly volume for those keywords.

Keyword Volumes

I can also use Keylime Toolbox’s site-specific click through rate data to calculate market opportunity for expanding more into this topic area. Keylime Toolbox’s Query Details Excel file provides an average click through rate for the filtered queries, so to get a rough market opportunity estimate of investing in expanding the topic area, just multiply the search volume for the keywords the site doesn’t get traffic for now by the average click through rate for the segment (in the example below, 10%).

Average CTR

In my Lamborghini example, the search volume for the keywords I’m not getting traffic for now is 20,390 each month. If I can assume an average of 10% of click through rate for those queries if I build content around them and start ranking for them, then my market opportunity is 2,039 visits a month from Google organic search.

The possibilities are endless!

Get Started Now With A Free Trial!

Sign up now for your free trial and check out Keylime Toolbox for yourself.

If you’d like to include log analysis or are an agency managing lots of sites, contact us for details of what we have to offer and discounted pricing that’s available.

Sign Up Now Take the Product Tour

Leave a comment

Your email address will not be published. Required fields are marked *