Specialized Bicycle Components · BikeStation · Costa Rica
From reports two to three months late to near real time.
BikeStation's leaders were deciding from data that was two to three months old. The BI team was already working to fix it for its own reasons, so we joined forces, and I was the link between the two teams and leadership. Reporting went to near real time across 15,000+ customers and 10,000+ SKUs.
- Problem
- Business reports ran two to three months behind, because products were miscategorized, customer records had gaps and the ERP could only export CSV files.
- My role
- Head of Product & Growth. I led the communication between my team, the BI team and leadership; the teams built the pipeline.
- What I did
- Joined forces with the BI team to define the tech stack and specifications for integrating Shopify, the ERP and Power BI, explained to leadership and management how it would help, won the budget for the team and its tools and cleared roadblocks.
- Outcome
- Near real-time reporting, analysis time cut by more than half, and leadership's first accurate view of product, inventory and sales.
Why the reports ran late
BikeStation was Costa Rica's largest bicycle retailer by store count, and its business intelligence ran on bad inputs. Products were not categorized correctly, and customer records were missing many fields. The ERP had no API, only CSV export, so every reporting cycle started with weeks of cleaning. By the time a report reached leadership, it described the business two to three months earlier: an August report built on May data.
One problem, two teams
I was on the ecommerce side, and my blocker was the miscategorized catalog. The business intelligence team was a separate team that was already pursuing the same fix for its own purposes: the same miscategorization was why their reports ran three months behind. We joined forces, and my obstacle became a bigger, shared, higher-impact effort that both teams carried.
From there my job was leadership and communication between my team and the BI team. Together we determined the tech stack and the specifications the work needed, and I explained to leadership and management how it would help. I ran the meetings that tied the project to the company's short- and medium-term goals while keeping it able to scale, I won the investment in the team and the subscriptions it needed, and I cleared roadblocks as they came up.
What changed
Clean the catalog
- Products re-categorized by hand
- Unstructured names and incomplete descriptions cleaned up
- Data gaps filled by scraping supplier sites
Automate the extract
- A daily CSV export from the ERP, automated with RPA
- Python scripts loading it into a cloud database
Report from one source
- Shopify, the ERP and Power BI working from the same data
- Daily inventory snapshots of 10,000+ SKUs, running to millions of rows
The teams built the pipeline with RPA and Python, and the implementers had a lot of freedom in how; I stayed involved as it took shape.
Using the data for product decisions
Once the data could be trusted, I ran a price-elasticity analysis on the top-selling products, projected against our sales and margin targets, to inform product decisions such as assortment, catalog and pricing. The BI team stopped cleaning files and started doing analysis, and leadership saw product, inventory and sales accurately for the first time.
Results
Near real time
reporting, down from two to three months behind
Across 15,000+ customers, 10,000+ SKUs and daily inventory snapshots in the millions of rows
Under half
the analysis time it took before the overhaul
The BI team stopped cleaning CSV files and started doing analysis