Developing Analytics and Reporting Dashboard
Analytics dashboard visualizes business metrics in real-time or periodically. Goal: make data from multiple sources readable without SQL queries. Key requirements: fast loading (users shouldn't wait 30 seconds), flexible filtering, correct aggregation.
Data Sources
Dashboard pulls from multiple places:
- Primary database PostgreSQL / MySQL
- Analytics warehouse (ClickHouse, BigQuery, Redshift)
- External APIs (Google Analytics, ad platforms)
- Files (CSV, Excel import)
Query Optimization
Main problem with analytical dashboards: heavy queries against OLTP databases. Solutions:
Materialized Views:
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
DATE_TRUNC('day', created_at) as date,
SUM(total) as revenue,
COUNT(*) as orders
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('day', created_at);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
Aggregation Tables (Rollups):
CREATE TABLE metrics_hourly (
metric_date DATE,
metric_hour INTEGER,
visits INTEGER,
conversions INTEGER,
revenue DECIMAL,
PRIMARY KEY (metric_date, metric_hour)
);
-- Populate via scheduled job
INSERT INTO metrics_hourly (metric_date, metric_hour, visits, conversions, revenue)
SELECT
DATE(created_at),
DATE_PART('hour', created_at)::INTEGER,
COUNT(*),
COUNT(CASE WHEN converted THEN 1 END),
SUM(COALESCE(revenue, 0))
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(created_at), DATE_PART('hour', created_at);
React Dashboard
function AnalyticsDashboard() {
const [metrics, setMetrics] = useState<Metrics | null>(null);
const [loading, setLoading] = useState(true);
useEffect(() => {
fetch('/api/analytics/summary?period=month')
.then(r => r.json())
.then(data => {
setMetrics(data);
setLoading(false);
});
}, []);
if (loading) return <Spinner />;
if (!metrics) return <div>No data</div>;
return (
<Grid columns={3} gap={4}>
<StatCard title="Revenue" value={`$${metrics.revenue}`} change={metrics.revenue_change} />
<StatCard title="Conversions" value={metrics.conversions} change={metrics.conversion_change} />
<StatCard title="Avg Order Value" value={`$${metrics.aov}`} />
<RevenueChart data={metrics.daily_data} />
<ConversionFunnel steps={metrics.funnel} />
<GeographicChart data={metrics.geo_data} />
</Grid>
);
}
Timeline
Basic dashboard with 5-6 key metrics: 2-3 days. Advanced reporting with filters, drill-downs, custom date ranges: 5-7 days.