Data processing details

Patient file processing

These actions are applied to the Patient Export CSV.

Patient sheet

Every patient in the Patient Export CSV is merged into the Patient Sheet.

Matches (based on the patient ID) are updated with any new data.

Sales file processing

All sales

These actions are applied to the Patient Export CSV.

1. Link sales to rows in the Patient Sheet based on ID.
2. Filter sale rows according to the following criteria:
   • Location is not The Health League.
   • Income Category is not Optical.
   • Item is not No Show.
   • Item is not Pickup.
   • Item is not Frame Adjustment or Repair.
   • Item is not New Glasses Selection and Ordering.
3. Collect multiple rows into single invoices:
   • Group by the invoice prefix (e.g. 72741-C01 + 72741-P01 → 72741), and sum sale amounts together.
   • This ensures we don’t double-count sales that are paid partially from MSP / by insurer / by patient / etc.
4. Sort all invoices by date + invoice number.

After this processing we are left with the All Sales Sheet.

New patient sales

Taking the All Sales Sheet, we iterate through the invoices, finding new patient appointments and adding up all subsequent sales:

• An invoice indicates a new patient appointment if the invoice Income Category is Initial Assessments.
• All invoices for that patient within the next 30 days are counted as subsequent sales for that new patient.

When we have a list of these invoices (one per row), we store them in the New Patient Sales Sheet.

Doctor sales

Once again taking the All Sales Sheet, we iterate through the invoices, finding doctor appointments and adding up all subsequent sales:

• An invoice indicates a doctor appointment if at least one of the following is true:
  • Item is RX re-check.
  • Income Category is Initial Assessments.
  • Income Category is Subsequent NeuroVisual Optometry.
  • Income Category is VT Progress Assessments.
• All invoices for that patient within the next 30 days are counted as subsequent sales for that new patient, except for sales that are ocular health sales (the Income Category is Ocular Health & CL Assessments).
• We then sum up the invoiced value for all those subsequent sales.
• An hourly rate is calculated for those sales by dividing the total invoiced amount by the amount of time associated with the corresponding doctor appointment:
  • If the Item is RX re-check, the time is 10 minutes.
  • If the Income Category is Initial Assessments, the time is 40 minutes.
  • If the Income Category is Subsequent NeuroVisual Optometry, the time is 20 minutes.
  • If the Income Category is VT Progress Assessments, the time is 20 minutes.

The results are stored in the Doctor Sales Sheet.

Referred patients

Starting with the New Patient Sales Sheet, we group the invoice rows by patient ID, summing the subsequent sales and ensuring we include the referring professional and referral source columns from the patient data. This effectively groups the sales such that there is one row of data per new patient, and makes it easy to track who referred that patient. We then store this data in the Referred Patients Sheet.

Referred glasses sales

Once again taking the New Patient Sales Sheet, we filter the invoices to only include those that are OD referrals and which are either lens or frame sales:

• An invoice is an OD referral if at least one of the following is true:
  • Referral Source includes the exact text Optometrist or OD.
  • Referring Professional includes the exact text OD.
• An invoice is a lens or frame sale if the Income Category is either Lenses or Frames.

Having filtered the list of invoices, we then group the data, exactly as when generating the Referred Patients Sheet. We split by location (Victoria and Nanaimo) and store these values in the Referred Glasses Sales Sheets.

Initial VT referrals

Once more taking the New Patient Sales Sheet, we filter the invoices such that we are left with OD referrals which are initial VT sales:

• An invoice is an OD referral if at least one of the following is true:
  • Referral Source includes the exact text Optometrist or OD.
  • Referring Professional includes the exact text OD.
• An invoice is an initial VT sale if the Item starts with the exact text Initial VT.

As with the other referral reports, we then group the data by patient ID, as when generating the Referred Patients Sheet. We again split by location (Victoria and Nanaimo) and store these values in the Referred Initial VT Sales Sheets.