Automate a weekly Salesforce pipeline report using n8n

Prerequisites

Why n8n?

n8n's Code node lets you write JavaScript for metric calculation and Slack formatting — far more flexible than Flow Builder's variable-per-stage approach. SOQL aggregation queries run server-side, so you get stage-level summaries in a single API call regardless of pipeline size. Self-hosted n8n is free with unlimited executions, and the visual canvas makes the Schedule → Query → Calculate → Post flow easy to follow. The trade-off is OAuth setup: you need a Salesforce Connected App.

Overview

This workflow runs every Monday morning, queries Salesforce for open pipeline data and recent closes, calculates key metrics, and posts a formatted report to Slack.

Step 1: Create a Salesforce Connected App

In Salesforce, go to Setup → App Manager → New Connected App:

1. App Name: n8n Pipeline Reporter
2. Enable OAuth Settings: checked
3. Callback URL: your n8n OAuth callback URL (e.g., https://your-n8n.com/rest/oauth2-credential/callback)
4. OAuth Scopes: Access and manage your data (api), Perform requests at any time (refresh_token, offline_access)
5. Save and note the Consumer Key and Consumer Secret

In n8n, add a Salesforce credential using these values.

Step 2: Add a Schedule Trigger

Create a new workflow and add a Schedule Trigger node:

• Trigger interval: Weekly
• Day of week: Monday
• Hour: 8
• Minute: 0
• Timezone: Your team's timezone

Step 3: Query open pipeline

Add an HTTP Request node to query Salesforce for pipeline by stage:

• Method: GET
• URL: {{ $json.empty ? '' : '' }}https://YOUR_INSTANCE.my.salesforce.com/services/data/v59.0/query
• Authentication: Predefined → Salesforce OAuth2
• Query params:
  • q: SELECT StageName, COUNT(Id) deal_count, SUM(Amount) total_amount FROM Opportunity WHERE IsClosed = false AND Amount != null GROUP BY StageName ORDER BY SUM(Amount) DESC

Step 4: Query closed deals this week

Add a second HTTP Request node:

• Query params:
  • q: SELECT COUNT(Id) closed_count, SUM(Amount) closed_amount FROM Opportunity WHERE CloseDate = THIS_WEEK AND IsWon = true

Step 5: Query new deals this week

Add a third HTTP Request node:

• Query params:
  • q: SELECT COUNT(Id) new_count, SUM(Amount) new_amount FROM Opportunity WHERE CreatedDate = THIS_WEEK

Step 6: Calculate metrics

Add a Code node to combine the data:

const pipeline = $('Open Pipeline').first().json.records;
const closed = $('Closed This Week').first().json.records[0];
const newDeals = $('New This Week').first().json.records[0];
 
const totalPipeline = pipeline.reduce((sum, s) => sum + (s.total_amount || 0), 0);
const totalDeals = pipeline.reduce((sum, s) => sum + s.deal_count, 0);
const avgDealSize = totalDeals > 0 ? totalPipeline / totalDeals : 0;
 
const stageBreakdown = pipeline.map(s =>
  `• *${s.StageName}*: ${s.deal_count} deals — $${(s.total_amount || 0).toLocaleString()}`
).join('\n');
 
return [{
  json: {
    totalPipeline,
    totalDeals,
    avgDealSize,
    closedCount: closed?.closed_count || 0,
    closedAmount: closed?.closed_amount || 0,
    newCount: newDeals?.new_count || 0,
    newAmount: newDeals?.new_amount || 0,
    stageBreakdown,
  }
}];

Step 7: Post to Slack

Add a Slack node:

• Resource: Message
• Operation: Send
• Channel: #sales-pipeline
• Message Type: Block Kit

{
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "📊 Weekly Pipeline Report"
      }
    },
    {
      "type": "section",
      "fields": [
        {"type": "mrkdwn", "text": "*Total Pipeline*\n${{ $json.totalPipeline.toLocaleString() }}"},
        {"type": "mrkdwn", "text": "*Open Deals*\n{{ $json.totalDeals }}"},
        {"type": "mrkdwn", "text": "*Avg Deal Size*\n${{ $json.avgDealSize.toLocaleString() }}"},
        {"type": "mrkdwn", "text": "*Closed This Week*\n{{ $json.closedCount }} — ${{ $json.closedAmount.toLocaleString() }}"},
        {"type": "mrkdwn", "text": "*New This Week*\n{{ $json.newCount }} — ${{ $json.newAmount.toLocaleString() }}"}
      ]
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*By Stage*\n{{ $json.stageBreakdown }}"
      }
    }
  ]
}

Step 8: Activate

1. Click Execute Workflow to test with real pipeline data
2. Verify the Slack message appears with correct metrics
3. Toggle the workflow to Active

Alternative: Use the Reports API

If you already have a Salesforce report configured, you can execute it via the Reports API instead of writing SOQL queries:

1. Find the report ID in Salesforce (it's in the URL when viewing the report)
2. Use an HTTP Request node: GET https://YOUR_INSTANCE.my.salesforce.com/services/data/v59.0/analytics/reports/{reportId}
3. Parse the response JSON to extract fact map data

This is useful when your report has complex filters or cross-object joins that are difficult to express in SOQL.

Common questions

How many n8n executions does a weekly report use?

1 execution per week, which includes all three SOQL queries, the Code node, and the Slack post. That's 4-5 executions per month — well within the Starter plan's 2,500 monthly limit.

Can I add a per-rep pipeline breakdown?

Yes. Add a fourth HTTP Request node with a SOQL query that groups by OwnerId and StageName. Join the Owner names in the Code node and add a second section to the Slack Block Kit message.

What if the Salesforce OAuth token expires between weekly runs?

n8n automatically refreshes Salesforce OAuth tokens using the refresh token. As long as your Connected App includes the refresh_token scope, the token renews transparently. Refresh tokens are valid for 90 days of inactivity by default — a weekly run keeps them active.

Cost

• n8n Cloud Starter: $24/mo for 2,500 executions. One weekly report = 4-5 executions/month.
• Self-hosted: Free. Unlimited executions.