The Challenge.
External reporting was a tangled mess—manual processes, inconsistent data, and endless back-and-forth with partners and regulators. It slowed us down and risked compliance headaches. We needed a smarter way to deliver accurate, timely reports that everyone could trust. While contract performance is important, it's nothing without accurate reporting that tells a full story.
My Role
- Collaborated with external stakeholders to define success metrics for all contracts.
- Developed two standard report schemas and queries that would service over 7 contracts.
- Wrote BigQuery scripts to pull data into connected sheets.
The Outcome
- Achieved an 98% reduction in the time needed to generate reports for external invoicing.
- Improved the time to launch a new contract by 75%.
- Grew the number of contracts by 4X by standardized offering and thus reducing negotiation times.
The Output #1: Patient Report
Report Goals:
- Provide updated patient profile information back to the client, such as a change of address.
- Indicate which patients have had either of the two contact goals completed: Health Needs Assessment (HNA) collected and Appointment Scheduled.
- Report details as they pertain to the contract goals. For the HNA, this means reporting patient responses to the assessment. For the HNA, this means reporting patient responses to the assessment.
Note: Each row should correspond to a single patient.
SQL Script:
Entity Relationship Diagram:
The Output #2: Engagement Report
Report Goals:
- Show the patient journey as it pertains to contact attempts and contract goals achieved. Even though the collection of appointment preferences isn’t an explicit contract goal, it is a key part of the patient journey. Preferences are required to book the appointment, which is an explicit goal.
- Indicate which contacts successfully engaged the patient and further, which led to the completion of the contract goals. Some contact attempts aren’t associated with any contract goals but all contract goals are associated with a contact attempt.
Note: Each row should correspond to a single contact.
SQL Script:
Entity Relationship Diagram:
.png)