Last week on PPC Tuesday, my colleague Kevin dove into Google’s Ad Display Builder, an important tool to generate image ads for AdWords. If you missed it, be sure to go check it out.

By the way, did you know that we deliver? We actually deliver Your Daily Concept, our daily video series, by e-mail to people who sign up – and it’s all free. This way, you’re guaranteed to get your dose of smart each day.

Today, you are in for a treat, as we are going to peel back the curtain to show some of the formulas we use in our day-to-day work.
Pay per click produces a lot of data, which is imperative to manage a successful campaign. What begins with research and hypothesis can only be vetted by examining the appropriate data. The best place to do this is within a spreadsheet, and as everyone knows the most powerful spreadsheet software out there is Microsoft Excel.
I am going to review 3 formulas that help give you the proper insight into your campaigns, including one very quick formula that makes writing ad copy easier, another formula we use when crunching the numbers, as well as the single-most important formula to measure the profitability of your efforts.
So, lets jump right into it.

---- Help us help you! Please take 3 minutes to complete this confidential survey. ---

How many times have you tried to manually count the letters & spaces in your ads to see if they fit? I bet most of you, like me, have lost count and had to start over. Well the first formula we are going to cover will do the counting for you!
It’s best to use when setting up campaigns and is called “len”.
As you probably know, Google has strict guidelines for their ads, including 25 characters for the title, and 35 characters for each description line and the display url. To measure how many characters are going into the cell, enter
=len(CELL)
This, coupled with a quick conditional format can let you know when you have gone over the limit. (Conditional Format Rule: highlight cell when greater than 35 (or 25).)

You can also use the result of the len formula as a jumping off point for other analysis. For example, if you wanted to serve a different string of text, or dynamic keywords, if the character count was below (or above) a certain threshold, you would reference the results of the len formula.

The next set of formulas are a little more complicated, but very handy when trying to crunch a lot of data, and you want it to be statistically viable.
They are called the SumIF and AverageIF campaigns. To apply this formula, you enter
=sumif open parenthesis, the range of data you want to qualify –comma– the qualifications or criteria –comma– and the range of values to add up, then close parenthesis.
Whether you are using averages or sums depends on the data you are crunching, if its rates (including Cost per click, click through rate, or average position) use AverageIF. If you are looking at clicks, cost, conversions, then you will want to use SumIF to get the total.
These allow you to see how a group of keywords or campaigns are doing, with qualifications.
By eliminating low performing metrics (keywords with click through rates better than 2%, or campaigns with more than 1 conversion) you can see how relevant data is stacking up.

So, for example if we want to add up the costs of all keywords that have at least a conversion, then you will enter the following formula:

=sumif(CONVERSION CELLS,>1,COST CELLS)

This will only add up the cost of the first and last row, since the 2 middle rows do not have enough conversions.
This gives you a total of 83.26 for 17 conversions, as opposed to $133.73 for 18 conversions.

The last formula I want to share with you is the most important, it’s how we calculate return on ad spend, affectionately known as ROAS. While some organizations just look at the revenue that came in against what was spent, we feel that it is not taking enough into account, so we back out the spend first.
Since you have already spent some of that revenue on advertising, we compute: Revenue minus spend, over spend and get a percentage. If you have other costs that drive the advertising (including management fees or even overhead) you can also back those out to see how much profit your advertising is driving.
For example if you spent $125 on advertising, and brought in $1,000, you would be able to compute a 700% Return on Ad Spend.
So there are three basic formulas that can help you Set Up, Manage, & Qualify your PPC Campaigns.
There are many other formulas out there that are beneficial to managing pay per click accounts, and even more possibilities when you include macros and pivot tables to make the data crunching more efficient.

I hope this video has been helpful!
To Learn More about Exclusive Concepts Profitable PPC product, or any of our excellent services in our suite of online marketing offerings including our Conversion Booster, SEO Foundation or Precision Email Marketing, please don’t hesitate to contact us. We have also launched our new Software As A Service destination, Conversions On Demand.com. Visit it today and see what we have to help your business.
Also, if you have specific questions about how your PPC Account is structured – and whether you are collecting the appropriate data, sign up for one of our Profitable PPC Audits, they are quick, free, and offer some great insight in how to optimize your campaign.

Thank you, this has been Chris for Exclusive Concepts Daily Concept: PPC Tuesday. I look forward to talking to you next time.