Standardize the inputs
Normalize dates, route labels, and keys from tracking workbooks before aggregation.
CASE STUDY 07 / DATA ENGINEERING
Pilot metrics and reporting automation
Keeping monthly research summaries consistent as records and program rules evolve.
ROUTE-BY-MONTH SUMMARIES
Illustrative visual; no research data shown
THE QUESTION
How can recurring study metrics be generated without confusing unique participants, repeat trips, and usable endpoint observations?
THE SETTING
The R workflow reads master tracking data, applies program-specific completion rules, updates route-by-month metrics, and writes formatted multi-sheet Excel summaries.
THE STORY IN PLAIN LANGUAGE
A metric is only useful when its definition survives the next update.
Repeat participants, incomplete endpoints, and changing route records make a simple row count misleading. The reporting workflow encodes completion rules, preserves distinct denominators, and produces reviewable outputs for recurring research decisions.
Technology behind this workflow 12 tools & formats
The stack follows the analytical task: prepare the data, fit or apply the method, check the result, and communicate the output.
See how the libraries connect to the wider portfolio →THE PROJECT, STEP BY STEP
See how the question became a reviewable result.
07 / DATA ENGINEERINGVISUAL WALKTHROUGH
STEP 01 / 03Standardize the inputs
Tracking recordsCompletion rulesReconciled reports
Route-by-month summariesSTEP 02 / 03Apply completion rules
Tracking recordsCompletion rulesReconciled reports
Route-by-month summariesSTEP 03 / 03Reconcile the report
Tracking recordsCompletion rulesReconciled reports
Route-by-month summariesIllustrative process diagram
Apply completion rules
Calculate complete and incomplete statuses using the right program structure and endpoint definitions.
Reconcile the report
Count participants separately from trips and measurements, then check route/month totals against source views.
THE TECHNICAL CASE
Decisions behind the analysis.
Recurring research metrics need stable definitions. A report can be technically reproducible and still misleading if it counts trips as people or treats missing endpoint data as a completed measurement.
- Unit of analysis
- Participant, trip, and measurement, aggregated by route and month only after completion rules are applied.
- Inputs
- Master tracking workbooks, existing metrics summaries, operational program definitions, and endpoint-completion fields.
- Deliverables
- Updated route-by-month metrics, annual summaries, and formatted multi-sheet Excel workbooks.
Modular orchestration
UPDATE_PILOT_METRICS.R coordinates readers, completion calculations, metric updates, and spreadsheet writing. Separating these responsibilities makes a definition change easier to isolate and inspect.
Program-specific completion
Different operational programs have different record structures. Dedicated functions calculate complete and incomplete statuses, including Island Hopper-specific handling, rather than assuming one generic rule covers every route.
Consistent aggregation
Normalized dates, route labels, and record keys support route-by-month summaries. Unique participants, trips, and usable endpoints are counted separately so repeated participation does not inflate the wrong metric.
Reviewable exports
The workbook-writing code manages multiple tabs and formatting. Reconciliation against source views gives reviewers a way to investigate totals. Original and updated script folders are versions of the same workflow, not independent projects.
METHOD IN FOCUS
Ingest → normalize keys and dates → apply completion rules → aggregate → reconcile → export
Each aggregate needs a declared unit and eligibility rule. Distinct participant counts are not generally additive across months or routes when the same person appears more than once.
EXPLORE THE METHOD
See why rows, trips, and people differ
Synthetic tracking records demonstrate distinct counts and completion filters. No participant information is used.
QUALITY OF THE EVIDENCE
Checks that change the conclusion.
- Duplicate records
Detect repeated trip keys before aggregation and distinguish valid repeated participation from duplicate rows.
- Missing identifiers
Report incomplete keys rather than grouping all missing identifiers into an apparent participant.
- Reconciliation
Check route/month totals against source views and document why distinct participant counts may not sum across groups.
- Output consistency
Verify expected tabs, column definitions, completion categories, and date boundaries in the delivered workbook.
FOR THE TECHNICAL CONVERSATION
Where I would take the discussion next.
Interview discussion can cover idempotent reruns, late-arriving records, changing completion definitions, and how to build a report that can explain the difference between two monthly snapshots.
WHAT IT DEMONSTRATES
From method to meaning.
The result is a repeatable monthly reporting workflow with reviewable Excel outputs and clear denominator definitions.