BK
All projects
PythonData EngineeringCRM Data OperationsDelivered

~22K pharmacy records in scope

CRM Data Collection, Cleansing & Enrichment Pipeline

Python-based data workflows for scraping, cleansing, matching and preparing CRM updates across a dataset of approximately 22,000 pharmacies.

Project delivered during the experience at Les Préparations de l'Apothicaire.

01 · Overview

Reliable CRM updates start before the import.

The CRM dataset needed targeted cleansing and enrichment before it could support reliable operational updates. Information was distributed across CRM exports, reference files and public web sources. Python workflows normalized and reconciled these inputs against the correct records. The resulting Excel and CSV datasets were staged for focused review and CRM import.

The Challenge

Reconcile first. Enrich second.

The work went beyond collecting new information. Names, addresses, phone numbers and emails arrived in inconsistent formats across multiple sources, and each enrichment had to be reconciled with the correct CRM record without weakening the reference dataset. The engineering priority was therefore controlled data quality: normalize first, match deterministically, preserve existing identifiers and prepare reviewable update files.

Automation level

Python-based data processing workflows with staged files and manual orchestration.

Files and scripts were staged and reviewed between processing steps.

Dataset Scale

22,043

Pharmacy records observed in a major working dataset

19,959

Rows in a CRM-ready, import-oriented workbook

Multiple intermediate datasets and versioned workbooks were used throughout the workflow. These figures represent different processing stages.

Data Sources

  • CRM exports
  • Internal reference files
  • Public specialized directories
  • Public web sources
  • Enriched working datasets

02 · Data Flow

A staged path from source files to a reviewable update dataset.

Web collection was used when needed; other workflows started directly from CRM and reference exports.

  1. 01

    CRM / Reference Exports

  2. 02

    Public Web Sources

  3. 03

    Python Collection & Scraping

  4. 04

    Cleaning & Standardization

  5. 05

    Matching & Reconciliation

  6. 06

    Targeted Enrichment

  7. 07

    CRM Update Dataset

  8. 08

    Review / Import

Python Processing

PY-01

Collection

HTTP GET requests, directory traversal and HTML parsing with requests and BeautifulSoup.

PY-02

Transformation

pandas, regular expressions and Unicode normalization applied to staged CSV and Excel files.

PY-03

Export

Versioned and timestamped CSV / Excel datasets prepared for control and downstream use.

  • Python
  • pandas
  • requests
  • BeautifulSoup
  • Regex
  • unicodedata
  • CSV / Excel
  • JSON / XML

Web Data Collection

  • HTTP GET requests
  • HTML parsing
  • Public directory-link traversal
  • Public name, address and phone-field extraction
  • tel: link handling
  • JavaScript / Leaflet popup decoding
  • Request delays
  • Timestamped CSV outputs

03 · Data Cleansing & Standardization

Normalize the representation before comparing the identity.

Raw NameNormalized Name

Accents, casing, whitespace and non-alphanumeric noise normalized.

Raw Contact FieldComparable Contact Key

Postal and phone values converted into consistent comparison formats.

  • Accents removed
  • Casing normalized
  • Whitespace cleaned
  • Non-alphanumeric noise removed
  • Postal codes standardized
  • Phone numbers normalized
  • Email syntax checked
  • Null values handled
  • Temporary technical columns removed

04 · Matching & Reconciliation

Deterministic rules applied in a controlled cascade.

The reviewed scripts compare normalized attributes through exact lookup rules. Some variants also use normalized phone alone.

  1. Rule 1

    Normalized name + postal code

    ↓
  2. Rule 2

    Normalized street + postal code

    ↓
  3. Rule 3

    Normalized phone + postal code

    ↓
  4. Fallback

    Normalized name + postal code

No fuzzy matching was identified in the reviewed scripts. Unmatched records therefore remain outside the deterministic enrichment path.

Composite Key Strategy

Multiple normalized attributes are combined to reduce ambiguity before a lookup is attempted.

normalized_name + postal_codenormalized_phone + postal_code

Data Reconciliation

CRM Record
Official / Reference Dataset
Public Web Data
Matching Rules
Consolidated Record

05 · CRM Update Preparation

Prepare targeted changes, then keep a human at the write boundary.

  1. CRM-01

    CRM Export

  2. CRM-02

    Cleaning

  3. CRM-03

    ID Recovery

  4. CRM-04

    Targeted Enrichment

  5. CRM-05

    Technical Column Removal

  6. CRM-06

    Import-ready Excel / CSV

  7. CRM-07

    Manual Review / CRM Import

No evidence of direct Zoho CRM API write-back was found in the reviewed files.

Data Quality Controls

  • Deterministic matching rules
  • Normalization before comparison
  • Reference / truth datasets
  • Filtered subsets
  • Duplicate handling
  • Syntax validation for selected fields
  • Intermediate versioning
  • Script-level statistics
  • Manual review before import

These controls supported file-level quality and review; they do not represent a complete data-observability platform.

Outputs Produced

Cleaned datasets

Enriched datasets

Matching files

CRM ID recovery files

Email update CSVs

Import-ready workbooks

Timestamped scraping exports

Reusable Python scripts

06 · My Contribution

Applied Python and data-quality reasoning to CRM operations.

  • Built Python scripts for structured public-data collection and targeted enrichment.
  • Designed deterministic matching rules using normalized composite keys.
  • Cleaned and standardized CRM-oriented datasets across staged files.
  • Reconciled information across CRM exports and multiple reference datasets.
  • Prepared targeted Excel and CSV datasets for CRM updates and import.
  • Supported CRM data quality and operational maintenance as CRM referent.

07 · Current Limitations

The evidence supports useful automation, not an orchestrated platform.

  • Manual orchestration between scripts and files
  • Hard-coded local paths in some scripts
  • No automated test suite
  • No persistent execution logging
  • No confirmed direct Zoho CRM API write-back
  • Deterministic matching only
  • Some historical scripts or inputs are missing
  • Email candidates without deliverability validation
  • External HTML structures can change

Engineering Takeaways

  1. 01Collection creates value only when the resulting data is trustworthy.
  2. 02Deterministic matching depends on normalization before comparison.
  3. 03Staged datasets make risky CRM updates reviewable before import.
  4. 04Business-critical data automation should preserve human validation at the write boundary.