Automation: Monthly SEO Report Pipeline with Zapier and Claude

What This Builds

An automation that, on the 1st of each month, reads your metrics spreadsheet, sends the data to Claude to generate narrative commentary, and creates a draft report document in Google Docs — ready for your review before sending to the client. Instead of spending 30-60 minutes per client on reporting, you spend 10 minutes reviewing and approving.

Prerequisites

• Zapier account (Professional plan or higher for multi-step Zaps with AI — check {{tool:Zapier.price}})
• Claude API access (Anthropic Console account — separate from Claude.ai subscription)
• Google Sheets with your monthly metrics in a consistent format
• Google Docs for final report output
• Comfortable reading basic Zapier workflows

The Concept

Think of this like setting up an assembly line. On reporting day, one worker (Zapier) picks up the data from your spreadsheet, hands it to another worker (Claude API) who writes the narrative, and a third worker (Zapier again) assembles the final document and puts it in Google Docs. You just do the final quality check.

Build It Step by Step

Part 1: Set up your metrics spreadsheet template

First, create a Google Sheets template that you'll fill in each month. Consistent column structure is critical — the automation reads specific cells.

Create a sheet with columns:

Column A: Client Name
Column B: Report Month (YYYY-MM)
Column C: Organic Sessions This Month
Column D: Organic Sessions Last Month
Column E: Sessions % Change (formula: =(C2-D2)/D2)
Column F: Organic Sessions Same Month Last Year
Column G: YoY % Change
Column H: Keywords in Top 10 (this month)
Column I: Keywords in Top 10 (last month)
Column J: Notable Rankings (up) — freetext
Column K: Notable Rankings (down) — freetext
Column L: Work Completed This Month — freetext
Column M: Next Month Priorities — freetext
Column N: Context/Notes — freetext
Column O: Client Industry
Column P: Client Primary Goal (traffic/leads/revenue)
Column Q: Report Ready for Processing? (Yes/No)

Each month, fill in rows for each client. When a client's row is complete, change Column Q to "Yes" — this triggers the automation.

Part 2: Get Claude API access

1. Go to console.anthropic.com and create an account
2. Click API Keys → Create Key — save the key securely (you'll add it to Zapier)
3. Note: Claude API pricing is usage-based (per token). A typical report narrative generation costs roughly $0.01-0.03 per client — very cheap for the time it saves

Part 3: Build the Zapier workflow

Create a new Zap in Zapier:

Trigger: Google Sheets — New or Updated Row

1. App: Google Sheets
2. Event: New or Updated Spreadsheet Row
3. Connect your Google account
4. Select your metrics spreadsheet and the specific sheet tab
5. Trigger column: Column Q
6. Filter: Only trigger when Column Q = "Yes"

Action 1: Format the data for Claude

Use Zapier's Code by Zapier (JavaScript) action to build the prompt string:

// Build the Claude prompt from spreadsheet data
const clientName = inputData.clientName;
const industry = inputData.industry;
const goal = inputData.primaryGoal;
const period = inputData.reportMonth;
const sessions = inputData.sessionsThisMonth;
const sessionsLast = inputData.sessionsLastMonth;
const sessionsPct = inputData.sessionsPctChange;
const yoyPct = inputData.yoyPctChange;
const kwTop10 = inputData.keywordsTop10;
const kwTop10Last = inputData.keywordsTop10Last;
const rankingsUp = inputData.notableUp;
const rankingsDown = inputData.notableDown;
const workCompleted = inputData.workCompleted;
const nextMonth = inputData.nextMonthPriorities;
const context = inputData.context;

const prompt = `Write a monthly SEO performance report narrative for ${clientName}, a ${industry} business whose primary SEO goal is ${goal}.

PERFORMANCE DATA — ${period}:
- Organic sessions: ${sessions} (vs ${sessionsLast} last month = ${sessionsPct}%; vs same month last year = ${yoyPct}%)
- Keywords in top 10: ${kwTop10} (was ${kwTop10Last} last month)
- Rankings improved: ${rankingsUp}
- Rankings declined: ${rankingsDown}
- Work completed: ${workCompleted}
- Context/notes: ${context}
- Next month priorities: ${nextMonth}

Write a 4-section narrative:
1. Performance Overview (2-3 sentences — lead with the headline metric, explain the trend)
2. What Drove Results (2-3 bullet points — connect specific work items to outcomes)
3. What We're Watching (1-2 sentences — honest about anything declining with context)
4. Next Month Focus (2 sentences — priorities and why they matter)

250-300 words total. Professional but plain-language. No jargon without definition.`;

return {prompt: prompt};

Action 2: Send to Claude API

Use Zapier's Webhooks by Zapier action:

1. App: Webhooks by Zapier
2. Action: POST
3. URL: https://api.anthropic.com/v1/messages
4. Headers:

   • x-api-key: your Claude API key
   • anthropic-version: 2023-06-01
   • content-type: application/json
5. Data (JSON):

{
  "model": "claude-opus-4-6",
  "max_tokens": 1024,
  "messages": [
    {
      "role": "user",
      "content": "{{output from Code step}}"
    }
  ]
}

1. After this step runs, map the response: the narrative text lives at content[0].text in the Claude API response

Action 3: Create the Google Doc

1. App: Google Docs
2. Action: Create Document from Template (or Create Document)
3. Document name: [Client Name] SEO Report — [Month]
4. Content: Your report header boilerplate + the Claude-generated narrative from the previous step
5. Folder: Your "Reports — Needs Review" Google Drive folder

Action 4: Mark the row as processed

1. App: Google Sheets
2. Action: Update Spreadsheet Row
3. Update Column Q to "Processing Complete" so the trigger doesn't re-fire

Part 4: Test and Refine

1. Fill in one test row in your spreadsheet with real (or dummy) data
2. Set Column Q to "Yes"
3. Run the Zap manually in test mode — watch each step execute
4. Check the Google Doc that was created — does the narrative look right?
5. Common issues:

   • Column mapping is off: check Zapier trigger output to confirm which field is which
   • Claude API returns error: check your API key and JSON formatting in the webhook step
   • Narrative is generic: improve the prompt in the Code step

Real Example: A Full Reporting Week

Setup: You manage 10 clients. On the 28th of each month, you fill in the metrics spreadsheet (one row per client, 5-10 minutes per client). By the 30th, all 10 rows have Column Q = "Yes."

What happens automatically:

• The Zap triggers 10 times (once per client row with "Yes")
• For each client, Claude generates a narrative in ~15 seconds
• 10 Google Docs appear in your "Reports — Needs Review" folder

What you do:

• Open each doc (10 minutes total)
• Review for factual accuracy and tone (most are 90% ready)
• Customize 1-2 sentences where the narrative missed context
• Move to "Approved" folder and send to client

Time saved: What was 5-6 hours of reporting work becomes 1-1.5 hours of reviewing and approving

What to Do When It Breaks

• Zap doesn't trigger → Check that Column Q is exactly "Yes" (case-sensitive); check that the trigger column is set correctly in Zapier
• Claude API returns 401 error → Your API key is wrong or expired — regenerate at console.anthropic.com
• Narrative mentions wrong client → The Code step has a mapping error — print out all inputData fields in the Code step to verify they match your column names
• Report narrative is too short/long → Adjust the max_tokens value in the API call and/or the word count instruction in the prompt

Variations

• Simpler version: Skip the API call and use Zapier's built-in OpenAI integration instead of the Claude webhook — easier setup, slightly lower quality output
• Extended version: Add a Zapier step that emails the Google Doc link to the client automatically once you've moved it to the "Approved" folder

What to Do Next

• This week: Build and test with one client's data; verify the full pipeline works end-to-end
• This month: Run a full reporting cycle through the automation; time yourself vs. the old process
• Advanced: Add a Slack notification when each report is ready for review so you get a direct alert instead of checking the folder

Advanced guide for SEO Specialist professionals. These techniques use more sophisticated AI features that may require paid subscriptions.