Automate Weekly Client SEO/Ads Reports with n8n + Claude Step-by-Step Guide

Client Config in Airtable

client_id            (autonumber, primary)
client_name          (text)         "Acme Coffee Roasters"
status               (select)       active | paused | offboarded
am_owner             (linked)       -> staff table
ga4_property_id      (text)         "properties/376412899"
gsc_site_url         (text)         "sc-domain:acmecoffee.com"
google_ads_customer  (text)         "123-456-7890"
meta_ad_account_id   (text)         "act_998877665544"
report_kpis          (multi-select) sessions, conversions, revenue,
                                    cpa, roas, organic_clicks, ...
brand_logo           (attachment)   client logo PNG, >= 600px wide
brand_primary_hex    (text)         "#0E5C3F"
brand_secondary_hex  (text)         "#F4B860"
brand_tone           (select)       formal | friendly | technical
recipients           (text)         comma-separated emails
slack_review_channel (text)         "#client-acme-internal"
report_day           (select)       Monday | Tuesday | ... (default Mon)
report_locale        (select)       en-US | en-GB | he-IL | ...

{
  "operation": "list",
  "base":  "{{ $credentials.airtable.baseId }}",
  "table": "clients",
  "filterByFormula":
    "AND({status}='active', {report_day}='{{ $today.weekday }}')",
  "fields": [
    "client_id","client_name","ga4_property_id","gsc_site_url",
    "google_ads_customer","meta_ad_account_id","report_kpis",
    "brand_logo","brand_primary_hex","brand_secondary_hex",
    "brand_tone","recipients","slack_review_channel","report_locale"
  ]
}

Parallel API Pulls per Client

POST https://analyticsdata.googleapis.com/v1beta/{{ $json.ga4_property_id }}:runReport
Authorization: Bearer {{ $credentials.googleSA.token }}
Content-Type: application/json

{
  "dateRanges": [
    { "startDate": "7daysAgo",  "endDate": "yesterday",
      "name": "current" },
    { "startDate": "14daysAgo", "endDate": "8daysAgo",
      "name": "previous" }
  ],
  "dimensions": [
    { "name": "sessionDefaultChannelGroup" }
  ],
  "metrics": [
    { "name": "sessions" },
    { "name": "totalUsers" },
    { "name": "conversions" },
    { "name": "totalRevenue" },
    { "name": "engagementRate" }
  ],
  "limit": 50
}

POST https://www.googleapis.com/webmasters/v3/sites/{{ encodeURIComponent($json.gsc_site_url) }}/searchAnalytics/query
Authorization: Bearer {{ $credentials.googleSA.token }}

{
  "startDate":   "{{ $json.range.start }}",
  "endDate":     "{{ $json.range.end }}",
  "dimensions":  ["query","page"],
  "rowLimit":    250,
  "dataState":   "final",
  "type":        "web"
}

// Run twice (current 7d + prior 7d), then diff in n8n Function node
// to surface top 10 winning queries and top 10 losing queries by clicks.

POST https://googleads.googleapis.com/v17/customers/{{ $json.google_ads_customer }}/googleAds:search
Authorization:    Bearer {{ $credentials.googleAds.token }}
developer-token:  {{ $credentials.googleAds.devToken }}
login-customer-id: {{ $credentials.googleAds.mccId }}

{
  "query": "
    SELECT
      campaign.id, campaign.name, campaign.status,
      metrics.cost_micros, metrics.clicks, metrics.impressions,
      metrics.conversions, metrics.conversions_value,
      segments.date
    FROM campaign
    WHERE segments.date DURING LAST_7_DAYS
      AND campaign.status != 'REMOVED'
    ORDER BY metrics.cost_micros DESC
    LIMIT 50
  "
}

Anomaly Detection (WoW & YoY)

const thresholds = {
  sessions:        { notable: 10, concerning: 20, critical: 35 },
  conversions:     { notable: 15, concerning: 25, critical: 40 },
  revenue:         { notable: 15, concerning: 25, critical: 40 },
  cpa:             { notable: 12, concerning: 20, critical: 35 },
  roas:            { notable: 10, concerning: 18, critical: 30 },
  organic_clicks:  { notable: 12, concerning: 22, critical: 35 }
};

function classify(metric, current, previous) {
  if (!previous) return { severity: "new", pct: null };
  const pct = ((current - previous) / previous) * 100;
  const t   = thresholds[metric] ?? { notable: 10, concerning: 20, critical: 35 };
  const a   = Math.abs(pct);
  let sev   = "stable";
  if (a >= t.critical)   sev = "critical";
  else if (a >= t.concerning) sev = "concerning";
  else if (a >= t.notable)    sev = "notable";
  return { severity: sev, pct: Number(pct.toFixed(1)),
           direction: pct >= 0 ? "up" : "down" };
}

const out = $input.all().map(item => {
  const r = item.json;
  return {
    metric: r.metric,
    current: r.current,
    previous_week: r.previous,
    previous_year: r.previous_yoy,
    wow: classify(r.metric, r.current, r.previous),
    yoy: classify(r.metric, r.current, r.previous_yoy)
  };
});

return out;

Claude Insight Prompt

You are the senior account strategist at {{ agency_name }} writing
the weekly recap section for one client. Your job is to pick the 3-5
things actually worth saying this week and write them in plain language
the client's marketing director can forward to their CEO unedited.

CLIENT CONTEXT:
- Client: {{ client_name }}
- Industry: {{ client_industry }}
- Primary KPI: {{ primary_kpi }}
- Tone: {{ brand_tone }} (formal | friendly | technical)
- This week's range: {{ range_label }}

INPUT DATA:
- Anomaly-classified metrics (only severity != stable provided)
- Top 10 winning organic queries WoW
- Top 10 losing organic queries WoW
- Top 5 ad campaigns by spend with ROAS deltas
- Notes from the AM (if any) — these take priority over data

OUTPUT — strict JSON, no prose:
{
  "headline": "one sentence the client would forward to their CEO",
  "wins":     ["1-2 sentence bullet", ...],   // 1-3 items
  "concerns": ["1-2 sentence bullet", ...],   // 0-3 items
  "next_steps": ["1-2 sentence bullet", ...], // 1-3 items
  "subject_line": "<= 60 chars, no emoji, no client name"
}

RULES:
- Never say "sessions decreased by X%". Say WHY it likely happened
  using the supplied query/campaign movers as evidence.
- Never invent a cause not supported by the data or AM notes.
- If a metric is "critical" down, it must appear in concerns.
- If primary KPI is up week-over-week AND year-over-year, lead the
  headline with that.
- Never reference seasonality unless YoY data shows the same pattern.
- Voice: write like a smart human strategist, not a dashboard.
- Never use the words "leverage", "synergy", "robust" or "holistic".

{
  "method": "POST",
  "url": "https://api.anthropic.com/v1/messages",
  "headers": {
    "x-api-key": "{{ $credentials.anthropic.apiKey }}",
    "anthropic-version": "2023-06-01",
    "content-type": "application/json"
  },
  "body": {
    "model": "claude-sonnet-4-5",
    "max_tokens": 1200,
    "system": "{{ $json.systemPrompt }}",
    "messages": [
      {
        "role": "user",
        "content": "ANOMALIES:n{{ JSON.stringify($json.anomalies) }}nnQUERY MOVERS:n{{ JSON.stringify($json.gscMovers) }}nnCAMPAIGN MOVERS:n{{ JSON.stringify($json.adsMovers) }}nnAM NOTES:n{{ $json.amNotes || 'none' }}"
      }
    ]
  }
}

{
  "headline": "Strongest revenue week of Q2 so far, driven by the new Ethiopia Yirgacheffe landing page picking up branded + non-branded traffic.",
  "wins": [
    "Organic revenue is up 28% week-over-week and 41% year-over-year. The Yirgacheffe page (live since Apr 14) added 1,240 new clicks from queries like 'ethiopian single origin coffee' and 'yirgacheffe vs sidamo'.",
    "Search Brand campaign ROAS climbed from 6.1x to 8.4x after the negative-keyword cleanup last Tuesday — wasted spend on coffee maker queries dropped to under 3% of budget."
  ],
  "concerns": [
    "Meta Advantage+ CPA jumped from $18 to $27 (+50%). The creative refresh that went live Friday is fatiguing fast — frequency on the top adset is at 4.2."
  ],
  "next_steps": [
    "Replace the two top-frequency Meta creatives with the seasonal Mother's Day variants by Wednesday.",
    "Build internal links from the existing Sidamo and Guatemala pages to the new Yirgacheffe page to compound the SEO momentum."
  ],
  "subject_line": "Acme weekly: best revenue week of Q2"
}

Branded PDF Assembly

<!doctype html>
<html lang="{{ locale }}">
<head>
  <meta charset="utf-8">
  <style>
    :root {
      --brand-primary:   {{ brand_primary_hex }};
      --brand-secondary: {{ brand_secondary_hex }};
    }
    body { font-family: Inter, sans-serif; color: #1a1a1a; }
    .cover { background: var(--brand-primary); color: white; padding: 60px; }
    .section h2 { color: var(--brand-primary); border-bottom: 2px solid var(--brand-secondary); }
    .kpi-grid  { display: grid; grid-template-columns: repeat(4, 1fr); gap: 12px; }
    .kpi       { background: #f7f7f7; padding: 18px; border-left: 4px solid var(--brand-primary); }
  </style>
</head>
<body>

  <section class="cover">
    <img src="{{ brand_logo_url }}" height="48">
    <h1>Weekly Performance Report</h1>
    <p>{{ client_name }} — {{ range_label }}</p>
  </section>

  <section class="section">
    <h2>Headline</h2>
    <p class="lead">{{ insight.headline }}</p>
  </section>

  <section class="section">
    <h2>KPIs</h2>
    <div class="kpi-grid">
      {{#each kpis}}
        <div class="kpi">
          <div class="label">{{ label }}</div>
          <div class="value">{{ value }}</div>
          <div class="delta {{ delta_class }}">{{ delta_label }}</div>
        </div>
      {{/each}}
    </div>
  </section>

  <section class="section">
    <h2>Wins</h2>
    <ul>{{#each insight.wins}}<li>{{ this }}</li>{{/each}}</ul>
  </section>

  <section class="section">
    <h2>Concerns</h2>
    <ul>{{#each insight.concerns}}<li>{{ this }}</li>{{/each}}</ul>
  </section>

  <section class="section">
    <h2>Next Steps</h2>
    <ul>{{#each insight.next_steps}}<li>{{ this }}</li>{{/each}}</ul>
  </section>

  <footer>
    Prepared by {{ am_name }} · {{ agency_name }} · {{ today }}
  </footer>
</body>
</html>

const puppeteer  = require('puppeteer');
const Handlebars = require('handlebars');

const tpl  = Handlebars.compile($input.first().json.templateHtml);
const html = tpl($input.first().json.report);

const browser = await puppeteer.launch({
  args: ['--no-sandbox','--disable-dev-shm-usage']
});
const page    = await browser.newPage();
await page.setContent(html, { waitUntil: 'networkidle0' });

const pdf = await page.pdf({
  format: 'A4',
  printBackground: true,
  margin: { top: '20mm', bottom: '20mm', left: '15mm', right: '15mm' }
});

await browser.close();

return [{
  binary: {
    report_pdf: {
      data: pdf.toString('base64'),
      mimeType: 'application/pdf',
      fileName: `${$json.report.client_slug}-week-${$json.report.iso_week}.pdf`
    }
  }
}];

Delivery + AM Handoff

{
  "channel": "{{ $json.client.slack_review_channel }}",
  "blocks": [
    {
      "type": "header",
      "text": { "type": "plain_text",
                "text": ":memo: {{ $json.client.client_name }} — weekly draft ready" }
    },
    {
      "type": "section",
      "text": { "type": "mrkdwn",
                "text": "*Headline:* {{ $json.report.insight.headline }}" }
    },
    {
      "type": "section",
      "fields": [
        { "type": "mrkdwn",
          "text": "*Subject:*n{{ $json.report.insight.subject_line }}" },
        { "type": "mrkdwn",
          "text": "*Recipients:*n{{ $json.client.recipients }}" }
      ]
    },
    {
      "type": "actions",
      "elements": [
        { "type": "button", "text": { "type": "plain_text",
          "text": "Send to client" }, "style": "primary",
          "action_id": "send_report",
          "value": "{{ $json.report.id }}" },
        { "type": "button", "text": { "type": "plain_text",
          "text": "Edit draft" }, "url": "{{ $json.report.editor_url }}" },
        { "type": "button", "text": { "type": "plain_text",
          "text": "Hold" }, "style": "danger",
          "action_id": "hold_report",
          "value": "{{ $json.report.id }}" }
      ]
    }
  ]
}

POST https://api.postmarkapp.com/email
X-Postmark-Server-Token: {{ $credentials.postmark.token }}

{
  "From":      "{{ $json.client.am_name }} <{{ $json.client.am_email }}>",
  "To":        "{{ $json.client.recipients }}",
  "Subject":   "{{ $json.report.insight.subject_line }}",
  "HtmlBody":  "{{ $json.report.email_html }}",
  "TextBody":  "{{ $json.report.email_text }}",
  "MessageStream": "outbound",
  "Attachments": [{
    "Name":        "{{ $json.report.pdf_filename }}",
    "Content":     "{{ $json.report.pdf_base64 }}",
    "ContentType": "application/pdf"
  }],
  "TrackOpens":  true,
  "Tag":         "weekly-report",
  "Metadata": {
    "client_id": "{{ $json.client.client_id }}",
    "iso_week":  "{{ $json.report.iso_week }}"
  }
}