Skip to content

Tracking enrolment processing efficiency and identifying bottlenecks

Introduction

Below is a real-world business challenge that I’ve encountered in my work. I have tried to lay out my thought process on how I go about it.

The insights below contains examples of work in my previous role, presented in a way that protects sensitive and proprietary information while demonstrating my technical skills and approach.

I have modified the data to ensure confidentiality and removed any information that could identify individuals or an organisation. This approach maintains the integrity of the analysis while upholding strict data privacy standards.

Thought process

Problem Definition

Be very clear about what is the business challenge/problem

  • I have found it helpful to clearly outline the business problem and its context. Writing it down on a piece of paper or on a whiteboard helps me to know what the business problem is rather than just thinking it in my head.
  • Clarifying the meaning of certain jargons that I need confirmed. Sometimes it may be my preconceived ideas but I tend to want to confirm what I think it means and getting feedback from the requester.
  • Ask a lot of questions related to the business problem (5 W - Why, What, Where, When, Who)

Test test and test! Unit testing and other possible testing methods to see whether the outcome is what is expected. In terms of a SQL query, I usually need to refine my query slightly. Along the way, I fix and improve the shape,format, and measures while adding comments about my thought process and why I've designed the query as such. With each version, I question whether the outcome make sense and whether it has answered the business problem fully.

Background

Stakeholders wanted to analyse workflow data to pinpoint delays in course enrolment processing and speed up approval times. However, there were no existing reports that captured enrolment transactional processing data even though the student source system did collect enrolment processing logs and timestamps. The goal was to have a dashboard that helped identify where bottlenecks occurred in the workflow.

Define scope

Since the amount of data from transactional processing was large, I met with one of senior business analyst to understand the scope for this report. Rather that grabbing all data, we decided to only focus on a specific cohort of students that would benefit the most from this.

Metrics to track

Discussion also centred on what were the must-haves in the report such as identifying monthly trend information and having key metrics like the number of days for approval, and the enrolment processing volume.

Pain points

We identified that staff were manually approving a cohort of course enrolments, when it should be automated. The goal was to identify where this pain points were, was it similar courses within a subject area, was it for particular student cohorts with similar circumstances? And were there random or reoccuring yearly?

Exploring source tables

During this exploration stage, I actively questioned existing processes and assumptions to ensure my understanding was accurate. This involved engaging with stakeholders and other business analysts to clarify procedures, validating possible workflow scenarios against real-world practices, and continuously challenging preconceived ideas.

Using SQL Server, I familiarised myself to the different tables to understand the data structure, and identify key fields like datestamp of earliest student enrolment to a class and date stamp of earliest approval. Most of these fields came from audit tables and student based enrolment tables. A number of discussions took place on teasing out the scenarios of the workflow at each step. The discussion focused on understanding the key points when a student first initiated their interest to enrol to a subject course to the time taken for that application to be approved automatically or manually by staff.

--Below is an example of the process taken to retrieve the fields and key metrics to answer the business challenge
--Retrieving transactional date stamps from source audit table. Since a student's enrolment ID could have multiple duplicated statuses, we used the first initiated date stamp and first approved date stamp.
WITH first_date AS
(
SELECT 
	ea.ID
	,MIN( ea.ProcessDate ) as first_date
	,MIN( CASE WHEN ea.Status = ' ' AND ea.Status in ( 'U', 'V' ) THEN ea.ProcessDate ELSE NULL END ) AS first_initiated_date
	,MIN( CASE WHEN ea.Status IN ( 'A' ) THEN ea.ProcessDate ELSE NULL END ) AS first_approved_date
	,MIN( CASE WHEN ea.Status IN ( 'X' ) THEN ea.ProcessDate ELSE NULL END) AS first_cancelled_date
	,MAX( CASE WHEN ea.Status IN ( 'X' ) THEN ea.ProcessDate ELSE NULL END) AS last_cancelled_date
FROM source_audit_table AS ea
WHERE ProcessDate >= YEAR(GETDATE())-5 
GROUP BY ea.ID
)
,
--Retrieving course data
course AS
(
SELECT DISTINCT 
	Faculty 
	,Subject_Level
	,Subject_Name 
	,Subject_Full_Desc
FROM Dim_Table
)
,
--
Final AS
(
SELECT
	er.ID 
	,er.ID
	,er.Year
	,CASE WHEN er.Semester in ('S','RS','XS') THEN 'Summer School'
		  WHEN er.Semester='S1' THEN 'Semester 1'
		  WHEN er.Semester='S2' THEN 'Semester 2'
            END AS Class_Start_Semester
	,c.Faculty 
	,c.Subject_Level
	,c.Subject_Name 
	,c.Subject_Full_Desc
	,er.Module AS Course
	,er.ClassCode
	,coalesce( fd.first_initiated_date, fd.first_date ) AS Intiated_Date
	,datediff( day, coalesce( fd.first_initiated_date, fd.first_date ), first_approved_date ) as Initiated_to_Approved_Duration
FROM source_enrol_table AS er
	INNER JOIN first_date AS fd
		ON fd.tID = er.ID
		AND fd.first_approved_date IS NOT NULL -- Only want to see those enrolment processing points that had an approved status
	LEFT JOIN course AS c    
		ON c.course = c.course_code
WHERE er.Year BETWEEN YEAR(GETDATE())-5 AND YEAR(GETDATE())-1
)
SELECT * FROM Final

Visualisation

To visualise the results, I imported the code into the Power BI.

Since stakeholders wanted to identify trends using key metrics like the number of days for approval, and the enrolment processing volume, I built a parameter button allowing users to toggle between the two metrics that interact dynamically with the visuals and other slicers on the right.

Key findings

  • On average from 2020 to 2024, it takes up to 8 days for an enrolment to move from initial processing to an approval status. When initial processing is initiated from January to September, it takes less than 4 days for approval. October to December months can take up to 17 days for approval.
  • From January to September, 60% of applications are approved on the same day, but during the busy season of October to December, same-day approvals drop to less than 30%.
  • Course enrolment patterns only seem to impact Semester 1 and 2 with average approval duration being wildly different in the different months. Summer School approval times are not impacted.
  • Enrolment processing volume is the highest during the month of January, February and November.
  • A specific subject related courses faced more delays in approval than other courses.

Stakeholders were also interested in seeing whether the number of average days of approval differed much across the years. So I created another page to answer this.

Some of the questions that I had to the stakeholders were as such below and it helped make me understand the current process workflow done by the faculties, and going forward , my recommendations on monitoring these:

Questions

  1. Are there specific approval duration targets from faculties?
  2. What factors contribute to longer approval times, and how can they be mitigated? It seems to be dependent on which month the processing was initiated

Going forward

Enrolment processing and approval process can be improved based on

  • Focusing on enrolments that took more than a week to approve to understand specific delays
  • Ensuring the business process to process these course enrolments are consistently managed by faculties for e.g. reviewing steps between initial processing and final approval to eliminate redundant tasks, creating guidelines and best practices for handling course enrolment volumes, training staff etc.
  • Implementing automated reminders or flags for course enrolments pending for approval for more than a certain number of days.