Aggregation von Marketing-Ausgaben in Google Sheets mithilfe von benutzerdefinierten Pivot-Tabellen und VLOOKUP

Name: Aggregation von Marketing-Ausgaben in Google Sheets mithilfe von benutzerdefinierten Pivot-Tabellen und VLOOKUP
Rating: 4.5 (10 reviews)
Author: Robert Breen

Fortgeschritten

Dies ist ein Document Extraction, Multimodal AI-Bereich Automatisierungsworkflow mit 10 Nodes. Hauptsächlich werden Merge, Summarize, GoogleSheets, ManualTrigger und andere Nodes verwendet. Verwenden Sie benutzerdefinierte Pivot-Tables und VLOOKUP in Google Sheets, um Marketing-Ausgabendaten zu aggregieren

Voraussetzungen

•Google Sheets API-Anmeldedaten

Verwendete Nodes (10)

Kategorie

Dokumentenextraktion

Multimodales KI

Workflow-Vorschau

Visualisierung der Node-Verbindungen, mit Zoom und Pan

Bei Klick auf 'Workflow ausführen'

Tabelle leeren

Pivot

Marketingdaten abrufen

VLOOKUP-Daten

Tabellen zusammenführen (VLOOKUP)

Pivot-Table-ähnliche Tabelle erstellen

React Flow

Workflow exportieren

Kopieren Sie die folgende JSON-Konfiguration und importieren Sie sie in n8n

{
  "meta": {
    "instanceId": "ad0113c344ee237399e44e9f11798b05baeb83a6196d514a9ae9d2ad71c3b5c9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5432b2ed-adde-4021-b6b3-e75e43bd102c",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1744,
        4816
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "20d77255-29ba-45aa-af1b-ff2b8ba91ef6",
      "name": "Tabelle leeren",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -240,
        4944
      ],
      "parameters": {
        "operation": "clear",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1235077339,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit#gid=1235077339",
          "cachedResultName": "render pivot"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?usp=drivesdk",
          "cachedResultName": "Pivot Data"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "43298259-36ab-403d-91f8-0d0a499b6daf",
      "name": "Pivot",
      "type": "n8n-nodes-base.summarize",
      "position": [
        -464,
        5040
      ],
      "parameters": {
        "options": {},
        "fieldsToSplitBy": "Name",
        "fieldsToSummarize": {
          "values": [
            {
              "field": "Spend ($)",
              "aggregation": "sum"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "ea502cf7-be4a-41ac-a1e0-38f73398c95e",
      "name": "Marketingdaten abrufen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1008,
        4944
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit#gid=0",
          "cachedResultName": "data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?usp=drivesdk",
          "cachedResultName": "Pivot Data"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "896e5698-f99a-4e1b-ad4c-f5c4c07e847e",
      "name": "VLOOKUP-Daten",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1280,
        5408
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 894339285,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit#gid=894339285",
          "cachedResultName": "Lookup"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?usp=drivesdk",
          "cachedResultName": "Pivot Data"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "4178ff0a-9cbb-4eef-93d8-2a012471f0ed",
      "name": "Tabellen zusammenführen (VLOOKUP)",
      "type": "n8n-nodes-base.merge",
      "position": [
        -688,
        5040
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "fieldsToMatchString": "Channel"
      },
      "typeVersion": 3.2
    },
    {
      "id": "3e622961-1081-40cd-870e-c3b0d114d3c0",
      "name": "Pivot-Table-ähnliche Tabelle erstellen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -224,
        5152
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "sum_Spend_($)",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "sum_Spend_($)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1235077339,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit#gid=1235077339",
          "cachedResultName": "render pivot"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?usp=drivesdk",
          "cachedResultName": "Pivot Data"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "c007458e-07ec-4b25-b898-272ce0fcde8f",
      "name": "Notizzettel55",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1808,
        4608
      ],
      "parameters": {
        "color": 7,
        "width": 2144,
        "height": 1056,
        "content": "### Build a pivot-style marketing spend summary in Google Sheets using n8n (Merge + Summarize + Vlookup)\n\nThis workflow transforms raw marketing data from Google Sheets into a **pivot-like summary table**. It merges lookup data, groups spend by name, and appends the results into a clean reporting tab — all automatically, without needing to manually build pivot tables in Sheets.\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d004d740-9e2d-409a-b5df-960bd28482a7",
      "name": "Notizzettel3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2240,
        4608
      ],
      "parameters": {
        "width": 400,
        "height": 1056,
        "content": "\n## 🔑 Setup Instructions\n\n### 1) Connect Google Sheets (OAuth2)\n1. In **n8n → Credentials → New → Google Sheets (OAuth2)**  \n2. Sign in with your Google account and grant access  \n3. In each Google Sheets node, select your **Spreadsheet** and the appropriate **Worksheet**:  \n   - **data** (raw spend)  \n   - **Lookup** (channel reference table)  \n   - **render pivot** (output tab)  \n\nhttps://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?gid=894339285#gid=894339285\n\n## 📬 Contact  \nNeed help customizing this (e.g., filtering by campaign, sending reports by email, or formatting your pivot)?  \n\n- 📧 **rbreen@ynteractive.com**  \n- 🔗 **[Robert Breen](https://www.linkedin.com/in/robert-breen-29429625/)**  \n- 🌐 **[ynteractive.com](https://ynteractive.com)**  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "2694e9b7-3405-4139-99bc-5ffd735f33a8",
      "name": "Notizzettel65",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1344,
        4880
      ],
      "parameters": {
        "color": 3,
        "width": 224,
        "height": 656,
        "content": "### 1) Connect Google Sheets (OAuth2)\n1. In **n8n → Credentials → New → Google Sheets (OAuth2)**  \n2. Sign in with your Google account and grant access  \n3. In each Google Sheets node, select your **Spreadsheet** and the appropriate **Worksheet**:  \n   - **data** (raw spend)  \n   - **Lookup** (channel reference table)  \n   - **render pivot** (output tab)  \n\nhttps://docs.google.com/spreadsheets/d/1aO6fhd-P55lXy9QuKs23Eifi1SzZ2GAFj0q0Qx4vl30/edit?gid=894339285#gid=894339285\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "43298259-36ab-403d-91f8-0d0a499b6daf": {
      "main": [
        [
          {
            "node": "20d77255-29ba-45aa-af1b-ff2b8ba91ef6",
            "type": "main",
            "index": 0
          },
          {
            "node": "3e622961-1081-40cd-870e-c3b0d114d3c0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "896e5698-f99a-4e1b-ad4c-f5c4c07e847e": {
      "main": [
        [
          {
            "node": "4178ff0a-9cbb-4eef-93d8-2a012471f0ed",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "ea502cf7-be4a-41ac-a1e0-38f73398c95e": {
      "main": [
        [
          {
            "node": "4178ff0a-9cbb-4eef-93d8-2a012471f0ed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4178ff0a-9cbb-4eef-93d8-2a012471f0ed": {
      "main": [
        [
          {
            "node": "43298259-36ab-403d-91f8-0d0a499b6daf",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5432b2ed-adde-4021-b6b3-e75e43bd102c": {
      "main": [
        [
          {
            "node": "ea502cf7-be4a-41ac-a1e0-38f73398c95e",
            "type": "main",
            "index": 0
          },
          {
            "node": "896e5698-f99a-4e1b-ad4c-f5c4c07e847e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Häufig gestellte Fragen

Wie verwende ich diesen Workflow?

Kopieren Sie den obigen JSON-Code, erstellen Sie einen neuen Workflow in Ihrer n8n-Instanz und wählen Sie "Aus JSON importieren". Fügen Sie die Konfiguration ein und passen Sie die Anmeldedaten nach Bedarf an.

Für welche Szenarien ist dieser Workflow geeignet?

Fortgeschritten - Dokumentenextraktion, Multimodales KI

Ist es kostenpflichtig?

Dieser Workflow ist völlig kostenlos. Beachten Sie jedoch, dass Drittanbieterdienste (wie OpenAI API), die im Workflow verwendet werden, möglicherweise kostenpflichtig sind.