0

Stats novice:

I work in an Operations department and we have a constant flow of customer contacts broken down by reason for contact. I have a minimum of 12 months worth of data.

I want to be able to trigger an alert based on whether number of contacts for a reason falls significantly outside of an expected value based on time of year and day of the week and recent trend for the reason i.e. the data shows that August will be more busy than Jan, Monday will be more busy than Sunday and that the data is trending downwards. I'd need this to run across 100+ contact reasons daily and will be running in Google BigQuery (or we have a Cube so that could be in Excel).

What test should I use to achieve this if I were to compute using SQL and what would the syntax for the test be?

Thank you very much

Emma

Emma
  • 1
  • Following @AlexK , I have recently posted some responses to analyzing daily data . Sort on newest https://stats.stackexchange.com/search?tab=newest&q=user%3a3382%20daily%20data . A favorite one of mine is this https://stats.stackexchange.com/questions/313810/simple-method-of-forecasting-number-of-guests-given-current-and-historical-data/313852#313852 . Holidays often play a role as responses before , on or after are often predictable. If you wish to post your data (ALL days must have an entry) , I will try and help you further. – IrishStat May 03 '19 at 17:19
  • Thank you very much AlexK and IrishStat; what you say makes a lot of sense. The easiest option would be control charting, but given the seasonal fluctuation this just wouldn't be reliable and would cause false positives in our busiest season. – Emma May 03 '19 at 18:31
  • The best option would be to use R. I have used this at a v basic level in the past and I know it does offer alerts, but it might take me too long to accomplish. What I'll do is read the literature over the weekend and if I need guidance then when I'm back Tues ask whether I'm permitted to post the data and take you up on your v kind offer IrishStat (no obligation) – Emma May 03 '19 at 18:37
  • 4 sure ... no obligation . many times both the instructor and the student learn from exercises ( particularly with new data ! ).. There is an R version of AUTOBOX that is fully functional if not free . – IrishStat May 03 '19 at 18:42
  • If you can't post the data .... doubly xform it (mean & standard deviation xform) and that will suffice at this stage of your investigation. By the way I started my long road to time series heaven as a QC statistician. – IrishStat May 03 '19 at 19:08

1 Answers1

0

It seems that you have two options. Ideally, I would recommend constructing a regression model that predicts the number of contacts based on values of month and day of week and that properly models the dependent variable as a count variable. You can also introduce variables that model overall and recent trends (e.g., variable that increments by 1 for every month/week from the start of your observation period) and that model any changes in your overall customer count. You can then compute the prediction interval for new (future) values and see if those new values fall within or outside the bounds of the prediction interval.

The second option is constructing statistical process control charts. The general idea is to compute the mean count of contacts for all possible combinations of values of the variables you are interested in (e.g., month of year, day of week) and then check if new values are above or below historical means let's say by more than two or three standard deviations. SPCCs allow for all kinds of rules for setting up upper and lower control limits.

There is at least one existing CV question on control charts: Understanding statistical control charts

For specific programming/syntax questions, you should use the Stack Overflow site. There are some Q&A's already there on the use of SQL for these kinds of problems:

Are there any Linear Regression Function in SQL Server?

Statistical Process Control Charts in SQL Server 2008 R2

AlexK
  • 1,007
  • 4
  • 11