GPT-4o、Google Sheets、メールを使用した自動ブランドメンション追跡器

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "45be95bf90d9b3ae2fe5e2695ab0d09c072dff6b4645327103a14d107f342c30",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "a3e98b7d-e020-402b-848f-c47025a37ed7",
      "name": "デイリートリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -380,
        -80
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "645199f4-192f-407b-b842-8f3d9c85a12a",
      "name": "クエリ設定",
      "type": "n8n-nodes-base.code",
      "position": [
        -160,
        -80
      ],
      "parameters": {
        "jsCode": "// Configuration - Update these values\nconst brandName = \"YourBrandName\"; // TODO: Replace with your actual brand name\n\n// TODO: Replace these example queries with ones relevant to YOUR brand and industry.\n// Aim for a mix of query types to get comprehensive insights.\nconst queries = [\n  // --- General Brand Awareness & Perception ---\n  \"What is [YourBrandName]?\",\n  \"Tell me about [YourBrandName]'s main products/services.\",\n  \"What are the benefits of using [YourBrandName]?\",\n  \"How does [YourBrandName] compare to other solutions in the [YourIndustry] market?\",\n  \"What do people say about [YourBrandName]?\",\n\n  // --- Problem/Solution Fit (User trying to solve a problem YourBrandName addresses) ---\n  \"How can I solve [Problem YourBrandName Solves]?\",\n  \"What are the best tools for [Task YourBrandName Helps With]?\",\n  \"I'm looking for a way to [Achieve Goal YourBrandName Facilitates], any suggestions?\",\n\n  // --- Feature-Specific or Use-Case Queries ---\n  \"Does [YourBrandName] offer [Specific Feature]?\",\n  \"How can I use [YourBrandName] for [Specific Use Case]?\",\n\n  // --- Competitor Comparison (Direct & Indirect) ---\n  // Replace [Competitor A/B/C] with actual competitor names relevant to you.\n  \"What are alternatives to [Competitor A]?\",\n  \"Compare [YourBrandName] and [Competitor B].\",\n  \"Is [YourBrandName] better than [Competitor C] for [Specific Need]?\",\n\n  // --- Industry or Category Questions (Where YourBrandName should ideally be mentioned) ---\n  \"What are the leading companies in the [YourIndustry] sector?\",\n  \"Recommend a good [Product/Service Category YourBrandName Belongs To].\",\n\n  // --- Negative or Challenging Queries (To see how AI handles them) ---\n  \"What are some downsides of using [YourBrandName]?\",\n  \"Are there any common complaints about [YourBrandName]?\",\n\n  // --- Buying Intent / Recommendation Seeking ---\n  \"Should I choose [YourBrandName] for [Specific Purpose]?\",\n  \"What's the best [Product/Service Category] for someone who needs [Specific Requirement]?\",\n\n  // --- Add more queries specific to your brand, product features, target audience pain points, and competitive landscape ---\n  // Example: \"How does [YourBrandName] handle [Unique Selling Proposition]?\"\n  // Example: \"What are the pricing options for [YourBrandName]?\"\n];\n\n// Create output items\nconst items = [];\nfor (let i = 0; i < queries.length; i++) {\n  items.push({\n    json: {\n      brandName: brandName,\n      query: queries[i],\n      queryIndex: i + 1,\n      timestamp: new Date().toISOString(),\n      date: new Date().toISOString().split('T')[0]\n    }\n  });\n}\n\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "f56df036-8e1e-46d8-81b1-fc96d6107c7f",
      "name": "ChatGPT 検索 (HTTP)",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        60,
        -80
      ],
      "parameters": {
        "url": "https://api.openai.com/v1/chat/completions",
        "method": "POST",
        "options": {
          "response": {
            "response": {
              "neverError": true,
              "fullResponse": true
            }
          }
        },
        "jsonBody": "={\n  \"model\": \"chatgpt-4o-latest\",\n  \"messages\": [\n    {\n      \"role\": \"user\", \n      \"content\": \"{{ $json.query }}\"\n    }\n  ],\n  \"max_tokens\": 1000,\n  \"temperature\": 0.7\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "nodeCredentialType": "openAiApi"
      },
      "credentials": {
        "openAiApi": {
          "id": "t921mWykUSe9acQO",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "0aa04ced-7fb9-4763-98a9-01efaf0b1a8d",
      "name": "レスポンス処理",
      "type": "n8n-nodes-base.code",
      "position": [
        280,
        -80
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Process the response and check for brand mentions\nconst httpResponse = $json; // This is the HTTP response\nconst originalQueries = $('Setup Queries').all(); // Get all original queries\n\n// Find the matching original query for this response\n// Since we're processing items one by one, we need to match by index\nconst currentIndex = $itemIndex; // Current item index\nconst originalData = originalQueries[currentIndex]?.json;\n\n// Get brand name from original data\nconst brandName = originalData?.brandName?.toLowerCase() || 'unknown';\n\nlet response = '';\nlet brandMentioned = 'No';\nlet error = null;\n\n// Check if there was an error\nif (httpResponse.statusCode && httpResponse.statusCode !== 200) {\n  response = `HTTP Error: ${httpResponse.statusCode} - ${httpResponse.statusMessage || 'Request failed'}`;\n  brandMentioned = 'Error';\n  error = `HTTP ${httpResponse.statusCode}`;\n} else if (httpResponse.body && httpResponse.body.choices && httpResponse.body.choices[0]) {\n  response = httpResponse.body.choices[0].message.content;\n  // Check for brand mention (case insensitive)\n  if (brandName && brandName !== 'unknown' && response.toLowerCase().includes(brandName)) {\n    brandMentioned = 'Yes';\n  }\n} else if (httpResponse.body && httpResponse.body.error) {\n  response = `API Error: ${httpResponse.body.error.message}`;\n  brandMentioned = 'Error';\n  error = httpResponse.body.error.message;\n} else {\n  response = 'No valid response received';\n  brandMentioned = 'Error';\n  error = 'Invalid response structure';\n}\n\n// Return combined data\nreturn {\n  json: {\n    timestamp: originalData?.timestamp || new Date().toISOString(),\n    date: originalData?.date || new Date().toISOString().split('T')[0],\n    query: originalData?.query || 'Unknown query',\n    queryIndex: originalData?.queryIndex || currentIndex + 1,\n    brandName: originalData?.brandName || 'Unknown brand',\n    response: response.substring(0, 500), // Limit response length\n    brandMentioned: brandMentioned,\n    error: error\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "5734b4c6-731b-4384-b86b-6732501015d6",
      "name": "Google Sheets へ保存",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        500,
        -80
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ $json.date }}",
            "Error": "={{ $json.error }}",
            "Query": "={{ $json.query }}",
            "Response": "={{ $json.response }}",
            "Timestamp": "={{ $json.timestamp }}",
            "Brand_Name": "={{ $json.brandName }}",
            "Query_Index": "={{ $json.queryIndex }}",
            "Brand_Mentioned": "={{ $json.brandMentioned }}"
          },
          "schema": [
            {
              "id": "Timestamp",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Timestamp",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Query",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Query",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Query_Index",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Query_Index",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Brand_Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Brand_Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Response",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Response",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Brand_Mentioned",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Brand_Mentioned",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Error",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Error",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_GOOGLE_SHEET_ID"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "k9rHOUQ8uoo854zo",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "e0878bda-3f73-4ba4-9845-ca472109e566",
      "name": "メールレポート生成",
      "type": "n8n-nodes-base.code",
      "position": [
        720,
        -80
      ],
      "parameters": {
        "jsCode": "// Generate email summary with competitor detection\nconst allItems = $input.all();\n\n// Get the processed data from Process Response node\nconst processedData = $('Process Response').all();\nconst dataToUse = processedData.length > 0 ? processedData : allItems;\n\n// Define competitors to look for (add/modify as needed)\nconst competitors = [\n  'honey',\n  'rakuten',\n  'ebates',\n  'capital one shopping',\n  'wikibuy',\n  'retailmenot',\n  'couponfollow',\n  'groupon',\n  'slickdeals',\n  'cashback',\n  'ibotta',\n  'dosh',\n  'drop',\n  'pei',\n  'checkout 51',\n  'swagbucks',\n  'topcashback',\n  'mr. rebates',\n  'befrugal',\n  'extrabux'\n];\n\n// Function to find competitors mentioned in response\nfunction findCompetitors(response) {\n  if (!response || typeof response !== 'string') return [];\n  \n  const responseText = response.toLowerCase();\n  const foundCompetitors = [];\n  \n  competitors.forEach(competitor => {\n    if (responseText.includes(competitor.toLowerCase())) {\n      // Capitalize first letter of each word for display\n      const displayName = competitor.split(' ')\n        .map(word => word.charAt(0).toUpperCase() + word.slice(1))\n        .join(' ');\n      foundCompetitors.push(displayName);\n    }\n  });\n  \n  return foundCompetitors;\n}\n\nconst totalQueries = dataToUse.length;\nconst mentions = dataToUse.filter(item => item.json.brandMentioned === 'Yes').length;\nconst errors = dataToUse.filter(item => item.json.brandMentioned === 'Error').length;\nconst brandName = dataToUse[0]?.json.brandName || 'Unknown';\nconst date = dataToUse[0]?.json.date || new Date().toISOString().split('T')[0];\n\n// Count total competitor mentions\nlet totalCompetitorMentions = 0;\nconst competitorCounts = {};\n\n// Create HTML table\nlet tableRows = '';\ndataToUse.forEach((item, index) => {\n  const bgColor = item.json.brandMentioned === 'Yes' ? '#d4edda' : \n                  item.json.brandMentioned === 'Error' ? '#f8d7da' : '#fff';\n  \n  // Find competitors in this response\n  const competitorsFound = findCompetitors(item.json.response);\n  const competitorsDisplay = competitorsFound.length > 0 ? \n    competitorsFound.join(', ') : \n    'None';\n  \n  // Count competitors for summary\n  competitorsFound.forEach(comp => {\n    competitorCounts[comp] = (competitorCounts[comp] || 0) + 1;\n    totalCompetitorMentions++;\n  });\n  \n  tableRows += `\n    <tr style=\"background-color: ${bgColor};\">\n      <td>${item.json.queryIndex || index + 1}</td>\n      <td>${item.json.query || 'Unknown query'}</td>\n      <td><strong>${item.json.brandMentioned || 'Unknown'}</strong></td>\n      <td>${competitorsDisplay}</td>\n    </tr>\n  `;\n});\n\n// Create competitor summary\nlet competitorSummary = '';\nif (Object.keys(competitorCounts).length > 0) {\n  competitorSummary = '<h3>Competitor Mentions Summary:</h3><ul>';\n  Object.entries(competitorCounts)\n    .sort((a, b) => b[1] - a[1]) // Sort by count descending\n    .forEach(([competitor, count]) => {\n      competitorSummary += `<li><strong>${competitor}</strong>: ${count} mentions</li>`;\n    });\n  competitorSummary += '</ul>';\n} else {\n  competitorSummary = '<p><em>No competitors mentioned in any responses.</em></p>';\n}\n\nconst subject = mentions > 0 ? \n  `🎯 Brand Mentioned! ${brandName} found in ${mentions}/${totalQueries} queries` :\n  `📊 Daily Report: ${brandName} - No mentions found`;\n\nconst htmlContent = `\n<h2>Brand Mention Monitor Report</h2>\n<p><strong>Brand:</strong> ${brandName}</p>\n<p><strong>Date:</strong> ${date}</p>\n<p><strong>Summary:</strong></p>\n<ul>\n  <li>Total Queries: ${totalQueries}</li>\n  <li>Brand Mentions: ${mentions}</li>\n  <li>Competitor Mentions: ${totalCompetitorMentions}</li>\n  <li>Errors: ${errors}</li>\n  <li>Success Rate: ${Math.round(((totalQueries - errors) / totalQueries) * 100)}%</li>\n</ul>\n\n${competitorSummary}\n\n<table border=\"1\" style=\"border-collapse: collapse; width: 100%;\">\n  <thead>\n    <tr style=\"background-color: #f8f9fa;\">\n      <th>Query #</th>\n      <th>Query</th>\n      <th>Brand Mentioned</th>\n      <th>Competitors Mentioned</th>\n    </tr>\n  </thead>\n  <tbody>\n    ${tableRows}\n  </tbody>\n</table>\n\n<p><em>Full details saved to Google Sheets</em></p>\n`;\n\nreturn {\n  json: {\n    subject: subject,\n    htmlContent: htmlContent,\n    summary: {\n      totalQueries,\n      mentions,\n      errors,\n      brandName,\n      date,\n      competitorMentions: totalCompetitorMentions,\n      competitorBreakdown: competitorCounts\n    }\n  }\n};\n"
      },
      "executeOnce": true,
      "typeVersion": 2
    },
    {
      "id": "7a27b9c0-7786-4f72-8aae-8f07887364a7",
      "name": "メール送信",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        940,
        -80
      ],
      "webhookId": "14d26982-a3f0-4621-92f1-0c9e00d6c627",
      "parameters": {
        "html": "={{ $json.htmlContent }}",
        "options": {},
        "subject": "={{ $json.subject }}",
        "toEmail": "recipient@example.com",
        "fromEmail": "sender@example.com"
      },
      "credentials": {
        "smtp": {
          "id": "ugdxCdIOAuolzgAf",
          "name": "SMTP account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "98246a8a-892e-495a-942a-a65cf778aa6e",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -400,
        -240
      ],
      "parameters": {
        "width": 160,
        "height": 140,
        "content": "Choose your timeframe. How often do you want the automation to trigger?"
      },
      "typeVersion": 1
    },
    {
      "id": "d33aef2f-0d61-4bcf-be98-415f74b3a6f3",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -180,
        -240
      ],
      "parameters": {
        "width": 150,
        "height": 140,
        "content": "Set up your brand name in the JavaScript + add the queries you want to track"
      },
      "typeVersion": 1
    },
    {
      "id": "757ae3e5-7559-41e5-955a-6d00a1df51d1",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        40,
        -240
      ],
      "parameters": {
        "width": 160,
        "height": 140,
        "content": "Sending the queries to ChatGPT. I recommend using the 'chatgpt-4o-latest' model. You need to add your API key."
      },
      "typeVersion": 1
    },
    {
      "id": "7e954307-67f0-4dfd-88fc-9ec318ab8b9e",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        260,
        -240
      ],
      "parameters": {
        "width": 150,
        "height": 140,
        "content": "This JavaScript node analyzes each response received from ChatGPT."
      },
      "typeVersion": 1
    },
    {
      "id": "36c271c6-42cd-4a60-98bf-50ef2583c7c6",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        460,
        -240
      ],
      "parameters": {
        "width": 170,
        "height": 140,
        "content": "Connect your Google Sheets. Don't forget to enable the Google Sheets and Google Drive APIs in your Google Cloud project."
      },
      "typeVersion": 1
    },
    {
      "id": "2e86ab49-b359-49b7-a1de-11eab4eb39e8",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        680,
        -240
      ],
      "parameters": {
        "width": 160,
        "height": 140,
        "content": "This node turns all the raw data into a nicely formated email. No need to change anything."
      },
      "typeVersion": 1
    },
    {
      "id": "68bcb8bb-9d73-4e75-91ff-e04cc9fb1eb0",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        920,
        -240
      ],
      "parameters": {
        "width": 160,
        "height": 140,
        "content": "Finally! Replace the placeholder emails and connect yours. Using Gmail? Leave smtp.gmail.com as host."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "a3e98b7d-e020-402b-848f-c47025a37ed7": {
      "main": [
        [
          {
            "node": "645199f4-192f-407b-b842-8f3d9c85a12a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "645199f4-192f-407b-b842-8f3d9c85a12a": {
      "main": [
        [
          {
            "node": "f56df036-8e1e-46d8-81b1-fc96d6107c7f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0aa04ced-7fb9-4763-98a9-01efaf0b1a8d": {
      "main": [
        [
          {
            "node": "5734b4c6-731b-4384-b86b-6732501015d6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f56df036-8e1e-46d8-81b1-fc96d6107c7f": {
      "main": [
        [
          {
            "node": "0aa04ced-7fb9-4763-98a9-01efaf0b1a8d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e0878bda-3f73-4ba4-9845-ca472109e566": {
      "main": [
        [
          {
            "node": "7a27b9c0-7786-4f72-8aae-8f07887364a7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5734b4c6-731b-4384-b86b-6732501015d6": {
      "main": [
        [
          {
            "node": "e0878bda-3f73-4ba4-9845-ca472109e566",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}