Send a weekly Slack report on HubSpot sequence performance using an agent skill

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

---
name: sequence-report
description: Generates a weekly performance report for HubSpot sequences (open rate, reply rate, meeting rate) and posts it to Slack.
disable-model-invocation: true
allowed-tools: Bash(python *)
---
 
Generate a weekly sequence performance 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 Sequence Performance Report: HubSpot -> Slack
Fetches sequence enrollment data, calculates open/reply/meeting rates, posts to Slack.
"""
import os
import sys
import time
from datetime import datetime, timezone, timedelta
 
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))
 
# --- Fetch sequences ---
print("Fetching sequences...")
seq_resp = requests.get(
    "https://api.hubapi.com/crm/v3/objects/sequences",
    headers=HEADERS,
    params={"limit": 100, "properties": "hs_sequence_name"},
)
seq_resp.raise_for_status()
sequences = seq_resp.json()["results"]
print(f"Found {len(sequences)} sequences")
 
# --- Fetch enrollments per sequence ---
def get_enrollments(seq_id):
    results = []
    after = "0"
    while True:
        resp = requests.post(
            "https://api.hubapi.com/crm/v3/objects/sequence_enrollments/search",
            headers=HEADERS,
            json={
                "filterGroups": [{"filters": [
                    {"propertyName": "hs_sequence_id", "operator": "EQ", "value": str(seq_id)},
                    {"propertyName": "hs_enrollment_start_date", "operator": "GTE", "value": start_ms},
                ]}],
                "properties": [
                    "hs_sequence_id", "hs_enrollment_state",
                    "hs_was_email_opened", "hs_was_email_replied",
                    "hs_was_meeting_booked",
                ],
                "limit": 100,
                "after": after,
            },
        )
        resp.raise_for_status()
        data = resp.json()
        results.extend(data["results"])
        if data.get("paging", {}).get("next", {}).get("after"):
            after = data["paging"]["next"]["after"]
        else:
            break
    return results
 
report = []
for seq in sequences:
    seq_name = seq["properties"].get("hs_sequence_name", f"Sequence {seq['id']}")
    enrollments = get_enrollments(seq["id"])
    time.sleep(0.2)  # Rate limit: 5 req/sec
 
    if not enrollments:
        continue
 
    enrolled = len(enrollments)
    opened = sum(1 for e in enrollments if e["properties"].get("hs_was_email_opened") == "true")
    replied = sum(1 for e in enrollments if e["properties"].get("hs_was_email_replied") == "true")
    meetings = sum(1 for e in enrollments if e["properties"].get("hs_was_meeting_booked") == "true")
 
    report.append({
        "name": seq_name, "enrolled": enrolled, "opened": opened,
        "replied": replied, "meetings": meetings,
        "open_rate": round(opened / enrolled * 100, 1),
        "reply_rate": round(replied / enrolled * 100, 1),
        "meeting_rate": round(meetings / enrolled * 100, 1),
    })
 
report.sort(key=lambda r: r["enrolled"], reverse=True)
 
if not report:
    print("No enrollment data for the last 7 days — skipping.")
    sys.exit(0)
 
# --- Build Slack message ---
total_enrolled = sum(r["enrolled"] for r in report)
total_replied = sum(r["replied"] for r in report)
total_meetings = sum(r["meetings"] for r in report)
 
seq_lines = []
for r in report:
    seq_lines.append(
        f"*{r['name']}* ({r['enrolled']} enrolled)\n"
        f"    Open: {r['open_rate']}% | Reply: {r['reply_rate']}% | Meeting: {r['meeting_rate']}%"
    )
 
blocks = [
    {"type": "header", "text": {"type": "plain_text", "text": "\U0001F4E7 Weekly Sequence Performance Report"}},
    {"type": "section", "fields": [
        {"type": "mrkdwn", "text": f"*Total Enrolled*\n{total_enrolled}"},
        {"type": "mrkdwn", "text": f"*Total Replies*\n{total_replied}"},
        {"type": "mrkdwn", "text": f"*Meetings Booked*\n{total_meetings}"},
    ]},
    {"type": "divider"},
    {"type": "section", "text": {"type": "mrkdwn", "text": "*Per-Sequence Breakdown*\n\n" + "\n\n".join(seq_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 Sequence Performance Report",
    blocks=blocks, unfurl_links=False,
)
print(f"Posted report: {result['ts']}")

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

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

name: Weekly Sequence 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/sequence-report/scripts/report.py
        env:
          HUBSPOT_TOKEN: ${{ secrets.HUBSPOT_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}