AI Sales Coaching System Spec

DockBlocks Sales Performance & Coaching Integration

Status: SPEC ONLY (research, no build)
Date: 2026-07-27
Audience: Matt (client), Alecia (PM), Engineering team

1. Executive Summary

This spec designs a real, integrated coaching email system for DockBlocks that: - Runs as a scheduled weekly job on Dokploy (not ChatGPT inbox hacks) - Reuses existing Zoho/Brevo/Apollo data connectors (no re-pulling) - Sends two weekly emails Friday 09:00 EDT: one team email + three individual emails (Jim, Mike, Mark) - Includes AI-generated coaching insights ("Big Win", "Biggest Opportunity", "Focus This Week") - Creates Gmail drafts for Matt's review before send - Extends 24h lead-status alert to Jim and Mike (currently Mark only) - Phases into the system incrementally (MVP first, full coach second)

Key principle: Data-driven coaching grounded in real Zoho/Brevo events, never invented metrics. When data is missing (e.g., 2026 sales goals), the spec says so and suggests fallbacks.

2. Data Model: Real Zoho Fields

2.1 Modules & Rep Identification

All queries use Zoho CRM v6 API (ZBASE = https://www.zohoapis.com/crm/v6).

Leads module (incoming prospects): - Lead_Owner (User name): "Jim Barber", "Mike Eastman", "Mark" (rep assignment) - Lead_Status: categorical (Attempted to Contact, Contacted, Contacted - Proposal Not Submitted, etc.) - Created_Time: when lead was added - Last_Activity_Time: last field update or engagement - Lead_Source: Apollo, Brevo, web form, etc. - Description: may contain industry/geography signals (parsed for "strengths")

Contacts module (converted to a person in the system): - Lead_Owner (inherited from Lead conversion) - Contact_Type_s: "Lead", "Customer" (conversion signal if "Customer") - Similar timestamp/source fields

Deals/Potentials module (sales pipeline): - Potential_Name: deal/quote name - Amount: potential revenue - Deal_Stage: pipeline stage (Proposal, Negotiation, etc.) - Contact_Name (linked contact) - Created_Time: when deal was created

Sales_Orders module (closed revenue): - Contact_Name (linked) - Grand_Total: actual closed revenue - Created_Time: close date - Line_Items (array): includes product_name for "top products" analysis

2.2 Rep Mapping

Apollo → Zoho Rep: - Jim Barber: Apollo seq IDs 696afce0f441e20021694776 (intro), 69752ea12bf06a000dbe68f5 (marina) - Mike Eastman: Apollo seq IDs 696b055552e625001dd4eee9 (intro), 69751314e86f8a0019642c72 (marina) - Mark: Government intro sequence (ID TBD in live system)

Brevo LEAD_OWNER_V2 field: - Integer enum: 1=Jim, 2=Mike, 3=Mark (string matches on Zoho Lead_Owner for synced contacts)

2.3 Time Windows

All metrics computed across three windows: 1. This week (W0): Mon 00:00 - Sun 23:59 EDT of report week 2. Prior week (W-1): same range, week prior 3. 4-week average (W-avg): median of prior 4 weeks 4. YTD: 2026-01-01 to report date 5. MTD: 2026-MM-01 to report date

3. Metrics: Mapping Matt's Request to Zoho Queries

3.1 Team Email Metrics

Each rep (Jim, Mike, Mark) gets a scorecard row showing:

Metric	Zoho Query	Data Type	Comment
Leads Contacted	`SELECT COUNT(id) FROM Leads WHERE Lead_Owner='<rep>' AND Created_Time IN [W0] AND Last_Activity_Time IS NOT NULL`	integer	Sum of leads assigned to rep that were touched (any field update) this week
New Potential $ (W0)	`SELECT SUM(Amount) FROM Potentials WHERE Contact_Name.Lead_Owner='<rep>' AND Created_Time IN [W0]`	float	Sum of new deal amount created this week, linked to rep-owned contacts
Booked Sales $ (W0)	`SELECT SUM(Grand_Total) FROM Sales_Orders WHERE Contact_Name.Lead_Owner='<rep>' AND Created_Time IN [W0]`	float	Revenue from closed sales orders this week
Booked Sales $ (YTD)	Same as above, Created_Time IN [2026-01-01, today]	float	Year-to-date closed revenue
Booked Sales $ (MTD)	Same as above, Created_Time IN [2026-MM-01, today]	float	Month-to-date closed revenue
Potential % to Order	(Booked Sales $ / New Potential $) * 100	%, nullable	Conversion rate: deals created to deals closed this period
Lead % to Potential	(New Potential $ / Leads_Contacted * avg_deal_size) * 100	%, nullable	Proxy: leads contacted that became opportunities (uses median SO value as deal size)

Goals (OPEN DECISION - NOT YET IN SYSTEM): - Matt promised to provide 2026 sales goals per rep (sales order targets for year) - Until provided, coach against: - Rep's historical average (e.g., "Mark averages $42K/week; you're at $38K") - Cohort median (e.g., "team average is $55K; you're at $38K") - Action: Before phase 2, request goals spreadsheet from Matt with weekly/monthly/YTD targets per rep

Prior Week + 4-Week Average: - Repeat same queries for W-1 and prior 4 weeks to show trends - Display in email as: "W0: $X | Prior: $Y | 4-wk avg: $Z"

3.2 Individual Rep Email Metrics

Each rep (Jim, Mike, Mark) receives a detailed email with their personal scorecard plus activity detail:

A. Same Scorecard as Team Email

(Booked Sales, New Potential, Lead conversion rates - segmented by week/MTD/YTD)

B. Activity & Funnel Detail

Metric	Zoho Query	Comment
Self-Gen Leads (W0)	`SELECT COUNT(id) FROM Leads WHERE Lead_Owner='<rep>' AND Lead_Source IN ['web form', 'direct', 'referral'] AND Created_Time IN [W0]`	Leads NOT from Apollo/Brevo (sales-sourced)
Apollo Leads Touched (W0)	`SELECT COUNT(id) FROM Leads WHERE Lead_Owner='<rep>' AND Lead_Source='Apollo' AND Last_Activity_Time IN [W0]`	Leads from sequences that were engaged (field updated)
Status Changes (W0)	`SELECT COUNT(id) FROM Leads WHERE Lead_Owner='<rep>' AND Lead_Status CHANGED IN [W0]`	COQL doesn't natively support "CHANGED" - use Last_Activity_Time + Last_Activity_Source as proxy
Conversions (W0)	`SELECT COUNT(id) FROM Contacts WHERE Lead_Owner='<rep>' AND Contact_Type_s='Customer' AND Created_Time IN [W0]`	Leads converted to customers this week
Activity Count (W0)	Count of Brevo email events (open/click) + Apollo engagement events where rep = Lead_Owner	Cross-system activity count (email opens + clicks + sequence responses)

C. Strengths Analysis (YTD)

Top Products (by SO count):

SELECT Line_Items.Product_Name, COUNT(Sales_Orders.id)
FROM Sales_Orders
WHERE Contact_Name.Lead_Owner='<rep>'
  AND Sales_Orders.Created_Time IN [2026-01-01, today]
GROUP BY Line_Items.Product_Name
ORDER BY COUNT(*) DESC
LIMIT 3

Top Industries (by SO count): - Parse Leads.Description field for industry keywords (regex: "boat", "marina", "dealer", "aquaculture", etc.) - Or use a Zoho custom field Industry_Category if it exists - GROUP and count by rep

Top Geographies (by SO count): - Parse Leads.Mailing_City / Mailing_State or Contacts geo fields - GROUP and count by rep

Dealer YTD (if applicable): - If Leads.Description or custom field indicates "referred by dealer" or "dealer partner", count and attribute to rep

4. Architecture: Integration with Existing Infra

4.1 Data Pipeline

┌─────────────────────┐
│  Zoho CRM API v6    │
│  (Leads, Contacts,  │
│   Deals, SOs)       │
└──────────┬──────────┘
           │
           ├─→ conversion_attribution.py (existing: 180d engagement window)
           │    └─ Reused by: rep_coaching_data.py (new)
           │
           ├─→ marketing_dashboard_data.py (existing: Apollo/Brevo stats)
           │    └─ Reused by: rep_coaching_data.py (new)
           │
           └─→ rep_coaching_data.py (NEW)
                ├─ COQL queries per rep
                ├─ Aggregate metrics (this week, prior, 4-wk avg, YTD)
                ├─ Activity detail + strengths analysis
                └─ Output: JSON coaching_data_<date>.json

┌──────────────────────┐
│  coaching_data.json  │
└──────────┬───────────┘
           │
           ├─→ coaching_email_generator.py (NEW)
                ├─ Render HTML email templates
                ├─ Format metrics (team + individual)
                └─ Create Gmail drafts (via gmail_send_helper.py)
                └─ Output: drafts in Gmail (sender: matt@dock-blocks.com)

┌──────────────────────────────┐
│  pivot_scheduler.py (LIVE)   │
│  Dokploy container job table │
└──────────┬───────────────────┘
           │
           └─ NEW JOB: "db-sales-coaching"
                {"id": "db-sales-coaching",
                 "argv": ["db_sales_coaching_email.py", "--deliver"],
                 "sched": {"hour": 9, "minute": 15, "dow": 4}}  # Fri 09:15
                 (staggered 15 min after db-client-update at 09:00)

4.2 New Scripts (3 files)

A. `scripts/reporting/rep_coaching_data.py`

Responsibility: - One-time weekly run (Friday 09:00-09:15) - Query Zoho per-rep for all metrics (W0, W-1, YTD, MTD, 4-wk avg) - Cross-reference Brevo engagement (from conversion_attribution.py pattern) - Parse strengths (top products/industries/geos) - Output single JSON file: data/reporting/rep_coaching_YYYY-MM-DD.json

Input: - ZOHO_CLIENT_ID, ZOHO_SECRET, ZOHO_REFRESH_TOKEN (env, via .env) - BREVO_API_KEY (env, via .env) - Report date (CLI arg, default: today)

Output schema:

{
  "report_date": "2026-07-27",
  "week_end": "2026-07-27",
  "reps": {
    "Jim": {
      "this_week": {
        "leads_contacted": 12,
        "new_potential_usd": 42000,
        "booked_sales_usd": 18500,
        "self_gen_leads": 3,
        "apollo_touches": 9,
        "status_changes": 5,
        "conversions": 1,
        "activity_count": 34
      },
      "prior_week": {...},
      "four_week_avg": {...},
      "ytd": {...},
      "mtd": {...},
      "goals": {
        "weekly_target": null,  // TBD: awaiting Matt's spreadsheet
        "ytd_target": null
      },
      "strengths": {
        "top_products": ["Pro Series 5", "Marina Guard", "Aquaculture Kit"],
        "top_industries": ["Marina", "Aquaculture", "Dealership"],
        "top_geos": ["FL", "TX", "CA"],
        "dealers_referred_ytd": 4
      }
    },
    "Mike": {...},
    "Mark": {...}
  },
  "meta": {
    "query_time_sec": 8.3,
    "data_freshness": "live",
    "goals_status": "pending"
  }
}

Error handling: - If Zoho API 401: force token refresh via zoho_auth.get_zoho_token(force=True) - If Brevo 429: retry with exponential backoff (existing pattern in marketing_dashboard_data.py) - If query returns 0 rows for a metric: log, do not fail; output null in JSON - If goals missing: document in meta.goals_status and coaching prompt will adapt

B. `scripts/reporting/coaching_email_generator.py`

Responsibility: - Load rep_coaching_data.json - Render two email templates (team + individual) - Generate LLM coaching insights (phase 2, see below) - Create Gmail drafts (DO NOT send) - Output: coaching_email_summary.json (for logging/tracking)

Input: - coaching_data_YYYY-MM-DD.json - Email templates (HTML in /scripts/reporting/templates/) - Claude API key (env: ANTHROPIC_API_KEY) - Matt's email (config: matt@dock-blocks.com as sender)

Output: - Gmail drafts (via gmail_send_helper.py, method: send_email(..., create_draft=True)) - To: team@dock-blocks.com (subject: "Weekly Sales Performance - Team") - To: jim@..., mike@..., mark@... (subject: "Your Weekly Sales Coaching") - From: matt@dock-blocks.com (via Gmail API's "send as" feature, requires setup) - coaching_email_summary.json: {"drafts_created": 4, "reps": [...], "status": "awaiting_review"}

C. `.claude/scripts/db_sales_coaching_email.py`

Responsibility: - Scheduled job wrapper (runs from pivot_scheduler.py) - Calls rep_coaching_data.py → coaching_email_generator.py in sequence - DMs Alecia (CEO) with status: "Sales coaching emails drafted. Review in Gmail drafts." - Logs to /data/logs/coaching_email_YYYY-MM-DD.log

Flags: - --deliver (phase 1 default: create drafts only, require manual send) - --send (phase 2+: auto-send after LLM review pass) - --dry-run (test mode: no Gmail API calls, outputs to stdout)

5. Email Templates

5.1 Team Email

Subject: "Weekly Sales Performance Summary - [Date Range]"
From: Matt
To: team@dock-blocks.com (or jim@..., mike@..., mark@... in a thread)

Structure:

[Paragraph 1: Contextual intro]
"Hi team, here's this week's sales performance snapshot. Everyone's doing solid work—see your 
benchmarks and where we can push further."

[Table 1: Team Scorecard]
┌─────────────────────────────────────────────────────────────────┐
│ Rep       │ This Week │ Prior   │ 4-wk Avg │ YTD      │ Goal    │
├─────────────────────────────────────────────────────────────────┤
│ Jim       │ $18.5K    │ $22.1K  │ $19.8K   │ $385K    │ TBD     │
│ Mike      │ $45.2K    │ $38.9K  │ $42.1K   │ $897K    │ TBD     │
│ Mark      │ $12.8K    │ $16.5K  │ $14.9K   │ $271K    │ TBD     │
└─────────────────────────────────────────────────────────────────┘

[Breakdown: New Potential $ and Lead % metrics]

[Section: Pace Analysis]
"Mike is tracking 15% ahead of 4-week average. Jim and Mark are slightly below—good 
opportunity to lean in on pipeline work this week."

[Paragraph: Positive callout per rep]
- Jim: "Nice work on the 3 self-gen leads this week—keep that momentum."
- Mike: "Strong close rate on new potentials. Keep leveraging that aquaculture network."
- Mark: "Government deals take time—your pipeline work is solid, conversions will follow."

[Paragraph: Team action item]
"Reminder: we need to finalize 2026 sales targets. Send me your goals by Friday EOD."

Design notes: - Clean, metric-focused (no emojis, no em-dashes) - Compare to prior week and 4-wk avg (not random benchmarks) - One positive observation per rep (grounded in actual data) - Delivered as HTML email

5.2 Individual Rep Email

Subject: "Your Sales Coaching: [Rep Name]"
From: Matt
To: jim@dock-blocks.com (or internal email if external addresses unavailable)

Structure:

[Greeting]
"Hi [Rep], here's your personalized sales snapshot for this week."

[Section: Your Scorecard (same metrics as team, but detailed)]
┌──────────────────────────────────────────────────┐
│ This Week:  $X booked  │ Y new potentials       │
│ Prior Week: $A booked  │ B new potentials       │
│ 4-wk Avg:   $Z booked  │ C new potentials (avg) │
└──────────────────────────────────────────────────┘

[Section: Activity This Week]
- 12 leads contacted (9 Apollo, 3 self-sourced)
- 5 lead status updates
- 1 lead converted to customer
- 34 total email/sequence activities

[Section: Your Strengths (YTD)]
- Top closing product: Pro Series 5 (5 closed)
- Strongest geography: Florida (12 closed deals)
- Top industry: Marina Operators (8 closed deals)
- Dealer referrals: 4 partnerships driving pipeline

[Section: AI Coaching Insights (Phase 2 — see below)]
- Big Win: "Your aquaculture pipeline is firing—3 deals in negotiation stage. Close at least 
  one by end of month to capture $60K+ revenue."
- Biggest Opportunity: "You're weak in self-gen leads this week (3 vs your 8-lead avg). 
  Spend 2 hours on warm outreach—your Marina contacts have high close rates."
- Focus This Week: "Push your 2 'Proposal Pending' deals to close. That's $35K sitting on 
  the fence."

[Closing]
"Let's sync if you want to dig into any of this. You're doing great work."

Design notes: - Data-driven: each insight is grounded in actual Zoho/Brevo metrics - Positive tone: frame as coaching, not criticism - Actionable: each insight has a concrete next step - Honest: if data missing (e.g., "no conversions this week"), acknowledge it rather than invent

6. LLM Coaching Prompt & Guardrails (Phase 2)

This section covers the AI insight generation; deferred to phase 2 build.

6.1 Prompt Design

Input to LLM: - Rep name, this week's metrics (booked $, new potential $, activity count, conversions) - Prior week metrics (for trend) - 4-week average (for baseline) - YTD metrics (for context) - Rep's strengths (top products, geos, industries) - Rep's weaknesses (low-performing categories) - Goals (if available) vs actuals - Last 7 days of Zoho comments/updates (if available via API)

Prompt template:

You are a sales coach reviewing [Rep Name]'s weekly performance.

ACTUAL DATA (from Zoho CRM):
- This week booked sales: $X (prior week: $Y, 4-wk avg: $Z)
- New potentials: $A (created this week)
- Leads contacted: B (Y self-sourced, Z Apollo-sourced)
- Conversions: C leads became customers
- Activity: D email/sequence touches

YTD CONTEXT:
- Total closed: $YTD
- Goal: $GOAL (or "goal not yet set")
- Top closing product: [Product] (E closed)
- Strongest geography: [Geo] (F closed deals)

ANALYZE and provide three bullet-point insights:
1. BIG WIN: One specific, genuine accomplishment from this week's data. Be concrete.
2. BIGGEST OPPORTUNITY: One specific gap or underperformance. Suggest a concrete action.
3. FOCUS THIS WEEK: One priority for next 7 days, grounded in pipeline/activity data.

RULES:
- Use only data provided above. Do not invent metrics or past performance.
- If a metric is missing (e.g., conversions = 0), acknowledge it; don't fabricate wins.
- Keep each insight to 2-3 sentences (actionable coaching, not essay).
- If goals not set, compare to rep's 4-week average instead.
- Frame positively, but honestly.

6.2 Guardrails

Pre-LLM validation: - Ensure all numbers in prompt match coaching_data.json - If any metric is null/0, explicitly note it in prompt ("conversions this week: 0") - Log the full prompt + LLM response for audit

Post-LLM validation: 1. Check that insights reference actual data points (e.g., if new potential $0, don't say "close the 3 deals") 2. Check tone: reject if it sounds like generic auto-generated copy (e.g., "great work, keep it up" without specifics) 3. Check length: reject if insights are >200 chars total (encourage concision)

Fallback: - If LLM output fails validation, use template: Big Win: [Summary of best metric this week] Biggest Opportunity: [Summary of lowest metric this week] Focus This Week: [Generic: "Review 3 oldest open potentials for close dates"]

7. Human-in-the-Loop: Gmail Drafts & Matt Approval

7.1 Draft Creation

Process: 1. coaching_email_generator.py calls gmail_send_helper.send_email(to=..., subject=..., body_html=..., draft=True) 2. Drafts appear in Matt's Gmail Drafts folder (not sent) 3. Matt reviews each email: - Team email: overall messaging, tone - Individual emails: each rep's coaching points (validates AI insights) 4. Matt edits if needed (Gmail UI) 5. Matt sends manually from Drafts (one-click)

Why drafts instead of auto-send? - Matt is the sender and voice—must approve - AI insights need human validation (especially first iterations) - Drafts give Matt flexibility to personalize per rep - Safer for phase 1 (no accidental sends to wrong people)

7.2 Gmail "Send As" Setup

Requirement: Drafts must appear to be from matt@dock-blocks.com, not the system service account.

Implementation: - Gmail API supports userId='mattgmail@dock-blocks.com' if Matt has delegated send permissions - OR: use Brevo transactional (simpler, no delegation needed) with custom "From: Matt West matt@dock-blocks.com" header - OR: Have the system create drafts in Alecia's Gmail, Alecia forwards to Matt for review

Decision: Use Brevo transactional for final send (phase 2), but phase 1 uses Gmail drafts so Matt sees exactly what will go out.

8. 24-Hour Lead-Status Alert Extension

8.1 Current State

Mark only: Webhook/automation sends Mark an alert when a lead's status is updated.

Live check: /scripts/webhooks/zoho_to_apollo_sync.py or Zoho workflow rule (location: TBD, verify with Alecia)

8.2 Extension to Jim & Mike

Change required: - Add Jim and Mike as alert recipients in the Zoho workflow rule (if workflow-based) - OR add jim@, mike@ to the webhook destination list (if webhook-based)

Spec: This is a separate 1-hour task, not part of coaching system build. Flag in roadmap as P1 follow-up.

Email template (24h alert, existing):

Subject: Lead status updated: [Lead Name]

[Lead name] status changed to: [New Status]
Last updated: [Timestamp]
Assigned to you: [Lead Owner]

Action: Log in to Zoho and follow up if needed.

9. Phased Build Plan

Phase 1: MVP (Week 1-2)

Goal: Data pipeline + draft email templates (no AI coaching)

Deliverables: - rep_coaching_data.py: COQL queries per rep, JSON output - coaching_email_generator.py: render HTML templates, create Gmail drafts (hardcoded template, no LLM) - db_sales_coaching_email.py: scheduler integration - Email templates (HTML): team + individual - Deployment: Add job to pivot_scheduler.py - No LLM yet (straight metrics + hardcoded insights)

Success criteria: - Rep_coaching_data.json populated with real metrics from Zoho - Team + individual drafts appear in Matt's Gmail Drafts Friday 09:15 - Metrics match dashboard (validate totals, counts, revenue) - Zero fabricated numbers

Effort: 12-16 hours (engineer)

Phase 2: LLM Coaching Insights (Week 3-4)

Goal: Add AI-generated "Big Win", "Biggest Opportunity", "Focus This Week"

Deliverables: - Coaching prompt (see section 6.1) - LLM validation logic (guardrails) - Update coaching_email_generator.py to call Claude API - Audit logging (prompt + response) - Test: 2-3 rounds with Matt as subject (validate insights feel personal, not generic)

Success criteria: - LLM insights reference actual Zoho data - No fabrication (zero invented numbers) - Tone is personal coaching, not template - Matt approves insights before production launch

Effort: 8-10 hours (engineer + LLM tuning with Matt)

Phase 3: Auto-Send & Lead-Status Alert (Week 4+)

Goal: Production-ready send (no drafts), extend 24h alert

Deliverables: - Replace Gmail draft creation with Brevo transactional send - Add --send flag to db_sales_coaching_email.py - Extend 24h lead-status alert to Jim+Mike (1h configuration task) - Monitoring + heartbeat (ensure job runs every Friday)

Success criteria: - Emails arrive in inbox, not drafts - From line shows "Matt West matt@dock-blocks.com" - No bounces, delivery >95% - Jim, Mike, Mark receive 24h status alerts

Effort: 6-8 hours (engineer + Zoho workflow setup)

10. Open Decisions & Risks

10.1 Data Gaps

Gap	Impact	Resolution
2026 Sales Goals	Can't compute "pace vs goal"; fallback to 4-week average	REQUEST: Matt provides goals spreadsheet (weekly/MTD/YTD targets per rep) by [date]
Goal Integration	Coaching metrics can't benchmark against targets	Phase 2: once goals received, add goals table to rep_coaching_data.json and compare
Deal Stage Taxonomy	Rep strengths analysis may over/under-count deals	VERIFY: what deal stages exist in Zoho (Proposal, Negotiation, Closed Won, etc.)? Map to expected funnel.
Product Classification	"Top products" requires parsing Line_Items or custom field	VERIFY: does Zoho Sales_Orders.Line_Items include product_name? If not, add custom field.
Industry/Geography	Strengths analysis relies on parsing Description field (fragile)	RECOMMEND: add Industry_Category custom field to Leads for structured capture.

10.2 Metrics We Can't (Yet) Compute

Metric Matt Asked For	Zoho Limitation	Workaround
"Self-gen leads"	No standard field marking Apollo vs self-sourced	Use Lead_Source field (filter where Lead_Source NOT IN ['Apollo', 'Brevo'])
"Leads touched/status-updated"	COQL doesn't have CHANGED operator	Use Last_Activity_Time + Last_Activity_Source; approximate as "any lead with activity in [W0]"
"Activity" (email-specific)	Zoho doesn't store Brevo/Apollo events natively	Join with Brevo SMTP events API (30-day retention) + Apollo engagement events
"Conversions"	Contact_Type_s = "Customer" is ~91% reliable, not 100%	Flag in report; mention false-positive/negative rate
"Days-to-close"	Requires Close_Date field in Sales_Orders (verify exists)	VERIFY: Sales_Orders.Created_Time or Close_Date field exists; calculate (Close_Date - Deal Created_Time)

10.3 Risks & Mitigation

Risk	Likelihood	Impact	Mitigation
Zoho API rate limits	Med	Job hangs on 429 response	Implement exponential backoff + retry (existing pattern in conversion_attribution.py)
Brevo 90-day event retention	High	Old activity metrics not available	Document in spec: if report runs >90d late, activity counts will be incomplete
LLM generates fake numbers (phase 2)	Med	Credibility loss with Matt	Strict prompt guardrails + validation logic + audit log all prompts/responses
Email sends to wrong reps	Low	Confusion, trust damage	Phase 1 drafts allow review; phase 3 requires validation that to_email matches rep email in config
Job doesn't fire Friday 09:15	Low	No coaching emails (silent failure)	Deadman heartbeat + CEO DM alert if job not executed by Friday 11:00
Matt's email changes	Low	Drafts sent from wrong account	Config file matt_email.json; update on change, validated before each run

11. File Locations & Dependencies

11.1 New Scripts

/Users/alecia/Documents/dev/dockblocks/
├── scripts/reporting/
│   ├── rep_coaching_data.py              (NEW)
│   ├── coaching_email_generator.py       (NEW)
│   └── templates/
│       ├── team_performance_email.html   (NEW)
│       └── individual_coaching_email.html (NEW)
├── .claude/scripts/
│   └── db_sales_coaching_email.py        (NEW, scheduler wrapper)
└── data/reporting/
    └── rep_coaching_2026-07-27.json      (OUTPUT: one per run)

11.2 Existing Reused

scripts/reporting/
├── conversion_attribution.py             (reuse: Zoho/Brevo queries pattern)
├── marketing_dashboard_data.py           (reuse: COQL + Apollo/Brevo APIs)

scripts/
├── gmail_send_helper.py                  (reuse: create Gmail drafts)
├── zoho_connector.py                     (reuse: ZohoCRMConnector class)

.claude/scripts/
├── pivot_scheduler.py                    (ADD job entry: "db-sales-coaching")

.env (must have):
├── ZOHO_CLIENT_ID
├── ZOHO_SECRET
├── ZOHO_REFRESH_TOKEN
├── BREVO_API_KEY
├── ANTHROPIC_API_KEY (phase 2)
└── GMAIL_CREDENTIALS_PATH (Gmail OAuth token)

11.3 External APIs

API	Endpoint	Used By	Rate Limit	Notes
Zoho v6	`https://www.zohoapis.com/crm/v6/`	rep_coaching_data.py	10,000/24h per org	v2 deprecated; use v6
Brevo v3	`https://api.brevo.com/v3/`	rep_coaching_data.py (activities)	300/day limit	Shared with marketing_dashboard_data.py; coordinate rate budget
Apollo v1	`https://api.apollo.io/v1/`	Reused via conversion_attribution.py	Depends on Apollo plan	Used for sequence metadata only (not high volume)
Gmail API	`https://www.googleapis.com/gmail/v1/`	coaching_email_generator.py	Unlimited (per account)	Requires OAuth; scopes: ['gmail.compose']
Claude API	`https://api.anthropic.com/`	coaching_email_generator.py (phase 2)	Model-dependent	Pay-per-token; budget ~$5-10/week per rep

12. Implementation Checklist

Pre-Build

[ ] Request goals spreadsheet from Matt: weekly/MTD/YTD targets per rep (Jim, Mike, Mark) for 2026
[ ] Verify Zoho schema: Sales_Orders.Close_Date or Created_Time for deal close? Any Industry/Product fields?
[ ] Verify current 24h alert: Is it Zoho workflow rule or webhook? Who maintains it? (Tim? George?)
[ ] Gmail setup: Does system service account have permission to create drafts visible to Matt? Test with test email.
[ ] Brevo rate budget: Confirm rep_coaching_data.py won't exceed daily limits if run + marketing_dashboard_data.py run same day.

Phase 1 Build

[ ] Create rep_coaching_data.py (COQL queries, JSON output)
[ ] Create coaching_email_generator.py (template rendering, Gmail draft creation)
[ ] Create db_sales_coaching_email.py (scheduler wrapper)
[ ] Create email HTML templates (team + individual)
[ ] Add job entry to pivot_scheduler.py
[ ] Test: Run locally with --dry-run, validate JSON output
[ ] Test: Create 1-2 Gmail drafts, validate formatting
[ ] Deploy: Push to main, deploy Dokploy container
[ ] Monitor: First Friday run, validate emails in Matt's drafts

Phase 2 Build

[ ] Design LLM coaching prompt (see 6.1)
[ ] Implement guardrails + validation logic
[ ] Integrate Claude API into coaching_email_generator.py
[ ] Test: 3 reps, 2 weeks of data, validate insights
[ ] Iterate with Matt on LLM insight quality
[ ] Audit logging: store all prompts + LLM responses

Phase 3 Build

[ ] Replace Gmail drafts with Brevo transactional sends
[ ] Add --send flag + confirmation prompt
[ ] Extend 24h lead-status alert to Jim+Mike (Zoho workflow rule update)
[ ] Monitoring: heartbeat, delivery tracking
[ ] Deploy to production

13. Success Metrics & Adoption Criteria

For Alecia/CEO to sign off on each phase:

Phase 1

Rep_coaching_data.json produced every Friday by 09:30
4 drafts (1 team, 3 individual) appear in Gmail by 09:30
Metrics match dashboard totals (validated spot-check: 2 reps, YTD revenue)
Zero data errors (no null values for callable metrics)
Job runs without manual intervention (scheduler test pass)

Phase 2

LLM insights generated and included in drafts
Matt reviews 3 weeks of coaching insights, marks >= 80% as "genuine/actionable"
No fabricated numbers in LLM output (audit log review)
Guardrail rejection rate < 5% (most LLM outputs pass validation)

Phase 3

100% delivery rate (no bounces, all emails in inboxes)
Reps report positive feedback on coaching emails (qualitative)
Matt extends deadman alert (if emails don't arrive by 10:00 Friday, CEO gets DM)
24h lead-status alert working for Jim, Mike, Mark (adoption = zero support tickets on missing alerts)

14. Example Report (Synthetic Data)

For illustrative purposes—what the system outputs:

{
  "report_date": "2026-07-27",
  "week_end": "2026-07-27",
  "reps": {
    "Jim": {
      "this_week": {
        "leads_contacted": 12,
        "new_potential_usd": 42000,
        "booked_sales_usd": 18500,
        "self_gen_leads": 3,
        "apollo_touches": 9,
        "status_changes": 5,
        "conversions": 1,
        "activity_count": 34
      },
      "prior_week": {
        "leads_contacted": 14,
        "booked_sales_usd": 22100,
        "new_potential_usd": 48000
      },
      "four_week_avg": {
        "leads_contacted": 13,
        "booked_sales_usd": 19800,
        "new_potential_usd": 44500
      },
      "ytd": {
        "booked_sales_usd": 385000,
        "conversions": 18
      },
      "goals": {
        "weekly_target": null,
        "ytd_target": null
      },
      "strengths": {
        "top_products": ["Pro Series 5", "Marina Guard", "Aquaculture Kit"],
        "top_industries": ["Marina", "Aquaculture"],
        "top_geos": ["FL", "TX"]
      },
      "ai_insights": {
        "big_win": "You closed a $18.5K aquaculture deal this week—that's your sweet spot. The 3 new Marina potentials look solid; push for at least one close by August 3.",
        "biggest_opportunity": "Self-gen leads dropped to 3 this week (vs 8-lead average). Spend 2 hours on warm Marina outreach—your close rate is 40% there.",
        "focus_this_week": "Two 'Proposal Pending' deals ($35K total) are sitting. Get commitments or close reasons by Friday."
      }
    },
    "Mike": {...},
    "Mark": {...}
  },
  "meta": {
    "query_time_sec": 8.3,
    "data_freshness": "live",
    "goals_status": "pending_from_matt"
  }
}

15. Appendix: Glossary

W0: This reporting week (Mon-Sun)
W-1: Prior week
W-avg: 4-week median/average
YTD: Year-to-date (Jan 1 - report date)
MTD: Month-to-date (MM/1 - report date)
Booked Sales $: Closed Sales_Orders.Grand_Total
New Potential $: Deals created in window (Potentials.Amount, Created_Time in window)
Lead % to Potential: Proxy conversion rate (leads contacted -> opportunities created)
Potential % to Order: Actual close rate (opportunities created -> orders closed)
Self-gen leads: Leads NOT from Apollo/Brevo (Lead_Source != 'Apollo', 'Brevo')
Apollo touches: Leads from Apollo sequences with activity (Last_Activity_Time in window)
Conversions: Leads/Contacts moved to Contact_Type_s='Customer' (or first Sales_Order created)
Dealer YTD: Count of leads referencing "dealer" or "referred by dealer" in Description field
Deadman heartbeat: Background monitor that alerts if scheduled job fails to run

16. References

Existing Code: - /Users/alecia/Documents/dev/dockblocks/scripts/reporting/conversion_attribution.py - /Users/alecia/Documents/dev/dockblocks/scripts/reporting/marketing_dashboard_data.py - /Users/alecia/Documents/dev/dockblocks/scripts/gmail_send_helper.py - /Users/alecia/Documents/dev/dockblocks/scripts/zoho_connector.py - /Users/alecia/Documents/dev/pp-v2/.claude/scripts/pivot_scheduler.py

Docs: - /Users/alecia/Documents/dev/dockblocks/docs/FIELD_MAPPINGS_COMPLETE.md - /Users/alecia/Documents/dev/dockblocks/docs/COMPREHENSIVE_TASKS.md - /Users/alecia/Documents/dev/dockblocks/reporting/dashboard/marketing-dashboard-v2.html (Section 4: Rep Scoreboard)

Email / Gmail: - Gmail API documentation: https://developers.google.com/gmail/api/guides (referenced, not linked live) - Brevo transactional docs: https://developers.brevo.com/reference/sendemail (referenced)

END OF SPEC

Approval Gate: - [ ] Matt (client): Requirements understood & agree with gaps/fallbacks? - [ ] Alecia (PM): Architecture integrates with existing infra? Scope realistic? - [ ] Engineer (TBD): Effort estimates reasonable? Any blocking unknowns?

Next Step: If approved, schedule Phase 1 sprint (2 weeks, 1 engineer FTE).