IPG: Automation of Data Intake and Cleansing from Data Suppliers
I led a mission-critical initiative to automate the quality assurance (QA) and ingestion of performance data files submitted by 12 third-party data service providers. Previously, the IPG analytics team performed manual QA on every incoming file, which introduced delays, increased the risk of human error, and created bottlenecks in communicating and correcting data issues with suppliers. My objective was to eliminate manual QA processes, reduce data errors, and create a sustainable feedback loop for vendor performance.
My Role
Project Manager
Team
Data and Analytics for Client Insights and Solutions
Timeline
June 2024 - October 2024
My Responsibilities:
I scoped project feasibility using a skill-mapping survey, collaborated with the Data & Tech leadership to allocate technically skilled resources, created a master calendar of file submissions, led the design of automated Python scripts for QA, developed process diagrams, and facilitated change management protocols between our internal teams, clients, and external vendors.
Tools and Methodologies Used:
We began with a comprehensive survey to gauge each team member’s capabilities in SQL, Python, and platform familiarity (e.g., Tableau, SA360).
-
Based on results, we assigned four technically proficient team members to the automation workstream.
-
Using Python and secure file transfer protocols (SFTP), the team created scripts that automatically scanned incoming files for schema consistency, missing data, and formatting issues. If issues were found, an automated email was generated to the supplier with precise error descriptions (row number, column name, and reason). A second QA step was ran in Data Manager before data was uploaded to Snowflake.
To manage internal alignment and client sign-offs, I produced detailed process diagrams and used Kaizen principles to iteratively improve workflows based on feedback. From the feedback, we created a quarterly supplier performance scorecard to assess compliance, drive accountability, and incentivize vendor improvement.
Challenges & Solutions
-
Gaining stakeholder confidence required rigorous documentation and transparency. I translated technical processes into visual artifacts and hosted walkthroughs with both client stakeholders and internal teams to secure approvals.
-
Another challenge was maintaining workflow continuity across U.S.-based and offshore teams. I leveraged Asana’s portfolio system to assign and balance workloads in a transparent, collaborative way. Team members volunteered for workstreams based on interest and availability, which boosted ownership and cross-training.
Key Outcomes & Metrics:
We achieved a 99% QA accuracy rate by automating human-error-prone steps. Over 80% of vendor files passed QA on the first submission, and underperforming vendors improved after targeted training. Automation reduced turnaround from days to hours, and the scorecard introduced a new standard of vendor transparency.
Lessons Learned:
This project reinforced the importance of aligning technical capabilities with workstreams. The skill-mapping tool helped us build a team that was both technically strong and personally invested. It also underscored the value of process visualization when navigating complex changes involving multiple stakeholders.
Project Artifacts
