This workflow corresponds to n8n.io template #14993 — we link there as the canonical source.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"id": "raw_id",
"name": "Salesforce Leads & Opportunities to PostgreSQL (Backfill & Incremental Sync ETL)",
"tags": [],
"nodes": [
{
"id": "f5fd8873-0c97-4ca8-afa3-8366ceab5284",
"name": "Lead Field Reference",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"width": 336,
"height": 384,
"content": "## Lead Field Reference\n\nUse API field names from **Salesforce Object Manager \u2192 Lead \u2192 Fields & Relationships**.\n\nUpdate the Lead fetch node only with fields that exist in your Salesforce Lead object."
},
"typeVersion": 1
},
{
"id": "88090729-c267-49d4-b121-ccd0af8c4837",
"name": "Opportunity Field Reference",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
400
],
"parameters": {
"width": 336,
"height": 432,
"content": "## Opportunity Field Reference\n\nUse API field names from **Salesforce Object Manager \u2192 Opportunity \u2192 Fields & Relationships**.\n\nUpdate the Opportunity fetch node only with fields that exist in your Salesforce Opportunity object."
},
"typeVersion": 1
},
{
"id": "af944ad5-8cf0-433c-8d73-97d1eddaa62f",
"name": "Fetch Opportunity Records",
"type": "n8n-nodes-base.salesforce",
"position": [
128,
640
],
"parameters": {
"options": {
"fields": "CreatedDate, CreatedById, Name, Phone__c, LeadSource, OwnerId, StageName, Amount, AccountId\n",
"conditionsUi": {
"conditionValues": [
{
"field": "CreatedDate",
"value": "={{ $json.since_datetime }}",
"operation": ">="
},
{
"field": "CreatedDate",
"value": "={{ $json.until_datetime }}",
"operation": "<="
}
]
}
},
"resource": "opportunity",
"operation": "getAll",
"returnAll": true
},
"typeVersion": 1
},
{
"id": "358c8d84-6626-4ce6-a26d-a1460d5ad223",
"name": "Opportunity De-duplication Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
1104,
544
],
"parameters": {
"width": 448,
"height": 320,
"content": "## Opportunity De-duplication\n\nThis branch removes duplicate opportunity rows before the merge step.\n\nThe current design intentionally keeps a single opportunity record per normalized opportunity phone key."
},
"typeVersion": 1
},
{
"id": "e6d52258-ef06-4557-b34e-bd9b639150b1",
"name": "Phone-Based Routing Overview",
"type": "n8n-nodes-base.stickyNote",
"position": [
368,
0
],
"parameters": {
"width": 448,
"height": 832,
"content": "## Phone-Based Routing Overview\n\nThis workflow intentionally splits data into two paths:\n\n- Records **without a phone value** bypass phone normalization and still flow into the final output.\n- Records **with a phone value** are normalized and used for lead-to-opportunity enrichment.\n\nThis ensures records with empty phone fields are still preserved in the final dataset."
},
"typeVersion": 1
},
{
"id": "57128d90-424f-4c4e-863f-41527f866aeb",
"name": "Normalize Output Fields",
"type": "n8n-nodes-base.code",
"position": [
2128,
240
],
"parameters": {
"jsCode": "return items.map(item => {\n const j = item.json || {};\n const attrs = j.attributes || {};\n\n // --- 1) Extract basic record metadata ---\n const sourceObject = attrs.type || null;\n const recordId = attrs.url\n ? attrs.url.split('/').pop()\n : (j.Id || null);\n\n // --- 2) Read phone values from possible source fields ---\n const phoneLead = j.Phone ?? j.body?.nomorlead ?? null;\n const phoneOpportunity = j.Phone__c ?? j.body?.nomoroppty ?? null;\n\n // --- 3) Prioritize Lead phone, then fall back to Opportunity phone ---\n const rawPhone = phoneLead || phoneOpportunity || null;\n\n // --- 4) Normalize phone into +62 format ---\n let cleanPhone = null;\n if (rawPhone) {\n let p = String(rawPhone).trim();\n\n // Remove all non-digit characters\n p = p.replace(/\\D/g, \"\");\n\n // If the number starts with 0, convert it to Indonesia country code format\n if (p.startsWith(\"0\")) {\n p = \"62\" + p.slice(1);\n }\n\n // If the number does not start with 62, prepend the Indonesia country code\n if (!p.startsWith(\"62\")) {\n p = \"62\" + p;\n }\n\n cleanPhone = \"+\" + p;\n }\n\n // --- 5) Build a standardized output object ---\n item.json = {\n Source_Object: sourceObject,\n SF_Id: recordId,\n CreatedById: j.CreatedById,\n\n CreatedDate: j.CreatedDate ?? null,\n Name: j.Name ?? null,\n\n Phone: rawPhone,\n Clean_Phone: cleanPhone,\n Email: j.Email ?? null,\n\n LeadSource: j.LeadSource ?? null,\n\n Status: j.Status ?? null, // Lead only\n StageName: j.StageName ?? null, // Opportunity only\n\n OwnerId: j.OwnerId ?? null,\n AccountId: j.AccountId ?? null,\n Amount: j.Amount ?? null\n };\n\n return item;\n});"
},
"typeVersion": 2
},
{
"id": "9f4d9b4b-0f7b-4333-a19c-3080ee4b07c1",
"name": "Batch Processing Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2016,
96
],
"parameters": {
"width": 784,
"height": 672,
"content": "## Salesforce Data Bank ETL (Backfill & Incremental Sync)\n\nThis workflow extracts Lead and Opportunity data from Salesforce and loads it into PostgreSQL for reporting and analytics. It supports two operating modes in a single workflow.\n\n### Two Input Modes\n\n- Historical Backfill (Manual Trigger)\nRun once to populate past data. Set start_date and end_date in the Set Date Range node. The workflow splits the range into 7-day batches to reduce load and prevent API issues.\n\n- Incremental Sync (Schedule Trigger)\nRuns automatically and pulls recent data (daily). The date range is calculated dynamically using ISO datetime.\n\n### Core Flow\n- Fetch Lead & Opportunity data from Salesforce\n- Split records based on phone availability\n- Normalize phone numbers into +62 format (Indonesia IDD)\n- Merge datasets using phone as a bridge\n- Standardize fields into a unified schema\n- Upsert into PostgreSQL (no duplicate records)\n\n### Phone Normalization\n\nPhone numbers are cleaned and converted into +62 format, representing Indonesia\u2019s international dialing code (IDD). This ensures consistent formatting, reliable merging, and compatibility with downstream systems.\n\n### Batch Processing\n\nDate periods are processed in batches (weekly chunks) to reduce load on Salesforce and downstream systems. This helps prevent oversized requests, timeouts, and temporary API errors during backfills.\n\n### Setup\n\na) Salesforce credential \u2014 Configure Salesforce OAuth2 with access to Lead and Opportunity objects\nb) PostgreSQL \u2014 Prepare target table (e.g. n8n_salesforce_data) with matching schema\nc) Historical range \u2014 Set start_date and end_date in Set Date Range node before running backfill\nd) Schedule trigger \u2014 Configure interval for incremental sync (e.g. daily)"
},
"typeVersion": 1
},
{
"id": "955410bc-9bdf-4b3b-9463-4cc5703911dc",
"name": "Lead and Opportunity Merge Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
1504,
0
],
"parameters": {
"width": 320,
"height": 512,
"content": "## Lead and Opportunity Merge\n\nThis merge step enriches lead records with opportunity data using the normalized phone-based merge key.\n\nRecords that do not have a usable phone value still remain in the final combined dataset."
},
"typeVersion": 1
},
{
"id": "6f4b4fe5-b6d1-48ab-86fe-03f96058b959",
"name": "Merge All Streams",
"type": "n8n-nodes-base.merge",
"position": [
1904,
224
],
"parameters": {
"numberInputs": 3
},
"typeVersion": 3.2
},
{
"id": "499aaf25-5440-4249-a669-3e6d14ba2e2a",
"name": "Set Historical Date Range",
"type": "n8n-nodes-base.set",
"position": [
-896,
192
],
"parameters": {
"mode": "raw",
"options": {},
"jsonOutput": "{\n \"start_date\": \"2025-12-07\",\n \"end_date\": \"2026-04-11\"\n}"
},
"typeVersion": 3.4
},
{
"id": "fdc19358-fff1-4a81-bf22-3c1430c8899f",
"name": "Generate Weekly Periods",
"type": "n8n-nodes-base.code",
"position": [
-672,
192
],
"parameters": {
"jsCode": "let start = new Date($input.first().json.start_date);\nconst end = new Date($input.first().json.end_date);\n\nconst output = [];\n\nconst formatDate = (date) => {\n const year = date.getFullYear();\n const month = String(date.getMonth() + 1).padStart(2, '0');\n const day = String(date.getDate()).padStart(2, '0');\n return `${year}-${month}-${day}`;\n};\n\nwhile (start <= end) {\n let until = new Date(start);\n until.setDate(until.getDate() + 6);\n\n if (until > end) {\n until = new Date(end);\n }\n\n const sinceDate = formatDate(start);\n const untilDate = formatDate(until);\n\n output.push({\n since: sinceDate,\n until: untilDate,\n since_datetime: `${sinceDate}T00:00:00Z`,\n until_datetime: `${untilDate}T23:59:59Z`,\n });\n\n start.setDate(start.getDate() + 7);\n}\n\nreturn output.map(d => ({ json: d }));"
},
"typeVersion": 2
},
{
"id": "487ea575-1eee-4354-85b5-870ac1a9e0b7",
"name": "Loop Over Date Periods",
"type": "n8n-nodes-base.splitInBatches",
"position": [
-352,
336
],
"parameters": {
"options": {}
},
"retryOnFail": true,
"typeVersion": 3
},
{
"id": "b7c39933-7a63-4f19-89b1-36f993621cd8",
"name": "Normalize Lead Phone (+62)",
"type": "n8n-nodes-base.set",
"position": [
928,
304
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "d3f954ee-b7fa-40b9-8771-3a0533d5fd3f",
"name": "body.nomorlead",
"type": "string",
"value": "={{ \n ($json.Phone || \"\")\n .toString()\n .trim() // remove leading/trailing whitespace and newline characters\n .replace(/\\s+/g, \"\") // remove internal whitespace\n .replace(/[^0-9]/g, \"\") // remove all non-digit characters (e.g. +, -, (, ))\n .replace(/^0/, \"+62\") // if starts with 0 \u2192 convert to Indonesia country code\n .replace(/^62+/, \"+62\") // prevent duplicated country code (e.g. 6262 \u2192 62)\n}}"
}
]
},
"includeOtherFields": true
},
"typeVersion": 3.4
},
{
"id": "eb1a3824-5466-4a72-8cd2-f776440a193e",
"name": "Normalize Opportunity Phone (+62)",
"type": "n8n-nodes-base.set",
"position": [
928,
704
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "d3f954ee-b7fa-40b9-8771-3a0533d5fd3f",
"name": "body.nomoroppty",
"type": "string",
"value": "={{ \n ($json.Phone__c || \"\")\n .toString()\n .trim() // remove leading/trailing whitespace and newline characters\n .replace(/\\s+/g, \"\") // remove internal whitespace\n .replace(/[^0-9]/g, \"\") // remove all non-digit characters (e.g. +, -, (, ))\n .replace(/^0/, \"+62\") // if starts with 0 \u2192 convert to Indonesia country code\n .replace(/^62+/, \"+62\") // prevent duplicated country code (e.g. 6262 \u2192 62)\n}}"
}
]
},
"includeOtherFields": true
},
"typeVersion": 3.4
},
{
"id": "eb4359be-af00-4a54-8d47-7eb87fea7578",
"name": "Fetch Lead Records",
"type": "n8n-nodes-base.salesforce",
"position": [
128,
224
],
"parameters": {
"options": {
"fields": "CreatedDate, CreatedById, Name, Phone, Email, LeadSource, Status, OwnerId",
"conditionsUi": {
"conditionValues": [
{
"field": "CreatedDate",
"value": "={{ $json.until_datetime }}",
"operation": "<="
},
{
"field": "CreatedDate",
"value": "={{ $json.since_datetime }}",
"operation": ">="
}
]
}
},
"operation": "getAll",
"returnAll": true
},
"credentials": {
"salesforceOAuth2Api": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "57a4d736-987f-441a-b642-0d6dbba075ff",
"name": "Upsert Rows into Postgres",
"type": "n8n-nodes-base.postgres",
"position": [
2128,
720
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "n8n_salesforce_data",
"cachedResultName": "n8n_salesforce_data"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "public"
},
"columns": {
"value": {
"Name": "={{ $json.Name }}",
"Email": "={{ $json.Email }}",
"Phone": "={{ $json.Clean_Phone }}",
"sf_id": "={{ $json.SF_Id }}",
"Amount": "={{ $json.Amount }}",
"Status": "={{ $json.Status }}",
"OwnerId": "={{ $json.OwnerId }}",
"AccountId": "={{ $json.AccountId }}",
"StageName": "={{ $json.StageName }}",
"synced_at": "={{ $now }}",
"LeadSource": "={{ $json.LeadSource }}",
"CreatedById": "={{ $json.CreatedById }}",
"CreatedDate": "={{ $json.CreatedDate }}"
},
"schema": [
{
"id": "id",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "id",
"defaultMatch": true,
"canBeUsedToMatch": true
},
{
"id": "CreatedDate",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "CreatedDate",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "CreatedById",
"type": "string",
"display": true,
"required": false,
"displayName": "CreatedById",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Name",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "Phone",
"type": "string",
"display": true,
"required": false,
"displayName": "Phone",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "Email",
"type": "string",
"display": true,
"required": false,
"displayName": "Email",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "LeadSource",
"type": "string",
"display": true,
"required": false,
"displayName": "LeadSource",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "StageName",
"type": "string",
"display": true,
"required": false,
"displayName": "StageName",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "OwnerId",
"type": "string",
"display": true,
"required": false,
"displayName": "OwnerId",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "Amount",
"type": "number",
"display": true,
"required": false,
"displayName": "Amount",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "AccountId",
"type": "string",
"display": true,
"required": false,
"displayName": "AccountId",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "synced_at",
"type": "dateTime",
"display": true,
"required": false,
"displayName": "synced_at",
"defaultMatch": false,
"canBeUsedToMatch": false
},
{
"id": "sf_id",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "sf_id",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"sf_id"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "upsert"
},
"credentials": {
"postgres": {
"name": "<your credential>"
}
},
"typeVersion": 2.6
},
{
"id": "06842db4-eccc-4f19-a336-514e6f3c7bfd",
"name": "Schedule Trigger (Incremental)",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-1120,
464
],
"parameters": {
"rule": {
"interval": [
{}
]
}
},
"typeVersion": 1.2
},
{
"id": "9da3f634-370c-4b2f-a0a4-eb01bab9d206",
"name": "Manual Trigger (Historical Backfill)",
"type": "n8n-nodes-base.manualTrigger",
"position": [
-1120,
192
],
"parameters": {},
"typeVersion": 1
},
{
"id": "a85a6d17-e1ac-4faa-9a14-5a6364a6a8bf",
"name": "Merge Lead with Opportunity",
"type": "n8n-nodes-base.merge",
"position": [
1616,
336
],
"parameters": {
"mode": "combine",
"options": {},
"advanced": true,
"joinMode": "enrichInput1",
"mergeByFields": {
"values": [
{
"field1": "body.nomorlead",
"field2": "body.nomoroppty"
}
]
}
},
"typeVersion": 3.2
},
{
"id": "ff23e9a9-9f4e-4f32-a577-014a9061d887",
"name": "Set Incremental Dates",
"type": "n8n-nodes-base.code",
"position": [
-896,
464
],
"parameters": {
"jsCode": "const now = new Date();\nconst yesterday = new Date(now);\nyesterday.setDate(now.getDate() - 1);\n\nconst formatDate = (date) => {\n const d = new Date(date);\n const year = d.getUTCFullYear();\n const month = String(d.getUTCMonth() + 1).padStart(2, '0');\n const day = String(d.getUTCDate()).padStart(2, '0');\n return `${year}-${month}-${day}`;\n};\n\nconst formatDateTime = (date, isEndOfDay = false) => {\n const d = new Date(date);\n if (isEndOfDay) {\n d.setUTCHours(23, 59, 59, 999);\n } else {\n d.setUTCHours(0, 0, 0, 0);\n }\n return d.toISOString();\n};\n\nconst since = formatDate(yesterday);\nconst until = formatDate(now);\n\nreturn [\n {\n json: {\n since,\n until,\n since_datetime: formatDateTime(yesterday, false),\n until_datetime: formatDateTime(now, true)\n }\n }\n];"
},
"typeVersion": 2
},
{
"id": "190d355e-ae98-4536-8316-b63ec9981ca7",
"name": "Remove Duplicate Opportunities",
"type": "n8n-nodes-base.removeDuplicates",
"position": [
1264,
704
],
"parameters": {
"compare": "selectedFields",
"options": {},
"fieldsToCompare": "body.nomoroppty"
},
"typeVersion": 2
},
{
"id": "ab62817d-8819-453f-990b-b8064ceeec3f",
"name": "Phone empty? (Lead)",
"type": "n8n-nodes-base.if",
"position": [
560,
224
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "77b5ed4b-f0ff-4f65-8e4e-920779c25871",
"operator": {
"type": "string",
"operation": "empty",
"singleValue": true
},
"leftValue": "={{ String($json.Phone || '').trim() }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "0e710607-a9d6-480b-bca8-eda248091e00",
"name": "Phone empty? (Opportunity)",
"type": "n8n-nodes-base.if",
"position": [
544,
640
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "77b5ed4b-f0ff-4f65-8e4e-920779c25871",
"operator": {
"type": "string",
"operation": "empty",
"singleValue": true
},
"leftValue": "={{ String($json.Phone__c || '').trim() }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
}
],
"active": false,
"settings": {
"binaryMode": "separate",
"executionOrder": "v1"
},
"versionId": "raw_versionid",
"connections": {
"Merge All Streams": {
"main": [
[
{
"node": "Normalize Output Fields",
"type": "main",
"index": 0
}
]
]
},
"Fetch Lead Records": {
"main": [
[
{
"node": "Phone empty? (Lead)",
"type": "main",
"index": 0
}
]
]
},
"Phone empty? (Lead)": {
"main": [
[
{
"node": "Merge All Streams",
"type": "main",
"index": 0
}
],
[
{
"node": "Normalize Lead Phone (+62)",
"type": "main",
"index": 0
}
]
]
},
"Set Incremental Dates": {
"main": [
[
{
"node": "Loop Over Date Periods",
"type": "main",
"index": 0
}
]
]
},
"Loop Over Date Periods": {
"main": [
[],
[
{
"node": "Fetch Lead Records",
"type": "main",
"index": 0
},
{
"node": "Fetch Opportunity Records",
"type": "main",
"index": 0
}
]
]
},
"Generate Weekly Periods": {
"main": [
[
{
"node": "Loop Over Date Periods",
"type": "main",
"index": 0
}
]
]
},
"Normalize Output Fields": {
"main": [
[
{
"node": "Upsert Rows into Postgres",
"type": "main",
"index": 0
}
]
]
},
"Fetch Opportunity Records": {
"main": [
[
{
"node": "Phone empty? (Opportunity)",
"type": "main",
"index": 0
}
]
]
},
"Set Historical Date Range": {
"main": [
[
{
"node": "Generate Weekly Periods",
"type": "main",
"index": 0
}
]
]
},
"Upsert Rows into Postgres": {
"main": [
[
{
"node": "Loop Over Date Periods",
"type": "main",
"index": 0
}
]
]
},
"Normalize Lead Phone (+62)": {
"main": [
[
{
"node": "Merge Lead with Opportunity",
"type": "main",
"index": 0
}
]
]
},
"Phone empty? (Opportunity)": {
"main": [
[
{
"node": "Merge All Streams",
"type": "main",
"index": 1
}
],
[
{
"node": "Normalize Opportunity Phone (+62)",
"type": "main",
"index": 0
}
]
]
},
"Merge Lead with Opportunity": {
"main": [
[
{
"node": "Merge All Streams",
"type": "main",
"index": 2
}
]
]
},
"Remove Duplicate Opportunities": {
"main": [
[
{
"node": "Merge Lead with Opportunity",
"type": "main",
"index": 1
}
]
]
},
"Schedule Trigger (Incremental)": {
"main": [
[
{
"node": "Set Incremental Dates",
"type": "main",
"index": 0
}
]
]
},
"Normalize Opportunity Phone (+62)": {
"main": [
[
{
"node": "Remove Duplicate Opportunities",
"type": "main",
"index": 0
}
]
]
},
"Manual Trigger (Historical Backfill)": {
"main": [
[
{
"node": "Set Historical Date Range",
"type": "main",
"index": 0
}
]
]
}
}
}
Credentials you'll need
Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.
postgressalesforceOAuth2Api
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
This workflow extracts Lead and Opportunity data from Salesforce, transforms and normalizes the data, and loads it into PostgreSQL as a structured data bank for reporting and analytics.
Source: https://n8n.io/workflows/14993/ — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
This workflow is designed for B2B/SaaS teams who want to secure renewals before it’s too late.
Short Description
This n8n template automatically enriches company records in your CRM using CompanyEnrich and keeps your data up to date without manual work.
Sync Local Falcon rankings to Salesforce. Uses @local-falcon/n8n-nodes-localfalcon, salesforce. Scheduled trigger; 11 nodes.
Salesforce:Account:create get addNote getAll getSummary update delete:Case:create get addComment getAll getSummary update delete:Attachment:create get getAll getSummary update delete. Uses start, sale