---
name: clients-patients-health-plans
description: Keep track of your client base — new clients coming in, patients you haven't seen in a while, and how your health plans are performing. Use when the user asks about client growth, lapsed patients, retention, species mix, or health plan subscriptions.
---

# Clients, Patients & Health Plans

## What this does for you

Your client base is the heart of the practice. This helps you spot growth
(new clients), risk (patients who've quietly stopped coming), and how well
your health plans are working — the things that decide next year's revenue.

## Ways to start (copy and paste one of these)

- "**How's our client base** looking this year?"
- "Find **patients we haven't seen in over a year**."
- "**Review our health plans** — are they working?"

## Try asking

- "How many new clients did we get each month this year?"
- "Which patients haven't been seen in over a year?"
- "What's our mix of dogs, cats, and other species?"
- "How many active health plan subscriptions do we have?"
- "Are our health plan members actually using what's included?"
- "How many health plans have overdue payments?"
- "Which health plans get cancelled most, and why?"

## Good to know

- A "lapsed" patient means no recorded visit in the period you choose —
  12 months is the usual starting point.
- Health plan "utilisation" shows how much of the plan's included items
  members have actually used. Very low utilisation can mean members don't
  know what's included; very high can mean the plan is underpriced.
- Deceased and archived patients are excluded from lapsed-patient lists.

---

## Instructions for the AI assistant

You are helping a veterinary clinic owner or practice manager who is **not
technical**. Data comes from the Provet MCP (`ch_run_query`, ClickHouse SQL,
database `production`).

**Core tables**
- `production.health_patients` — one row per patient: `patient_name`,
  `species_label`, `breed_label`, `is_alive`, `is_archived`,
  `last_consultation_date`, `patient_created_date`, `client_id`.
- `production.health_clients` — one row per client: `full_name`, `email`,
  `city`, `is_client_archived`.
- `production.health_consultations_metrics` — has `is_first_time_client`
  for counting genuinely new clients per period.
- `production.health_plan_subscriptions` — one row per subscription:
  `plan_name`, `is_active`, `is_ongoing`, `monthly_fee`, `utilisation_pct`,
  `has_overdue_payment`, `overdue_payment_count`, `cancel_reason_label`,
  `start_date`, `cancellation_date`, `next_renewal_date`.

**Rules that prevent wrong numbers**
1. Yes/no columns are numbers: write `is_alive = 1`, `is_archived = 0`,
   `has_overdue_payment = 1`.
2. Lapsed patients: `is_alive = 1 AND is_archived = 0 AND
   last_consultation_date < today() - INTERVAL 12 MONTH` (adjust the
   period to what the user asks).
3. New clients: count `is_first_time_client = 1` on consultations — do not
   count client records created, which includes imports.
4. If a lapsed list is very large, give the count plus the 20 most
   recently active, and offer the full list as a follow-up.
5. Names, emails and addresses are personal data — only show them when the
   user asks for a contact list, not in general statistics.

**Standard run**: if the user says "how's our client base" or "review our
health plans" with no specifics, produce the matching fixed mini-report.
Client base: new clients per month this year; lapsed patient count
(12 months); species mix; and the 20 most recently active lapsed patients.
Health plans: active subscriptions; average utilisation; plans with overdue
payments; top cancellation reasons. End by offering the full lapsed list
or a per-plan breakdown.

**Response style**: headline first ("You gained 48 new clients this year;
about 300 patients are due a reminder"), then the detail. Frame lapsed
patients as an opportunity (reminders, vaccination recalls), not a failure.