GPT-4o, Sheets 및 Slack을 사용한 월간 Google Ads 성과 분석 자동화

• •Slack Bot Token 또는 Webhook URL
• •대상 API의 인증 정보가 필요할 수 있음
• •Google Sheets API 인증 정보
• •OpenAI API Key

{
  "meta": {
    "instanceId": "04fd795d32aabb18b913b4a3350b5cd0e9313a422ea0e7bdac0da2fb76cac9f7"
  },
  "nodes": [
    {
      "id": "9c5d8412-ea52-4af7-a129-f3808418de5c",
      "name": "월간 트리거",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "Runs 1st of every month to analyze last 30 days",
      "position": [
        -140,
        -60
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 0 1 * *"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "69c788b5-fdc2-4407-86c2-13dffe38bf61",
      "name": "성과 데이터 가져오기",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Gets 30-day performance data via GAQL. Min 100 impressions for statistical validity.",
      "position": [
        140,
        -60
      ],
      "parameters": {
        "url": "=https://googleads.googleapis.com/{{$env.GOOGLE_ADS_API_VERSION}}/customers/{{$env.GOOGLE_ADS_CUSTOMER_ID}}/googleAds:search",
        "method": "POST",
        "options": {},
        "jsonBody": "={\"query\": \"SELECT ad_group_ad.ad.id, ad_group_ad.ad.responsive_search_ad.headlines, ad_group.name, metrics.impressions, metrics.clicks, metrics.ctr FROM ad_group_ad WHERE segments.date DURING LAST_30_DAYS AND metrics.impressions > 100 ORDER BY metrics.clicks DESC LIMIT 1000\"}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleAdsOAuth2Api"
      },
      "credentials": {
        "googleAdsOAuth2Api": {
          "id": "x3Atp2GWGOjlhMz9",
          "name": "Google Ads account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "fb6f2b02-9ca1-4260-a761-d1fa23b6e926",
      "name": "성과 데이터 준비",
      "type": "n8n-nodes-base.code",
      "notes": "Analyzes performance data, groups by category/theme, calculates CTRs, formats for AI",
      "position": [
        520,
        -60
      ],
      "parameters": {
        "jsCode": "// Format performance data for AI analysis\n\nconst response = $input.first().json;\nconst results = response.results || [];\n\n// Group by category (ad group) and extract headlines\nconst categoryData = {};\nconst themeData = {};\n\nresults.forEach(result => {\n  const category = result.adGroup?.name || 'Unknown';\n  const headlines = result.adGroupAd?.ad?.responsiveSearchAd?.headlines || [];\n  const headline = headlines[0]?.text || '';\n  const ctr = parseFloat(result.metrics?.ctr || 0);\n  const impressions = parseInt(result.metrics?.impressions || 0);\n  const clicks = parseInt(result.metrics?.clicks || 0);\n  \n  if (impressions < 100) return;\n  \n  // Aggregate by category\n  if (!categoryData[category]) {\n    categoryData[category] = {impressions: 0, clicks: 0, ads: []};\n  }\n  categoryData[category].impressions += impressions;\n  categoryData[category].clicks += clicks;\n  categoryData[category].ads.push({headline, ctr, impressions, clicks});\n  \n  // Track themes\n  const themes = ['vegan', 'organic', 'natural', 'premium', 'sale', 'new', 'free shipping'];\n  themes.forEach(theme => {\n    if (headline.toLowerCase().includes(theme)) {\n      if (!themeData[theme]) {\n        themeData[theme] = {impressions: 0, clicks: 0, count: 0};\n      }\n      themeData[theme].impressions += impressions;\n      themeData[theme].clicks += clicks;\n      themeData[theme].count += 1;\n    }\n  });\n});\n\n// Calculate CTRs\nconst categoryAnalysis = Object.entries(categoryData).map(([cat, data]) => ({\n  category: cat,\n  ctr: ((data.clicks / data.impressions) * 100).toFixed(2),\n  total_impressions: data.impressions,\n  total_clicks: data.clicks,\n  ad_count: data.ads.length\n})).sort((a, b) => parseFloat(b.ctr) - parseFloat(a.ctr));\n\nconst themeAnalysis = Object.entries(themeData).map(([theme, data]) => ({\n  theme,\n  ctr: ((data.clicks / data.impressions) * 100).toFixed(2),\n  total_impressions: data.impressions,\n  ad_count: data.count\n})).sort((a, b) => parseFloat(b.ctr) - parseFloat(a.ctr));\n\nreturn {\n  total_ads_analyzed: results.length,\n  date_range: 'Last 30 days',\n  top_categories: categoryAnalysis.slice(0, 5),\n  bottom_categories: categoryAnalysis.slice(-5),\n  top_themes: themeAnalysis.slice(0, 5),\n  bottom_themes: themeAnalysis.slice(-5),\n  analysis_prompt: `Analyze this Google Ads performance data and provide actionable insights:\\n\\nTop Performing Categories:\\n${JSON.stringify(categoryAnalysis.slice(0, 5), null, 2)}\\n\\nBottom Performing Categories:\\n${JSON.stringify(categoryAnalysis.slice(-5), null, 2)}\\n\\nTop Performing Themes:\\n${JSON.stringify(themeAnalysis.slice(0, 5), null, 2)}\\n\\nBottom Performing Themes:\\n${JSON.stringify(themeAnalysis.slice(-5), null, 2)}\\n\\nProvide 3-5 specific recommendations for improving ad copy. Include estimated impact percentages. Format as: {'recommendations': [{'action': '...', 'expected_impact': '...', 'priority': 'HIGH/MEDIUM/LOW'}]}`\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "15384935-f99f-4e06-a475-b232106fe256",
      "name": "AI 에이전트 - 성과 분석",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "notes": "n8n AI Agent analyzes patterns and provides insights",
      "position": [
        800,
        -60
      ],
      "parameters": {
        "text": "={{$json.analysis_prompt}}",
        "options": {
          "systemMessage": "You are a Google Ads performance analyst. Analyze the data and provide specific, actionable recommendations. Return valid JSON only."
        },
        "promptType": "define"
      },
      "typeVersion": 1.7
    },
    {
      "id": "9adc1284-fa91-43c3-b326-260a51cfb000",
      "name": "OpenAI 채팅 모델",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "notes": "Lower temperature (0.2) for analytical tasks",
      "position": [
        780,
        120
      ],
      "parameters": {
        "model": "gpt-4o",
        "options": {
          "maxTokens": 2000,
          "temperature": 0.2
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "qsXncMzW3jo6pxGX",
          "name": "OpenAi Arvancloud"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "e5f12de9-b23c-4052-872e-466a9148b404",
      "name": "보고서 생성",
      "type": "n8n-nodes-base.code",
      "notes": "Creates formatted report with AI insights",
      "position": [
        1260,
        -60
      ],
      "parameters": {
        "jsCode": "// Parse AI recommendations and create report\n\nconst aiOutput = $input.first().json.output;\nconst performanceData = $node['Prepare Performance Data'].json;\n\n// Parse JSON from AI\nlet recommendations;\ntry {\n  const jsonMatch = aiOutput.match(/```json\\s*([\\s\\S]*?)```/) || aiOutput.match(/{[\\s\\S]*}/);\n  recommendations = JSON.parse(jsonMatch ? jsonMatch[1] || jsonMatch[0] : aiOutput);\n} catch (e) {\n  recommendations = {recommendations: [{action: aiOutput, priority: 'HIGH'}]};\n}\n\nconst report = `# 30-Day Performance Analysis Report\n\n## Executive Summary\nAnalyzed: ${performanceData.total_ads_analyzed} ads\nPeriod: ${performanceData.date_range}\nDate: ${new Date().toISOString().split('T')[0]}\n\n## Top Performing Categories\n${performanceData.top_categories.map(c => `- ${c.category}: ${c.ctr}% CTR (${c.ad_count} ads)`).join('\\n')}\n\n## Top Performing Themes\n${performanceData.top_themes.map(t => `- \"${t.theme}\" messaging: ${t.ctr}% CTR (${t.ad_count} ads)`).join('\\n')}\n\n## AI-Powered Recommendations\n${recommendations.recommendations?.map((r, i) => `${i+1}. [${r.priority}] ${r.action}\\n   Expected Impact: ${r.expected_impact || 'TBD'}`).join('\\n\\n')}\n\n## Next Steps\n1. Update ad copy to emphasize top-performing themes\n2. A/B test recommendations\n3. Review again in 30 days\n\n---\nGenerated by n8n AI Agent + OpenAI\n`;\n\nreturn {\n  report_markdown: report,\n  recommendations: recommendations.recommendations || [],\n  top_themes: performanceData.top_themes,\n  generated_at: new Date().toISOString()\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "ef39ae9e-d5f1-4ac5-83d2-648d70637564",
      "name": "Sheets에 보고서 저장",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Archives report in Google Sheets",
      "position": [
        1500,
        -60
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "Performance Reports"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{$env.GOOGLE_SHEET_ID}}"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "875c5cb7-e6d2-4576-89ad-334e88668200",
      "name": "보고서 전송",
      "type": "n8n-nodes-base.slack",
      "notes": "Sends report to team via Slack",
      "position": [
        1720,
        -60
      ],
      "webhookId": "cb69a0fc-d86c-4011-b4f8-0c8435b8f57b",
      "parameters": {
        "text": "={{$json.report_markdown}}",
        "otherOptions": {}
      },
      "typeVersion": 2.1
    },
    {
      "id": "77996afd-b9cb-49d1-a0b4-996210484fa6",
      "name": "스티커 메모",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -260,
        -360
      ],
      "parameters": {
        "width": 280,
        "height": 220,
        "content": "## 🟨 1 “Monthly Trigger”\n\n🕓 Runs every month\nAutomatically starts on the 1st of each month to analyze the last 30 days of Google Ads performance."
      },
      "typeVersion": 1
    },
    {
      "id": "7175f013-792a-4e70-80d3-035ec5cd485c",
      "name": "스티커 메모1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -40,
        140
      ],
      "parameters": {
        "width": 360,
        "height": 220,
        "content": "## 🟨 2 “Get Performance Data”\n\n📊 Fetches ad metrics\nQueries Google Ads API for CTR, clicks, and impressions using GAQL.\nFilters out ads with fewer than 100 impressions for accurate analysis."
      },
      "typeVersion": 1
    },
    {
      "id": "439ff524-d0ad-4a84-b697-be637f70447f",
      "name": "스티커 메모2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        380,
        -400
      ],
      "parameters": {
        "width": 420,
        "height": 200,
        "content": "## 🟨 3 “Prepare Performance Data”\n\n🧮 Processes raw metrics\nGroups ads by category and common themes (e.g., “sale,” “free shipping”).\nCalculates average CTRs and builds a summary prompt for AI analysis."
      },
      "typeVersion": 1
    },
    {
      "id": "16c3d3b7-7f19-4c31-8b72-161f8d7c5b8c",
      "name": "스티커 메모3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        740,
        260
      ],
      "parameters": {
        "width": 360,
        "height": 220,
        "content": "## 🟨 4 “AI Agent - Analyze Performance”\n\n🤖 AI insight generation\nSends the summary data to GPT-4o for analysis.\nThe AI recommends optimizations and highlights top-performing messaging."
      },
      "typeVersion": 1
    },
    {
      "id": "fb0e5d69-95b4-4cd4-834a-b5f563fb367a",
      "name": "스티커 메모4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1080,
        -400
      ],
      "parameters": {
        "width": 420,
        "height": 200,
        "content": "## 🟨 5 “Generate Report”\n\n🧾 Creates readable summary\nConverts AI output into a formatted markdown report.\nIncludes actionable recommendations and key performance stats."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "e5f12de9-b23c-4052-872e-466a9148b404": {
      "main": [
        [
          {
            "node": "ef39ae9e-d5f1-4ac5-83d2-648d70637564",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9c5d8412-ea52-4af7-a129-f3808418de5c": {
      "main": [
        [
          {
            "node": "69c788b5-fdc2-4407-86c2-13dffe38bf61",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9adc1284-fa91-43c3-b326-260a51cfb000": {
      "ai_languageModel": [
        [
          {
            "node": "15384935-f99f-4e06-a475-b232106fe256",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "69c788b5-fdc2-4407-86c2-13dffe38bf61": {
      "main": [
        [
          {
            "node": "fb6f2b02-9ca1-4260-a761-d1fa23b6e926",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ef39ae9e-d5f1-4ac5-83d2-648d70637564": {
      "main": [
        [
          {
            "node": "875c5cb7-e6d2-4576-89ad-334e88668200",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "fb6f2b02-9ca1-4260-a761-d1fa23b6e926": {
      "main": [
        [
          {
            "node": "15384935-f99f-4e06-a475-b232106fe256",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "15384935-f99f-4e06-a475-b232106fe256": {
      "main": [
        [
          {
            "node": "e5f12de9-b23c-4052-872e-466a9148b404",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}