Sync Salesforce leads and opportunities to PostgreSQL with backfill and incremental ETL

Salesforce Leads & Opportunities to PostgreSQL (Backfill & Incremental Sync ETL)

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.

It is designed for scalable data ingestion and supports both historical backfill and incremental sync in a single workflow.

---

Use Case

This workflow is suitable when you need to:

• Centralize Salesforce data into a database for reporting
• Build a data warehouse for BI tools (Looker Studio, Metabase, etc.)
• Track lead-to-opportunity lifecycle
• Merge multiple Salesforce objects into a unified dataset
• Maintain a clean and normalized CRM data layer

---

Two Input Modes

1. Historical Backfill (Manual Trigger)

Run once to populate historical data.

• Set start_date and end_date in the "Set Historical Date Range" node
• Data is split into 7-day batches
• Each batch is processed sequentially to reduce API load

---

2. Incremental Sync (Schedule Trigger)

Runs automatically (e.g. daily).

• Date range is generated dynamically using ISO datetime
• Typically pulls data from yesterday until today
• No manual input required

---

Batch Processing

Date ranges are processed in weekly batches.

This helps:

• Prevent large API requests
• Reduce timeout risk
• Improve stability during backfill
• Keep memory usage efficient

---

Core Workflow Logic

1. Data Extraction

• Fetch Lead records from Salesforce
• Fetch Opportunity records from Salesforce
• Filter using CreatedDate (since_datetime and until_datetime)

---

2. Phone-Based Routing

Records are split into two paths:

• Records without phone:

• Skip normalization

• Still included in final dataset

• Records with phone:

• Processed for normalization

• Used for merging

This ensures no data is lost even if phone is missing.

---

3. Phone Normalization (+62)

Phone numbers are standardized into:

+62XXXXXXXXXX

Steps:

• Remove spaces and symbols
• Remove all non-digit characters
• Convert 0xxxx → 62xxxx
• Ensure no duplicated prefix (e.g. 6262)
• Add "+" prefix

This uses Indonesia's International Direct Dialing (IDD) code: +62

---

4. Opportunity De-duplication

• Duplicate opportunities are removed
• Based on normalized phone key

This ensures clean merging and avoids duplicate enrichment.

---

5. Lead–Opportunity Merge

Merge is done using normalized phone fields:

• body.nomorlead
• body.nomoroppty

Behavior:

• Lead is the primary dataset
• Opportunity enriches lead

Records without phone:

• Still preserved
• Not removed

---

6. Data Standardization

All records are transformed into a unified schema:

• Source_Object
• SF_Id
• CreatedDate
• CreatedById
• Name
• Phone
• Clean_Phone
• Email
• LeadSource
• Status
• StageName
• OwnerId
• AccountId
• Amount

---

7. Upsert to PostgreSQL

• Uses UPSERT (insert or update)
• Matching key: sf_id

Behavior:

• New data → insert
• Existing data → update

Ensures:

• No duplicate records
• Idempotent execution

---

Data Flow Summary

Salesforce (Lead + Opportunity) → Date Filtering → Batch Processing (weekly) → Phone Routing → Phone Normalization (+62) → Opportunity Deduplication → Lead–Opportunity Merge → Data Standardization → PostgreSQL (Upsert)

---

Setup Requirements

Before using this workflow, prepare the following:

1. Salesforce

• Salesforce OAuth2 credential
• Access to:
• Lead object
• Opportunity object
• Ensure API access is enabled

---

2. PostgreSQL

• Active PostgreSQL database
• Credentials configured in n8n
• Table created (see schema below)

---

3. n8n Environment

• n8n instance (cloud or self-hosted)
• Salesforce node configured
• PostgreSQL node configured

---

4. Date Configuration (Backfill)

• Set start_date and end_date manually in: "Set Historical Date Range" node

---

5. Schedule Configuration (Incremental)

• Configure Schedule Trigger
• Recommended:
• Daily execution
• Off-peak hours

---

Minimal PostgreSQL Table Schema

CREATE TABLE n8n_salesforce_data ( sf_id TEXT PRIMARY KEY, Source_Object TEXT, CreatedDate TIMESTAMP, CreatedById TEXT, Name TEXT, Phone TEXT, Clean_Phone TEXT, Email TEXT, LeadSource TEXT, Status TEXT, StageName TEXT, OwnerId TEXT, AccountId TEXT, Amount NUMERIC, synced_at TIMESTAMP DEFAULT NOW() );

---

Important Notes

• sf_id is used as the unique key for upsert
• Clean_Phone is recommended for indexing if used in analytics
• Data consistency depends on phone normalization quality
• Schema must be updated manually if additional fields are added

---

Known Limitations

• Phone-based matching may fail if:

• Phone numbers are inconsistent

• Phone is missing in both Lead and Opportunity

• No deduplication for Leads (only Opportunities handled)

• No retry logic for API failures (can be added)

---

Recommended Improvements

• Add index on Clean_Phone for faster queries
• Add logging table for monitoring ETL runs
• Add retry and error handling nodes
• Extend support for:
• Contact
• Account
• Campaign data

---

Summary

This workflow provides a reliable and scalable way to:

• Extract Salesforce data
• Normalize and merge datasets
• Store structured data in PostgreSQL
• Enable analytics and reporting pipelines

It is best suited for teams building a lightweight data warehouse layer on top of Salesforce.