Track lead-to-MQL conversion rate by source and report to Slack using a Claude Code skill

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

.claude/skills/conversion-report/
├── SKILL.md                          # workflow guidelines + config
├── templates/
│   └── slack-conversion-report.md    # Block Kit template for Slack messages
└── references/
    └── hubspot-contacts-api.md       # HubSpot API patterns

---
name: conversion-report
description: Generate a lead-to-MQL conversion report by source from HubSpot and post it to Slack
disable-model-invocation: true
allowed-tools: Bash, Read
---
 
## Goal
 
Query HubSpot for leads created in the last 7 days, identify which ones reached MQL status, calculate conversion rates by source, and post a formatted report to Slack.
 
## Configuration
 
Read these environment variables:
 
- `HUBSPOT_ACCESS_TOKEN` — HubSpot private app token (required)
- `SLACK_BOT_TOKEN` — Slack bot token starting with xoxb- (required)
- `SLACK_CHANNEL_ID` — Slack channel ID starting with C (required)
 
Default lookback window: 7 days (last Monday to this Monday). The user may request a different window.
 
## Workflow
 
1. Validate that all required env vars are set. If any are missing, print which ones and exit.
2. Compute the date range: 7-day lookback from today midnight UTC, in milliseconds.
3. Search for all contacts created in the period using the HubSpot CRM Search API. See `references/hubspot-contacts-api.md` for the endpoint and request format.
4. Search for MQLs from the same period by adding a `lifecyclestage = marketingqualifiedlead` filter.
5. Group both sets by `hs_analytics_source`. Calculate per-source and overall conversion rates.
6. Post the report to Slack using the Block Kit format in `templates/slack-conversion-report.md`.
7. Print a summary of total leads, MQLs, and overall conversion rate.
 
## Important notes
 
- HubSpot lifecycle stage values are **lowercase with no spaces**: use `marketingqualifiedlead`, not `Marketing Qualified Lead`.
- Timestamps must be in Unix **milliseconds** (13 digits). Passing seconds (10 digits) returns zero results with no error.
- The Search API returns max 100 results per request. Use the `after` cursor from `paging.next.after` to paginate if needed.
- Contacts without an `hs_analytics_source` property (manually created, API imports) should be grouped as `UNKNOWN`.
- Filter on `createdate` for when the lead was created, not `hs_timestamp`.
- Use the `requests` library for HTTP calls and `slack_sdk` for Slack. Install them with pip if needed.

# Slack Conversion Report Template
 
Use this Block Kit structure for the weekly lead-to-MQL conversion report.
 
## Block Kit JSON
 
```json
{
  "channel": "<SLACK_CHANNEL_ID>",
  "text": "Weekly Lead-to-MQL Conversion Report",
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "📈 Weekly Lead-to-MQL Conversion Report"
      }
    },
    {
      "type": "section",
      "fields": [
        {
          "type": "mrkdwn",
          "text": "*Total Leads*\n<total_leads>"
        },
        {
          "type": "mrkdwn",
          "text": "*Total MQLs*\n<total_mqls>"
        },
        {
          "type": "mrkdwn",
          "text": "*Overall Conversion*\n<overall_rate>%"
        }
      ]
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "*Conversion by Source*\n<source_lines>"
      }
    },
    {
      "type": "context",
      "elements": [
        {
          "type": "mrkdwn",
          "text": "Last 7 days | Generated <date_string>"
        }
      ]
    }
  ]
}
```
 
## Source line format
 
Each source line should follow this format:
`• *SOURCE_NAME*: X leads → Y MQLs (Z%)`
 
Sort sources by lead count descending.
 
## Notes
 
- The top-level `text` field is required by Slack as a fallback for notifications.
- Sources without an `hs_analytics_source` property should be labeled `UNKNOWN`.

# HubSpot Contacts Search API Reference
 
## Search for contacts
 
Use the CRM Search API to find contacts within a date range, optionally filtered by lifecycle stage.
 
**Request:**
 
```
POST https://api.hubapi.com/crm/v3/objects/contacts/search
Authorization: Bearer <HUBSPOT_ACCESS_TOKEN>
Content-Type: application/json
```
 
**Body (all leads in period):**
 
```json
{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "createdate",
          "operator": "GTE",
          "value": "<start_timestamp_ms>"
        },
        {
          "propertyName": "createdate",
          "operator": "LT",
          "value": "<end_timestamp_ms>"
        }
      ]
    }
  ],
  "properties": ["hs_analytics_source", "lifecyclestage", "createdate"],
  "limit": 100
}
```
 
**Body (MQLs only — add lifecycle stage filter):**
 
```json
{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "createdate",
          "operator": "GTE",
          "value": "<start_timestamp_ms>"
        },
        {
          "propertyName": "createdate",
          "operator": "LT",
          "value": "<end_timestamp_ms>"
        },
        {
          "propertyName": "lifecyclestage",
          "operator": "EQ",
          "value": "marketingqualifiedlead"
        }
      ]
    }
  ],
  "properties": ["hs_analytics_source", "lifecyclestage", "createdate"],
  "limit": 100
}
```
 
- `value` for timestamps is a Unix timestamp in **milliseconds** (multiply seconds by 1000).
- `limit` max is 100. If there are more results, use the `after` cursor from `paging.next.after` to paginate.
- Lifecycle stage values are **lowercase with no spaces**: `marketingqualifiedlead`, not `Marketing Qualified Lead`.
 
**Response shape:**
 
```json
{
  "total": 124,
  "results": [
    {
      "id": "12345",
      "properties": {
        "hs_analytics_source": "ORGANIC_SEARCH",
        "lifecyclestage": "marketingqualifiedlead",
        "createdate": "2026-03-01T10:30:00.000Z"
      }
    }
  ],
  "paging": {
    "next": {
      "after": "100"
    }
  }
}
```
 
## Common source values
 
HubSpot's `hs_analytics_source` property uses these standard values:
- `ORGANIC_SEARCH` — search engine traffic
- `PAID_SEARCH` — paid search ads
- `DIRECT_TRAFFIC` — direct visits
- `REFERRALS` — referral links
- `SOCIAL_MEDIA` — social media traffic
- `EMAIL_MARKETING` — email campaigns
- `OTHER_CAMPAIGNS` — other tracked campaigns
- `OFFLINE` — offline sources
- Null/missing — contacts created manually or via API without tracking

/conversion-report

Fetching leads created in last 7 days... found 741 contacts
Fetching MQLs from same period... found 98 MQLs
Calculating conversion by source...
Posting report to Slack...
Done. 741 leads → 98 MQLs (13.2% overall conversion).
Top source: Referral at 36.8% conversion.

# Run every Monday at 9 AM
0 9 * * 1 cd /path/to/your/project && claude -p "Run /conversion-report" --allowedTools 'Bash(*)' 'Read(*)'

name: Weekly Conversion Report
on:
  schedule:
    - cron: '0 14 * * 1'  # 9 AM ET = 2 PM UTC, Mondays only
  workflow_dispatch: {}
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: anthropics/claude-code-action@v1
        with:
          prompt: "Run /conversion-report"
          allowed_tools: "Bash(*),Read(*)"
        env:
          ANTHROPIC_API_KEY: ${{ secrets.ANTHROPIC_API_KEY }}
          HUBSPOT_ACCESS_TOKEN: ${{ secrets.HUBSPOT_ACCESS_TOKEN }}
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          SLACK_CHANNEL_ID: ${{ secrets.SLACK_CHANNEL_ID }}