Re-Creating AdWords Campaign Performance Charts in Excel, Part 1: The Data

Interesting things you will learn in this how-to:

• How to prepare data for year-over-year analysis in Excel
• How to use the Excel INDEX-MATCH formula
• How to use the Excel ISNA formula

A few weeks ago I showed you how to compare date ranges in AdWords, and then a few days later I came across an article on Search Engine Journal about using the new AdWords dashboard located on the "Home" tab. 

In the article the author mentioned how, in the new version of the Home tab, you couldn’t do date range comparison like in the previous version. See the image below:

So, I thought it might be interesting to try and recreate this chart and summary in Excel. The finished dashboard will look something like this:

For this exercise I will be using a variation of the Juice Analytics DTP Framework.

Data (the “D” in “DTP”)

Pull a campaign level-of-detail report from the "Dimensions" tab. There is no need to pull all of the calculated metrics like CTR, Avg. CPC, etc., because they will all have to be re-created in our Excel pivot tables. See the image below for the columns you will need to pull in your ad group report:

Because we will need to be able to do date range comparisons, you will need to pull two reports: a current and a previous performance report. In my example I pulled YTD data (1/1/2011 – 3/28/2011). 

I offset my previous data range to match the same day of the week, not just the same date, (1/2/2010 – 3/29/2010) to get a more accurate comparison. Eventually I plan on updating my current report data regularly, so I decided to pull all of last year’s data (1/2/2010 – 12/31/2010). This way I will only have to update one report.

Steps for getting the right data:

1. Pull a Year-To-Date report from the Dimensions tab (might make sense to schedule this report) using the columns shown above and put the report in a worksheet named "Data."

2. Pull the previous date report as described above and put the report in a worksheet named "Previous."

The next thing we need to do is tie the previous data back to our current data. To do this we will need to create a unique identifier or primary key. In the right-most column in your "Data" worksheet, add the following formula (this assumes you have formatted your data as a table in Excel):

= CONCATENATE(current[[#This Row],[Day]],current[[#This Row],[Campaign]])

This should result in something like: 40544Campaign or (date)(campaign name)

Use the following formula for your "Previous" data set:

=CONCATENATE((previous[[#This Row],[Day]]+364),previous[[#This Row],[Campaign]])

Next we will use the INDEX-MATCH formula (lookup) in combination with the IF(ISNA formula to pull over our previous performance data to our "Data" worksheet. Using the IF(ISNA formula gets rid of “N/A” errors when there is no match. We need “0’s” not “N/A’s” for our pivot table to work properly.

=IF(ISNA(INDEX(previous!C:C,MATCH(current[[#This Row],[Lookup_ID]],previous!$H:$H,0))),0,INDEX(previous!C:C,MATCH(current[[#This Row],[Lookup_ID]],previous!$H:$H,0)))

Modify the formula above for each comparison metric (Prv_Impr, Prv_Clicks, Prv_cost, Prv_AvgPos, & Prv_Conv).

The INDEX-MATCH formula is a superior way to perform LOOKUPS in Excel.

At this point you should have all of your comparison metrics pulled into your "current" worksheet. We will be using the "current" worksheet as our main data source for our pivot table and charts. If done correctly, all we will have to do to update our dashboard is append new data to our "current" worksheet.

Because we are going to be aggregating one calculated metric (AvgPos) we need to create a weighted average position. We do this by adding the following formulas in new columns to the right of our data set:

=current[[#This Row],[Avg. position]]*current[[#This Row],[Impressions]]

=current[[#This Row],[Prv_AvgPos]]*current[[#This Row],[Prv_Impr]]

or AvgPos * Impressions

We will use these new columns in our pivot table to create our weighted average position; otherwise we would be reporting averages of averages.

In the next post, we will focus on the "transformation" piece of DTP, which will involve pivot tables, named ranges, offset formulas, and other Excel goodness.

This is a guest post by Chad Summerhill, author of the blog PPC Prospector, provider of free PPC tools and PPC tutorials, and in-house AdWords Specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).

This post originated on the WordStream Blog. WordStream provides keyword tools for pay-per click (PPC) and search engine optimization (SEO) aiding in everything from keyword discovery to keyword grouping and organization.

Source: http://www.wordstream.com/blog/ws/2011/04/18/adwords-dashboard-excel

link exchange pay per click ppc experts social media social networking