Gordon Strodel, Archetype Consulting

Gordon Strodel

Data & Analytics Solution Owner for Slalom Boston. Background in Mechanical Engineering. Obsessed with solving problems elegantly and permanently.

📍 Boston, MA

Analyzing Workflow Trends and Predicting Time to Completion

Analyzing Workflow Trends and Predicting Time to Completion


At a local health exchange, customer requests were logged in the CRM system via a "service request" (SR.) Per the business process and the outreach program to the customers, each SR was to represent an indication of renewal by the health exchange. Thus, if you wanted to keep your 2014 health insurance into 2015, you had to call, email, or return a form to the local health connect. Those workers would then log a SR indicating the type of renewal the person requested.


For each renewal SR, there was a certain amount of work (business analysis and system work) each customer representative needed to do to complete the renewal application for 2015 for the customer. Given the deadlines of the Obama Administration, every renewal needed to be completed by a certain date for citizens to claim full coverage for 2015 and avoid the federal penalty. Thus, the health connect needed to monitor how these renewals were being processed over time and predict when they would all be completed.

The Approach:

Monitoring the overall total count of SR's is relatively straightforward, however additional requirements had to be defined and questions answered, such as:

  1. How do we know the renewal was processed correctly vs in-error?

  2. How do we count additional SR's that came in after renewals were starting to be processed?

  3. What is the best time frame to monitor the process and use as a baseline to predict future performance?

It was decided that the data be approached in the following manner:

  1. To determine if the renewal SR was processed correctly, a QC (Quality Control) process was set up for a select group of very experienced case workers to monitor and review the 2015 applications to ensure everything was entered correctly. Workers then "tagged" each renewal SR with a certain value depending on whether the SR was processed correctly or not. Thus the report data could ignore everything with a "bad" combination of "tags."

  2. A date cutoff was used to determine which renewals were "too late" vs. "critical to complete." The report data easily ignored SR's logged after a certain date.

  3. Given that the processing by case workers was relatively linear, a linear regression (with a vertical correction due to the last known data point) was used to predict future workflow. Due to the variability in worker processing over time, three time-bases were used for the linear regression: 7 day, 15 day, and 30 day. (Separate correction values were used for all three trend lines.)

The Chart:

This particular chart was generated after many, many renewal SR's had been processed and the health exchange was trying to finish the remaining 2015 renewal requests. Here's a quick explanation of what's going on:

  • The green area is the cumulative total of all renewal service requests aggregated by day

  • The "Today Line" was provided for easy reading but is not really needed

  • Per our earlier discussion, the 3 regression lines show up on right of the chart and are driven off the indicated time period. The vertical (y-axis value) has been "corrected" so that is always matches up with the last known data point of the green area.

  • The red, dashed "Target Cumulative" is hard-coded to the total number of renewals (completed + remaining to complete), but is end-dated at when the renewals needed to be completed. As you can see from the chart, many renewals were completed to the right of the red target line. This target line gave the health exchange an indication of overall performance. If the green area was above the red, dashed line, things were ahead of schedule.

Reading on the far right of the chart, you can see that the ETA for the Renewal completion is roughly in the mid-June time range assuming a 30-day average completion rate.


The chart above was produced by the reporting team on a daily basis for review by the Executive Team during the duration of 2015 Open Enrollment. It gave a clear indication of progress and remaining work to the operations team for workflow planing. It was one of the most-viewed charts of the reporting packet and generated excellent discussions on the Ops Team for prioritizing work and getting results.

Dive Deep! - Additional Resources

  • Since the above chart was excel based, if you would like additional detail into how the Excel file worked, feel free to contact me and I can send you a scrubbed copy of the file.

  • Regression Equations in Excel:

    • Slope of a Linear Regression: Link

    • Y-Intercept of a Linear Regression: Link

Data Visualization Summit 2015

Data Visualization Summit 2015

Monitoring Eligibility Results for Healthcare Exchanges

Monitoring Eligibility Results for Healthcare Exchanges