Simple ROI Projecting and Tracking
by Rob Laporte
Visibility Magazine – Spring 2012
(original article reprinted below)
I once heard a joke about an incredibly high-tech world where a scientist won a prestigious award for the invention of paper and pencil.
Web marketers are often hypnotized by fancy analytics packages and reports. Such tools are useful – if actually used to decide on actions – but often executives just want a quick yet comprehensive look at the profit earned and/or expected, so that they can decide what web marketing is worth starting, stopping or changing. After those top-level decisions, then the more detailed analytics inform strategy and tactics.
Data inputs for Excel projecting and tracking vary in accuracy and ease of acquiring, but all can be ascertained sufficiently.
Cost of goods, marketing costs (including in-house employees’ time at hourly rates that factor all compensation) and past conversion rates are among the easiest to determine. More difficult is tracking marketing assists from multi-channel clicks over time, but analytics packages are getting better with this tracking. Among the most time-consuming data to ascertain is the monetary value of leads, like calls, form submissions and emails, but this can be done too. At the very least, you could ask yourself what you would pay for a lead. Of course in-house records of offline conversions make such valuation of leads more accurate.
The tables below cover PPC, SEO & conversion rate optimization in ecommerce and lead generation.
To save space in this article, I eliminated columns of ROI percentage, which can be useful when measuring against upper management’s targets or against other marketing investments. You can see that adding columns for various factors would be simple enough. These kinds of spreadsheets can be used for any web marketing, and each marketing channel’s table can be combined into Excel workbooks with a table that summarizes and compares. There are ways to directly import data from Google Analytics to Google Doc spreadsheets, but the ease and speed of adding data manually suggests that setting up such automation would take a long time to pay for itself.
TWO ROI SPREADSHEETS FOR SEO AND CONVERSION RATE OPTIMIZATION
TWO ROI SPREADSHEETS FOR PPC
I have found that such spreadsheets facilitate the sales process and comprise an excellent first paid job for clients.
Rather than sell standard services prioritized during a relatively brief initial sales consultation, I can allocate a budget and sometimes justify a larger budget by revealing the ROI of each marketing channel. The client spends a little, learns a lot about their needs and my firm’s quality of work, and becomes better prepared to get budget approvals from bosses or colleagues or, in small businesses, from spouses or inner skeptics. For example, in developing the above spreadsheets, I used a small client who has spent a little with us every year since 1998 and has done well. To my surprise (and a little embarrassment) I found that even a pessimistic 10% increase in organic traffic plus a 20% increase in conversion rates would earn 400% ROI within the first year after website rebranding. The client saw the clear numbers in the simple spreadsheet and signed on immediately and for much more than their typical yearly maintenance spend. On the other hand, I’m doing an initial consultation now where I suspect that when I plug in the gross profit figures due to me in a few days, the spreadsheets will show that, sorry, neither SEO nor PPC will be profitable for them, and that therefore the enthusiasm and considerable work so far by this start-up team will prove wasted. Sad though that would be, it’s a lot less sad than their spending tons more and then failing.
Should this ROI projecting be part of a free or a paid initial consultations? Each firm must answer this question for themselves, but for the prospective buyer, I offer some caveats about the freebie:
- The good web marketing firm is likely to enjoy plenty of demand, so unless your business is exceptionally large or otherwise desirable, the firm probably won’t spend much time on a free initial consultation — at least not by the principals of the firm who know the most.
- The not so good firm or one that aims to expand rapidly by selling a lot and using a factory model of lower-wage, lower-skilled people has the incentive to make the consultation little more than a proposal that prioritizes by what they sell, not by what you need.
- In short, you’ll probably get what you pay for.