The Challenge
Salon chains and multi-location businesses need consolidated analytics. Each location generates booking data, revenue records, inventory changes, and customer feedback. Without a unified view, salon owners make decisions on gut feel rather than data. Questions like "Which location has the highest no-show rate?" or "What time of day generates the most revenue?" require stitching together data from multiple sources.
For AiSalonHub, building a traditional analytics stack (OLAP database, ETL pipeline, BI tool) would add significant infrastructure cost and complexity. Each salon generates thousands of events per day -- querying this in real-time for a dashboard demands both speed and cost-efficiency.
The Solution
AiSalonHub built a **Real-Time Analytics Dashboard** that queries Cloudflare D1 directly using materialized views and aggregation tables. No separate analytics database, no ETL pipeline, no BI tool subscription. Every chart and metric on the dashboard runs as a D1 query against pre-aggregated data, refreshed every 5 minutes by a cron worker.
The key insight: instead of querying raw event data (millions of rows), the system maintains hourly, daily, and monthly aggregation tables that reduce query complexity from scanning 10M rows to reading 500 rows.
Architecture
```
Raw Events (D1) -> Aggregation Workers -> Materialized Tables -> Dashboard API
| | | |
Booking, sale, Every 5 minutes: hourly_bookings, Cloudflare Worker
no-show, review roll up events daily_revenue, queries aggregated D1
events land to hourly tables monthly_kpi returns JSON
```
Aggregation Strategy
Three tiers of aggregation maintain the balance between freshness and query performance:
**Tier 1: Hourly Tables (5-minute refresh)**
```sql
CREATE TABLE agg_hourly_bookings (
salon_id TEXT,
date_hour TEXT, -- '2026-05-09T17:00:00'
total_bookings INTEGER,
no_shows INTEGER,
cancellations INTEGER,
walk_ins INTEGER,
revenue_cents INTEGER,
avg_service_duration_min REAL,
PRIMARY KEY (salon_id, date_hour)
);
```
**Tier 2: Daily Tables (1-hour refresh)**
```sql
CREATE TABLE agg_daily_revenue (
salon_id TEXT,
date TEXT,
service_revenue_cents INTEGER,
product_revenue_cents INTEGER,
gift_card_sales_cents INTEGER,
unique_customers INTEGER,
new_customers INTEGER,
returning_customers INTEGER,
avg_revenue_per_customer_cents INTEGER,
PRIMARY KEY (salon_id, date)
);
```
**Tier 3: Monthly KPI Tables (daily refresh)**
```sql
CREATE TABLE agg_monthly_kpi (
salon_id TEXT,
year_month TEXT, -- '2026-05'
total_revenue_cents INTEGER,
total_customers INTEGER,
churn_rate REAL,
avg_visits_per_customer REAL,
customer_lifetime_value_cents INTEGER,
booking_fill_rate REAL,
PRIMARY KEY (salon_id, year_month)
);
```
The Aggregation Worker
A Cloudflare Worker runs on a 5-minute cron trigger:
```typescript
export default {
async scheduled(event, env, ctx) {
await env.DB.prepare(`
INSERT OR REPLACE INTO agg_hourly_bookings
SELECT
salon_id,
strftime('%Y-%m-%dT%H:00:00', created_at) as date_hour,
COUNT(*) FILTER (WHERE event_type = 'booking') as total_bookings,
COUNT(*) FILTER (WHERE event_type = 'no_show') as no_shows,
COUNT(*) FILTER (WHERE event_type = 'cancel') as cancellations,
COUNT(*) FILTER (WHERE event_type = 'walk_in') as walk_ins,
SUM(event_data->>'$.amount_cents') as revenue_cents,
AVG(event_data->>'$.duration_min') as avg_service_duration_min
FROM customer_events
WHERE created_at >= datetime('now', '-2 hours')
GROUP BY salon_id, date_hour
`).run();
}
};
```
If it is the top of the hour, the worker also runs the daily aggregation. If it is midnight, it runs the monthly aggregation. This cascading approach ensures the most granular data is updated most frequently while higher-level aggregations catch up later.
Dashboard API Layer
A separate Worker serves the dashboard API:
```typescript
export default {
async fetch(request, env) {
const url = new URL(request.url);
const salonId = url.searchParams.get('salon_id') || 'all';
const period = url.searchParams.get('period') || 'today';
if (period === 'today') {
const today = new Date().toISOString().slice(0, 10);
const data = await env.DB.prepare(`
SELECT date_hour, total_bookings, no_shows, cancellations, revenue_cents
FROM agg_hourly_bookings
WHERE salon_id = ? AND date_hour LIKE ?
ORDER BY date_hour
`).bind(salonId, `${today}%`).all();
return Response.json(data.results);
}
if (period === 'month') {
const month = new Date().toISOString().slice(0, 7);
const data = await env.DB.prepare(`
SELECT * FROM agg_monthly_kpi
WHERE salon_id = ? AND year_month = ?
`).bind(salonId, month).first();
return Response.json(data);
}
const comparison = await env.DB.prepare(`
SELECT salon_id, total_revenue_cents, total_customers,
churn_rate, customer_lifetime_value_cents
FROM agg_monthly_kpi
WHERE year_month = ?
ORDER BY total_revenue_cents DESC
`).bind(new Date().toISOString().slice(0, 7)).all();
return Response.json(comparison.results);
}
};
```
Caching Layer
To handle dashboard refreshes from salon managers who open the page simultaneously at opening time, the API worker adds Cloudflare Cache headers:
```typescript
const response = Response.json(data.results, {
headers: {
'Cache-Control': 'public, max-age=120, s-maxage=120',
'CDN-Cache-Control': 'public, max-age=120'
}
});
```
This gives 2-minute cache at the edge, so 50 concurrent dashboard loads hit Cloudflare cache instead of D1. The aggregation worker runs every 5 minutes, so data is at most 7 minutes stale.
Results
After deploying the analytics dashboard to all 50 partner salons:
- **Dashboard load time**: 80ms median (P95: 200ms) -- faster than most BI tools
- **D1 queries per day**: 12,000 dashboard loads + 288 aggregation runs -- well within free tier
- **Infrastructure cost**: $0 additional -- runs on existing Workers and D1 allocations
- **Salon manager adoption**: 73% of managers check the dashboard at least once daily
Key Takeaways
- **Pre-aggregation is the key to real-time D1 analytics**: Querying raw event data at dashboard load time is expensive; hourly and daily aggregation tables make it instant
- **Serverless analytics is cheaper than managed BI**: For 50 salons generating 50K events per day, the aggregation pipeline costs approximately $8 per month in Workers CPU time
- **Cascading aggregation reduces write contention**: Writing to hourly tables every 5 minutes distributes the write load compared to a single daily batch job
- **D1 SQL is sufficient for business analytics**: Window functions, CTEs, and aggregate filters handle 95% of dashboard queries without needing a specialized OLAP database