At PwC, I worked long-term on a project for the State Department of Community and Justice (DCJ)
Problem
The Department of Communities and The Department of Justice had recently merged into a single team. There had been gaps between the data and the case handling between the two topics, which lead to inefficient and costly case handling. The Department wanted to be able to identify links between children in care with parents in the justice system so that this could be incorporated into case management.
Goal
To combine disparate legacy data sources to provide greater insight and casualties into social issues affecting the state of NSW including: increasing permanency for children in out-of-home care, reducing recidivism in the prison population, and reducing homelessness. The insights were to be used by department employees, ministers of parliament and NGO partners.
Output
Coordinated multiple workshops with senior stakeholders to define the design/data requirements for the data model and dashboards.
Lead the build and implementation of all data modelling requirements in BigQuery using SQL, as well as the build of dashboards.
Devised and implemented a data matching algorithm utilizing advanced analytical capabilities (e.g. metaphone 3), enabling seamless identification of children in out-of-home care with parents in the corrections system, resulting in improved case management for children.
Collaborated on ETL architecture and design in Dataflow to streamline integration of data from legacy systems, enhancing data accessibility and usability.
The below is samples of what I created (using de-identified sample data):
Built an overview dashboard combining multiple different data sources to cater for a range of stakeholders.
A single dashboard was built and row level security and permissions were used to alter the data displayed to different audiences based on their clearance and roles.
Devised and implemented a data matching algorithm utilizing advanced analytical capabilities (e.g. metaphone 3), enabling seamless identification of children in out-of-home care with parents in the corrections system, resulting in improved case management for children.
Metaphone is a phonetic algorithm, an algorithm for indexing words by their English pronunciation so that it accounts for inconsistencies in spelling and pronunciation.
Each social issue had an in depth report. Key skills to build these report included pre-modelling the data in GCP BigQuery to be compare data on the departments specific quarterly requirements.
Data was ordered and partitioned to identify children in care that had re-occurring entries into care. This was challenging as sometimes the children entered care with different sub-providers/agencies who enter data differently so there was not direct linking ids. I developed fuzzy-matching logic across fields to correctly identify related cases without incorrectly returning results.
Developed training guides to hand over the maintenance of reports as well as for general users on how to engage with the reports and gain the most out of their usage. I conducted workshops with multiple department secretaries and senior stakeholders.