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