2
$\begingroup$

Problem

Determine the relationship between the percentage of the time that the phone gets answered before going to voicemail and the number of sales that are made.

So example output might be:

  • f(43%) = 4
  • f(80%) = 10
  • f(95%) = 12

Note: I'm not a math professional so I apologize that I am unable to express this problem in the correct and formal way.

Background

I own a mobile auto detailing company. I wrote a computer program that routes all of our calls in and out of the company. It also has a scheduler where we put in all of our appointments (a new appointment is how I am defining a "sale" here.)

So I have a huge list of phone calls about 13,000 in the past year - and I can go back at least 5 years if need be. I also have a huge list of all of the appointments that we've scheduled, including the times that the appointments were created.

What I don't understand is how to process this data so that I can get an approximate relationship between the percentage of the time that we are answering the phone at any given time and how many appointments we are scheduling.

This is especially difficult since we almost always return calls that are missed, and sometimes we do schedule appointments that way, so its hard to see the affect on answering the phone when they first call vs. calling them back. For all I know, the relationship could be inverted and we make more sales when we don't answer the phone as much (although this is highly unlikely)

I am a computer programmer, so I can write up some code to process this data, but I don't know the correct statistical methods to process it properly.

My end goal is to be able to understand things like

"Since we answered the phone 75% of the time today, and got 43 total calls, we probably would have scheduled 2 more jobs if we had answered the phone 20% more."

The only way I can think of to do this is do break all of the data into days, then plot it in a spreadsheet with one axis being percent of the time the phone was answered that day and the other axis being the ratio between appointments scheduled and total calls for that day - then draw a trend line - but this seems a little crude since the data is being broken up into days.

For anyone who's interested, here's a screenshot of some of the information tallied by day:

enter image description here

Edit

I think what I had in mind when writing this original question was probably overkill for the problem. Essentially we are dealing with a few signals:

Calls: _____1____1____0_1____1____1_____0___________0______ 
       0 = Missed, 1 = Answered

Sales: ______1_________1_________1____________1___________1
       1 = a sale was made

There is probably some mathematical way to find the relationship between the two signals. Like looking at the froward time deltas between phone calls and sales being made that will expose the relationship between periods of time with greater and lesser answer rates vs. sales being made. The complexity here is that I was looking for a mathematical process that doesn't require the data to be pre-clustered into days (or some other time period).

...But enough of that! There is an easier and slightly less precise way to do this with "common sense" math if I just take the precision hit and pre-cluster into days. Frankly the results are more than good enough for the purposes here: For us to understand the approximate financial cost of missing calls (and the financial gain of higher answer rates). Thus I can balance this priority against other priorities. Now I can answer questions like

  • Should I pay X dollars to hire another person to help answer the phone?
  • Is it ok to miss a call during a team meeting?
  • Do we have enough volume to hire another technician if we answer the phone more?

Approximate Solution (Good-enough solution)

I exported a year of data into Google Sheets, specifically I looked at: - Percent of phone calls answered - Total appointments scheduled / Total calls for the day. (a higher percentage here means that we converted more of our inbound leads into sales)

Each dot represents the "Percent of Inbound Calls Answered" for that day vs "Number of Appointments Scheduled Divided by Total Inbound Calls".

As you can see, higher answer rates are correlated with a larger percentage of inbound leads being converted into sales (Note, all missed calls are always returned even if they don't leave a message):

Then I added a trend line. If I understand what I did correctly, the trend line is basically saying that for each call we answer, there is about an 18% chance we will make an additional sale that day. Another way of looking at it is that for every 5.5 calls that we miss, we are losing 1 sale.

enter image description here

$\endgroup$
  • $\begingroup$ If you own a business, you should hire a mathematical consultant for your mathematical questions. $\endgroup$ – Gerry Myerson Sep 20 '17 at 1:52
  • $\begingroup$ Trouble with your trend thru blue dots is that the residuals (vertical dist. from dot to line) are much more variable at right than at left. This means you can't reliably use regression line for prediction. Not sure what cure. But you might think why variability for high pct answ is so much higher. $\endgroup$ – BruceET Sep 27 '17 at 23:27
1
$\begingroup$

When you decide on exactly what variables to use, you might have some success doing a linear regression. From your screenshot I used $x$ =percent answered as the 'predictor' variable $(79,70, 43, \dots)$ and $y$ = net appointments $(4, 7, 8, \dots)$ as the 'predicted' variable. Then I made a plot of $y$ (vertical axis) against $x$ (horizontal axis).

The next thing is to find the 'regression line'. The data are very noisy and certainly do not fit a line very well, but a line can be found: The method to find the "best" line is to define a $d$ residual as the vertical distance from each point to the line. Then to draw the line so that the sum of squared residuals is minimized. It sounds like it might be a lot of trial and error, but it's not. There is a fairly simple algorithm for doing it. In effect, the data pick the line. Here are the points and the line.

enter image description here

The like slopes downward, showing a negative association between $x$ and $y.$ The equation of the line is $Y = 5.96 - 0.0345 x.$ On average about 60% of the calls are answered each day and there are about 4 appointments a day. If only 50% of calls are answered there are is a slight increase in the daily number of appointments. The negative slope (-0.0345) indicates that the daily number of appointments rises by about .35 for every 10% decrease in percentage of calls answered.

The bands show how good predictions of $y$ from $x$ would be for a 'new' day (with a new $x$ and a new $y.$ For so little data and so much scatter about the line, the bands are far apart. For example, 70% answered could yield between 0 appointments and about 11. This is called a 'prediction interval' (PI).

The program I used prints a cluster of four more graphs that help to decide whether the data roughly meet the mathematical assumptions for a useful interpretation of the regression line.

enter image description here

The straight line at upper-left and the histogram at lower-left indicate that the residuals are nearly normally distributed, which is good. The scatterplot at upper-right shows that the size of the residuals (vertical scatter about the line) is about the same for all values of $x,$ which is also good. The plot at lower-right indicates that residuals get smaller over the 21-day period for which you have data, which is puzzling (especially because I don't know the circumstances of that period).

With more data, the line might slope upward and the band for the prediction interval might get narrower. (There simply isn't enough data here to overcome the noise and determine the true direction of the association--positive or negative.)

Practically, I realize that this experiment probably does not yield useful results. There is a small amount of data and I may have picked the wrong numbers off you screen shot. Theoretically, the experiment indicates that your general idea is worth further investigation.

I used Minitab statistical software for my analysis, but I believe you can do a very similar regression analysis in Excel (perhaps without the four 'diagnostic' graphs at the end). I hope you have a general idea what I did and that you can figure out how to do something similar in Excel. I'd like to encourage you to experiment further with more data, and perhaps more knowledgeably selected variables.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.