Analyzing patient demographics

Geographic distribution

After establishing a foundational understanding of how Zus organizes and summarizes patient data, you may want to perform some analyses of the demographics of your patient population, including the geographic distribution of your patients' home addresses. The following query counts the number of patients by zip code using the patient and patient_address tables .

📘

Sample query available here

Age distribution

The following query calculates the number of unique patients in each age bracket based on their birth date, to help users evaluate the age distribution of their patient population.

📘

Sample query available here

Most recent phone number

For value-based care (VBC) organizations, having accurate and up-to-date contact information is critical for reaching and engaging their patient population. VBC organizations often receive contact information for their attributed patients from their payer partner, but this contact information is often outdated or incorrect, especially when working with vulnerable populations where address or phone number changes are common.

This query helps identify both first-party phone numbers (gathered directly by the healthcare provider) and third-party phone numbers (sourced from external systems), ensuring that only third-party phone numbers that differ from first-party phone numbers are displayed.

For a step-by-step breakdown:

1. PROCESSED_PHONE_NUMBERS Table:
   • Retrieves and standardizes phone numbers from the CONTACT_POINT table
   • It removes non-numeric characters and formats the numbers to a consistent structure (XXX-XXX-XXXX)
   • It also captures the timestamp associated with when the phone number was last recorded or updated, using the COALESCE function to select the most relevant date across multiple fields (encounter date, document reference date, etc.)
2. FIRST_PARTY_PHONE_NUMBERS Table:
   • Aggregates phone numbers collected directly by the healthcare organization (DATA_SOURCE IS NULL), known as first-party phone numbers
   • The query ensures deduplication by using ARRAY_UNIQUE_AGG on the phone use and formatted phone numbers
3. THIRD_PARTY_PHONE_NUMBERS Table:
   • Aggregates phone numbers sourced from third-party data (DATA_SOURCE IS NOT NULL), known as third-party phone numbers
   • It ranks the phone numbers by recency using a window function (RANK()), ordering them by the phone number's recorded date
4. Final SELECT:
   • Joins the first-party and third-party phone numbers by UPID
   • It selects only the most recent third-party phone numbers (recency_rank = 1) that are not already in the list of first-party phone numbers

📘

Sample query available here

Most recent email

Similar to identifying the most recent phone number for a patient, this query identifies the most recent email address for a patient. It is comparable in structure, with the main differences being:

1. It retrieves email addresses from the CONTACT_POINT table (system = email).
2. It standardizes email addresses by removing spaces, converting to lowercase, and removing spaces.
3. It excludes email addresses that do not abide by the email address format and that likely represent placeholder addresses, like none@, noemail@, opt-out@, unsubscribe@, donotreply@, test@, etc.

📘

Sample query available here