CASE FILE 07NDA — CLIENT GENERALISEDALL DATA MOCKED

Customers, sorted into 16 groups.

A Shopify store was sending the same offers to everyone. This project scored every customer on loyalty and buying frequency, and sorted them into 16 clear groups — each with its own plan.

CLIENTSHOPIFY STORE
MODELRETENTION → REPEAT REVENUE
STACKSHOPIFY · SQL · LOOKER
ENGAGEMENT5 PHASES · FULL SYSTEM
STATUSLIVE · NDA
THE BOARD — OVERVIEWAI-BUILT · LIVE HTML
EXHIBIT A
1

The big grid is the main view: every customer sits in one of 16 boxes.

2

The highlighted boxes are the most valuable groups to target next.

3

The list on the right shows the marketing plan running for each group.

The Brief

Everyone got the same offer.

Over 8,000 customers were treated the same way. Loyal big spenders got the same 10% discount as one-time buyers, and customers about to leave heard nothing at all.

We scored every customer on loyalty and how often they buy, then placed them in a 4×4 grid. Each of the 16 boxes has a count, a value and a marketing plan.

THE GOAL: stop treating 8,412 customers as one group. Give each group the right offer.

✗ THE PROBLEMS

  • ✗ VIPs and one-timers got identical offers
  • ✗ At-risk customers invisible until churned
  • ✗ High-AOV single buyers never re-approached
  • ✗ Winback timing guessed, not measured
  • ✗ Retargeting audiences built by hunch
The System

How the system works.

Customer data comes from Shopify. SQL scores each customer on loyalty and buying frequency every night. The scores fill the 4×4 grid in Looker Studio, and the groups are sent to Meta and Klaviyo for targeting.

Shopify

Customer-level orders, AOV, refunds, recency.

→

SQL Scoring

Loyalty + frequency scores, refreshed nightly.

→

The Matrix

4×4 crossing in BigQuery-style marts — counts, values, movement.

→

Looker Studio

Matrix hero page + segment, behaviour and plays pages.

→

Action Layer

Segment exports to ads + email · pocket-shift alerts.

The Process

The process, step by step.

01

Audit

  • Profiled the base: repeat, refund, dormancy patterns
  • Found the two highest-value neglected pockets
02

Measurement Plan

  • Loyalty + frequency scoring rules, signed off
  • Named the play per pocket before building
03

Implementation

  • Customer-level extraction + nightly SQL scoring
  • Matrix marts with movement tracking
04

Dashboard Build

  • Matrix-hero layout — the grid is the page
  • Segment plays list with live status
05

Automate & Optimise

  • Audience exports to Meta + Klaviyo
  • Pocket-shift alerts when segments move
The Build

The groups, in detail.

Two more pages: the group table and the alerts page. Built in HTML with sample data.

PAGE 02 / 06 — POCKETSLIVE HTML · MOCK DATA
EXHIBIT B
Pocket Deep-Dive

Each group, with its plan.

  • ■ The table shows each group’s size, average order value and the plan assigned to it.
  • ■ The bars show how the win-back emails perform, step by step.
  • ■ The status list confirms the groups are synced to Meta and Klaviyo each morning.
PAGE 06 / 06 — ALERTS + AILIVE HTML · MOCK DATA
EXHIBIT C
Automation Layer

The grid reports its own changes.

  • ■ Rules spot customers moving between groups the week it happens.
  • ■ Recovered customers are counted in dollars, not guesses.
  • ■ Each alert includes the next step, like enrolling a group in an email flow.
Deliverables

What was delivered.

The Results

The results.

16 CLEAR GROUPS.

One big customer list became 16 groups, each with a count and a plan.

BEST GROUPS, FOUND.

Loyal-but-infrequent buyers and big one-time spenders — the most valuable groups — are now targeted directly.

WIN-BACKS ON TIME.

Customers going quiet are caught by a set rule and an automatic email flow, not by luck.

NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS

Next Steps

Want this for your business?

If your whole list gets the same discount, this grid will show you who deserves better.

★ Book a call →Next case →← All cases