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

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

• Re-Creating AdWords Campaign Performance Charts in Excel, Part 1: The Data
• Re-Creating AdWords Campaign Performance Charts in Excel, Part 2: The Transformation
• Re-Creating AdWords Campaign Performance Charts in Excel, Part 3: Making the Scorecard
• Re-Creating AdWords Campaign Performance Charts in Excel, Part 4: Making the Charts

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.

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:

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.

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

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.

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

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/05/19/re-creating-adwords-dashboard-5

link popularity link exchange pay per click ppc experts social media