Berichterstattungsautomatisierung

• •Slack Bot Token oder Webhook URL
• •OpenAI API Key

{
  "id": "E32wcwtKunq0Tibz",
  "meta": {
    "instanceId": "6615d3df9d365bf328b0a329fe952ab6b434c9ecc8f5a2517849ec1f68f0d9b0",
    "templateCredsSetupCompleted": true
  },
  "name": "Reporting Automation",
  "tags": [
    {
      "id": "OYNCvrDxVQHw2gvR",
      "name": "Upwork",
      "createdAt": "2025-10-02T10:34:02.340Z",
      "updatedAt": "2025-10-02T10:34:02.340Z"
    },
    {
      "id": "ZcM21JDfzTQclYLn",
      "name": "Templated on N8N",
      "createdAt": "2025-10-14T17:00:09.847Z",
      "updatedAt": "2025-10-14T17:00:09.847Z"
    }
  ],
  "nodes": [
    {
      "id": "d5ec0af7-cb90-4cd6-91dc-69857fe7bca9",
      "name": "Zeitplan-Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        144,
        256
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "daysInterval": 7,
              "triggerAtHour": 7
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "7381727c-9a29-4da8-aaf3-cff59a138738",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -416,
        -80
      ],
      "parameters": {
        "color": 6,
        "width": 496,
        "height": 384,
        "content": "## 📊 What the Automation Does\n- Runs automatically on a schedule (e.g. every Monday).  \n- Pulls campaign performance data (here: demo data for Google Ads, Meta, TikTok, YouTube).  \n- Uses AI (LLM) to write a clear executive summary with wins, issues, and recommendations.  \n- Builds a structured report in Markdown (totals, channel performance, top campaigns).  \n- Creates a Google Doc with the full report.  \n- Notifies the team in Slack with topline numbers + report link.  \n- Emails the report directly to stakeholders or clients."
      },
      "typeVersion": 1
    },
    {
      "id": "f9fc4b70-f2dd-44f8-8ba1-245bf9f6a116",
      "name": "Google Ads Demo",
      "type": "n8n-nodes-base.code",
      "position": [
        368,
        256
      ],
      "parameters": {
        "jsCode": "// Reproducible dummy metrics for last 7 days, by channel & campaign.\n// No external API needed. Good for demos.\n\n// --- helpers ---\nfunction seededRandom(seed) {\n  // simple LCG\n  let s = seed % 2147483647;\n  if (s <= 0) s += 2147483646;\n  return () => (s = s * 16807 % 2147483647) / 2147483647;\n}\nconst seed = 20250601; // fix for reproducible demo\nconst rand = seededRandom(seed);\n\nfunction round(n, d=0) {\n  const p = Math.pow(10,d);\n  return Math.round(n*p)/p;\n}\n\nfunction dateISO(d) {\n  return d.toISOString().slice(0,10);\n}\n\n// --- period (last 7 days) ---\nconst end = new Date(); // today\nconst start = new Date(end);\nstart.setDate(end.getDate() - 6); // inclusive 7 days\n\n// --- channels & base volumes ---\nconst channels = [\n  { key: 'google_ads',  name: 'Google Ads',     baseImp: 140000, baseCpc: 0.6,  baseConvRate: 0.018 },\n  { key: 'meta_ads',    name: 'Meta Ads',       baseImp: 120000, baseCpc: 0.45, baseConvRate: 0.015 },\n  { key: 'tiktok_ads',  name: 'TikTok Ads',     baseImp:  90000, baseCpc: 0.35, baseConvRate: 0.012 },\n  { key: 'youtube_ads', name: 'YouTube Ads',    baseImp: 110000, baseCpc: 0.40, baseConvRate: 0.010 },\n];\n\nconst campaignsByChannel = {\n  google_ads:  ['Brand Search', 'Competitor Search', 'Non-Brand Generic'],\n  meta_ads:    ['Prospecting Video', 'Retargeting Carousel', 'Broad Static'],\n  tiktok_ads:  ['Spark Ads UGC', 'Creator Whitelist', 'TopView Test'],\n  youtube_ads: ['In-Stream Skippable', 'In-Feed Shorts', 'Remarketing']\n};\n\n// --- generate data ---\nlet totals = { impressions: 0, clicks: 0, conversions: 0, spend: 0, revenue: 0 };\nconst byChannel = [];\n\nfor (const ch of channels) {\n  const chImpressions = Math.floor(ch.baseImp * (0.9 + rand()*0.3)); // ±15%\n  const chClicks = Math.floor(chImpressions * (0.01 + rand()*0.02)); // 1%–3%\n  const chCpc = round(ch.baseCpc * (0.85 + rand()*0.3), 2);\n  const chSpend = round(chClicks * chCpc, 2);\n  const chConvRate = ch.baseConvRate * (0.8 + rand()*0.4); // ±20%\n  const chConversions = Math.floor(chClicks * chConvRate);\n  const aov = 38 + rand()*22; // average order value 38–60\n  const chRevenue = round(chConversions * aov, 2);\n  const chRoas = chSpend > 0 ? round(chRevenue / chSpend, 2) : null;\n\n  // campaigns\n  const campNames = campaignsByChannel[ch.key];\n  const campaigns = [];\n  let remImp = chImpressions, remClicks = chClicks, remConv = chConversions, remSpend = chSpend, remRev = chRevenue;\n\n  for (let i=0;i<campNames.length;i++) {\n    const share = i < campNames.length-1 ? (0.3 + rand()*0.5) : 1; // last gets remainder\n    const imp = i < campNames.length-1 ? Math.max(0, Math.floor(remImp * share * 0.4)) : remImp;\n    const clk = i < campNames.length-1 ? Math.max(0, Math.floor(remClicks * share * 0.4)) : remClicks;\n    const conv= i < campNames.length-1 ? Math.max(0, Math.floor(remConv * share * 0.4)) : remConv;\n    const sp  = i < campNames.length-1 ? round(remSpend * share * 0.4, 2) : round(remSpend,2);\n    const rev = i < campNames.length-1 ? round(remRev * share * 0.4, 2) : round(remRev,2);\n    campaigns.push({\n      name: campNames[i],\n      impressions: imp,\n      clicks: clk,\n      conversions: conv,\n      spend: sp,\n      revenue: rev,\n      roas: sp>0 ? round(rev/sp,2) : null,\n      cpc: clk>0 ? round(sp/clk,2) : null,\n      ctr: imp>0 ? round((clk/imp)*100,2) : null,\n      convRate: clk>0 ? round((conv/clk)*100,2) : null,\n    });\n    remImp -= imp; remClicks -= clk; remConv -= conv; remSpend = round(remSpend - sp,2); remRev = round(remRev - rev,2);\n  }\n\n  totals.impressions += chImpressions;\n  totals.clicks += chClicks;\n  totals.conversions += chConversions;\n  totals.spend = round(totals.spend + chSpend, 2);\n  totals.revenue = round(totals.revenue + chRevenue, 2);\n\n  byChannel.push({\n    key: ch.key,\n    name: ch.name,\n    impressions: chImpressions,\n    clicks: chClicks,\n    conversions: chConversions,\n    spend: chSpend,\n    revenue: chRevenue,\n    roas: chRoas,\n    cpc: chCpc,\n    ctr: chImpressions>0 ? round((chClicks/chImpressions)*100,2) : null,\n    convRate: chClicks>0 ? round((chConversions/chClicks)*100,2) : null,\n    campaigns\n  });\n}\n\nconst period = { start: dateISO(start), end: dateISO(end) };\nconst summary = {\n  impressions: totals.impressions,\n  clicks: totals.clicks,\n  conversions: totals.conversions,\n  spend: totals.spend,\n  revenue: totals.revenue,\n  roas: totals.spend>0 ? round(totals.revenue/totals.spend,2) : null,\n  ctr: totals.impressions>0 ? round((totals.clicks/totals.impressions)*100,2) : null,\n  convRate: totals.clicks>0 ? round((totals.conversions/totals.clicks)*100,2) : null,\n};\n\nreturn {\n  period,\n  summary,\n  byChannel\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "c335b635-893a-4e1d-aeeb-ecc4c4947bbd",
      "name": "Message a model",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        592,
        256
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "GPT-4O-MINI"
        },
        "options": {},
        "messages": {
          "values": [
            {
              "content": "=Create a short executive summary (120–180 words) for a weekly ad performance report.\nUse the provided JSON metrics. Include: key wins, issues, and 1-2 high-impact recommendations.\n\nPeriod: {{ $json.period.start }} → {{ $json.period.end }}\nTotals: {{ $json.summary }}\nChannels: {{ $json.byChannel[0] }}"
            },
            {
              "role": "system",
              "content": "You are a senior performance marketer. Write concise, actionable summaries."
            }
          ]
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "WVC4WZ1lsahz1CtE",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "6a4ebcda-4997-4dc8-ab60-3370c0bf373e",
      "name": "Code in JavaScript",
      "type": "n8n-nodes-base.code",
      "position": [
        960,
        256
      ],
      "parameters": {
        "jsCode": "/**\n * Build a weekly report in Markdown from fake metrics + LLM executive summary.\n * IMPORTANT: Set these to your exact node names in the left sidebar.\n */\nconst METRICS_NODE = \"Google Ads Demo\";   // <— dein Metrics-Code-Node\nconst SUMMARY_NODE = \"Message a model\";   // <— dein LLM-Node\n\n// ---- pull data from the referenced nodes ----\nconst metrics = $node[METRICS_NODE]?.json;\nif (!metrics) {\n  throw new Error(`Metrics node \"${METRICS_NODE}\" not found or has no JSON output.`);\n}\nconst period = metrics.period || {};\nconst S = metrics.summary || {};\nconst channels = metrics.byChannel || [];\n\nconst exec = $node[SUMMARY_NODE]?.json?.message?.content\n          || \"No executive summary available.\";\n\n// ---- helpers ----\nfunction fmt(n, d=0) {\n  if (n === null || n === undefined) return \"-\";\n  return Number(n).toLocaleString(undefined, { maximumFractionDigits: d });\n}\n\nfunction mdChannelTable(rows) {\n  const header = `| Channel | Impr. | Clicks | Conv. | Spend | Revenue | ROAS | CTR | CVR |\n|---|---:|---:|---:|---:|---:|---:|---:|---:|`;\n  const lines = rows.map(r =>\n    `| ${r.name} | ${fmt(r.impressions)} | ${fmt(r.clicks)} | ${fmt(r.conversions)} | $${fmt(r.spend,2)} | $${fmt(r.revenue,2)} | ${fmt(r.roas,2)} | ${fmt(r.ctr,2)}% | ${fmt(r.convRate,2)}% |`\n  );\n  return [header, ...lines].join('\\n');\n}\n\nfunction mdTopCampaigns(rows) {\n  const all = [];\n  for (const ch of rows) {\n    for (const c of (ch.campaigns || [])) all.push({ channel: ch.name, ...c });\n  }\n  all.sort((a,b) => (b.roas ?? 0) - (a.roas ?? 0));\n  const top = all.slice(0,3);\n  const header = `| Campaign | Channel | ROAS | Spend | Revenue | CTR | CVR |\n|---|---|---:|---:|---:|---:|---:|`;\n  const lines = top.map(t =>\n    `| ${t.name} | ${t.channel} | ${fmt(t.roas,2)} | $${fmt(t.spend,2)} | $${fmt(t.revenue,2)} | ${fmt(t.ctr,2)}% | ${fmt(t.convRate,2)}% |`\n  );\n  return [header, ...lines].join('\\n');\n}\n\n// ---- assemble markdown ----\nconst md = [\n  `# Weekly Performance Report`,\n  `**Period:** ${period.start ?? \"-\"} → ${period.end ?? \"-\"}`,\n  ``,\n  `## Executive Summary`,\n  exec,\n  ``,\n  `## Totals`,\n  `- Impressions: ${fmt(S.impressions)}`,\n  `- Clicks: ${fmt(S.clicks)}`,\n  `- Conversions: ${fmt(S.conversions)}`,\n  `- Spend: $${fmt(S.spend,2)}`,\n  `- Revenue: $${fmt(S.revenue,2)}`,\n  `- ROAS: ${fmt(S.roas,2)}`,\n  `- CTR: ${fmt(S.ctr,2)}%`,\n  `- Conversion Rate: ${fmt(S.convRate,2)}%`,\n  ``,\n  `## Performance by Channel`,\n  channels.length ? mdChannelTable(channels) : \"_No channel data._\",\n  ``,\n  `## Top Campaigns (by ROAS)`,\n  channels.length ? mdTopCampaigns(channels) : \"_No campaigns available._\",\n  ``,\n  `*Generated automatically by n8n.*`\n].join('\\n');\n\nreturn {\n  report: md,\n  period,\n  summary: S,\n  byChannel: channels\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "42bceff8-69d9-4d21-9d94-f3f7c2dd82ed",
      "name": "Create a document",
      "type": "n8n-nodes-base.googleDocs",
      "position": [
        1248,
        256
      ],
      "parameters": {
        "title": "=Weekly Performance Report – {{ $json.period.start }} to {{ $json.period.end }}",
        "folderId": "11ih8BSx4EacEniL01PhPSHzRbvaWj83n"
      },
      "credentials": {
        "googleDocsOAuth2Api": {
          "id": "9WVdXWeVTwOgO9X6",
          "name": "ClickLessAI Google Docs"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "4c463d5e-23a6-4662-b6c9-f1b818e81cd0",
      "name": "Update a document",
      "type": "n8n-nodes-base.googleDocs",
      "position": [
        1520,
        256
      ],
      "parameters": {
        "actionsUi": {
          "actionFields": [
            {
              "text": "={{ $('Code in JavaScript').item.json.report }}",
              "action": "insert"
            }
          ]
        },
        "operation": "update",
        "documentURL": "={{ $json.id }}"
      },
      "credentials": {
        "googleDocsOAuth2Api": {
          "id": "9WVdXWeVTwOgO9X6",
          "name": "ClickLessAI Google Docs"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "b9a2b5f0-0b83-439a-9acd-04031d27ed27",
      "name": "Send a message",
      "type": "n8n-nodes-base.slack",
      "position": [
        1808,
        256
      ],
      "webhookId": "8e51042f-918e-4efb-bf65-86e675e65a65",
      "parameters": {
        "text": "=:bar_chart: Weekly report is ready\nPeriod: {{ $('Code in JavaScript').item.json.period.start }} → {{ $('Code in JavaScript').item.json.period.end }}\nTopline: ROAS {{ $('Code in JavaScript').item.json.summary.roas }} | Spend ${{ $('Code in JavaScript').item.json.summary.spend }}\n\nOpen Doc: https://docs.google.com/document/d/{{ $json.documentId }}/edit",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "C09HKQVAKB7",
          "cachedResultName": "demo"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "credentials": {
        "slackOAuth2Api": {
          "id": "G4NvdInPNhmhP1e9",
          "name": "Slack account"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "d01c3e2e-abc4-4cf8-8136-22b5c4b15c62",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -416,
        320
      ],
      "parameters": {
        "color": 4,
        "width": 496,
        "height": 368,
        "content": "## 💡 Why This Is Valuable\n- Saves time – no manual copy-paste across ad platforms or spreadsheets.  \n- Standardizes reporting – same structure and clarity every week.  \n- Adds insights – AI summary highlights wins, problems, and recommendations automatically.  \n- Improves transparency – team + client get instant access in Slack/Docs/Email.  \n- Scales easily – works for multiple clients/campaigns with minimal changes.  \n- Professional client experience – polished reports delivered consistently on time."
      },
      "typeVersion": 1
    },
    {
      "id": "177d41bb-20ff-46da-af5d-3e8e863b5e71",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        288,
        -16
      ],
      "parameters": {
        "color": 3,
        "height": 448,
        "content": "## 📊 Generate Metrics (Demo)\nProduces fake ad performance data for Google Ads, Meta, TikTok & YouTube.\n\n👉 Replace with real API connectors (Google Ads, Meta Ads, TikTok, YouTube) if you want live data."
      },
      "typeVersion": 1
    },
    {
      "id": "ef227c18-b1f5-4dde-87f0-4bc5fa3c6fd0",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        544,
        -16
      ],
      "parameters": {
        "color": 2,
        "width": 320,
        "height": 448,
        "content": "## 🤖 AI Executive Summary\nSends metrics to OpenAI (LLM) to create a concise summary with wins, issues, and recommendations.  \n\n👉 Make sure your **OpenAI credentials** are connected."
      },
      "typeVersion": 1
    },
    {
      "id": "7007246b-9269-4e94-a243-0e76c1926654",
      "name": "Haftnotiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        -16
      ],
      "parameters": {
        "color": 4,
        "width": 256,
        "height": 448,
        "content": "## 📝 Build Markdown Report\nCombines raw metrics + AI summary into a structured Markdown report.  \nIncludes totals, per-channel table, and top campaigns by ROAS. \n \n👉 Node name references must match exactly (“Google Ads Demo”, “Message a model”)."
      },
      "typeVersion": 1
    },
    {
      "id": "e8091e8f-7c67-407b-9533-b8a41f339b29",
      "name": "Haftnotiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1152,
        -16
      ],
      "parameters": {
        "color": 5,
        "width": 272,
        "height": 448,
        "content": "## 📄 Google Docs Creation\nCreates a new Google Doc titled  \n**“Weekly Performance Report – [Start Date] to [End Date]”**.  \n\n👉 Requires Google Docs OAuth connection."
      },
      "typeVersion": 1
    },
    {
      "id": "57e73a10-801c-40e6-a840-5f29c8f5a4b3",
      "name": "Haftnotiz7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1440,
        -16
      ],
      "parameters": {
        "width": 256,
        "height": 448,
        "content": "## 🖊️ Update Google Doc\nInserts the Markdown report into the created document.  \n👉 You’ll get a polished report ready for sharing."
      },
      "typeVersion": 1
    },
    {
      "id": "1ed03086-b047-4138-a733-f3566cd69678",
      "name": "Haftnotiz6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1712,
        -16
      ],
      "parameters": {
        "color": 6,
        "width": 304,
        "height": 448,
        "content": "## 💬 Slack Notification\nSends a Slack message with topline numbers (ROAS, Spend) + direct link to the Google Doc.  \n👉 Connect your Slack account and set the channel ID."
      },
      "typeVersion": 1
    },
    {
      "id": "28afc12f-f5fb-4262-bcaa-1f1f5900dcad",
      "name": "Haftnotiz8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        528,
        480
      ],
      "parameters": {
        "width": 1056,
        "height": 416,
        "content": "## 💡 Extra Recommendation: Use a Google Docs Template\nInstead of creating a blank Google Doc, connect this workflow to a **pre-styled Google Docs template**.  \n👉 This makes your reports look more professional right away.  \n\n### Benefits:\n- Consistent branding (logo, fonts, colors).  \n- Polished design without extra formatting steps.  \n- Easier for clients/stakeholders to read.  \n\n### How:\n1. Create a Google Docs file with your preferred layout and styles.  \n2. Save its **document ID**.  \n3. Replace the \"Create Google Doc\" node with a **Copy Document** step that duplicates your template.  \n4. Update the copy with the AI-generated report content.  \n\n💡 Result: Every report keeps a consistent, branded look while still being updated automatically.\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "4e9d3eae-5822-422b-85d9-9b9c0dd0bcbb",
  "connections": {
    "f9fc4b70-f2dd-44f8-8ba1-245bf9f6a116": {
      "main": [
        [
          {
            "node": "c335b635-893a-4e1d-aeeb-ecc4c4947bbd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c335b635-893a-4e1d-aeeb-ecc4c4947bbd": {
      "main": [
        [
          {
            "node": "6a4ebcda-4997-4dc8-ab60-3370c0bf373e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "f9fc4b70-f2dd-44f8-8ba1-245bf9f6a116",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "42bceff8-69d9-4d21-9d94-f3f7c2dd82ed": {
      "main": [
        [
          {
            "node": "4c463d5e-23a6-4662-b6c9-f1b818e81cd0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4c463d5e-23a6-4662-b6c9-f1b818e81cd0": {
      "main": [
        [
          {
            "node": "b9a2b5f0-0b83-439a-9acd-04031d27ed27",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6a4ebcda-4997-4dc8-ab60-3370c0bf373e": {
      "main": [
        [
          {
            "node": "42bceff8-69d9-4d21-9d94-f3f7c2dd82ed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}