The Unknown Power Of Search Queries -- Knowing SQ Part III

In part 2 of this series, we gathered all of the data we would need for our search query mining exercise into Excel, as seen below.



Now, we must take the time to prepare our data for analysis. This will include creating derived fields to bring information to the surface, flagging and deleting noise, converting counts to proportions, etc. We are going to use the power of Excel to our advantage and push our data to its limits to extract value.

Here are some of the questions our data will need to be able to answer easily:

• What search queries have high impressions but no clicks? (might be a good negative candidate)
• What search queries have resulted in a conversion? (promote these to exact match keywords in your account).
• What search queries have a below average CTR for the ad group? (negative candidate)
• What search queries have an above average cost per conversion? (might need a new ad/landing page or may be negative candidates)

 

Step #1: Format your data as an Excel Table

 

1.    Highlight your entire SQR dataset including the header row.
2.    Click on "Format as Table" from the Home ribbon




3.    Choose a style that you like
4.    Click "OK"



5.    Repeat these steps for both the ad group and account keyword structure datasets

Step #2: Flag Duplicate Search Queries

Duplicate search queries are broad and phrase match search queries that are already being targeted in your account as an exact match keyword. In other words, your broad and phrase match keywords might be ad-poaching your exact match keywords.
1.    Go to your AdWords Editor keyword data and cut column C (keyword) and insert it at column A so that "Keyword" is your first column.



2.    Create a new column "DUP_CK" in column "N" in your "sqr_data" tab. Because you formatted your dataset as a table, all you have to do is type the word "DUP_CK" in cell "N1" and a new column will be created.
3.    Type this formula in "N2" (or use the formula wizard):
=VLOOKUP(Table1[[#This Row],[Search term]],editor_kw_data!A:C,2,FALSE)
4.    After Excel is through processing, copy all of column "N" and right click then "Paste Special > Values" back into column "N". This way every time you change something in Excel it doesn’t start processing through your data again (can be annoying if you have 100K search queries).

Step #3: Word Count Column

 

We will use a search query word count later in the analysis process while we are surveying our data.
1.    Create a "WORD_COUNT" column in cell "O1" just as before.
2.    Type this formula in cell "O2": =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Step #4: Create a search query peer CTR column

 

Having a CTR comparison (or reasonable CTR expectation) will be very valuable for our analysis. In order to calculate a "Peer CTR" we must subtract the search queries clicks and impressions from the ad group’s total clicks and impressions and then calculate a CTR.

1.    Create a "PEER_CTR" column in cell "P1"
2.    Type this formula in cell "P2":
=(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,4,FALSE)-Table1[[#This Row],[Clicks]])/(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,5,FALSE)-Table1[[#This Row],[Impressions]])
Friendly formula: (ag_clilcks – sq_clicks)/(ag_imp – sq_imp) = PEER_CTR

Step #5: Create a search query peer conversion rate column

Like "PEER_CTR", a comparison (expected conversion rate) is a great metric to have during your search query analysis. You create it very similarly to how you just created "PEER_CTR".

1.    Create a "PEER_CVR" column in cell "Q1" (CVR = conversion rate)
2.    Type this formula in cell "Q2" similar to the "PEER_CTR" formula:
=(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,10,FALSE)-Table1[[#This Row],[Conv. (1-per-click)]])/(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,4,FALSE)-Table1[[#This Row],[Clicks]])
Friendly formula: (ag_conv – sq_conv)/(ag_clicks – sq_clicks) = PEER_CVR

Step #6: Create a search query peer cost/conversion column

In order for us to later look for a high CPA search query, we must have an average or expected CPA to compare against.

1.    Create a "PEER_CPA" column in cell "R1" (CPA = cost per conversion)
=(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,8,FALSE)-Table1[[#This Row],[Cost]])/(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_data!A:L,10,FALSE)-Table1[[#This Row],[Cost / conv. (1-per-click)]])

Step #7: Create a needed impressions column

In order to answer the question, "How many impression are needed before we should expect to see clicks?" we must calculate a needed impressions metric. We do this by dividing the number of clicks you would like to see by the peer CTR.

Of course, you could use a more "statistically significant" approach as I described in my PPC analysis post. The point is to create a flag that can be used to filter out search queries with sparse data later in our analysis.

1.    Create a "NEEDED_IMP" column in cell "S1"
2.    Type this formula in cell "S2": =1.5/Table1[[#This Row],[PEER_CTR]]

Step #8: Create a needed clicks column

 

In order to answer the question, "How many clicks are needed before we should expect to see conversions?" we must calculate a needed clicks metric. We do this by dividing 1-2 clicks by the peer CTR.

1.    Create a "NEEDED_CLICKS" column in cell "T1"
2.    Type this formula in cell "T2": =1.5/Table1[[#This Row],[PEER_CVR]]

Step #9: Create an enough impressions column

 

In this step we are going to use the "NEEDED_IMP" column that we created earlier with an "IF" statement to set a flag that we can use as a filter later in our analysis.

1.    Create an "ENOUGH_IMP" column in cell "U1"
2.    Type this formula in cell "U2":
=IF(Table1[[#This Row],[Impressions]]>Table1[[#This Row],[NEEDED_IMP]],"Y","N")

Step #10: Create an enough clicks column

Again we are going to use a derived field that we created earlier, "NEEDED_CLICKS" to set a flag for "ENOUGH_CLICKS".

1.    Create an "ENOUGH_CLICKS" column in cell "V1"
2.    Type this formula in cell "U2":
=IF(Table1[[#This Row],[Clicks]]>Table1[[#This Row],[NEEDED_CLICKS]],"Y","N")

Step #11: Create a low CTR column

This is a very important filter that we will use later that helps us focus on search queries with below average CTRs. The formula described below will set a flag for "LOW_CTR" for search queries with a CTR that is 30%+ below its peers in that ad group.

1.    Create a "LOW_CTR" column in cell "W1"
2.     Type this formula in cell "W2":
=IF(Table1[[#This Row],[CTR]]<(Table1[[#This Row],[PEER_CTR]]*0.7),"Y","N")

 

Step #12: Create a low conversion rate column

Just like low CTR, a below average conversion rate can be an attribute of a negative keyword candidate.

1.    Create a "LOW_CVR" column in cell "X1"
2.    Type this formula in cell "W2":
=IF(Table1[[#This Row],[Conv. rate (1-per-click)]]<(Table1[[#This Row],[PEER_CVR]]*0.7),"Y","N")

 

Step #13: Create a high cost per conversion column

A high CPA can also offer insights into potential problems with a search query. The search query may perform better with a different landing page or perhaps it needs to be added as a negative keyword. The formula below sets a flag for search queries that have a CPA 30%+ above its peers.

1.    Create a "HIGH_CPA" column in cell "Y1"
2.     Type this formula in cell "Y2":
=IF(Table1[[#This Row],[Cost / conv. (1-per-click)]]>(Table1[[#This Row],[PEER_CPA]]*1.3),"Y","N")



This might seem like a lot of work, but once you get familiar with these techniques it should only take you about five minutes to create all 12 columns.

I also encourage you to experiment with creating your own derived fields for your analysis. What I have described above comes from much trial and error (with my own data), and is by no means the only way you could/should prepare your data. Start by formulating the questions you will be asking of your data. This will help you figure out what new fields, flags, etc. you will need to answer those questions.

I look forward to my next post on advanced search query mining, in which I will show you how to start mining your prepared data for insights. We will be creating a negative candidates list as well as a keyword expansion list.