How to Build a PPC Command Center in Google Sheets

Aggregation and visualization of performance data is a must in AdWords. Without a dashboard it's too hard to understand what's going on. Especially if you're making changes and want to understand their impact on the account.

This is the only visualization of data you get inside AdWords:


What is this? A graph for ants? Google Analytics isn't much better either.

So here's what we're going to create today:


I highly recommend you watch the video tutorial (9mins) and get access to the Google Spreadsheet, it's much easier than this blog post. You can access this here: AdWords Dashboard in Google Sheets Tutorial + Template

You can just make a copy of the template and plug in your stats from AdWords.

But some people prefer text and screenshots, so here goes - I'll guide you step by step on how to build this from scratch.

Master Data

The first sheet is the master data sheet - this is where all the other sheets will pull their data from.

If you're you're not sure which KPIs you should be tracking, here are my recommendations on what you should start with:

Contextual Data

Base Metrics
Converted Clicks

Derived Metrics

CPA Targets



Contextual Data

Gives context to the data. This is the biggest dimension missing from AdWords or GA. Everytime you have AdWords open, you should have your master data spreadsheet open in a tab right next to it, ready to make notes of any significant changes you made or things you've noticed.

Eg. "Added remarketing" or "Increased bids, expect CPC and cost to rise" or "Added new batch of negatives"... This way later, you'll be able to make sense of the graphs and trends.

Data Explanation / Example
*Date* Eg. MM/DD/YY
*Notes* When something signifiant happens or you make a change, make a note.

Base Metrics

Stuff you can only get from AdWords. The easiest way to get this data is to download it from the dimensions tab. Segment per day using the View button. Customize columns using the Columns dropdown. You can "save this set of columns" so you don't have to do this every time.


Data Explanation / Example
*Impressions* How many times your ad has shown
*Clicks* How many clicks you were charged for
*Cost* Cash monies
*Converted Clicks* Unique conversions, used to be called conversions - 1 per click


Ideally, you want to set a CPA Target. If you're not sure what your target cost per conversion is, start with your break even point. A lot of your decision making around bids, keywords, and where you spend your time inside of AdWords will be dictated by cost per conversion (or cost per converted click) and where it stands relative to your CPA targets.

Data Explanation / Example
*CPA Target* Target spend per converted click


Even better than CPAs and targets, is net profit. But at a minimum, it would be great to track the revenue generated per day through AdWords. This may be using e-commerce tracking in GA, or maybe you tag your traffic in your CRM.

Data Explanation / Example
*Revenue* Revenue Generated by AdWords.
*Sales* Number of sales generated by AdWords
*Profit* Revenue * Margin - Cost

A few more advanced metrics I like to track:

Data Explanation / Example
*EPC* Earnings per click: Revenue/Clicks
*Conv. Value / Cost* Similar to ROI.
*Profit* Cash monies

See Moby Memory Case Study for how to use Conv. Value / Cost to make decisions.

Generating graphs in Google Sheets

Once you're data is in there, select the columns (usually date + metrics + notes) by clicking the column letter (this selects the whole column) and holding down control or command to select multiple columns. Then "Insert > Chart..."

It should look like this:


Once you've inserted the graph (and done some edits like change the appearance to get an area chart instead of a line), click the little arrow dropdown on the top right and click "move to own sheet..." Like this:


Repeat with each metric you want to chart. The nice thing is that from now on, anytime you update the master data table the graphs will update automagically! (This is because we selected the entire column and not just the data.

Happy dashboarding!

In case you missed it, if you want to watch the video tutorial (9mins) and get access to the Google Spreadsheet, you can do so here: AdWords Dashboard in Google Sheets Tutorial + Template