Event logs and process mining

01The problem

You could map Harbourline's clearance process by interviewing the team, and you should. But interviews tell you how people think the process runs. The team will say "documents are usually fine" and "inspection takes a day or two". The systems have recorded every clearance, every activity, and when it started and finished. That record says how the process actually runs, for all 477 cases, not just the ones people remember.

Using those records to discover and measure a process is called process mining. Large companies buy specialist software for it (Celonis, Disco, Microsoft Process Advisor), but the core ideas need nothing more than Excel, SQL or Python and an event log.

02The concept

An event log

Every process-mining analysis starts from a table with at least three columns:

What you can derive

• Variants: the distinct paths cases take. List each case's activities in time order and count the distinct sequences. A "simple" process often has dozens of variants.
• Rework: activities that repeat or exist only to fix something (Request corrected documents, Re-check documents).
• Processing time: end − start of each activity.
• Waiting time: the start of an activity minus the end of the previous one in the same case. Sort by case, then by start time, and take the previous row's end (LAG in SQL, shift in pandas, a formula referencing the row above in Excel).
• Lead time: last end − first start (or, for Harbourline, release − arrival).

Cautions

• Logs record only what systems record. A phone call chasing a customer may leave no trace.
• Timestamps can be when something was entered, not when it happened. Check a few cases with the people involved.
• Compare like with like: a pilot that changed the process (Harbourline's checklist, from May) creates new variants.

03Example

The most common variants of Harbourline's clearance process:

There are 9 variants in all. The rework variants (with a correction loop, sometimes two) aren't rare exceptions: together they're a large share of the work.

04Walkthrough

1. Sort events.csv by case_id, then start_time.
2. Add a processing time column (end − start, in hours).
3. Add a waiting time column: this row's start minus the previous row's end, only when the previous row is the same case.
4. Average the waiting time by activity. Which steps wait longest? (The first task below asks for one.)
5. Count the cases with at least one Re-check documents, before the pilot.

WITH e AS (
  SELECT
    case_id,
    activity,
    start_time,
    end_time,
    LAG(end_time) OVER (PARTITION BY case_id ORDER BY start_time) AS previous_end
  FROM events
)
SELECT
  activity,
  ROUND(AVG((julianday(start_time) - julianday(previous_end)) * 24), 1) AS avg_wait_hours
FROM e
WHERE previous_end IS NOT NULL
GROUP BY activity
ORDER BY avg_wait_hours DESC;

05Practice

06Check your understanding