Automate a weekly Salesforce pipeline report using n8n

Prerequisites

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.

Cost

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