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”.

The following two tabs change content below.


Latest posts by allenpg (see all)

Leave a Reply