Daily leads briefing

Daily Leads Briefing

Source of truth

• Google Sheet: Firm Master Tracker
• Tabs you read: Leads, Ad Spend, Intake Funnel
• Read-only. Never write back.

What to compute (yesterday)

For yesterday (firm's local timezone), compute:

1. New leads (count of rows in Leads where Date = yesterday)
2. Cost per lead (sum of Ad Spend Spend yesterday ÷ sum of Leads Generated yesterday)
3. Booked consults (count where Status in {consult_booked, consult_attended, signed} and Booked Consult Date = yesterday)
4. Signed cases (count where Status = signed and Signed Date = yesterday)
5. Est. revenue from signed (sum of Case Value for those signed rows)
6. Best campaign by signed-case ROI (signed cases ÷ spend, ranked)

Compare each to the trailing 7-day average. Flag any metric that's >25% above or below the average.

Output: Slack post to #daily-pulse

Single message, under 200 words, this exact shape:

☀️ Daily Pulse — {date}

• New leads: {n} ({+/-}% vs 7-day avg)
• CPL: ${cpl} ({+/-}% vs 7-day avg)
• Booked consults: {n}
• Signed cases: {n} → ~${revenue}
• Best campaign: {campaign} ({signed/spend ratio})

🏆 Win of the day: {one sentence}
⚠️  Watch out: {one sentence — only if something is off, else omit}

Data as of {timestamp}. Source: Firm Master Tracker.

Live Artifact (when asked to render the dashboard, not the briefing)

Build a Live Artifact with:

• 3 KPI cards: New leads (yesterday), CPL (yesterday), Signed (yesterday)
• Line chart: leads per day for the last 14 days
• Bar chart: leads by campaign for the last 30 days
• Table: yesterday's leads with Status, Practice Area, Source
• "Data as of {timestamp}" badge in the top-right, driven by the actual fetch timestamp

Constraints (these break artifacts if violated):

• Use Recharts for charts, shadcn/ui Card for layout, lucide-react for icons
• No localStorage or sessionStorage — keep state in useState only
• No external fetch() — Claude does the fetch, you embed the data
• Tolerate missing cells (show "—", never "NaN")
• If the sheet read fails, show a red banner with the error and last-cached values

Always confirm column-to-visualization mapping with the user before finalizing. This is the single biggest accuracy lever.

Fallbacks

• If sheet returns 0 rows for yesterday → post "🟡 Pulse: Tracker is empty for {date}. Please confirm." and stop. No fake numbers.
• If sheet read fails → post "🔴 Pulse: Tracker read failed: {error message}. Last successful pull: {timestamp}." and stop.
• If current local time is after 11am when the schedule fires → prepend "⏰ Late run — was supposed to fire at 7am." Helps catch laptop-was-asleep failures.

Never do

• Don't write to the tracker sheet
• Don't post to Slack channels other than #daily-pulse without explicit user confirmation
• Don't include lead names or PII in the Slack post (counts and aggregates only)
• Don't extrapolate from less than 5 leads — say "n too low to compare"