Today we have more handy Excel tricks from Bing Ads Evangelist John Gagnon. This second installment of PPC Excel tips focuses on intermediate level techniques for speeding up campaign analysis. Paid search managers will benefit, but really anyone using Excel for data analysis will find good information or a helpful refresher here.
Intermediate Tip: Get more out of Pivot Tables with calculated fields and by fixing #DIV/0 errors.
Pivot tables are truly a must-have Excel skill for marketers. If you haven’t used pivot tables yet, don’t be timid. You can’t damage your data set when working with pivot tables because they simply aggregate the data you are working with in a separate area, typically in a separate sheet. For more on getting into pivot tables read this earlier post from Annie Cushing.
Calculated Fields:
For those with pivot table experience, you know that once data such as impressions, clicks, spend are aggregated, you still need to calculate metrics like CPA and CTR as you do with regular campaign exports. In other words, you can’t Sum cost/conversion or click-through data, you have to calculate them from the aggregated cost, click and impression data. This is what Calculated Fields do for you with just a little set up work. I have to admit, for years I calculated these metrics in cells outside of pivot tables not knowing about the magic of calculated fields.
A great thing about calculated fields is the data columns you want to use in your formula do not have to be included in your current pivot table. For example, in the demo below, John shows how to calculate CPA to find the total cost per conversion, but only spend is included in his pivot table, not cost/conversion.
One note, your calculated fields can’t use duplicate names from fields already in your data set. Thus the “CPA” field name in this example.
The calculated field will append a new column to the right in your pivot table. You can then sort and filter based on this new column.
Fixing #DIV/0 Errors:
Whether you’re using calculated fields or not, if you divide a number by zero in Excel it returns the dreaded #DIV/0! error. This can obviously skew your analysis if you’re looking at CPA data.
“If you ignore the error, you’re missing out on valuable information — a keyword can spend thousands and still not convert a single time,” says John.
The solution: Use the IFERROR() formula in the calculated field to return “spend” or “cost” when there are 0 conversions. Open the Calculated Fields dialog again, go to the CPA field in the Name dropdown, and change the formula to: =IFERROR(Spend/Conversions,Spend)
John is using the 2013 version of Excel. If you’re using Excel 2010, you’ll find calculated fields under the Options tab in PivotTable Tools.
IFERROR can be used outside of calculated fields as well. It’s a good function for marketers to know, particularly for cost analysis. The logic of the formula basically says, if the calculation returns an error (#DIV/0! in this case) then return X, and X can be your total spend, or it can be a specific number or even text. Essentially, you can customize what the formula returns by putting what you want after that comma in the formula.
If you have any pivot table questions or tips you’d like to share, please do so in the comments below. Stay tuned tomorrow for the last segment in this series when John shares some advanced level Excel tips for PPC marketers. And be sure to check out PPC Excel Tips For Every Level: Part 1, Huge Time Savers For Beginners (And Beyond) if you missed it last week.
Contributing authors are invited to create content for Search Engine Land and are chosen for their expertise and contribution to the search community. Our contributors work under the oversight of the editorial staff and contributions are checked for quality and relevance to our readers. The opinions they express are their own.
