Automated the data collection, normalization, and reporting from multiple Email Service Provider (ESP) platforms. Migrate and build out data pipeline from vendor services and into client’s own AWS resources.
Automated Data Pipeline and Reporting for Email Marketing Agency
One of the largest private label email and SMS marketing agencies in the US.
What We Did
- Data Science Consulting
- Lead classification system
- Remarketing classification and regression with custom model
- Email marketing integration with cloud relational database
- Report Automation
- Data Validation & Reconciliation
- API Development
- Technical Consulting
- Code Review for Python development team
- Amazon Web Services – EC2
- Amazon Web Services – RDS (Aurora MySQL)
- Jupyter Notebook
- Pandas and NumPy
- Oracle NetSuite
- Apache Airflow
- SOAP / XML-RPC / REST API protocols
- SFTP protocol
- Data engineering plan to build data pipeline from scratch. Set up interactive notebooks for analysts to import data, manipulate and export in multiple formats.
- Machine Learning models for customer segmentation and to optimize new mailing lists for open and clickthrough stats.
- Built out schema for all email campaign data on an AWS Relational Database Service (RDS) MySQL database with Aurora, with backups and replication to ensure no data loss, ever.
- Custom API clients for disparate ESP providers; some of which had no API. Ran on a cron schedule to update regularly, with commands exposed to data consumers in the company to run or rerun data fetching and reporting jobs without touching code.
- Agency team still relied heavily on spreadsheets. Automated building of spreadsheets, pivoting, formatting, and sending. Many dozens of labor hours saved each week.
- Company outsourced a team of more than 40 contractors to aggregate data from Email Service Provider platforms: download data, transform, and email spreadsheets to be uploaded by a data analyst to Tableau Server. Our system automated this whole process so no humans handled the data from source to database. Huge cost savings, and no more drudgery!
- Previously took 1 to 2 days for the most up to data reporting, which was displeasing to brands the agency had as clients. Automation from new data point at the email vendor to end user at the agency got that time down to less than 20 seconds.
- “All data, everywhere, always” – this was our directive when we were brought on. Happy to say, we succeeded in delivering.