AdWords Tips

Re-Creating AdWords Campaign Performance Charts in Excel, Part 4: Making the Charts

By Chad Summerhill May 11, 2011 Posted In: AdWords Tips Comments: 1

In part three of this series, you learned how to build our dashboard’s scorecard. In this article, I will show you:

  • How to build the charts.
  • How to copy and reuse your charts to save time when building multiple charts.

Building the Charts 

The AdWords Campaign Performance report only has two charts and they allow you to choose from many different available metrics for comparison. Our dashboard isn’t really limited by space, so we will be creating a chart for each of our KPI’s.

  • Impressions
  • Clicks
  • Cost
  • Conversions
  • CTR
  • CR
  • CPA
  • CPC

The best way to do this is to create and format one chart for one KPI, and then we will copy the chart several times for each of the other KPI’s. This technique will save you lots of time, because you will only have to format your chart once.

Follow these steps:

1. Insert a blank Line chart into your dashboard worksheet.

Insert Line Graph

2. Add “impressions” as a series by right-clicking on the chart and clicking “select data.”

3. Add “prv_impressions” as a series using the same method.

4. Add “date” for your X axis using the same method mentioned above.

We will be replacing these series later with named ranges.

You should have a chart that looks something like this:

Impressions

What we need to do next is format our chart for our dashboard:

1. Move the legend to the bottom and rename them “Prev” and “Current.”

2. Change all font sizes to 8pt (we need all the space we can get).

3. Remove horizontal gridlines from the “Layout” menu > Gridlines > None.

Remove Horizontal Gridlines

4. Add Major Vertical Gridlines from the same menu as mentioned previously.

5. Format the gridlines by right-clicking and clicking Format gridlines…Change the line type to a dash and the line color to a gray.

6. Format the X axis dates by right-clicking the X axis and clicking Format axis…Change the alignment to vertical and the format to dd/mm.

7. Remove the border from the chart area by right-clicking on the chart and clicking on Format Chart Area…

8. Change the chart type of the prv_impressions series by right-clicking on the series and clicking on Change Series Chart Type…Change it to an area chart.

After all of your formatting, you should have a chart that looks something like this:

The next steps involve replacing our current and previous chart series and X axis with our named ranges located on the transform worksheet.

  1. Right-click on the X axis and click “Select Data…” Then click the “Edit” button on the Horizontal Axis Labels.

Axis Labels

  1. Replace the contents with: ='GoogleDashboard_042611.xlsb'!date (GoogleDashboard_042611.xlsb is the name of my workbook. Use the name of your workbook here.)

  2. Right-click and click “Select Data…” again, but this time “Edit” the “Current” series.

    Select Data Source
  1. Change the contents to: =GoogleDashboard_042611.xlsm!curr_impr

  2. Repeat step 4, but this time “Edit” the “Previous” series and use this formula: =GoogleDashboard_042611.xlsm!prv_impr

Now our chart series and X axis will reference our dynamic named ranges instead of the static columns. In the next steps, we will copy our original formatted chart and create our multiple charts for each or our KPI’s.

Make sure you are through making formatting changes to your chart before your proceed. You won’t enjoy making the same formatting change to eight different charts.

1. Select your chart and Copy it.

2. Then select a different cell and Paste it back to your dashboard worksheet.

3. Go ahead and delete the legend from your new chart. We will be stacking the charts and you will only need the legend to be present on the bottom chart.

4. Change the X axis font color to white, so that it will be invisible.

5. Copy & Paste this new chart six more times until you have eight total charts on your dashboard worksheet.

6. Position your charts as seen below (you will have to use the “Bring to Front” tool when creating your overlaps):

7. Now follow the steps described previously for changing your series named ranges. For example you can change the bottom right chart to show conversions by changing “curr_impr” to “curr_conv” and “prv_impr” to “prv_conv”.

8. You can also add chart titles for each KPI chart.

After you’re though changing your charts to reflect all of our different KPI’s you are ready for the next and final step in creating our AdWords dashboard. In the next post I will show you how to pull everything together and make your charts and scorecard dynamically change when you adjust date ranges and campaign filters using Excel controls.

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

Saturday September 24, 2011

Keyword Research (not verified) Said:

Huh? I didn't know that Excel was capable of doing that! I usually resort to other tools when drawing charts and stuff like that.

Thanks a lot for this information! Now I don't need to spend more dollars for other applications, when I already have one installed at my computer!

Dee

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.