Generate a weekly Salesforce pipeline report using a Claude Code skill

mkdir -p .claude/skills/sf-pipeline-report/{templates,references}

---
name: sf-pipeline-report
description: Generate a Salesforce pipeline report and post it to Slack. Queries open Opportunities, calculates key metrics, and formats a Block Kit message.
disable-model-invocation: true
allowed-tools: Bash, Read
---
 
## Workflow
 
1. Read `references/salesforce-soql.md` for SOQL aggregate query patterns
2. Read `references/slack-api.md` for posting Block Kit messages
3. Read `templates/report-format.md` for the default report structure
4. Query Salesforce for pipeline by stage (open Opportunities grouped by StageName)
5. Query Salesforce for deals closed this week (IsWon = true, CloseDate = THIS_WEEK)
6. Query Salesforce for new deals this week (CreatedDate = THIS_WEEK)
7. Calculate metrics: total pipeline, deal count, average deal size, closed-won, new deals
8. Format a Slack Block Kit message with headline metrics and stage breakdown
9. POST the message to the configured Slack channel
10. Print a summary of the report
 
## Rules
 
- Use SOQL aggregate functions (GROUP BY, COUNT, SUM) to minimize API calls
- Handle null Amount fields — exclude with Amount != null in SOQL
- Format dollar amounts with commas and no decimal places
- If the user asks for additional metrics (per-rep, per-month, velocity), adapt the SOQL queries accordingly
- Use environment variables: SALESFORCE_INSTANCE_URL, SALESFORCE_ACCESS_TOKEN, SLACK_BOT_TOKEN, SLACK_CHANNEL_ID

# Salesforce SOQL Aggregate Query Reference
 
## Query with aggregation
 
```
GET {SALESFORCE_INSTANCE_URL}/services/data/v59.0/query?q={SOQL}
Authorization: Bearer {SALESFORCE_ACCESS_TOKEN}
```
 
### Pipeline by stage
 
```sql
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
```
 
Response:
```json
{
  "totalSize": 5,
  "records": [
    {
      "StageName": "Negotiation",
      "deal_count": 12,
      "total_amount": 1250000
    },
    {
      "StageName": "Proposal",
      "deal_count": 18,
      "total_amount": 890000
    }
  ]
}
```
 
### Closed-won this week
 
```sql
SELECT COUNT(Id) closed_count, SUM(Amount) closed_amount
FROM Opportunity
WHERE CloseDate = THIS_WEEK AND IsWon = true
```
 
### New deals this week
 
```sql
SELECT COUNT(Id) new_count, SUM(Amount) new_amount
FROM Opportunity
WHERE CreatedDate = THIS_WEEK
```
 
### Pipeline by rep
 
```sql
SELECT Owner.Name, COUNT(Id) deal_count, SUM(Amount) total_amount
FROM Opportunity
WHERE IsClosed = false AND Amount != null
GROUP BY Owner.Name
ORDER BY SUM(Amount) DESC
```
 
Notes:
- SOQL date literals: THIS_WEEK, THIS_MONTH, THIS_FISCAL_QUARTER, LAST_N_DAYS:7
- SOQL strings must be URL-encoded when passed as query params
- Aggregate queries count against the 15,000 daily API call limit
- NULL Amount values are excluded from SUM — always filter with Amount != null

# Slack API Reference
 
## Post a message with Block Kit
 
```
POST https://slack.com/api/chat.postMessage
Authorization: Bearer {SLACK_BOT_TOKEN}
Content-Type: application/json
```
 
Request body:
```json
{
  "channel": "{SLACK_CHANNEL_ID}",
  "text": "Weekly Pipeline Report — fallback text for notifications",
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "Weekly Pipeline Report"
      }
    },
    {
      "type": "section",
      "fields": [
        { "type": "mrkdwn", "text": "*Total Pipeline*\n$3,100,000" },
        { "type": "mrkdwn", "text": "*Open Deals*\n63" }
      ]
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*By Stage*\n• *Negotiation*: 12 deals — $1,250,000\n• *Proposal*: 18 deals — $890,000"
      }
    }
  ]
}
```
 
Notes:
- `text` is the fallback shown in notifications; `blocks` render in the Slack client
- The bot must have `chat:write` scope and be added to the target channel
- Section fields are displayed in a 2-column grid (max 10 fields per section)
- Use `*bold*` and `_italic_` for mrkdwn formatting

# Default Pipeline Report Format
 
## Headline metrics (section with fields)
 
| Metric | SOQL source | Format |
|--------|-------------|--------|
| Total Pipeline | SUM(Amount) from open Opportunities | $X,XXX,XXX |
| Open Deals | COUNT(Id) from open Opportunities | number |
| Avg Deal Size | Total Pipeline / Open Deals | $XX,XXX |
| Closed This Week | COUNT + SUM where IsWon = true, CloseDate = THIS_WEEK | count — $amount |
| New This Week | COUNT + SUM where CreatedDate = THIS_WEEK | count — $amount |
 
## Stage breakdown (section with mrkdwn text)
 
List each stage on a new line:
```
• *StageName*: deal_count deals — $total_amount
```
 
Sorted by total_amount descending.
 
## Optional additions (when the user asks)
 
- **Per-rep breakdown**: GROUP BY Owner.Name, format as a second section
- **Week-over-week delta**: compare to LAST_WEEK values, show +/- percentage
- **Deals closing this month**: WHERE CloseDate = THIS_MONTH AND IsClosed = false
- **Stale deals**: WHERE LastActivityDate < LAST_N_DAYS:14 AND IsClosed = false

# In Claude Code
/sf-pipeline-report

# Run every Monday at 8 AM
0 8 * * 1 cd /path/to/project && claude -p "Run /sf-pipeline-report — post the weekly pipeline report to Slack." --allowedTools 'Bash,Read' 2>&1 >> /var/log/sf-pipeline.log

name: Salesforce Weekly Pipeline Report
on:
  schedule:
    - cron: '0 13 * * 1'  # 8 AM ET Monday = 1 PM UTC
  workflow_dispatch: {}
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: anthropics/claude-code-action@v1
        with:
          prompt: "Run /sf-pipeline-report — post the weekly pipeline report to Slack."
          allowed_tools: "Bash,Read"
        env:
          SALESFORCE_INSTANCE_URL: ${{ secrets.SALESFORCE_INSTANCE_URL }}
          SALESFORCE_ACCESS_TOKEN: ${{ secrets.SALESFORCE_ACCESS_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}