I’ll provide a complete solution addressing foreign key validation, migration sequencing, and ID mapping strategies.
Step 1: Dependency Analysis and Migration Sequencing
Before starting any import, map your entity dependencies:
- Level 0 (No dependencies): Customer segments, product catalogs, email templates
- Level 1 (Depends on Level 0): Marketing campaigns (references segments + templates)
- Level 2 (Depends on Level 1): Campaign executions, A/B test variants
Critical Rule: Always import parent entities before child entities that reference them.
Migration Sequence:
Phase 1: Import customer segments → capture ID mappings
Phase 2: Import email templates → capture ID mappings
Phase 3: Import product catalogs → capture ID mappings
Phase 4: Transform campaign data using ID mappings
Phase 5: Import transformed campaigns
Phase 6: Import campaign execution history (optional)
Step 2: ID Mapping Strategy
Create a mapping table approach to handle ID transformation:
Option A: External Mapping Database
Create a staging database with mapping tables:
CREATE TABLE segment_id_mapping (
legacy_segment_id VARCHAR(100),
scx_segment_id VARCHAR(36),
segment_name VARCHAR(255),
import_timestamp TIMESTAMP
);
During segment import, populate this table immediately after each successful import.
Option B: CSV Mapping Files
If you don’t have a staging database, use CSV mapping files:
- Import segments into SAP CX
- Export segments from SAP CX with new IDs
- Join export with legacy data on segment name/description
- Create mapping CSV: `legacy_id,scx_id,entity_name
- Use this mapping to transform campaign references
Step 3: Foreign Key Validation Pre-Import
Before importing campaigns, validate all foreign key references:
import pandas as pd
# Load mapping tables
segment_map = pd.read_csv('segment_id_mapping.csv')
template_map = pd.read_csv('template_id_mapping.csv')
# Load campaign data
campaigns = pd.read_csv('legacy_campaigns.csv')
# Validate segment references
invalid_segments = campaigns[~campaigns['segment_id'].isin(segment_map['legacy_segment_id'])]
if len(invalid_segments) > 0:
print(f"Warning: {len(invalid_segments)} campaigns reference non-existent segments")
invalid_segments.to_csv('campaigns_invalid_segments.csv')
This identifies orphaned references before import failure.
Step 4: ID Transformation Process
Transform campaign data to use SAP CX IDs:
# Map segment IDs
segment_lookup = dict(zip(segment_map['legacy_segment_id'], segment_map['scx_segment_id']))
campaigns['segment_id_new'] = campaigns['segment_id'].map(segment_lookup)
# Map template IDs
template_lookup = dict(zip(template_map['legacy_template_id'], template_map['scx_template_id']))
campaigns['template_id_new'] = campaigns['template_id'].map(template_lookup)
# Check for unmapped references
unmapped = campaigns[campaigns['segment_id_new'].isna()]
if len(unmapped) > 0:
print(f"Error: {len(unmapped)} campaigns have unmapped segment references")
# Handle unmapped references - either skip or assign default segment
# Export transformed campaigns
campaigns_transformed = campaigns[['campaign_name', 'segment_id_new', 'template_id_new', ...]]
campaigns_transformed.to_csv('campaigns_import_ready.csv', index=False)
Step 5: Handling Complex Multi-Reference Scenarios
If campaigns reference multiple segments (e.g., target segment + exclusion segment):
- Create separate columns for each reference type in your mapping
- Transform each reference independently
- Validate that all references are mapped before import
Step 6: Import Execution with Validation
When importing the transformed campaigns:
- Test Import: Import 10-20 campaigns first to validate the mapping
- Verify References: Check in SAP CX that campaigns correctly link to segments
- Batch Import: Process remaining campaigns in batches of 50-100
- Error Handling: Enable “Skip invalid records” to log failures without halting
- Post-Import Validation: Query SAP CX to confirm all foreign key relationships are intact
Step 7: Alternative Approach - API-Based Import with Live Lookup
For more dynamic control, use SAP CX OData API:
import requests
# During campaign import, look up segment by name instead of ID
def get_scx_segment_id(segment_name):
response = requests.get(
f"{scx_api_url}/Segments?$filter=name eq '{segment_name}'",
headers={'Authorization': f'Bearer {token}'}
)
return response.json()['d']['results'][0]['id']
# Import each campaign with resolved references
for campaign in legacy_campaigns:
scx_segment_id = get_scx_segment_id(campaign['segment_name'])
campaign_payload = {
'name': campaign['name'],
'segmentId': scx_segment_id,
'templateId': get_scx_template_id(campaign['template_name'])
}
requests.post(f"{scx_api_url}/Campaigns", json=campaign_payload, headers=headers)
This approach eliminates pre-transformation but is slower for large datasets.
Step 8: Maintaining Referential Integrity
After import:
-
Run integrity checks in SAP CX:
- Query campaigns with null segment references
- Verify template associations
- Check for orphaned campaign executions
-
Create a reconciliation report:
- Total campaigns imported vs source count
- Foreign key validation results
- List of campaigns requiring manual review
Best Practices
- Never import child entities before parents - always respect dependency hierarchy
- Maintain audit trail - keep all mapping files for troubleshooting
- Use business keys for matching - segment names, template codes (more reliable than legacy IDs)
- Test with subset - validate the entire process with 50 campaigns before full migration
- Plan for rollback - have a strategy to revert if critical data is corrupted
Handling Your Specific Error
For the error you encountered (segment SEG-GOLD-TIER-2023 not found):
- Verify the segment was successfully imported in Phase 1
- Check if the segment name matches between legacy and SAP CX
- Confirm the mapping table includes this segment
- Update the campaign CSV with the new UUID before re-importing
- If segment is missing, import it first, then retry campaign import
This systematic approach ensures referential integrity and prevents the foreign key constraint violations you’re experiencing.