Google Sheets, GPT-4o, 이메일을 사용하여 마케팅 활동 ROI 보고서 생성

• •Google Sheets API 인증 정보
• •OpenAI API Key

{
  "meta": {
    "instanceId": "ad0113c344ee237399e44e9f11798b05baeb83a6196d514a9ae9d2ad71c3b5c9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "df93659a-1341-4042-885c-1624e5501f3f",
      "name": "스티키 노트",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1792,
        -176
      ],
      "parameters": {
        "width": 540,
        "height": 848,
        "content": "## 📈 Campaign ROI Report with Generative AI + Email\n\nThis n8n workflow pulls campaign data from Google Sheets, summarizes it using OpenAI, and sends a performance recap via Outlook email.\n\n### ✅ Step 1: Connect Google Sheets\n\n1. In n8n, go to **Credentials** → click **New Credential**\n2. Select **Google Sheets OAuth2 API**\n3. Log in with your Google account and authorize\n4. Use a spreadsheet with:\n   - Column names in the first row  \n   - Data in rows 2–100  \n5. Example format: [📄 Sample Marketing Sheet](https://docs.google.com/spreadsheets/d/1UDWt0-Z9fHqwnSNfU3vvhSoYCFG6EG3E-ZewJC_CLq4/edit?usp=sharing)\n\n### ✅ Step 2: Connect OpenAI\n\n1. Go to [OpenAI API Keys](https://platform.openai.com/api-keys)\n2. Make sure you have a payment method set under [Billing](https://platform.openai.com/settings/organization/billing/overview)\n3. In n8n, create a new **OpenAI API** credential\n4. Paste your API key and save\n\n\n### 📬 Need Help?\n\nFeel free to contact me if you run into issues:\n\n- 📧 robert@ynteractive.com  \n- 🔗 [LinkedIn](https://www.linkedin.com/in/robert-breen-29429625/)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "07bc087a-adc5-4094-8236-bf3c90dfc7db",
      "name": "워크플로 시작",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1088,
        64
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "07ace160-586f-46b0-8b13-8d7fa467703b",
      "name": "OpenAI 채팅 모델1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -224,
        528
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o",
          "cachedResultName": "gpt-4o"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "4l6TDfLZVFS24g3X",
          "name": "OpenAi account 4"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "9f3326c5-e0c8-48e6-a560-08ee63ada662",
      "name": "구조화 출력 파서1",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        96,
        272
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"output\": \"Overall, campaign performance was strong this week. Retargeting efforts delivered the highest return, while a few awareness campaigns underperformed in conversions.\\n\\n• 📈 'Spring Retargeting' had the highest ROAS at 9.1\\n• 💰 'Loyalty Push' generated $12,000 revenue on $1,600 spend\\n• 📉 'Awareness Boost - TikTok' had low conversions despite high spend\\n• 🧠 Meta Ads accounted for 70% of total conversions\\n\\nTotals:\\n• Total Spend: $12,480\\n• Impressions: 983,400\\n• Clicks: 23,980\\n• Conversions: 1,482\\n• Revenue: $48,000\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "4719e276-755a-4791-9865-d6c12172c0d5",
      "name": "스티키 노트1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1216,
        272
      ],
      "parameters": {
        "color": 7,
        "width": 684,
        "height": 400,
        "content": "### Aggregate and Combine Data"
      },
      "typeVersion": 1
    },
    {
      "id": "84d6aad8-d30f-4c77-a134-476065d8d674",
      "name": "스티키 노트10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1216,
        -176
      ],
      "parameters": {
        "color": 3,
        "width": 672,
        "height": 416,
        "content": "### 2. Prepare Your Google Sheet\n\n#### Connect your Data in Google Sheets\n- Data must be in a format similar to this: [Sample Marketing Data](https://docs.google.com/spreadsheets/d/1UDWt0-Z9fHqwnSNfU3vvhSoYCFG6EG3E-ZewJC_CLq4/edit?gid=365710158#gid=365710158)\n- First row contains column names\n- Data in rows 2-100\n- Log in with OAuth2 and choose your workbook and sheet\n- Optional: Try connecting to Airtable, Notion or your Database"
      },
      "typeVersion": 1
    },
    {
      "id": "135a26ff-151e-494f-a862-c59063285dc4",
      "name": "스티키 노트2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -496,
        -176
      ],
      "parameters": {
        "color": 7,
        "width": 828,
        "height": 848,
        "content": "### AI Agent analyzes data and sends daily email"
      },
      "typeVersion": 1
    },
    {
      "id": "4b5211e3-46d3-42f7-ad79-64eff2d30567",
      "name": "데이터 가져오기",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -832,
        64
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 365710158,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UDWt0-Z9fHqwnSNfU3vvhSoYCFG6EG3E-ZewJC_CLq4/edit#gid=365710158",
          "cachedResultName": "Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1UDWt0-Z9fHqwnSNfU3vvhSoYCFG6EG3E-ZewJC_CLq4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1UDWt0-Z9fHqwnSNfU3vvhSoYCFG6EG3E-ZewJC_CLq4/edit?usp=drivesdk",
          "cachedResultName": "Sample Marketing Data - n8n"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "5b61292d-8951-4ec2-9274-a9600c5d4d07",
      "name": "캠페인 합계",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -1168,
        336
      ],
      "parameters": {
        "options": {},
        "fieldsToSplitBy": "Campaign",
        "fieldsToSummarize": {
          "values": [
            {
              "field": "Spend ($)",
              "aggregation": "sum"
            },
            {
              "field": "Clicks",
              "aggregation": "sum"
            },
            {
              "field": "Conversions",
              "aggregation": "sum"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "19ec9e9f-651f-44d2-868e-cd6742376707",
      "name": "채널 합계",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -1168,
        496
      ],
      "parameters": {
        "options": {},
        "fieldsToSplitBy": "Channel",
        "fieldsToSummarize": {
          "values": [
            {
              "field": "Spend ($)",
              "aggregation": "sum"
            },
            {
              "field": "Clicks",
              "aggregation": "sum"
            },
            {
              "field": "Conversions",
              "aggregation": "sum"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "f7f310bb-457c-4784-922d-3ca31ebcbe02",
      "name": "결합",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        -1024,
        336
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData",
        "destinationFieldName": "campaign_performance"
      },
      "typeVersion": 1
    },
    {
      "id": "200c07d6-05c3-4680-a22b-5ec6803c02a7",
      "name": "결합",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        -1024,
        496
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData",
        "destinationFieldName": "channel_performance"
      },
      "typeVersion": 1
    },
    {
      "id": "51d56f0e-6c2e-40c4-ac67-445218b9a78e",
      "name": "결과 병합",
      "type": "n8n-nodes-base.merge",
      "position": [
        -848,
        400
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.2
    },
    {
      "id": "7995b9a9-7bc4-4b41-81ac-a33694e42657",
      "name": "텍스트로 변환",
      "type": "n8n-nodes-base.code",
      "position": [
        -704,
        400
      ],
      "parameters": {
        "jsCode": "const campaignData = items[0].json.campaign_performance || [];\nconst channelData = items[0].json.channel_performance || [];\n\nlet campaignSummary = `📊 Campaign Performance:\\n`;\nfor (const entry of campaignData) {\n  campaignSummary += `• ${entry.Campaign}: $${entry[\"sum_Spend_($)\"].toFixed(2)} spend, ${entry[\"sum_Clicks\"]} clicks, ${entry[\"sum_Conversions\"]} conversions\\n`;\n}\n\nlet channelSummary = `\\n📣 Channel Performance:\\n`;\nfor (const entry of channelData) {\n  channelSummary += `• ${entry.Channel}: $${entry[\"sum_Spend_($)\"].toFixed(2)} spend, ${entry[\"sum_Clicks\"]} clicks, ${entry[\"sum_Conversions\"]} conversions\\n`;\n}\n\nreturn [\n  {\n    json: {\n      output: campaignSummary + channelSummary\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "43390f07-1797-41d7-bee5-40c37f0ff73f",
      "name": "마케팅 데이터 분석",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -144,
        0
      ],
      "parameters": {
        "text": "=Data: {{ $json.output }}",
        "options": {
          "systemMessage": "You are a Marketing Performance Assistant.\n\nUse the data access campaign data, including fields like: campaign name, cost, impressions, clicks, conversions, and revenue.\n\n\n\nYour job is to write a clear and helpful summary of campaign performance for a marketing team.\n\nYour response must be in **JSON format** with only one field:\n\n- `\"output\"`: A string that contains:\n  - A short paragraph explaining overall performance\n  - 3–5 bullet points with key insights\n  - Total spend, impressions, clicks, conversions, and revenue (as bullets)\n\nUse natural, business-friendly language and make it sound like part of a weekly email report. Use emojis if helpful. Do not include raw data or tables.\n\n**Example format:**\n\n```json\n{\n  \"output\": \"Overall, campaign performance was strong this week. Retargeting efforts delivered the highest return, while a few awareness campaigns underperformed in conversions.\\n\\n• 📈 'Spring Retargeting' had the highest ROAS at 9.1\\n• 💰 'Loyalty Push' generated $12,000 revenue on $1,600 spend\\n• 📉 'Awareness Boost - TikTok' had low conversions despite high spend\\n• 🧠 Meta Ads accounted for 70% of total conversions\\n\\nTotals:\\n• Total Spend: $12,480\\n• Impressions: 983,400\\n• Clicks: 23,980\\n• Conversions: 1,482\\n• Revenue: $48,000\"\n}\n"
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "8167ef4f-9a26-4fb3-a885-79a5a5f3664a",
      "name": "스티키 노트9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        304
      ],
      "parameters": {
        "color": 3,
        "width": 368,
        "height": 336,
        "content": "### 1. Set Up OpenAI Connection\n\n#### Get API Key:\n1. Go to [OpenAI Platform](https://platform.openai.com/api-keys)\n1. Go to [OpenAI Billing](https://platform.openai.com/settings/organization/billing/overview)\n2. Add funds to your billing account & copy your api key into the openAI credentials\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "200c07d6-05c3-4680-a22b-5ec6803c02a7": {
      "main": [
        [
          {
            "node": "51d56f0e-6c2e-40c4-ac67-445218b9a78e",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "4b5211e3-46d3-42f7-ad79-64eff2d30567": {
      "main": [
        [
          {
            "node": "5b61292d-8951-4ec2-9274-a9600c5d4d07",
            "type": "main",
            "index": 0
          },
          {
            "node": "19ec9e9f-651f-44d2-868e-cd6742376707",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "19ec9e9f-651f-44d2-868e-cd6742376707": {
      "main": [
        [
          {
            "node": "200c07d6-05c3-4680-a22b-5ec6803c02a7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "51d56f0e-6c2e-40c4-ac67-445218b9a78e": {
      "main": [
        [
          {
            "node": "7995b9a9-7bc4-4b41-81ac-a33694e42657",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5b61292d-8951-4ec2-9274-a9600c5d4d07": {
      "main": [
        [
          {
            "node": "200c07d6-05c3-4680-a22b-5ec6803c02a7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "07bc087a-adc5-4094-8236-bf3c90dfc7db": {
      "main": [
        [
          {
            "node": "4b5211e3-46d3-42f7-ad79-64eff2d30567",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7995b9a9-7bc4-4b41-81ac-a33694e42657": {
      "main": [
        [
          {
            "node": "43390f07-1797-41d7-bee5-40c37f0ff73f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "07ace160-586f-46b0-8b13-8d7fa467703b": {
      "ai_languageModel": [
        [
          {
            "node": "43390f07-1797-41d7-bee5-40c37f0ff73f",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "43390f07-1797-41d7-bee5-40c37f0ff73f": {
      "main": [
        []
      ]
    },
    "9f3326c5-e0c8-48e6-a560-08ee63ada662": {
      "ai_outputParser": [
        [
          {
            "node": "43390f07-1797-41d7-bee5-40c37f0ff73f",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    }
  }
}