Dustin K MacDonald

Menu
  • Home
  • About
  • Economic and Community Development
  • Nonprofit Management
    • Counselling and Service Delivery
    • Suicide Prevention / Crisis Intervention
  • Politics and Governance
  • Math and Statistics
  • Salesforce
Menu

Predicting Your Helpline Call Answer Rate

Posted on January 29, 2015November 25, 2018 by Dustin

One role of helpline managers is to manage their workers so that they can answer the most calls possible within the available resources. Even helplines that run 24-hours and have 100% coverage can’t answer 100% of the calls that come in if they have more callers calling in than workers available.

Using a system like Chronicall can give you real-time information on the calls that you answer and don’t and prepare more detailed results (for instance, noting where calls are not answered because the worker is already on a call.)

Given a series of values that are related to each other, regression allows us to predict values where we either don’t have the data or where we want to know the “average” of a piece of data.

For this task, we assume all you have is the data about how many hours your helpline is covered (either in hours or percentages) and the percentage of calls that you answer.

Hours Covered (out of 24) Call Answer Percentage
24 80
24 78
24 82
24 76
24 79
22 75
22 85
22 76
20 82
20 80
19 70
18 74

While we can use the regression formulas by-hand, Excel provides simple techniques for deducing the formula. The first step (for the purpose of this article) was to do the calculations by hand to demonstrate. You can see the regression article for full details on how to do this.

Regression By Hand

Hours Covered (out of 24) [X] Call Answer Percentage [Y] X2 Y2 XY
24 80 576 6400 1920
24 78 576 6084 1872
24 82 576 6724 1968
24 76 576 5776 1824
24 79 576 6241 1896
22 75 484 5625 1650
22 85 484 7225 1870
22 76 484 5776 1672
20 82 400 6724 1640
20 80 400 6400 1600
19 70 361 4900 1330
18 74 324 5476 1332
263 937 5817 73351 20574

b = (12*20574 – 263*937) / 12*5817 – 263^2
b = 0.71969

a = 937 / 12 – 0.71969 * (263/12)
a = 62.3101

So our final equation is:

Y’ = a + bX
Y’ = 62.3101 + (0.71969)X

Using Excel

We can use Excel to simplify this calculation. Starting with an Excel spreadsheet containing our X and Y values:

step1

Next, we use Excel’s LINEST function. This requires you to select TWO cells at once. The first required value (called an “argument” in Excel) is the known Y values. In this case, it is C2 through C13. The next value is the known X values (B2 through B13.)

STEP2

The third argument is whether to set b to zero, or to calculate it normally. Since we’re using the equation Y’ = a + bX and not the equation Y = mx + b, we’ll set it to TRUE. The final argument asks whether we want additional statistical information included, so we set this to FALSE.

STEP3

So our final equation is:

=LINEST(C2:C13;B2:B13;TRUE;FALSE)

After we’re done typing this, instead of hitting enter like normal, we hit Ctrl-Shift-Enter. This is very important! If we neglect to do this, Excel will only give us part of the information we need. If we’ve done this correctly, Excel will put brackets around the formula, like this:step4

And you’ll notice that both cells you selected are filled in. The first cell holds the b value and the second cell holds the a value. Putting them into the formula, we have:

Y’ = 62.31024 + (0.719685)X

So, if we want to calculate what our answer percentage will be if we have 21 hours of coverage:

Y = 62.31024 + (0.719685)21 = 77.42

This falls right in line with our expected values, and this technique can be used with any other data where you need to predict values in a linear fashion.



Cite this article as: MacDonald, D.K., (2015), "Predicting Your Helpline Call Answer Rate," retrieved on March 30, 2023 from http://dustinkmacdonald.com/predicting-your-helpline-call-answer-rate/.

2 thoughts on “Predicting Your Helpline Call Answer Rate”

  1. Zahir says:
    February 24, 2016 at 11:51 am

    Hi, I humorously posit that your Key Assumption about call flow being linear is incorrect (there are ways to fit non-linear curves and they are also available in Excel) . In the simplest case if you look at the predicted level for a 24 hour period than it’s conceivable that over that period there are smaller “busy periods” where the answered-call rate would be of significance when to compared to less busy periods in the same day. Not to mention that the flow for the day before Valentine’s day might be very different to the last Friday before a long weekend.

    You might want to look at something called Erlang C if you are not already familiar with it.

    Just something of interest I thought I’d bring up.

    Very useful site (I got some cool pointers already) and heartiest greetings from South Africa.

    Reply
    1. Dustin says:
      February 24, 2016 at 12:22 pm

      Thanks for the reply! You’re right that there are busier and quieter time periods, and I’m familiar with the Erlang C formulas for call centre staffing. We’ve found this simple linear formula generally smooths out over the 24 hour day and gives us some useful information, but this may not hold true for every (or perhaps even most) organizations.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Privacy Policy

See here for our privacy policy. This site uses affiliate links and Adsense ads to provide targeted advertising.

Tags

analytical technique assessment city council communication community development counselling crisis chat crisis intervention data science eastern university economic development education empathy evaluation forecasting fundraising governance humint intelligence intelligence analysis keokuk county language learning legal local government management peer support personal development politics professional development protective factors psychosocial risk factors safety planning salesforce sigourney social media statistics suicide suicide assessment suicide risk assessment technology terrorism training violence risk assessment youth

Recommended Posts

  • Conducting Psychosocial Assessments
  • DCIB Model of Suicide Risk Assessment
  • ABC Model of Crisis Intervention
  • My Friend is Suicidal - What do I do?

Recent Posts

  • University of the Cumberlands PhD in Information Technology
  • Joining the US Coast Guard Auxiliary
  • What is a Salesforce Business Analyst?
  • Why I Joined Mensa
  • NCCM Documentation

Archives

  • March 2023 (1)
  • February 2023 (2)
  • January 2023 (4)
  • December 2022 (2)
  • May 2022 (1)
  • April 2022 (2)
  • March 2022 (1)
  • February 2022 (1)
  • December 2021 (1)
  • October 2021 (1)
  • August 2021 (2)
  • May 2021 (3)
  • December 2020 (1)
  • November 2020 (4)
  • July 2020 (1)
  • June 2020 (1)
  • April 2020 (1)
  • March 2020 (4)
  • February 2020 (7)
  • January 2020 (1)
  • November 2019 (1)
  • October 2019 (2)
  • September 2019 (4)
  • August 2019 (2)
  • March 2019 (1)
  • February 2019 (1)
  • January 2019 (1)
  • December 2018 (4)
  • November 2018 (3)
  • October 2018 (3)
  • September 2018 (19)
  • October 2017 (2)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (39)
  • May 2017 (3)
  • April 2017 (4)
  • March 2017 (4)
  • February 2017 (4)
  • January 2017 (5)
  • December 2016 (4)
  • November 2016 (4)
  • October 2016 (5)
  • September 2016 (4)
  • August 2016 (5)
  • July 2016 (5)
  • June 2016 (5)
  • May 2016 (3)
  • April 2016 (2)
  • March 2016 (2)
  • February 2016 (2)
  • January 2016 (4)
  • December 2015 (2)
  • November 2015 (2)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (1)
  • June 2015 (2)
  • May 2015 (5)
  • April 2015 (3)
  • March 2015 (8)
  • February 2015 (12)
  • January 2015 (28)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Recent Comments

  • Dustin on Eastern University Master of Science in Data Science Half-way Review
  • Joly on Eastern University Master of Science in Data Science Half-way Review
  • Dustin on Crisis Triage Rating Scale (CTRS)
  • thomas mahek on Crisis Triage Rating Scale (CTRS)
  • Dustin on Eastern University MS in Data Science 2022 Review

Tags

analytical technique assessment city council communication community development counselling crisis chat crisis intervention data science eastern university economic development education empathy evaluation forecasting fundraising governance humint intelligence intelligence analysis keokuk county language learning legal local government management peer support personal development politics professional development protective factors psychosocial risk factors safety planning salesforce sigourney social media statistics suicide suicide assessment suicide risk assessment technology terrorism training violence risk assessment youth
© 2023 Dustin K MacDonald | Powered by Minimalist Blog WordPress Theme