Case study 03
Tracing 826 mismatched completion records to one migration rule
Part of the training portal project in Moving a government training portal onto Moodle 5.0, now with 3,800+ users. This one covers the data checks after launch.
After a government training portal moved to Moodle, I ran two investigations to check that its completion and booking data could be trusted.
The situation
The portal launched on a new LMS in February 2026, with years of training history carried across from the old system. Its reports feed completion figures and session planning.
Two questions came up in 2026. In June: were booking confirmation emails reaching everyone who enrolled? In August: did the portal's two kinds of completion report agree with each other?
My role
I'm the portal's LMS administrator, and I do its data work myself. For each question I defined what "correct" meant, built the comparison, and wrote up the results twice: as numbered items with examples for the developers, and in plain language for the partner.
What I did
Completion records (Aug – Sep 2026)
The portal produces an all-users report and a separate report for each module. They should agree, so I compared every user-module record across the two: 29,016 in all.
Before counting anything by date, I converted the timestamps from UTC (Coordinated Universal Time): The world's reference time zone. Sydney is 10 or 11 hours ahead of it.Glossary to Melbourne time. Without that step, 79 completion dates showed a day early, which matters for any record near the edge of a reporting period.
The comparison found 826 records for 681 users that didn't match. All of them traced back to one rule: how part-completed records migrated from the old portal were treated.
A second, independent pass from the raw files agreed with mine, and together they turned up 8 further report defects. In one, a status column could never show "Completed" for anyone. In another, the induction video report downloaded empty, although 293 people had watched the video.
Each finding went to the developers as a numbered tracker item with real examples. When fixes arrived, I checked them cell by cell on staging until the comparison showed zero mismatches, on 10 September 2026.
Booking confirmations (Jun 2026)
The partner noticed that some participants hadn't received booking confirmations. To see how widespread it was, I adapted a Google Apps Script extractor to pull about 700 sent confirmation emails into a spreadsheet. On the way, I fixed a parsing problem: the email tables put their labels in header cells, so key fields came back blank.
I then matched each email to an enrolment: 1,232 enrolments across 132 sessions. The session date field mixed formats, so I matched on recipient email and a normalised session title instead, and used booking time to break ties for sessions that ran more than once.
About 53% of enrolments in the audit window had a matching confirmation, and the session-level audit showed which sessions had none. A sent copy proves an email left the system, not that it arrived, so the figures describe sending, not delivery.
Results
- 29,016 completion records compared; 826 mismatches for 681 users traced to one migration rule.
- Zero mismatches on staging after the fix (10 Sep 2026).
- 8 further report defects found and logged with examples.
- About 700 confirmations matched against 1,232 enrolments across 132 sessions, giving a 53% coverage figure and a session-level audit.
What I'd do differently
- Make the comparison a standing check. I ran it once, as an investigation. Run after every release, it would catch report regressions within days.
- Ask for delivery logs on day one. For any email investigation, sent copies only tell half the story. I'd request the mail server's delivery records before starting the analysis.
Tools and skills
- Analysis: Python (pandas, openpyxl), Excel, record-level reconciliation, time zone handling
- Data collection: Google Apps Script for extracting email data
- Communication: developer-ready defect write-ups, plain-language summaries for non-technical readers
- AI: an AI assistant (Claude) for scripting and the independent second pass, with every result checked by me
What it looked like (recreated)
- 29,016 completion records compared
- 826 mismatches found, traced to one migration rule
- 0 mismatches after the fix
| Module | Records compared | Matched | Mismatched | Cause | Status |
|---|---|---|---|---|---|
| Module A | 4,812 | 4,690 | 122 | Part-completed rule | Fixed |
| Module B | 3,950 | 3,950 | 0 | – | Matched |
| Module C | 5,104 | 4,931 | 173 | Part-completed rule | Fixed |
| Module D | 2,876 | 2,876 | 0 | – | Matched |
| Module E | 3,410 | 3,322 | 88 | Part-completed rule | Fixed |
- Sent emails Extracted to a spreadsheet 720
- Recipient and normalised session title Labels read from header cells 704
- Candidate sessions One or more per email 690
- Booking-time tie-break For repeated sessions 46
- Matched session Email tied to an enrolment 652
| Session | Date | Enrolments | Confirmations found | Coverage | Follow-up |
|---|---|---|---|---|---|
| Session 12 | 3 Jun | 24 | 22 | 92% | None |
| Session 14 | 5 Jun | 18 | 9 | 50% | Check with partner |
| Session 15 | 6 Jun | 30 | 0 | 0% | Resend confirmations |
| Session 19 | 12 Jun | 16 | 13 | 81% | None |
Client, partner and vendor names, and some details, are left out. Any visuals are recreations with invented data.