Automate a weekly pipeline report with HubSpot and Slack using a Claude Code skill

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

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

---
name: pipeline-report
description: Generate a weekly pipeline report from HubSpot and post it to Slack
disable-model-invocation: true
allowed-tools: Bash, Read
---
 
## Goal
 
Fetch all active deals from HubSpot, calculate pipeline metrics (total value, deals by stage, stale deals), 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 pipeline: `default`. The user may request a specific pipeline ID.
 
## Workflow
 
1. Validate that all required env vars are set. If any are missing, print which ones and exit.
2. Fetch pipeline stage definitions from HubSpot to build a stage ID → label map. See `references/hubspot-deals-api.md` for the endpoint.
3. Search for all active deals in the pipeline. See `references/hubspot-deals-api.md` for the search endpoint and request format. Paginate if needed.
4. Calculate metrics: total pipeline value, deal counts by stage, and deals with no activity in the last 14 days (stale deals).
5. Post the report to Slack using the Block Kit format in `templates/slack-pipeline-report.md`.
6. Print a summary of total pipeline value, deal count, and stale deal count.
 
## Important notes
 
- Deal amounts may be null or empty. Treat missing amounts as $0.
- The Deals Search API returns max 100 results per request. Use the `after` cursor from `paging.next.after` to paginate.
- Use `hs_lastmodifieddate` (not `closedate`) to determine if a deal is stale.
- Stage IDs in deal properties are internal identifiers — map them to human-readable labels using the pipelines endpoint.
- The default pipeline ID is `default` in most HubSpot portals. If the user has renamed it, they may need to provide the pipeline ID.
- Use the `requests` library for HTTP calls and `slack_sdk` for Slack. Install them with pip if needed.

# Slack Pipeline Report Template
 
Use this Block Kit structure for the weekly pipeline report.
 
## Block Kit JSON
 
```json
{
  "channel": "<SLACK_CHANNEL_ID>",
  "text": "Weekly Pipeline Report",
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "📊 Weekly Pipeline Report"
      }
    },
    {
      "type": "section",
      "fields": [
        {
          "type": "mrkdwn",
          "text": "*Total Pipeline*\n$<total_value>"
        },
        {
          "type": "mrkdwn",
          "text": "*Active Deals*\n<deal_count>"
        }
      ]
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*Deals by Stage*\n<stage_lines>"
      }
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "⚠️ *At Risk (14+ days stale)*\n<stale_lines>"
      }
    },
    {
      "type": "context",
      "elements": [
        {
          "type": "mrkdwn",
          "text": "Generated <date_string>"
        }
      ]
    }
  ]
}
```
 
## Stage line format
 
Each stage line: `• Stage Name: X deals`
 
## Stale deal line format
 
Each stale deal line: `• Deal Name — $Amount (Xd stale)`
 
Show the top 5 stale deals, sorted by days stale descending.
 
## Notes
 
- The top-level `text` field is required by Slack as a fallback for notifications.
- Omit the "At Risk" section if there are no stale deals.
- Format dollar amounts with commas (e.g., $2,400,000).

# HubSpot Deals API Reference
 
## Get pipeline stages
 
Build a stage ID → label map for the report.
 
**Request:**
 
```
GET https://api.hubapi.com/crm/v3/pipelines/deals
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
```
 
**Response shape:**
 
```json
{
  "results": [
    {
      "id": "default",
      "label": "Sales Pipeline",
      "stages": [
        {
          "id": "appointmentscheduled",
          "label": "Appointment Scheduled"
        },
        {
          "id": "qualifiedtobuy",
          "label": "Qualified to Buy"
        }
      ]
    }
  ]
}
```
 
## Search for deals
 
Use the CRM Search API to find all active deals in a pipeline.
 
**Request:**
 
```
POST https://api.hubapi.com/crm/v3/objects/deals/search
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
Content-Type: application/json
```
 
**Body:**
 
```json
{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "pipeline",
          "operator": "EQ",
          "value": "default"
        }
      ]
    }
  ],
  "properties": ["dealname", "amount", "dealstage", "closedate", "hs_lastmodifieddate"],
  "sorts": [{"propertyName": "amount", "direction": "DESCENDING"}],
  "limit": 100
}
```
 
- `limit` max is 100. Use the `after` cursor from `paging.next.after` to paginate.
- `amount` may be null or empty for deals without a dollar value set.
- `hs_lastmodifieddate` is an ISO 8601 timestamp (e.g., `"2026-03-01T10:30:00.000Z"`).
- `dealstage` is an internal stage ID — map it using the pipelines endpoint above.
 
**Response shape:**
 
```json
{
  "total": 47,
  "results": [
    {
      "id": "12345",
      "properties": {
        "dealname": "Acme Corp — Enterprise",
        "amount": "150000",
        "dealstage": "qualifiedtobuy",
        "closedate": "2026-04-15T00:00:00.000Z",
        "hs_lastmodifieddate": "2026-02-20T14:30:00.000Z"
      }
    }
  ],
  "paging": {
    "next": {
      "after": "100"
    }
  }
}
```

/pipeline-report

Fetching pipeline stages... found 6 stages
Fetching deals... found 47 active deals
Calculating metrics...
  Total pipeline: $2,400,000
  Stale deals: 3 (14+ days without activity)
Posting report to Slack...
Done. 47 deals, $2.4M total pipeline.

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

name: Weekly Pipeline Report
on:
  schedule:
    - cron: '0 13 * * 1'  # 8 AM ET = 1 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 /pipeline-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 }}