CUSTOMER EXPORT CLEANING - SUMMARY REPORT
======================================================================

INPUT
  Source file: customer_export_messy.csv
  Rows: 100,000
  Columns: 5 (customer_id, customer_name, signup_date, lifetime_value, email)

OUTPUT
  Clean dataset: customer_export_clean.csv
  Rows: 97,000
  Issues log: customer_export_issues.csv
  Rows: 52,960

WHAT WAS FIXED
----------------------------------------------------------------------

1. DUPLICATES REMOVED: 3,000 rows
   Rule: same customer_id = duplicate. All 3,000 duplicate groups were
   exact duplicates (same name, date, value, email). Kept first occurrence.

2. DATES PARSED: 100,000 / 100,000 (100%)
   Four input formats detected and converted to ISO 8601 (YYYY-MM-DD):
     ISO (2025-09-15):           24,953 rows - passed through
     Slash (1/11/2025):          24,869 rows - parsed as MDY (US)
     Dash (11-10-2026):          24,985 rows - parsed as MDY (US)
     Dot (14.8.2026):            25,193 rows - parsed as DMY (European)
   Date range after cleaning: 2025-01-01 to 2026-12-28

3. AMOUNTS PARSED: 100,000 / 100,000 (100%)
   Four input formats detected and converted to signed float:
     Plain (972.51):             25,009 rows
     Parenthesized ((3971.36)):  24,954 rows - treated as negative
     Dollar prefix ($1,451.65):  25,085 rows
     USD suffix (681.03 USD):    24,952 rows
   Result: 24,194 negative values (24.94% of clean dataset)
   LTV range: -3999.94 to 3999.97

4. NAMES NORMALIZED: 100,000 / 100,000 (100%)
   Transformations applied:
     - Leading/trailing whitespace trimmed: 5,128 rows
     - UPPERCASE -> Title Case: 28,430 rows
     - 'Last, First' -> 'First Last': 20,092 rows
     - Multiple spaces collapsed
   Result: all 97,000 clean names are Title Case 'First Last' format

5. EMAILS NORMALIZED: 50,040 / 100,000 (50.04%)
   Full emails (has @ and TLD): 50,040 rows - lowercased, kept as-is
   No-TLD emails (user@example): 24,998 rows - set to NULL, flagged
   Bare usernames (no @):       24,962 rows - set to NULL, flagged

WHAT COULD NOT BE FIXED (FLAGGED FOR MANUAL REVIEW)
----------------------------------------------------------------------

1. EMAILS WITHOUT VALID DOMAIN: 49,960 rows
   - 24,998 rows have '@' but no TLD (e.g., 'sofia48@example')
   - 24,962 rows are bare usernames with no '@' (e.g., 'david2')
   These were set to NULL in the clean dataset. The client needs to
   supply the correct domain or confirm whether these should be
   treated as valid internal identifiers.

2. DUPLICATE ROWS: 3,000 rows removed
   Each removed row is logged in the issues file with its customer_id
   and the detail 'Row removed as duplicate'.

ASSUMPTIONS MADE
----------------------------------------------------------------------

1. SLASH dates (1/11/2025) interpreted as MDY (US). 14,312 of 24,869
   slash dates had second part > 12, confirming MDY. The remaining
   10,557 ambiguous dates were also parsed as MDY per the default.

2. DASH dates (11-10-2026) interpreted as MDY (US). The first part
   never exceeded 12 across 24,985 rows, confirming MDY.

3. DOT dates (14.8.2026) interpreted as DMY (European). The first
   part went up to 31, confirming DMY.

4. Parenthesized amounts ((3971.36)) treated as negative per the
   stated accounting convention. This cannot be arithmetically
   verified since lifetime_value is a snapshot, not a running total.

5. Duplicate rule: same customer_id = duplicate. Rows sharing a
   normalized name or email but with different customer_ids were
   NOT merged. The client should decide whether those represent
   the same person under different IDs.

RECONCILIATION
----------------------------------------------------------------------

  Input rows:                    100,000
  Duplicate rows removed:          3,000
  Clean rows:                     97,000
  Check: 100,000 - 3,000 = 97,000 OK

  Issues logged:                 52,960
    email_no_tld:                24,998
    email_bare_username:         24,962
    duplicate_removed:            3,000
  Check: 24,998 + 24,962 + 3,000 = 52,960 OK

FILE LIST
----------------------------------------------------------------------

  customer_export_clean.csv   - 97,000 rows, 9 columns (clean + raw)
  customer_export_issues.csv  - 52,960 rows, 5 columns (issue log)