AdWords Tips

Re-Creating AdWords Campaign Performance Charts in Excel, Part 5: Dynamic Controls

By Chad Summerhill May 19, 2011 Posted In: AdWords Tips Comments: 3

This is the final post in my series on Re-Creating the AdWords Dashboard in Excel. For the rest of the series, see below:

In part four of this series, you learned how to build the charts for the AdWords dashboard. In this article, I will show you how to make the dashboard dynamic by teaching you how to:

  • Add a dynamic campaign filter.
  • Add a dynamic date-range picker using a Data Validation list.

Building the Campaign Filter

As you may recall we are using the DTP framework as provided by Juice Analytics, and part of that framework is a Visual Basic macro to help make our dashboard dynamic.

Here’s how you add the code to your own workbook:

1. Copy this:

' On PivotTable update in the Display page
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 Dim masterPf As PivotField
 Dim slavePf As PivotField
 Dim pt As PivotTable
 
 ' for each PageField in the master PivotTAble
 For Each masterPf In Target.PageFields
    ' check all the PivotTables in the transform sheet
    For Each pt In Worksheets("transform").PivotTables
      ' if the slave PivotTable contains a PageField
      ' with the same value as the masterPf
      For Each slavePf In pt.PageFields
        If slavePf.Value = masterPf.Value Then
          ' make the slave PageField have the same CurrentPage (selected item)
          ' as the master PageField
          Worksheets("transform").PivotTables(CStr(pt)).PivotFields(CStr(slavePf)).CurrentPage = CStr(masterPf.CurrentPage)
        End If
      Next slavePf
    Next pt
  Next masterPf
End Sub

2. Now right-click your “dashboard” worksheet tab and click View Code.

3. Next, double-click the “dashboard” worksheet seen in the tree menu and paste the code.

Visual Basic

4. Save and close.

It’s important that your transformation worksheet to be named correctly for this code to work properly. In this example you should use the lower-case “transform.”

Now that we have the code in place to drive our dynamic campaign filter, we need to add the control to the “dashboard” worksheet.

1. From the “current” worksheet create another pivot table.

2. Add “campaign” to the “Report Filter.”

3. Cut and paste the pivot table onto your “dashboard” worksheet as seen below:

Pivot Table

That’s all you have to do for the campaign filter. Now when you use the pivot table filter on your “dashboard” worksheet, the pivot table on the “transform” worksheet will be changed automatically. Of course, the data on the “transform” worksheet is the data we use to drive our charts and scorecard.

Building the Date Range Picker

The date range picker is made of two drop-down selectors: one for the start date and one for the end date. In order to create the date range picker, we need to use Excel’s Data Validation feature.

1. From the “dashboard” worksheet, select the cell where you want to place your start date menu.

2. Navigate to Data Validation from the Data menu.

Data Validation

3. Choose “List” from the “Allow” drop-down menu and select the date column from your “transform” worksheet as seen below (make sure you add a couple of zeros to your row number so that you date picker will continue to work as you data gets bigger).

Data Validation

4. Now copy and paste your start date picker to be used as your end date picker.

You may recall that we used this formula to name our date column: =OFFSET(transform!$A$5, transform!$J$1, 0, transform!$K$1+1,1). We need to populate the cells referenced in this formula.

5. On the “transform” worksheet, use the following formula in cell J2: =dashboard!BA3-A6 (selected start date – the first date in the date column).

6. On the “transform” worksheet, use the following formula in cell K2: =dashboard!BB3-dashboard!BA3 (end date – start date).

Now, when you change your start and end dates from the dashboard date range selectors, these values will change automatically and these values drive your charts and scorecard.

All that’s left for you to do is some personalization through formatting. The dashboard is complete and ready to use. When you add new data to the “current” worksheet and refresh your pivot table, your charts and scorecard will be updated automatically.

AdWords Dashboard

For those of you who made it all the way to the end of this blog series, here’s a link to download the Google AdWords PPC Dashboard. All you will have to do is plug in your own data and refresh the pivot table on the “transform” worksheet. Enjoy!

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 May 20, 2011

PPC Weekly Roundup — the Get In Shape Edition (not verified) Said:

[...] is wrapping up their multi-part series on Re-Creating AdWords Campaign Performance Charts in Excel, by PPC Prospector’s Chad Summerhill, with their 5th and final installment going up this [...]

Monday September 19, 2011

Annie Key (not verified) Said:


This new dynamic controls are really helpful in Adwords Campaign. The step by step tutorial on how to do this on Excel is easy to understand. Thus, one will be able to execute it the proper way.

Monday April 29, 2013

David (not verified) Said:

A Really great resource. thanks a lot for sharing Chad!

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.