毎日 Google広告の動向を Notion と Google スプシッドシートに同期

• •Notion API Key
• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "1ff3f71877f94a07ce0b138fce26fa00489eae8aeeb1e8b837f52d5baca65402",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5ff5e3a4-baf5-4cbf-a75d-6d36a82a4e56",
      "name": "付箋ノート12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        760
      ],
      "parameters": {
        "color": 3,
        "width": 640,
        "height": 720,
        "content": "\n#### 2. G-Ads Query Conversion\n\n**Purpose**: Fetches bottom-of-funnel metrics (results and ROI).\n\n**Query:**\n\n```sql\nSELECT\n  campaign.id,\n  campaign.name,\n  metrics.conversions,\n  segments.conversion_action_name,\n  segments.date\nFROM campaign\nWHERE segments.date = '{{$json.yesterday}}'\n```\n\n**Returns:**\n\n* `metrics.conversions`: Number of conversions (e.g. purchase, lead)\n* `segments.conversion_action_name`: Type of conversion action\n* `segments.date`: The reporting date\n\n**Use case**: Track campaign outcomes and effectiveness.\n\n---\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4e646849-8389-4ee5-95d1-f1d50437c623",
      "name": "付箋ノート11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -100,
        760
      ],
      "parameters": {
        "color": 3,
        "width": 640,
        "height": 740,
        "content": "### Google Ads Click vs Conversion (HTTP POST)\n\n***\nThis workflow includes two HTTP Request nodes that send POST requests to the Google Ads API via the `googleAds:search` endpoint. Both use GAQL to query yesterday's campaign data, but each focuses on different metrics.\n***\n\n---\n\n#### 1. G-Ads Query Click\n\n**Purpose**: Fetches top-of-funnel metrics (traffic and cost).\n\n**Query:**\n```sql\nSELECT\n  campaign.id,\n  campaign.name,\n  metrics.impressions,\n  metrics.clicks,\n  metrics.cost_micros,\n  segments.date\nFROM campaign\nWHERE segments.date = '{{$json.yesterday}}'\n````\n\n**Returns:**\n\n* `metrics.impressions`: Number of times ads were shown\n* `metrics.clicks`: Number of ad clicks\n* `metrics.cost_micros`: Ad cost in micros (÷ 1,000,000)\n* `segments.date`: The reporting date\n\n**Use case**: Monitor ad visibility, engagement, and spending.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "33ddcd1d-f6f7-437c-b920-f8b3e17ffa3b",
      "name": "G-Ads Query Click",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        380,
        400
      ],
      "parameters": {
        "url": "https://googleads.googleapis.com/v20/customers/{{YOUR_CUSTOMER_ID}}/googleAds:search",
        "method": "POST",
        "options": {},
        "jsonBody": "={{ \n  {\n    query: `SELECT \n      campaign.id, \n      campaign.name, \n      metrics.impressions, \n      metrics.clicks, \n      metrics.cost_micros, \n      segments.date \n    FROM campaign \n    WHERE segments.date = '${$json.yesterday}'`\n  }\n}}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "developer-token"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            },
            {
              "name": "login-customer-id"
            }
          ]
        },
        "nodeCredentialType": "googleAdsOAuth2Api"
      },
      "credentials": {},
      "typeVersion": 4.2
    },
    {
      "id": "58d2857b-fbe7-4906-9878-985a99426f29",
      "name": "G-Ads Query Conversion",
      "type": "n8n-nodes-base.httpRequest",
      "onError": "continueRegularOutput",
      "position": [
        380,
        600
      ],
      "parameters": {
        "url": "https://googleads.googleapis.com/v20/customers/{{YOUR_CUSTOMER_ID}}/googleAds:search",
        "method": "POST",
        "options": {},
        "jsonBody": "={{ \n  {\n    query: `SELECT \n      campaign.id,\n      campaign.name,\n      metrics.conversions,\n      segments.conversion_action_name,\n      segments.date \n    FROM campaign \n    WHERE segments.date = '${$json.yesterday}'`\n  }\n}}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "headerParameters": {
          "parameters": [
            {
              "name": "developer-token"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            },
            {
              "name": "login-customer-id"
            }
          ]
        },
        "nodeCredentialType": "googleAdsOAuth2Api"
      },
      "credentials": {},
      "typeVersion": 4.2
    },
    {
      "id": "a6009cf3-afc3-4539-b58f-d8e3138564d5",
      "name": "Notion1",
      "type": "n8n-nodes-base.notion",
      "onError": "continueRegularOutput",
      "position": [
        1200,
        400
      ],
      "parameters": {
        "options": {},
        "resource": "databasePage",
        "databaseId": {
          "__rl": true,
          "mode": "id",
          "value": ""
        },
        "propertiesUi": {
          "propertyValues": [
            {
              "key": "Campaign Name|title",
              "title": "={{ $json.campaign.name }}"
            },
            {
              "key": "Campaign ID|number",
              "numberValue": "={{ parseInt($json.campaign.id) }}"
            },
            {
              "key": "Impressions|number",
              "numberValue": "={{ parseInt($json.metrics.impressions)}}"
            },
            {
              "key": "Clicks|number",
              "numberValue": "={{ parseInt($json.metrics.clicks) }}"
            },
            {
              "key": "Cost|number",
              "numberValue": "={{ Math.floor($json.metrics.costMicros / 1000000) }}"
            },
            {
              "key": "Conversion Type|rich_text",
              "textContent": "={{ $json.segments.conversionActionName || \"N/A\" }}"
            },
            {
              "key": "Conversions|number",
              "numberValue": "={{ Math.round(Number($json.metrics.conversions || 0) * 100) / 100 }}"
            },
            {
              "key": "Date|date",
              "date": "={{ $json.segments.date }}"
            }
          ]
        }
      },
      "credentials": {},
      "typeVersion": 2.2
    },
    {
      "id": "049a0fc6-bf5b-4c82-96c7-da45563d1017",
      "name": "Notion2",
      "type": "n8n-nodes-base.notion",
      "onError": "continueRegularOutput",
      "position": [
        1460,
        760
      ],
      "parameters": {
        "options": {},
        "resource": "databasePage",
        "databaseId": {
          "__rl": true,
          "mode": "id",
          "value": ""
        },
        "propertiesUi": {
          "propertyValues": [
            {
              "key": "Tanggal|date",
              "date": "={{ $json.date }}"
            },
            {
              "key": "Total Impressions|number",
              "numberValue": "={{ $json.total_impressions }}"
            },
            {
              "key": "Total Clicks|number",
              "numberValue": "={{ $json.total_clicks }}"
            },
            {
              "key": "Total Conversions|number",
              "numberValue": "={{ $json.total_conversions }}"
            },
            {
              "key": "Total Cost|number",
              "numberValue": "={{ $json.total_cost }}"
            },
            {
              "key": "Conversion Types|rich_text",
              "textContent": "={{ $json.conversion_types }}"
            }
          ]
        }
      },
      "credentials": {},
      "typeVersion": 2.2
    },
    {
      "id": "9e331b5a-2162-404c-9918-50b1f8a0c14b",
      "name": "Google スプレッドシート10",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1200,
        600
      ],
      "parameters": {
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "id",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": ""
        }
      },
      "credentials": {},
      "typeVersion": 4.5
    },
    {
      "id": "59bd6861-0ceb-42a1-a85d-0d239dc607d5",
      "name": "Split Click2",
      "type": "n8n-nodes-base.code",
      "onError": "continueRegularOutput",
      "position": [
        660,
        400
      ],
      "parameters": {
        "jsCode": "return items[0].json.results.map(r => ({ json: r }));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "154beaed-97e3-42eb-8c36-34487cda7e0c",
      "name": "Split Conversion2",
      "type": "n8n-nodes-base.code",
      "onError": "continueRegularOutput",
      "position": [
        660,
        600
      ],
      "parameters": {
        "jsCode": "return items[0].json.results.map(r => ({ json: r }));"
      },
      "typeVersion": 2
    },
    {
      "id": "7a981181-0a99-4446-bcc4-042b7b7e9dbe",
      "name": "マージ2",
      "type": "n8n-nodes-base.merge",
      "position": [
        900,
        500
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "advanced": true,
        "joinMode": "enrichInput1",
        "mergeByFields": {
          "values": [
            {
              "field1": "campaign.id",
              "field2": "campaign.id"
            },
            {
              "field1": "segments.date",
              "field2": "segments.date"
            }
          ]
        }
      },
      "typeVersion": 3.1
    },
    {
      "id": "29a7436f-61e9-42f7-bdfe-7a4b50f45d36",
      "name": "Daily Recap2",
      "type": "n8n-nodes-base.code",
      "position": [
        1200,
        860
      ],
      "parameters": {
        "jsCode": "let totalClicks = 0;\nlet totalImpressions = 0;\nlet totalCost = 0;\nlet totalConversions = 0;\nlet conversionTypes = new Set();\nlet date = null;\n\nfor (const item of items) {\n  const d = item.json;\n\n  totalClicks += parseInt(d.metrics?.clicks || 0);\n  totalImpressions += parseInt(d.metrics?.impressions || 0);\n  totalCost += parseInt(d.metrics?.costMicros || 0) / 1_000_000;\n  totalConversions += parseFloat(d.metrics?.conversions || 0);\n\n  const convType = d.segments?.conversionActionName;\n  if (convType) conversionTypes.add(convType);\n\n  date = d.segments?.date || date;\n}\n\nreturn [\n  {\n    json: {\n      date,\n      total_clicks: totalClicks,\n      total_impressions: totalImpressions,\n      total_cost: Number(totalCost.toFixed(2)),\n      total_conversions: Number(totalConversions.toFixed(2)),\n      conversion_types: Array.from(conversionTypes).join(', ') || 'N/A'\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "2708ffda-f942-4e55-990d-baeac0f1d8f3",
      "name": "Google スプレッドシート11",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1480,
        960
      ],
      "parameters": {
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "id",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": ""
        }
      },
      "credentials": {},
      "typeVersion": 4.5
    },
    {
      "id": "3be681d5-6941-443d-8485-2603107190f4",
      "name": "付箋ノート10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1760,
        600
      ],
      "parameters": {
        "color": 4,
        "width": 640,
        "height": 460,
        "content": "\n## Output Summary\n\n**Notion Databases:**\n- `Google Ads Campaign Tracker`: stores individual campaign metrics\n- `Google Ads Daily Summary`: stores daily totals and conversion types\n\n**Google Sheets Tabs:**\n- `Campaign Daily Report`: per-campaign data\n- `Summary Report`: aggregated daily performance"
      },
      "typeVersion": 1
    },
    {
      "id": "9be455f9-4fe9-4bdd-882f-4ec4ec212f36",
      "name": "付箋ノート9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1000,
        0
      ],
      "parameters": {
        "color": 3,
        "width": 640,
        "height": 460,
        "content": "\n\n### 3. Notion Database Setup\nCreate two databases in Notion:\n- **Google Ads Campaign Tracker**\n  - Fields: `Campaign Name`, `Campaign ID`, `Impressions`, `Clicks`, `Cost`, `Conversion Type`, `Conversions`, `Date`\n- **Google Ads Daily Summary**\n  - Fields: `Date`, `Total Impressions`, `Total Clicks`, `Total Conversions`, `Total Cost`, `Conversion Types`\n- Share both databases with your Notion integration\n\n### 4. Google Sheets Setup\n- Create a spreadsheet with two tabs:\n  - `Campaign Daily Report` → for campaign-level rows\n  - `Summary Report` → for daily aggregated metrics\n- Match all column headers to the workflow fields\n- Connect your Google account to n8n using **Google Sheets OAuth2**\n\n---"
      },
      "typeVersion": 1
    },
    {
      "id": "b5865a91-1a2e-43d2-869d-6b321a94abea",
      "name": "付箋ノート8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        -100
      ],
      "parameters": {
        "color": 3,
        "width": 640,
        "height": 480,
        "content": "---\n\n## Setup Steps\n\n### 1. Schedule the Workflow\n- The workflow is triggered using a `Schedule Trigger` node\n- Set the schedule to run **every day at 08:00 AM**\n- Connect it to the `Set Yesterday Date` node\n\n### 2. Google Ads API Access\n- Create a Google Ads developer account and obtain a **developer token**\n- Set up OAuth2 credentials with Google Ads scope\n- In n8n, configure the **Google Ads OAuth2 API** credential\n- Ensure HTTP request headers include:\n  - `developer-token`\n  - `login-customer-id`\n  - `Content-Type: application/json`\n- To retrieve data, make a `POST` request to:\nhttps://googleads.googleapis.com/v20/customers/{customerId}/googleAds:search\nReplace `{customerId}` with the client account ID (not the MCC ID)"
      },
      "typeVersion": 1
    },
    {
      "id": "9aeaf3f7-798f-4ca9-b7e1-6ffad5ead9d1",
      "name": "付箋ノート",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -780,
        620
      ],
      "parameters": {
        "width": 640,
        "height": 800,
        "content": "## How It Works\n\n1. **Scheduled Trigger at 08:00 AM**  \n   The workflow begins with a `Schedule Trigger` node that runs once per day at 08:00.\n\n2. **Set Yesterday’s Date**  \n   The `Set` node defines a variable for the target date (yesterday), which is used in the API queries.\n\n3. **Query Google Ads API – Clicks & Cost**  \n   The first HTTP request pulls campaign-level metrics:\n   - `campaign.id`, `campaign.name`  \n   - `metrics.clicks`, `metrics.impressions`, `metrics.cost_micros`\n\n4. **Query Google Ads API – Conversions**  \n   The second HTTP request pulls conversion-related data:\n   - `metrics.conversions`, `segments.conversion_action_name`\n\n5. **Split and Merge**  \n   Both responses are split into individual campaign rows and merged using:\n   - `campaign.id`  \n   - `segments.date`  \n\n6. **Store Campaign-Level Data**  \n   - Stored in Notion database: **\"Google Ads Campaign Tracker\"**  \n   - Appended to Google Sheets tab: **\"Campaign Daily Report\"**\n\n7. **Generate Daily Summary**  \n   A code node calculates daily totals across all campaigns:\n   - Total impressions, clicks, conversions, cost\n   - Unique conversion types  \n   The summary is stored in:\n   - Notion database: **\"Google Ads Daily Summary\"**  \n   - Google Sheets tab: **\"Summary Report\"**\n"
      },
      "typeVersion": 1
    },
    {
      "id": "c0b96c74-15f6-4cea-8727-e1a9c5c0a9d1",
      "name": "スケジュールトリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        500
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 8
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "be1b4d73-99b2-4fe6-a1a3-34df0eada594",
      "name": "設定 Yesterday Date2",
      "type": "n8n-nodes-base.set",
      "position": [
        180,
        500
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "bfc7fb43-2b0f-4f44-a239-b35583c4d114",
              "name": "=yesterday",
              "type": "string",
              "value": "={{ $now.minus({ days: 1 }).toFormat('yyyy-MM-dd') }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "79d493b6-2e71-45e7-8c05-a6f20e8b9b55",
      "name": "付箋ノート7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -780,
        120
      ],
      "parameters": {
        "width": 640,
        "height": 460,
        "content": "## Description\n\n\nThis workflow automates the daily reporting of Google Ads campaign performance. It pulls click and conversion data from the Google Ads API, merges both datasets, and stores the results into Notion databases and Google Sheets.\n\nIt includes a campaign-level log and a daily performance summary. The workflow is triggered automatically every day at **08:00 AM**, helping marketing teams maintain a consistent and centralized reporting system without manual effort.\n\n---\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "Merge2": {
      "main": [
        [
          {
            "node": "Google Sheets10",
            "type": "main",
            "index": 0
          },
          {
            "node": "29a7436f-61e9-42f7-bdfe-7a4b50f45d36",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "29a7436f-61e9-42f7-bdfe-7a4b50f45d36": {
      "main": [
        [
          {
            "node": "049a0fc6-bf5b-4c82-96c7-da45563d1017",
            "type": "main",
            "index": 0
          },
          {
            "node": "Google Sheets11",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "59bd6861-0ceb-42a1-a85d-0d239dc607d5": {
      "main": [
        [
          {
            "node": "Merge2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Set Yesterday Date2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "33ddcd1d-f6f7-437c-b920-f8b3e17ffa3b": {
      "main": [
        [
          {
            "node": "59bd6861-0ceb-42a1-a85d-0d239dc607d5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "154beaed-97e3-42eb-8c36-34487cda7e0c": {
      "main": [
        [
          {
            "node": "Merge2",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Set Yesterday Date2": {
      "main": [
        [
          {
            "node": "58d2857b-fbe7-4906-9878-985a99426f29",
            "type": "main",
            "index": 0
          },
          {
            "node": "33ddcd1d-f6f7-437c-b920-f8b3e17ffa3b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "58d2857b-fbe7-4906-9878-985a99426f29": {
      "main": [
        [
          {
            "node": "154beaed-97e3-42eb-8c36-34487cda7e0c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}