Measure Marketing Return on Investment (ROI) in 6 Easy Steps

I know, I know, what the heck does an accountant know about marketing? You are right, not much. But I do know how to measure. And if you don't measure, you can't improve. If you ask yourself what you would like to improve the most in your business most folks would agree its sales. If you asked most sales people their biggest impediment to closing sales they will say 'marketing and branding, as no one knows us or our value.' There may be other reasons but generally I would agree with the premise that communicating your brand to your prospective client or customer is the easiest way to 'grease the skids' for the sales people.

Ask the same business owner their feelings on increasing the level of marketing spend in order to boost sales and you can guess the reaction. One of several responses will occur:

1. "No"
2. "We already tried that"
3. "I would if we could see the Return on Investment (ROI)"

I can't help the first two responses but some simple statistical analysis can help with the third response. I am a firm believer that if you can measure some type of return on investment for marketing (the Holy Grail) it makes decisions as a business owner much, much easier and less risky.

So how do you measure ROI for marketing spend? It may not be perfect but here's an approach that we've seen work at several of our firm's clients. The purpose of this measure is not to measure the effectiveness of an individual program (e.g. click-through with Google AdWords) however it can be modified for that purpose. The intent here is to measure overall marketing spend, either as time or resources, relative to sales in order to establish three things:

• Strength of the statistical correlation
• The actual financial correlation
• 'When' you should see the return to know if you're successful

Here's a real-world example:

Lisa is the owner of a custom retail products company that has end uses also in commercial hotels and apartment complexes. She knows that she needs to spend money on marketing and she's established some benchmarks that she should budget relative to her sales. What she does not know though is whether or not a specific program would be successful, the actual return on marketing spend to each one of her audiences (e.g. end retail consumer, business), or the average rate of return on marketing spend. Nor does she really feel absolutely confident that if she cut marketing spend in half would she see a resulting 50% decline in sales (or contrarily – if she boosted marketing spend by 50% would there be a 50% increase in sales?) So, how did Lisa solve this dilemma? Statistics – and more specifically, the use of regression analysis.

Step One – Determine Your Theory

In Lisa's case, the theory is that if you spend more on marketing there is likely a corresponding increase in sales. Statistics is not very powerful at proving correlations that you didn't have a hunch about – however it is powerful at either disproving correlations (e.g. wow, there actually is no correlation – A does not cause B) and proving correlations (e.g. I thought A caused B, now I know statistically that this is true). In my business, a professional services firm, my theory is that marketing TIME and MEETINGS correlates to sales – and unfortunately we don't track that, yet. D'oh!

Step Two – Get Your Data

For statistics to be meaningful you really need a good sized data set. I'd suggest at a minimum three years (or 36 months) of data. In Lisa's case, she gathered sales by customer segment and associated marketing spend by customer segment.

Step Three – Build Your Model

It is helpful to have some medium skills in MS Excel for this one. Regression analysis requires installation of the Analysis Toolkit in MS Excel (comes with MS Office) – http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx. Once you've loaded the toolkit the model building is pretty straightforward:

1. Create column X – Sales. Going down each row enter in 36 months of sales (for an individual customer segment).
2. Create column Y – Marketing Spend. Going down each row, enter in the amount of marketing dollars spent in the same month.
3. This one's up to you – in marketing it's generally accepted that when you spend $1 it might take a month to a year to actually see the corresponding sales. I'd advocate looking at a three month minimum correlation – a lot of this will depend on your own hunch on marketing. Start three more columns called 'month 1, month 2, and month 3'. These represents months of marketing spend. So – in 'Month 1' enter in the amount you spent on marketing that month. In month two enter the amount of marketing spend the prior month. In month three, enter the amount of marketing spend three months ago.

Your finished product should look something like this:

Step Four – Perform Regression Analysis

Using MS Excel Regression Analysis, go to work. The all data should start at the third month / row (in this case, March) assuming you are using three months (or five months, start at the fifth month/row). The 'X' is the sales column. The 'Y' is the array that starts at the third month 1 column (in this case, '1 - $9,000). Set the 'Constant is Zero' checkbox and also click the 'Residual' checkbox. The output area can really be anywhere in MS Excel (I like to use a new worksheet).

Step Five – Analyze Results

What does it all mean? R Square means the 'strength of the correlation.' 1 is the best. Anything above 75% or so for this purpose is probably enough to make a bet on in my opinion. In Lisa's case, her R Square was .86, meaning that there was an 86% (in layman's terms) likelihood that if she spent $1 in marketing it would generate SOME type of sales return. Pretty good odds.

What type of return? This is where the coefficients come into play. If you sum these you get a pretty good (albeit on the low side) estimate of the actual return. In Lisa's case, the coefficients sum to $5.50 – so rounding up she gets a $6 return for every $1 spent, with an 86% likelihood that this will occur. In Lisa's case, this was confirmed by simply dividing sales by marketing spend for the year for each year and looking at the average.

When do you see the results? Divide an individual coefficient by the sum of the coefficients (do this for each of your three – or however many months you used) and you get a rough breakdown of the 'marketing yield' in each month for a $1 spent in each month. In Lisa's case, she found that when she spent a $1, 37% of the return was in the first month, 34% of the return was in the second month, and 29% of the return was in the third month.

Step Six – Use the Data

Now what do you do?

1. Use the data to measure the effectiveness of your marketing. If, on a rolling twelve months, you find that you are BEATING your historic returns (in other words, if Lisa is beating 6:1 on a rolling basis) – you have a good marketing campaign. If you are lower than your historic return you have a bad marketing campaign.
2. Use the data to predict sales. If you know that there's a good correlation between marketing and sales, you can peg your sales to marketing spend (so, using Lisa's percentages, Sales in January would be (6 X November Marketing Spend X 29%) + (6 X December Spend X 34%) and (6 X January Spend X 37%). A note of caution – in statistics there is always a deviation – so as a whole your predictive model will be good – however, in a given month it will vary up or down (see the residuals in your analysis) – be prepared to operate w/in some boundaries here and see point 1.

In closing – this is a powerful tool that takes the guesswork out of marketing gambles. You still have to decide whether or not a campaign will be effective, but at least now you have the tools to measure your marketing ROI.