Self-directed · SQL & Analytics
A SQL retention teardown for a coffee shop
I wanted a project I owned start to finish, not one that stopped at a slide. So I built a year of coffee-shop sales from scratch and put a single question to it: who comes back, and what makes them?
Why I built this
I built it for two reasons. The first is simple: I wanted something on my portfolio that shows what I can do with data, not just that I passed a course — something a person can open and read straight from the raw rows down to a recommendation. The second reason was for me. I'd just finished SQL Essential Training, and I don't think a certificate counts for much until you've pointed it at a problem nobody handed you the answer to. So I made up my own problem and sat with it until I had worked it through.
Retention was a deliberate choice. It's what marketing plans are forever gesturing at — “build loyalty” — and almost never measuring. I wanted to find out whether I could take that vague goal and turn it into a number, and then into a decision.
How I worked
I started with the question, not the code. Before writing anything I decided exactly what I was trying to answer: who comes back, when do they fall away, and is there anything the shop could do about it. Settling that first kept me from writing a dozen queries that were interesting and decided nothing.
Then I needed data. I had no real point-of-sale export to work from, so I wrote one — a generator that treats customers as regulars, occasionals, and one-time walk-ins, and spreads their visits across a year. I set a fixed seed so the numbers don't shift every time it runs; if I'm going to quote a figure, I want to be able to get it back.
I worked up from the easy questions. Headline counts and average order value came first, mostly to check the data behaved like a real shop. Once I trusted it, I moved to the harder one: a cohort table that groups customers by the month of their first visit and follows who sticks around.
A drop-off curve shows you where customers leak, but not what to do about it, so I turned the finding into two ideas an owner could actually act on — pushing loyalty signup, and whether a first-visit bag of beans says anything about who stays. I wrote a query for each. One held up and one didn't: the loyalty gap was hard to argue with, while the bean-bag hunch I had been sure of came back flat. I left the dead end in rather than quietly cutting it, because the reason I like this kind of work is that the data gets to overrule me.
The part that gave me the most trouble was the date math. Lining up each visit against a customer's first month, in plain SQL, took a few tries before it came out clean.
The setup
I wanted a realistic problem to point SQL at, so I wrote a generator that simulates a single independent coffee shop over twelve months — 900 customers, about 10,200 orders, and just under 17,000 line items. Customers fall into the mix any shop would recognize: a core of regulars, a wider band of occasionals, and a long tail of people who walk in once and never come back. Baskets are built drink-first, with pastries, food, and the occasional retail bag of beans layered on.
Then I loaded it into SQLite and worked entirely in SQL — CTEs, window-style date math, conditional aggregation — treating it the way I'd treat a real point-of-sale export.
The headline numbers
A 74.9% repeat rate — the share of customers who came back on a second, separate day — sounds healthy. But a single lifetime number hides when people drift away. For that you have to cohort.
Cohorting by first visit
I grouped every customer by the month of their first purchase, then measured how many were still active one, two, and three months on. The query leans on two CTEs — one to find each customer's first month, one to list every active month — and a little integer date math to get the month offset:
WITH firsts AS (
SELECT customer_id, substr(MIN(order_date),1,7) AS cohort
FROM tx GROUP BY customer_id
),
months AS (
SELECT DISTINCT customer_id, substr(order_date,1,7) AS ym FROM tx
),
joined AS (
SELECT f.cohort, f.customer_id,
(CAST(substr(m.ym,1,4) AS INT)*12 + CAST(substr(m.ym,6,2) AS INT))
- (CAST(substr(f.cohort,1,4) AS INT)*12 + CAST(substr(f.cohort,6,2) AS INT)) AS month_n
FROM firsts f JOIN months m ON f.customer_id = m.customer_id
)
SELECT cohort, COUNT(DISTINCT customer_id) AS size,
ROUND(100.0*COUNT(DISTINCT CASE WHEN month_n=1
THEN customer_id END)/COUNT(DISTINCT customer_id),1) AS m1_pct
FROM joined GROUP BY cohort;| First-visit cohort | Customers | Month +1 | Month +2 | Month +3 |
|---|---|---|---|---|
| Jun 2025 | 83 | 61.4% | 45.8% | 41.0% |
| Aug 2025 | 94 | 50.0% | 46.8% | 36.2% |
| Oct 2025 | 99 | 62.6% | 57.6% | 46.5% |
| Dec 2025 | 96 | 58.3% | 43.8% | 34.4% |
| Jan 2026 | 97 | 61.9% | 58.8% | 44.3% |
The shape is what matters here: roughly 55–62% come back the next month, settling around 40% by month three. The biggest leak is early. If you want to move retention, the first 30 days after someone's first cup is where the room to do it is.
Testing two levers
A drop-off curve tells you where the problem is, not what to do. So I tested two hunches a shop could act on. One of them held up; the other didn't, and I've kept both here.
Lever A — the loyalty program (worked)
Splitting customers by whether they joined the loyalty program on day one:
| Segment | Customers | Repeat rate | Avg. visits |
|---|---|---|---|
| Loyalty member | 199 | 96.0% | 20.4 |
| Non-member | 701 | 68.9% | 7.5 |
Members repeat at 96% versus 69% and visit 2.7× as often. Correlation, not proof of cause — the people who sign up are probably already the committed ones — but the gap is large enough that pushing signup at the first visit is an obvious thing to test for real.
Lever B — first-visit retail beans (didn't)
My hunch was that buying a bag of beans to take home on visit one signals a customer who'll stick. The data disagreed:
| Segment | Customers | Repeat rate |
|---|---|---|
| Bought beans on visit 1 | 78 | 71.8% |
| Did not | 822 | 75.2% |
No lift — if anything, a hair lower. A first-visit bean purchase isn't a retention signal here, so I wouldn't build a campaign on it. It was a reasonable idea, and one query was enough to put it to rest.
What I'd tell the owner
If this were a real shop, three things would be worth talking about. The clearest is the first month, since that is where the cohort curve drops off hardest — a welcome offer, or any reason to come back a second time, goes straight at the leak. Loyalty signup looks worth pushing at the very first visit, because members come in close to three times as often; I'd still want an A/B test to see how much of that the program is causing rather than just attracting. And I'd leave the bean-bag idea alone. It's intuitive, but the numbers don't support it.
What I took away
This is the part of the work I like most. I started with a vague question — is retention okay? — and turned it into a cohort table. That table turned up two things worth testing, and one of them I ended up throwing out. That is the reason I wanted to learn this in the first place: to follow a question all the way down to an answer I can stand behind.
Tools & method
- SQL (SQLite) — CTEs, conditional aggregation, cohort date math
- Python — data generation and a thin query runner
- Fully reproducible: fixed random seed, version-controlled SQL
Every figure above is reproducible. The files: analysis.sql (the queries), generate_data.py (builds the dataset), run.py (runs the queries), and the generated data itself, transactions.csv (724 KB). Run python3 generate_data.py && python3 run.py to print the numbers on this page.