Overview
A Series-B 3PL company providing technology solutions to 100+ warehouses across the US saw a significant surge in product adoption during the pandemic due to the unprecedented growth of e-commerce. As consumers shifted to digital channels, warehouses and fulfillment service providers struggled to efficiently coordinate the pick, pack, and ship processes in getting orders to customers on time.
The company relied on a decentralized network of warehouses, which struggled under the pressure of growing order volumes across varied providers to even meet 80% order fulfillment rates. Amazon set the industry standard for 95% on time shipment rates from order to customer door, so the benchmark was clear. But what about the solution?
Pungo discovered that this massive coordination effort required world class data movement.
- Application data was locked in an onprem PostgreSQL database, with limited read-only SQL query privileges handed to engineers and one technical PM that would directly impact application performance running one-off queries
- Marketing and CS data was siloed in vendor systems and managed using manual Google Sheets reporting. Salesforce, Zendesk, and application data don’t talk to each other in a seamless way, revealing data gaps across tools owned by different stakeholders
- There was no established reporting routine to measure service reliability across the 100+ warehouse network nationwide. No standard on-time shipment rate performance metric made it impossible to hold fulfillment partners accountable
With these in mind, Pungo approached this project by designing the core data services revolving around BigQuery and Looker implementation, then continuing to unlock new optimization opportunities by partnering with Ops and Product leaders to negotiate better rates with partners and drive more product adoption.
Action
We broke this project into 3 phases:
1. Data Migration into BigQuery
We started by assessing the existing data infrastructure and identifying key data sources and their integration points.
- Replicated application data from the on-prem PostgreSQL database to BigQuery, ensuring minimal disruption to ongoing operations
- Established ETL processes to regularly update BigQuery with data from various sources, including Salesforce, Zendesk, and other marketing tools
- Created a unified data schema in BigQuery to consolidate information and provide a single source of truth for the organization
2. Looker Implementation and Report Building
Once data was centralized in BigQuery, we focused on making it accessible and actionable through Looker.
- Built the core foundation of hundreds of LookML data models to separate fact and dimension tables that accurately represented business processes
- Developed a suite of reports to monitor core operational metrics such as on-time shipment rate and order fulfillment accuracy
- Facilitated “Operations War Room” weekly with Execs and Ops stakeholders to detect trends in warehouse performance and strategize on actions to get to 95% on-time shipment rate
3. Fine-Tuning and Actionability
After the tooling migration, efforts were focused on growth benchmarking and training internal teams to adopt new data tools and building additional enhancements to drive operational benchmarks.
- Developed a unified rate sheet data model for every service offering, integrating data from various parcel carriers and other vendors
- Implemented optimization algorithm to identify the highest margin selection for different fulfillment scenarios without compromising on SLAs with customers
- Built a roadmap for continuous improvement, outlining future enhancements and new scope to keep pace with the company’s evolving needs
Result
We enabled the 3PL client to exceed industry standards for on-time shipment and order accuracy rates, enhancing performance visibility across the warehouse network to better meet customer expectations.
- Achieving higher operational performance unlocked 5X revenue growth over
- Conducted training sessions for 20+ non-technical leaders in Looker and SQL
- Fostered a culture of data-informed decision-making across the company through weekly, monthly, and quarterly reviews of trends and insights
