Track lead-to-MQL conversion rate by source and report to Slack using an agent skill

mkdir -p .claude/skills/conversion-report/scripts

---
name: conversion-report
description: Generates a lead-to-MQL conversion report by source from HubSpot for the last 7 days and posts it to Slack.
disable-model-invocation: true
allowed-tools: Bash(python *)
---
 
Generate a weekly conversion report by running the bundled script:
 
1. Run: `python $SKILL_DIR/scripts/report.py`
2. Review the output for any errors
3. Confirm the report was posted to Slack

#!/usr/bin/env python3
"""
Weekly Lead-to-MQL Conversion Report: HubSpot -> Slack
Queries leads and MQLs by source, calculates conversion rates, posts to Slack.
"""
import os
import sys
from datetime import datetime, timezone, timedelta
from collections import defaultdict
 
try:
    import requests
except ImportError:
    os.system("pip install requests slack_sdk -q")
    import requests
 
from slack_sdk import WebClient
 
# --- Config ---
HUBSPOT_TOKEN = os.environ.get("HUBSPOT_TOKEN")
SLACK_TOKEN = os.environ.get("SLACK_BOT_TOKEN")
SLACK_CHANNEL = os.environ.get("SLACK_CHANNEL_ID")
 
if not all([HUBSPOT_TOKEN, SLACK_TOKEN, SLACK_CHANNEL]):
    print("ERROR: Missing required env vars: HUBSPOT_TOKEN, SLACK_BOT_TOKEN, SLACK_CHANNEL_ID")
    sys.exit(1)
 
HEADERS = {"Authorization": f"Bearer {HUBSPOT_TOKEN}", "Content-Type": "application/json"}
 
# --- Date range ---
now = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0)
start = now - timedelta(days=7)
start_ms = str(int(start.timestamp() * 1000))
end_ms = str(int(now.timestamp() * 1000))
 
# --- Search helper ---
def search_contacts(extra_filters=None):
    filters = [
        {"propertyName": "createdate", "operator": "GTE", "value": start_ms},
        {"propertyName": "createdate", "operator": "LT", "value": end_ms},
    ]
    if extra_filters:
        filters.extend(extra_filters)
    all_results = []
    after = "0"
    while True:
        resp = requests.post(
            "https://api.hubapi.com/crm/v3/objects/contacts/search",
            headers=HEADERS,
            json={
                "filterGroups": [{"filters": filters}],
                "properties": ["hs_analytics_source", "lifecyclestage", "createdate"],
                "limit": 100,
                "after": after,
            },
        )
        resp.raise_for_status()
        data = resp.json()
        all_results.extend(data["results"])
        if data.get("paging", {}).get("next", {}).get("after"):
            after = data["paging"]["next"]["after"]
        else:
            break
    return all_results
 
# --- Fetch data ---
print("Fetching leads created in last 7 days...")
all_leads = search_contacts()
print(f"Found {len(all_leads)} leads")
 
print("Fetching MQLs from same period...")
mqls = search_contacts(extra_filters=[
    {"propertyName": "lifecyclestage", "operator": "EQ", "value": "marketingqualifiedlead"}
])
print(f"Found {len(mqls)} MQLs")
 
# --- Calculate conversion ---
leads_by_source = defaultdict(int)
mqls_by_source = defaultdict(int)
 
for lead in all_leads:
    src = lead["properties"].get("hs_analytics_source") or "UNKNOWN"
    leads_by_source[src] += 1
 
for mql in mqls:
    src = mql["properties"].get("hs_analytics_source") or "UNKNOWN"
    mqls_by_source[src] += 1
 
sources = sorted(
    set(leads_by_source) | set(mqls_by_source),
    key=lambda s: leads_by_source.get(s, 0),
    reverse=True,
)
 
source_lines = []
for src in sources:
    lc = leads_by_source.get(src, 0)
    mc = mqls_by_source.get(src, 0)
    rate = round(mc / lc * 100, 1) if lc > 0 else 0
    source_lines.append(f"\u2022 *{src}*: {lc} leads \u2192 {mc} MQLs ({rate}%)")
 
total_leads = len(all_leads)
total_mqls = len(mqls)
overall_rate = round(total_mqls / total_leads * 100, 1) if total_leads > 0 else 0
 
# --- Post to Slack ---
blocks = [
    {"type": "header", "text": {"type": "plain_text", "text": "\U0001F4C8 Weekly Lead-to-MQL Conversion Report"}},
    {"type": "section", "fields": [
        {"type": "mrkdwn", "text": f"*Total Leads*\n{total_leads}"},
        {"type": "mrkdwn", "text": f"*Total MQLs*\n{total_mqls}"},
        {"type": "mrkdwn", "text": f"*Overall Conversion*\n{overall_rate}%"},
    ]},
    {"type": "divider"},
    {"type": "section", "text": {"type": "mrkdwn", "text": f"*Conversion by Source*\n" + "\n".join(source_lines)}},
    {"type": "context", "elements": [
        {"type": "mrkdwn", "text": f"Last 7 days | Generated {datetime.now().strftime('%A, %B %d, %Y')}"}
    ]},
]
 
print("Posting to Slack...")
slack = WebClient(token=SLACK_TOKEN)
result = slack.chat_postMessage(
    channel=SLACK_CHANNEL, text="Weekly Lead-to-MQL Conversion Report",
    blocks=blocks, unfurl_links=False,
)
print(f"Posted report: {result['ts']}")

# Claude Code
/conversion-report
 
# Or run directly
python .claude/skills/conversion-report/scripts/report.py

# crontab -e
0 9 * * 1 cd /path/to/project && python .claude/skills/conversion-report/scripts/report.py

name: Weekly Conversion Report
on:
  schedule:
    - cron: '0 14 * * 1'
  workflow_dispatch: {}
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'
      - run: pip install requests slack_sdk
      - run: python .claude/skills/conversion-report/scripts/report.py
        env:
          HUBSPOT_TOKEN: ${{ secrets.HUBSPOT_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}