אוטומציית דוחות שבועיים ללקוחות סוכנות שיווק עם n8n ו-Claude — מדריך שלב אחר שלב

קונפיגורציית לקוח ב-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"
  ]
}

שליפות API במקביל לכל לקוח

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
  "
}

זיהוי אנומליות (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;

Prompt תובנות של Claude

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)
- Locale: {{ report_locale }}  // respond in this language
- 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 just 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.

{
  "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": "השבוע החזק ביותר ברווח אורגני ברבעון, על רקע עליית התנועה לדף יירגצ'ף החדש שתופס גם שאילתות מותג וגם שאילתות לא-מותג.",
  "wins": [
    "ההכנסה האורגנית עלתה ב-28% מול שבוע שעבר ו-41% YoY. הדף החדש (עלה ב-14 באפריל) הוסיף 1,240 קליקים משאילתות כמו 'קפה אתיופי סינגל אוריג'ין' ו'יירגצ'ף מול סידאמו'.",
    "ROAS של קמפיין Search Brand עלה מ-6.1x ל-8.4x אחרי ניקוי שליליות ביום שלישי שעבר — בזבוז על שאילתות 'מכונת קפה' ירד ל-3% מהתקציב."
  ],
  "concerns": [
    "ב-Meta Advantage+ ה-CPA קפץ מ-$18 ל-$27 (+50%). רענון הקריאייטיב שעלה ביום שישי מתעייף מהר — ה-frequency ב-adset המוביל עומד על 4.2."
  ],
  "next_steps": [
    "להחליף את שני הקריאייטיבים עם ה-frequency הגבוה ב-Meta בווריאציות ליום האם, עד יום רביעי.",
    "לבנות לינקים פנימיים מהדפים הקיימים (סידאמו, גואטמלה) לדף יירגצ'ף כדי להגביר את התאוצה ב-SEO."
  ],
  "subject_line": "אקמה - שבועי: שבוע ההכנסה הטוב ברבעון"
}

הרכבת PDF ממותג

<!doctype html>
<html lang="{{ locale }}" dir="{{ direction }}">
<head>
  <meta charset="utf-8">
  <style>
    :root {
      --brand-primary:   {{ brand_primary_hex }};
      --brand-secondary: {{ brand_secondary_hex }};
    }
    body { font-family: Inter, "Heebo", 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-inline-start: 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`
    }
  }
}];

שילוח + Handoff ל-AM

{
  "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 }}"
  }
}