Loyalty points import fails with duplicate customer IDs during bulk upload

CRM SAP Customer Experience (SAP CX)
, , , , , , , ,
1

We’re migrating historical loyalty points data from our legacy system to SAP CX using CSV imports. The process consistently fails when processing customer records with duplicate customer IDs across different loyalty programs.

The CSV import utility throws duplicate key exceptions halfway through processing around 50,000 records. Our source data has customers enrolled in multiple programs (Gold, Silver, Platinum), and the aggregation logic seems to conflict when the same customer ID appears multiple times.

Error pattern we’re seeing:


ERROR: Duplicate entry 'CUST-10234' for key 'customer_loyalty.PRIMARY'
Failed at row 23,456 of 50,000
Rollback initiated

We’ve tried basic deduplication in Excel, but that loses the multi-program enrollment data. The migration is blocked, and management needs this completed for Q2 campaign planning. Has anyone successfully handled CSV import aggregation for loyalty data with customers in multiple segments?

2

I’ll walk you through the complete solution that addresses all three key aspects: CSV import aggregation, duplicate key error handling, and ETL preprocessing.

Step 1: ETL Preprocessing with Aggregation

First, you need to preprocess your CSV file to aggregate loyalty points by customer. Here’s a practical approach using Python pandas:

import pandas as pd
df = pd.read_csv('loyalty_source.csv')
aggregated = df.groupby('CustomerID').agg({'Points': 'sum', 'EnrollmentDate': 'min'}).reset_index()
aggregated.to_csv('loyalty_import_ready.csv', index=False)

This consolidates multiple program entries into single customer records with total points and earliest enrollment date.

Step 2: Duplicate Key Error Handling

The duplicate key error occurs because the CSV Import Utility processes records sequentially without in-memory deduplication. Even with ‘Update existing records’ enabled, it can’t handle duplicates within the same batch. Your options:

  • Option A: Use the aggregated CSV from Step 1 (recommended)
  • Option B: Enable the import tool’s ‘Skip duplicate records’ mode and run multiple passes, but this risks data loss
  • Option C: Split CSV by program and import with custom merge logic in SAP CX workflows

Step 3: Validation and Import Sequencing

Before importing the aggregated file:

  1. Run a validation check: SELECT CustomerID, COUNT(*) FROM staging_csv GROUP BY CustomerID HAVING COUNT(*) > 1 to confirm no duplicates remain
  2. Create a backup of existing loyalty data in SAP CX
  3. Use the Data Import Tool with these settings:
    • Mode: Insert new records only (first run) or Update/Insert (if re-running)
    • Batch size: 5,000 records (prevents timeout on large files)
    • Error handling: Log errors but continue processing
  4. Monitor the import job logs for any foreign key violations or constraint errors

Step 4: Post-Import Reconciliation

After import completes:

  • Compare record counts: source distinct customers vs imported records
  • Validate total points: SUM(points) in source should match SAP CX loyalty totals
  • Check for orphaned program enrollments if you need to preserve program-level detail

Alternative: Preserve Multi-Program Detail

If you need to maintain which points came from which program (Gold/Silver/Platinum), modify your data model:

  1. Import customers first (deduplicated list)
  2. Create a separate import for program enrollments with foreign key to customer
  3. Use SAP CX’s loyalty point allocation rules to sum across programs

This approach avoids aggregation loss but requires two import jobs with proper sequencing.

Error Log Analysis

For the specific error you encountered, enable detailed logging in the import tool settings. The error “Duplicate entry ‘CUST-10234’ for key ‘customer_loyalty.PRIMARY’” indicates the import reached the second occurrence of that customer ID at row 23,456. With aggregation, this customer would appear only once, eliminating the constraint violation.

The preprocessing ETL step is critical - it’s not just about deduplication, but proper business logic aggregation that preserves data integrity while meeting SAP CX’s import requirements.

3

Another approach is to keep the multi-program structure but change your import strategy. Instead of importing into a single loyalty points table, create separate import jobs for each program tier, then use SAP CX’s loyalty aggregation rules to calculate total points. This preserves the program-level detail for reporting while avoiding the duplicate key constraint. Check if your loyalty module supports hierarchical point structures.

4

I’ve seen this exact issue before. The problem is that SAP CX’s CSV import expects one record per customer, but your source has multiple rows per customer (one for each program). You need preprocessing with ETL to aggregate before import. The built-in import utility doesn’t handle row-level aggregation on the fly.