lead

The lead semantic model is the primary analytical resource for leads. Each record represents a single lead with its full lifecycle — from creation through status changes, conversion, and closure.

Use dimensions to filter and group results, measures for aggregations, and segments to apply common pre-built filters.

Dimensions

Marketing Properties

Name	Type	Description
source	string	UTM source parameter
medium	string	UTM medium parameter
campaign	string	UTM campaign parameter
term	string	UTM term parameter
content	string	UTM content parameter
marketing_source_name	string	Marketing source display name
marketing_source_id	string	Marketing source ID

Lead Properties

Name	Type	Description
lead_source	string	Top-level lead source: Web, Phone, Fax, InPerson, EMR, Unknown
origin	string	Lead origin: External, Manual, LeadPage
lead_type	string	Lead type: MQL (Marketing Qualified), SQL (Sales Qualified)
engagement_type	string	NewPatient or ReturningPatient
is_using_insurance	boolean	Whether the lead is using insurance
priority	string	Lead priority level: Urgent, High, Medium, Low

Lead Task Status

Name	Type	Description
task_status_name	string	Current task status display name
task_status_id	string	Current task status ID
service_name	string	Service display name
service_id	string	Service ID
closed_lost_reason_name	string	Closed-lost reason display name
closed_lost_reason_id	string	Closed-lost reason ID
closed_lost_note	string	Free-text note on closed-lost
due_date	time	Task due date
next_follow_up_date	time	Next scheduled follow-up date

Timestamps

Name	Type	Description
created_at	time	When the lead was created
updated_at	time	When the lead was last updated
opened_at	time	When the lead task was opened
started_at	time	When the lead task was started (first touch)
completed_at	time	When the lead task was completed (closed won)
closed_at	time	When the lead task was closed (closed lost)
first_encounter_date	time	Date of the patient's first encounter
deleted_at	time	When the lead was soft-deleted
transferred_at	time	When the lead was transferred

Identifiers

Name	Type	Description
id	string	Lead ID (primary key)
provider_organization_id	string	Organization ID
owner_id	string	Assigned owner (user) ID
location_id	string	Location ID
location_name	string	Location display name
payer_id	string	Payer ID
benefit_id	string	Benefit ID
patient_id	string	Matched patient ID
patient_case_id	string	Patient case ID
duplicate_of_id	string	ID of the lead this is a duplicate of

External EMR IDs

Name	Type	Description
external_location_id	string	Location ID in external EMR
external_payer_id	string	Payer ID in external EMR
external_patient_id	string	Patient ID in external EMR
external_patient_case_id	string	Patient case ID in external EMR
external_appointment_id	string	Appointment ID in external EMR

Measures

Counts

Name	Type	Description
count	count	Total number of leads
current_in_progress_count	count	Leads currently in progress
current_open_count	count	Leads currently open
started_count	count	Leads that have been started (first touch)
closed_won_count	count	Leads closed as won
closed_lost_count	count	Leads closed as lost
soonest_appointment_created_count	count	Leads that resulted in a scheduled appointment
first_visit_count	count	Leads where the patient had a first encounter
soonest_occurred_appointment_count	count	Leads with an appointment that should have occurred relative to current time

Percentages

Name	Type	Format	Description
percentage_closed_won	number	percent	Percentage of leads closed as won
percentage_closed_lost	number	percent	Percentage of leads closed as lost
percentage_appointment_scheduled	number	percent	Percentage of leads with an appointment scheduled
percentage_appointment_attended	number	percent	Percentage of appointments attended

Time-to Metrics

Name	Type	Description
avg_time_to_first_touch	number	Average seconds from open to first touch
avg_time_to_closed_won	number	Average seconds from open to closed won
avg_time_to_closed_lost	number	Average seconds from open to closed lost
avg_time_to_first_visit	number	Average seconds from open to first encounter
time_to_first_touch_total	sum	Sum of seconds from open to first touch (all leads)
time_to_closed_won_total	sum	Sum of seconds from open to closed won (all leads)
time_to_closed_lost_total	sum	Sum of seconds from open to closed lost (all leads)
time_to_first_visit_total	sum	Sum of seconds from open to first encounter (all leads)

Segments

Name	Description
not_deleted	Excludes soft-deleted leads (deleted_at IS NULL)
not_duplicate	Excludes duplicate leads (duplicate_of_id IS NULL)
not_transferred	Excludes transferred leads (transferred_at IS NULL)
sql_leads_only	Only SQL-type leads (lead_type = 'SQL')
active_sql_leads	Not deleted, not duplicate, not transferred, and SQL-type only

Example Queries

Conversion rate by source:

SELECT
  lead.source,
  MEASURE(lead.count),
  MEASURE(lead.closed_won_count),
  MEASURE(lead.percentage_closed_won)
FROM lead
WHERE lead.created_at >= '2024-01-01'
  AND lead.active_sql_leads = true -- segment filter
GROUP BY 1;

Average time-to-first-touch by location:

SELECT
  lead.location_id,
  lead.location_name,
  MEASURE(lead.avg_time_to_first_touch),
  MEASURE(lead.count)
FROM lead
WHERE lead.opened_at >= '2024-01-01'
  AND lead.active_sql_leads = true -- segment filter
GROUP BY 1, 2;