Send a weekly Slack report on HubSpot sequence performance using a Claude Code skill

mkdir -p .claude/skills/sequence-report/{templates,references}

.claude/skills/sequence-report/
├── SKILL.md                          # workflow guidelines + config
├── templates/
│   └── slack-sequence-report.md      # Block Kit template for Slack messages
└── references/
    └── hubspot-sequences-api.md      # HubSpot API patterns

---
name: sequence-report
description: Generate a weekly performance report for HubSpot sequences and post it to Slack
disable-model-invocation: true
allowed-tools: Bash, Read
---
 
## Goal
 
Fetch all HubSpot sequences, pull enrollment data for the last 7 days, calculate open/reply/meeting rates per sequence, and post a formatted report to Slack.
 
## Configuration
 
Read these environment variables:
 
- `HUBSPOT_ACCESS_TOKEN` — HubSpot private app token (required)
- `SLACK_BOT_TOKEN` — Slack bot token starting with xoxb- (required)
- `SLACK_CHANNEL_ID` — Slack channel ID starting with C (required)
 
Default lookback window: 7 days. The user may request a different window.
 
## Workflow
 
1. Validate that all required env vars are set. If any are missing, print which ones and exit.
2. Fetch all sequences from HubSpot. See `references/hubspot-sequences-api.md` for the endpoint.
3. Compute the 7-day lookback timestamp in milliseconds.
4. For each sequence, search for enrollments in the time range. See `references/hubspot-sequences-api.md` for the enrollment search endpoint and properties.
5. Calculate per-sequence metrics: open rate, reply rate, meeting booked rate.
6. Rank sequences by enrollment count descending.
7. Post the report to Slack using the Block Kit format in `templates/slack-sequence-report.md`.
8. Print a summary of how many sequences were reported and aggregate totals.
 
## Important notes
 
- The Sequences API requires **HubSpot Sales Hub Professional or Enterprise**. Free and Starter plans return 403 Forbidden.
- Sequences are accessed via the CRM v3 objects API: `GET /crm/v3/objects/sequences`. Older `/sequences/v2/` endpoints are deprecated.
- Enrollment boolean properties (`hs_was_email_opened`, `hs_was_email_replied`, `hs_was_meeting_booked`) are returned as **strings** `"true"` or `"false"`, not actual booleans.
- The Search API rate limit is 5 requests per second. Add a 200ms delay between enrollment searches.
- The Search API returns max 100 results per request. Use the `after` cursor from `paging.next.after` to paginate if needed.
- Use the `requests` library for HTTP calls and `slack_sdk` for Slack. Install them with pip if needed.

# Slack Sequence Report Template
 
Use this Block Kit structure for the weekly sequence performance report.
 
## Block Kit JSON
 
```json
{
  "channel": "<SLACK_CHANNEL_ID>",
  "text": "Weekly Sequence Performance Report",
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "📧 Weekly Sequence Performance Report"
      }
    },
    {
      "type": "section",
      "fields": [
        {
          "type": "mrkdwn",
          "text": "*Total Enrolled*\n<total_enrolled>"
        },
        {
          "type": "mrkdwn",
          "text": "*Total Replies*\n<total_replied>"
        },
        {
          "type": "mrkdwn",
          "text": "*Meetings Booked*\n<total_meetings>"
        }
      ]
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*Per-Sequence Breakdown*\n\n<sequence_lines>"
      }
    },
    {
      "type": "context",
      "elements": [
        {
          "type": "mrkdwn",
          "text": "Last 7 days | Generated <date_string>"
        }
      ]
    }
  ]
}
```
 
## Sequence line format
 
Each sequence line should follow this format:
```
*Sequence Name* (X enrolled)
    Open: Y% | Reply: Z% | Meeting: W%
```
 
Separate sequences with blank lines for readability.
 
## Notes
 
- The top-level `text` field is required by Slack as a fallback for notifications.
- Section text has a 3,000-character limit. If there are many sequences, truncate to the top 10.

# HubSpot Sequences API Reference
 
## List sequences
 
Fetch all sequences to get IDs and names.
 
**Request:**
 
```
GET https://api.hubapi.com/crm/v3/objects/sequences?limit=100&properties=hs_sequence_name
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
```
 
**Response shape:**
 
```json
{
  "results": [
    {
      "id": "12345",
      "properties": {
        "hs_sequence_name": "Outbound — Enterprise"
      }
    }
  ],
  "paging": {
    "next": {
      "after": "100"
    }
  }
}
```
 
## Search for enrollments
 
Find enrollments for a specific sequence in a time range.
 
**Request:**
 
```
POST https://api.hubapi.com/crm/v3/objects/sequence_enrollments/search
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
Content-Type: application/json
```
 
**Body:**
 
```json
{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "hs_sequence_id",
          "operator": "EQ",
          "value": "<sequence_id>"
        },
        {
          "propertyName": "hs_enrollment_start_date",
          "operator": "GTE",
          "value": "<start_timestamp_ms>"
        }
      ]
    }
  ],
  "properties": [
    "hs_sequence_id",
    "hs_enrollment_state",
    "hs_was_email_opened",
    "hs_was_email_replied",
    "hs_was_meeting_booked"
  ],
  "limit": 100
}
```
 
- `value` for timestamps is a Unix timestamp in **milliseconds**.
- `limit` max is 100. Use the `after` cursor for pagination.
 
**Response shape:**
 
```json
{
  "total": 42,
  "results": [
    {
      "id": "67890",
      "properties": {
        "hs_sequence_id": "12345",
        "hs_enrollment_state": "EXECUTING",
        "hs_was_email_opened": "true",
        "hs_was_email_replied": "false",
        "hs_was_meeting_booked": "false"
      }
    }
  ],
  "paging": {
    "next": {
      "after": "100"
    }
  }
}
```
 
**Important:** Boolean properties are returned as the **strings** `"true"` or `"false"`, not actual boolean values. Compare with `=== "true"` in JavaScript or `== "true"` in Python.

/sequence-report

Fetching sequences... found 3 active sequences
Fetching enrollments per sequence...
  Outbound — Enterprise: 85 enrolled
  Outbound — Mid-Market: 124 enrolled
  Re-engagement — Churned: 42 enrolled
Posting report to Slack...
Done. 3 sequences, 251 total enrollments, 31 replies, 9 meetings booked.

# Run every Monday at 9 AM
0 9 * * 1 cd /path/to/your/project && claude -p "Run /sequence-report" --allowedTools 'Bash(*)' 'Read(*)'

name: Weekly Sequence Report
on:
  schedule:
    - cron: '0 14 * * 1'  # 9 AM ET = 2 PM UTC, Mondays only
  workflow_dispatch: {}
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: anthropics/claude-code-action@v1
        with:
          prompt: "Run /sequence-report"
          allowed_tools: "Bash(*),Read(*)"
        env:
          ANTHROPIC_API_KEY: ${{ secrets.ANTHROPIC_API_KEY }}
          HUBSPOT_ACCESS_TOKEN: ${{ secrets.HUBSPOT_ACCESS_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}