Automate a weekly pipeline report with HubSpot and Slack using n8n

Prerequisites

Why n8n?

n8n is the best option for a weekly pipeline report if you want a visual workflow with no per-execution cost. The visual editor makes it easy to add new metrics, change the pipeline filter, or split reports by team. Self-hosted n8n runs unlimited workflows for free. The Code node handles deal aggregation and metric calculation, while the Slack node supports Block Kit formatting out of the box.

How it works

• Schedule Trigger fires weekly on Monday morning
• HTTP Request node fetches pipeline stage definitions for ID-to-label mapping
• HTTP Request node searches all active deals with pagination
• Code node aggregates metrics — total pipeline value, deals by stage, stale deals
• Slack node posts a Block Kit message with the report

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

Common questions

How many n8n executions does this use per month?

One execution per week = ~4 executions/month. On n8n Cloud Starter ($24/mo, 2,500 executions), this uses less than 0.2% of your quota. Self-hosted n8n has no execution limits.

What if I have multiple pipelines?

Add a loop or duplicate the deal search node for each pipeline. You can use a Router node to post separate reports to different Slack channels per pipeline.

Can I add week-over-week comparison?

Yes. Use n8n's static data ($getWorkflowStaticData('global')) to persist last week's metrics. Compare current values against stored values and add delta indicators to the report.

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