Note from Pat: Since writing this post, I added another ROI Calculator on the 3rd tab…for direct mail. Enjoy.
While catching up with an old friend and colleague, the conversation turned to setting goals and tracking the performance of certain social media activities as well as a social media channels over time.
So, I pulled out my laptop and put this workbook together. Hopefully it will be of some value to you too.
Campaigns with Multiple Activities. My friend wanted to promote a special offer – for example, a $100 purchase with 20% profit - through multiple channels to cold leads, prospects and current customers. In order to track results and evaluate the performance of individual activities as well as the overall campaign, he created unique landing pages, toll-free numbers and email accounts for each activity. In other words, when he ‘tweeted’ this offer, the link took the respondent to a unique landing page with a registration form, a unique toll-free number and unique email account so the individual could respond through the form, an email or a call.
We added all of the planned activities (Rows 2 through 5) along with the relevant data – the cost (budget) for each activity, the projected audience size (reach), response rates as well as the order size and gross profit (%).
This allowed him to evaluate each activity and decide if it made financial sense to move forward.
Now, to be honest, the Projected Response Rate (%) for Twitter was a ‘SWAG’ – but the workbook allowed him to run multiple scenarios in order to see the impact. So he simply ran three scenarios with what he considered to be “Low”, “Medium” and “High” variables. Even in the worst possible projection, he felt comfortable with the overall results so he decided to move forward.
In Columns K through Q, he was able to enter in actual results so he could compare each activity and compare with the original projections. As you can see in Cells Q6, Q8 and Q9, the actual performance was much better than originally projected in this scenario. (And in Cell N6, the actual expenses were lower than projected.)
Channel ROI. My buddy was also interested in how to evaluate a specific channel – Social Media or, at an even deeper level, Twitter, Blogs, etc. This worksheet was created to help him with the corporate blog over a specific time frame (month, quarter, year).
Columns A through D allowed him to project expenses. He felt that about 20% of a full-time employee (FTE) would be able to handle writing, posting, responding to comments, etc. And he felt that about 5% of another IT FTE would help with technical issues. (Again, he ran Low-Medium-High scenarios so what you see in the workbook at this point is just for the purpose of showing how the calculator works.)
Columns E through J help project important results – conversion rates for moving ‘Followers’ to ‘Customers’ that purchased $1,000 worth of products/services in that time frame with 25% gross profit. If he were to hit these numbers, he would have a $625 (Cell G9) profit from the channel. (Again, creating a process for moving individuals from Point A to Point B so that they purchase products is critical – so use Promo Codes, unique landing pages, toll-free numbers.)
And, finally, Columns K through P were used to report actual results.
Summary. Calculators that have been designed for one particular scenario might not work for other businesses – but it can get the juices flowing so that some tweaks can be made or a completely new tool created. The value of tools like this is that it forces everyone to walk through a decision making/goal-setting process. At the end, you have buy-in and understanding that could dramatically alter the initial plan.
Let me know what you think…and if you have questions, ask!!!
