top of page

Background

TelcoPrime, a telecommunications operator, was in the middle of a Customer 360 initiative when it hit a wall: years of customer data had piled up without any consistent standards. Addresses were written six different ways, state names appeared as full names, abbreviations, and inconsistent capitalizations, and phone numbers were cluttered with symbols and, in some cases, corrupted entirely. Marketing was losing 8-15% of campaign spend on mail that never reached customers, churn prediction models were underperforming because they relied on unreliable location data, and engineering teams were stuck in a cycle of manual fixes instead of building anything new.

​

This case study covers the work done to fix that foundation using Microsoft Fabric. The focus was a low-code Dataflow Gen2 pipeline that cleaned and standardized customer addresses, states, and phone numbers by matching them against reference lookup tables, then flagged each record as trustworthy or not before loading it into a clean, structured table ready for the business to use. Beyond the technical fix, the project also mapped out a broader framework for how the company should think about data quality going forward — treating this cleanup as a one-time lift rather than a problem that keeps coming back.

Problem

  • ​​Customer addresses existed in six different formats, with the same street suffix written as "Street," "St," "Str.," or "Rd" within the same column, making standard matching techniques fail.

  • State values were wildly inconsistent, with California alone appearing as "CA," "ca," "Calif.," and "California" — undermining any location-based analysis or segmentation

  • Phone numbers were contaminated with parentheses, dashes, and periods, and a portion of entries were completely invalid or corrupted, requiring a way to tell salvageable data apart from unsalvageable data. 

  • Marketing campaigns were wasting an estimated 8-15% of spend on mail and outreach that never reached customers due to bad address data

  • There was no shared, agreed-upon standard for what "clean" customer data looked like, so different teams ended up applying their own inconsistent fixes.

Solution

Set up the Lakehouse in Microsoft Fabric with the raw customer data (customer_bronze.csv) alongside two reference files — address_suffix_lookup.csv and state_lookup.csv — which would be used to match and correct inconsistent addresses and state values

Loaded the address suffix lookup table into DataFlows Gen2 (Power Query Online), which maps every raw suffix variation (like "Street," "St.," "Str.," "Avenue," "Ave") to one standard, clean version (like "St" or "Ave")

Loaded the state lookup table into DataFlows Gen2 , which maps every raw state variation (like "CA," "ca," "Calif.," "California") to a single standardized two-letter code

Loading the data (Customer Bronze Table)

  • Connected to the Lakehouse navigated into the CS10 folder to pull in customer_bronze.csv, the raw, messy customer data into Dataflows Gen2 (Power Query Online window).

  • Promoted the first row to column headers and set proper data types for each column (CustomerID as a number, Address/State/Phone as text)​​

Standardizing the State column

  • Joined the customer data with the state_lookup table, matching each raw state value (like "ca" or "Calif.") against its standardized two-letter code​

  • Pulled in the clean "Standard_State" value, removed the old messy State column, and renamed the clean version back to "State" so the table structure stayed the same

Splitting the Address column

  • ​Split the Address field by the comma, separating the main street address from the unit/apartment number (e.g., "123 Main St" from "Apt 4B")

  • Split the street address again, this time by space, to isolate the last word — which is typically the suffix (like "Street," "St.," or "Ave")

Standardizing the Address suffix

  • ​Joined this isolated suffix against the address_suffix_lookup table to find its standardized version (e.g., "Street" → "St")

  • Added a rule: if no match was found in the lookup table, keep the original suffix as-is rather than losing the data

  • Removed the now-unneeded raw suffix column and combined the standardized suffix back together with the rest of the street name into one clean "Address_Street" field

  • Renamed the unit/apartment column to "Address_Unit"

Cleaning the Phone column

  • ​Duplicated the original Phone column into a new column called "Phone_Clean," so the raw data stayed untouched

  • Stripped out every character that wasn't a digit (removing dashes, dots, parentheses, letters, etc.) by following the 0 - 9 text only approach.

  • Checked that the remaining digits added up to exactly 10 — anything shorter or longer was marked as invalid (set to null)

  • Added a check for fake/repeated numbers (like "5555555555") and marked those as invalid too

  • Removed the original, uncleaned Phone column since it was no longer needed

Data Quality Flag Column and Final Structure

  • Added a new column, "Data_Quality_Flag," that automatically marks each row as 1 (trusted) if the phone number passed all cleaning checks, or 0 (flagged) if it didn't

  • Reordered all the columns into the final, agreed-upon format: CustomerID, Address_Street, Address_Unit, State, Phone_Clean, Data_Quality_Flag — matching the Customer_Silver schema.

Lakehouse

Loaded the final CS10_Customer_Bronze table into the Lakehouse with all customer records — showing standardized street addresses, unit numbers, and two-letter state codes, with phone numbers either cleaned to a valid number or marked NULL, and each row correctly flagged as 1 (trusted) or 0 (needs review) based on data quality.

Outcome

  • ​Cleaner marketing outreach: Standardized addresses mean campaigns reach real customers instead of bouncing back, directly cutting into the 8-15% of marketing spend previously wasted on undeliverable mail

  • Faster, safer decision-making: A single Data_Quality_Flag lets any team instantly separate trustworthy records from ones that need review, without manual double-checking

  • Reduced manual rework: By fixing address, state, and phone inconsistencies at the source through an automated, repeatable pipeline, engineering teams spend far less time on one-off data cleanup

  • A foundation built to scale: The Dataflow Gen2 pipeline is published and scheduled to run daily, meaning the customer data stays clean and trustworthy every day, not just as a one-time fix

bottom of page