Casino Analytics, Rebuilt

First, Make It Fast

The analytics endpoint took 68 seconds for the opted-in view. Almost none of that was the 35 aggregate queries, which totalled about a quarter of a second. It was the quality scorer looking up the ship for every casino offer sailing through a path that bypassed the in-memory ship cache, seven passes over the full set, roughly 170K database round trips per request.

Everything is now derived from one grouped snapshot of the opted-in sailings (by room type, comp flags, nights, ship class, and points) plus one row per account, with every section computed in PHP from count-weighted sums. The finished bundle is cached for 30 minutes and invalidated when anyone toggles their data-sharing setting. Personal sections stay live and only query your own sailings. An old-vs-new parity script on production confirmed identical values before the swap.

Then, Make It Right (#201)

Rebuilding the pipeline exposed metric bugs that had been baked into every chart:

• Celebrity accounts were in the Royal Caribbean tables. Accounts were never filtered by cruise line, so Captain's Club tiers inflated the user counts, produced 45 "Unknown" casino tiers, and skewed recency. Analytics now use one shared definition of a contributing account: Royal Caribbean, active, not deleted, not paused, no login failures, synced within 7 days, deduplicated by login, and opted in.
• Tier sorting was a no-op. Stored tiers are PRIME and DIAMOND_PLUS; the sort map had "Prime" and "Diamond Plus", so every tier sorted last. Case-insensitive now, with a Pre-Gold rung.
• Junior Suite scored like a full suite via keyword match. It's now 35, between Balcony (30) and Suite (40).
• Room types compared case-sensitively (INTERIOR vs Interior), GTY counted as a comp in one place and ranked above Comp in another, and a single 300-sailing offer could dominate any sailing-weighted distribution. Distributions are per-account weighted now, with sailing counts kept for tooltips.
• "Average offers per user" is now a median, with n shown on every bar and tile.

New Sections

Comp value in dollars. Each comped sailing is joined to the current 2-guest fare for its cabin class, so you can see the distribution of what a comp is worth by room type and tier, and "your best offer is worth about $X".

Unlock curve. For each room type and comp type, the share of accounts at each tier and points level that hold at least one. This is the "when do I start getting balconies" chart.

Offer trend. New offers per contributing account per month since January, with the first-sync backlog excluded, plus a day-of-month histogram of when offers arrive.

Free play and perks. Sailings and offers bucketed by free play amount ($0, $1-50, $51-100, $101-250, $251-500, $500+) by tier, and perk families (bonus free play, onboard credit, passes) by tier.

Where the comps are. Sail-month share for the next twelve months and the top departure ports.

Casino tier is the primary axis throughout, since it survives the annual points reset. Points brackets are still there, but they're now resolved at build time from the Club Royale thresholds (Choice, Prime at 2,500, Signature at 25,000, Masters at 100,000) with a few extra edges inside the wide bands, and any band with fewer than three contributing accounts merges into its neighbor so no bar describes fewer than three people. The contributing-accounts tile also shows what share of eligible accounts have opted in.