PPC Data Analysis (Peer Comparision) Using Excel Sheets

inShare5

My new favorite saying for PPC is, “Context is king!”  How do you know if a keyword’s CTR is good or bad?  How about your keywords CVR (conversion rate)?  We determine this by comparing our keyword’s performance to the average performance of the other similar keyword/ad combinations.

“Effective PPC analysis is about finding the segments in your account that deviate from the norm. In many cases, you’re leaving opportunity on the table.” –Alex Cohen

PPC analysts make these types of comparisons all the time, although not always through a systematic, data-driven approach.  I want to show you how you can add these valuable comparison metrics to your data-analysis process, so we are not just doing these types of comparisons intuitively. 
 

I will be using KEYWORD_PEER_CTR for this article, but most of the techniques I will be demonstrating could also be applied to other comparison metrics like: PEER CVR, PEER CPA, etc.  These types of metrics can also be calculated at the campaign, ad-group, and text ad levels and are not limited to just keywords.

KW_PEER CTR: the proportion of clicks and impressions for a given ad-group minus the keyword’s own performance data (i.e. KW_PEER_CTR = (adgroup_clicks – keyword_clicks)/(adgroup_imp – keyword_imp).

Now in order for this type of calculation to have the most benefit, it is important to have a best-practice account structure with tightly themed ad-groups.  Regardless, your keyword’s performance is tied directly to the same ads as all of the other keywords in the ad-group, so the calculation I described above is a valid way to create this comparison metric.

Peer calculations, such as the one described above are finding their way into some of the industry’s PPC software platforms.  You will often see them under the heading of ‘best-practice’ filters, alerts, or rules.  And while this type of calculation is especially suited for the database environment that drives these software platforms, you can easily create peer calculations using your PPC reports and Excel.

Keyword PEER_CTR Using an AdWords Keyword Report and Excel

 

Step #1: Run an AdWords Keyword Report

The first thing that you will need is an AdWords keyword report.  You can download this report directly from your ‘Campaigns’ tab in the AdWords web-interface or through your MCC Reports Center.

Regardless, at a minimum you will need to include the following columns in order to make our peer_ctr calculation:

• Keyword
• Ad-group
• Impressions
• Clicks
• CTR

Of course, you can pull more columns if you plan on performing additional analysis, but for our purposes this will be sufficient. 

Step #2: Format your data as an Excel Table

Formatting your dataset as a table in Excel has many benefits that I won’t go into now, but as you work with your data they will become evident.

1. Highlight your entire Keyword report including the header row (not the totals row at the bottom).
2. Click on “Format as Table” form the Home ribbon




Format as Table


3. Choose a style that you like
4. Click “OK”

 

Step #3: Create an Ad Group Roll-up of Your Keyword Report

Because we are comparing our keyword’s performance against that of its peers in its ad-group we will need impressions and clicks aggregated (summed) at the ad-group level.

1. Select any cell in your new table and Insert a PivotTable in a new worksheet (since we formatted as a table we don’t have to manually select the entire dataset).




Insert a Pivot Table


2. Add Ad Group to the ‘Row label’ and Sum Impressions and Clicks as Values
3. Copy the data from the pivot table and Paste Special > Paste Values in a new worksheet.
4. Highlight your entire dataset and “Format as a Table” as described above.




Ad Group Table


5. You can name this worksheet “adgroup_lookup”




Keyword Report & Ad Group Lookup Worksheets

Step #4: VLOOKUP Magic

 

In order for us to create this calculation (KW_PEER_CTR = (adgroup_clicks – keyword_clicks)/(adgroup_imp – keyword_imp) we will use Excel’s VLOOKUP formula.

1. In your keyword report worksheet go to the right most open header cell and type “PEER_CTR” and hit enter.  Because we formatted as a table you will see a new column populate the full length of the dataset.
2. In the first cell of your new column type the following formula:
=(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_lookup!A:C,3,FALSE)-Table1[[#This Row],[Clicks]])/(VLOOKUP(Table1[[#This Row],[Ad group]],adgroup_lookup!A:C,2,FALSE)-Table1[[#This Row],[Impressions]])
Friendly Formula: (adgroup_clicks – kw_clicks)/(adgroup_imp – kw_imp) = PEER_CTR





Peer CTR Formula
3. Next you can add a CTR_DIFF column and calculate the difference in keyword CTR compared to its peer’s CTR using this formula:

=(Table1[[#This Row],[CTR]]-Table1[[#This Row],[PEER_CTR]])/Table1[[#This Row],[PEER_CTR]]

Friendly Formula: (kw_ctr – peer_ctr)/ peer_ctr = CTR_DIFF
 

CTR Difference Formula

Step #4: Use this Knowledge to Create More Comparison Metrics

Hopefully you can see the benefit and power of creating these types of comparison metrics.  Take what you have learned and add: PEER_CVR and PEER_CPA and see how much more powerful your analysis becomes with this added context. Using these techniques will allow you to create actionable lists of keywords with above or below average CTR, CVR, or CPA’s quickly and easily.