Automated Google Ads Campaign Report to Google Sheets (with Airtable Customer Management)

• •Airtable API Key
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich

{
  "meta": {
    "instanceId": "393ca9e36a1f81b0f643c72792946a5fe5e49eb4864181ba4032e5a408278263",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "c4758e73-06d7-4bc0-a6c4-52b6a2b53a9c",
      "name": "Elemente durchlaufen",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        992,
        64
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "b3664fd2-799c-4478-9455-8d5b71cd7aa0",
      "name": "Récupération info airtable",
      "type": "n8n-nodes-base.airtable",
      "position": [
        240,
        80
      ],
      "parameters": {
        "base": {
          "__rl": true,
          "mode": "list",
          "value": "appk6h4dLarLUXKpR",
          "cachedResultUrl": "https://airtable.com/appk6h4dLarLUXKpR",
          "cachedResultName": "Base clients"
        },
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "tblTrvdyaIcsSgOWR",
          "cachedResultUrl": "https://airtable.com/appk6h4dLarLUXKpR/tblTrvdyaIcsSgOWR",
          "cachedResultName": "Projets"
        },
        "options": {},
        "operation": "search"
      },
      "credentials": {
        "airtableTokenApi": {
          "id": "9mTR7mhD0VA7GbZP",
          "name": "Airtable Personal Access Token account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "4e919d97-042a-47d8-a547-5a0266e689c1",
      "name": "Aktiv",
      "type": "n8n-nodes-base.if",
      "position": [
        512,
        80
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "273b2433-ec4f-4016-bdb1-591e005d65db",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json['Status - Prévisionnel budgétaire'] }}",
              "rightValue": "Actif"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "31348885-1dea-42ee-935c-a5c00e134fee",
      "name": "Ecommerce/Lead",
      "type": "n8n-nodes-base.switch",
      "position": [
        1568,
        80
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "outputKey": "Ecommerce",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "f1a24e58-8bd1-47c2-bd53-08e8e0f6b150",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json['Typologie ADS'] }}",
                    "rightValue": "Ecommerce"
                  }
                ]
              },
              "renameOutput": true
            },
            {
              "outputKey": "Lead",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "84e9b31e-50a6-4fe0-a05c-361f4c20bb53",
                    "operator": {
                      "name": "filter.operator.equals",
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json['Typologie ADS'] }}",
                    "rightValue": "Lead"
                  }
                ]
              },
              "renameOutput": true
            }
          ]
        },
        "options": {}
      },
      "typeVersion": 3.2
    },
    {
      "id": "8e111aeb-60f6-4447-bb63-0f1e1e628aed",
      "name": "Ecommerce-Abfrage",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2176,
        0
      ],
      "parameters": {
        "url": "=  https://googleads.googleapis.com/v21/customers/{{ $json['ID GADS'] }}/googleAds:searchStream",
        "method": "POST",
        "options": {},
        "jsonBody": "{\n  \"query\": \"SELECT campaign.advertising_channel_type, metrics.cost_micros, metrics.conversions_value FROM campaign WHERE segments.date DURING LAST_MONTH AND metrics.cost_micros > 0 ORDER BY metrics.cost_micros DESC\"\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            },
            {
              "name": "developer-token",
              "value": "Your cool token"
            },
            {
              "name": "login-customer-id",
              "value": "Your cool customer id"
            }
          ]
        },
        "nodeCredentialType": "googleAdsOAuth2Api"
      },
      "credentials": {
        "googleAdsOAuth2Api": {
          "id": "m33VGwzg8HmFr9hC",
          "name": "Google Ads account"
        }
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "id": "19b0ef3d-97b9-46e7-a9e1-d8ec74b5a6ce",
      "name": "Lead-Abfrage",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2176,
        144
      ],
      "parameters": {
        "url": "=https://googleads.googleapis.com/v21/customers/{{ $json['ID GADS'] }}/googleAds:searchStream",
        "method": "POST",
        "options": {},
        "jsonBody": "{\n  \"query\": \"SELECT campaign.advertising_channel_type, metrics.cost_micros, metrics.conversions FROM campaign WHERE segments.date DURING LAST_MONTH AND metrics.cost_micros > 0 ORDER BY metrics.cost_micros DESC\"\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            },
            {
              "name": "developer-token",
              "value": "Your cool token"
            },
            {
              "name": "login-customer-id",
              "value": "Your cool customer id"
            }
          ]
        },
        "nodeCredentialType": "googleAdsOAuth2Api"
      },
      "credentials": {
        "googleAdsOAuth2Api": {
          "id": "m33VGwzg8HmFr9hC",
          "name": "Google Ads account"
        }
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "id": "17dbc8cc-5d4c-4fc6-9716-3d57a33d1ac9",
      "name": "Ecommerce-Daten sortieren",
      "type": "n8n-nodes-base.code",
      "position": [
        2784,
        0
      ],
      "parameters": {
        "jsCode": "// Fonction pour traiter et grouper les données Google Ads (ECOMMERCE uniquement)\nfunction processGoogleAdsData(inputData) {\n  // Vérifier si nous avons la structure attendue: inputData[0].json.results\n  if (!inputData || !Array.isArray(inputData) || !inputData[0] || !inputData[0].json || !inputData[0].json.results) {\n    console.log(\"Structure non reconnue:\", JSON.stringify(inputData).substring(0, 300));\n    return {};\n  }\n  \n  const results = inputData[0].json.results;\n  \n  // Initialiser tous les types de campagnes avec des valeurs à 0\n  const campaignTypes = {\n    'PERFORMANCE_MAX': { cout: 0, conversion: 0 },\n    'SEARCH': { cout: 0, conversion: 0 },\n    'DISPLAY': { cout: 0, conversion: 0 },\n    'VIDEO': { cout: 0, conversion: 0 },\n    'DEMAND_GEN': { cout: 0, conversion: 0 },\n    'SHOPPING': { cout: 0, conversion: 0 }\n  };\n  \n  // Grouper les données par type de campagne\n  const groupedData = {};\n  \n  results.forEach(result => {\n    const channelType = result.campaign.advertisingChannelType;\n    const costEuros = parseFloat(result.metrics.costMicros) / 1000000;\n    const conversionsValue = parseFloat(result.metrics.conversionsValue || 0); // Chercher conversionsValue dans les données\n    \n    if (!groupedData[channelType]) {\n      groupedData[channelType] = {\n        totalCost: 0,\n        totalConversionsValue: 0\n      };\n    }\n    \n    groupedData[channelType].totalCost += costEuros;\n    groupedData[channelType].totalConversionsValue += conversionsValue;\n  });\n  \n  // Remplir les données pour chaque type de campagne\n  Object.keys(campaignTypes).forEach(type => {\n    if (groupedData[type]) {\n      campaignTypes[type].cout = Math.round(groupedData[type].totalCost * 100) / 100;\n      campaignTypes[type].conversion = Math.round(groupedData[type].totalConversionsValue * 100) / 100; // Valeur en euros\n    }\n    // Les valeurs restent à 0 si pas de données pour ce type\n  });\n  \n  return campaignTypes;\n}\n\n// Récupérer les données d'entrée\nconst inputData = $input.all();\nconst campaignData = processGoogleAdsData(inputData);\n\n// Retourner les données dans le format demandé\nreturn { \n  json: {\n    Performance_max: campaignData.PERFORMANCE_MAX,\n    Search: campaignData.SEARCH,\n    Display: campaignData.DISPLAY,\n    Video: campaignData.VIDEO,\n    Demand_gen: campaignData.DEMAND_GEN,\n    Shopping: campaignData.SHOPPING\n  } \n};"
      },
      "typeVersion": 2
    },
    {
      "id": "b35fc1c9-8242-4b75-bd08-ef6e0eff9018",
      "name": "Lead-Daten sortieren",
      "type": "n8n-nodes-base.code",
      "position": [
        2784,
        144
      ],
      "parameters": {
        "jsCode": "// Fonction pour traiter et grouper les données Google Ads\nfunction processGoogleAdsData(inputData) {\n  // Vérifier si nous avons la structure attendue: inputData[0].json.results\n  if (!inputData || !Array.isArray(inputData) || !inputData[0] || !inputData[0].json || !inputData[0].json.results) {\n    console.log(\"Structure non reconnue:\", JSON.stringify(inputData).substring(0, 300));\n    return {};\n  }\n\n  const results = inputData[0].json.results;\n  \n  // Initialiser tous les types de campagnes avec des valeurs à 0\n  const campaignTypes = {\n    'PERFORMANCE_MAX': { cout: 0, conversion: 0 },\n    'SEARCH': { cout: 0, conversion: 0 },\n    'DISPLAY': { cout: 0, conversion: 0 },\n    'VIDEO': { cout: 0, conversion: 0 },\n    'DEMAND_GEN': { cout: 0, conversion: 0 },\n    'SHOPPING': { cout: 0, conversion: 0 }\n  };\n\n  // Grouper les données par type de campagne\n  const groupedData = {};\n  \n  results.forEach(result => {\n    const channelType = result.campaign.advertisingChannelType;\n    const costEuros = parseFloat(result.metrics.costMicros) / 1000000;\n    const conversions = parseFloat(result.metrics.conversions);\n\n    if (!groupedData[channelType]) {\n      groupedData[channelType] = {\n        totalCost: 0,\n        totalConversions: 0\n      };\n    }\n\n    groupedData[channelType].totalCost += costEuros;\n    groupedData[channelType].totalConversions += conversions;\n  });\n\n  // Remplir les données pour chaque type de campagne\n  Object.keys(campaignTypes).forEach(type => {\n    if (groupedData[type]) {\n      campaignTypes[type].cout = Math.round(groupedData[type].totalCost * 100) / 100;\n      campaignTypes[type].conversion = Math.round(groupedData[type].totalConversions * 100) / 100;\n    }\n    // Les valeurs restent à 0 si pas de données pour ce type\n  });\n\n  return campaignTypes;\n}\n\n// Récupérer les données d'entrée\nconst inputData = $input.all();\nconst campaignData = processGoogleAdsData(inputData);\n\n// Retourner les données dans le format demandé\nreturn { \n  json: {\n    Performance_max: campaignData.PERFORMANCE_MAX,\n    Search: campaignData.SEARCH,\n    Display: campaignData.DISPLAY,\n    Video: campaignData.VIDEO,\n    Demand_gen: campaignData.DEMAND_GEN,\n    Shopping: campaignData.SHOPPING\n  } \n};"
      },
      "typeVersion": 2
    },
    {
      "id": "e3ca13a8-2d94-46b9-a6d1-43b0896f7c57",
      "name": "Ecommerce-Abfrage formatieren",
      "type": "n8n-nodes-base.code",
      "position": [
        3328,
        0
      ],
      "parameters": {
        "jsCode": "// Fonction pour obtenir la colonne correspondant au mois précédent\nfunction getPreviousMonthColumn() {\n  const now = new Date();\n  const currentMonth = now.getMonth(); // 0 = Janvier, 1 = Février, etc.\n  \n  // Calculer le mois précédent\n  let previousMonth = currentMonth - 1;\n  \n  // Si on est en janvier (0), le mois précédent est décembre (11)\n  if (previousMonth < 0) {\n    previousMonth = 11;\n  }\n  \n  // Mapping des mois vers les colonnes (0-indexed)\n  const monthToColumn = {\n    0: 'B',  // Janvier\n    1: 'C',  // Février\n    2: 'D',  // Mars\n    3: 'E',  // Avril\n    4: 'F',  // Mai\n    5: 'G',  // Juin\n    6: 'H',  // Juillet\n    7: 'I',  // Août\n    8: 'J',  // Septembre\n    9: 'K',  // Octobre\n    10: 'L', // Novembre\n    11: 'M'  // Décembre\n  };\n  \n  return monthToColumn[previousMonth];\n}\n\n// Obtenir la colonne pour le mois précédent\nconst column = getPreviousMonthColumn();\n\n// Récupérer l'URL du spreadsheet depuis le module précédent\nconst spreadsheetUrl = $('Loop Over Items').first().json['Automation budget'];\n\n// Extraire l'ID du spreadsheet depuis l'URL Google Sheets\nfunction extractSpreadsheetId(url) {\n  // Format: https://docs.google.com/spreadsheets/d/ID/edit#gid=0\n  // ou: https://docs.google.com/spreadsheets/d/ID\n  const match = url.match(/\\/spreadsheets\\/d\\/([a-zA-Z0-9-_]+)/);\n  return match ? match[1] : null;\n}\n\nconst spreadsheetId = extractSpreadsheetId(spreadsheetUrl);\n\nif (!spreadsheetId) {\n  console.log(\"Impossible d'extraire l'ID du spreadsheet depuis:\", spreadsheetUrl);\n}\n\n// Construire l'URL avec la colonne dynamique pour les lignes 2 à 11\nconst range = `${column}2:${column}11`; // CORRIGÉ : F2:F11 pour mai au lieu de F2:F3\nconst url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?valueInputOption=USER_ENTERED`;\n\n// Récupérer les données du nœud précédent (vos données Google Ads)\nconst inputData = $input.first();\n\n// Debug pour voir la structure des données\nconsole.log(\"Structure inputData:\", JSON.stringify(inputData, null, 2));\n\n// Extraire les données selon la structure réelle\nlet performanceMaxCost = 0;\nlet performanceMaxConversions = 0;\nlet demandGenCost = 0;\nlet demandGenConversions = 0;\nlet searchCost = 0;\nlet searchConversions = 0;\nlet videoCost = 0;\nlet videoConversions = 0;\nlet shoppingCost = 0;\nlet shoppingConversions = 0;\n\n// Vérifier différentes structures possibles\nif (inputData && inputData.Performance_max) {\n  // Structure directe\n  performanceMaxCost = inputData.Performance_max.cout || 0;\n  performanceMaxConversions = inputData.Performance_max.conversion || 0;\n  demandGenCost = inputData.Demand_gen.cout || 0;\n  demandGenConversions = inputData.Demand_gen.conversion || 0;\n  searchCost = inputData.Search.cout || 0;\n  searchConversions = inputData.Search.conversion || 0;\n  videoCost = inputData.Video.cout || 0;\n  videoConversions = inputData.Video.conversion || 0;\n  shoppingCost = inputData.Shopping?.cout || 0;\n  shoppingConversions = inputData.Shopping?.conversion || 0;\n} else if (inputData && inputData.json) {\n  // Structure avec json\n  performanceMaxCost = inputData.json.Performance_max?.cout || 0;\n  performanceMaxConversions = inputData.json.Performance_max?.conversion || 0;\n  demandGenCost = inputData.json.Demand_gen?.cout || 0;\n  demandGenConversions = inputData.json.Demand_gen?.conversion || 0;\n  searchCost = inputData.json.Search?.cout || 0;\n  searchConversions = inputData.json.Search?.conversion || 0;\n  videoCost = inputData.json.Video?.cout || 0;\n  videoConversions = inputData.json.Video?.conversion || 0;\n  shoppingCost = inputData.json.Shopping?.cout || 0;\n  shoppingConversions = inputData.json.Shopping?.conversion || 0;\n} else {\n  console.log(\"Structure des données non reconnue\");\n}\n\n// Construire le body avec toutes les valeurs\nconst bodyData = {\n  \"values\": [\n    [performanceMaxCost],      // Ligne 2 = coût Performance Max\n    [performanceMaxConversions], // Ligne 3 = conversions Performance Max\n    [demandGenCost],           // Ligne 4 = coût Demand Gen\n    [demandGenConversions],    // Ligne 5 = conversions Demand Gen\n    [searchCost],              // Ligne 6 = coût Search\n    [searchConversions],       // Ligne 7 = conversions Search\n    [videoCost],               // Ligne 8 = coût Video\n    [videoConversions],        // Ligne 9 = conversions Video\n    [shoppingCost],            // Ligne 10 = coût Shopping\n    [shoppingConversions]      // Ligne 11 = conversions Shopping\n  ]\n};\n\n// Retourner la configuration pour le nœud HTTP\nreturn {\n  json: {\n    url: url,\n    method: 'PUT',\n    body: JSON.stringify(bodyData),\n    range: range,\n    column: column,\n    currentMonth: new Date().toLocaleDateString('fr-FR', { month: 'long' }),\n    previousMonth: new Date(new Date().setMonth(new Date().getMonth() - 1)).toLocaleDateString('fr-FR', { month: 'long' })\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "b128af04-8aca-4870-9354-6c9a5f051911",
      "name": "Lead-Abfrage formatieren",
      "type": "n8n-nodes-base.code",
      "position": [
        3328,
        144
      ],
      "parameters": {
        "jsCode": "// Fonction pour obtenir la colonne correspondant au mois précédent\nfunction getPreviousMonthColumn() {\n  const now = new Date();\n  const currentMonth = now.getMonth(); // 0 = Janvier, 1 = Février, etc.\n  \n  // Calculer le mois précédent\n  let previousMonth = currentMonth - 1;\n  \n  // Si on est en janvier (0), le mois précédent est décembre (11)\n  if (previousMonth < 0) {\n    previousMonth = 11;\n  }\n  \n  // Mapping des mois vers les colonnes (0-indexed)\n  const monthToColumn = {\n    0: 'B',  // Janvier\n    1: 'C',  // Février\n    2: 'D',  // Mars\n    3: 'E',  // Avril\n    4: 'F',  // Mai\n    5: 'G',  // Juin\n    6: 'H',  // Juillet\n    7: 'I',  // Août\n    8: 'J',  // Septembre\n    9: 'K',  // Octobre\n    10: 'L', // Novembre\n    11: 'M'  // Décembre\n  };\n  \n  return monthToColumn[previousMonth];\n}\n\n// Obtenir la colonne pour le mois précédent\nconst column = getPreviousMonthColumn();\n\n// Récupérer l'URL du spreadsheet depuis le module précédent\nconst spreadsheetUrl = $('Loop Over Items').first().json['Automation budget'];\n\n// Extraire l'ID du spreadsheet depuis l'URL Google Sheets\nfunction extractSpreadsheetId(url) {\n  // Format: https://docs.google.com/spreadsheets/d/ID/edit#gid=0\n  // ou: https://docs.google.com/spreadsheets/d/ID\n  const match = url.match(/\\/spreadsheets\\/d\\/([a-zA-Z0-9-_]+)/);\n  return match ? match[1] : null;\n}\n\nconst spreadsheetId = extractSpreadsheetId(spreadsheetUrl);\n\nif (!spreadsheetId) {\n  console.log(\"Impossible d'extraire l'ID du spreadsheet depuis:\", spreadsheetUrl);\n}\n\n// Construire l'URL avec la colonne dynamique pour les lignes 2 à 11\nconst range = `${column}2:${column}11`; // CORRIGÉ : F2:F11 pour mai au lieu de F2:F3\nconst url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?valueInputOption=USER_ENTERED`;\n\n// Récupérer les données du nœud précédent (vos données Google Ads)\nconst inputData = $input.first();\n\n// Debug pour voir la structure des données\nconsole.log(\"Structure inputData:\", JSON.stringify(inputData, null, 2));\n\n// Extraire les données selon la structure réelle\nlet performanceMaxCost = 0;\nlet performanceMaxConversions = 0;\nlet demandGenCost = 0;\nlet demandGenConversions = 0;\nlet searchCost = 0;\nlet searchConversions = 0;\nlet videoCost = 0;\nlet videoConversions = 0;\nlet shoppingCost = 0;\nlet shoppingConversions = 0;\n\n// Vérifier différentes structures possibles\nif (inputData && inputData.Performance_max) {\n  // Structure directe\n  performanceMaxCost = inputData.Performance_max.cout || 0;\n  performanceMaxConversions = inputData.Performance_max.conversion || 0;\n  demandGenCost = inputData.Demand_gen.cout || 0;\n  demandGenConversions = inputData.Demand_gen.conversion || 0;\n  searchCost = inputData.Search.cout || 0;\n  searchConversions = inputData.Search.conversion || 0;\n  videoCost = inputData.Video.cout || 0;\n  videoConversions = inputData.Video.conversion || 0;\n  shoppingCost = inputData.Shopping?.cout || 0;\n  shoppingConversions = inputData.Shopping?.conversion || 0;\n} else if (inputData && inputData.json) {\n  // Structure avec json\n  performanceMaxCost = inputData.json.Performance_max?.cout || 0;\n  performanceMaxConversions = inputData.json.Performance_max?.conversion || 0;\n  demandGenCost = inputData.json.Demand_gen?.cout || 0;\n  demandGenConversions = inputData.json.Demand_gen?.conversion || 0;\n  searchCost = inputData.json.Search?.cout || 0;\n  searchConversions = inputData.json.Search?.conversion || 0;\n  videoCost = inputData.json.Video?.cout || 0;\n  videoConversions = inputData.json.Video?.conversion || 0;\n  shoppingCost = inputData.json.Shopping?.cout || 0;\n  shoppingConversions = inputData.json.Shopping?.conversion || 0;\n} else {\n  console.log(\"Structure des données non reconnue\");\n}\n\n// Construire le body avec toutes les valeurs\nconst bodyData = {\n  \"values\": [\n    [performanceMaxCost],      // Ligne 2 = coût Performance Max\n    [performanceMaxConversions], // Ligne 3 = conversions Performance Max\n    [demandGenCost],           // Ligne 4 = coût Demand Gen\n    [demandGenConversions],    // Ligne 5 = conversions Demand Gen\n    [searchCost],              // Ligne 6 = coût Search\n    [searchConversions],       // Ligne 7 = conversions Search\n    [videoCost],               // Ligne 8 = coût Video\n    [videoConversions],        // Ligne 9 = conversions Video\n    [shoppingCost],            // Ligne 10 = coût Shopping\n    [shoppingConversions]      // Ligne 11 = conversions Shopping\n  ]\n};\n\n// Retourner la configuration pour le nœud HTTP\nreturn {\n  json: {\n    url: url,\n    method: 'PUT',\n    body: JSON.stringify(bodyData),\n    range: range,\n    column: column,\n    currentMonth: new Date().toLocaleDateString('fr-FR', { month: 'long' }),\n    previousMonth: new Date(new Date().setMonth(new Date().getMonth() - 1)).toLocaleDateString('fr-FR', { month: 'long' })\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "cf4d7432-ad0e-4723-8d2a-f16ff06ad930",
      "name": "Ecommerce ausfüllen",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 5,
      "position": [
        3856,
        0
      ],
      "parameters": {
        "url": "={{ $json.url }}",
        "method": "PUT",
        "options": {},
        "jsonBody": "={{ $json.body }}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "wBRLUCktxqXE6DVJ",
          "name": "Google Sheets account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.2,
      "waitBetweenTries": 5000
    },
    {
      "id": "1442efc1-a364-4db8-8093-a666dbea53b3",
      "name": "Lead ausfüllen",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 5,
      "position": [
        3856,
        144
      ],
      "parameters": {
        "url": "={{ $json.url }}",
        "method": "PUT",
        "options": {},
        "jsonBody": "={{ $json.body }}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "wBRLUCktxqXE6DVJ",
          "name": "Google Sheets account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.2,
      "waitBetweenTries": 5000
    },
    {
      "id": "774d718e-621e-4e7f-888f-e00568bd165a",
      "name": "Warten",
      "type": "n8n-nodes-base.wait",
      "position": [
        4080,
        0
      ],
      "webhookId": "bd560ce5-7949-451a-86d5-94609aea5368",
      "parameters": {
        "unit": "minutes",
        "amount": 1
      },
      "typeVersion": 1.1
    },
    {
      "id": "826b1370-83e8-40ae-b575-8f0df2392b8b",
      "name": "Warten1",
      "type": "n8n-nodes-base.wait",
      "position": [
        4080,
        144
      ],
      "webhookId": "b8153fc1-ce55-40c5-a0cb-14ace51e0e48",
      "parameters": {
        "unit": "minutes",
        "amount": 1
      },
      "typeVersion": 1.1
    },
    {
      "id": "1fb19d74-b1f5-4315-8ed5-7e4219cf5000",
      "name": "Notizzettel1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -432,
        -48
      ],
      "parameters": {
        "width": 544,
        "height": 288,
        "content": "# Phase 1: Monthly Schedule Activation"
      },
      "typeVersion": 1
    },
    {
      "id": "51823372-7977-40e8-8324-579e1cd660b3",
      "name": "10. des Monats",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "10 du mois",
      "position": [
        -176,
        80
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 10 3 * *"
            }
          ]
        }
      },
      "notesInFlow": true,
      "typeVersion": 1.2
    },
    {
      "id": "1d08c787-1165-4281-b8ba-b985479fc310",
      "name": "Notizzettel3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -432,
        288
      ],
      "parameters": {
        "width": 544,
        "height": 288,
        "content": "### What the system does:\nTriggers automatically on the 3rd of each month at 10:00 AM\nInitiates monthly budget tracking workflow for all active clients\nEnsures consistent timing for financial data collection\nPrepares system for batch processing of multiple Google Ads accounts\n\n### Result:\n✅ Automated monthly execution without manual oversight\n✅ Consistent timing ensures reliable budget tracking\n✅ No missed reporting periods due to human error\n✅ Scalable scheduling for growing client base"
      },
      "typeVersion": 1
    },
    {
      "id": "d29504a1-5ef4-4dfd-8c67-4ac9be4710a0",
      "name": "Notizzettel4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        160,
        -48
      ],
      "parameters": {
        "color": 2,
        "width": 544,
        "height": 288,
        "content": "# Phase 2: Client Database Retrieval and Filtering"
      },
      "typeVersion": 1
    },
    {
      "id": "c8d9c354-25d5-4e4e-a485-d81c347c36ed",
      "name": "Notizzettel5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        160,
        288
      ],
      "parameters": {
        "color": 2,
        "width": 544,
        "height": 496,
        "content": "### What you do:\nEnsure all client information is properly configured in Airtable database beforehand\nVerify Google Ads account IDs are correctly entered for each client\nConfirm campaign typology (Ecommerce vs Lead Generation) is specified\nCheck that Google Sheets URLs for budget tracking are provided and accessible\nSet client status to \"Active\" for accounts requiring monthly budget tracking\n\n### What the system does:\nConnects to Airtable database containing client project information\nRetrieves all project records with Google Ads account details\nFilters for clients with \"Active\" budget tracking status\nValidates required fields including Google Ads IDs and spreadsheet URLs\nPrepares clean dataset of accounts requiring monthly updates\n\n### Result:\n✅ Complete active client database synchronized\n✅ Only properly configured clients processed\n✅ Missing or incorrect data prevented from causing workflow failures\n✅ Efficient resource utilization with validated client information\n✅ Targeted data collection for relevant accounts"
      },
      "typeVersion": 1
    },
    {
      "id": "5f21ead0-6816-4bef-82b7-3ef551a03ab7",
      "name": "Notizzettel6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        752,
        -48
      ],
      "parameters": {
        "color": 3,
        "width": 544,
        "height": 288,
        "content": "# Phase 3: Sequential Account Processing"
      },
      "typeVersion": 1
    },
    {
      "id": "22019478-e669-4151-8db3-9cf2aa40c30b",
      "name": "Notizzettel7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        752,
        288
      ],
      "parameters": {
        "color": 3,
        "width": 544,
        "height": 272,
        "content": "### What the system does:\nProcesses each active client account one at a time in batches\nMaintains data integrity during bulk processing\nPrepares individual account context for Google Ads API calls\nEnsures systematic handling of all qualifying accounts\n\n### Result:\n✅ Organized sequential processing of all client accounts\n✅ System stability maintained during bulk operations\n✅ Data consistency preserved throughout workflow\n✅ Scalable approach for large client portfolios"
      },
      "typeVersion": 1
    },
    {
      "id": "89441ce2-fa24-4524-b66e-41e80184d850",
      "name": "Notizzettel8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1344,
        -48
      ],
      "parameters": {
        "color": 4,
        "width": 544,
        "height": 288,
        "content": "# Phase 4: Campaign Type Classification and Data Extraction"
      },
      "typeVersion": 1
    },
    {
      "id": "00341c81-6a1c-42ce-aa63-8bdc4f8899ef",
      "name": "Notizzettel9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1344,
        288
      ],
      "parameters": {
        "color": 4,
        "width": 544,
        "height": 496,
        "content": "### What the system does:\nAnalyzes client account type (Ecommerce vs Lead Generation)\nRoutes to appropriate Google Ads API query based on business model\nExtracts campaign performance data from previous month\nApplies different metrics collection strategies per account type\n\n**For Ecommerce Accounts:**\n- Collects cost data (metrics.cost_micros) for spend tracking\n- Gathers conversion value data (metrics.conversions_value) for revenue attribution\n- Focuses on ROI and revenue-generating metrics\n\n**For Lead Generation Accounts:**\n- Collects cost data (metrics.cost_micros) for spend tracking  \n- Gathers conversion count data (metrics.conversions) for lead volume\n- Focuses on lead acquisition cost and volume metrics\n\n### Result:\n✅ Accurate business-model-specific data collection\n✅ Relevant metrics gathered for each account type\n✅ Complete previous month performance captured\n✅ Proper ROI analysis foundation established"
      },
      "typeVersion": 1
    },
    {
      "id": "322980fc-5181-401e-aefd-14a1a8053719",
      "name": "Notizzettel10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1936,
        -128
      ],
      "parameters": {
        "color": 5,
        "width": 544,
        "height": 432,
        "content": "# Phase 5: Google Ads API Data Retrieval"
      },
      "typeVersion": 1
    },
    {
      "id": "83bed6d5-39a0-4dc4-bba1-7cab6534152a",
      "name": "Notizzettel11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1936,
        352
      ],
      "parameters": {
        "color": 5,
        "width": 544,
        "height": 464,
        "content": "### What the system does:\nConnects to Google Ads API using client-specific account IDs\nExecutes campaign performance queries for the previous month\nRetrieves comprehensive campaign data by advertising channel type\nHandles API authentication and rate limiting automatically\nFilters for campaigns with actual spend (cost_micros > 0)\n\n**Campaign Types Tracked:**\n- Performance Max campaigns\n- Search campaigns  \n- Display campaigns\n- Video/YouTube campaigns\n- Demand Gen campaigns\n- Shopping campaigns\n\n### Result:\n✅ Complete campaign performance data retrieved\n✅ All major advertising channels covered\n✅ Previous month data accurately captured\n✅ API rate limits and authentication handled seamlessly"
      },
      "typeVersion": 1
    },
    {
      "id": "79b2649e-1feb-42ad-ac51-d09544e9fa12",
      "name": "Notizzettel12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2528,
        -128
      ],
      "parameters": {
        "color": 6,
        "width": 544,
        "height": 432,
        "content": "# Phase 6: Data Processing and Campaign Aggregation"
      },
      "typeVersion": 1
    },
    {
      "id": "6166bbce-8850-41e6-ab80-5917502ec881",
      "name": "Notizzettel13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2528,
        352
      ],
      "parameters": {
        "color": 6,
        "width": 544,
        "height": 464,
        "content": "### What the system does:\nProcesses raw Google Ads API responses into structured format\nAggregates campaign performance by advertising channel type\nConverts cost data from micros to standard currency format\nCalculates totals for each campaign type across the account\nHandles missing data gracefully with zero-value defaults\n\n**Data Processing Features:**\n- Automatic currency conversion (micros to euros)\n- Campaign type grouping and aggregation\n- Missing data handling with appropriate defaults\n- Decimal precision rounding for financial accuracy\n\n### Result:\n✅ Clean, structured campaign performance data\n✅ Accurate financial calculations with proper formatting\n✅ Complete coverage of all campaign types\n✅ Reliable data ready for spreadsheet integration"
      },
      "typeVersion": 1
    },
    {
      "id": "77ef6875-b3cc-4c20-9ce9-0daa90726fe7",
      "name": "Notizzettel14",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3120,
        -128
      ],
      "parameters": {
        "color": 7,
        "width": 544,
        "height": 432,
        "content": "# Phase 7: Dynamic Spreadsheet Integration Setup"
      },
      "typeVersion": 1
    },
    {
      "id": "a7360df4-4481-4590-8a9d-f13f0dbe453d",
      "name": "Notizzettel15",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3120,
        352
      ],
      "parameters": {
        "color": 7,
        "width": 544,
        "height": 464,
        "content": "### What the system does:\nCalculates correct column placement based on previous month\nDynamically determines Google Sheets column (B for January, C for February, etc.)\nExtracts spreadsheet ID from client-provided Google Sheets URLs\nConstructs proper API requests for Google Sheets batch updates\nFormats data arrays matching spreadsheet structure requirements\n\n**Smart Column Detection:**\n- Automatic month calculation and column mapping\n- Handles year transitions (December to January)\n- Maps months to spreadsheet columns (B=Jan, C=Feb, etc.)\n- Ensures data lands in correct reporting period\n\n### Result:\n✅ Accurate monthly data placement in client spreadsheets\n✅ Dynamic column detection eliminates manual configuration\n✅ Proper data structure for spreadsheet integration\n✅ Year-end transitions handled automatically\n"
      },
      "typeVersion": 1
    },
    {
      "id": "11b83dcf-f6f9-452c-8642-64534baffd71",
      "name": "Notizzettel16",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3696,
        -128
      ],
      "parameters": {
        "width": 544,
        "height": 432,
        "content": "# Phase 8: Automated Spreadsheet Updates and Rate Limiting"
      },
      "typeVersion": 1
    },
    {
      "id": "ea4f4d08-b487-46f7-affc-d7e7d51f459b",
      "name": "Notizzettel17",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3696,
        352
      ],
      "parameters": {
        "width": 544,
        "height": 512,
        "content": "### What the system does:\nUpdates client Google Sheets with campaign performance data\nUses Google Sheets API for reliable data transmission\nImplements retry logic for failed update attempts\nUpdates 10 rows of data per account (5 campaign types × 2 metrics each)\nImplements 1-minute wait periods between client account processing to prevent Google Sheets API rate limiting and quota exceeded errors\n\n**Update Structure Per Account:**\n- Row 2-3: Performance Max (cost, conversions/conversion value)\n- Row 4-5: Demand Gen (cost, conversions/conversion value)  \n- Row 6-7: Search (cost, conversions/conversion value)\n- Row 8-9: Video (cost, conversions/conversion value)\n- Row 10-11: Shopping (cost, conversions/conversion value)\n\n### Result:\n✅ Client spreadsheets automatically updated with latest performance data\n✅ Reliable data transmission with error handling and rate limiting protection\n✅ System stability maintained during bulk processing of large client portfolios\n✅ Comprehensive campaign performance tracking\n✅ Manual data entry eliminated for all clients\n✅ No service interruptions or failed executions due to API limits"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "774d718e-621e-4e7f-888f-e00568bd165a": {
      "main": [
        [
          {
            "node": "c4758e73-06d7-4bc0-a6c4-52b6a2b53a9c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4e919d97-042a-47d8-a547-5a0266e689c1": {
      "main": [
        [
          {
            "node": "c4758e73-06d7-4bc0-a6c4-52b6a2b53a9c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "826b1370-83e8-40ae-b575-8f0df2392b8b": {
      "main": [
        [
          {
            "node": "c4758e73-06d7-4bc0-a6c4-52b6a2b53a9c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "51823372-7977-40e8-8324-579e1cd660b3": {
      "main": [
        [
          {
            "node": "b3664fd2-799c-4478-9455-8d5b71cd7aa0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "19b0ef3d-97b9-46e7-a9e1-d8ec74b5a6ce": {
      "main": [
        [
          {
            "node": "b35fc1c9-8242-4b75-bd08-ef6e0eff9018",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "31348885-1dea-42ee-935c-a5c00e134fee": {
      "main": [
        [
          {
            "node": "8e111aeb-60f6-4447-bb63-0f1e1e628aed",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "19b0ef3d-97b9-46e7-a9e1-d8ec74b5a6ce",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c4758e73-06d7-4bc0-a6c4-52b6a2b53a9c": {
      "main": [
        [],
        [
          {
            "node": "31348885-1dea-42ee-935c-a5c00e134fee",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8e111aeb-60f6-4447-bb63-0f1e1e628aed": {
      "main": [
        [
          {
            "node": "17dbc8cc-5d4c-4fc6-9716-3d57a33d1ac9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1442efc1-a364-4db8-8093-a666dbea53b3": {
      "main": [
        [
          {
            "node": "826b1370-83e8-40ae-b575-8f0df2392b8b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b35fc1c9-8242-4b75-bd08-ef6e0eff9018": {
      "main": [
        [
          {
            "node": "b128af04-8aca-4870-9354-6c9a5f051911",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cf4d7432-ad0e-4723-8d2a-f16ff06ad930": {
      "main": [
        [
          {
            "node": "774d718e-621e-4e7f-888f-e00568bd165a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b128af04-8aca-4870-9354-6c9a5f051911": {
      "main": [
        [
          {
            "node": "1442efc1-a364-4db8-8093-a666dbea53b3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "17dbc8cc-5d4c-4fc6-9716-3d57a33d1ac9": {
      "main": [
        [
          {
            "node": "e3ca13a8-2d94-46b9-a6d1-43b0896f7c57",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e3ca13a8-2d94-46b9-a6d1-43b0896f7c57": {
      "main": [
        [
          {
            "node": "cf4d7432-ad0e-4723-8d2a-f16ff06ad930",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b3664fd2-799c-4478-9455-8d5b71cd7aa0": {
      "main": [
        [
          {
            "node": "4e919d97-042a-47d8-a547-5a0266e689c1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}