Email Funnel Metrics

Tracks the full email outreach funnel from send to meeting booked. All metrics are computed at query time via SQL aggregations over synced SmartLead data.

---

Data Sources

Table	Purpose
jeff_sl_lead_messages	Email events (SENT, REPLY) with timestamps
jeff_sl_leads	Lead records with status and category
jeff_sl_campaigns	Campaign metadata (series, sub_series, client)
jeff_sl_categories	SmartLead category labels (Positive Reply, Meeting Booked, etc.)
jeff_sl_email_accounts	Sender email account info
jeff_meetings	Meeting CRM records with stage tracking and agency_id (credit owner)
orange_schema.agencies	Internal agency table; bridges Smartlead client_id to CRM credit via smartlead_client_id
jeff_posthog_events	Audit page view and click events
jeff_job_rows	Links audits to jobs and emails
jeff_jobs	Links jobs to SmartLead campaigns

---

Date Modes

The date mode controls how events are bucketed into time periods and which leads are included in the date range. Everything else (metric columns, joins, filters) stays the same.

Activity Mode (default)

Each event is bucketed by its own timestamp. The date range filters on m.time (the event timestamp).

Use when: "What happened in this time window?" — operational dashboards, daily monitoring.

date_trunc('{granularity}', m.time)::date AS date

WHERE m.time >= :date_from
  AND m.time < :date_to + INTERVAL '1 day'

Cohort Mode

ALL events for a lead are bucketed by that lead's email_1 send date. The date range filters on COALESCE(sent_e1.time, l.created_at) — so it selects leads whose first email was sent in the date range, then pulls in all their events regardless of when those events occurred.

Use when: "For leads we emailed in this window, what were the outcomes?" — campaign effectiveness, conversion analysis.

-- Extra self-join to look up each lead's email_1 send time
LEFT JOIN jeff_sl_lead_messages sent_e1
    ON sent_e1.lead_id = m.lead_id
    AND sent_e1.campaign_id = m.campaign_id
    AND sent_e1.sequence_number = 1
    AND sent_e1.type = 'SENT'

date_trunc('{granularity}', COALESCE(sent_e1.time, l.created_at))::date AS date

WHERE COALESCE(sent_e1.time, l.created_at) >= :date_from
  AND COALESCE(sent_e1.time, l.created_at) < :date_to + INTERVAL '1 day'

What changes between the two modes

Component	Activity	Cohort
Date expression (SELECT + GROUP BY)	m.time	COALESCE(sent_e1.time, l.created_at)
WHERE date filter	Filters on event timestamp	Filters on email_1 send date
Extra JOIN	None	Self-join to jeff_sl_lead_messages to find email_1 timestamp
Metric columns	Same	Same
Other JOINs	Same	Same
Filters	Same	Same

Example

A lead receives email_1 on Jan 3, then replies on Jan 15, then books a meeting on Jan 22. Querying January with weekly granularity:

Event	Activity Mode Bucket	Cohort Mode Bucket
email_1 sent	Week of Jan 1	Week of Jan 1
reply	Week of Jan 15	Week of Jan 1
meeting booked	Week of Jan 22	Week of Jan 1

In activity mode, the events spread across 3 weeks. In cohort mode, all 3 events collapse into the week the lead was first emailed.

---

Dimensions (group by)

Dimension	Source	Description
series	campaigns.series	Campaign series number
sub_series	campaigns.sub_series	Campaign sub-series string
client_id	campaigns.client_id	SmartLead client
agency_id	agencies.id	Agency that owns the credit. SL-side metrics resolve via agencies.smartlead_client_id = campaigns.client_id; CRM metrics read jeff_meetings.agency_id directly. Agencies with NULL smartlead_client_id show zero SL-side counts and real CRM counts.
domain	email_accounts.domain_name	Sending email domain
provider	email_accounts.type	SMTP, GMAIL, or OUTLOOK
sequence_number	lead_messages.sequence_number	Position in email sequence
agency_name	agencies.name (via PostHog join)	Agency name (PostHog-only queries)
marketplace	PostHog event properties	Marketplace code, e.g., US

CRM metrics (crm_positive_replies, crm_meetings_booked) only decompose along client_id and agency_id. When grouped by series, sub_series, domain, provider, or sequence_number, the CRM columns are NULL — those dimensions describe the source message, which is not the credit unit.

---

Filters

Filter	Description
client_id	Restrict to specific Smartlead client IDs. For CRM metrics, resolves via agencies.smartlead_client_id — the count includes meetings credited to any agency linked to the requested client(s).
campaign_id	Restrict to specific campaign IDs
series	Restrict to specific series numbers
sub_series	Restrict to specific sub-series strings
agency_id	Restrict to specific agency IDs. For SL-side metrics, joins agencies.smartlead_client_id = campaigns.client_id. For CRM metrics, filters jeff_meetings.agency_id directly.
agency_name	Agency name filter (PostHog-only queries)

---

Count Metrics (21)

All SQL snippets below show the metric column logic. The date bucketing (activity vs cohort) and WHERE clause are determined by the date mode described above.

Delivery Metrics

Metric	SQL Logic	Description
prospects_reached	COUNT(DISTINCT campaign_lead_map_id) WHERE type='SENT' AND sequence_number=1	Lead-campaign participations that received email 1. Same lead in two campaigns counts twice. Denominator for all rate metrics.
unique_prospects_reached	COUNT(DISTINCT lead_id) WHERE type='SENT' AND sequence_number=1	Distinct humans that received email 1, deduped on lead_id only. Same lead in two campaigns counts once. Non-additive across group_by dimensions.
total_emails_sent	COUNT(*) WHERE type='SENT'	Total emails across all sequence steps
email_1_sent	COUNT(*) WHERE type='SENT' AND sequence_number=1	First email sends
email_2_sent	COUNT(*) WHERE type='SENT' AND sequence_number=2	Second email sends
email_3_sent	COUNT(*) WHERE type='SENT' AND sequence_number=3	Third email sends

Reply Metrics

Metric	SQL Logic	Description
total_replies	COUNT(DISTINCT lead_id) WHERE type='REPLY'	Leads who replied (any type)
replies_email_1	COUNT(DISTINCT lead_id) WHERE type='REPLY' AND sequence_number=1	Replies to email 1
replies_email_2	COUNT(DISTINCT lead_id) WHERE type='REPLY' AND sequence_number=2	Replies to email 2
replies_email_3	COUNT(DISTINCT lead_id) WHERE type='REPLY' AND sequence_number=3	Replies to email 3

SmartLead Category Metrics (rep-validated)

These come from SmartLead's lead categorization system. A sales rep manually assigns these categories after reviewing lead responses.

Metric	Category Match	Description
sl_positive_replies	category.name = 'Positive Reply'	Rep-confirmed genuine interest
sl_meetings_booked	category.name = 'Meeting Booked'	Prospect confirmed a meeting slot
total_bounces	category.name = 'Sender Originated Bounce'	Delivery failures
ooo_replies	category.name = 'Out Of Office'	Auto-replies, not real engagement

CRM Metrics (from Meeting CRM)

CRM metrics attribute meetings by agency ownership (jeff_meetings.agency_id), not by the source campaign's Smartlead client. When the rep publishes a meeting via POST /meetings/{id}/publish, the chosen agency owns the credit — even if it differs from the agency that owns the campaign that produced the lead. Meetings with NULL agency_id (unpublished) are excluded.

When filtered by client_id, the lookup resolves agencies.smartlead_client_id → mtg.agency_id. This makes the count match what the CRM UI shows for that client/week (whereas the older campaign-source attribution would undercount whenever a meeting was credited to a different agency than the campaign owner).

Bucketing in both activity and cohort date modes is by the meeting event date (creation date for crm_positive_replies, first BOOKED+ event date for crm_meetings_booked) — the source email's cohort window does not apply because the credited agency may have nothing to do with that source campaign.

Metric	SQL Logic	Description
crm_positive_replies	COUNT(DISTINCT mtg.id) joined jeff_meetings → orange_schema.agencies ON agencies.id = mtg.agency_id	Meetings credited to an agency in the date window. Bucketed by mtg.created_at.
crm_meetings_booked	COUNT(DISTINCT mtg.id) over the same join, restricted to meetings with a stage event reaching BOOKED+ (BOOKED, NO_SHOW, PITCHED, DID_NOT_PITCH, DID_NOT_CLOSE, CLOSED)	Meetings that have ever reached BOOKED or a later stage. Monotonic — backward transitions don't drop the count. Bucketed by the first such event's date.

Other

Metric	SQL Logic	Description
blocked_leads	COUNT(DISTINCT lead_id) WHERE lead.status = 'BLOCKED'	SmartLead prevented sending

PostHog Engagement Metrics

Track audit page interactions. Joined via: jeff_posthog_events → jeff_job_rows (audit_id) → jeff_jobs (job_id) → jeff_sl_leads (campaign_id + email).

Metric	Event Filter	Description
audit_views	event = 'audit_viewed'	Distinct leads who viewed audit page
unique_audit_visitors	DISTINCT distinct_id WHERE event = 'audit_viewed'	Unique browser sessions on audit pages
cta_clicks	event = '$autocapture' AND el_text ILIKE '%get started%' OR '%başlayın%'	CTA button clicks on audit page
amazon_clicks	event = '$autocapture' AND el_text ILIKE '%view on amazon%'	"View on Amazon" link clicks

---

Rate Metrics (10)

All rates use prospects_reached as denominator unless noted otherwise. Rates are computed in Python after the SQL query returns (not in SQL).

Caller note: rate metrics depend on the underlying counts being selected. When requesting a rate via smartlead_query_funnel, also pass its numerator and denominator count metrics — missing counts silently produce 0.0.

Metric	Formula	Description
reply_rate	total_replies / prospects_reached	Raw reply rate (includes OOO, negative)
sl_positive_reply_rate	sl_positive_replies / prospects_reached	Rep-confirmed positive reply rate
sl_booking_rate	sl_meetings_booked / prospects_reached	End-to-end conversion from email to meeting
sl_slot_to_booking_rate	sl_meetings_booked / sl_positive_replies	Conversion from positive reply to booked meeting
reply_to_crm_positive_rate	crm_positive_replies / total_replies	Reply-quality rate — fraction of replies that become CRM-positive. Caveat: numerator is agency-attributed, denominator is campaign-attributed (see paragraph below).
bounce_rate	total_bounces / prospects_reached	Delivery failure rate
blocked_rate	blocked_leads / prospects_reached	Rate SmartLead refused to send
audit_view_rate	audit_views / prospects_reached	Email-to-page conversion
cta_click_rate	cta_clicks / prospects_reached	End-to-end email to CTA click
audit_cta_rate	cta_clicks / audit_views	On-page CTA conversion rate

crm_positive_reply_rate and crm_booking_rate were removed in EQU-290. CRM counts attribute by agency credit while prospects_reached attributes by campaign source — a meeting credited to agency X may have come from agency Y's campaign, so X's denominator doesn't include the email that produced it. The ratio is per-agency-meaningless under the new attribution. Request crm_positive_replies / crm_meetings_booked and prospects_reached separately if you need to compute a ratio with that caveat in mind.

reply_to_crm_positive_rate (kept) inherits the same numerator-vs-denominator attribution split: numerator is agency-attributed CRM positives, denominator is replies on the source client's campaigns. Treat it as a directional signal rather than a precise per-client conversion.

Rate computation code

def safe_div(num, den):
    return round(num / den, 4) if den else 0.0

reached = row["prospects_reached"]

row["reply_rate"]              = safe_div(row["total_replies"], reached)
row["sl_positive_reply_rate"]  = safe_div(row["sl_positive_replies"], reached)
row["sl_booking_rate"]         = safe_div(row["sl_meetings_booked"], reached)
row["sl_slot_to_booking_rate"] = safe_div(row["sl_meetings_booked"], row["sl_positive_replies"])
row["reply_to_crm_positive_rate"] = safe_div(row["crm_positive_replies"], row["total_replies"])
row["bounce_rate"]             = safe_div(row["total_bounces"], reached)
row["blocked_rate"]            = safe_div(row["blocked_leads"], reached)
row["audit_view_rate"]         = safe_div(row["audit_views"], reached)
row["cta_click_rate"]          = safe_div(row["cta_clicks"], reached)
row["audit_cta_rate"]          = safe_div(row["cta_clicks"], row["audit_views"])

---

Full SQL Query

The complete query with all metric columns. The date expression and WHERE clause shown here are for activity mode — see Date Modes above for how these change in cohort mode.

Full funnel query (activity mode)

SELECT
    date_trunc('{granularity}', m.time)::date AS date,

    -- Delivery
    COUNT(DISTINCT CASE WHEN m.type = 'SENT' AND m.sequence_number = 1
          THEN l.id END) AS prospects_reached,
    COUNT(*) FILTER (WHERE m.type = 'SENT') AS total_emails_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 1) AS email_1_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 2) AS email_2_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 3) AS email_3_sent,

    -- Replies
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' THEN l.id END) AS total_replies,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.sequence_number = 1
          THEN l.id END) AS replies_email_1,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.sequence_number = 2
          THEN l.id END) AS replies_email_2,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.sequence_number = 3
          THEN l.id END) AS replies_email_3,

    -- SmartLead categories (rep-validated)
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Positive Reply')
          THEN l.id END) AS sl_positive_replies,
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Meeting Booked')
          THEN l.id END) AS sl_meetings_booked,
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Sender Originated Bounce')
          THEN l.id END) AS total_bounces,
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Out Of Office')
          THEN l.id END) AS ooo_replies,

    -- (CRM metrics are computed in a separate query — see "CRM CTE" below)

    -- Blocked
    COUNT(DISTINCT CASE WHEN l.status = 'BLOCKED' THEN l.id END) AS blocked_leads,

    -- PostHog engagement
    COUNT(DISTINCT CASE WHEN ph.event = 'audit_viewed' THEN l.id END) AS audit_views,
    COUNT(DISTINCT CASE WHEN ph.event = 'audit_viewed'
          THEN ph.distinct_id END) AS unique_audit_visitors,
    COUNT(DISTINCT CASE WHEN ph.event = '$autocapture' AND (
          ph.properties->>'$el_text' ILIKE '%get started%' OR
          ph.properties->>'$el_text' ILIKE '%başlayın%')
          THEN l.id END) AS cta_clicks,
    COUNT(DISTINCT CASE WHEN ph.event = '$autocapture' AND
          ph.properties->>'$el_text' ILIKE '%view on amazon%'
          THEN l.id END) AS amazon_clicks

FROM {schema}.jeff_sl_lead_messages m
JOIN {schema}.jeff_sl_leads l
    ON l.id = m.lead_id AND l.campaign_id = m.campaign_id
JOIN {schema}.jeff_sl_campaigns c
    ON c.id = m.campaign_id
LEFT JOIN {schema}.jeff_sl_categories cat
    ON cat.id = l.lead_category_id
LEFT JOIN {schema}.jeff_job_rows ph_jr
    ON ph_jr.prospect_email = l.email
LEFT JOIN {schema}.jeff_jobs ph_j
    ON ph_j.id = ph_jr.job_id AND ph_j.sl_campaign_id = c.id
LEFT JOIN {schema}.jeff_posthog_events ph
    ON ph.audit_id = ph_jr.audit_id AND ph_j.id IS NOT NULL
WHERE m.time >= :date_from
    AND m.time < :date_to + INTERVAL '1 day'
    AND c.parent_campaign_id IS NULL  -- exclude sub-campaigns
GROUP BY 1
ORDER BY 1

CRM CTE (agency-attributed, runs in a separate SQL pass)

CRM metrics are computed in their own queries because (a) attribution is by jeff_meetings.agency_id, not by the source campaign's client_id, and (b) bucketing is by the meeting event date in both activity and cohort modes. Results are merged into the main funnel rows by (date, client_id|agency_id) keys after both queries return.

-- crm_positive_replies (bucketed by mtg.created_at)
SELECT
    date_trunc('{granularity}', mtg.created_at)::date AS date,
    a.smartlead_client_id AS client_id,        -- present when group_by includes client_id
    a.id AS agency_id,                         -- present when group_by includes agency_id
    COUNT(DISTINCT mtg.id) AS crm_positive_replies
FROM {schema}.jeff_meetings mtg
JOIN {schema}.agencies a ON a.id = mtg.agency_id     -- excludes NULL agency_id
WHERE mtg.created_at >= :date_from
  AND mtg.created_at <  :date_to + INTERVAL '1 day'
  -- client_id filter resolves through the agency mapping:
  AND a.smartlead_client_id = ANY(:filter_client_ids)   -- when client_id filter set
  AND a.id                  = ANY(:filter_agency_ids)   -- when agency_id filter set
GROUP BY 1, 2, 3;

-- crm_meetings_booked (bucketed by first stage event reaching BOOKED+)
WITH first_booked AS (
    SELECT DISTINCT ON (meeting_id)
           meeting_id, created_at AS booked_at
    FROM {schema}.jeff_meeting_stage_events
    WHERE new_stage IN ('BOOKED','NO_SHOW','PITCHED','DID_NOT_PITCH','DID_NOT_CLOSE','CLOSED')
    ORDER BY meeting_id, created_at
)
SELECT
    date_trunc('{granularity}', fb.booked_at)::date AS date,
    a.smartlead_client_id AS client_id,
    a.id AS agency_id,
    COUNT(DISTINCT mtg.id) AS crm_meetings_booked
FROM first_booked fb
JOIN {schema}.jeff_meetings mtg ON mtg.id = fb.meeting_id
JOIN {schema}.agencies     a   ON a.id   = mtg.agency_id
WHERE fb.booked_at >= :date_from
  AND fb.booked_at <  :date_to + INTERVAL '1 day'
GROUP BY 1, 2, 3;

If group_by contains a CRM-incompatible dimension (series, sub_series, domain, provider, sequence_number), the CRM CTE is skipped entirely and the CRM columns remain NULL on every row.

PostHog-only query (no SmartLead join)

When only PostHog metrics are requested (audit_views, unique_audit_visitors, cta_clicks, amazon_clicks, and their rates), a simpler query runs directly against PostHog events without the SmartLead join chain:

SELECT
    date_trunc('{granularity}', pe.timestamp)::date AS date,
    COUNT(*) FILTER (WHERE pe.event = 'audit_viewed') AS audit_views,
    COUNT(DISTINCT pe.distinct_id) FILTER (WHERE pe.event = 'audit_viewed')
        AS unique_audit_visitors,
    COUNT(*) FILTER (WHERE pe.event = '$autocapture' AND (
        pe.properties->>'$el_text' ILIKE '%get started%' OR
        pe.properties->>'$el_text' ILIKE '%başlayın%'))
        AS cta_clicks,
    COUNT(*) FILTER (WHERE pe.event = '$autocapture' AND
        pe.properties->>'$el_text' ILIKE '%view on amazon%')
        AS amazon_clicks
FROM {schema}.jeff_posthog_events pe
JOIN {schema}.jeff_audits ja ON ja.id = pe.audit_id
WHERE pe.timestamp >= :date_from
    AND pe.timestamp < :date_to + INTERVAL '1 day'
GROUP BY 1
ORDER BY 1

This shows ALL audit page activity regardless of campaign linkage.