Decodo、GeminiでTrustpilotとSitejabberの感情分析をSheetsに保存

• •Google Sheets API認証情報
• •Google Gemini API Key

{
  "meta": {
    "instanceId": "689fa22e68cd4198e4ae37f3cc44f498087edd235a867e22515be823bab694c7",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "f5101b6f-36d6-480e-9463-bda69f3d7c57",
      "name": "Decodo レビュースクレイパー",
      "type": "@decodo/n8n-nodes-decodo.decodoTool",
      "position": [
        864,
        592
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "22fe7960-a78f-4180-884c-3e48c2d07b1c",
      "name": "Parse: 構造化されたJSONの出力",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        1104,
        592
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"business_name\": \"Shein\",\n  \"industry_field\": \"Online Fast Fashion Retail\",\n  \"company_summary\": \"Shein is a global e-commerce fashion brand offering low-cost clothing and accessories, primarily targeting young consumers worldwide.\",\n  \"trustpilot_reviews\": [\n    { \"rating\": \"4/5\", \"summary\": \"Customers like the variety and prices but complain about slow shipping.\" },\n    { \"rating\": \"3/5\", \"summary\": \"Quality can be inconsistent but returns are easy.\" }\n  ],\n  \"sitejabber_reviews\": [\n    { \"rating\": \"3.5/5\", \"summary\": \"Affordable fashion but poor customer service response times.\" },\n    { \"rating\": \"4/5\", \"summary\": \"Trendy products and frequent restocks are appreciated.\" }\n  ],\n  \"overall_sentiment\": \"Positive\",\n  \"source_links\": {\n    \"trustpilot\": \"https://www.trustpilot.com/review/shein.com\",\n    \"sitejabber\": \"https://www.sitejabber.com/reviews/shein.com\"\n  }\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "9afa03aa-e1a3-4e94-8f59-293864618877",
      "name": "LLM: 要約と推奨事項",
      "type": "@n8n/n8n-nodes-langchain.googleGemini",
      "position": [
        1392,
        288
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "models/gemini-2.5-flash",
          "cachedResultName": "models/gemini-2.5-flash"
        },
        "options": {},
        "messages": {
          "values": [
            {
              "content": "=Analyze this business reviews, if there is no review from one of the website, add a message \"There's no data in this website\"\nTrustpilot: {{ $json.output.trustpilot_reviews.toJsonString() }}\nSitejabber: {{ $json.output.sitejabber_reviews.toJsonString() }}\nResellerratings: {{ $json.output.resellerratings_reviews.toJsonString() }}\n\nGive conclusion, what to improve and overall aspect."
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "cf73f7df-2221-46cd-9645-15f830794ba3",
      "name": "Merge: ビジネスデータ + レビュー概要",
      "type": "n8n-nodes-base.merge",
      "position": [
        1744,
        368
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.2
    },
    {
      "id": "63733be1-ccb2-45ea-b2b4-8d2e71bbf994",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        128
      ],
      "parameters": {
        "width": 800,
        "height": 736,
        "content": "## Analyze Trustpilot & Sitejabber sentiment with Decodo + Gemini to Sheets\n\nSign up for Decodo [HERE](https://visit.decodo.com/discount) for Discount\n\nThis template scrapes public reviews from **Trustpilot** and **Sitejabber** with a Decodo tool, converts findings into a **flat, spreadsheet-ready JSON**, generates a concise sentiment summary with **Gemini**, and appends everything to **Google Sheets**. It’s ideal for reputation snapshots, competitive analysis, or lightweight BI pipelines that need structured data and a quick narrative.\n\n## Who’s it for?\nMarketing teams, growth analysts, founders, and agencies who need repeatable review collection and sentiment summaries without writing custom scrapers or manual copy/paste.\n\n## How it works\n1. A **Form Trigger** collects the *Business Name or URL*.\n2. **Set (Config Variables)** stores `business_name`, `spreadsheet_id`, and `sheet_id`.\n3. The **Agent** orchestrates the Decodo tool and enforces a **strict JSON schema** with at most **10 reviews per source**.\n4. **Gemini** writes a succinct summary and recommendations, noting missing sources with: “There’s no data in this website.”\n5. A **Merge** node combines JSON fields with the narrative.\n6. **Google Sheets** appends a row.\n\n## How to set up\n1. Add **Google Sheets**, **Gemini**, and **Decodo** credentials in Credential Manager.\n2. Replace `(YOUR_SPREADSHEET_ID)` and `(YOUR_SHEET_ID)` in **Set: Config Variables**.\n3. In **Google Sheets**, select **Define below** and map each column explicitly.\n4. Keep the parser and agent connections intact to guarantee flat JSON.\n5. Activate, open the form URL, submit a business, and verify the appended row.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "1d45e9cc-5f15-4743-a08c-70853e0ef3de",
      "name": "Trigger: ビジネスレビューフォーム",
      "type": "n8n-nodes-base.formTrigger",
      "position": [
        160,
        368
      ],
      "webhookId": "f8b58317-b57a-4db3-ab23-7304f9b64883",
      "parameters": {
        "options": {},
        "formTitle": "Business Review Lookup",
        "formFields": {
          "values": [
            {
              "fieldLabel": "Business Name or URL",
              "requiredField": true
            }
          ]
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "df9d8bb9-5c30-4ab6-905c-2b14c7e28f06",
      "name": "Set: 設定変数",
      "type": "n8n-nodes-base.set",
      "position": [
        464,
        368
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "b9f1e501-0234-4d32-a7ba-8287c5d3f80e",
              "name": "business_name",
              "type": "string",
              "value": "={{ $json['Business Name or URL'] }}"
            },
            {
              "id": "5f681a78-395f-4ff8-b56a-2d31d425ee15",
              "name": "=chatInput",
              "type": "string",
              "value": "=I’d like to know more about the business reputation of {{ $json['Business Name'] }}. Please summarize the company profile and collect recent customer reviews"
            },
            {
              "id": "30674f1c-a98f-4af8-b560-f8ac00fcf345",
              "name": "spreadsheet_id",
              "type": "string",
              "value": "(YOUR_SPREADSHEET_ID)"
            },
            {
              "id": "5e34efe2-8eff-4f67-a516-83ddc89c79e1",
              "name": "sheet_id",
              "type": "string",
              "value": "(YOUR_SHEET_ID)"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "cf8384fd-1f4d-4d95-b421-bca4786e7dbb",
      "name": "Agent: ビジネスレビュースキャナー",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        784,
        368
      ],
      "parameters": {
        "options": {
          "systemMessage": "=# System Message — Business Review Scanner Agent (Structured JSON Version)\n\nYou are a Business Review Scanner Agent, an intelligent assistant specialized in analyzing and summarizing customer reviews for a specific business.\n\nYour goal is to collect data and generate structured JSON output that can be directly stored in spreadsheets or databases.\n\n---\n\n## Tools\n\nDecodo Tool — used for scraping and extracting reviews only from:\n\n- Trustpilot\n- Sitejabber\n\n---\n\n## Task Description\n\nWhen the user provides a business name, you must:\n\n1. Scrape and extract available company details and reviews from the sources above.\n2. Analyze the company profile, recent customer sentiment, and summarize the key points.\n3. Return a clean, valid JSON object using the format described below.\n\n---\n\n## Output Rules\n\n1. The output must be strictly JSON (no markdown, no text outside `{}`).\n2. JSON must be flat and machine-readable, ideal for spreadsheets or database import.\n3. If a source (Trustpilot, Sitejabber, or ResellerRatings) is missing, leave the corresponding field with \"Data not found\".\n4. Include no more than 10 summarized reviews per source.\n5. Do not include raw HTML, emojis, or extra text.\n6. All fields are required — if unavailable, set the value as `null` or empty string.\n\n## Required JSON Structure\n{\n  \"business_name\": \"string\",\n  \"industry_field\": \"string\",\n  \"company_summary\": \"string\",\n  \"trustpilot_reviews\": [\n    {\n      \"rating\": \"number or string\",\n      \"summary\": \"string\"\n    }\n  ],\n  \"sitejabber_reviews\": [\n    {\n      \"rating\": \"number or string\",\n      \"summary\": \"string\"\n    }\n  ],\n  \"overall_sentiment\": \"Positive | Negative\",\n  \"source_links\": {\n    \"trustpilot\": \"string\",\n    \"sitejabber\": \"string\",\n    \"resellerratings\": \"string\"\n  }\n}\n"
        },
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "14ed0056-9c18-41af-a42b-286ad88f1b9f",
      "name": "Google Sheets: 結果を追加",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2048,
        368
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set: Config Variables').item.json.sheet_id }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set: Config Variables').item.json.spreadsheet_id }}"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.7
    },
    {
      "id": "a9747161-faff-481c-9f77-d2e3da185da0",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        272
      ],
      "parameters": {
        "color": 5,
        "width": 256,
        "height": 320,
        "content": "Use one required field: Business Name or URL."
      },
      "typeVersion": 1
    },
    {
      "id": "462eaf9a-ae9e-4034-a3c2-69ff37464f59",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        272
      ],
      "parameters": {
        "color": 5,
        "width": 288,
        "height": 320,
        "content": "Make sure to replace (YOUR_SPREADSHEET_ID) and (YOUR_SHEET_ID) with your own ID."
      },
      "typeVersion": 1
    },
    {
      "id": "b3d9e8bb-2807-4002-a4db-4081ea2b15a0",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        784,
        576
      ],
      "parameters": {
        "color": 3,
        "height": 256,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\nCommunity node; works on self-hosted n8n. Make sure to add your Decodo API credentials."
      },
      "typeVersion": 1
    },
    {
      "id": "bf88c1e3-4eaa-4cd5-8d87-4ef2bcef8cc5",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1040,
        576
      ],
      "parameters": {
        "color": 3,
        "width": 224,
        "height": 256,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\nKeep the provided schema. Output must be strictly flat JSON."
      },
      "typeVersion": 1
    },
    {
      "id": "642453e8-354f-4bc8-9653-0e1d6958579f",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1952,
        144
      ],
      "parameters": {
        "color": 5,
        "width": 304,
        "height": 400,
        "content": "Google Sheets columns for mapping (suggested)\n- business_name, \n- industry_field, \n- company_summary, \n- overall_sentiment,\n- summary_text,\n- trustpilot_url, \n- sitejabber_url"
      },
      "typeVersion": 1
    },
    {
      "id": "de54cc4b-1674-4642-94d0-7d1633d6437f",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        0
      ],
      "parameters": {
        "color": 3,
        "width": 480,
        "height": 96,
        "content": "> **Disclaimer:** This workflow relies on a **community Decodo node** and therefore requires **self-hosted n8n**. Install the node from the community marketplace and configure the Decodo API credential."
      },
      "typeVersion": 1
    },
    {
      "id": "3bf1fcb6-d7fc-4535-8bad-623f6c94b29c",
      "name": "付箋7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        128
      ],
      "parameters": {
        "color": 4,
        "width": 2288,
        "height": 736,
        "content": "## Analyze Business Reputation (Trustpilot + Sitejabber) with Gemini and Decodo\n\nSelf-hosted template using a community Decodo node. Scrapes reviews, produces flat JSON, summarizes with Gemini, and appends results to Google Sheets."
      },
      "typeVersion": 1
    },
    {
      "id": "f242c6fd-079b-41f0-9797-1b153f9a6671",
      "name": "AI Model: Google Gemini (Agent)",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        656,
        656
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.5-pro"
      },
      "typeVersion": 1
    },
    {
      "id": "4490602f-a6be-4f8e-b60c-54d263093af4",
      "name": "Set: 出力フィールド",
      "type": "n8n-nodes-base.set",
      "position": [
        1136,
        368
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "5fd2f317-d25e-478c-a013-37cf49f68170",
              "name": "output.business_name",
              "type": "string",
              "value": "={{ $json.output.business_name }}"
            },
            {
              "id": "b643e944-69f2-4a30-b5ed-4cd21d225d14",
              "name": "output.industry_field",
              "type": "string",
              "value": "={{ $json.output.industry_field }}"
            },
            {
              "id": "767fa26e-2227-4a2f-92fd-ee5946c9ef75",
              "name": "output.company_summary",
              "type": "string",
              "value": "={{ $json.output.company_summary }}"
            },
            {
              "id": "c4b6165b-c697-41d1-a0e2-7980d1ce1d66",
              "name": "output.trustpilot_reviews",
              "type": "array",
              "value": "={{ $json.output.trustpilot_reviews }}"
            },
            {
              "id": "5ce03ddd-cfc2-4c1e-9f63-17af186e654e",
              "name": "output.sitejabber_reviews",
              "type": "array",
              "value": "={{ $json.output.sitejabber_reviews }}"
            },
            {
              "id": "17713d7e-4434-4897-8fc5-d6fdaf0acf13",
              "name": "output.overall_sentiment",
              "type": "string",
              "value": "={{ $json.output.overall_sentiment }}"
            },
            {
              "id": "0e570aa8-b046-48f1-a1c1-28cb598a136b",
              "name": "output.source_links",
              "type": "object",
              "value": "={{ $json.output.source_links }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    }
  ],
  "pinData": {},
  "connections": {
    "4490602f-a6be-4f8e-b60c-54d263093af4": {
      "main": [
        [
          {
            "node": "9afa03aa-e1a3-4e94-8f59-293864618877",
            "type": "main",
            "index": 0
          },
          {
            "node": "cf73f7df-2221-46cd-9645-15f830794ba3",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "f5101b6f-36d6-480e-9463-bda69f3d7c57": {
      "ai_tool": [
        [
          {
            "node": "cf8384fd-1f4d-4d95-b421-bca4786e7dbb",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "df9d8bb9-5c30-4ab6-905c-2b14c7e28f06": {
      "main": [
        [
          {
            "node": "cf8384fd-1f4d-4d95-b421-bca4786e7dbb",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "22fe7960-a78f-4180-884c-3e48c2d07b1c": {
      "ai_outputParser": [
        [
          {
            "node": "cf8384fd-1f4d-4d95-b421-bca4786e7dbb",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "1d45e9cc-5f15-4743-a08c-70853e0ef3de": {
      "main": [
        [
          {
            "node": "df9d8bb9-5c30-4ab6-905c-2b14c7e28f06",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cf8384fd-1f4d-4d95-b421-bca4786e7dbb": {
      "main": [
        [
          {
            "node": "4490602f-a6be-4f8e-b60c-54d263093af4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f242c6fd-079b-41f0-9797-1b153f9a6671": {
      "ai_languageModel": [
        [
          {
            "node": "cf8384fd-1f4d-4d95-b421-bca4786e7dbb",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "9afa03aa-e1a3-4e94-8f59-293864618877": {
      "main": [
        [
          {
            "node": "cf73f7df-2221-46cd-9645-15f830794ba3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cf73f7df-2221-46cd-9645-15f830794ba3": {
      "main": [
        [
          {
            "node": "14ed0056-9c18-41af-a42b-286ad88f1b9f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}