> ## Documentation Index
> Fetch the complete documentation index at: https://ritika-fa61c43f.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Standardization Strategies for CRM Column Cleaning

> How to use all four standardization strategies — TO_DATETIME_ISO, LOWERCASE_STRIP, EXTRACT_NUMBERS, and PHONE_E164 — to clean corrupted CRM columns.

Standardization is the first concrete transformation step your pipeline should execute after profiling a source. The environment's `STANDARDIZE_COLUMN` action accepts a `source`, a `column`, and a `standardization_strategy`, then applies a deterministic in-place mutation to every value in that column within the in-memory DataFrame. All four strategies are idempotent — running the same strategy twice on an already-clean column is safe. Each strategy was designed to undo a specific corruption pattern from the chaos engine, so choosing the right one for the right column is the difference between a clean merge and a silently broken join key.

***

## The STANDARDIZE\_COLUMN Action

To apply a standardization strategy, send an action payload with `action_type` set to `STANDARDIZE_COLUMN` along with the target `source`, `column`, and `standardization_strategy`:

```json theme={null}
{
  "action_type": "STANDARDIZE_COLUMN",
  "source": "<source_name>",
  "column": "<column_name>",
  "standardization_strategy": "<STRATEGY_NAME>"
}
```

A successful call returns a reward between `0.01` and `0.05` (proportional to how many dirty values were corrected) and sets `last_action_feedback` to:

```
"Standardized <source>.<column> using <strategy>"
```

> [!NOTE]
>
> Standardization mutates the DataFrame in place within the environment's memory. You do not need to pass the data back — subsequent `VIEW_SOURCE` or `EXECUTE_SQL` calls will see the cleaned values immediately.

***

## Strategy Reference

> [tab] TO\_DATETIME\_ISO
>
> ### TO\_DATETIME\_ISO
>
> `TO_DATETIME_ISO` normalizes date strings into ISO 8601 format (`YYYY-MM-DD`). Under the hood the environment calls `pd.to_datetime(col, errors='coerce')` and then formats the result as `YYYY-MM-DDT00:00:00`, using an empty string `""` for any value that could not be parsed.
>
> **Designed for:** `signup_date`
>
> #### What it handles
>
> | Input Value | Output Value | Reason |
> | - | - | - |
> | `"2023/07/14"` | `"2023-07-14T00:00:00"` | Slash separator converted |
> | `"2023-07-14"` | `"2023-07-14T00:00:00"` | Already ISO, reformatted with time component |
> | `"??/??/????"` | `""` | Completely unparseable → coerced to NaT → `""` |
> | `"2023-13-12"` | `""` | Invalid month 13 → coerced to NaT → `""` |
> | `None` | `""` | Null → coerced to NaT → `""` |
>
> #### JSON action payload
>
> ```json theme={null}
> {
>   "action_type": "STANDARDIZE_COLUMN",
>   "source": "web_forms",
>   "column": "signup_date",
>   "standardization_strategy": "TO_DATETIME_ISO"
> }
> ```
>
> #### When to use it
>
> Apply `TO_DATETIME_ISO` to `signup_date` in every source that passed through `correlate_truth()`. For Task 1 and Task 2 this means `web_forms` and `legacy_db`. Do not apply it to `salesforce` or `web_leads` in Task 3 — those sources do not have a `signup_date` column.
>
> > [!TIP]
> >
> > After running `TO_DATETIME_ISO`, use `HANDLE_MISSING` with `FILL_VALUE` (value `""`) or `DROP_ROW` on `signup_date` to handle the rows that produced empty strings. Whether to drop or fill depends on whether `signup_date` is required in your final output schema.

> [tab] LOWERCASE\_STRIP
>
> ### LOWERCASE\_STRIP
>
> `LOWERCASE_STRIP` lowercases every character and strips leading and trailing whitespace from string values in the target column. It is implemented as `.astype(str).str.lower().str.strip()`.
>
> **Designed for:** `email`, `name`, and any string column with casing or whitespace corruption.
>
> #### What it handles
>
> | Input Value | Output Value | Reason |
> | - | - | - |
> | `"JANE.DOE@GMAIL.COM"` | `"jane.doe@gmail.com"` | Uppercase lowercased |
> | `"  Jane Doe  "` | `"jane doe"` | Leading/trailing whitespace stripped, lowercased |
> | `"  JOHN SMITH  "` | `"john smith"` | Both mutations reversed |
> | `"jane.doe@gmail.com"` | `"jane.doe@gmail.com"` | Already clean — no change |
>
> #### JSON action payload
>
> ```json theme={null}
> {
>   "action_type": "STANDARDIZE_COLUMN",
>   "source": "web_forms",
>   "column": "email",
>   "standardization_strategy": "LOWERCASE_STRIP"
> }
> ```
>
> #### When to use it
>
> * **Always** apply to `email` before any join or deduplication. The chaos engine uppercases \~50 % of emails in `web_forms` and `legacy_db`; `legacy_db` in Task 3 uppercases every `contact_email` value. A mismatch in email casing will silently drop matching rows from your join.
>
> * Apply to `name` before `FUZZY_NAME_PHONE` deduplication — the fuzzy proxy in the environment normalizes names using lowercase/strip before comparing.
>
> > [!WARNING]
> >
> > `LOWERCASE_STRIP` calls `.astype(str)` internally, which converts `None`/`NaN` to the string literal `"none"` or `"nan"`. If you need to preserve `None` values, run `HANDLE_MISSING` **after** standardization and use `FILL_VALUE` with `""` to clean up these artifacts, or `DROP_ROW` to remove them.

> [tab] EXTRACT\_NUMBERS
>
> ### EXTRACT\_NUMBERS
>
> `EXTRACT_NUMBERS` strips all non-digit characters from phone column values, additionally handles extension suffixes, and re-attaches the `+` E.164 prefix. The environment's implementation splits on `ext` or `x` first, applies `re.sub(r'\D+', '', s)` to the remaining string, then prepends `+` to produce a valid E.164 number. Non-digit strings (such as `"Invalid Number"`) become empty strings `""`.
>
> **Designed for:** `phone` — as the **first step** in the two-step phone cleaning sequence.
>
> #### What it handles
>
> | Input Value | Output Value | Reason |
> | - | - | - |
> | `"15551234567 ext. 42"` | `"+15551234567"` | Extension stripped, digits extracted, `+` prepended |
> | `"(155) 512-34567"` | `"+15551234567"` | Parentheses and hyphens removed |
> | `"Invalid Number"` | `""` | No digits extracted → empty string |
> | `"+15551234567"` | `"+15551234567"` | Already E.164 — effectively a no-op |
> | `None` | `""` | Null → empty string |
>
> > [!NOTE]
> >
> > The environment's `EXTRACT_NUMBERS` implementation goes beyond simple digit extraction — it also calls the E.164 formatting logic internally. In practice, a single `EXTRACT_NUMBERS` call is sufficient to clean most phone corruption patterns. Follow up with `PHONE_E164` as a safety pass to ensure the `+` prefix is always present.
>
> #### JSON action payload
>
> ```json theme={null}
> {
>   "action_type": "STANDARDIZE_COLUMN",
>   "source": "web_forms",
>   "column": "phone",
>   "standardization_strategy": "EXTRACT_NUMBERS"
> }
> ```
>
> #### When to use it
>
> Use `EXTRACT_NUMBERS` as step one of the phone cleaning sequence. It handles the extension-suffix pattern (`"ext. 42"`) and the US parenthetical format (`"(xxx) xxx-xxxx"`) that `PHONE_E164` alone cannot parse. Always follow `EXTRACT_NUMBERS` with `PHONE_E164` to enforce the canonical `+{digits}` shape.

> [tab] PHONE\_E164
>
> ### PHONE\_E164
>
> `PHONE_E164` normalizes phone strings to E.164 format by prepending a `+` character to any digit-only string that does not already start with one. Non-digit strings (e.g., `"Invalid Number"`) become empty strings `""`.
>
> **Designed for:** `phone` — as the **second step** in the two-step phone cleaning sequence, after `EXTRACT_NUMBERS`.
>
> #### What it handles
>
> | Input Value | Output Value | Reason |
> | - | - | - |
> | `"15551234567"` | `"+15551234567"` | `+` prepended |
> | `"+15551234567"` | `"+15551234567"` | Already E.164 — no change |
> | `"Invalid Number"` | `""` | Not a digit string → empty |
> | `""` | `""` | Already empty |
> | `None` | `""` | Null → empty |
>
> #### JSON action payload
>
> ```json theme={null}
> {
>   "action_type": "STANDARDIZE_COLUMN",
>   "source": "web_forms",
>   "column": "phone",
>   "standardization_strategy": "PHONE_E164"
> }
> ```
>
> #### When to use it
>
> Apply `PHONE_E164` as the final step after `EXTRACT_NUMBERS`. It is a lightweight normalization pass — it does not remove extension suffixes or parse complex formats, so it must be preceded by `EXTRACT_NUMBERS` for full coverage. For `legacy_db.home_phone` in Task 3, the values are already E.164 from the generator, so `PHONE_E164` is still safe but may produce minimal changes.
>
> > [!TIP]
> >
> > After `PHONE_E164`, apply `HANDLE_MISSING` with `DROP_ROW` or `FILL_VALUE` on `phone` to remove any empty strings that were originally `"Invalid Number"` or `None`. This prevents empty phone strings from interfering with `FUZZY_NAME_PHONE` deduplication.

***

## Column-to-Strategy Mapping

| Column | Recommended Strategy | Notes |
| - | - | - |
| `signup_date` | `TO_DATETIME_ISO` | Apply to all sources with this column |
| `email` | `LOWERCASE_STRIP` | Apply before every join and deduplication step |
| `name` | `LOWERCASE_STRIP` | Apply before `FUZZY_NAME_PHONE` |
| `contact_email` (`legacy_db`, Task 3) | `LOWERCASE_STRIP` | Uppercase in all Task 3 legacy rows |
| `phone` (step 1) | `EXTRACT_NUMBERS` | Strips extensions and formatting characters |
| `phone` (step 2) | `PHONE_E164` | Adds `+` prefix to digit-only strings |

***

## Recommended Phone Cleaning Sequence

Cleaning phone numbers reliably requires three sequential steps. Run them in this order:

**1. Strip extensions and formatting with EXTRACT\_NUMBERS**

```json theme={null}
{
  "action_type": "STANDARDIZE_COLUMN",
  "source": "web_forms",
  "column": "phone",
  "standardization_strategy": "EXTRACT_NUMBERS"
}
```

This removes `ext. NNN` suffixes, strips parentheses and hyphens from US format numbers, and converts `"Invalid Number"` strings to `""`.

**2. Enforce E.164 format with PHONE\_E164**

```json theme={null}
{
  "action_type": "STANDARDIZE_COLUMN",
  "source": "web_forms",
  "column": "phone",
  "standardization_strategy": "PHONE_E164"
}
```

This prepends `+` to any digit-only string that is missing it, ensuring every valid phone number follows the `+{country_code}{number}` shape.

**3. Handle remaining empty and null values**

After the two standardization steps, values that were `"Invalid Number"`, `None`, or unparseable will be empty strings `""`. Decide whether to drop those rows or fill them:

```json theme={null}
{
  "action_type": "HANDLE_MISSING",
  "source": "web_forms",
  "column": "phone",
  "missing_strategy": "FILL_VALUE",
  "fallback_value": ""
}
```

Or to drop rows with no phone:

```json theme={null}
{
  "action_type": "HANDLE_MISSING",
  "source": "web_forms",
  "column": "phone",
  "missing_strategy": "DROP_ROW"
}
```

***

## Reward Feedback

The environment calculates a dense heuristic reward for every `STANDARDIZE_COLUMN` call based on how many values were in a dirty state before the transformation. The formula is:

```
improvement_ratio = dirty_rows / total_rows
reward = min(0.05, 0.01 + (improvement_ratio × 0.1))
```

This means a source with 80 % dirty values in a column gives a reward of approximately `0.01 + (0.80 × 0.1) = 0.09`, capped at `0.05`. A source that is already clean gives the minimum reward of `0.01`. Use this signal as a heuristic indicator of how much corruption was present — though it does not reflect whether your cleaning strategy was semantically correct.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.