ECommerce Case Study3
Python Snowflake dbt Power BI Amazon SP-API · Walmart Seller API 3PL Integration Layer · Procurement
The Challenge
The CMO had a number that had started to concern her: 68 percent of first-time buyers did not make a second purchase within 90 days. She had arrived at this figure by comparing the 12-month cohort count in Shopify to the new-customer count and doing rough division. The actual analysis — which customers had repurchased, when, through what channel, and after what type of post-purchase experience — had never been done, because the data to do it lived in four separate systems that no one had connected. Shopify held the order history. Recharge held the subscription data. Klaviyo held every email sent, opened, and clicked. Yotpo held the loyalty points, reviews, and referrals. The retention team worked from weekly Klaviyo campaign reports and monthly Shopify cohort exports. They had no way to see which email sequences were actually driving repurchase, which loyalty redemptions were correlated with long-term retention, or which subscription cancellation patterns were predictable before a customer clicked cancel. The subscription churn problem was the most operationally damaging. The brand ran three subscription products on 30, 45, and 60-day cycles. Cancellations were discovered when they happened — a Recharge notification, a Shopify order flagged as cancelled, an email bounce. There was no model to identify a subscriber who was showing pre-cancellation behaviour — skipping deliveries, reducing order frequency on one-time purchases, opening fewer emails — before they reached the cancel button. The retention blind spots: • Four customer data systems — Shopify, Recharge, Klaviyo, Yotpo — with no connections between them and no unified customer view • Repurchase analysis done manually from Shopify exports — a monthly exercise producing rough cohort numbers with no channel, campaign, or product-level attribution
Subscription churn entirely reactive — cancellations discovered after the fact, with no
pre-cancellation signal model to enable intervention
Email retention sequences built on time-based logic (Day 7, Day 14, Day 30 post-purchase)
rather than behavioural triggers — the same flow sent to a customer who had already purchased
again as to a customer who had gone completely quiet
Loyalty programme data never connected to purchase behaviour — the retention team did not
know whether loyalty point earners had materially higher repeat rates than non-participants
19+ hours per week spent by the retention team pulling, cleaning, and reconciling data from the
four platforms to produce the weekly retention report
Every weekday morning, the lead analyst would begin at 8 AM. She would log into each brand's Shopify admin and pull the overnight orders report — three logins, three exports. She would log into Meta Business Manager for each brand — three more logins, three ad account exports. She would pull
The DataVine Solution
The Solution
- W e started with a single question: what does a customer who stays look like at 7, 30, 60, and 90 days post-first-purchase, compared to a customer who leaves? Answering that question required a unified customer record that connected every interaction across all four platforms. Building that record was the foundation of every analysis that followed. Building the unified customer data foundation • Built API integrations pulling order-level transaction history from Shopify, subscription status and delivery history from Recharge, email send, open, click, and conversion events from Klaviyo, and loyalty point earnings, redemptions, and referral events from Yotpo into a centralised Google BigQuery warehouse • Built a customer master connecting every identified buyer across all four platforms — matching Shopify customer IDs to Recharge subscriber records, Klaviyo profiles, and Yotpo accounts — creating a single customer timeline showing every purchase, every email interaction, every loyalty event, and every subscription action in chronological order • Built dbt transformation models producing a clean customer activity dataset with standardised engagement metrics — one definition of an "active" customer, one methodology for calculating days-since-last-purchase, documented logic for handling subscription skips, pauses, and gift orders • Built a full 24-month historical customer cohort dataset, reconstructing the complete purchase and engagement history for every identified buyer from the available platform data Building the retention intelligence layer • Built a customer segmentation model classifying every active customer daily into one of seven retention states: new (first purchase within 14 days), engaged (2+ purchases, active email engagement), at-risk (single purchase, no second purchase within 45 days), lapsing (declining purchase frequency), subscription-active, subscription-at-risk (showing skip and engagement decline signals), and churned — with the segment updated automatically each morning • Built a subscription churn propensity model using gradient boosting on 18 months of subscriber behaviour — scoring every active subscriber daily on their probability of cancellation within the next 30 days, with the specific behavioural signals driving each score surfaced alongside it • Built a customer lifetime value model projecting each customer's expected 12-month purchase value based on their acquisition channel, first-product purchased, email engagement trajectory, and purchase frequency trend — giving the acquisition team a predicted LTV figure for each new-customer cohort within 30 days of acquisition • Built a repurchase driver analysis identifying the email sequences, loyalty redemptions, and product pairings most strongly correlated with second-purchase conversion — giving the retention team a data-driven brief for flow restructuring
- Building the retention intelligence dashboard • Built a Looker Studio dashboard suite with five views: a CMO customer health summary, a cohort performance tracker comparing 30/60/90-day repurchase rates by acquisition channel and first-product, a subscription health dashboard with live churn propensity scores, a retention campaign performance view linking email sequences to repurchase outcomes, and a loyalty programme effectiveness analysis • Built a daily at-risk customer action queue — every customer in the at-risk or subscription-at-risk segment, ranked by churn propensity score and projected LTV, with their engagement history and top product affinity surfaced for the retention team to act on before that day's email flows launched • Configured automated weekly retention briefings for the CMO and retention team covering cohort health, subscription churn rate, at-risk segment size movement, and the prior week's repurchase rate by acquisition channel
Operational Impact
The first Monday after go-live, the operations manager opened the dashboard at 8 AM instead of spending her Friday afternoon in Excel. Every SKU across all three platforms, every stock level, every days-of-supply figure — live, current as of 4 AM that morning, and built by no one. The stockout risk tracker immediately surfaced five SKUs that were projected to reach zero inventory within the next 14 days — none of which had triggered any alert in the prior week's manual review because the review had been based on aggregate category-level stock rather than SKU-level velocity analysis. Emergency reorders were placed the same morning. Four of the five SKUs maintained continuous Buy Box eligibility through what would have been their stockout window. The excess inventory report identified $620K of stock across 47 SKUs in the pet accessories and personal care categories that had not turned in more than 90 days. The procurement team had continued ordering these SKUs on their standard cycle because no one had flagged the velocity deterioration. Over the following eight weeks, 31 of the 47 SKUs were cleared through Amazon Lightning Deals, W almart Rollbacks, and targeted price adjustments. W arehouse carrying costs for those SKUs were eliminated.
16+ hours of weekly manual inventory reconciliation eliminated — replaced by a 4-hour-refresh
automated cross-platform inventory dashboard
$620K in excess and slow-moving inventory identified and actioned — reducing carrying cost and
releasing working capital
41% reduction in stockout incidents across all three channels in the first 60 days post go-live
Stockout detection moved from reactive (post-suppression) to proactive (21-day forward-looking
risk model)
Static reorder points replaced by a demand-adjusted dynamic model updated weekly —
incorporating sales velocity, seasonality, supplier lead times, and ranking sensitivity
First unified view of fulfilment performance across Amazon FBA, W almart Fulfilment, and 3PL —
enabling channel-level SLA and return rate comparison for the first time
WHAT COMES NEXT
DataVines is now building a marketplace pricing intelligence layer for the seller — pulling competitor pricing data across all active ASINs and W almart items on a daily basis, comparing against the seller's current prices, and generating repricing recommendations that balance Buy Box competitiveness against margin floor constraints. The goal is to move from manual weekly pricing reviews to a continuously calibrated system that responds to competitive movements within hours, not after the next Friday review cycle. CASE STUDY 03 · E-COMMERCE · HEALTH & WELLNESS · CUSTOMER RETENTION & LIFETIME VALUE ANALYTICS They Were Acquiring Customers at $58 Each. Most of Them Only Bought Once.
A direct-to-consumer health and wellness brand had a strong first-purchase funnel but no systematic view of what happened to customers after their first order. DataVines built the customer lifetime value and retention intelligence platform that shifted the entire organisation's attention from acquisition cost to long-term customer value.
The retention team's Monday morning changed immediately. Instead of pulling four platform exports and spending the morning building their weekly report, they opened the dashboard and had a complete customer health view — cohort repurchase rates, at-risk segment size, subscription churn score distribution, and the week's top action queue — before 9 AM. The repurchase driver analysis produced a finding that directly contradicted the team's existing email sequence logic. The flows had been structured around post-purchase timing — Day 7 check-in, Day 14 review request, Day 30 replenishment reminder. The data showed that the strongest predictor of second-purchase conversion was not timing but product pairing: customers whose first order included one specific protein SKU repurchased at a rate 2.7x higher when their first post-purchase email featured a complementary product recommendation rather than the standard brand story sequence. The subscription churn model identified 340 active subscribers in the pre-cancellation signal zone — customers who had skipped a delivery in the past 45 days and whose email open rate had declined by more than 40%. None of them had received any differentiated retention communication. A targeted win-back sequence was launched to all 340 within the week. Cancellations from that cohort over the following 30 days were 44% lower than the historical baseline for equivalent pre-cancellation segments.
19+ hours of weekly retention analytics prep time eliminated — replaced by an automated daily
dashboard and weekly briefing
Subscription churn detection moved from reactive (post-cancellation notification) to proactive
(30-day forward-looking propensity model covering 100% of active subscribers)
34% improvement in 90-day repeat purchase rate following data-driven email flow restructuring
based on repurchase driver analysis
2.4x higher projected LTV for customers acquired through email-first versus paid-only journeys —
enabling acquisition budget reallocation toward higher-LTV channels
Customer segmentation model updated daily — retention team's outreach driven by live
behavioural data, not static time-based post-purchase sequences
Four customer data platforms unified into a single customer master — one timeline, one segment,
one LTV score per customer, every morning
WHAT COMES NEXT
DataVines is now building a next-best-action recommendation engine for the brand's retention flows — using each customer's purchase history, product affinity, email engagement pattern, and current retention segment to generate a personalised recommended action (product recommendation, loyalty offer, subscription pause, or win-back incentive) for each individual customer, triggered automatically by behavioural events rather than sent on a fixed calendar. The goal is to make the retention programme respond to customer behaviour as it happens, not on a schedule built around when it is convenient to send. CASE STUDY 04 · E-COMMERCE · MULTI-BRAND RETAIL GROUP · EXECUTIVE KPI & DATA PIPELINE AUTOMATION Three Brands, Seven Platforms, One Person Spending Her Mornings in Spreadsheets. Every Day. A multi-brand e-commerce group operating three DTC storefronts across fashion, home décor, and beauty had a full-time analyst whose primary job had become producing daily reports manually. DataVines automated the entire data infrastructure — and gave the analyst her days back for the work that actually required a human.
More Success Stories
Explore how we helped other brands in the E-Commerce sector.
ECommerce Case Study1
DATA-VINES Client Success Series E-COMMERCE & RETAIL Case Studies Four stores. Four data crises. Four transformations. How DataVines helped e-commerce brands, DTC retailers, and marketplace sellers replace gut instinct and disconnected spreadsheets with real-time intelligence — and star...
ECommerce Case Study2
consistently appearing early in the customer journey for purchases that subsequently completed through channels that claimed last-click credit. $112K was reallocated away from TikTok and Pinterest in the following budget cycle, toward Google Shopping and Klaviyo email flows. Blended ROAS, measure...