Automate a weekly pipeline report with HubSpot and Slack using code and cron

Prerequisites

Node.js 18+ or Python 3.9+
HubSpot private app token (scopes: crm.objects.deals.read, crm.schemas.deals.read)
Slack Bot Token (xoxb-...) with chat:write scope, or a Slack Incoming Webhook URL
A scheduling environment: cron, GitHub Actions, or a cloud function

Step 1: Set up the project

# Verify your HubSpot token works
curl -s "https://api.hubapi.com/crm/v3/pipelines/deals" \
  -H "Authorization: Bearer $HUBSPOT_TOKEN" | head -c 200

Step 2: Fetch pipeline stages

First, get a mapping of stage IDs to human-readable names. HubSpot deals return stage IDs like closedwon — not labels like "Closed Won".

curl -s "https://api.hubapi.com/crm/v3/pipelines/deals" \
  -H "Authorization: Bearer $HUBSPOT_TOKEN" \
  | jq '.results[0].stages[] | {id: .id, label: .label}'

Step 3: Search for active deals

Use the HubSpot Search API to pull all deals in your pipeline. This endpoint supports filtering and pagination.

curl -s -X POST "https://api.hubapi.com/crm/v3/objects/deals/search" \
  -H "Authorization: Bearer $HUBSPOT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "filterGroups": [{
      "filters": [{
        "propertyName": "pipeline",
        "operator": "EQ",
        "value": "default"
      }]
    }],
    "properties": ["dealname","amount","dealstage","closedate","createdate","hubspot_owner_id","hs_lastmodifieddate"],
    "sorts": [{"propertyName": "amount", "direction": "DESCENDING"}],
    "limit": 100
  }'

Pagination

The search endpoint returns max 100 results per page. The after cursor is a string — pass it back in the next request. When paging.next is absent, you've reached the last page. The endpoint caps at 10,000 total results.

Property values are strings

All HubSpot property values come back as strings — even numbers and booleans. amount returns "50000" not 50000. Always parse with float() / parseFloat() and handle None/null values.

Step 4: Calculate metrics

from datetime import datetime, timezone
 
def calculate_metrics(deals, stage_map):
    total_value = 0
    by_stage = {}
    stale_deals = []
 
    for deal in deals:
        props = deal.properties
        amount = float(props.get("amount") or 0)
        total_value += amount
 
        stage_id = props.get("dealstage", "unknown")
        stage_name = stage_map.get(stage_id, stage_id)
        by_stage[stage_name] = by_stage.get(stage_name, 0) + 1
 
        # Flag stale deals (no update in 14+ days)
        last_mod = datetime.fromisoformat(
            props["hs_lastmodifieddate"].replace("Z", "+00:00")
        )
        days_stale = (datetime.now(timezone.utc) - last_mod).days
        if days_stale > 14:
            stale_deals.append({
                "name": props["dealname"],
                "amount": amount,
                "days_stale": days_stale
            })
 
    return {
        "total_value": total_value,
        "deal_count": len(deals),
        "by_stage": by_stage,
        "stale_deals": sorted(stale_deals, key=lambda d: d["days_stale"], reverse=True)
    }

Step 5: Post to Slack

Build a formatted report using Slack's Block Kit and post it.

curl -X POST "https://slack.com/api/chat.postMessage" \
  -H "Authorization: Bearer $SLACK_BOT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "channel": "C0123456789",
    "text": "Weekly Pipeline Report",
    "blocks": [
      {"type":"header","text":{"type":"plain_text","text":"📊 Weekly Pipeline Report"}},
      {"type":"section","fields":[
        {"type":"mrkdwn","text":"*Total Pipeline*\n$2,400,000"},
        {"type":"mrkdwn","text":"*Active Deals*\n47"}
      ]},
      {"type":"divider"},
      {"type":"section","text":{"type":"mrkdwn","text":"*Deals by Stage*\n• Qualified: 12\n• Proposal: 8\n• Closed Won: 5"}}
    ]
  }'

Slack Block Kit limits

Max 50 blocks per message. Section text max: 3,000 characters. Total payload max: 50 KB. If your report exceeds these limits, post a summary as the main message and details as threaded replies using thread_ts from the initial chat.postMessage response.

Step 6: Schedule with cron or GitHub Actions

Cron (server-based):

# crontab -e
0 8 * * 1 cd /path/to/pipeline-report && node report.js >> /var/log/pipeline-report.log 2>&1

GitHub Actions (serverless):

# .github/workflows/pipeline-report.yml
name: Weekly Pipeline Report
on:
  schedule:
    - cron: '0 13 * * 1'  # 8 AM ET = 1 PM UTC
  workflow_dispatch: {}     # Allow manual runs
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
      - run: npm ci && node report.js
        env:
          HUBSPOT_TOKEN: ${{ secrets.HUBSPOT_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}

Environment variables

Never commit tokens to your repo. Use GitHub Secrets, .env files (gitignored), or your hosting platform's secrets manager.

Rate limits

API	Limit	Impact
HubSpot general	150 req / 10 sec per account	No concern for weekly report
HubSpot Search	5 req / sec	Matters with pagination (add 200ms delay between pages)
HubSpot daily	500,000 req / day	No concern
Slack chat.postMessage	~20 req / min	No concern for 1-2 messages

Trade-offs

Full control over data transformation, formatting, and delivery logic
Version controlled — track changes in git, review in PRs
No per-execution cost — runs on existing infrastructure (GitHub Actions free tier includes 2,000 min/month)
No vendor lock-in — standard APIs, portable code
Maintenance overhead — you handle errors, monitoring, and updates
No visual debugging — logs, not a visual flow builder