How to Make A High-Quality AdWords Report in 15 Minutes [Free Template]

6

For those of us who spend all day in the AdWords UI, there’s almost no report out there that can include every bit of information we use to optimize our accounts. But even for the most hardened veteran of that grey and white landscape, trends can be hard to see without quick visualization.

At WordStream we love creating custom dashboards for our clients so they can easily communicate goals within their company and staff. We automate it whenever possible so we can spend more time optimizing your account!

Below is an introduction to getting your way on making aesthetic and functional dashboards that you can white-label yourself. I highly recommend going through the steps yourself; however we have included a template at the bottom if you wish to go straight to the final product.

Downloading Your AdWords Data AdWords report

Select your date range – if creating a brand new report go back as far as you want! We’re doing 2014-Today. 

Make sure you have only the following columns: Impressions, clicks, cost, average position, converted clicks, total conversion value (you can substitute conversions for converted clicks if necessary).

Click on the Download button and segment by “Day”. Add another and segment by week, then add another and segment by month. Then download and open it!

AdWords report date range

Your report should look like the one above. 

We want to delete a few columns now, so for campaign state click on the letter above it, in this example you will click on the “D” which selects the whole column. Rather than hitting “delete” and clearing the column we want to hit “Ctrl & -“ to remove the row altogether. Do the same for the other highlighted columns.

The remaining columns should be: Day, Week, Month, Campaign, Impressions, Clicks, Cost, Avg Position, Converted Clicks, Total Conv. Value.

AdWords report formula
Now we will add a simple formula so that we can get an accurate read on average position. (Note, we will be using a Weighted Average Position).

Title the column WAP and in the first cell use the formula =Impressions * Avg Pos (Displayed here as =E3*H3).

Drag this formula down your data (or double click in the small black box of the selected cell for instant drop down!)

AdWords report menu
Delete the first row of the sheet that contains the date range using “Ctrl –“ and go to the end and delete the 4 total rows as well. (Click on the number to the left of the row to select the entire row). Select cell A1, then in the toolbar go to “Insert”.

Now for the Pivot Table!

AdWords report pivot table 

The default settings for the pivot table are what we want to be using so just hit “OK”.

We now have an empty pivot table, so grab your stress ball and squeeze it a few times, and get ready to wonder why you ever afraid of them to begin with!

AdWords report pivot table fields

AdWords report pivot table values

Here we have the initial metrics dragged in. These are the metrics that just get added together!

For the metrics that require multiplication or division (CTR = Clicks / Impressions) we have to do one quick special step.

AdWords report calculated fields

Go To “Formulas” and then “Calculated Field”:

AdWords report insert calculated field

Name and create the following metrics:

  • CTR = Clicks / Impressions
  • CPC = Cost / Clicks
  • CPA = Cost / Converted Clicks
  • ROI = Total Conversion Value / Cost
  • CVR = Converted Clicks / Clicks
  • Weighted Avg Pos = WAP / Impressions

 AdWords report data

All right, things are coming together! But it’s still pretty ugly. If you present this to your board or client you’ll get a face full of hot coffee or a pink slip so let’s put lipstick on this pig.

AdWords report formatting 

Currently the columns are named “Sum Of XXXX” so let’s rename them and give them the proper units. In the values area click on the drop down for the first option, impressions, and go to value field settings:

AdWords report impressions 

Rename it to just “Impressions “ (make sure to put a space at the end of the word{excel intricacies}).

Then go to Number Format:

AdWords report numbers 

For Impressions we want “Number” with 0 decimal places. Details for the rest are below:

  • Clicks: Number, 0
  • Cost: Currency, 2
  • Converted Clicks: Number, 0
  • CTR: Percentage, 1
  • CPC: Currency, 2
  • CPA: Currency, 2
  • ROI: Currency, 2
  • CVR: Percentage, 2
  • Weighted Avg Pos: Number, 1

AdWords report data

Much better! Now let’s sort the data so the new data is on top!

AdWords report pivot table field list

On the right panel click on Month and sort “Newest to Oldest” and repeat for week and then day.

AdWords report trend data

Now we’re at a point where we can start to see some actual trends in the data.

AdWords report Excel colors

Add some color by going to the design tab and picking whatever is aesthetically pleasing to you.

Add a few columns to the left of the pivot, and a few to the top. Then highlight a column at the top, click “Ctrl Shift Right arrow” and right click the header letters and click “Hide.”

AdWords report Excel merge

Highlight the top few rows above the pivot and under “Home” click on “Merge & Center.”

I like to match this header to the color scheme of the pivot and then type in your client’s name.

AdWords report presentation

And now in a mere 15 minutes we have a great-looking AdWords report that can be sent to any client! Click here to download a free template of this report.

About the Author

Michael Griffith is a Senior Paid Search Strategist at WordStream. In addition to managing client accounts he provides technical assistance and conversion tracking solutions for those with advanced needs. Thankfully he is more adept at helping his clients succeed than he is at fishing. You can follow Michael on Twitter.

Find out how you're REALLY doing in AdWords!

Watch the video below on our Free AdWords Grader:

Visit the AdWords Grader.

Comments

hafiz
Aug 19, 2015

thank you so much! this really helps me a lot.

Helen
Apr 22, 2016

Wow. Awesome.

Site
May 30, 2016

Awesome! Thanks a lot!

Kate
Aug 15, 2016

This is such a great report! I used it to add in a dashboard to my weekly tracking document with one of my clients. This was by far the simplest method for consolidating the most important trends for him, and it really took barely any time to get set up. I already loved Pivot Tables before I used this guide, but this taught me some extra tricks. Thanks!

Raju
Jan 19, 2017

Hi Team,

You people done a such job for ppc users/managers.Just now created ppc report from this article.
I would say one thing "Adwords = Word Stream"
Keep it up.

Best Regards,
Razzu

PHILIP POLASKI
Mar 07, 2017

Great stuff! Still relevant in 2017. Tq

Leave a comment