This is a common question from CEOs who are optimizing their sales and customer support teams: “Which customers consume the most effort but yield the least money?”
This often called the efficiency per client, or the ROI (return on investment) of a customer.
In theory, you could collect all of the activity done on a client and weigh that against all of the money attributed to that client. Then compare this for all clients and like Santa (sorry — ’tis the season), see who has been good and who has been bad.
However this is harder than it seems! We’ll walk you through it – there’s an easy solution at the end of the tunnel.
Step 1: Collect Activity Data
Getting good activity data has two important facets:
1. Make sure you collect activity data from all customer-facing groups of your company! For example, you want to collect both sales data as well as customer-support data if you want a true holistic picture of the effort spent on a customer.
This can be harder than it seems because each group probably has its own database of activities. For example, your sales team may log their activities in Salesforce.com while the support team logs its activities in ZenDesk. Most online systems will have APIs to programmatically pull this data, or you can fall back to the tried-and-true Export To CSV method.
2. The activity data should be weighted. If you do a simple count of activities, a 1-minute voicemail will count the same as a 60-minute demo call. The same goes for support emails vs. support conference calls, or any other process you have which touches a customer.
We recommend that you give each activity a weight, and then sum across the weighted amounts. The weight can be any number but we recommend setting “1” as your most basic customer touch, and then giving every other activity an integer relative to that base activity’s effort, like this:
PS. These are the weights we use for our company. Get your own!
Step 2: Collect Money Data
Ideally you’d want revenue per client. But, revenue requires knowing when money was earned — often a complex process involving general-ledger debits and credits on Deferred Revenue. This makes the problem much more complex.
What about using payments per client? This is a good idea, except for the fact that in the QuickBooks API, there is no association between payments and invoice line items. You need to be able to filter on invoice line items in order to exclude things like expense reimbursements, which should not be counted towards the efficiency calculation.
Thus, we’d recommend using invoiced amount as your efficiency money metric if you are on QuickBooks. Yes, this could overestimate the money from each client if they have a problem paying you. However using invoices will give you the flexibility you need on filtering out non-relevant money amounts.
Note: if your company collects annual prepayments for work, both payments or invoices will load the money towards the front of your analysis time window. However as long as your analysis window is at least a year and your customers have at most an annual prepay, this is a moot point and won’t be an issue.
Step 3: Combine And Report
Our goal is the classic formula:
ROI = return / investment
You could do this as “dollars per unit of effort”:
ROI = [sum of invoiced amount] / [sum of weighted activities]
However, this will yield a set of results that are all fractions of a penny. That’s hard to wrap your head around. We think a more intuitive way is instead to flip the equation on its head and instead calculate the “effort per dollar“:
Efficiency = [sum of weighted activities] / [sum of invoiced amount]
Do the above calculation for each customer with an invoice in your time window. For both parts of that equation, you want to use the date of the activity and the due-date of the invoice as your criteria on whether to include those items in the time window.
What system should you use to compile all this data? Excel is good for making a quick prototype. However don’t make a permanent dashboard in Microsoft Excel — you will suffer from data update hassles, file-size issues, and human error.
Microsoft Access is a slightly better option for building your own analysis from scratch since it’s less likely to break when you update the data. But Access is not exactly the most user-friendly system. Also, you’ll still suffer from how to automatically get data into Access from your various sales / support / finance systems.
Of course, if you’re a big company and you have an in-house development team, why not go full custom with a MySQL database, some Python scripting, and the API connections to each data source? The answer to why not is that custom work is reinventing the wheel if this problem has already been solved by ready-made software.
Thus, what’s the easiest way to get the above analysis? Use an online Sales + Support + Finance analytics system…like us! The above three steps are very close to how we do our built-in Efficiency report shown in the screenshot above. Ours has extra features like one-click cross-system filtering…but that’s the topic of another blog post.