Analyze Your Account in Minutes With Excel
September 14, 2012
This week, we’ve been focused on giving you all kinds of tips and tricks with Excel to help you build out your accounts.
- Kayla showed you how to use Excel to estimate new account performance.
- Sean showed you how you can use Excel to find the best budgets for your campaigns.
- Dave showed you some quick tips with Excel filters to quickly structure your keyword lists.
- Amanda showed you how to write ad copy in a flash with various Excel functions.
So now that you’ve projected, budgeted, built out, and written copy for your account using Excel, what’s next?
The long answer? Throwing all of that performance data together to see what you’ve got.
The short answer? Pivot Tables and Formulas.
Use Pivot Tables to Quickly Examine Account Structure
First, you’re going to want to download a Keyword Report using the last 30, 60, or 90 days as your time frame. Make sure it has Quality Score and Match Type information. With this one report, you can generate multiple tables to help analyze your Quality Score and Account Structure.
For Quality Score, I’m going to defer entirely to the great Brad Geddes strategy on Normalized Quality Score as detailed here on PPC Hero. Following his guide should leave you with an actionable list of Ad Groups on which you can focus your Quality Score efforts.
For overall Account Structure, we can use a few other tactics. The first is a simple analysis of Keywords per Ad Group:
- Create a Pivot Table using your Keyword Report.
- In your Row fields, place Campaign and Ad Group (Campaign is optional, but if you have duplicate ad groups across multiple campaigns, it will cloud your data).
- In your Data field, select a Count of Keywords.
This provides you with a quick count of keywords per Ad Group. You’ll be able to diagnose any Ad Groups in need of a breakout in short order. A helpful tip as we go forward, if your version of Excel is anything like mine: Hide Campaign and Ad Group Total rows, they tend to get in the way.
This time, we’re going to take a look at Match Type performance across the account (Or, if you prefer, across campaigns and even ad groups).
- Create another Pivot Table.
- Use Match Type as your primary statistic in the Row field (preceded by Campaign and Ad Group if you’re looking to get granular).
- In your Data field, select Count of Keyword, Sum of Impressions, Sum of Conversions, and Sum of Cost.
- Now, to get an idea of the percentage breakdown, add in another four rows dividing each statistic detailed in the last step by the grand total at the bottom. And remember, placing a $ in front of the cell number makes it a constant, so the formula is a breeze (so the formula would be something like =B5/B$10, as in the example below.)
This gives you an idea of how each Match Type is performing in this account, and an idea of where to focus your efforts. In this particular account, Broad match terms were mainly comprised of branded terms, hence the great CPA. But this is another quick tip to see which Match Types are carrying the load in your account.
You can do a similar breakdown regarding the percent of Low Search Volume keywords in your account, Count of Ad Groups, or any other statistic you can think of all through the magic of Pivot Tables.
Use Pivot Tables to Quickly Examine Your Ad Copy
You thought we were finished with Pivot Tables, didn’t you? No such luck – it’s the utility belt of PPC Heroes everywhere. Next, download an Ad Report over the same time frame.
To start, you can perform another Count of Ads per Ad Group.
- Generate yet ANOTHER Pivot Table.
- Put “Ad Group” in to the Row field (And Campaigns, too, depending on the naming structure you’ve chosen for your Ad Group).
- Then, select “Count” of any of your ad lines for the Data field – it doesn’t matter which, we’re only concerned with how many there are, not their content.
Again, another quick tip to provide you with a breakdown of ads per Ad Group. If any of the numbers are off, you might have some ad tests you need to conclude.
Depending on how you’ve structured your latest round of Ad Testing (See: Amanda’s post yesterday), you have a few options available to you to sort through the data.
First, insert an extra row beside your Description Line 1 and Description Line 2 columns and concatenate the two rows together like so:
This will provide a handy one-stop shop for you to review your data later on.
Now, you could go through the ad report, line by line, examining each individual ad in painstaking fashion, or you could use some more Pivot Tables.
- Create another Pivot Table out of the Ad Report.
- In the Row Field, place your Headline, Description Line 1, Description Line 2, the combined Ad Copy we just made above, or Display URL. Or, heck, use all of them. Throw in Campaigns and Ad Groups as well if you’re feeling bold – this is more about the overall copy performance, but you can get as specific as you’d like.
- Throw in your performance metrics, specifically Sum of Impressions, Sum of Clicks, Sum of Cost, and Sum of Conversions.
- Then, calculate the CTR and Conversion rate of each ad/ad element next to your Pivot Table.
- Finally, add in Evil Genius Jeff Allen’s Conversions Per 100k Impressions Formula in the last column.
This gives you a quick breakdown of each ad element, either individually or together, and how they perform on an account-wide basis. But, you’re still just looking through copy on a line-by-line basis. There are fewer lines, but we can do better.
Remember that aggregate line of copy we made using the concatenate function? It’s time to put it to use. Examine your copy looking for similar themes and make a list – they could be terms you’ve used, products, calls to action, anything. We’re going to make use of the SUMIF function.
Taking the list you’ve compiled above, create a new tab in your spreadsheet. Enter in the items you’re looking to test in one line, and prepare some columns for your other performance metrics – Conversions, Cost, Impressions, Clicks, etc. Now, use the following formula:
=SUMIF(‘Ad Report Tab’!Aggregate Copy Column:Aggregate Copy Column,”*test copy*”,’Ad Report Tab’!Metric Column:Metric Column)
So… that’s fairly indecipherable, I know. In practice, it will look like this (depending on your column layout):
=SUMIF(‘Ad report.csv’!E:E,”*sign up now*”,’Ad report.csv’!S:S)
Where column ‘E’ is the aggregate copy column, and ‘S’ measures conversions. This will sum up the conversion data for all instances of copy that contain “sign up now”, a call to action in use for this client. You can then change up the text in between the asterisks to test whichever elements of your copy that you’d like in a Darwinian Thunderdome of data analysis, where only the strongest copy will survive.
So there you have it. Excel is easily one of the most helpful tools at our disposal in PPC account management. There are many more complex formulas and tools you can use to help analyze your account, such as VLOOKUP and beyond, but they’re usually in the territory of the North American Bearded Quadlin. As always, if you have any PPC Excel advice, tips, and tricks – leave a comment below. Thanks for reading!
Expanded Text Ads: 7 Million Clicks Say They’re Underperforming
Over the past 6 weeks, we have been lucky enough to experiment with expanded text ads, and frankly, I’m not impressed with their performance.
£300 Off Hero Conf London Ends Tomorrow!
Time is nearly gone to reserve your seat for Hero Conf London, 24-26 October at etc.venues St Paul’s, at a huge discount off the regular conference rate!
Expanded Text Ads Come To Google AdWords Editor
Advertisers have been able to create expanded text ads, but couldn't utilize AdWords Editor. That has now changed with the release of the latest version of AdWords Editor.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Bidding Farewell to Adwords Converted Clicks: Don't Panic
AdWords will soon be saying goodbye to the "Converted Clicks" metric. Learn more about what this means for your account and how to prepare for the transition.
Olympic PPC Ad Strategies
How spectators consume sporting events and a massive expansion in the digital space will prove to shake up who and how advertisers reach their audiences.
Hanapin's PPC Resources Are Now Ungated!
You can now access Hanapin's whitepapers and toolkits without having to fill out a form!
View Ad Creative Through The Eyes Of Your Searchers
Learn to understand that ads do not live in the vacuum of an excel sheet and common practices should be tested.
[New Webinar!] 11 Red Flags To Watch Out For When Working With An Agency
Leveraging Analytics For Remarketing: Picking The Ripest Fruit
Remarketing prevalence is well founded based on competitive returns in both volume and efficiency. With that said, this "low hanging fruit" world isn't always easy to pick.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.
Mobile Price Extensions Rolling Out In AdWords
It looks like "Christmas in July" has come to Google. Recently announced price extensions are designed to help mobile marketers qualify clicks and inform prospective consumers.