CCFish runs a real-time game analytics dashboard built on Cloudflare Workers and D1 that serves two masters — developers debugging live gameplay and marketers optimizing player revenue — from a single data pipeline. Every `level_up`, `iap_purchase`, `session_end`, and `crash_report` hits a Workers endpoint within milliseconds, lands in D1, and surfaces in under 100ms across dashboards purpose-built for each audience.
The Problem — Split Analytics Leaves Both Teams Blind
Most mobile games maintain two analytics systems. The development team instruments a pipeline for crash monitoring and error logging via a third-party SDK. The marketing team runs a separate stack for DAU, retention cohorts, and IAP conversion funnels. This split creates three chronic problems.
**1. Partial visibility.** Devs see a crash but not whether it correlates with an IAP flow. Marketers see conversion drop but cannot tell if a performance regression in the shop scene caused it.
**2. Duplicate instrumentation.** The same event fires through two SDKs, consuming bandwidth on devices constrained by the Cocos Creator 2.4.15 render loop.
**3. Cross-team latency.** When a player abandons payment, the dev pipeline records `purchase_cancelled` instantly, but marketing's overnight batch hides it until the next day's report.
Both teams need the same data at the same speed on the same infrastructure.
The Solution — A Unified Analytics Dashboard
A single pipeline ingests all telemetry through a Cloudflare Workers endpoint, writes to D1, and surfaces queryable dashboards for both dev and marketing. Every view queries the same D1 tables.
| Persona | Views | Key Metrics |
|---------|-------|-------------|
| **Developer** | Error trends, session logs, performance traces | Crash rate, frame drop %, API latency P50/P95/P99 |
| **Marketer** | Retention cohorts, IAP funnels, ad revenue | DAU/MAU, D1/D7/D30 retention, ARPU, ARPPU |
| **PM/Lead** | Executive summary, cross-correlation | Dev x Marketing composite view |
The key decision: store raw events as granular rows and compute aggregations via SQL views. This preserves every event so new questions can be answered from history without reprocessing.
Architecture — Workers Ingestion, D1 Storage, Dashboard Query Layer
The pipeline has three tiers: game client, Cloudflare Worker ingestion, D1 database, and an API Worker serving the dashboard frontend.
Ingestion Worker
Receives POST payloads with a standard envelope:
```typescript
interface AnalyticsEvent {
event_id: string;
event_type: string;
player_id: string;
timestamp: number;
payload: Record<string, unknown>;
build_version: string;
platform: string;
}
```
Validates, enriches, and batches writes of up to 100 events to D1. Batching is critical — CCFish generates 50–200 events per player per session.
D1 Schema
```sql
CREATE TABLE events (
event_id TEXT PRIMARY KEY,
event_type TEXT NOT NULL,
player_id TEXT NOT NULL,
ts INTEGER NOT NULL,
payload TEXT NOT NULL,
build_version TEXT NOT NULL,
platform TEXT NOT NULL
);
```
Materialized views for retention and daily aggregates refresh via a 5-minute cron Worker.
Implementation — Building the Dashboard Views
Developer Diagnostics
```typescript
export async function getErrorTrends(request: Request, env: Env) {
const { since, build } = Object.fromEntries(
new URL(request.url).searchParams.entries()
);
const result = await env.DB.prepare(`
SELECT payload->>'$.error_code' AS error_code,
payload->>'$.scene' AS scene,
COUNT(*) AS occurrences,
COUNT(DISTINCT player_id) AS affected_players
FROM events WHERE event_type = 'crash_report'
AND ts > ? AND (? IS NULL OR build_version = ?)
GROUP BY payload->>'$.error_code', payload->>'$.scene'
ORDER BY occurrences DESC LIMIT 50
`).bind(since, build || null, build || null).all();
return Response.json(result.results);
}
```
Devs see: "The Arctic Expedition scene has a null-pointer affecting 340 iOS players since build 2.0.0."
Marketing Analytics
```typescript
export async function getRevenueFunnel(request: Request, env: Env) {
const days = parseInt(
new URL(request.url).searchParams.get('period') || '7d'
);
const result = await env.DB.prepare(`
WITH funnel AS (
SELECT player_id,
bool_or(event_type = 'iap_store_view') AS viewed_store,
bool_or(event_type = 'iap_product_select') AS selected_product,
bool_or(event_type = 'iap_purchase_start') AS started_checkout,
bool_or(event_type = 'iap_purchase_complete') AS completed_purchase
FROM events WHERE ts > unixepoch() * 1000 - ? * 86400000
GROUP BY player_id
) SELECT COUNT(*) AS total,
SUM(completed_purchase::int) AS purchases FROM funnel
`).bind(days * 86400000).all();
return Response.json(result.results[0]);
}
```
Marketers watch this funnel live during campaigns. If store views spike but purchases do not, they know the issue is price or friction — within seconds.
Cross-Correlation View
```sql
SELECT DATE(dev.ts / 1000, 'unixepoch') AS day,
COUNT(DISTINCT dev.player_id) AS players_with_errors,
COUNT(DISTINCT iap.player_id) AS players_with_purchases
FROM events dev
LEFT JOIN events iap ON iap.player_id = dev.player_id
AND iap.event_type = 'iap_purchase_complete'
AND iap.ts BETWEEN dev.ts AND dev.ts + 300000
WHERE dev.event_type = 'crash_report'
GROUP BY day ORDER BY day;
```
Crash-affected players showed 62% lower same-session purchase rates. This signal led directly to prioritizing crash fixes ahead of feature work — a correlation impossible to find with split systems.
Results — Under 100ms Queries, Cross-Team Alignment
After three release cycles:
| Metric | Before | After |
|--------|--------|-------|
| Crash cluster surfacing | 4–6 hours | <30 seconds |
| IAP funnel latency | 24 hours | <100 ms |
| Cross-correlation | Impossible | ~200 ms |
| Dev dashboard views/day | ~5 | ~40 |
| Marketing dashboard views/day | ~3 | ~120 |
Both teams now start meetings from the same dashboard, looking at the same D1 data — filtered to their roles.
Key Takeaways
**One pipeline, two audiences.** Store raw events at maximum granularity and compute persona-specific views on top.
**D1 is fast enough for real-time analytics.** With proper indexing and materialized views, aggregate queries return in under 100ms.
**Cross-correlation is the killer feature.** Joining crash data with purchase data in one SQL query uncovered patterns neither team would have found alone.
**Build for the unknown.** Raw events let you answer new questions without re-architecting. When marketing wanted "session depth before first purchase," it was a 15-line SQL query, not a data pipeline project.