The Problem (Why)

Stars and Honey (a premium CPG company specializing in collagen based protein bars) needed a highly scalable solution to process substantial fragmented and at times inaccurate historical data.

They routinely ran time consuming cohort retention on thousands of Shopify orders, using data pulled from Shopify Reports and other third-party data providers. While value in metric was high, confidence began to slip due to process:

• The process was time consuming, both generating the report and validating it’s results
• Data was not time zone normalized, leading to staff producing inconsistent results due to time zone differences
• It was an error prone process
• Lacked standardization, so report varied in form and were inconsistent

The Technical Challenge and Solution (How)

We needed an application that could:

1. Centralize fragmented analytic data from multiple third-party providers.
2. Export aggregated data in specific domain-specific form factors for their reports.
3. Accurately and deterministically calculate advanced metrics with over 10m individual parameters.
4. Provide traceability and auditability via “paper-trail” interface for validating calculation results.
5. Scale with the growing number of orders and exponential nature of calculating cohort retention over time.

I developed a scalable analytics app embedded directly in the Shopify Admin. This app enhanced decision-making and increased operational efficiency by processing more than 10k orders per month and calculated month-to-month cohort retention over an annual spread.

The application featured simple analytic aggregation and domain-defined export schema, pulling insights from Shopify Reports and Triple Whale, to provide further operational velocity.

To ensure seamless integration with day-to-day operations, React and Shopify’s Poloris design system was used to build the frontend. Node.js (Express) was used to build a light backend that connected with a PostgreSQL database and ensured high availability.

Cohort retention analysis dashboard

The Result

I developed a highly optimized caching and compression algorithm to store historical cohort retention results, allowing near instant calculations on 12k+ months of data and more than 10m parameters. This simple caching and compression algorithm increased calculation efficiency drastically (more than 10x), optimizing a process that originally took minutes down to a few seconds.

A routine business operation that took multiple man hours per week was automated down to just a few clicks in Shopify Admin. The application increased operational efficiency by up to 20x (based on time saved running numbers manually)