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

你需要一个数据看板。

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

---

一、看板架构

1.1 整体架构

code复制

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

---

1.2 看板功能规划

模块	功能	优先级
概览	UV/PV/留存/新增	P0
趋势	日/周/月趋势图	P0
页面分析	页面PV/UV排行	P1
来源分析	场景值/渠道分布	P1
用户画像	设备/地域/版本分布	P2
漏斗分析	转化漏斗	P2
留存分析	次日/7日/30日留存	P2

---

二、数据库设计

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设计

方法	路径	说明
GET	/api/overview	概览数据
GET	/api/trend	趋势数据
GET	/api/pages	页面分析
GET	/api/sources	来源分析
GET	/api/devices	设备分析
GET	/api/retention	留存分析
GET	/api/funnel	漏斗分析

---

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 技术选型

技术	选择	理由
框架	Vue 3	轻量，组件化
图表	ECharts	功能强大，中文文档完善
UI	Element Plus	Vue 3生态
构建	Vite	快

---

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>

---

六、部署方案

规模	配置	月成本
< 10万UV	1台 2核4G + MySQL + Redis	~200元
10万-100万UV	Nginx + Node×2 + ClickHouse + Redis + Kafka	~2000元
> 100万UV	K8s集群 + ClickHouse集群 + Kafka集群 + Flink	~10000元起

---

七、看板避坑指南

坑	解决方案
数据延迟	对齐统计口径、时区，允许5%误差
查询慢	预聚合 + Redis缓存 + 降采样
数据不准	和微信官方对比 + 异常检测 + 根因下钻

---

写在最后

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

搭建看板的关键：

1. 从简到繁：先做概览和趋势，再做页面和来源，最后做漏斗和留存
2. 预聚合：不要每次查询都算，提前算好汇总数据
3. 缓存：热点数据用Redis缓存，5分钟过期
4. 校准：和微信官方数据对比，确保口径一致

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