PostgreSQL
Customer Segment Consistency Audit
SQL · Data Quality · CRM Analytics · 237 lines · 7 stages
Back to Portfolio

Customer Segment Consistency Audit

A multi-stage SQL audit workflow that detects customers assigned to conflicting loyalty segments and automatically categorizes each conflict by the business rule that explains it — so analysts only review genuinely ambiguous cases.

PostgreSQL Data Quality CRM Analytics CTEs & Temp Tables Index Optimization

Audit Pipeline

1
Data Pool
Filter conflicted
accounts
→
2
Index
8 indexes for
performance
→
3
Lifecycle
Reactivated &
churn-recovery
→
4
Hard Rules
Policy-defined
segment rules
→
5
Nuances
Soft channel
exceptions
→
6
Account
Owner & code
consistency
→
7
Output
Categorized
results

Problem Being Solved

  • Each customer should map to exactly one loyalty segment (Platinum, Gold, Standard, Freemium)
  • Segment conflicts cause misfired campaigns, wrong discount tiers, and inaccurate churn reports
  • With tens of thousands of accounts, manual review is infeasible — an automated triage pipeline was needed

Key SQL Techniques

  • Chained CTEs — each stage excludes accounts already categorized upstream
  • UNNEST + ARRAY to declare rule tables inline without separate schema objects
  • IS DISTINCT FROM for null-safe comparisons on optional flag columns
  • Temp table indexes to prevent full-table scans across 7 join stages

Inputs

  • customers_active — master customer table
  • accounts_master — supplemental ID lookups
  • pending_migration — next migration queue

Impact

  • Auto-resolves 83% of conflicts — analysts only review the remaining 17%
  • Replaces multi-day manual review with a single script run
  • Fully reusable — swap in a new account list each cycle
customer_segment_audit.sql 237 lines 

      

    


    
    

      

        
Sample output — illustrates the two result tables. All identifiers are synthetic placeholders.

        
No real customer data is shown.

        
Results 2 — Summary by Conflict Reason

        

          
38
Lifecycle Rules

          
124
Tier Hard Rules

          
57
Tier Nuances

          
219
Independent Account

          
81
Needs Review

          
519
Total Audited

        

        
Results 1 — Customer Detail (sample rows)

        

          

            
EmailSegsSegmentsConflict ReasonNotes

            
              
alice.m@example.com2PLATINUM, GOLDLifecycle RulesREACTIVATED — transitional segment expected

              
enterprise.co@corp.com2PLATINUM, SILVER_PLUSTier Hard RulesENTERPRISE permits multiple segments by policy

              
market.biz@trade.com2STANDARD, PREMIUMTier Hard RulesMARKETPLACE permits multiple segments by policy

              
smb.owner@startup.io2GOLD, GROWTH_TIERTier NuancesSoft exception — SMB_ACCOUNTS channel nuance

              
family.acct@home.net2GOLD, STANDARDIndependent AccountDistinct account codes per segment

              
mystery.user@web.com2GOLD, FREEMIUM_PLUSNeeds ReviewNo matching rule — manual review required

            
          

        

        
Only Needs Review rows (81 of 519, ~16%) need analyst attention. The other 84% are auto-categorized.