Source/UTM Attribution and Reporting

{
  "id": "PwuNENpHebhFo0Jr",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Source/UTM Attribution and Reporting",
  "tags": [],
  "nodes": [
    {
      "id": "86c3479e-c819-470a-8bac-971f6df5e098",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -2352,
        -336
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "7f59cac1-886e-4819-8fa9-2e8279e4ff2e",
      "name": "Send Follow-up Email1",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -1104,
        -336
      ],
      "webhookId": "0c82c299-6938-42ed-bda6-5007d79af34f",
      "parameters": {
        "sendTo": "[email protected]",
        "message": "={{ $json.text }}",
        "options": {},
        "subject": "weekely reports"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "a8d3bed1-c8ad-4d74-841b-12e324219b3b",
      "name": "Get row(s) in sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -2128,
        -336
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1254124532,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1BqdjkHQrA9Pz_td4N9H7pL5T8Poh0cL8ERXN_N3ogeU/edit#gid=1254124532",
          "cachedResultName": "Form responses 1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1BqdjkHQrA9Pz_td4N9H7pL5T8Poh0cL8ERXN_N3ogeU",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1BqdjkHQrA9Pz_td4N9H7pL5T8Poh0cL8ERXN_N3ogeU/edit?usp=drivesdk",
          "cachedResultName": "Source/UTM Attribution and Reporting( testing)"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ptC5XVqswYJ3rH2d",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4
    },
    {
      "id": "776392c7-f956-4095-90fe-d49dbfdbe698",
      "name": "Basic LLM Chain",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        -1536,
        -336
      ],
      "parameters": {
        "text": "=Create a professional **Weekly Lead Attribution Report** in **responsive HTML format** from the following JSON data:\n\n{{ JSON.stringify($json, null, 2) }}\n\nRequirements:\n- Use modern, clean card-style layout\n- Include sections: Summary, Key Metrics, Top Sources, Cost per Lead (CPL), Insights, Priority Actions\n- Dynamically fill in values from the JSON\n- Add light background, rounded boxes, and simple table for metrics\n- Make it mobile-friendly for email\n- Show CPL per source in a clear table (e.g., Source → Leads → Spend → CPL)\n- Use emojis for clarity but keep business-professional tone\n- Output **only HTML**, no markdown or explanations\n",
        "batching": {},
        "messages": {
          "messageValues": [
            {
              "message": "You are a professional business reporting assistant. Create clear, well-formatted daily reports for business managers. Use emojis and proper formatting for mobile messaging apps like Telegram."
            }
          ]
        },
        "promptType": "define"
      },
      "typeVersion": 1.7
    },
    {
      "id": "e95e0a73-1706-4efc-9999-8779eb4a1d13",
      "name": "Azure OpenAI Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatAzureOpenAi",
      "position": [
        -1472,
        -128
      ],
      "parameters": {
        "model": "gpt-4o-mini",
        "options": {}
      },
      "credentials": {
        "azureOpenAiApi": {
          "id": "C3WzT18XqF8OdVM6",
          "name": "Azure Open AI account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "62f8fcae-06a3-4206-9349-488c521069b1",
      "name": "Code",
      "type": "n8n-nodes-base.code",
      "position": [
        -1744,
        -336
      ],
      "parameters": {
        "jsCode": "// Aggregate all leads into one summary object\nconst input = $input.all().map(i => i.json);\n\n// Count total leads\nconst total = input.length;\n\n// Count per source\nconst sourceCount = {};\nfor (const lead of input) {\n  const source = (lead[\"Source \"] || \"unknown\").trim().toLowerCase();\n  sourceCount[source] = (sourceCount[source] || 0) + 1;\n}\n\nreturn [\n  {\n    json: {\n      total_leads: total,\n      sources: sourceCount,\n      leads: input // keep full leads if needed\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "c6591637-1bc5-4652-bad8-9a92f4d09e85",
      "name": "Code1",
      "type": "n8n-nodes-base.code",
      "position": [
        -1936,
        -336
      ],
      "parameters": {
        "jsCode": "const inputData = $input.all();\n\nlet sources = {};\nlet spend = {\n  instagram: 0,\n  linkedin: 0,\n  googleads: 0\n};\n\nfor (const row of inputData) {\n  const data = row.json;\n\n  // Normalize keys\n  const normalized = {};\n  for (let key in data) {\n    normalized[key.toLowerCase().trim()] = data[key];\n  }\n\n  const src = (normalized[\"source\"] || \"\").toLowerCase().trim();\n\n  if (!sources[src]) sources[src] = 0;\n  sources[src] += 1;\n\n  // Accumulate spend\n  if (src === \"instagram\" && normalized[\"spend_instagram\"]) {\n    spend.instagram += Number(normalized[\"spend_instagram\"]);\n  }\n  if (src === \"linkedin\" && normalized[\"spend_linkedin\"]) {\n    spend.linkedin += Number(normalized[\"spend_linkedin\"]);\n  }\n  if (src === \"googleads\" && normalized[\"spend_googleads\"]) {\n    spend.googleads += Number(normalized[\"spend_googleads\"]);\n  }\n}\n\n// Calculate CPL\nlet cpl = {};\nfor (let src in sources) {\n  cpl[src] = sources[src] > 0 && spend[src] > 0 \n    ? (spend[src] / sources[src]).toFixed(2) \n    : \"N/A\";\n}\n\nreturn [{\n  total_leads: inputData.length,\n  sources,\n  spend,\n  cpl\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "70a272de-518c-49c2-ae09-f83e939ed29b",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2432,
        -560
      ],
      "parameters": {
        "content": "Schedule Trigger\n\nRuns the workflow at a set interval (currently every hour).\n\nActs as the starting point to fetch new or updated lead data."
      },
      "typeVersion": 1
    },
    {
      "id": "608beaec-9cef-43ee-8c1c-c4b240949e6a",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2192,
        -160
      ],
      "parameters": {
        "color": 2,
        "height": 192,
        "content": "Get row(s) in sheet\n\nPulls lead data from your Google Sheet (Form responses 1).\n\nThis includes Name, Email, Phone, Source, and (if added) spend columns."
      },
      "typeVersion": 1
    },
    {
      "id": "248007d3-fcc3-4a34-825b-fd11ad624cd7",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2000,
        -656
      ],
      "parameters": {
        "color": 3,
        "height": 272,
        "content": "Code1\n\nFirst processing step.\n\nNormalizes the sheet data (fixes column names like “Source ”).\n\nAggregates lead counts per source.\n\nCollects spend values (Instagram, LinkedIn, Google Ads).\n\nPrepares a JSON object with total leads, sources, spend, and CPL (cost per lead)."
      },
      "typeVersion": 1
    },
    {
      "id": "5f408388-ebc6-4874-91b2-d322de28dc2e",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1808,
        -160
      ],
      "parameters": {
        "color": 4,
        "height": 240,
        "content": "Code\n\nFurther aggregates leads into one summary object.\n\nKeeps a clean version of the total leads, source counts, and full lead list.\n\nThis ensures only one report is generated instead of per lead row"
      },
      "typeVersion": 1
    },
    {
      "id": "6b886b25-6d37-4d5a-80f5-609a5be393b5",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1536,
        -640
      ],
      "parameters": {
        "color": 5,
        "height": 256,
        "content": "Basic LLM Chain\n\nTakes the aggregated JSON from Code → prompts the AI.\n\nGenerates a professional Weekly Lead Attribution Report in responsive HTML.\n\nIncludes Summary, Key Metrics, Top Sources, CPL, Insights, and Actions."
      },
      "typeVersion": 1
    },
    {
      "id": "c46b65a1-ff48-474b-9ac1-580c3ae7290a",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1520,
        16
      ],
      "parameters": {
        "height": 192,
        "content": "Azure OpenAI Chat Model1\n\nThe actual LLM engine (using GPT-4o-mini via Azure OpenAI).\n\nProvides language understanding and text generation capability for your LLM chain."
      },
      "typeVersion": 1
    },
    {
      "id": "6d233ab6-695d-484f-81ab-d1a38b64c0c4",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1168,
        -176
      ],
      "parameters": {
        "color": 6,
        "height": 240,
        "content": "Send Follow-up Email1\n\nSends the generated HTML report via Gmail.\n\nRecipient\n\nSubject: “weekely reports”.\n\nBody: the dynamic HTML report from the LLM node."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "bcb5ca10-c6b7-43fe-a3cc-bec61e595b24",
  "connections": {
    "Code": {
      "main": [
        [
          {
            "node": "Basic LLM Chain",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code1": {
      "main": [
        [
          {
            "node": "Code",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Basic LLM Chain": {
      "main": [
        [
          {
            "node": "Send Follow-up Email1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Get row(s) in sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get row(s) in sheet": {
      "main": [
        [
          {
            "node": "Code1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Follow-up Email1": {
      "main": [
        []
      ]
    },
    "Azure OpenAI Chat Model1": {
      "ai_languageModel": [
        [
          {
            "node": "Basic LLM Chain",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  }
}