Case study 03 · Jun 2026 – Sep 2026 · Analyst and LMS administrator # Tracing 826 mismatched completion records to one migration rule After a government training portal moved to Moodle, I ran two investigations to check that its completion and booking data could be trusted. *Part of the training portal project in [Moving a government training portal onto Moodle 5.0, now with 3,800+ users](https://dulitha.net/work/lms-migration-drupal-to-moodle/). This one covers the data checks after launch.* ## At a glance - **My role:** analyst and LMS administrator - **Period:** Jun – Sep 2026 - **Scale:** 29,016 user-module completion records; 1,232 enrolments across 132 sessions; about 700 confirmation emails - **Worked with:** a training delivery partner, an offshore development vendor and the studio's director - **Tools:** Excel, Python (pandas), Google Apps Script, an AI assistant - **Headline outcome:** 826 mismatched records traced to one migration rule, fixed, and re-checked to zero mismatches ## 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 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 --- *Client, partner and vendor names, and some details, are left out. Any visuals are recreations with invented data.*