Project overview
The challenge
- Real estate agents received leads from many sources in inconsistent formats, requiring hours of manual cleanup.
- Purchased lead lists contained many invalid phone numbers, wasting SMS campaign budgets.
Engineered a specialized lead generation and management platform for the Dubai real estate market, processing thousands of leads from various sources with automated data cleaning and enrichment workflows.
Built ETL pipelines using Pandas to validate, deduplicate, and normalize lead data from CSV imports. Implemented international phone number validation using the phonenumbers library, ensuring SMS campaigns only target valid UAE mobile numbers (+971).
Integrated Twilio API for automated SMS outreach campaigns, enabling real estate agents to engage with qualified leads immediately. Developed an audit schema tracking all data transformations, ensuring GDPR compliance and data lineage for regulatory requirements.
Technical architecture
CSV Ingestion: Flask web interface accepts CSV uploads. Pandas reads and validates file structure before processing.
Data Validation Pipeline: phonenumbers library validates international format, filters for UAE mobile numbers, rejects non-UAE numbers.
Deduplication: SQL queries check existing leads by phone and email, preventing duplicate CRM entries.
Twilio Integration: Automated SMS campaigns for qualified leads with customizable templates.
Audit Trail: PostgreSQL schema logs every transformation for compliance and debugging.
Key challenges & solutions
International Phone Number Validation
Used Google's phonenumbers library to parse international formats. Implemented regex for UAE mobile patterns (+971 5X).
Pandas ETL Performance for Large Files
Optimized Pandas processing for large CSVs using chunked reading (chunksize=1000).
Data Lineage and GDPR Compliance
Implemented comprehensive audit schema tracking source files, transformations, and delivery status, ensuring GDPR compliance.
Impact & results
Bulk CSV lead ingestion and cleanup with a Pandas ETL
Automated Twilio SMS outreach sequences on validated numbers
Contacts deduplicated in PostgreSQL across lead sources
Key features
- CSV batch import and processing
- International phone number validation
- Automatic deduplication logic
- Twilio SMS campaign integration
- PostgreSQL relational database
- Flask web interface for data management
- Audit trail for all transformations
- Data validation with custom validators
- Schema migration scripts
- Error handling and rollback mechanisms