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.

The following two tabs change content below.

allenpg

Latest posts by allenpg (see all)

Leave a Reply