Dear Health New Zealand,

INTRODUCTION AND CONTEXT
For the following request can you please extract from the Health NZ database the data “as is” in a machine readable csv format.

This is to assess the quality and completeness of the data being recorded so that the public and suitable analysts can make an independent assessment and extract any useful information.

Therefore there is no need for the data entry to be 100% complete or correct, nor for Health NZ staff to be diverted from other operational tasks to make any amendments. The dataset just needs to be extracted in the way that has been requested below by a DBA with knowledge of the relevant databases.

COLUMNS OF DATASET 1
S0: Source Country – eg USA, Spain
S1: Manufacturer Name – e.g. Pfizer
S2: Product Name and Version/Code – eg there may be a nomenclature regarding initial doses and subsequent booster doses. This may be easier to extract as separate fields.
S3: Manufacturer Batch Number – as provided by the Manufacturer
S4: Number of Doses supplied – by the Manufacturer in that Batch
S5: Date of First Supply – the Minimum Date recorded for when a batch was received by Health NZ
S6: Date of Last Supply – the Maximum Date recorded for when a batch was received by Health NZ
D1: Pay Per Dose (PPD) Batch Number – the internal ID used on the Health NZ PPD system
D2: Sending Site – eg Name of the Pharmacy or Vax Centre
Sending Site Type – eg Pharmacy
D3: Number of Doses Recorded as Dispatched – as recorded in the relevant database at the time the query is run (without correction or alteration)
D4: Date of First Dispatch – the Minimum Date recorded for when a batch was dispatched to a Sending Site
D5: Date of Last Dispatch – the Maximum Date recorded for when a batch was dispatched to a Sending Site

NOTES ON DATA ITEMS
N1: Columns marked S1 to S6 should be repeated where they relate to a dispatch from that supplied batch.
N2: S5 and S6 are less important and can be excluded if they are not available in the dataset.
N3: Null or zero entries may occur and Health NZ should provide a general explanation (not row by row in the dataset) of the reasons why these occur – eg data entry issues so some batch numbers are incorrect.
N4: Totals may not align – and the Health NZ should provide a general explanation (not row by row in the dataset) of the reasons why these occur – eg “business process not followed”, “doses lost”, “doses that were not stored corrected and were therefore wasted”.
N5: No quantification is required for the reasons above – just a reasonable bullet point list of the reasons that Health NZ is aware of from existing documentation and knowledge in the minds of public servants/officials.
N6: Please provide a description of the columns provided with details of any coding scheme/rules used to generate DATASET 1 or DATASET 2
N7: Where a supplied batch is dispatched multiple times to a Sending Site across multiple months then these records in the database would be grouped into one line.
N8: Where there is no Dispatch record to the Sending Site – the record of the administration of the dose to a person can be used as a proxy for the dispatch record – with the date of administration used instead of the date of dispatch.
N9: For the avoidance of doubt, incorrect Batch IDs or other items are OK. This is an audit of the Health NZ data quality and an exercise in statistically extracting information of use for the public and professionals using techniques that have a high degree of fault tolerance.

EXAMPLE
For a Pfizer batch of 100 doses that was received all on 1/4/2021 split and sent to a Pharmacy (30) and a GP Practice (50) with 20 doses wasted during April 2021 the 2 lines in the csv file might look something like.

“USA”,“Pfizer”, “Vax1234”, “BatchABCD”, 100, 01/04/2021, 01/04/2021, 11, “Pharmacy1234”, “Pharmacy”, 30, 15/04/2021,15/04/2021
“USA”,”Pfizer”, “Vax1234”, “BatchABCD”, 100, 01/04/2021, 01/04/2021, 11, “GP4567”, “GP”, 50, 11/04/2021,26/04/2021

POSSIBLE EXCEPTION CONDITION – UNHANDLED MANY TO MANY RELATIONSHIP
N10: Where a dispatch contains items from many supplied batches the database should have separate records and this will be able to be split across two or more records for each supplied batch. If this many to many relationship is not handled in the database (that is end-to-end track-and-trace cannot be undertaken by Health NZ) then a) exclude these dispatches from the DATASET 1 above and b) please also provide a second dataset DATASET 2 with these dispatches and only include the following Columns:

DATASET 2
D1: Pay Per Dose (PPD) Batch Number – the internal ID used on the Health NZ PPD system
D2: Sending Site – eg Name of the Pharmacy or Vax Centre
Sending Site Type – eg Pharmacy
D3: Number of Doses Recorded as Dispatched – as recorded in the relevant database at the time the query is run (without correction or alteration)
D4: Date of First Dispatch – the Minimum Date recorded for when a batch was dispatched to a Sending Site
D5: Date of Last Dispatch – the Maximum Date recorded for when a batch was dispatched to a Sending Site
S0: Source Country – eg USA, Spain
S1: Manufacturer Name – e.g. Pfizer
S2: Product Name and Version/Code – eg there may be a nomenclature regarding initial doses and subsequent booster doses. This may be easier to extract as separate fields.
S3: Manufacturer Batch Number – as provided by the Manufacturer
S4: Number of Doses supplied – by the Manufacturer in that Batch
S5: Date of First Supply – the Minimum Date recorded for when a batch was received by Health NZ
S6: Date of Last Supply – the Maximum Date recorded for when a batch was received by Health NZ

N11: Where the affected dispatches are listed and duplicated across rows that identify the possible Manufacturer supplied batches that they could have come from. My suggestion is that the possible supplied batches for affected dispatched doses can be narrowed by a DBA using date ranges.

N12: Please name and state whether you have returned DATASET 1, DATASET 2 or both.

REASONABLENESS OF REQUEST
This request removes the need for any cleaning or completing of the data by Health NZ and will only require the services of a DBA/analyst to aggregate and group data that has been collected during the normal course of Health NZ’s business.

This OIA does not require Health NZ to make any judgement about “fit for purpose” as this is an audit of what actually has been recorded in the database, and the judgement of what is useful will be able to be made by the receivers of the OIA’s dataset.

No patient privacy issues are involved so there is no need to suppress counts less than a certain number.

No commercial issues are relevant as this OIA deals purely with dispatch not the number of doses administered – and there may be many reasons for payment to differ substantially from dispatch.

In order to establish data integrity and auditability (eg for wastage or quality control between supply and dispatch and other scenarios of interest), it is necessary to track by the relevant IDs supplied by the manufacturer and the PPD system maintained by the Health NZ.

This type of dataset request Health NZ has successfully fulfilled for past OIAs.

We have previously established that I am a NZ citizen, resident and taxpayer and entitled to make this OIA request.

WAYS OF WORKING
To make the most efficient use of time please Consult early rather than waiting until the end of the 20 working day statutory period. An intention to not fulfil or refuse parts of this OIA should be Consulted on as soon as practical and in good faith.

Yours faithfully,

Chris Johnston