MSP metric #1: Customer effective rate - A key profitability metric you should be tracking

As a managed service provider, you probably track many key metrics to calculate account profitability, SLA compliance, resource utilization, product margin, net operating income, and more. While the use for some of these metrics depend largely on the size of your operation and your clientele, here's an interesting KPI that can give you insight into dollars-made-per-hour.

Customer effective rate (CER): It is a measure of how your customers are effectively using the engineer's time against what they are paying for. This KPI helps you asses the general profitability of the account, asses the performance ratio of engineers' time vs the dollars made.

Customer effective rate = total amount invoiced / number of hours spent or logged on the customers' account

To build this report, you need to create a couple of query tables (the queries are given below), and create an aggregate formula on "Query2" to calculate the Customer Effective Rate (formula included below).

Query 1

SELECT

w.*,

r."Account" `Account`

FROM "Worklog" AS w JOIN "Request" AS r ON w."RequestID" = r."RequestID"

Query 2

SELECT

b."Account" `Account`,

YEAR(b."Bill Date") `Billing Year`,

SUM(b."Bill Amount") `Total Bill Amount`,

ROUND(SUM(w."Time Spent in Hrs"), 2) `Total Time Spent (Hrs)`

FROM "BillDetails" AS b LEFT JOIN "Query 1" AS w ON b."Account" = w."Account"