Automate a weekly pipeline report with HubSpot and Slack using n8n

Prerequisites

Step 1: Create the workflow and schedule trigger

Open n8n and create a new workflow. Add a Schedule Trigger node:

• Trigger interval: Weeks
• Day of week: Monday
• Hour: 8
• Minute: 0
• Timezone: Set to your team's timezone (n8n cloud defaults to UTC)

Step 2: Fetch deal stages from HubSpot

Before pulling deals, you need a map of stage IDs to human-readable names. Add an HTTP Request node:

• Method: GET
• URL: https://api.hubapi.com/crm/v3/pipelines/deals
• Authentication: Predefined Credential Type → HubSpot API
• Headers: The credential handles the Authorization: Bearer header automatically

This returns all pipelines and their stages. The response includes results[].stages[] with id (e.g., closedwon) and label (e.g., "Closed Won").

Step 3: Pull active deals

Add a second HTTP Request node to search for deals. Use the HubSpot Search API (not the basic list endpoint) because it supports filtering by pipeline and stage:

• Method: POST
• URL: https://api.hubapi.com/crm/v3/objects/deals/search
• Body content type: JSON
• Body:

{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "pipeline",
          "operator": "EQ",
          "value": "default"
        }
      ]
    }
  ],
  "properties": [
    "dealname", "amount", "dealstage", "pipeline",
    "closedate", "createdate", "hubspot_owner_id",
    "hs_lastmodifieddate"
  ],
  "sorts": [
    { "propertyName": "amount", "direction": "DESCENDING" }
  ],
  "limit": 100
}

Step 4: Transform the data with a Code node

Add a Code node (set to "Run Once for All Items") to process the deal data and calculate metrics:

const deals = $input.all().map(item => item.json);
 
// Parse the search response
const results = deals[0]?.results || deals;
const pipelineData = $('Fetch Stages').first().json.results;
 
// Build stage name lookup
const stageMap = {};
for (const pipeline of pipelineData) {
  for (const stage of pipeline.stages) {
    stageMap[stage.id] = stage.label;
  }
}
 
// Calculate metrics
let totalValue = 0;
const byStage = {};
let staleDeals = [];
 
for (const deal of results) {
  const amount = parseFloat(deal.properties.amount || '0');
  totalValue += amount;
 
  const stageId = deal.properties.dealstage;
  const stageName = stageMap[stageId] || stageId;
  byStage[stageName] = (byStage[stageName] || 0) + 1;
 
  // Flag deals with no activity in 14+ days
  const lastMod = new Date(deal.properties.hs_lastmodifieddate);
  const daysSinceUpdate = (Date.now() - lastMod) / (1000 * 60 * 60 * 24);
  if (daysSinceUpdate > 14) {
    staleDeals.push({
      name: deal.properties.dealname,
      amount,
      daysSinceUpdate: Math.round(daysSinceUpdate),
    });
  }
}
 
return [{
  json: {
    totalValue,
    dealCount: results.length,
    byStage,
    staleDeals,
  }
}];

Step 5: Format and send to Slack

Add a Slack node:

• Resource: Message
• Operation: Send a Message
• Channel: Select your #sales-reports channel (or enter the channel ID)
• Message Type: Block Kit

For the Blocks field, use an expression that builds Block Kit JSON:

{
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "📊 Weekly Pipeline Report"
      }
    },
    {
      "type": "section",
      "fields": [
        {
          "type": "mrkdwn",
          "text": "*Total Pipeline*\n${{ $json.totalValue.toLocaleString() }}"
        },
        {
          "type": "mrkdwn",
          "text": "*Active Deals*\n{{ $json.dealCount }}"
        }
      ]
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*Deals by Stage*\n{{ Object.entries($json.byStage).map(([stage, count]) => `• ${stage}: ${count}`).join('\\n') }}"
      }
    },
    {
      "type": "context",
      "elements": [
        {
          "type": "mrkdwn",
          "text": "Report generated {{ new Date().toLocaleDateString('en-US', { weekday: 'long', month: 'long', day: 'numeric', year: 'numeric' }) }}"
        }
      ]
    }
  ]
}

Step 6: Add error handling

Add error handling so you know if the report fails silently:

1. In each HTTP Request node, enable Settings → Retry On Fail with 2 retries and 5 second wait
2. Create a separate Error Workflow with an Error Trigger node that sends you a Slack DM when the main workflow fails
3. In the main workflow, go to Settings → Error Workflow and select your error workflow

Step 7: Test and activate

1. Click Execute Workflow to run the full workflow manually
2. Check each node's output — verify deal data is parsed correctly and the Slack message looks right
3. Toggle the workflow to Active so the Schedule Trigger fires automatically each Monday

Cost and maintenance

• n8n cloud: starts at $24/mo for the Starter plan (2,500 executions/month). A weekly report uses ~4 executions/month (one per Monday). Self-hosted n8n is free.
• Maintenance: minimal once running. Update the stage map if you rename pipeline stages in HubSpot. Monitor the error workflow for failures.

Next steps

Once the basic report is running, consider adding:

• Owner breakdown — use the HubSpot Owners API (GET /crm/v3/owners) to resolve hubspot_owner_id to rep names and add a per-rep section
• Week-over-week comparison — store last week's metrics in n8n's static data ($getWorkflowStaticData('global')) and calculate deltas
• Stale deal alerts — add a second Slack message listing deals with no activity in 14+ days
• Google Sheets backup — add a Google Sheets node to log each week's metrics for historical tracking