{ "edges": [], "nodes": [ { "id": "0286c917-d771-4835-a5f8-71f79a5e59e8", "name": "List Files", "type": "integration", "params": { "filter": { "folderId": { "__rl": true, "mode": "list", "value": "", "cachedResultUrl": "", "cachedResultName": "" } }, "options": {}, "service": "googleDrive", "resource": "fileFolder", "operation": "read", "searchMethod": "query" }, "position": [ -100, -800 ] }, { "id": "7d9b0c0a-86ee-4aae-8d73-66f409b0a57f", "name": "OpenAI Chat Model", "type": "passthrough", "params": { "mode": "noOp", "model": { "__rl": true, "mode": "list", "value": "gpt-4o", "cachedResultName": "gpt-4o" }, "options": {} }, "position": [ 1620, -540 ] }, { "id": "d3d58b0a-3107-4525-92a8-d54332e9a8a5", "name": "is XLSX", "type": "if", "params": { "options": {}, "conditions": { "options": { "version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "820b48a1-676d-400b-894f-3b3a5203eca7", "operator": { "type": "string", "operation": "contains" }, "leftValue": "={{ $json.name }}", "rightValue": ".xlsx" } ] } }, "position": [ 540, -800 ] }, { "id": "884e4a08-3b19-4485-aba7-c69887607b82", "name": "Get File", "type": "integration", "params": { "fileId": { "__rl": true, "mode": "id", "value": "={{ $json.id }}" }, "options": { "binaryPropertyName": "data", "googleFileConversion": { "conversion": {} } }, "service": "googleDrive", "operation": "download" }, "position": [ 100, -800 ] }, { "id": "c72fde38-de38-4734-a7e8-aa70e8638cad", "name": "Merge XLSX and CSV", "type": "merge", "params": {}, "position": [ 1200, -800 ] }, { "id": "cd23e23c-9bb7-4b8d-90ab-8917783cf1ab", "name": "Format Data", "type": "code", "params": { "jsCode": "const result = {};\n\nfor (const item of items) {\n const fileName = item.json.fileName || item.json.name || 'unknown_file';\n const baseName = fileName\n .split('.')[0]\n .replace(/\\s+/g, '_')\n .toLowerCase()\n .replace(/\\s*\$\\d+\$$/, '')\n .replace(/_+$/, '')\n .trim();\n\n // regex → result key\n const map = [\n { key: 'search_terms', regex: /search_term/ },\n { key: 'campaigns', regex: /campaign/ },\n { key: 'targeting', regex: /targeting/ },\n { key: 'placement', regex: /placement/ },\n { key: 'budgets', regex: /budget/ },\n ];\n\n const entry = map.find(m => m.regex.test(baseName));\n const mappedKey = entry ? entry.key : null;\n\n console.log('fileName:', fileName);\n console.log('baseName:', baseName);\n console.log('mappedKey:', mappedKey);\n\n if (!mappedKey) {\n throw new Error(`${fileName} → ${baseName} → Unrecognized file name structure`);\n }\n result[mappedKey] = result[mappedKey] || [];\n result[mappedKey].push(item.json);\n}\n\nreturn [{ json: result }];\n\n\n\n" }, "position": [ 1420, -800 ] }, { "id": "02172577-d867-45a4-96ea-eb105169deff", "name": "Set fileName", "type": "set", "params": { "options": { "dotNotation": true, "ignoreConversionErrors": false }, "assignments": { "assignments": [ { "id": "a467fabb-d7d0-482d-8a6a-afcd97cc0d8c", "name": "fileName", "type": "string", "value": "={{ $json.name }}" } ] }, "includeOtherFields": true }, "position": [ 320, -800 ] }, { "id": "e5a24705-0ad5-4629-b183-d279bdca8b29", "name": "Preserve File Name", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "d6883fe9-d04f-4c86-bc9a-f4dd526afca2", "name": "fileName", "type": "string", "value": "={{ $('is XLSX').item.json.fileName }}" } ] }, "includeOtherFields": true }, "position": [ 980, -900 ] }, { "id": "3c315a0c-a89e-490a-9a82-e3d96d2b94c7", "name": "Email Optimizations", "type": "integration", "params": { "sendTo": "={{ $('Email Options').first().json.send_to }}", "message": "={{\n (() => {\n let raw = $node[\"AI Analyze\"].json[\"text\"];\n\n // 🔧 Remove triple backticks and optional \"json\" tag\n raw = raw.replace(/^```json\\s*/i, \"\").replace(/```$/, \"\").trim();\n\n let data;\n\n try {\n data = JSON.parse(raw);\n } catch (err) {\n return `

❌ Failed to parse AI output.
${err.message}

`;\n }\n\n let msg = \"

Amazon Ads Optimization Instructions

\";\n\n // Optional Summary Totals\n const totalSpend = (data.campaign_adjustments || []).reduce((sum, c) => sum + (c.projected_daily_spend_usd || 0), 0);\n const totalSales = (data.campaign_adjustments || []).reduce((sum, c) => sum + (c.projected_daily_sales_usd || 0), 0);\n msg += `

Total Budget Increase Recommended:
`;\n msg += `Estimated daily spend: $${totalSpend.toFixed(2)}
`;\n msg += `Estimated daily sales: $${totalSales.toFixed(2)}

`;\n\n // Campaign Adjustments\n msg += \"

Campaign Adjustments:

${c.campaign_name}
- Default bid × ${c.default_bid_multiplier} (–${percent}%)
- Bid adjustments:
  - Top of Search: ${c.bid_adjustments.top_of_search ?? 0}%
  - Rest of Search: ${c.bid_adjustments.rest_of_search ?? 0}%
  - Product pages: ${c.bid_adjustments.product_pages ?? 0}%
- Budget: ${c.budget_change.action} by ${c.budget_change.percent}%
- Est. daily spend: $${c.projected_daily_spend_usd.toFixed(2)}
- Est. daily sales: $${c.projected_daily_sales_usd.toFixed(2)}
- ACoS: ${c.estimated_acos_percent}%
- ROAS: ${c.estimated_roas_multiple.toFixed(2)}x

\";\n\n // Keyword Recommendations\n if ((data.keyword_recommendations?.add_exact?.length || 0) > 0 ||\n (data.keyword_recommendations?.negative?.length || 0) > 0) {\n msg += \"

Keyword Recommendations:

Add exact: \"${k.term}\" in ${k.campaign_name} / ${k.ad_group_name} at $${k.suggested_bid}
Negative: \"${n}\"
Negative: \"${n.term}\" in ${n.campaign_name || 'Unspecified Campaign'}

\";\n }\n\n // Targeting Recommendations\n if ((data.targeting_recommendations || []).length > 0) {\n msg += \"

Targeting Recommendations:

${t.target} in ${t.campaign_name} / ${t.ad_group_name}: ${t.action}${valueText}

\";\n }\n\n return msg;\n })()\n}}\n", "options": {}, "service": "gmail", "subject": "={{ $('Email Options').first().json.subject }}", "operation": "read" }, "position": [ 2016, -800 ] }, { "id": "f4fc0a70-2df9-4b7b-b60c-856b1b74ead7", "name": "Extract XLSX Data", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "operation": "xlsx" }, "position": [ 760, -900 ] }, { "id": "d0618a5b-1995-474d-a969-38e856b1b91a", "name": "Extract CSV Data", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "binaryPropertyName": "=data" }, "position": [ 760, -700 ] }, { "id": "67f9d0a2-2f34-416a-bc11-ef776e6e4ab3", "name": "Preserve CSV File Name", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "d6883fe9-d04f-4c86-bc9a-f4dd526afca2", "name": "fileName", "type": "string", "value": "={{ $('is XLSX').item.json.fileName }}" } ] }, "includeOtherFields": true }, "position": [ 980, -700 ] }, { "id": "818205c9-0fe9-4fe6-8556-657f087ba7b9", "name": "When clicking ‘Test workflow’", "type": "manualTrigger", "params": {}, "position": [ -500, -800 ] }, { "id": "158da856-b682-4f98-afcc-4fa12b978db0", "name": "Email Options", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "60c2189a-2ca3-43ac-bffc-371bbc3c123b", "name": "send_to", "type": "string", "value": "" }, { "id": "c6f588b3-b8b9-4a83-817b-a68de36d2570", "name": "subject", "type": "string", "value": "" } ] } }, "position": [ -300, -800 ] }, { "id": "ca2f4a7c-5aa9-4f6a-bc04-aedce5e0aaed", "name": "AI Analyze", "type": "passthrough", "params": { "mode": "noOp", "text": "={{JSON.stringify($json)}}", "messages": { "messageValues": [ { "message": "You are an Amazon Ads Optimization Assistant. You will receive five structured datasets from Sponsored Products reports:\n- search_terms\n- campaigns\n- targeting\n- placement\n- budgets\n\nYour goal is to generate precise performance recommendations for bid strategy, targeting, and budget scaling.\n\n---\n\n1. Campaign Adjustments:\nFor each campaign, return:\n- campaign_name (string)\n- default_bid_multiplier (float, optional — only if bid should change)\n- bid_adjustments: { top_of_search, rest_of_search, product_pages } (percentages)\n- budget_change: { action: increase | decrease | none, percent: float }\n- projected_daily_spend_usd (float)\n- projected_daily_sales_usd (float)\n- estimated_acos_percent (float)\n- estimated_roas_multiple (float)\n\nBase projections on historical 30-day data. If a budget increase is recommended, scale projected spend and sales proportionally. Return NaN only if data is insufficient.\n\n---\n\n2. Keyword Recommendations:\nRecommend at least 5 exact-match keywords to add. Each must include:\n- term\n- campaign_name\n- ad_group_name\n- suggested_bid (USD)\n\nAlso return at least 3 negative keywords:\n- { term: \"...\", campaign_name?: \"...\" }\n\nDo not return keyword recommendations that lack campaign and ad group names.\n\n---\n\n3. Targeting Recommendations:\nRecommend at least 3 targets to pause or increase bids. Return:\n- target (ASIN, keyword, or match group)\n- campaign_name\n- ad_group_name\n- action: \"pause\" or \"increase_bid\"\n- value: float (if increasing bid)\n\n---\n\nRespond ONLY with a JSON object in this exact format. Do NOT include backticks, code blocks, or explanations:\n\n{\n \"campaign_adjustments\": [...],\n \"keyword_recommendations\": {\n \"add_exact\": [...],\n \"negative\": [...]\n },\n \"targeting_recommendations\": [...]\n}\n\n" } ] }, "promptType": "define" }, "position": [ 1640, -800 ] }, { "id": "error-b4018fc8", "name": "Error Handler", "type": "passthrough", "params": { "mode": "noOp", "message": "Workflow execution error", "options": {} }, "position": [ 1000, 400 ] } ] }

Amazon Ads Optimization with OpenAI and Drive