How to Turn 5 Years of Task Data Into Meaningful Insights
Our team is doing data migration projects. Every month we resolve about 1,000 tasks and team had an intuition that by analyzing historical data we can find bottlenecks and identify processes that require further improvements.
As an input we had historical data of all tasks we have ever completed. And as a result we were looking to find out where can we improve efficiency of how we do our work.
In this post I am going share the steps we have followed to get from data of 50,000 tasks into set of meaningful metrics and reports.
Step 1: Load raw data from the tool
You need to start somewhere. So first thing was to get the export of all the tasks. We use custom built task management solution, so I had to ask for help from the development team to export the data. But if you use tools like Asana, Trello or Wrike you should be able to export CSV file easily.
As a result I got the CSV file of about 50,000 rows. Not to overwhelm myself I decided to work with data from April 2017 only. This way I would not need to deal with time series and it reduced the size of the file to about 1,000 rows.
Step 2: Identify metrics to monitor the trends over time
Before we move to the next step, you might want to refresh your PivotTable skills. PivotTable is a great way to summarize the data. OK, I will be honest, its not that easy. I had to browse few guides to get myself comfortable with PivotTable. This is one of the guides by Hubspot team that will help. If its your first time using PivotTable, save yourself a day and just in case buy a stress relief toy.
Now that I have my data in Excel and not afraid of PivotTable, I can start playing with it. Goal at this step is to come up with any crazy ideas of grouping the data in any ways you can. It’s like a brainstorming session. Any stupid idea is good.
Here are the reports that I have created:
- Tasks by type, with drill-down by assignee.
- Same as above, filtered to include tasks for paid projects.
- Same as above, filtered to include pre-sales projects we did not get paid for.
- Complex tasks, with drill-down by project category.
- Average number of tasks for each project category.
- Top closers of tasks for each team, with drill-down by task type
For each of the reports above, I have highlighted the numbers that stood out for some reason. For example, task type that pops up the most, person handling the most tasks, things we do for free during pre-sales projects, project categories that generate the most tasks and so on.
Step 3: Find trends to monitor on a regular basis
At this stage we had a quick team meeting to review the findings and various facts we have came up with at previous steps. Some of them were really useless, many were fun to check, but didn’t help a lot. But reviewing those reports together helped us come up with numbers that we want to monitor on a regular basis.
Trends we want to monitor on a weekly basis
- Number of easy tasks week by week. we are trying to automate as many tasks as possible, so we want this number of easy tasks to be decreasing over time.
- Number of tricky tasks week by week. We just want to monitor this number to understand the workload.
- Number of tasks in category of projects that generates most tasks.
- Number of tasks that require help from people in other teams.
We just started to collect this data, so it will take time to accumulate the value of this data.
Step 4: Setup daily metrics and reports
In addition to trends we also defined few daily reports to pay attention to.
Each team is now getting simple daily report by email. The report provides an overview of tasks completed yesterday summarized by the task type. Goal of the report is to create transparency of what is being done every day.
New KPIs for individuals
Also each person will get an email with personal metrics showing them how many easy tasks they closed, how many complex tasks closed and also how many tasks they reviewed completed by others.
So as a result, after few days of playing with data and some automation we have following new data :
- 2 new reports sent out by email
- personal KPIs for each team member
- set of metrics we track on a weekly basis
I know that what I did would not get me a Data Scientist degree and even junior data analyst might laugh at my methods here. But my goal was to get quick results from data we had, make first step towards resolving specific problem we had and take one more step towards becoming data-driven company. I think we accomplished that, and our metrics, analysis and techniques can only get better from here.
