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

Prerequisites

Why code?

Code is the best option when you want zero ongoing cost and full control over the pipeline report. You own the script, can version-control it, and run it on GitHub Actions for free. Setup takes about 45 minutes. The trade-off is that changes require editing code — but for a weekly report that rarely changes, this is usually fine.

How it works

1. Fetch pipeline stage definitions to map stage IDs to human-readable labels
2. Search all active deals using HubSpot's CRM Search API with pagination
3. Calculate metrics — total pipeline value, deal counts by stage, stale deals (14+ days without activity)
4. Post a Block Kit message to Slack with the pipeline summary and at-risk deals

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_ACCESS_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_ACCESS_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_ACCESS_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
  }'

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"}}
    ]
  }'

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_ACCESS_TOKEN: ${{ secrets.HUBSPOT_ACCESS_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}

Common questions

What if I have multiple pipelines?

Change the pipeline filter value from "default" to your pipeline's ID, or remove the pipeline filter entirely to include all deals. You can find pipeline IDs in HubSpot Settings > Objects > Deals > Pipelines (the ID is in the URL when editing).

How do I add week-over-week comparisons?

Store the current week's metrics in a JSON file (or database). On the next run, load the previous week's data, compute deltas, and add trend indicators to the Slack message. For GitHub Actions, use artifacts or a separate storage service.

Can I split the report by sales rep?

Yes. Add hubspot_owner_id to the deal properties, fetch the owner roster from /crm/v3/owners, and group deals by owner. Post per-rep summaries or a leaderboard alongside the pipeline totals.

What happens if a deal has no amount set?

The script treats missing amounts as $0 (float(props.get("amount") or 0)). These deals still count in the stage breakdown and stale deal checks, just with zero dollar value.

Rate limits

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