Introduction
In data-driven companies, the Data & Analytics department often struggles with preparing raw CRM exports for meaningful analysis. CRM exports typically contain inconsistent formatting, duplicate records, missing data, or irrelevant fields, which require tedious manual cleaning. Automating this process not only saves time but also ensures data consistency and accuracy for downstream analytics and reporting. In this article, we will walk you through building an automation workflow using n8n, a powerful open-source workflow automation tool, to clean CRM export files before feeding them into your data pipelines or BI tools.
Who Benefits?
– Data Analysts and Data Engineers: Automates repetitive data cleaning tasks.
– Startup CTOs and Ops Specialists: Ensures clean, consistent data for decision-making.
– Marketing and Sales Teams: Improves the reliability of their CRM data insights.
Tools and Services Integrated
– n8n: Workflow automation and orchestration platform.
– Google Drive or Dropbox: Storage for exported CRM files (CSV/Excel).
– Google Sheets: Optional for reviewing cleaned data.
– CRM systems, e.g., HubSpot, Salesforce (source of export files).
Workflow Overview
The workflow will trigger when a new CRM export file (CSV) lands in a specified cloud storage folder (Google Drive). It will then:
1. Read the file contents.
2. Parse the CSV data.
3. Run data transformations and cleaning:
– Remove duplicates based on primary keys like email or contact ID.
– Normalize date formats.
– Trim whitespace from string fields.
– Fill or flag missing critical fields.
– Remove or reformat irrelevant columns.
4. Output a cleaned CSV file saved back to cloud storage.
5. Optionally, update a Google Sheet for quick review.
Step-by-Step Technical Tutorial
Prerequisites
– An n8n instance (self-hosted or n8n cloud).
– Access to a cloud storage connected account (Google Drive).
– Example CRM export files (CSV).
Step 1: Setup Trigger Node – Google Drive Trigger or Poller
– Use the “Google Drive Trigger” or “Polling” node to monitor a specific folder for new CSV files.
– Scope the node to trigger only when new files appear, filtering by .csv extension.
Configuration:
– Select Google Drive OAuth2 credentials.
– For example, monitor a folder named “CRM Exports”.
Step 2: Read File Node – Google Drive
– Add a “Google Drive” node configured to get the file content.
– Configuration:
– Operation: Download
– File ID: Use output from the trigger node that gives the new file’s ID.
– This downloads the CSV data content as a binary.
Step 3: Parse CSV Node – CSV to JSON Conversion
– Insert a “CSV” node to parse the file’s binary data into JSON objects.
– Configuration:
– Operation: Parse
– Input: Connect to the previous node’s binary data.
– Set correct delimiter (usually comma).
Step 4: Data Cleaning and Transformation – Function or Set Nodes
– Add a “Function” node to run JavaScript for advanced data cleaning.
Inside the Function node script:
– Remove duplicate records:
Use a Set or object to track unique keys, e.g., email or contact ID.
– Normalize date fields:
Use `Date` objects to convert various formats to ISO strings.
– Trim whitespace:
Call `.trim()` on string fields.
– Handle missing critical fields:
Flag records missing email or phone, or filter them out.
– Remove irrelevant columns:
Delete keys that are not needed for analysis.
Example snippet inside Function node:
“`javascript
const uniqueRecords = new Map();
return items.filter(item => {
const email = item.json.email?.trim().toLowerCase();
if (!email) return false; // Filter out if email missing
if (uniqueRecords.has(email)) return false; // Duplicate
uniqueRecords.set(email, true);
// Normalize dates
if (item.json.createdAt) {
const date = new Date(item.json.createdAt);
item.json.createdAt = isNaN(date) ? null : date.toISOString();
}
// Trim all strings
for (let key in item.json) {
if (typeof item.json[key] === ‘string’) {
item.json[key] = item.json[key].trim();
}
}
// Remove irrelevant columns
delete item.json.tempNotes;
delete item.json.unusedField;
return true;
});
“`
Step 5: Convert Cleaned JSON back to CSV
– Add a CSV node to convert the cleaned JSON back to CSV format.
– Configuration:
– Operation: Serialize
– Columns: Explicitly define relevant columns or leave empty for automatic
– This transforms the cleaned data back into a CSV binary.
Step 6: Upload Cleaned File to Cloud Storage
– Use a “Google Drive” node to upload the cleaned CSV file.
– Configuration:
– Operation: Upload
– Specify target folder, e.g., “CRM Cleaned”
– File Name: Append a suffix like “_cleaned” plus timestamp
– Binary Data: Use output from the CSV serialize node
Step 7 (Optional): Update Google Sheets for Visualization
– If you want an immediate review, add a “Google Sheets” node to append rows or replace content in a sheet.
– Map relevant fields from cleaned JSON.
Common Errors and Robustness Tips
– Authentication errors with cloud services: Ensure OAuth2 credentials are valid and refreshed.
– Large files may time out or hit memory limits; consider batching or chunk processing.
– Date parsing may fail if source data has inconsistent formats; add validation or fallback.
– Duplicate detection keys must be consistent and unique; verify field availability.
– Monitor node execution logs within n8n to catch parsing or runtime errors.
Scaling and Adaptation
– To scale, implement pagination or chunking for large exports.
– Extend the workflow to ingest CRM exports from multiple systems by parameterizing folders or credentials.
– Integrate notifications (Slack or Email) to alert the team when cleaned files are ready.
– Add additional data enrichment steps, like geocoding addresses or validating emails.
– Schedule periodic runs or real-time triggers depending on your CRM export frequency.
Summary
Automating the cleaning of CRM exports with n8n streamlines your data preparation, reduces manual errors, and accelerates analytics workflows. By following this step-by-step guide, your Data & Analytics team can implement a reusable, scalable pipeline that converts raw, messy CRM data into trusted, analysis-ready datasets. Leverage n8n’s flexibility to expand and customize the workflow as your data landscape evolves.
Bonus Tip
Store logs or summaries of each cleaned batch (number of records processed, duplicates removed) into a database or dashboard for auditability and continuous improvement of your data quality processes.