{"id":"Agn9dzf5YTqcmQGN","meta":{"instanceId":"workflow-bd879e7b","versionId":"1.0.0","createdAt":"2025-09-29T07:07:43.154377","updatedAt":"2025-09-29T07:07:43.154389","owner":"n8n-user","license":"MIT","category":"automation","status":"active","priority":"high","environment":"production"},"name":"Amazon Ads AI Optimization","nodes":[{"id":"0286c917-d771-4835-a5f8-71f79a5e59e8","name":"List Files","type":"n8n-nodes-base.googleDrive","position":[-100,-800],"parameters":{"filter":{"folderId":{"__rl":true,"mode":"list","value":"","cachedResultUrl":"","cachedResultName":""}},"options":{},"resource":"fileFolder","searchMethod":"query"},"credentials":{"googleDriveOAuth2Api":{"id":"UPKjIF2z8RkkmP21","name":"Google Drive account"}},"typeVersion":3,"notes":"This googleDrive node performs automated tasks as part of the workflow."},{"id":"7d9b0c0a-86ee-4aae-8d73-66f409b0a57f","name":"OpenAI Chat Model","type":"n8n-nodes-base.noOp","position":[1620,-540],"parameters":{"model":{"__rl":true,"mode":"list","value":"gpt-4o","cachedResultName":"gpt-4o"},"options":{}},"credentials":{"openAiApi":{"id":"qszlkCg3ypMJEWvt","name":"OpenAi account"}},"typeVersion":1,"notes":"This lmChatOpenAi node performs automated tasks as part of the workflow."},{"id":"d3d58b0a-3107-4525-92a8-d54332e9a8a5","name":"is XLSX","type":"n8n-nodes-base.if","position":[540,-800],"parameters":{"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"}]}},"typeVersion":2.2,"notes":"This if node performs automated tasks as part of the workflow."},{"id":"884e4a08-3b19-4485-aba7-c69887607b82","name":"Get File","type":"n8n-nodes-base.googleDrive","position":[100,-800],"parameters":{"fileId":{"__rl":true,"mode":"id","value":"={{ $json.id }}"},"options":{"binaryPropertyName":"data","googleFileConversion":{"conversion":{}}},"operation":"download"},"credentials":{"googleDriveOAuth2Api":{"id":"UPKjIF2z8RkkmP21","name":"Google Drive account"}},"typeVersion":3,"notes":"This googleDrive node performs automated tasks as part of the workflow."},{"id":"c72fde38-de38-4734-a7e8-aa70e8638cad","name":"Merge XLSX and CSV","type":"n8n-nodes-base.merge","position":[1200,-800],"parameters":{},"typeVersion":3.1,"notes":"This merge node performs automated tasks as part of the workflow."},{"id":"cd23e23c-9bb7-4b8d-90ab-8917783cf1ab","name":"Format Data","type":"n8n-nodes-base.code","position":[1420,-800],"parameters":{"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"},"typeVersion":2,"notes":"This code node performs automated tasks as part of the workflow."},{"id":"02172577-d867-45a4-96ea-eb105169deff","name":"Set fileName","type":"n8n-nodes-base.set","position":[320,-800],"parameters":{"options":{"dotNotation":true,"ignoreConversionErrors":false},"assignments":{"assignments":[{"id":"a467fabb-d7d0-482d-8a6a-afcd97cc0d8c","name":"fileName","type":"string","value":"={{ $json.name }}"}]},"includeOtherFields":true},"typeVersion":3.4,"notes":"This set node performs automated tasks as part of the workflow."},{"id":"31db008f-20e4-4fe3-a9d0-1815b3802690","name":"Sticky Note2","type":"n8n-nodes-base.stickyNote","position":[-140,-1040],"parameters":{"color":3,"width":180,"height":200,"content":"## Change\nChoose the \"folder\" in the filter options to the folder containing your Ad reports\n"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"0ba8c273-8369-4009-9b93-b0fb243a3c85","name":"Sticky Note3","type":"n8n-nodes-base.stickyNote","position":[1640,-1000],"parameters":{"width":260,"content":"## AI Analysis\nUses GPT-4o to process the bundled reports and generate optimization instructions.\nPasses system instructions and cleaned data as input."},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"451bb016-1766-4688-aafc-75937e0d5c3f","name":"Sticky Note5","type":"n8n-nodes-base.stickyNote","position":[-660,-580],"parameters":{"width":540,"height":700,"content":"## Amazon Ads Report Scheduling Instructions\nTo run this workflow, schedule the following Sponsored Products reports in the Amazon Ads Console:\n\nUse \"Detailed\" for:\n\nSearch Term Report → Sponsored_Products_Search_Term_Detailed_L30\n\nTargeting Report → Sponsored_Products_Targeting_Detailed_L30\n\nUse \"Summary\" for:\n\nCampaign Report → Sponsored_Products_Campaign_L30\n\nPlacement Report → Sponsored_Products_Placement_L30\n\nBudget Report → Sponsored_Products_Budget_L30\n\nShared settings for all reports:\n\nDate Range: Last 30 Days\n\nFrequency: Daily\n\nFormat: .xlsx or .csv\n\nDelivery: Email + Console Download\n\nMake sure filenames match expectations so the workflow can route them correctly."},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"a671a4f1-05b0-4d7c-9cc1-8c2838593e34","name":"Sticky Note6","type":"n8n-nodes-base.stickyNote","position":[-60,-580],"parameters":{"width":400,"height":520,"content":"## Report Delivery\n\nHow to get reports into Google Drive\n\nUse one of the following:\n\n📥 Manual Upload – Download emailed reports and move them to your Drive folder\n\n🤖 Automation – Use n8n to watch Gmail for no-reply@amazon.com, extract attachments, and upload to Drive\n\n💻 Drive Sync Folder – Use a local folder synced to Google Drive with rules for report types\n\nReports must match expected filenames so the flow can identify and classify them."},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"63a7f391-2bc7-41f9-a53f-e742950c60bf","name":"Sticky Note7","type":"n8n-nodes-base.stickyNote","position":[360,-580],"parameters":{"width":360,"height":520,"content":"## Upgrade! 🚀\n\nApply for an Amazon Advertising API developer account to unlock full automation:\n\nGenerate reports programmatically via the Reports API\n\nFetch report files directly into n8n using HTTP or custom nodes\n\nEliminate email + Drive dependency entirely\n\n🔗 {{ $env.API_BASE_URL }}\n\nOnce approved, you can schedule report generation and download all required data securely and automatically.\n**Double click** to edit me. [Guide]({{ $env.WEBHOOK_URL }}"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"e5a24705-0ad5-4629-b183-d279bdca8b29","name":"Preserve File Name","type":"n8n-nodes-base.set","position":[980,-900],"parameters":{"options":{},"assignments":{"assignments":[{"id":"d6883fe9-d04f-4c86-bc9a-f4dd526afca2","name":"fileName","type":"string","value":"={{ $('is XLSX').item.json.fileName }}"}]},"includeOtherFields":true},"typeVersion":3.4,"notes":"This set node performs automated tasks as part of the workflow."},{"id":"3c315a0c-a89e-490a-9a82-e3d96d2b94c7","name":"Email Optimizations","type":"n8n-nodes-base.gmail","position":[2016,-800],"webhookId":"b9d7c1a9-a1a3-4b97-97c9-a272f0e97127","parameters":{"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:

\";\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:

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

Targeting Recommendations:

\";\n }\n\n return msg;\n })()\n}}\n","options":{},"subject":"={{ $('Email Options').first().json.subject }}"},"credentials":{"gmailOAuth2":{"id":"6m7O3IpXy4mCRogW","name":"Brian Gmail"}},"typeVersion":2.1,"notes":"This gmail node performs automated tasks as part of the workflow."},{"id":"f4fc0a70-2df9-4b7b-b60c-856b1b74ead7","name":"Extract XLSX Data","type":"n8n-nodes-base.extractFromFile","position":[760,-900],"parameters":{"options":{},"operation":"xlsx"},"typeVersion":1,"notes":"This extractFromFile node performs automated tasks as part of the workflow."},{"id":"d0618a5b-1995-474d-a969-38e856b1b91a","name":"Extract CSV Data","type":"n8n-nodes-base.extractFromFile","position":[760,-700],"parameters":{"options":{},"binaryPropertyName":"=data"},"typeVersion":1,"notes":"This extractFromFile node performs automated tasks as part of the workflow."},{"id":"67f9d0a2-2f34-416a-bc11-ef776e6e4ab3","name":"Preserve CSV File Name","type":"n8n-nodes-base.set","position":[980,-700],"parameters":{"options":{},"assignments":{"assignments":[{"id":"d6883fe9-d04f-4c86-bc9a-f4dd526afca2","name":"fileName","type":"string","value":"={{ $('is XLSX').item.json.fileName }}"}]},"includeOtherFields":true},"typeVersion":3.4,"notes":"This set node performs automated tasks as part of the workflow."},{"id":"818205c9-0fe9-4fe6-8556-657f087ba7b9","name":"When clicking ‘Test workflow’","type":"n8n-nodes-base.manualTrigger","position":[-500,-800],"parameters":{},"typeVersion":1,"notes":"This manualTrigger node performs automated tasks as part of the workflow."},{"id":"1612753d-0b7f-4ae5-9ec0-8ad39f1003b1","name":"Sticky Note","type":"n8n-nodes-base.stickyNote","position":[-580,-1040],"parameters":{"width":220,"content":"## Trigger\nYou may replace this with a scheduled event or poll the folder for changes."},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"158da856-b682-4f98-afcc-4fa12b978db0","name":"Email Options","type":"n8n-nodes-base.set","position":[-300,-800],"parameters":{"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":""}]}},"typeVersion":3.4,"notes":"This set node performs automated tasks as part of the workflow."},{"id":"4f1f251e-5cfb-468d-9531-9c2ba2c875f6","name":"Sticky Note1","type":"n8n-nodes-base.stickyNote","position":[-320,-1040],"parameters":{"color":3,"width":160,"content":"## Change!\nEdit these email options."},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"ca2f4a7c-5aa9-4f6a-bc04-aedce5e0aaed","name":"AI Analyze","type":"n8n-nodes-base.noOp","position":[1640,-800],"parameters":{"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"},"typeVersion":1,"notes":"This chainLlm node performs automated tasks as part of the workflow."},{"id":"error-b4018fc8","name":"Error Handler","type":"n8n-nodes-base.stopAndError","typeVersion":1,"position":[1000,400],"parameters":{"message":"Workflow execution error","options":{}}}],"active":false,"settings":{"executionOrder":"v1","saveManualExecutions":true,"callerPolicy":"workflowsFromSameOwner","errorWorkflow":null,"timezone":"UTC","executionTimeout":3600,"maxExecutions":1000,"retryOnFail":true,"retryCount":3,"retryDelay":1000},"versionId":"286aae2a-f8df-489d-9f03-89d0b50b1800","connections":{"List Files":{"main":[[]]},"OpenAI Chat Model":{"main":[[]]},"Get File":{"main":[[]]},"Extract XLSX Data":{"main":[[]]},"Extract CSV Data":{"main":[[]]}},"description":"Automated workflow: Amazon Ads AI Optimization. This workflow integrates 12 different services: stickyNote, code, chainLlm, googleDrive, merge. It contains 27 nodes and follows best practices for error handling and security.","notes":"Excellent quality workflow: Amazon Ads AI Optimization. This workflow has been optimized for production use with comprehensive error handling, security, and documentation."}