Data Management Best Practices

Example: 100,000 Member Program with Poor Data Quality

Email Bounce Rate: 20% (should be <5%)
  - Wasted emails: 20,000 per send
  - Cost: RM0.05 per email × 20,000 × 12 monthly = RM12,000/year

Duplicate Members: 8% (should be <1%)
  - False member count: 8,000 duplicates
  - Inflated liability: Points issued to duplicates
  - Skewed analytics and reporting

Failed Reward Shipments: 12% (should be <2%)
  - Cost per failed shipment: RM15
  - Monthly reward shipments: 500
  - Failed shipments: 60/month × RM15 = RM900/month = RM10,800/year

Total Estimated Annual Cost: >RM25,000 + inflated liability + poor analytics

Critical Fields (Must be 100% complete):
  - Member ID (system-generated)
  - Full name (first + last)
  - Primary contact method (phone OR email)
  - Join date
  - Initial tier

Important Fields (Target 95%+ complete):
  - Mobile phone number
  - Email address
  - Date of birth
  - Preferred communication channel

Optional Fields (Nice to have):
  - Mailing address
  - Secondary contact
  - Preferences and interests
  - Communication opt-in preferences

Quality Metric: Completeness Score = (Populated Required Fields / Total Required Fields) × 100
Target: >98% for critical fields, >95% for important fields

Accuracy Checks:

Phone Numbers:
  - Format validation (Malaysia: +60-XX-XXXX-XXXX)
  - Area code validation
  - Length validation
  - No obvious test data (123456, 000000)

Email Addresses:
  - Format validation (contains @, valid domain)
  - Domain existence check
  - No obvious fake emails (test@test.com, asdf@asdf.com)
  - Catch typos (gamil.com → gmail.com)

Dates:
  - Valid date ranges (birthdays: 1920-current year minus 10)
  - Logical consistency (join date not in future)
  - Format standardization (YYYY-MM-DD)

Names:
  - Proper capitalization
  - Remove numbers and special characters
  - No profanity or obvious fake names

Addresses:
  - Postal code validation
  - State/city consistency
  - Address standardization

Cross-System Consistency:
  - Customer name matches between POS and membership
  - Phone number identical in all systems
  - Email address consistent across platforms
  - Address synchronized between e-commerce and membership

Temporal Consistency:
  - Join date doesn't change
  - Birthday doesn't change (unless error correction)
  - Historical transactions remain accurate
  - Tier history maintains integrity

Duplicate Detection Rules:

Exact Match (High Confidence Duplicate):
  - Same phone number AND same email
  - Same phone number AND same name
  - Same email AND same name
  Action: Flag for immediate review and merge

Probable Match (Medium Confidence):
  - Similar name (fuzzy match >90%) AND same birthday
  - Same phone number, different email (possible typo)
  - Same email, different phone (phone update)
  Action: Flag for investigation

Possible Match (Low Confidence):
  - Similar name AND similar address
  - Same last name AND same phone area code
  Action: Monitor, flag if transaction patterns similar

Update Frequency Requirements:

Real-Time Updates:
  - Points balance after transaction
  - Tier status after qualification
  - Voucher generation
  - Account activation/deactivation

Near Real-Time (Within minutes):
  - Contact information changes
  - Communication preferences
  - Address updates
  - Birthday corrections

Daily Batch Updates:
  - Tier qualification checks
  - Points expiry processing
  - Birthday lists for upcoming month
  - Inactive member identification

Monthly Updates:
  - Data quality scores
  - Duplicate detection runs
  - Data completeness reports
  - Member segmentation refresh

Essential Fields (Required at Enrollment):
  - Full name
  - Mobile phone number OR email address (at least one)
  - Date of birth (for birthday rewards)
  - PDPA consent checkbox

Optional Fields (Can be collected later):
  - Mailing address (collect when needed for shipment)
  - Gender (collect if relevant for personalization)
  - Preferences (collect progressively through engagement)
  - Secondary contact information

Progressive Profiling Strategy:
  - Enrollment: Minimum required fields only
  - First purchase: Mailing address if needed
  - Post-purchase survey: Preferences and interests
  - Profile completion incentive: Bonus points for completing profile

Phone Number Validation:
  - Format check: +60-XX-XXXX-XXXX or local format
  - Length validation: 10-12 digits
  - Not sequential (12345678)
  - Not repetitive (88888888)
  - Real-time feedback: "Invalid phone format"

Email Validation:
  - Format check: contains @ and valid TLD
  - Domain existence: DNS lookup
  - Common typo correction: gamil.com → gmail.com
  - Duplicate check: Email already registered?
  - Real-time feedback: "Email already in use"

Date of Birth Validation:
  - Age range: 10-120 years old
  - Not future date
  - Format: DD/MM/YYYY
  - Birthday reward eligibility: Age 18+ required?

Name Validation:
  - Minimum 2 characters
  - No numbers
  - No excessive special characters
  - Auto-capitalize properly

First Purchase:
  Capture: Shipping address if e-commerce
  Capture: Product category preferences from purchase
  Update: Email if only phone collected initially

Profile Completion Prompts:
  Timing: After 2-3 transactions
  Incentive: "Complete your profile, earn 100 bonus points"
  Fields: Address, gender, communication preferences
  Method: In-app prompt or email

Post-Purchase Surveys:
  Timing: 7 days after purchase
  Questions: Product satisfaction, category interests
  Incentive: Small point bonus (50 points)
  Capture: Preferences for personalization

Birthday Month:
  Confirm: Birthday on record is correct
  Update: Correction if needed
  Capture: Gift preferences for future birthdays

Tier Upgrade:
  Update: Shipping address for tier kit
  Capture: Premium tier preferences
  Confirm: Current contact information

Completeness Audit:
  - % of members with complete critical fields
  - % of members with email addresses
  - % of members with mobile phone
  - % of members with date of birth
  Target: >95% for all critical fields

Accuracy Audit:
  - Email bounce rate from last campaign
  - SMS delivery failure rate
  - Invalid phone number percentage
  - Address validation failure rate
  Target: <5% bounce/failure rates

Duplicate Audit:
  - Run duplicate detection algorithm
  - High confidence matches: Immediate merge
  - Medium confidence: Manual review
  - Track duplicate rate over time
  Target: <1% duplicate rate

Activity Audit:
  - Identify inactive members (no activity 12+ months)
  - Flag for re-engagement or purge
  - Update member status accordingly
  Target: <15% long-term inactive rate

Email Validation:
  - Use email validation service
  - Remove hard bounces permanently
  - Flag soft bounces for review
  - Update status to "email invalid"

Phone Number Validation:
  - Verify active numbers (if service available)
  - Update mobile carrier information
  - Flag disconnected numbers
  - Standardize formats

Address Validation:
  - Use postal service address validation
  - Standardize address formats
  - Update postal codes
  - Flag incomplete addresses

Duplicate Resolution:
  - Identify all probable duplicates
  - Manual review of medium confidence matches
  - Merge confirmed duplicates
  - Document merge rationale

Self-Service Channels:
  - Mobile app: Profile edit screen
  - Website: Account settings page
  - In-store: Kiosk or staff-assisted update
  - Phone: Customer service verification then update

Update Workflow:
  1. Member initiates change
  2. System validates new data
  3. For critical changes (phone, email): Verify via OTP
  4. Update primary system
  5. Sync to all integrated systems
  6. Confirmation message to member

Verification for Critical Updates:
  Phone Number Change:
    - Send OTP to new phone number
    - Verify OTP before updating
    - Notification to old number about change

  Email Address Change:
    - Send verification link to new email
    - Confirm via link before updating
    - Notification to old email about change

Bounced Email Detection:
  - Hard bounce: Mark email invalid, request update
  - Soft bounce: Track, flag after 3 consecutive bounces
  - Updated email from purchase: Suggest update in app

Failed SMS Delivery:
  - Track delivery failures
  - Flag number after 3 consecutive failures
  - Prompt member to update phone number

Address Return Mail:
  - Reward shipment returned: Flag address as invalid
  - Request address update from member
  - Hold future shipments until updated

POS Data Corrections:
  - Cashier notices wrong phone: Update during transaction
  - Member provides corrected email: Update immediately
  - Name spelling correction: Update after verification

Explicit Consent Collection:
  - Clear consent checkbox (not pre-checked)
  - Plain language explanation of data use
  - Separate consents for different purposes
  - Option to decline marketing while enrolling

Consent Purposes:
  □ Membership program participation (required)
  □ Marketing communications via email (optional)
  □ Marketing communications via SMS (optional)
  □ Marketing communications via phone (optional)
  □ Sharing data with partners (optional)
  □ Data analytics for personalization (optional)

Consent Record-Keeping:
  - Date/time of consent
  - Consent method (online form, paper, verbal)
  - Specific consents granted
  - IP address for online consents
  - Audit trail of consent changes

Right to Access:
  - Member requests copy of their data
  - Response timeline: 21 days
  - Format: Readable, structured (PDF or CSV)
  - Include: All personal data, transaction history, consents
  - Process: Online request form, verify identity, deliver securely

Right to Correction:
  - Member requests data correction
  - Response timeline: 21 days
  - Verify correction request
  - Update all systems
  - Confirm completion to member

Right to Withdraw Consent:
  - Member can opt out of marketing
  - Immediate processing required
  - Update communication preferences
  - Confirm opt-out to member
  - Maintain membership if only marketing opt-out

Right to Deletion (Right to be Forgotten):
  - Member requests account deletion
  - Verify identity
  - Close account and deactivate
  - Anonymize or delete data per retention policy
  - Confirm deletion to member
  - Retain legally required data only (transaction records: 7 years)

Active Members:
  - All data retained while account active
  - Regular updates and synchronization

Inactive Members (12+ months no activity):
  - Retain data for re-engagement
  - Suppress from marketing if 24+ months inactive
  - Consider deletion after 36+ months per policy

Deleted Accounts:
  - Anonymize personal information
  - Retain transaction history (legal requirement: 7 years)
  - Remove from marketing databases immediately
  - Purge from backup systems per schedule

Legal Hold:
  - Retain data under legal investigation
  - Document hold reason and authority
  - Restore from archive if needed
  - Release after hold lifted

Consent:
  - Must be freely given, specific, informed, unambiguous
  - Cannot pre-check consent boxes
  - Must be as easy to withdraw as to give
  - Cannot make enrollment conditional on non-essential consents

Data Minimization:
  - Collect only data necessary for stated purpose
  - Justify every field collected
  - Delete when no longer needed

Right to Portability:
  - Provide data in machine-readable format
  - Enable transfer to another service
  - Timeline: 30 days

Breach Notification:
  - Notify supervisory authority within 72 hours
  - Notify affected members if high risk
  - Document all breaches

Source of Truth:
  Customer Master Data: Customer Maintenance Applet
  Membership Data: Membership Admin Console
  Points Balance: Membership Points Currency system
  Transactions: POS/E-Commerce systems

Synchronization Patterns:

Real-Time Sync (Immediate):
  - Points earned from transaction
  - Points redeemed during checkout
  - Tier upgrade/downgrade
  - Account activation/deactivation
  Method: API calls, event-driven

Near Real-Time (Within 5 minutes):
  - Profile updates from member
  - Contact information changes
  - Communication preferences
  Method: Change data capture, queue processing

Batch Sync (Scheduled):
  - Nightly: Transaction reconciliation
  - Daily: Tier qualification checks
  - Weekly: Data quality metrics
  - Monthly: Member segmentation
  Method: Scheduled jobs, file transfer

Conflict Resolution:
  - Timestamp-based: Most recent update wins
  - System priority: Customer Maintenance > others
  - Manual review: Flag conflicts for human decision

Key Metrics:
  - Sync latency: Time from change to propagation
  - Sync success rate: % of successful syncs
  - Sync failures: Count and reasons
  - Data consistency: % match across systems

Alerts:
  - Sync latency > 15 minutes: Warning
  - Sync latency > 1 hour: Critical
  - Sync failure rate > 5%: Warning
  - Sync failure rate > 10%: Critical
  - Data inconsistency detected: Immediate investigation

Reconciliation:
  - Daily: Points balance reconciliation
  - Weekly: Member count reconciliation
  - Monthly: Full data consistency check
  - Quarterly: Deep reconciliation audit

Metrics:
  - Total members by tier
  - New enrollments (daily, weekly, monthly)
  - Active members (purchased last 90 days)
  - Inactive members (no purchase 180+ days)
  - Tier distribution
  - Average points balance by tier

Visualizations:
  - Enrollment trend line chart
  - Tier distribution pie chart
  - Active vs. inactive members
  - Member growth rate

Metrics:
  - Email completeness rate
  - Phone number completeness rate
  - Birthday completeness rate
  - Email bounce rate (last 30 days)
  - SMS delivery failure rate
  - Duplicate member rate
  - Data completeness score

Alerts:
  - Bounce rate > 5%: Investigate
  - Duplicate rate > 2%: Clean up
  - Completeness < 90%: Improve capture

RFM Segmentation:
  - Recency: Days since last purchase
  - Frequency: Transactions in last 12 months
  - Monetary: Total spending in last 12 months

Segments:
  - Champions: High R, F, M
  - Loyal Customers: High F, M
  - At Risk: High M, low R
  - Hibernating: Low R, F, M previously high

Actions by Segment:
  - Champions: Reward and retain
  - Loyal: Upsell and cross-sell
  - At Risk: Win back campaigns
  - Hibernating: Re-engagement or purge