小程序数据看板开发实战：从0到1搭建数据分析平台

数据采集完了，数据也存了，但老板问"昨天UV多少"，你还要去数据库跑SQL？

你需要一个数据看板。

这篇文章，我会从0到1教你搭建一个可用的数据看板。

一、看板架构

1.1 整体架构

code复制

┌──────────────────────────────────────────────────┐
│ 前端展示 │
│ Vue/React + ECharts/D3 │
├──────────────────────────────────────────────────┤
│ API层 │
│ Node.js/Python + RESTful API │
├──────────────────────────────────────────────────┤
│ 数据层 │
│ ClickHouse（实时） + MySQL（配置） │
├──────────────────────────────────────────────────┤
│ 采集层 │
│ 前端埋点 → 上报网关 → Kafka │
└──────────────────────────────────────────────────┘

1.2 看板功能规划

二、数据库设计

2.1 汇总表设计

sql复制

— 日汇总表
CREATE TABLE daily_summary (
stat_date Date,
app_id String,
uv UInt64,
pv UInt64,
new_user UInt64,
session_count UInt64,
avg_duration Float64,
bounce_rate Float64,
retention_1d Float64 DEFAULT 0,
retention_7d Float64 DEFAULT 0,
retention_30d Float64 DEFAULT 0,
created_at DateTime DEFAULT now(),
updated_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (app_id, stat_date);

— 页面汇总表
CREATE TABLE page_daily_summary (
stat_date Date,
app_id String,
page_path String,
uv UInt64,
pv UInt64,
avg_stay_time Float64,
exit_rate Float64,
created_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (app_id, stat_date, page_path);

— 来源汇总表
CREATE TABLE source_daily_summary (
stat_date Date,
app_id String,
scene UInt32,
source_name String,
uv UInt64,
pv UInt64,
new_user UInt64,
created_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (app_id, stat_date, scene);

— 设备汇总表
CREATE TABLE device_daily_summary (
stat_date Date,
app_id String,
platform String,
brand String,
model String,
uv UInt64,
pv UInt64,
created_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (app_id, stat_date, platform, brand);

2.2 汇总任务

javascript复制

const { ClickHouse } = require('clickhouse');

const ch = new ClickHouse({
url: 'http://clickhouse:8123',
basicAuth: { username: 'default', password: '' },
});

async function runDailySummary(date) {
// 1. 日UV/PV汇总
await ch.query(`
INSERT INTO daily_summary (stat_date, app_id, uv, pv, new_user, session_count, avg_duration, bounce_rate)
SELECT
event_date, app_id,
uniq(user_id) AS uv,
count() AS pv,
countIf(is_first_visit = 1) AS new_user,
uniq(session_id) AS session_count,
avg(session_duration) AS avg_duration,
countIf(session_pv = 1) / count() AS bounce_rate
FROM (
SELECT
event_date, app_id, user_id, session_id,
min(event_time) OVER (PARTITION BY user_id) = event_time AS is_first_visit,
max(event_time) OVER (PARTITION BY session_id) –
min(event_time) OVER (PARTITION BY session_id) AS session_duration,
count() OVER (PARTITION BY session_id) AS session_pv
FROM track_events
WHERE event_date = '${date}' AND event_name IN ('app_launch', 'page_view')
)
GROUP BY event_date, app_id
`).toPromise();

// 2. 页面汇总
await ch.query(`
INSERT INTO page_daily_summary (stat_date, app_id, page_path, uv, pv, avg_stay_time, exit_rate)
SELECT
event_date, app_id,
JSONExtractString(properties, 'pagePath') AS page_path,
uniq(user_id) AS uv, count() AS pv,
avg(toFloat64(JSONExtractString(properties, 'stayDuration'))) / 1000 AS avg_stay_time,
exit_count / count() AS exit_rate
FROM track_events
WHERE event_date = '${date}' AND event_name = 'page_view'
GROUP BY event_date, app_id, page_path
`).toPromise();

// 3. 来源汇总
await ch.query(`
INSERT INTO source_daily_summary (stat_date, app_id, scene, source_name, uv, pv, new_user)
SELECT
event_date, app_id,
toUInt32(JSONExtractString(properties, 'scene')) AS scene,
getSceneName(toUInt32(JSONExtractString(properties, 'scene'))) AS source_name,
uniq(user_id) AS uv, count() AS pv,
countIf(is_first_visit = 1) AS new_user
FROM track_events
WHERE event_date = '${date}' AND event_name = 'app_launch'
GROUP BY event_date, app_id, scene, source_name
`).toPromise();

console.log(`日汇总完成: ${date}`);
}

三、API层开发

3.1 API设计

3.2 核心API实现

javascript复制

const express = require('express');
const app = express();
const APP_ID = 'your_app_id';

// 概览数据
app.get('/api/overview', async (req, res) => {
const { date = getToday() } = req.query;

const result = await ch.query(`
SELECT uv, pv, new_user, session_count, avg_duration, bounce_rate,
retention_1d, retention_7d, retention_30d
FROM daily_summary
WHERE app_id = '${APP_ID}' AND stat_date = '${date}'
`).toPromise();

const yesterday = getYesterday(date);
const yesterdayResult = await ch.query(`
SELECT uv, pv, new_user
FROM daily_summary
WHERE app_id = '${APP_ID}' AND stat_date = '${yesterday}'
`).toPromise();

const today = result[0] || {};
const yesterdayData = yesterdayResult[0] || {};

res.json({
date,
uv: today.uv || 0,
uv_change: calcChange(today.uv, yesterdayData.uv),
pv: today.pv || 0,
pv_change: calcChange(today.pv, yesterdayData.pv),
new_user: today.new_user || 0,
new_user_change: calcChange(today.new_user, yesterdayData.new_user),
avg_duration: today.avg_duration || 0,
bounce_rate: today.bounce_rate || 0,
retention_1d: today.retention_1d || 0,
retention_7d: today.retention_7d || 0,
retention_30d: today.retention_30d || 0,
});
});

// 趋势数据
app.get('/api/trend', async (req, res) => {
const { start_date, end_date, metric = 'uv' } = req.query;

const result = await ch.query(`
SELECT stat_date, uv, pv, new_user, avg_duration, bounce_rate
FROM daily_summary
WHERE app_id = '${APP_ID}' AND stat_date >= '${start_date}' AND stat_date <= '${end_date}'
ORDER BY stat_date
`).toPromise();

res.json({
dates: result.map(r => r.stat_date),
[metric]: result.map(r => r[metric]),
});
});

// 页面分析
app.get('/api/pages', async (req, res) => {
const { date = getToday(), sort = 'pv', limit = 20 } = req.query;

const result = await ch.query(`
SELECT page_path, uv, pv, avg_stay_time, exit_rate
FROM page_daily_summary
WHERE app_id = '${APP_ID}' AND stat_date = '${date}'
ORDER BY ${sort} DESC LIMIT ${limit}
`).toPromise();

res.json(result);
});

// 留存分析
app.get('/api/retention', async (req, res) => {
const { start_date, end_date } = req.query;

const result = await ch.query(`
SELECT cohort_date, day_n, cohort_size, retained,
retained / cohort_size AS retention_rate
FROM retention_table
WHERE app_id = '${APP_ID}'
AND cohort_date >= '${start_date}' AND cohort_date <= '${end_date}'
ORDER BY cohort_date, day_n
`).toPromise();

res.json(result);
});

function getToday() { return new Date().toISOString().split('T')[0]; }
function getYesterday(date) {
const d = new Date(date); d.setDate(d.getDate() – 1);
return d.toISOString().split('T')[0];
}
function calcChange(current, previous) {
if (!previous || previous === 0) return current > 0 ? 100 : 0;
return ((current – previous) / previous * 100).toFixed(1);
}

app.listen(8080);

四、前端看板开发

4.1 技术选型

4.2 概览页面

vue复制

<template>
<div class="dashboard">
<h1>小程序数据看板</h1>
<el-date-picker v-model="date" type="date" />

<!– 核心指标卡片 –>
<div class="metric-cards">
<MetricCard title="UV" :value="overview.uv" :change="overview.uv_change" icon="👥" />
<MetricCard title="PV" :value="overview.pv" :change="overview.pv_change" icon="👁️" />
<MetricCard title="新增用户" :value="overview.new_user" :change="overview.new_user_change" icon="🆕" />
<MetricCard title="平均时长" :value="formatDuration(overview.avg_duration)" icon="⏱️" />
<MetricCard title="跳出率" :value="formatPercent(overview.bounce_rate)" icon="🚪" />
<MetricCard title="次日留存" :value="formatPercent(overview.retention_1d)" icon="🔄" />
</div>

<!– 趋势图 –>
<div class="trend-chart">
<h2>UV趋势</h2>
<div ref="trendChart" style="height: 400px;"></div>
</div>

<!– 页面排行 –>
<div class="page-ranking">
<h2>页面排行</h2>
<el-table :data="pages" stripe>
<el-table-column prop="page_path" label="页面路径" />
<el-table-column prop="uv" label="UV" sortable />
<el-table-column prop="pv" label="PV" sortable />
<el-table-column prop="avg_stay_time" label="平均停留(秒)" sortable />
<el-table-column prop="exit_rate" label="退出率" sortable />
</el-table>
</div>
</div>
</template>

<script setup>
import { ref, onMounted, watch } from 'vue';
import * as echarts from 'echarts';

const date = ref(new Date());
const overview = ref({});
const pages = ref([]);
const trendChart = ref(null);

async function fetchOverview() {
const dateStr = date.value.toISOString().split('T')[0];
const res = await fetch(`/api/overview?date=${dateStr}`);
overview.value = await res.json();
}

async function fetchTrend() {
const endDate = date.value.toISOString().split('T')[0];
const startDate = new Date(date.value);
startDate.setDate(startDate.getDate() – 29);
const res = await fetch(`/api/trend?start_date=${startDate.toISOString().split('T')[0]}&end_date=${endDate}`);
const data = await res.json();
const chart = echarts.init(trendChart.value);
chart.setOption({
tooltip: { trigger: 'axis' },
xAxis: { type: 'category', data: data.dates },
yAxis: { type: 'value' },
series: [{ data: data.uv, type: 'line', smooth: true, areaStyle: { opacity: 0.3 } }],
});
}

async function fetchPages() {
const dateStr = date.value.toISOString().split('T')[0];
const res = await fetch(`/api/pages?date=${dateStr}`);
pages.value = await res.json();
}

watch(date, () => { fetchOverview(); fetchTrend(); fetchPages(); });
onMounted(() => { fetchOverview(); fetchTrend(); fetchPages(); });
</script>

4.3 留存分析热力图

vue复制

<template>
<div class="retention-heatmap">
<h2>留存分析</h2>
<div ref="heatmapChart" style="height: 600px;"></div>
</div>
</template>

<script setup>
import { ref, onMounted } from 'vue';
import * as echarts from 'echarts';

const heatmapChart = ref(null);

async function fetchRetention() {
const endDate = new Date().toISOString().split('T')[0];
const startDate = new Date(); startDate.setDate(startDate.getDate() – 30);
const res = await fetch(`/api/retention?start_date=${startDate.toISOString().split('T')[0]}&end_date=${endDate}`);
const data = await res.json();

const dates = […new Set(data.map(d => d.cohort_date))];
const days = […new Set(data.map(d => d.day_n))].sort((a, b) => a – b);
const heatmapData = data.map(d => [dates.indexOf(d.cohort_date), d.day_n, (d.retention_rate * 100).toFixed(1)]);

const chart = echarts.init(heatmapChart.value);
chart.setOption({
tooltip: { formatter: (p) => `${dates[p.value[0]]}<br/>第${p.value[1]}天留存: ${p.value[2]}%` },
xAxis: { type: 'category', data: dates, axisLabel: { rotate: 45 } },
yAxis: { type: 'category', data: days.map(d => `第${d}天`) },
visualMap: { min: 0, max: 100, inRange: { color: ['#ebedf0', '#9be9a8', '#40c463', '#30a14e', '#216e39'] } },
series: [{ type: 'heatmap', data: heatmapData, label: { show: true, formatter: (p) => p.value[2] + '%' } }],
});
}

onMounted(fetchRetention);
</script>

五、实时看板

5.1 WebSocket实时推送

javascript复制

// 服务端
const WebSocket = require('ws');
const wss = new WebSocket.Server({ port: 8081 });
const Redis = require('ioredis');
const sub = new Redis();

sub.subscribe('uv:realtime');
sub.on('message', (channel, message) => {
const data = JSON.parse(message);
wss.clients.forEach((client) => {
if (client.readyState === WebSocket.OPEN) {
client.send(JSON.stringify({ type: 'uv_update', data }));
}
});
});

vue复制

<!– 客户端 –>
<template>
<div class="realtime-uv">
<h3>实时UV</h3>
<div class="uv-number">{{ realtimeUV }}</div>
</div>
</template>

<script setup>
import { ref, onMounted, onUnmounted } from 'vue';
const realtimeUV = ref(0);
let ws = null;

onMounted(() => {
ws = new WebSocket('ws://localhost:8081');
ws.onmessage = (event) => {
const data = JSON.parse(event.data);
if (data.type === 'uv_update') realtimeUV.value = data.data.uv;
};
});
onUnmounted(() => { if (ws) ws.close(); });
</script>

六、部署方案

七、看板避坑指南

写在最后

数据看板，是数据价值的"最后一公里"。

搭建看板的关键：

记住：看板是给人看的，不是给机器看的。用户体验和数据准确性同样重要。