Stripe Customer Duplicate Detection & Management

{
  "id": "Rr0vbFBRhTCMYSuP",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Stripe Customer Duplicate Detection & Management",
  "tags": [],
  "nodes": [
    {
      "id": "38d831a3-05c2-4de0-94db-a1669fa98ec7",
      "name": "Workflow Description",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -928,
        352
      ],
      "parameters": {
        "width": 389,
        "height": 672,
        "content": "## 🎯 Stripe Customer Duplicate Detection & Management\n\nThis workflow automatically scans your Stripe customers daily to identify potential duplicates and helps you maintain a clean customer database.\n\n### What this workflow does:\n• Fetches all customers from Stripe daily at 2 AM\n• Uses advanced fuzzy matching to detect duplicates by email and name\n• Logs findings to Airtable for review and approval\n• Sends detailed Slack notifications with actionable insights\n\n### Key Features:\n• Email-based duplicate detection (99% confidence)\n• Name similarity matching using Levenshtein distance\n• Automated logging with status tracking\n• Smart grouping and prioritization\n• Detailed Slack reports with statistics\n\n### Setup Requirements:\n1. Stripe API credentials\n2. Airtable base with duplicate tracking table\n3. Slack workspace integration\n"
      },
      "typeVersion": 1
    },
    {
      "id": "3a339de6-55b4-45ae-82e5-77455a181e74",
      "name": "Schedule Setup",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -512,
        48
      ],
      "parameters": {
        "width": 236,
        "height": 408,
        "content": "## ⏰ Daily Trigger Setup\n\nScheduled to run every day at 2 AM to avoid peak business hours.\n\n**Cron Expression:** `0 2 * * *`\n- 0: minute (0)\n- 2: hour (2 AM)\n- *: any day of month\n- *: any month\n- *: any day of week\n\n💡 **Tip:** You can modify the schedule based on your timezone and business needs."
      },
      "typeVersion": 1
    },
    {
      "id": "6994f890-98d7-47fc-9e77-2b25d636ae13",
      "name": "Daily Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -432,
        480
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 2 * * *"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "8cf9c8a7-38fc-47c9-a83c-68537ae947c3",
      "name": "Stripe Setup",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -336,
        656
      ],
      "parameters": {
        "width": 300,
        "height": 444,
        "content": "## 💳 Stripe Customer Fetch\n\nRetrieves all customers from your Stripe account for duplicate analysis.\n\n**Setup Steps:**\n1. Create Stripe API credentials in n8n\n2. Use your Stripe Secret Key (starts with sk_)\n3. Ensure the key has read permissions for customers\n\n**Security Note:** Never hardcode API keys - always use n8n credentials manager.\n\n⚠️ **Important:** This fetches ALL customers. For large datasets (10k+ customers), consider adding pagination or filters."
      },
      "typeVersion": 1
    },
    {
      "id": "8e276971-d826-4307-bd90-547ec767a7bd",
      "name": "Fetch All Stripe Customers",
      "type": "n8n-nodes-base.stripe",
      "position": [
        -208,
        480
      ],
      "parameters": {
        "filters": {},
        "resource": "customer",
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "stripeApi": {
          "id": "DV4tPpxjbOUkGfAx",
          "name": "Stripe account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "5f0f09fd-3d34-4839-8a51-d865eed0bc88",
      "name": "Detection Algorithm",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        -64
      ],
      "parameters": {
        "width": 300,
        "height": 532,
        "content": "## 🔍 Duplicate Detection Logic\n\nAdvanced algorithm that identifies potential duplicates using:\n\n**Email Matching (99% confidence):**\n- Exact email matches across customers\n- Case-insensitive comparison\n- Highest priority for merging\n\n**Name Similarity (80%+ threshold):**\n- Uses Levenshtein distance algorithm\n- Handles typos and variations\n- Configurable similarity threshold\n\n**Smart Grouping:**\n- Oldest customer becomes primary\n- All others suggested for merge\n- Prevents duplicate processing\n\nThe algorithm processes customers efficiently and avoids false positives."
      },
      "typeVersion": 1
    },
    {
      "id": "3f53c9e4-9a5a-4519-ae46-8e27b60f3890",
      "name": "Analyze Customer Duplicates",
      "type": "n8n-nodes-base.function",
      "position": [
        32,
        480
      ],
      "parameters": {
        "functionCode": "// Function to calculate Levenshtein distance\nfunction levenshteinDistance(str1, str2) {\n  const matrix = [];\n  \n  for (let i = 0; i <= str2.length; i++) {\n    matrix[i] = [i];\n  }\n  \n  for (let j = 0; j <= str1.length; j++) {\n    matrix[0][j] = j;\n  }\n  \n  for (let i = 1; i <= str2.length; i++) {\n    for (let j = 1; j <= str1.length; j++) {\n      if (str2.charAt(i - 1) === str1.charAt(j - 1)) {\n        matrix[i][j] = matrix[i - 1][j - 1];\n      } else {\n        matrix[i][j] = Math.min(\n          matrix[i - 1][j - 1] + 1,\n          matrix[i][j - 1] + 1,\n          matrix[i - 1][j] + 1\n        );\n      }\n    }\n  }\n  \n  return matrix[str2.length][str1.length];\n}\n\n// Function to calculate similarity percentage\nfunction calculateSimilarity(str1, str2) {\n  if (!str1 || !str2) return 0;\n  \n  const maxLength = Math.max(str1.length, str2.length);\n  if (maxLength === 0) return 100;\n  \n  const distance = levenshteinDistance(str1.toLowerCase(), str2.toLowerCase());\n  return Math.round(((maxLength - distance) / maxLength) * 100);\n}\n\n// Extract customers array from items - each item contains one customer\nconst customers = items.map(item => item.json);\nconst suggestions = [];\nconst processedCustomers = new Set();\n\nconsole.log(`Processing ${customers.length} customers for duplicates`);\n\n// Group customers by email and name for efficient duplicate detection\nconst emailGroups = new Map();\nconst nameGroups = new Map();\n\ncustomers.forEach(customer => {\n  // Group by email\n  if (customer.email) {\n    const emailKey = customer.email.toLowerCase();\n    if (!emailGroups.has(emailKey)) {\n      emailGroups.set(emailKey, []);\n    }\n    emailGroups.get(emailKey).push(customer);\n  }\n  \n  // Group by name (for name similarity matching)\n  if (customer.name) {\n    const nameKey = customer.name.toLowerCase();\n    if (!nameGroups.has(nameKey)) {\n      nameGroups.set(nameKey, []);\n    }\n    nameGroups.get(nameKey).push(customer);\n  }\n});\n\n// Process email-based duplicates\nemailGroups.forEach((group, email) => {\n  if (group.length > 1) {\n    // Sort by creation date to make the oldest one primary\n    group.sort((a, b) => a.created - b.created);\n    const primary = group[0];\n    \n    // Create suggestions for all others against the primary\n    for (let i = 1; i < group.length; i++) {\n      const secondary = group[i];\n      if (!processedCustomers.has(secondary.id)) {\n        let confidenceScore = 95;\n        let matchReason = 'Email match';\n        \n        // Higher confidence if names also match\n        if (primary.name && secondary.name && primary.name.toLowerCase() === secondary.name.toLowerCase()) {\n          confidenceScore = 99;\n          matchReason = 'Email + Name exact match';\n        }\n        \n        suggestions.push({\n          primary_customer_id: primary.id,\n          secondary_customer_id: secondary.id,\n          email: primary.email || secondary.email || '',\n          name_similarity_score: confidenceScore,\n          primary_name: primary.name || '',\n          secondary_name: secondary.name || '',\n          match_reason: matchReason,\n          status: 'Pending Review'\n        });\n        \n        processedCustomers.add(secondary.id);\n      }\n    }\n    processedCustomers.add(primary.id);\n  }\n});\n\n// Process name-based duplicates (only for customers not already processed by email)\nnameGroups.forEach((group, name) => {\n  if (group.length > 1) {\n    // Filter out customers already processed by email matching\n    const unprocessedGroup = group.filter(customer => !processedCustomers.has(customer.id));\n    \n    if (unprocessedGroup.length > 1) {\n      // Sort by creation date to make the oldest one primary\n      unprocessedGroup.sort((a, b) => a.created - b.created);\n      const primary = unprocessedGroup[0];\n      \n      // Create suggestions for all others against the primary\n      for (let i = 1; i < unprocessedGroup.length; i++) {\n        const secondary = unprocessedGroup[i];\n        const nameSimilarity = calculateSimilarity(primary.name, secondary.name);\n        \n        if (nameSimilarity >= 80) {\n          suggestions.push({\n            primary_customer_id: primary.id,\n            secondary_customer_id: secondary.id,\n            email: primary.email || secondary.email || '',\n            name_similarity_score: nameSimilarity,\n            primary_name: primary.name || '',\n            secondary_name: secondary.name || '',\n            match_reason: 'Name similarity',\n            status: 'Pending Review'\n          });\n        }\n      }\n    }\n  }\n});\n\nconsole.log(`Found ${suggestions.length} duplicate suggestions`);\n\n// Return suggestions as n8n items format\nreturn suggestions.map(suggestion => ({ json: suggestion }));"
      },
      "typeVersion": 1
    },
    {
      "id": "31ac8a3a-a2ac-4468-96d3-43eefb377823",
      "name": "Airtable Configuration",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        144,
        624
      ],
      "parameters": {
        "width": 320,
        "height": 524,
        "content": "## 📊 Airtable Logging Setup\n\nLogs all duplicate suggestions to Airtable for review and approval workflow.\n\n**Required Table Fields:**\n- Primary Customer ID (Text)\n- Secondary Customer ID (Text)\n- Email (Email)\n- Name Similarity Score (Number)\n- Primary Name (Text)\n- Secondary Name (Text)\n- Match Reason (Single Select)\n- Status (Single Select: Pending Review, Approved, Rejected)\n\n**Setup Steps:**\n1. Create Airtable Personal Access Token\n2. Replace the hardcoded base and table IDs with your own\n3. Set up the table structure as described above\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2a1648e3-4276-45b6-8dd1-37e9cd9f6b70",
      "name": "Log to Airtable Database",
      "type": "n8n-nodes-base.airtable",
      "position": [
        256,
        480
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "id",
          "value": "{{ $env.AIRTABLE_TABLE_ID }}"
        },
        "options": {},
        "operation": "append",
        "application": {
          "__rl": true,
          "mode": "id",
          "value": "{{ $env.AIRTABLE_BASE_ID }}"
        },
        "authentication": "airtableTokenApi"
      },
      "credentials": {
        "airtableTokenApi": {
          "id": "nWc9JHR6t25WPWVV",
          "name": "Airtable Personal Access Token account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c8a3224a-1da5-4230-9edd-909afa20757a",
      "name": "Message Format",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        -48
      ],
      "parameters": {
        "width": 300,
        "height": 520,
        "content": "## 📝 Message Formatting\n\nCreates a detailed Slack message with:\n\n**Summary Statistics:**\n- Total duplicate suggestions found\n- Number of customer groups affected\n- Total customers involved\n\n**Match Type Breakdown:**\n- Email matches (highest confidence)\n- Name similarity matches\n- Combined email + name matches\n\n**Top Duplicate Groups:**\n- Shows the most problematic duplicates\n- Includes customer count per group\n- Limited to top 3 for readability\n\n**Action Items:**\n- Direct link to Airtable for review\n- Clear next steps for the team"
      },
      "typeVersion": 1
    },
    {
      "id": "dfbf1b63-22a0-4d4c-a4e9-07565b5cab82",
      "name": "Format Notification Message",
      "type": "n8n-nodes-base.function",
      "position": [
        464,
        480
      ],
      "parameters": {
        "functionCode": "const suggestions = items;\nconst count = suggestions.length;\nconst airtableLink = `https://airtable.com/${process.env.AIRTABLE_BASE_ID}/${process.env.AIRTABLE_TABLE_ID}`;\n\n// Initialize these variables outside the if/else blocks\nconst emailGroups = new Map();\nconst matchReasons = new Map();\n\nlet message = '';\n\nif (count === 0) {\n  message = '✅ No duplicate customers found in today\\'s scan.';\n} else {\n  // Analyze the suggestions for better insights\n  suggestions.forEach(item => {\n    const suggestion = item.json ? item.json : item.fields; // Handle both formats\n    \n    // Group by email\n    const email = suggestion.email;\n    if (email) {\n      if (!emailGroups.has(email)) {\n        emailGroups.set(email, new Set());\n      }\n      emailGroups.get(email).add(suggestion.primary_customer_id);\n      emailGroups.get(email).add(suggestion.secondary_customer_id);\n    }\n    \n    // Count match reasons\n    const reason = suggestion.match_reason || 'Unknown';\n    matchReasons.set(reason, (matchReasons.get(reason) || 0) + 1);\n  });\n  \n  // Create detailed message\n  const uniqueCustomerGroups = emailGroups.size;\n  const totalDuplicateCustomers = Array.from(emailGroups.values()).reduce((sum, customerSet) => sum + customerSet.size, 0);\n  \n  message = `⚠️ *Duplicate Customer Alert*\\n\\n`;\n  message += `📊 *Summary:*\\n`;\n  message += `• ${count} duplicate suggestion${count > 1 ? 's' : ''} found\\n`;\n  message += `• ${uniqueCustomerGroups} customer group${uniqueCustomerGroups > 1 ? 's' : ''} affected\\n`;\n  message += `• ${totalDuplicateCustomers} total customers involved\\n\\n`;\n  \n  // Add breakdown by match reason\n  if (matchReasons.size > 0) {\n    message += `🔍 *Match Types:*\\n`;\n    for (const [reason, reasonCount] of matchReasons) {\n      let emoji = '📧';\n      if (reason && reason.includes && reason.includes('Name')) emoji = '👤';\n      if (reason && reason.includes && reason.includes('Email + Name')) emoji = '💯';\n      \n      message += `${emoji} ${reason}: ${reasonCount}\\n`;\n    }\n    message += `\\n`;\n  }\n  \n  // Add top duplicate groups (limit to 3 for brevity)\n  const sortedGroups = Array.from(emailGroups.entries())\n    .sort((a, b) => b[1].size - a[1].size)\n    .slice(0, 3);\n  \n  if (sortedGroups.length > 0) {\n    message += `🎯 *Top Duplicate Groups:*\\n`;\n    sortedGroups.forEach(([email, customerIds], index) => {\n      message += `${index + 1}. ${email} (${customerIds.size} customers)\\n`;\n    });\n    message += `\\n`;\n  }\n  \n  message += `👀 *Action Required:* Please review and approve merges in Airtable\\n`;\n  message += `🔗 *Review Link:* ${airtableLink}`;\n}\n\nreturn [{\n  json: {\n    message: message,\n    count: count,\n    unique_groups: emailGroups.size || 0,\n    total_customers_affected: Array.from(emailGroups.values()).reduce((sum, customerSet) => sum + customerSet.size, 0) || 0,\n    match_reasons: Object.fromEntries(matchReasons),\n    airtable_link: airtableLink\n  }\n}];"
      },
      "typeVersion": 1
    },
    {
      "id": "b3d6a5cd-e852-4694-a27e-3d5f53b4a81d",
      "name": "Slack Setup",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        640,
        640
      ],
      "parameters": {
        "width": 300,
        "height": 600,
        "content": "## 💬 Slack Notification Setup\n\nSends detailed duplicate reports to your team Slack channel.\n\n**Setup Steps:**\n1. Create Slack App with Bot Token\n2. Add bot to your target channel\n3. Replace hardcoded channel ID with your channel\n4. Grant necessary permissions (chat:write)\n\n**Message Features:**\n- Markdown formatting for better readability\n- Emoji indicators for different match types\n- Direct links to Airtable for action\n- Summary statistics for quick overview\n\n**Security:** Use environment variables for sensitive channel IDs and tokens.\n\n💡 **Tip:** Consider using different channels for different alert levels."
      },
      "typeVersion": 1
    },
    {
      "id": "228175d2-da58-4ccc-afda-b7f358f6a3d1",
      "name": "Send Slack Notification",
      "type": "n8n-nodes-base.slack",
      "position": [
        672,
        480
      ],
      "webhookId": "1a3b3a22-c489-4a9d-ae15-80fb2b6f0ea5",
      "parameters": {
        "text": "={{ $json.message }}",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "{{ $env.SLACK_CHANNEL_ID }}",
          "cachedResultName": "duplicate-alerts"
        },
        "otherOptions": {
          "mrkdwn": true
        }
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account"
        }
      },
      "typeVersion": 2.3
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "15516be5-62fa-4865-a572-48dd92f7294b",
  "connections": {
    "Daily Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch All Stripe Customers",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Log to Airtable Database": {
      "main": [
        [
          {
            "node": "Format Notification Message",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch All Stripe Customers": {
      "main": [
        [
          {
            "node": "Analyze Customer Duplicates",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Customer Duplicates": {
      "main": [
        [
          {
            "node": "Log to Airtable Database",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Notification Message": {
      "main": [
        [
          {
            "node": "Send Slack Notification",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}