I am working on a model which will allow me to predict how long it will take for a "job" to be completed, based on historical data. Each job has a handful of categorical characteristics (all independant), and some historic data might look like:
JobID Manager City Design ClientType TaskDuration a1 George Brisbane BigKahuna Personal 10 a2 George Brisbane SmallKahuna Business 15 a3 George Perth BigKahuna Investor 7
Thus far, my model has been relatively basic, following these basic steps:
- Aggregate the historical data based on each category, calculating the mean, and counting how many times it occurs. From the previous example, the result would be:
Category Value Mean Count Manager George 10.66 3 City Brisbane 12.5 2 City Perth 7 1 Design BigKahuna 8.5 2 Design SmallKahuna 15 1 ClientType Personal 10 1 ClientType Business 15 1 ClientType Investor 7 1
- For each job in the system, calculate the job duration based on the above. For example:
JobID Manager City Design ClientType b5 George Brisbane SmallKahuna Investor Category Value CalculatedMean CalculatedCount Factor (Mean * Count) Manager George 10.66 3 31.98 City Brisbane 12.5 2 25 Design SmallKahuna 15 1 15 ClientType Investor 7 1 7 TaskDuration = SUM(Factor) / SUM(CalculatedCount) = 78.98 / 7 = 11.283 ~= 11 days
After testing my model on a few hundred finished jobs from the last four months, I calculated average discrepancies ranging from -15% to +25%.
In my actual model I have 15 categories, and am drawing historical data from ~400 jobs.
I think the largest issue (amongst others) is the simplicity of my model. Are their better/well established methods for calculating a value based on categorical data? And if not, how can I improve my predictions?