The Problem
CCFish's player base spans casual free-to-play users, mid-tier spenders, and high-value whales. These segments behave completely differently. A $1.99 starter pack offer delights a day-2 new user but annoys a whale who already spent $200. A "50% off premium pass" appeals to mid-tier spenders but feels irrelevant to casual users who never buy. Sending the wrong offer to the wrong segment wastes conversion potential and damages player sentiment.
Manual segmentation is painful. The marketing team exports player data from the game database, builds segments in a spreadsheet, creates offer variants for each segment, then manually targets them through the game server. This process takes 3-4 hours per campaign and segments are stale within days as players move between engagement states.
The Solution: Serverless Segment Builder on Workers + D1
We built a dynamic segment builder that runs every 4 hours as a Cloudflare Cron Trigger. It queries D1 for every active player, evaluates their behavioral profile, assigns them to one or more segments, and stores the membership in a D1 junction table. The game server queries this table in real-time to serve the right offer for each player.
```
D1: Player Events --> Cron Trigger (4x daily)
|
Worker: Evaluate Segmentation Rules
|
D1: Write player_segments junction table
|
Game API Request --> D1: Check segment membership
|
Return personalized offer --> Game Client
```
Step 1: Segmentation Rules Engine
Each segment is defined as a D1 row with a JSON rule set:
```json
{
"conditions": [
{"field": "total_purchases", "op": ">=", "value": 5},
{"field": "total_spend", "op": ">=", "value": 50},
{"field": "days_since_last_purchase", "op": "<=", "value": 30}
],
"match": "all",
"ttl_hours": 4
}
```
The rules engine supports six operators (`=`, `!=`, `>`, `>=`, `<`, `<=`) and three match modes: `all` (AND logic), `any` (OR logic), and `none` (exclusion). Segments can reference computed fields like `purchase_frequency` (purchases/days-since-install) and `whale_score` (a composite of spend, session count, and referral value).
Step 2: Behavioral Signals Tracked
The segment builder uses these player signals from D1:
| Signal | Source | Update Frequency |
|--------|--------|------------------|
| Last login timestamp | Login event | Real-time |
| Total sessions | Session end event | Real-time |
| Total purchases | Purchase event | Real-time |
| Total spend (USD) | Purchase event | Real-time |
| Days since install | Player record | Static |
| Referral count | Referral event | Real-time |
| Push opt-in status | Push token | On change |
| Active campaigns seen | Offer display log | Real-time |
The cron trigger queries a materialized view we maintain in D1 called `player_behavior_snapshot` — updated every 4 hours by the same pipeline. This avoids running expensive aggregations against the raw event table for every segment evaluation.
Step 3: Offer Targeting at Request Time
When the CCFish game client requests available offers, the Worker looks up the player's segment memberships and matches them against active offers in D1:
```sql
SELECT o.* FROM offers o
JOIN offer_segments os ON o.id = os.offer_id
JOIN player_segments ps ON os.segment_id = ps.segment_id
WHERE ps.player_id = ? AND o.active = 1 AND ps.expires_at > datetime('now')
ORDER BY o.priority DESC
LIMIT 1;
```
This query executes in under 10ms on D1 at CCFish's current scale. The join chain is Player -> Segments -> Offers, with indexes on `player_segments(player_id, expires_at)` and `offer_segments(segment_id)`.
Step 4: Segment Analytics Dashboard
Every segment evaluation writes summary stats to a D1 analytics table: segment size, average spend per member, conversion rate on targeted offers, and churn rate. This feeds a Workers-rendered dashboard that the marketing team uses to adjust segment rules without touching SQL.
The dashboard reveals surprising patterns. "Whale" players (spent > $100) represent only 2.3% of the player base but generate 38% of revenue. The "Dormant Whale" segment -- players who spent > $50 but haven't logged in for 14+ days -- has a 22% reactivation rate when offered a personalized comeback bundle. These insights would be invisible without automated segmentation.
Results
| Metric | Before | After | Lift |
|--------|--------|-------|------|
| Segment creation time | 3-4 hours/campaign | 2 minutes/campaign (config) | -99% |
| Offers per segment | 1 generic | 3-5 targeted | +300% |
| Targeted offer conversion | 3.1% | 8.7% | +181% |
| Whale segment revenue | $1,240/month | $1,890/month | +52% |
| Segmentation cost | ~$400/month (labor) | $1.80/month | -99.5% |
Key Takeaways
- **Dynamic segmentation beats static cohorts.** Player behavior changes daily. A 4-hour refresh cycle catches engagement shifts before they become churn events.
- **Rule-based segmentation is enough for 8K MAU.** You don't need ML models until you hit 100K+ players. SQL rules with composite scores capture 90% of the targeting value.
- **Delegate segment membership lookup to the game server.** The game client shouldn't decide what to show -- it should ask the server "what offers do I have?" and let the backend evaluate the rules. This prevents client manipulation and keeps targeting logic centralized.
- **Analytics feedback loops are what make segmentation valuable.** Without the dashboard showing "who converted on what", segmentation is a blind guess. Measure everything.