Client Efficiency Index for ConnectWise Manage On Premise

Overview

This document describes how to create Client Efficiency Index (CEI) gauges in BrightGauge. Please note that the examples in this document use datasets specific to ConnectWise Manage®.

What is Client Efficiency Index?

CEI is a metric calculated from gross margin that enables you to monitor and compare client performance. The CEI is calculated as follows:

CEI.png

Here, n equals your normalization factor. The normalization factor ensures that your target gross margin yields a CEI of 1 (100%). For example, if your target is 60%, the CEI is calculated as follows:

CEI = [Gross Margin] + 0.4

Normalizing the data enables you to more easily determine which clients are off-target and by how much.

CEI Gauge Layers

CEI gauges are built using three normal layers and three calculated metric layers:

  1. Layer 1: Total Revenue
  2. Layer 2: Labor Cost
  3. Layer 3: Direct Cost
  4. Calculated Metric 1: Layer 1 - Layer 2 - Layer 3
  5. Calculated Metric 2: Calculated Metric 1 / Layer 1
  6. Calculated Metric 3: Calculated Metric 2 + n

The following sections provide more information on the three normal layers and list the parameters necessary to build each layer. Please note that the datasets required to build the normal layers differ between the On-Premises and Hosted ConnectWise Manage® datasources.

Layer 1: Total Revenue

This layer sums all invoices that account for agreement and time and materials revenue. Total revenue includes all monthly recurring managed service revenue and any add-on time and materials work for each client.

To build the layer:

  • Dataset: Invoice Data
  • Chart type: Column chart
  • Measure: Sum of invoice_amount
  • Dimension: company_name

Layer 2: Labor Cost

This layer utilizes the fully loaded labor cost. The fully-loaded labor cost is tied to members in Manage and includes actual hours related to each account fully loaded with burden rate. Ensure that your labor cost data is correctly configured in Manage. Member cost information is set in Manage on a member's Details tab in the Time/Expense/Billing Information section. For more information, please refer to Time Expense Billing Information.

To build the layer:

  • Dataset: Time Entry Last 210 Days (on-premises) or Time Entry Last 120 Days (hosted)
  • Chart type: Column chart
  • Measure: Sum of time_cost
  • Dimension: company

Layer 3: Direct Cost

This layer shows the costs associated with supporting a client, including RMM licenses, software licenses, backup hardware, etc. This information is gathered from the additions within a client's agreement. Additions include a price and a cost. Because BrightGauge utilizes the addition cost, ensure that each addition has a Unit Cost. For more information, refer to Additions Tab - Agreement.

  • Dataset: Agreement Data Monthly (on-premises) or Agreement Data Monthly Last 3 Months (hosted)
  • Chart type: Column chart
  • Measure: Sum of total_addition_cost
  • Dimension: company_name

Note: Layers 1-3 will have date-based filters depending on the gauge (or what you want the gauge to reflect).

We have taken the above information and now included it in six new gauges:

  • CEI - Last Month

Screen_Shot_2017-06-28_at_13.56.45.png

  • Top 25 CEI - Last Month

Screen_Shot_2017-06-28_at_13.57.25.png

  • Bottom 25 CEI - Last Month

Screen_Shot_2017-06-28_at_13.58.09.png

  • CEI - Last 3 Months

Screen_Shot_2017-06-28_at_13.58.28.png

  • Top 25 CEI - Last 3 Months

Screen_Shot_2017-06-28_at_13.58.48.png

  • Bottom 25 CEI - Last 3 Months

Screen_Shot_2017-06-28_at_13.59.10.png

 

Build a CEI Gauge

To build a CEI gauge:

  1. From BrightGauge, select New Item > Gauge.
    Add a new gauge
  2. Select your ConnectWise Manage datasource.
  3. Select the Invoice Data dataset.
  4. Click Continue.
  5. Select Column Chart Gauge.
    Column chart type
  6. Add invoice_amount as the Measure.
  7. Select Sum as the aggregation and click ADD.
    Select sum aggregation
  8. Add company_name as the Dimension.

    Tip: To help keep track of your layers, give your layers a helpful name. Select the Design tab in the left side panel. Click the layer in the Formatting section, then enter a Label. For the first layer, enter Total Revenue and click APPLY. Select the Data tab to continue building the gauge.

  9. Click the plus sign to create a new layer.
    Add a new gauge layer
  10. Select New Layer > Blank (New Dataset).

    Important: Selecting Blank (New Dataset) enables you to select the correct dataset for the new layer.

  11. Select your Manage datasource.
  12. Select the Time Entry Last 210 Days dataset.
  13. Click Continue.
  14. Ensure that the chart type is set to Column Chart Gauge.
  15. Label this second layer Labor cost.
  16. Add time_cost as the Measure.
  17. Select Sum as the aggregation and click ADD.
  18. Add company as the Dimension.
  19. Click the plus sign to create a new layer.
  20. Select New Layer > Blank (New Dataset).
  21. Select your Manage datasource.
  22. Select the Agreement Data Monthly dataset.
  23. Click Continue.
  24. Label this third layer Direct cost.
  25. Ensure that the chart type is set to Column Chart Gauge.
  26. Add total_addition_cost as the Measure.
  27. Select Sum as the aggregation and click ADD.
  28. Add company_name as the Dimension.
  29. Click the plus sign to create a new layer.
  30. Select Calculated Metric.
  31. Drag and drop the first three layers and the subtraction operator into the Formula bar to create the following formula: Total revenue - Labor cost - Direct cost
    Build your first calculated metric
  32. Click the plus sign to create a new layer.
  33. Select Calculated Metric.
  34. Label this layer Gross Margin.
  35. Drag and drop the first calculated metric layer, the division sign (/), and the Total Revenue layer into the Formula bar to create the Gross Margin formula.
    Build your second calculated metric
  36. Click the plus sign to create a new layer.
  37. Select Calculated Metric.
  38. Label this layer CEI.
  39. Drag and drop the Gross Margin layer, the addition sign (+), and the Any Number field into the Formula bar to build the CEI formula.
    Calculate your CEI
  40. Enter your normalization factor and click APPLY.
  41. Hide all layers except the CEI layer.
    • Click the three dots on a layer tab.
    • Select Hide.

We hope you get a lot of great usage out of this new info!  As always, if you have any questions, feel free to reach out to us at our support desk.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments