Category Archives: Webmaster Tools

Re-presenting CTRs as a function of Query x Position on SERP

Scraping Top Queries in GWT yields (after a fairly labour-intensive process) a table of Query-specific CTRs expressed as a function of Position on the SERP.

We want to re-present the CTRs in a more meaningful way – including grouping the CTRs into these categories: (Position 1 to 5 on Page 1 of SERP), (Position 6 to 10 on Page 1), (Page 1), (Page 2), and (Page 3 or more).

  1. Open the original data file from scraping the Top Queries: “<start date>-<end date>-<filter>-positions-tq.xls.”
  2. Open the processing file: “CTRs as a function of tq x pos on SERP – Template.xls.”
  3. Save #2 with appropriate identifiers.
  4. Copy  #1 to sheet #2:”tq x pos orig”.
  5. Copy column = “Query” from sheet #2:”tq x pos orig” to sheet#2:”queries from tq x pos”.
  6. Apply Filter -> Advanced Filter to sheet#2:”queries from tq x pos” to copy unique Queries only to an adjacent column (see Figure below).
  7. Copy column of unique Queries from sheet#2:”queries from tq x pos” to sheet#2:”queries unique”.
  8. Copy column = “Query” from sheet#2:”queries unique” to column = “Query” of sheet#2:”CTRs dyn”.
  9. After some time the CTRs will be re-presented.

excel advanced filter queries in ctrs x pos

Our final step is to consolidate the CTRs into groups corresponding to different Segments or Pages of the SERP results.

  1. Copy and Paste Special = Values the columns headed “BLOCK #1″ from sheet#2:”CTRs dyn” to the columns headed “BLOCK #1″ in sheet#2:”CTRs flat”.
  2. Ditto for columns headed “BLOCK #2″ and “BLOCK #3″.
  3. Save the file.

… and we’re done!

Further analysis of these data is usually done on the Copy and Paste Special = Values of sheet#2:”CTRs flat”.

Scraping Top Pages in GWT

Set the Parameters

  • Select Report Type = Top Pages.
  • Set Date Range with “Start Date” and “End Date”.
  • Select Filter = {All | Web | Mobile} – disregard “Location” and “Traffic” options.
  • Set Rows to display = Total number of Top Queries by editing the “grid.s” parameter in URL.
  • In practice, we generate 3 datasets (Filter = {All | Web | Mobile})  for a given Date Range.
  • The most important single dataset is Filter = Web.
  • The comparison of Filter = Web vs. Filter = Mobile may also be of interest – if so, use Filter = All to generate the exhaustive list of Queries to support this comparison.

Download two datasets and save as .csv files:

  • Click on “Download This Table” – save as dtt-tp.csv.
  • Click on “Download Chart Data” – save as dcd-tp.csv.

Import the .csv files to Excel

  • Import dtt-tp.csv as comma-delimited UTF-8 format Data into Excel – save as <start date>-<end date>-<filter>-dtt-tp.xls e.g. 20140415-20140428-web-dtt-tp-xls.
  • Ditto dcd-tp.csv - save as <start date>-<end date>-<filter>-dcd-tp.xls.

Generate iMacros script to Blow-Out Queries

  • Copy and paste source code for a given Profile into  source.txt.
  • Using PowerGrep, re-write source.txt to as iMacros TAG commands:
    PowerGrep - Settings for Collect data from GWT source code
    Where we search on (getElementById)(\u0028\u0022)([^\u0022]+) and replace with/collect to TAG POS=1 TYPE=A ATTR=ID:\3
  • Copy and paste TAG POS lines from source.txt into iMacros macro scrape-tp.iim:
    VERSION BUILD=8810214 RECORDER=FX
    TAB T=1
    URL GOTO=https://www.google.com/webmasters/tools/top-search-queries?hl=en&siteUrl=http%3A%2F%2Fwww.site.com%2F&de=20131207&db=20131201&qv=change&type=urls&grid.s=500&prop=ALL&region=&more=true
    TAG POS=1 TYPE=A ATTR=ID:ud_4C057013_C11A7450_F2B4C340
    TAG POS=1 TYPE=A ATTR=ID:ud_7BB0D7E1_944C001F_71BB9CF7
    ...

Blow-Out & Scrape the “Top Pages” Queries

  • Run scrape-tp.iim to blow-out the queries for Top Pages.
  • Select, copy and paste the blown-out queries on-page table into Excel (say) <start date>-<end date>-<filter>-scr-tp.xls.
  • Note that the “Change” data is available for previous 30 days only.
    Page Impressions Change Clicks Change CTR Change Avg. position Change
    www.site.com/
    92,789 0% 10,536 -8% 11% -1 4.2 -0.2
    query01 62,674 7,742
    query01 5,146 723

For the other Filters defined in Step 1, repeat Steps 2 – 5.

Leaving us with …

For each of the 3 Filters defined in Step 1, we have generated 3 Excel files:

  • <start date>-<end date>-<filter>-dtt-tp.xls – from “Download This Table” – Step 3
  • <start date>-<end date>-<filter>-dcd-tp.xls – from “Download Chart Data” – Step 3
  • <start date>-<end date>-<filter>-scr-tp.xls – from scraping “Top Pages” table on-page - Step 5

See my video of (1) and (2) -  WMT – Setting Parameters.
For comparing Filter-specific datasets see Merging GWT \”Top Queries\” for Filter = (All | Web | Mobile).

It’s possible to compare datasets over time where “Change” data is available in some datasets but not in others – but this is a bit of a hassle and so our tools will allow for comparing datasets either with or without the Change data, but not both.

Scraping Top Queries in GWT

Set the Parameters

  • Select Report Type = Top Queries.
  • Set Date Range with “Start Date” and “End Date”.
  • Select Filter = {All | Web | Mobile} – disregard “Location” and “Traffic” options.
  • Set Rows to display = Total number of Top Queries by editing the “grid.s” parameter in URL.
  • In practice, we generate 3 datasets (Filter = {All | Web | Mobile})  for a given Date Range.
  • The most important single dataset is Filter = Web.
  • The comparison of Filter = Web vs. Filter = Mobile may also be of interest – if so, use Filter = All to generate the exhaustive list of Queries to support this comparison.

Download two main datasets and save as .csv files:

  • Click on “Download This Table” – save as dtt-tq.csv.
  • Click on “Download Chart Data” – save as dcd-tq.csv.

Import the .csv files to Excel

  • Import dtt-tq.csv as comma-delimited UTF-8 format Data into Excel – save as <start date>-<end date>-<filter>-dtt-tq.xls e.g. 20140415-20140428-web-dtt-tq-xls.
  • Ditto dcd-tq.csv - save as <start date>-<end date>-<filter>-dcd-tq.xls.

Generate iMacros script to Browse the Queries

  • Every Top Query links to two tables – (1) the Query’s target URLS; (2) the Query’s CTR as a function of POSITIONS on the SERP (without regard to the target URL). We will use iMacros to browse and download these tables for every Query:
  • Sort the “Top Queries” by Impressions-Descending – as we will want to align with <start date>-<end date>-<filter>-dtt-tq.xls (Step 3).
  • Copy the source code for the “Top Queries” page to source.txt.
  • Using PowerGrep, re-write source.txt as a series of EVENT TYPE commands. PowerGrep searches for (wmt.common.display.Star\(‘star_)([^\u0027]+) and /collects:
    
    EVENT TYPE=CLICK SELECTOR="#qd_\2" BUTTON=0
    EVENT TYPE=CLICK SELECTOR="#download-urls>DIV" BUTTON=0
    EVENT TYPE=CLICK SELECTOR="#top-search-queries>DIV:nth-of-type(13)>DIV:nth-of-type(3)>BUTTON" BUTTON=0
    EVENT TYPE=CLICK SELECTOR="#download-positions>DIV" BUTTON=0
    EVENT TYPE=CLICK SELECTOR="#top-search-queries>DIV:nth-of-type(13)>DIV:nth-of-type(3)>BUTTON" BUTTON=0
    BACK
    

    Note: one or both of the values of xx and yy in DIV:nth-of-type(xx)>DIV:nth-of-type(yy) seem to change from session to session – and may need to be changed in the iMacros script. Be prepared – this is a LONG process – and the script crashes periodically, and you must delete those commands that have executed properly before proceeding with the remaining commands.

  • Copy and paste the “EVENT TYPE … BACK” lines from source.txt into scrape-tq.iim.
  • Run scrape-tq.iim to browse the Queries and download/save the URLS and POSITIONS tables as *.csv files.
  • Separate the POSITIONS and URLS *.csv files into two folders.

Edit the Query-specific URLS and POSITIONS files

Deleting the Header

  • We use PowerGREP to delete the Header in the *.csv files, so it isn’t repeated when we merge the files into one:
  • In the URLS files, delete “Page,Impressions,Change,Clicks,Change,CTR,Change”
  • In the POSITIONS files, delete “Position in search results,Impressions,Clicks,CTR”
  • We will insert a modified Header (“New Header” = “Query,” & “Old Header”) into the merged file.

Inserting the Query = FileName

URLS files

Using PowerGREP’s Search & Replace:

  • To deal with lines beginning with a ” in front of the Url, (^\u0022)(.*) –> %FILENAMENODOT%\u002C\1\2
  • To deal with lines with no “, (^http)(.*) –> %FILENAMENODOT%\u002C\1\2

POSITIONS files

Using PowerGREP’s Search & Replace:

  • To deal with lines beginning with numbers 1 to 5, (^[\d\w]+)([\d\w\002B\u002C\u0025]+$) –> %FILENAMENODOT%\u002C\1\2
  • For lines beginning with the characters 6 to 10, (^6 to 10)([\d\w\002B\u002C\u0025]+$) –> “%FILENAMENODOT%\u002C\1\2
  • Then (^2nd page)([\d\w\002B\u002C\u0025]+$) –> %FILENAMENODOT%\u002C\1\2
  • And (^3rd page )(\u002b)([\d\w\002B\u002C\u0025]+$) –> %FILENAMENODOT%\u002C\1\2\3

Merge the Query-specific URLS and POSITIONS files

  • Open a cmd window, navigate to the folder containing the URLS *.csv files, and run the command copy *.csv urls.csv – likewise, for the folder containing the POSITIONS *.csv files.
  • Import urls.csv and positions.csv as comma-delimited, UTF-8 format Data in Excel. Save as —urls-tq.xls and —positions-tq.xls.
  • Insert the Column Headings:
    • In —urls-tq.xls, insert “Query,Page,Impressions,Change,Clicks,Change,CTR,Change”
    • In —positions-tq.xls, insert “Query,Position,Impressions,Clicks,CTR”
  • Scan the *.xls files – usually there are a few entries out-of-whack – it’s usually clear how to fix them.
  • To convert the Unicode characters in some of the URLs, we found this site is great – http://code.cside.com/3rdpage/us/url/converter.html – just paste all the URLs, including the “%”s into the widget, decode, then paste the conversion into the Excel.

Repeat for the other Filters defined in Step 1.

Leaving us with …

For each of the 3 Filters defined in Set the Parameters, we generate 4 Excel files:

  • <start date>-<end date>-<filter>-dtt-tq.xls – from “Download This Table”
  • <start date>-<end date>-<filter>-dcd-tq.xls – from “Download Chart Data”
  • <start date>-<end date>-<filter>-urls-tq.xls – from scraping/merging “Top Queries” URLS tables
  • <start date>-<end date>-<filter>-positions-tq.xls – from scraping/merging “Top Queries” POSITIONS tables

Also see Re-presenting CTRs as a function of Query x Position on SERP.

Downloading data from Google Webmaster Tools

Today, Google has published some Python scripts which allow an automated download of data from the Google Webmaster Tools, namely the Search Query data.

UPDATE: I created a Java version of the Search Query downloader. Any feedback would be great.

The two examples included provide a simple script to download search query data for the last month into a CSV file and another to create a Google Docs spreadsheet containing that data.

The required steps to run the scripts are documented in the Project Wiki, assuming you’ve got Python running already. The ‘selected_downloads’ variable may be extended to include the TOP_PAGES alongside the TOP_QUERIES in the download:

selected_downloads = ['TOP_QUERIES', 'TOP_PAGES']

 

Downloading data for all your sites

To download the search query data for all sites I have in my Google Webmaster Tools account I changed the example script as follows:

# Instantiate the downloader object
downloader = Downloader()
# Authenticate with your Webmaster Tools sign-in info
downloader.LogIn(email, password)

# Get the list of sites available
sites = downloader.GetSitesList()

# Initiate the download
for site in sites:
  print site.title.text
  try:
    downloader.DoDownload(site.title.text, selected_downloads)
  except ValueError:
    print "No JSON data"

The function GetSitesList() needs to be added to downloader.py:

  def GetSitesList(self):
    stream = self._client.request('GET', self.SITES_PATH)
    sites = wmt.SitesFeedFromString(stream.read())

    return sites.entry

with SITES_PATH being defined as

  SITES_PATH = '/webmasters/tools/feeds/sites/'

Additionally gdata.webmastertools needs to be imported, so add this line to the list of imports at the beginning of the script:

import gdata.webmastertools as wmt

12 COMMENTS

  1. Looks very useful script – however a little problem – is there a “wmt” not declared somewhere in your example to download all data? I get a “global name ‘wmt’ not defined” when I put your GetSitesList() to the downloader script.

    • Mark, thanks for spotting this and apologies for the mistake. I forgot to mention the import line which I have now added to the article.

      Sorry for not replying earlier but I’ve been away for some days.

  2. Pingback: Keyword not provided | FrankOli.de
  3. hi! do you find how to download charts? when download 30days charts from gwt manually – it saved with name timeseries_queries. But i cannot find any method to download them automatically. But it will be really nice to integrate all stats at one interface.

    • Hi Varun,

      no, I haven’t tried to use OAuth with this script. I opted for the much simpler username / password authentication which I consider more practical for the use in a scheduled script.

      Frank

      • I totally agree with you. Somehow I was hoping to have all my robots working without I am having them to store any credential for security reasons but OAuth/2 just in case to put them in my dev environment where more guys have access then I can count :).

  4. Pingback: Chrome bald auch mit Suche über SSL – noch mehr (not provided) | Webkruscht

Download search queries data using Python – 20111222

http://googlewebmastercentral.blogspot.ca/2011/12/download-search-queries-data-using.html

For all the developers who have expressed interest in getting programmatic access to the search queries data for their sites in Webmaster Tools, we’ve got some good news. You can now get access to your search queries data in CSV format using a open source Python script from thewebmaster-tools-downloads project. Search queries data is not currently available via the Webmaster Tools API, which has been a common API user request that we’re considering for the next API update. For those of you who need access to search queries data right now, let’s look at an example of how the search queries downloader Python script can be used to download your search queries data and upload it to a Google Spreadsheet in Google Docs.

Example usage of the search queries downloader Python script
1) If Python is not already installed on your machine, download and install Python.
2) Download and install the Google Data APIs Python Client Library.
3) Create a folder and add the downloader.py script to the newly created folder.
4) Copy the example-create-spreadsheet.py script to the same folder as downloader.py and edit it to replace the example values for “website,” “email” and “password” with valid values for your Webmaster Tools verified site.
5) Open a Terminal window and run the example-create-spreadsheet.py script by entering “python example-create-spreadsheet.py” at the Terminal window command line:

python example-create-spreadsheet.py

6) Visit Google Docs to see a new spreadsheet containing your search queries data.


If you just want to download your search queries data in a .csv file without uploading the data to a Google spreadsheet use example-simple-download.py instead of example-create-spreadsheet.py in the example above.

You could easily configure these scripts to be run daily or monthly to archive and view your search queries data across larger date ranges than the current one month of data that is available in Webmaster Tools, for example, by setting up a cron job or using Windows Task Scheduler.

An important point to note is that this script example includes user name and password credentials within the script itself. If you plan to run this in a production environment you should follow security best practices like using encrypted user credentials retrieved from a secure data storage source. The script itself uses HTTPS to communicate with the API to protect these credentials.

Take a look at the search queries downloader script and start using search queries data in your own scripts or tools. Let us know if you have questions or feedback in the Webmaster Help Forum.

Written by , Webmaster Trends Analyst

Barry Wise said…

Excellent – been waiting for this. Just curious to know why Google always seems to prefer Python over PHP

DECEMBER 23, 2011 AT 4:25 AMJavier said…

Schedule in Windows 7  http://windows.microsoft.com/en-US/windows7/schedule-a-task

DECEMBER 23, 2011 AT 8:30 AMDan DeVeney said…

Really great addition guys. Thanks! As an FYI to everyone else, I’ve found you can change the selected_downloads in the script from “TOP_QUERIES” to “TOP_PAGES” to pull you top pages report instead.

DECEMBER 23, 2011 AT 11:14 AMFrank said…

For those who like to use this to download the data for all their sites at once, I’ve changed the scripts somewhat: http://www.webkruscht.com/2011/downloading-data-from-google-webmaster-tools

DECEMBER 24, 2011 AT 8:22 AMThomas Hey’l said…This is really a great Christmas present. With a little help of JohnMu I’ve been able to pimp those scripts so their output becomes configurable with dates, regions, query types, pages or queries, etc.
Please add the mapping for TOP_QUERY_CHART and TOP_PAGES_CHART to the JSON list of downloadable files – this would really improve the tool.

 

DECEMBER 25, 2011 AT 7:32 AMlaborant said…Hi I’m stuck at 4) where example-create-spreadsheet.py is executed. There I get thid output:
Traceback (most recent call last):
File “C:\Python27\Lib\gdata-2.0.15\test2\exa
in
downloader.DoDownload(website, selected_do
File “C:\Python27\Lib\gdata-2.0.15\test2\dow
d
sites_json = json.loads(available)
File “C:\Python27\lib\json\__init__.py”, lin
return _default_decoder.decode(s)
File “C:\Python27\lib\json\decoder.py”, line
obj, end = self.raw_decode(s, idx=_w(s, 0)
File “C:\Python27\lib\json\decoder.py”, line
raise ValueError(“No JSON object could be
ValueError: No JSON object could be decoded

The gdata python client works I’ve tested it with the example code from the installation HowTo. I’ve modified email password and website.
Does anyone has an idea?

DECEMBER 31, 2011 AT 10:42 PMSteve said…

If you’re looking for PHP support: I wrote something up here: http://code.google.com/p/php-webmaster-tools-downloads/source/browse/

JANUARY 26, 2012 AT 4:39 AMcarinth said…

I was considering using Google and you associates for web publishing and investing. However since you have now abandoned Democracy and taken the Communist view of Corporate Might is Right by going along will an Illegal bill which promotes regression, monopolization, subjugation and dictatorship. I hope you guys crash and burn for what you have done.

JANUARY 29, 2012 AT 5:18 AMUnknown said…

Seems the script is no more working for External Links and other features. Looks like the _GetDownloadList function returns just 2 or 3 downloadable URLs…

JULY 5, 2012 AT 11:57 AMMichael Stitt said…

Can you point me to the documentation for tweaking these files? For example, This gives the top search queries for the past 30 days, but what if I only want data for the past week? Or yesterday? Thanks for the article!

MARCH 5, 2013 AT 2:15 PMMichael Stitt said…

Nice article! I realize you wrote it over 2 years ago but the scripts still work. Can you point me to any documentation showing how to change the date range? For example, what if I only want top queries for the past week, or day?

JULY 3, 2013 AT 2:30 AMAaron said…

I was able to customize the date range by editing downloader.py and changing “url = self._GetFullUrl(path)” to “url = self._GetFullUrl(path + ‘&prop=WEB&region&db=20130801&de=20130807&more=true&format=csv’)” where “db” is the beginning date” and “de” is the end date.

SEPTEMBER 27, 2013 AT 3:49 PMMatt said…

got it to work and it works like a charm. can anyone point me in the right direction on how to setup it up so that it creates automatic monthly exports?

OCTOBER 1, 2013 AT 5:13 AMClayton Sheppard said…

Does this work with python33?

OCTOBER 17, 2013 AT 11:09 AMGoogle Webmaster Central said…

Hi everyone,

Since over a year has passed since we published this post, we’re closing the comments to help us focus on the work ahead. If you still have a question or comment you’d like to discuss, free to visit and/or post your topic in our Webmaster Central Help Forum.

Thanks and take care,
The Webmaster Central Team

Google – Update to Top Search Queries data – 20120125

http://googlewebmastercentral.blogspot.ca/2012/01/update-to-top-search-queries-data.html

Starting today, we’re updating our Top Search Queries feature to make it better match expectations about search engine rankings. Previously we reported the average position of all URLs from your site for a given query. As of today, we’ll instead average only the top position that a URL from your site appeared in.

An example
Let’s say Nick searched for [bacon] and URLs from your site appeared in positions 3, 6, and 12. Jane also searched for [bacon] and URLs from your site appeared in positions 5 and 9. Previously, we would have averaged all these positions together and shown an Average Position of 7. Going forward, we’ll only average the highest position your site appeared in for each search (3 for Nick’s search and 5 for Jane’s search), for an Average Position of 4.

We anticipate that this new method of calculation will more accurately match your expectations about how a link’s position in Google Search results should be reported.

How will this affect my Top Search Queries data?
This change will affect your Top Search Queries data going forward. Historical data will not change. Note that the change in calculation means that the Average Position metric will usually stay the same or decrease, as we will no longer be averaging in lower-ranking URLs.

Check out the updated Top Search Queries data in the Your site on the web section of Webmaster Tools. And remember, you can also download Top Search Queries data programmatically!

We look forward to providing you a more representative picture of your Google Search data. Let us know what you think in our Webmaster Forum.

Posted by , Google Analytics team, and , Webmaster Trends Analyst

I’m afraid I still find the data on the table baffling. For example, it says that the average position for my site for the term “chevron b19″ is 55th. I would defy anyone to find a way of searching for “chevron b19″ on Google that gets my site any position other than first. It’s not exactly a common subject and I have the only detailed page dedicated to the subject. It can’t possibly be averaging 55th.

JANUARY 26, 2012 AT 3:41 AMDavidHarnadek said…

This only affects the Top Queries report in GWT or GA. There are no changes to SERPs because of this change.

Prior to the change, if you had 3 URLs return for a search query, they were all weighted the same when averaging. Now Google only takes the highest ranking URL for that search query when averaging your position.

We should all see somewhat higher average position on the Top Queries report (unless you had only one URL return for the query, in which case there will be no change).

JANUARY 26, 2012 AT 8:23 AMUnknown said…

This is good, as the data might make some usable sense now. But, wait, what!?? You were reporting average rank of all ranking pages??? How was that even useful before? No wonder it was so inaccurate. It was reporting something whacky, not what anyone would expect (or use) at all.

JANUARY 26, 2012 AT 9:51 AMBarbaraD said…

Thank you, this is already smth, because data shown before was way too far for the reality.

Still, i have a question. You said Google counts the highest rankings now. So if Bob saw my site on the 3rd position, and Jane on the 5th, the average position will be 4. Ok, sounds good. But what if Tom, Jack and another 100 people saw me on the second page, but Bob and Jane saw me once on the positions 3 and 5, will it still show 4? Even i was there only twice?
I would appreciate more details here. Thanks

JANUARY 30, 2012 AT 8:22 AMSusan Moskwa said…

@BarbaraD:

If 100 people saw your site in position 11 (and no higher), Bob saw it in 3rd position, and Jane saw it in 5th position, the average would be ((100 * 11) + (1 * 3) + (1 * 5))/102 = 10.8

FEBRUARY 3, 2012 AT 2:09 PMBarbaraD said…

@Susan

thank you for this formula.
let’s take a more complicated case)
The site was shown 4002 times (Impressions). Once on the 3rd place, and once on the 5th place. The rest 4000 times much lower. So it looks like this:
((1000 * 11) + (1 * 3) + (1 * 5)+(1000 * 20) + (1000 * 30) + (1000 * 35))/4002 = 3.8
This is irrelevant
Ok, better example:
((1 * 11) + (1 * 20) + (2000 * 30)+(7000 * 90))/9002 = 7.5
Your site was not even in top 10 and GWT will show us 7.5 position when the site was on the 90th position all the time?
I do not think the formula you provided is correct ot used by Google
Would like to hear the answer from the author of this article

FEBRUARY 10, 2012 AT 1:37 AMSusan Moskwa said…@BarbaraD:
Your math is incorrect. We would show 23.9 as the average position for the first example, and 76.6 as the average position for the second example.

 

Example 1:
(1000 * 11) + (1 * 3) + (1 * 5) + (1000 * 20) + (1000 * 30) + (1000 * 35) = 96008
96008 / 4002 = 23.9

Example 2:
(1 * 11) + (1 * 20) + (2000 * 30) + (7000 * 90) = 690031
690031 / 9002 = 76.6

JULY 20, 2012 AT 3:26 AMGoogle Webmaster Central said…

Hi everyone,

Since over a year has passed since we published this post, we’re closing the comments to help us focus on the work ahead. If you still have a question or comment you’d like to discuss, free to visit and/or post your topic in our Webmaster Central Help Forum.

Thanks and take care,
The Webmaster Central Team

Merging GWT “Top Queries” for Filter = (All | Web | Mobile)

Starting with the Original “Top Queries” Excel Files

For a given Time Range, the “Top Queries” for Filter = (All | Web | Mobile) are readily saved to separate Excel files (see Scraping Google’s Webmaster Tools – Search Queries  – Step 2):

  • all-tq-dtt.xls
  • web-tq-dtt.xls
  • mobile-tq-dtt.xls

The tables in these *.xls files contain the following data:

Query Impressions Clicks CTR Avg. position
query01 20,812 2,597 12% 4.1
query02 3,912 342 9% 4.4

Creating a Master “Top Queries” Excel File

Our Master “Top Queries” file – xxx – consists of the following Worksheets:

  • All – where we copy the contents of all-tq-dtt.xls
  • Web – where we copy the contents of web-tq-dtt.xls
  • Mobile – where we copy the contents of mobile-tq-dtt.xls
  • Merge – where we use the vlookup function to align the contents of the Web and Mobile worksheets by Search Query with the contents of the All worksheet

The Merge worksheet provides three additional statistics comparing the (Web vs. All) and (Mobile vs. All) “Top Queries” data:

  • %Imps – the percentage of Impressions for Query[i] on All platforms that are accounted for by (say) the Web
  • %Clicks – the percentage of Clicks for Query[i] on All platforms that are accounted for by (say) the Web
  • w2aCTR or m2aCTR – the ratio of the CTR for Impressions of Query[i] on (say) the Web to the CTR for Impressions of Query[i] on All platforms
All Platforms Web Only
Query Imps Clicks CTR AP Imps Clicks CTR AP %Imps %Clicks w2aCTR
query01 20,812 2,597 12% 4.1 17,389 2,206 13% 3.6 84% 85% 1.02
query02 3,912 342 9% 4.4 3,178 280 9% 4.1 81% 82% 1.00

Comparing CTRs for Impressions on Mobile vs. the Web

We may well expect the value of w2aCTR (and m2aCTR) to be close to unity. A value of w2aCTR << 1.0 would suggest that an Impression of Query[i] on the Web was less likely to result in a Click than an Impression of Query[i] on All platforms; likewise, a value of w2aCTR >> 1.0 would suggest that an Impression of Query[i] on the Web was more likely to result in a Click than an Impression of Query[i] on All platforms.

Of course, the CTR for Impressions of Query[i] on All platforms is highly correlated with the CTR for Impressions of Query[i] on the Web, particularly since Impressions on the Web account for 70+ percent of Impressions on All platforms – so the statistic w2aCTR is not so informative; the statistic m2aCTR has a similar difficulty.

A more promising statistic is m2wCTR – the ratio of the CTR for Impressions of Query[i] on Mobile to the CTR for Impressions of Query[i] on the Web. A value of m2wCTR equal to unity indicates that an Impression of Query[i] on Mobile is as likely to result in a Click as an Impression of Query[i] on the Web. A value of m2wCTR << 1.0 would suggest that an Impression of Query[i] on Mobile was less likely to result in a Click than an Impression of Query[i] on the Web; likewise, a value of m2wCTR >> 1.0 would suggest that an Impression of Query[i] on Mobile was more likely to result in a Click than an Impression of Query[i] on the Web.

Note:

  1. When the Mobile CTR = 0 AND the Web CTR = 0, then m2wCTR = 1
  2. When the Mobile CTR = 0 AND the Web CTR > 0, then m2wCTR = 0
  3. When the Mobile CTR > 0 AND the Web CTR = 0, then m2wCTR = #DIV/0!
  4. When the Mobile CTR = #N/A OR the Web CTR = #N/A, then m2wCTR = #N/A

In a One Week sample, we found m2wCTR = #N/A for about 3-in-5 Queries and m2wCTR = #DIV/0! for about 1-in-5 Queries – meaning we obtained numerical m2wCTRs for only 1-in-5 Queries.

The increasing availability of numerical m2wCTRs with increasing sample sizes (e. g. Two Week sample) is so far unknown to me.