AdWords Tips

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

By Chad Summerhill April 18, 2011 Posted In: AdWords Tips Comments: 4

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:

AdWords Campaign Performance

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:

AdWords Dashboard in Excel

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:

AdWords Dashboard

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.

AdWords Dashboard

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

AdWords Performance Grader




If you enjoyed this post, please consider leaving a comment.

Comments

Friday April 22, 2011

PPC Weekly Roundup – April Showers Edition (not verified) Said:

[...] got you covered this week. WordStream offers the first in a series of posts to help PPC managers re-create AdWords campaign performance in Excel, and Jason Leister shared his PPC tips and PPC forecast with us just the other [...]

Wednesday July 13, 2011

seo company (not verified) Said:

Awesome article, we are going to use this for one of our clients. Cheers..

Wednesday August 10, 2011

Anonymous (not verified) Said:

what does the +364 stand for? =CONCATENATE((previous[[#This Row],[Day]]+364),previous[[#This Row],[Campaign]])

Tuesday July 23, 2013

Bernhard (not verified) Said:

Hey Chad,

this is really an amazing Dashboard which you habe created here! Unfortunately I am totally lost, starting from the INDEX-MATCH formular, combined with the IF/ISNA formular.

I am trying to solve this problem since two days now, but I cant find any solution. Do I have to use this formular in the "current" worksheet?

Any chance to contact me?

 

Best regards

Bernhard

Leave a Comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.