Smartlead 至 HubSpot 性能分析
高级
这是一个Sales, Marketing领域的自动化工作流，包含 23 个节点。主要使用 If, Set, Code, Merge, Hubspot 等节点。 Smartlead 至 HubSpot 性能分析
前置要求
- •HubSpot API Key
- •PostgreSQL 数据库连接信息
- •可能需要目标 API 的认证凭证
- •Google Sheets API 凭证
使用的节点 (23)
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"meta": {
"instanceId": "a2435d996b378e3a6fdef0468d70285e3aa0fbd0004de817bfc80e80afee4e7b"
},
"nodes": [
{
"id": "8a4ba8b8-b76e-4572-becd-e7f8fbea2651",
"name": "提取营销活动数据",
"type": "n8n-nodes-base.httpRequest",
"position": [
500,
960
],
"parameters": {
"url": "=https://server.smartlead.ai/api/v1/campaigns/{{ $json.id }}/leads-export",
"options": {
"batching": {
"batch": {
"batchSize": 0
}
}
},
"sendQuery": true,
"queryParameters": {
"parameters": [
{
"name": "api_key",
"value": "={{ $json['API KEY'] }}"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "90011ed6-180d-4170-8932-ac3aa7d0e5df",
"name": "获取所有营销活动",
"type": "n8n-nodes-base.httpRequest",
"position": [
-20,
940
],
"parameters": {
"url": "https://server.smartlead.ai/api/v1/campaigns",
"options": {
"batching": {
"batch": {
"batchSize": 0
}
}
},
"sendQuery": true,
"queryParameters": {
"parameters": [
{
"name": "api_key",
"value": "={{ $json['API KEY'] }}"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "c41afcf1-9256-47fa-ad99-3e1af880e53d",
"name": "遍历项目",
"type": "n8n-nodes-base.splitInBatches",
"position": [
200,
940
],
"parameters": {
"options": {
"reset": "={{ $node['Loop Over Items'].context[\"done\"] }}"
}
},
"typeVersion": 3
},
{
"id": "606bfc18-1d70-4d64-ac70-ae6f42bf0dbb",
"name": "更新营销活动",
"type": "n8n-nodes-base.postgres",
"position": [
720,
1220
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "ce_campaign",
"cachedResultName": "ce_campaign"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "outbound_activities",
"cachedResultName": "outbound_activities"
},
"columns": {
"value": {
"name": "={{ $json.name }}",
"status": "={{ $json.status }}",
"user_id": "={{ $json.user_id }}",
"client_id": "={{ $json.client_id }}",
"created_at": "={{ $json.created_at }}",
"updated_at": "={{ $json.updated_at }}",
"campaign_id": "={{ $json.id }}",
"track_settings": "={{ $json.track_settings }}",
"unsubscribe_text": "={{ $json.unsubscribe_text }}",
"max_leads_per_day": "={{ $json.max_leads_per_day }}",
"parent_campaign_id": "={{ $json.parent_campaign_id }}",
"send_as_plain_text": "={{ $json.send_as_plain_text }}",
"stop_lead_settings": "={{ $json.stop_lead_settings }}",
"follow_up_percentage": "={{ $json.follow_up_percentage }}",
"min_time_btwn_emails": "={{ $json.min_time_btwn_emails }}",
"scheduler_cron_value": "={{ $json.scheduler_cron_value }}",
"enable_ai_esp_matching": "={{ $json.enable_ai_esp_matching }}",
"psg_last_update_timestamp": "={{ $now }}"
},
"schema": [
{
"id": "campaign_id",
"type": "number",
"display": true,
"removed": false,
"required": true,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "user_id",
"type": "number",
"display": true,
"required": false,
"displayName": "user_id",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "created_at",
"type": "dateTime",
"display": true,
"required": true,
"displayName": "created_at",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "updated_at",
"type": "dateTime",
"display": true,
"required": true,
"displayName": "updated_at",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "status",
"type": "string",
"display": true,
"required": true,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "track_settings",
"type": "array",
"display": true,
"required": false,
"displayName": "track_settings",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "scheduler_cron_value",
"type": "object",
"display": true,
"required": false,
"displayName": "scheduler_cron_value",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "min_time_btwn_emails",
"type": "number",
"display": true,
"required": false,
"displayName": "min_time_btwn_emails",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "max_leads_per_day",
"type": "number",
"display": true,
"required": false,
"displayName": "max_leads_per_day",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "stop_lead_settings",
"type": "string",
"display": true,
"required": false,
"displayName": "stop_lead_settings",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "enable_ai_esp_matching",
"type": "boolean",
"display": true,
"required": false,
"displayName": "enable_ai_esp_matching",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "send_as_plain_text",
"type": "boolean",
"display": true,
"required": false,
"displayName": "send_as_plain_text",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "follow_up_percentage",
"type": "number",
"display": true,
"required": false,
"displayName": "follow_up_percentage",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "unsubscribe_text",
"type": "string",
"display": true,
"required": false,
"displayName": "unsubscribe_text",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "parent_campaign_id",
"type": "number",
"display": true,
"required": false,
"displayName": "parent_campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "client_id",
"type": "number",
"display": true,
"required": false,
"displayName": "client_id",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "psg_last_update_timestamp",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "psg_last_update_timestamp",
"defaultMatch": false,
"canBeUsedToMatch": false
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"campaign_id"
]
},
"options": {
"queryBatching": "independently"
},
"operation": "upsert"
},
"credentials": {
"postgres": {
"id": "z7VPpa7mFIGKNewM",
"name": "Postgres Aikido"
}
},
"typeVersion": 2.5
},
{
"id": "b9f61fd6-9327-428e-9e78-4ca0779476ea",
"name": "合并",
"type": "n8n-nodes-base.merge",
"position": [
1220,
980
],
"parameters": {
"mode": "combine",
"options": {},
"combineBy": "combineByPosition"
},
"typeVersion": 3
},
{
"id": "b8c1082d-a12f-4e56-af8c-73641b45da67",
"name": "代码",
"type": "n8n-nodes-base.code",
"notes": "// Retrieve the CSV-like data from the 'data' field in the input\nconst csvData = $json['data']; // Ensure that 'data' is the correct field name\n\n// Check if csvData exists and is not empty\nif (!csvData) {\n console.log(\"Input data structure:\", $json); // Debugging output to inspect input structure\n throw new Error('No CSV data provided. Ensure the correct field reference is being used.');\n}\n\n// Split the CSV into rows\nconst rows = csvData.split('\\n');\n\n// Extract the headers\nconst headers = rows[0].replace(/\"/g, '').split(',');\n\n// Iterate over each data row and map it to an object\nconst output = rows.slice(1).map(row => {\n const values = row.match(/(\".*?\"|[^\",]+)(?=\\s*,|\\s*$)/g).map(value => {\n // Remove surrounding quotes from each value if present\n return value.startsWith('\"') && value.endsWith('\"') ? value.slice(1, -1) : value;\n });\n\n const item = {};\n headers.forEach((header, index) => {\n item[header] = values[index] || null;\n });\n\n return { json: item };\n});\n\nreturn output;",
"position": [
720,
960
],
"parameters": {
"jsCode": "// Retrieve the CSV-like data from the 'data' field in the input\nconst csvData = items[0].json.data; // Ensure that 'data' is the correct field name\n\n// Check if csvData exists and is not empty\nif (!csvData) {\n console.log(\"Input data structure:\", ); // Debugging output to inspect input structure\n throw new Error('No CSV data provided. Ensure the correct field reference is being used.');\n}\n\nif (typeof csvData !== 'string') {\n throw new Error('CSV data is not a string. Please check the input data format.');\n}\n\n// Preprocess the CSV data to handle missing values\nconst preprocessedCsvData = csvData.replace(/,,/g, ',\"\",');\n\n// Split the CSV into rows\nconst rows = preprocessedCsvData.split(/\\r?\\n/); // Adjust to handle different line endings\n\n// Define the expected number of columns based on CSV structure\nconst expectedNumberOfColumns = 22;\n\n// Iterate over each data row starting from the second row (index 1) and map it to an object\nconst output = rows.slice(1).map((row, index) => {\n // Split the row into values, accounting for empty columns using regex\n const values = row.split(/,(?=(?:(?:[^\"]*\"){2})*[^\"]*$)/).map(value => {\n // Remove surrounding quotes from each value if present and trim whitespace\n return value.startsWith('\"') && value.endsWith('\"') ? value.slice(1, -1).trim() : value.trim();\n });\n\n // Ensure that the number of values matches the expected number of columns\n while (values.length < expectedNumberOfColumns) {\n values.push(\"\"); // Add empty strings for missing values\n }\n\n // Check if the number of values matches expected columns\n if (values.length !== expectedNumberOfColumns) {\n console.warn(`Row ${index + 1} doesn't have the expected number of columns. Skipping this entry.`);\n return null; // Skip this row if it doesn't match the expected columns\n }\n\n // Create an item object with a fixed structure\n const item = {\n id: values[0] || null,\n campaign_lead_map_id: values[1] || null,\n status: values[2] || null,\n category: values[3] || null,\n is_interested: values[4] === 'true', // Convert to boolean\n created_at: values[5] || null,\n first_name: values[6] || null,\n last_name: values[7] || null,\n email: values[8] || null,\n phone_number: values[9] || null,\n company_name: values[10] || null,\n website: values[11] || null,\n location: values[12] || null,\n custom_fields: values[13] || null,\n linkedin_profile: values[14] || null,\n company_url: values[15] || null,\n is_unsubscribed: values[16] === 'true', // Convert to boolean\n unsubscribed_client_id_map: values[17] || null,\n last_email_sequence_sent: values[18] || null,\n open_count: parseInt(values[19], 10) || 0, // Convert to number\n click_count: parseInt(values[20], 10) || 0, // Convert to number\n reply_count: parseInt(values[21], 10) || 0 // Convert to number\n };\n\n return { json: item };\n}).filter(item => item !== null); // Remove null entries from output\n\n// Return the structured output\nreturn output;\n"
},
"typeVersion": 2,
"alwaysOutputData": true
},
{
"id": "f6550deb-0479-475e-b3ba-9507a4ac8911",
"name": "遍历项目1",
"type": "n8n-nodes-base.splitInBatches",
"position": [
180,
160
],
"parameters": {
"options": {
"reset": "={{ $node['Loop Over Items1'].context[\"done\"] }}"
}
},
"typeVersion": 3
},
{
"id": "a183df85-17a2-4886-adc9-68b5ab5fa8b0",
"name": "HubSpot",
"type": "n8n-nodes-base.hubspot",
"position": [
420,
180
],
"parameters": {
"operation": "getAll",
"authentication": "oAuth2",
"additionalFields": {}
},
"credentials": {
"hubspotOAuth2Api": {
"id": "JOrebC0LtzWrkgzz",
"name": "Robaws"
}
},
"executeOnce": false,
"typeVersion": 2.1,
"alwaysOutputData": true
},
{
"id": "da7e2980-6f82-4867-a460-306095234f5f",
"name": "如果",
"type": "n8n-nodes-base.if",
"position": [
640,
180
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "e77d0ee2-bb31-483b-98ee-b0acb0b54bb4",
"operator": {
"type": "boolean",
"operation": "false",
"singleValue": true
},
"leftValue": "={{ $json.companyId.isEmpty() }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "9247f4c5-05dd-48a4-8bf9-c67a8936570c",
"name": "定时触发器",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-1340,
980
],
"parameters": {
"rule": {
"interval": [
{}
]
}
},
"typeVersion": 1.2
},
{
"id": "16623c02-5fb6-40cd-835b-2557eddbbf85",
"name": "更新插入营销活动记录",
"type": "n8n-nodes-base.postgres",
"onError": "continueErrorOutput",
"position": [
980,
960
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "ce_campaign_activity",
"cachedResultName": "ce_campaign_activity"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "outbound_activities",
"cachedResultName": "outbound_activities"
},
"columns": {
"value": {
"id": "={{ $json.id }}",
"email": "={{ $json.email }}",
"status": "={{ $json.status }}",
"website": "={{ $json.email.extractDomain() }}",
"category": "={{ $json.category }}",
"location": "={{ $json.location }}",
"last_name": "={{ $json.last_name }}",
"created_at": "={{ $json.created_at }}",
"first_name": "={{ $json.first_name }}",
"open_count": "={{ $json.open_count }}",
"campaign_id": "={{ $('Loop Over Items').item.json.id }}",
"click_count": "={{ $json.click_count }}",
"company_url": "={{ $json.company_url }}",
"reply_count": "={{ $json.reply_count }}",
"company_name": "={{ $json.company_name }}",
"phone_number": "={{ $json.phone_number }}",
"custom_fields": "={{ JSON.stringify(JSON.parse($json.custom_fields.replace(/\"\"/g, '\"'))) }}",
"is_interested": "={{ $json.is_interested }}",
"is_unsubscribed": "={{ $json.is_unsubscribed }}",
"linkedin_profile": "={{ $json.linkedin_profile }}",
"campaign_lead_map_id": "={{ $json.campaign_lead_map_id }}",
"last_email_sequence_sent": "={{ $json.last_email_sequence_sent }}",
"psg_last_update_timestmap": "={{ $now }}",
"unsubscribed_client_id_map": "={{ $json.unsubscribed_client_id_map }}"
},
"schema": [
{
"id": "id",
"type": "number",
"display": true,
"removed": false,
"required": true,
"displayName": "id",
"defaultMatch": true,
"canBeUsedToMatch": true
},
{
"id": "campaign_lead_map_id",
"type": "number",
"display": true,
"required": false,
"displayName": "campaign_lead_map_id",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "category",
"type": "string",
"display": true,
"required": false,
"displayName": "category",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "is_interested",
"type": "boolean",
"display": true,
"required": false,
"displayName": "is_interested",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "created_at",
"type": "dateTime",
"display": true,
"required": true,
"displayName": "created_at",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "first_name",
"type": "string",
"display": true,
"required": false,
"displayName": "first_name",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "last_name",
"type": "string",
"display": true,
"required": false,
"displayName": "last_name",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "email",
"type": "string",
"display": true,
"required": false,
"displayName": "email",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "phone_number",
"type": "string",
"display": true,
"required": false,
"displayName": "phone_number",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "company_name",
"type": "string",
"display": true,
"required": false,
"displayName": "company_name",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "website",
"type": "string",
"display": true,
"required": false,
"displayName": "website",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "location",
"type": "string",
"display": true,
"required": false,
"displayName": "location",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "custom_fields",
"type": "object",
"display": true,
"required": false,
"displayName": "custom_fields",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "linkedin_profile",
"type": "string",
"display": true,
"required": false,
"displayName": "linkedin_profile",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "company_url",
"type": "string",
"display": true,
"required": false,
"displayName": "company_url",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "is_unsubscribed",
"type": "boolean",
"display": true,
"required": false,
"displayName": "is_unsubscribed",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "unsubscribed_client_id_map",
"type": "object",
"display": true,
"required": false,
"displayName": "unsubscribed_client_id_map",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "last_email_sequence_sent",
"type": "number",
"display": true,
"required": false,
"displayName": "last_email_sequence_sent",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "open_count",
"type": "number",
"display": true,
"required": false,
"displayName": "open_count",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "click_count",
"type": "number",
"display": true,
"required": false,
"displayName": "click_count",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "reply_count",
"type": "number",
"display": true,
"required": false,
"displayName": "reply_count",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "psg_last_update_timestmap",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "psg_last_update_timestmap",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "campaign_id",
"type": "number",
"display": true,
"removed": false,
"required": false,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": false
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"id"
]
},
"options": {
"queryBatching": "independently"
},
"operation": "upsert"
},
"credentials": {},
"typeVersion": 2.5
},
{
"id": "be550807-7ec6-45bc-b522-ae958200e90e",
"name": "HUBSPOT 表格",
"type": "n8n-nodes-base.postgres",
"position": [
900,
160
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "hubspot",
"cachedResultName": "hubspot"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "outbound_activities",
"cachedResultName": "outbound_activities"
},
"columns": {
"value": {
"campaign_id": "={{ $node['Loop Over Items1'].data.campaign_id}}",
"lifecyclestage": "={{ $json.properties.lifecyclestage.value }}",
"hs_num_open_deals": "={{ $json.properties.hs_num_open_deals.value }}",
"hubspot_company_id": "={{ $json.companyId }}"
},
"schema": [
{
"id": "hubspot_company_id",
"type": "number",
"display": true,
"removed": false,
"required": true,
"displayName": "hubspot_company_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "campaign_id",
"type": "number",
"display": true,
"required": false,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "lifecyclestage",
"type": "string",
"display": true,
"required": false,
"displayName": "lifecyclestage",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "hs_num_open_deals",
"type": "number",
"display": true,
"required": false,
"displayName": "hs_num_open_deals",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "last_engagement_date",
"type": "dateTime",
"display": true,
"removed": true,
"required": false,
"displayName": "last_engagement_date",
"defaultMatch": false,
"canBeUsedToMatch": false
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"hubspot_company_id"
]
},
"options": {
"queryBatching": "independently"
},
"operation": "upsert"
},
"credentials": {
"postgres": {
"id": "VtxZTfSI4m2NFeN5",
"name": "Postgres Personal Personal Folder"
}
},
"typeVersion": 2.5
},
{
"id": "328b900e-8c21-4578-b6a4-8c17fbccca26",
"name": "搜索",
"type": "n8n-nodes-base.postgres",
"position": [
-40,
160
],
"parameters": {
"query": "SELECT\n ca.id,\n ca.campaign_id,\n ca.email,\n MIN(ca.first_name) AS first_name,\n MIN(ca.last_name) AS last_name,\n SUM(ca.reply_count) AS reply_count,\n max(hb_lifecyclestage_check_timestamp) as hb_lifecyclestage_check_timestamp,\n CASE\n -- Check if there is a comma and handle the extraction first\n WHEN MIN(ca.linkedin_profile) LIKE '%,%' \n THEN \n -- Replace /sales/people/ with /in/ on the extracted part before the comma\n REPLACE(LEFT(MIN(ca.linkedin_profile), POSITION(',' IN MIN(ca.linkedin_profile)) - 1), '/sales/people/', '/in/')\n ELSE \n -- For profiles without a comma, check for the replacement directly\n REPLACE(MIN(ca.linkedin_profile), '/sales/people/', '/in/')\n END AS linkedin_profile,\n MAX(ca.company_url) AS company_profile,\n -- Extracting domain from email to create the website column\n SUBSTRING(ca.email FROM POSITION('@' IN ca.email) + 1) AS website,\n c.created_at,\n c.updated_at,\n c.status,\n c.name\nFROM\n outbound_activities.ce_campaign_activity ca\nJOIN\n outbound_activities.ce_campaign c ON ca.campaign_id = c.campaign_id\n--left join outbound_activities.hubspot hb on \n\nWHERE \n hb_lifecyclestage_check_timestamp IS NULL \n OR hb_lifecyclestage_check_timestamp < NOW() - INTERVAL '24 hours'\n\n \nGROUP BY\n ca.id,\n ca.campaign_id,\n ca.email,\n c.created_at,\n c.updated_at,\n c.status,\n c.name\n\n\nlimit 5000",
"options": {},
"operation": "executeQuery"
},
"credentials": {
"postgres": {
"id": "VtxZTfSI4m2NFeN5",
"name": "Postgres Personal Personal Folder"
}
},
"typeVersion": 2.5
},
{
"id": "c403ef52-894d-476a-aaba-6527c7cb2184",
"name": "Postgres1",
"type": "n8n-nodes-base.postgres",
"position": [
640,
380
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "ce_campaign_activity",
"cachedResultName": "ce_campaign_activity"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "outbound_activities",
"cachedResultName": "outbound_activities"
},
"columns": {
"value": {
"id": "={{ $('Loop Over Items1').item.json.id }}",
"hb_lifecyclestage_check_timestamp": "={{ $now }}"
},
"schema": [
{
"id": "id",
"type": "number",
"display": true,
"removed": false,
"required": true,
"displayName": "id",
"defaultMatch": true,
"canBeUsedToMatch": true
},
{
"id": "campaign_lead_map_id",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "campaign_lead_map_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "category",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "category",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "is_interested",
"type": "boolean",
"display": true,
"removed": true,
"required": false,
"displayName": "is_interested",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "created_at",
"type": "dateTime",
"display": true,
"removed": true,
"required": true,
"displayName": "created_at",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "first_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "first_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "last_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "email",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "phone_number",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "phone_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "company_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "website",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "website",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "location",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "location",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "custom_fields",
"type": "object",
"display": true,
"removed": true,
"required": false,
"displayName": "custom_fields",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "linkedin_profile",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "linkedin_profile",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_url",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "company_url",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "is_unsubscribed",
"type": "boolean",
"display": true,
"removed": true,
"required": false,
"displayName": "is_unsubscribed",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "unsubscribed_client_id_map",
"type": "object",
"display": true,
"removed": true,
"required": false,
"displayName": "unsubscribed_client_id_map",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_email_sequence_sent",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "last_email_sequence_sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "open_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "open_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "click_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "click_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reply_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "reply_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "psg_last_update_timestmap",
"type": "dateTime",
"display": true,
"removed": true,
"required": false,
"displayName": "psg_last_update_timestmap",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "campaign_id",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "hb_lifecyclestage_check_timestamp",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "hb_lifecyclestage_check_timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"id"
]
},
"options": {},
"operation": "update"
},
"credentials": {
"postgres": {
"id": "VtxZTfSI4m2NFeN5",
"name": "Postgres Personal Personal Folder"
}
},
"typeVersion": 2.5
},
{
"id": "671f168b-a720-42e6-964d-a7f2871d2d6e",
"name": "更新 HUBSPOT 活动表格",
"type": "n8n-nodes-base.postgres",
"position": [
1120,
160
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "ce_campaign_activity",
"cachedResultName": "ce_campaign_activity"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "outbound_activities",
"cachedResultName": "outbound_activities"
},
"columns": {
"value": {
"id": "={{ $('Loop Over Items1').item.json.id }}",
"hb_lifecyclestage_check_timestamp": "={{ $now }}"
},
"schema": [
{
"id": "id",
"type": "number",
"display": true,
"removed": false,
"required": true,
"displayName": "id",
"defaultMatch": true,
"canBeUsedToMatch": true
},
{
"id": "campaign_lead_map_id",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "campaign_lead_map_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "category",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "category",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "is_interested",
"type": "boolean",
"display": true,
"removed": true,
"required": false,
"displayName": "is_interested",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "created_at",
"type": "dateTime",
"display": true,
"removed": true,
"required": true,
"displayName": "created_at",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "first_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "first_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "last_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "email",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "phone_number",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "phone_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_name",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "company_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "website",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "website",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "location",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "location",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "custom_fields",
"type": "object",
"display": true,
"removed": true,
"required": false,
"displayName": "custom_fields",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "linkedin_profile",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "linkedin_profile",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_url",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "company_url",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "is_unsubscribed",
"type": "boolean",
"display": true,
"removed": true,
"required": false,
"displayName": "is_unsubscribed",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "unsubscribed_client_id_map",
"type": "object",
"display": true,
"removed": true,
"required": false,
"displayName": "unsubscribed_client_id_map",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_email_sequence_sent",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "last_email_sequence_sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "open_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "open_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "click_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "click_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reply_count",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "reply_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "psg_last_update_timestmap",
"type": "dateTime",
"display": true,
"removed": true,
"required": false,
"displayName": "psg_last_update_timestmap",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "campaign_id",
"type": "number",
"display": true,
"removed": true,
"required": false,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "hb_lifecyclestage_check_timestamp",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "hb_lifecyclestage_check_timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"id"
]
},
"options": {},
"operation": "update"
},
"credentials": {
"postgres": {
"id": "VtxZTfSI4m2NFeN5",
"name": "Postgres Personal Personal Folder"
}
},
"typeVersion": 2.5
},
{
"id": "6ebe6482-0f31-465a-8532-abaf3822ad72",
"name": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
-140,
-60
],
"parameters": {
"color": 3,
"width": 1531.405758029468,
"height": 669.051063941859,
"content": "## HUBSPOT 生命周期阶段（潜在客户状态）"
},
"typeVersion": 1
},
{
"id": "31ea75c2-a228-4390-b125-8f2ac0b96a07",
"name": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-140,
760
],
"parameters": {
"color": 3,
"width": 1831,
"height": 669,
"content": "## SMARTLEAD 营销活动数据"
},
"typeVersion": 1
},
{
"id": "8d7e4883-74e2-4758-b2d9-504eb7301cbd",
"name": "设置 SMARTLEAD API 密钥",
"type": "n8n-nodes-base.set",
"position": [
-1040,
980
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "7f81531d-f76f-42c7-b536-2b7b70563e12",
"name": "API KEY",
"type": "string",
"value": "<< ADD YOUR API KEY HERE >>"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "1742845b-2ce5-4184-a7b0-6f5606714fcb",
"name": "便签2",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1100,
780
],
"parameters": {
"height": 400,
"content": "## 在此处查找您的 smartlead API 密钥 [here](https://app.smartlead.ai/app/settings/profile)"
},
"typeVersion": 1
},
{
"id": "e10205a7-3859-4a31-85ba-59c5cc0b69f7",
"name": "Postgres",
"type": "n8n-nodes-base.postgres",
"position": [
-40,
1700
],
"parameters": {
"query": "SELECT \n h.campaign_id,\n c.status,\n c.name,\n COUNT(DISTINCT h.hubspot_company_id) AS total_companies,\n SUM(CASE WHEN h.lifecyclestage = 'lead' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS lead_count,\n SUM(CASE WHEN h.lifecyclestage = 'marketingqualifiedlead' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS marketingqualifiedlead_count,\n SUM(CASE WHEN h.lifecyclestage = 'salesqualifiedlead' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS salesqualifiedlead_count,\n SUM(CASE WHEN h.lifecyclestage = 'opportunity' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS opportunity_count,\n SUM(CASE WHEN h.lifecyclestage = 'customer' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS customer_count,\n SUM(CASE WHEN h.lifecyclestage = '140669943' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS lifecyclestage_140669943_count,\n SUM(CASE WHEN h.lifecyclestage = '140669942' AND h.hs_num_open_deals != 0 THEN 1 ELSE 0 END) AS lifecyclestage_140669942_count\nFROM \n outbound_activities.hubspot h\nJOIN \n outbound_activities.ce_campaign c ON h.campaign_id = c.campaign_id\nGROUP BY \n h.campaign_id, c.status, c.name",
"options": {
"queryBatching": "independently"
},
"operation": "executeQuery"
},
"credentials": {
"postgres": {
"id": "VtxZTfSI4m2NFeN5",
"name": "Postgres Personal Personal Folder"
}
},
"retryOnFail": true,
"typeVersion": 2.5
},
{
"id": "19a80be4-f81f-44f7-8108-a20f6af8e315",
"name": "便签3",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1300,
1340
],
"parameters": {
"width": 740,
"height": 400,
"content": "## POSTGRES 安装 [指南](https://github.com/wukimidaire/postgres_table_templates)"
},
"typeVersion": 1
},
{
"id": "7bc235d2-65c8-41fd-b429-26b2422cbfa8",
"name": "便签4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-120,
1580
],
"parameters": {
"color": 3,
"width": 1060,
"height": 1313.3157639300548,
"content": "## 按照此分步指南操作，重点关注此流程的接下来 3 个表格创建："
},
"typeVersion": 1
},
{
"id": "0af663c4-faa9-49ae-a5d3-3bcb6ea7888a",
"name": "Google Sheets",
"type": "n8n-nodes-base.googleSheets",
"position": [
180,
1700
],
"parameters": {
"columns": {
"value": {
"name": "={{ $json.name }}",
"status": "={{ $json.status }}",
"lead_count": "={{ $json.lead_count }}",
"campaign_id": "={{ $json.company_id }}",
"customer_count": "={{ $json.customer_count\n}}\n",
"total_companies": "={{ $json.total_companies }}",
"opportunity_count": "={{ $json.opportunity_count\n }}",
"salesqualifiedlead_count": "={{ $json.salesqualifiedlead_count }}",
"marketingqualifiedlead_count": "={{ $json.marketingqualifiedlead_count }}",
"lifecyclestage_140669942_count": "={{ $json.lifecyclestage_140669942_count\n}}\n",
"lifecyclestage_140669943_count": "={{ $json.lifecyclestage_140669943_count\n}}\n"
},
"schema": [
{
"id": "campaign_id",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "campaign_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "total_companies",
"type": "string",
"display": true,
"required": false,
"displayName": "total_companies",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "lead_count",
"type": "string",
"display": true,
"required": false,
"displayName": "lead_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "marketingqualifiedlead_count",
"type": "string",
"display": true,
"required": false,
"displayName": "marketingqualifiedlead_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "salesqualifiedlead_count",
"type": "string",
"display": true,
"required": false,
"displayName": "salesqualifiedlead_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "opportunity_count",
"type": "string",
"display": true,
"required": false,
"displayName": "opportunity_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "customer_count",
"type": "string",
"display": true,
"required": false,
"displayName": "customer_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "lifecyclestage_140669943_count",
"type": "string",
"display": true,
"required": false,
"displayName": "lifecyclestage_140669943_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "lifecyclestage_140669942_count",
"type": "string",
"display": true,
"required": false,
"displayName": "lifecyclestage_140669942_count",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"campaign_id"
]
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1kG5uXCzOJdUTapA6p-IbH3D8sjpGZ5MQm_IhhvPvIGE/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1kG5uXCzOJdUTapA6p-IbH3D8sjpGZ5MQm_IhhvPvIGE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1kG5uXCzOJdUTapA6p-IbH3D8sjpGZ5MQm_IhhvPvIGE/edit?usp=drivesdk",
"cachedResultName": "Smartlead Reporting - TEMPLATE"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "qx3ux5eQ43R4Hmbq",
"name": "Google Sheets account 2"
}
},
"typeVersion": 4.5
}
],
"pinData": {},
"connections": {
"If": {
"main": [
[
{
"node": "HUBSPOT TABLE",
"type": "main",
"index": 0
}
],
[
{
"node": "Postgres1",
"type": "main",
"index": 0
}
]
]
},
"Code": {
"main": [
[
{
"node": "UPSERT CAMPAIGN ACTIVITY",
"type": "main",
"index": 0
}
]
]
},
"Merge": {
"main": [
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"SEARCH": {
"main": [
[
{
"node": "Loop Over Items1",
"type": "main",
"index": 0
}
]
]
},
"HubSpot": {
"main": [
[
{
"node": "If",
"type": "main",
"index": 0
}
]
]
},
"Postgres": {
"main": [
[
{
"node": "Google Sheets",
"type": "main",
"index": 0
}
]
]
},
"Postgres1": {
"main": [
[
{
"node": "Loop Over Items1",
"type": "main",
"index": 0
}
]
]
},
"HUBSPOT TABLE": {
"main": [
[
{
"node": "UPDATE HUBSPOT ACTIVITY TABLE",
"type": "main",
"index": 0
}
]
]
},
"Loop Over Items": {
"main": [
null,
[
{
"node": "EXTRACT CAMPAIGN DATA",
"type": "main",
"index": 0
},
{
"node": "UPDATE CAMPAIGN",
"type": "main",
"index": 0
}
]
]
},
"UPDATE CAMPAIGN": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"Loop Over Items1": {
"main": [
null,
[
{
"node": "HubSpot",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "SET SMARTLEAD API KEY",
"type": "main",
"index": 0
}
]
]
},
"FETCH ALL CAMPAIGNS": {
"main": [
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"EXTRACT CAMPAIGN DATA": {
"main": [
[
{
"node": "Code",
"type": "main",
"index": 0
}
]
]
},
"SET SMARTLEAD API KEY": {
"main": [
[
{
"node": "FETCH ALL CAMPAIGNS",
"type": "main",
"index": 0
},
{
"node": "Postgres",
"type": "main",
"index": 0
},
{
"node": "SEARCH",
"type": "main",
"index": 0
}
]
]
},
"UPSERT CAMPAIGN ACTIVITY": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
],
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"UPDATE HUBSPOT ACTIVITY TABLE": {
"main": [
[
{
"node": "Loop Over Items1",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 销售, 营销
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
使用Dropcontact批量请求每小时丰富最多1500个邮箱
使用Dropcontact批量请求每小时丰富最多1500个邮箱
Code
Wait
Slack
+7
12 节点victor de coster
销售
Overpass 潜在客户生成系统
使用 OpenStreetMap 数据生成商业线索并保存到 Google Sheets
If
Set
Code
+12
27 节点Akram Kadri
销售
使用Google Maps生成潜在客户 - AlexK1919
使用Google Maps生成潜在客户
If
Set
Code
+13
42 节点Alex Kim
销售
实时Notion Todoist双向同步模板
使用Redis的Notion Todoist实时双向同步
If
Set
Code
+26
246 节点Mario
销售
使用GPT-4o、WordPress和LinkedIn发布自动化RSS内容到博客文章
使用GPT-4o、WordPress和LinkedIn发布自动化RSS内容到博客文章
If
Set
Code
+21
40 节点Immanuel
人工智能
通过 AI 图像生成从热门趋势创建 Instagram 内容
通过 AI 图像生成从热门趋势创建 Instagram 内容
If
Set
Code
+9
44 节点mustafa kendigüzel
人工智能
