| title | ADR-0009: In-memory all-time computation for customer journey analytics | |||
|---|---|---|---|---|
| description | Customer journey/loyalty views compute from one all-time query grouped in Elixir, with no caps, paging, or time window for v1. | |||
| adr_id | 0009 | |||
| status | active | |||
| date | 2026-06-05 | |||
| tags |
|
The Customer Loyalty analytics tool (AmplifyWeb.Tools.CustomerLoyaltyLive,
backed by Amplify.Services.SalesComparison) answers a composition question:
for an event or the last 3 months, what fraction of buyers were first-timers,
returning, or lapsed. classify_customer/2 decides this from two timestamps —
it is purely time-based and never records which show a customer's prior
purchase was for.
User feedback asked to see the returning customers themselves and which shows
they returned to — a retention/journey question the current model cannot
answer. A new "Repeat Customers / Journeys" capability is being added with three
views that all derive from one structure, the per-customer show journey
(distinct (email, variant) rows joining Order → LineItem → ProductVariant → Product, grouped by lowercased email, ordered by ProductVariant.starts_at_utc):
- A — repeat-customer list (superfan leaderboard): emails with ≥2 shows + their show history, each linking to the existing customer detail page.
- B — show-to-show migration: consecutive show pairs in each journey ("after X, returning customers next bought Y").
- C — repeat-attendance histogram: count of shows per customer bucketed 1 / 2 / 3 / 4+.
Two modeling choices were settled during exploration and constrain this
decision: "returned to a show" means purchased (not scanned attendance —
many venues don't scan tickets), and journeys are ordered by show date
(starts_at_utc), not order date (assumption: a customer sees a show, then buys
more). Unlike the existing 3-month composition buckets, the journey views use
all-time history, because loyalty is a multi-year signal and a 3-month cap
would hide exactly the superfans the feature exists to surface.
Compute the journey backbone with a single all-time DB query and do all grouping, sequencing, and pair-counting in Elixir memory — with no pagination, no top-N caps, and no time window — for v1. The one in-memory structure feeds all three views (A list, B migration, C histogram).
- One all-time query + in-memory grouping (chosen): single read, no N+1, all three views share one structure, simplest code. Con: the full result set lives in memory and renders on one page; a very large account could be slow.
- SQL-side aggregation (window functions for migration pairs, GROUP BY for histogram): bounded memory, scales further. Rejected for v1 as premature complexity — three separate non-trivial queries for a question whose row counts are modest on an internal admin tool.
- Keep the 3-month window: bounds the data cheaply. Rejected — it defeats the loyalty use case by hiding multi-year repeat customers.
- Use scanned
Ticketdata for true attendance: more accurate "attended". Rejected — scan coverage is inconsistent across venues, so it would undercount and mislead.
- One query, one shared transform — the three views stay consistent and cheap to build and reason about.
- Expected load is fine: a venue with ~10k customers × ~50 shows ≈ 30k distinct rows grouped in memory.
- Risk (accepted for v1): a very large account (100k+ customers) can produce a large in-memory result and a slow page render; there is no paging or cap to bound it. This is an internal admin tool, so the blast radius is limited to staff users.
- Re-evaluate when an account's journey result is large enough to be felt. Deferred mitigations, in rough order: cap to the last 12 months, limit the migration matrix to top-N shows by volume, then push aggregation into SQL.
- "Purchased + show-date ordering" is now the house definition for journey
analytics; if a future view needs true attendance it must opt into
Ticketdata explicitly and account for uneven scan coverage.