Having trouble converting between Business KPIs and your Workforce Engagement Management (WEM) system?
Maybe you set targets in Percentage of Calls Answered (PCA), but your Workforce Management (WFM) system needs Average Speed of Answer (ASA) or Service Level (SL) instead?
You're not alone – we hear this a lot. To help, our Technical Consultant, James Knight, shares a simple trick to create a conversion formula that provides personalised outputs for your business, channel by channel.
Read on for some Excel magic tailored for Contact Centre Managers and Planners!
Step 1
Get data that includes both metrics for a chosen time period. We usually recommend daily data, as it provides a good balance – enough data per row to show some consistency (having only 10 contacts a day would be too variable!) while still having enough rows to draw meaningful conclusions.
You can also use different time intervals, such as weekly, hourly, or even 15-minute intervals, depending on your needs.
Step 2
Go to Insert → Charts → Scatter and select a scatter chart using your data. You may need to adjust it by right-clicking the chart and selecting "Select Data" to ensure it's displaying correctly.
Step 3
Right-click on any data point and select "Add Trendline".
Step 4
Right-click on the trendline and select "Format Trendline." At the bottom of the menu, tick the boxes to display both the Equation and the R-squared value.
A quick note on R-squared: The closer this value is to 1, the stronger the correlation between your trendline and data points.
Next, choose a trendline that best fits your data. In this example, we've gone with a 2nd-order Polynomial (anyone else getting GCSE Maths flashbacks?) because it gives the best fit and the highest R² value.
Your graph should now look something like this – almost there!
Step 5
Convert the Equation to a Formula!
Here’s what to keep in mind:
-
y is the vertical axis, and it should be the output you're looking for. In this case, we know our Average Speed of Answer (ASA) but want to find the equivalent PCA. If you need the opposite, you can switch the data around, or you could always solve for x instead!
-
1E-07 is scientific notation in Excel, which means moving the decimal point for 1.0, 7 places to the left—so 1E-07 = 0.0000001.
-
To raise a number to a power, use x^2.
So, the equation on the graph:
y = 1E-07x² - 0.0004x + 0.9969
Becomes the formula:
= 0.0000001(A1^2) - 0.0004A1 + 0.9969**
Now, typing any ASA value into cell A1 will give you the equivalent PCA!
Some Additional Tips:
Consider re-doing this analysis if any of the following change:
- Mix changes (like separating sales and service)
- Routing changes that deflect certain call types before reaching the queue
- New on-hold messaging encouraging callers to visit the website
- A fresh, catchy on-hold tune!
Further Thoughts:
- Anything that impacts caller patience (e.g., time of day) could also affect this relationship, and it’s worth exploring.
- Validating an IVR redesign with analysis that shows fewer abandons for the same wait times could be another useful application of this method.
