867 lines
15 KiB
Markdown
867 lines
15 KiB
Markdown
---
|
||
title: 1. 你的数据库结构 → 适合做哪些分析？
|
||
source:
|
||
author: shenwei
|
||
published:
|
||
created:
|
||
description:
|
||
tags: []
|
||
link:
|
||
kanban-plugin:
|
||
aliases:
|
||
cssclasses:
|
||
---
|
||
|
||
|
||
|
||
|
||
下面给出一份**极其系统、面向实战、贴合你数据库结构**的 Apache Superset Dashboard 设计方案。内容包含：
|
||
|
||
- **指标体系设计（面向选品）**
|
||
|
||
- **数据模型（Superset Dataset）如何准备**
|
||
|
||
- **核心图表（含推荐可视化类型）**
|
||
|
||
- **完整 Dashboard 结构布局**
|
||
|
||
- **高级玩法（预测、异常、趋势、自动选品）**
|
||
|
||
- **SQL 示例（基于你当前数据结构 tiktok_products）**
|
||
|
||
|
||
因为你会持续扩展抓取系统，我用的风格是“可长期演进的专业选品分析系统”。
|
||
|
||
---
|
||
|
||
# 1. 你的数据库结构 → 适合做哪些分析？
|
||
|
||
从你提供的 DDL（）提炼可用于 Superset 的核心事实数据：
|
||
|
||
## **核心表：products**
|
||
|
||
字段用于分析选品的关键指标：
|
||
|
||
- `sold`（销量）
|
||
|
||
- `final_price`, `initial_price`, `discount_percent`
|
||
|
||
- `category`
|
||
|
||
- `store_name`
|
||
|
||
- `prodct_rating`（JSON，包含平均评分、评分数量）
|
||
|
||
- `timestamp`（抓取时间）
|
||
|
||
- `videos / product_videos / product_images`
|
||
|
||
- `position`（热度排名）
|
||
|
||
- `shipping_fee`, `specifications`, `colors/sizes`（JSON）
|
||
|
||
|
||
## **辅助表：product_reviews**
|
||
|
||
- `rating`
|
||
|
||
- `review_date`
|
||
|
||
- `review_text`
|
||
|
||
- `product_id`
|
||
|
||
|
||
可做情绪分析、评分趋势等（如果你未来接入 NLP）。
|
||
|
||
## **product_variations**
|
||
|
||
- `sku`, `stock`, `final_price`, `discount_percent`
|
||
可做 SKU 层价格、库存监控。
|
||
|
||
|
||
---
|
||
|
||
# 2. Superset Dashboard 的目标（适用于 TikTok Shop）
|
||
|
||
你的核心目标是：
|
||
|
||
> **“找出热卖产品 + 高评分 + 低竞争 + 高折扣” → 决定选哪些产品卖**
|
||
|
||
根据 TikTok Shop 的数据特性，一个专业选品 Dashboard 应支持：
|
||
|
||
### **核心能力**
|
||
|
||
1. **爆品发现（基于销量、评分、折扣、视频曝光）**
|
||
|
||
2. **价格 vs 销量关系分析（找出最优价格带）**
|
||
|
||
3. **类目机会洞察（某类目热卖、低竞争）**
|
||
|
||
4. **店铺监控（竞争对手表现）**
|
||
|
||
5. **SKU 层库存 + 价格管理**
|
||
|
||
6. **评论分析（质量、评分趋势）**
|
||
|
||
|
||
---
|
||
|
||
# 3. Superset 数据集（Dataset）如何准备
|
||
|
||
Superset 不会自动解析 JSON，你需要：
|
||
|
||
## **A. 预处理 JSON 字段（推荐）**
|
||
|
||
创建 SQL View：
|
||
|
||
### `view_products_cleaned`
|
||
|
||
包含：
|
||
|
||
```sql
|
||
SELECT
|
||
id,
|
||
title,
|
||
category,
|
||
store_name,
|
||
final_price,
|
||
initial_price,
|
||
discount_percent,
|
||
sold,
|
||
position,
|
||
timestamp,
|
||
JSON_EXTRACT(prodct_rating, '$.rating') AS rating,
|
||
JSON_EXTRACT(prodct_rating, '$.count') AS rating_count,
|
||
JSON_EXTRACT(videos, '$') AS videos_raw
|
||
FROM products;
|
||
```
|
||
|
||
理由：
|
||
|
||
- Superset 能直接计算 numeric fields（rating，rating_count）。
|
||
|
||
- JSON 在 chart 里很难使用，变成列更灵活。
|
||
|
||
|
||
## **B. 预处理评论 View（可选）**
|
||
|
||
```sql
|
||
SELECT
|
||
id, product_id, rating, review_date
|
||
FROM product_reviews;
|
||
```
|
||
|
||
用于评分趋势折线图。
|
||
|
||
---
|
||
|
||
# 4. Superset Dashboard 组件设计（完整方案）
|
||
|
||
下面给出**最适合 TikTok Shop 选品的 Dashboard 结构**。
|
||
|
||
---
|
||
|
||
# 🌟 **Dashboard 总结构（建议 4 个 Tab）**
|
||
|
||
---
|
||
|
||
# **📌 Tab 1：爆品雷达（核心指标总览）**
|
||
|
||
用于快速筛选「值得跟卖」的产品。
|
||
|
||
### 推荐可视化：
|
||
|
||
### **① KPI 卡片**
|
||
|
||
- 总产品数
|
||
|
||
- 热卖产品数（sold > X）
|
||
|
||
- 平均评分
|
||
|
||
- 平均最终价格
|
||
|
||
- 折扣商品占比
|
||
|
||
|
||
### **② 热门产品榜（Top10）**
|
||
|
||
图表：Bar Chart（水平条形）
|
||
维度：`title`
|
||
指标：`sold`
|
||
排序：DESC
|
||
|
||
### **③ 热门类目占比（Pie / Donut）**
|
||
|
||
维度：`category`
|
||
指标：`count(*)`
|
||
|
||
快速识别高需求类目。
|
||
|
||
### **④ 销量 vs 价格 气泡图**
|
||
|
||
图表：Scatter Plot
|
||
|
||
- X：final_price
|
||
|
||
- Y：sold
|
||
|
||
- 气泡大小：rating
|
||
|
||
- 气泡颜色：category
|
||
|
||
|
||
作用：
|
||
|
||
- 一眼识别“低价高销量类”
|
||
|
||
- 一眼识别“高客单价爆品”
|
||
|
||
|
||
### **⑤ 产品评分分布直方图**
|
||
|
||
图表：Histogram
|
||
字段：rating
|
||
分析整体产品质量分布。
|
||
|
||
---
|
||
|
||
# **📌 Tab 2：类目机会洞察（Category Analytics）**
|
||
|
||
核心目标：**识别值得进入的新类目**。
|
||
|
||
### **① 类目热度榜**
|
||
|
||
- 维度：category
|
||
|
||
- 指标：SUM(sold)
|
||
|
||
|
||
### **② 类目评分平均值 vs 销量**
|
||
|
||
图表：Heatmap
|
||
|
||
- 行：category
|
||
|
||
- 列：rating（分箱）
|
||
|
||
- 值：sold
|
||
|
||
|
||
### **③ 类目价格带分布（箱线图）**
|
||
|
||
图表：Box Plot
|
||
维度：category
|
||
指标：final_price
|
||
|
||
用途：
|
||
找出“利润空间大但竞争低”的类目。
|
||
|
||
---
|
||
|
||
# **📌 Tab 3：店铺监控（Store Performance）**
|
||
|
||
当你分析竞争对手时特别有用。
|
||
|
||
### **① 店铺销量排名**
|
||
|
||
维度：store_name
|
||
指标：SUM(sold)
|
||
|
||
### **② 店铺平均评分**
|
||
|
||
图表：Bar Chart
|
||
维度：store_name
|
||
指标：AVG(rating)
|
||
|
||
### **③ 店铺产品价格分布（箱线图）**
|
||
|
||
### **④ 店铺上新趋势**
|
||
|
||
图表：Time-series Area
|
||
维度：timestamp
|
||
指标：count(product_id)
|
||
|
||
---
|
||
|
||
# **📌 Tab 4：评论与用户反馈分析**
|
||
|
||
如果你以后做 NLP，可以进一步增强，这里给出基础方案。
|
||
|
||
### **① 评分趋势折线图（来自 product_reviews）**
|
||
|
||
- 时间：review_date
|
||
|
||
- 指标：avg(rating)
|
||
|
||
- 过滤：按产品
|
||
|
||
|
||
### **② 产品评论数量 vs 销量 散点图**
|
||
|
||
X：rating_count
|
||
Y：sold
|
||
过滤器：category
|
||
|
||
### **③ 好评/差评占比**
|
||
|
||
如果 rating_count 提取出来，可以算：
|
||
|
||
```sql
|
||
SUM(CASE WHEN rating >= 4 THEN 1 END) / COUNT(*)
|
||
```
|
||
|
||
---
|
||
|
||
# 5. Superset 中的交互过滤器（强烈推荐）
|
||
|
||
放在顶部：
|
||
|
||
- **Category**（选择类目）
|
||
|
||
- **Store Name**
|
||
|
||
- **价格范围**（Slider）
|
||
|
||
- **时间范围**（timestamp）
|
||
|
||
- **分页选择（Top N）**
|
||
|
||
- **评分范围**
|
||
|
||
|
||
这样整个 Dashboard 变成一个动态选品系统。
|
||
|
||
---
|
||
|
||
# 6. 高阶玩法（选品策略自动化）
|
||
|
||
如果你愿意，我可以帮你写 SQL 自动生成“推荐选品列表”。
|
||
|
||
例如选品评分（适合作为一个表格 Chart）：
|
||
|
||
```sql
|
||
SELECT
|
||
id,
|
||
title,
|
||
category,
|
||
final_price,
|
||
sold,
|
||
rating,
|
||
rating_count,
|
||
discount_percent,
|
||
(
|
||
sold * 0.4 +
|
||
COALESCE(rating, 0) * 15 +
|
||
COALESCE(discount_percent, 0) * 0.5 +
|
||
rating_count * 0.2
|
||
) AS score
|
||
FROM view_products_cleaned
|
||
ORDER BY score DESC
|
||
LIMIT 50;
|
||
```
|
||
|
||
你可以自定义权重。
|
||
|
||
---
|
||
|
||
# 7. Dashboard 布局建议
|
||
|
||
按照 Superset 12yo+ 企业级布局习惯：
|
||
|
||
### **第一行**
|
||
|
||
- KPI 卡片（4–6 个）
|
||
|
||
|
||
### **第二行（两列）**
|
||
|
||
- 左：销量 TOP10
|
||
|
||
- 右：价格 vs 销量（散点图）
|
||
|
||
|
||
### **第三行**
|
||
|
||
- 类目占比（甜甜圈图）
|
||
|
||
|
||
### **第四行（两列）**
|
||
|
||
- 类目价格分布（箱线图）
|
||
|
||
- 类目评分热力图
|
||
|
||
|
||
### **第五行**
|
||
|
||
- 产品选品评分排行榜（表格）
|
||
|
||
|
||
你是做 SaaS 和 Cloud Delivery 的，这种布局非常适合长期运营。
|
||
|
||
---
|
||
|
||
# 8. 如果你愿意，我可以直接为你生成：
|
||
|
||
✅ Superset SQL View
|
||
✅ Superset Dashboard JSON（可以直接“Import Dashboard”）
|
||
✅ 各个图表的配置 JSON
|
||
✅ 覆盖你的业务逻辑（爆品模型、利润模型、库存模型）
|
||
只需要你告诉我：
|
||
|
||
### 你更倾向：
|
||
|
||
A. “电商选品分析” 全套 Dashboard？
|
||
B. “竞争对手监控” Dashboard？
|
||
C. “评论质量分析” Dashboard？
|
||
D. “视频带货表现分析”（基于 videos）？
|
||
|
||
我可以直接给你 Superset 可导入的完整 dashboard 文件（JSON）。
|
||
|
||
很好，你选择了 **A. 电商选品分析 Dashboard** + **B. 竞争对手监控 Dashboard**。
|
||
下面我会给出一套**专业级、完整可落地**的 Superset 方案，包含：
|
||
|
||
1. **总体结构（两个大 Dashboard）**
|
||
|
||
2. **每个 Dashboard 的图表清单**
|
||
|
||
3. **SQL View（用于 Superset Dataset）**
|
||
|
||
4. **推荐的布局结构**
|
||
|
||
5. **可选：我可以帮你自动生成可直接导入 Superset 的 Dashboard JSON**
|
||
|
||
|
||
你只需要告诉我你希望我生成 **单个 Dashboard 还是两个 Dashboard 的 JSON**。
|
||
|
||
---
|
||
|
||
# ------------------------------------
|
||
|
||
# 🟦 PART 1：电商选品分析 Dashboard（核心）
|
||
|
||
# ------------------------------------
|
||
|
||
该 Dashboard 的目标：
|
||
|
||
> 用于快速发现 TikTok Shop 上的「爆品」、「高利润」、「高评分」、「低竞争」、「价格带机会」。
|
||
|
||
---
|
||
|
||
# 1. 数据集准备（SQL View）
|
||
|
||
为了 Superset 图表更好用，你需要创建以下 View（只需要一次）。
|
||
|
||
## **① view_products_cleaned**
|
||
|
||
```sql
|
||
CREATE OR REPLACE VIEW view_products_cleaned AS
|
||
SELECT
|
||
id,
|
||
source_id,
|
||
title,
|
||
store_name,
|
||
category,
|
||
final_price,
|
||
initial_price,
|
||
discount_percent,
|
||
sold,
|
||
position,
|
||
timestamp,
|
||
JSON_EXTRACT(prodct_rating, '$.rating') AS rating,
|
||
JSON_EXTRACT(prodct_rating, '$.count') AS rating_count,
|
||
(final_price * sold) AS total_gmv,
|
||
(initial_price - final_price) AS discount_amount
|
||
FROM products;
|
||
```
|
||
|
||
**理由：**
|
||
|
||
- `rating`、`rating_count` 提出来便于做 Heatmap / Ranking
|
||
|
||
- `discount_amount` 用于看促销带货效果
|
||
|
||
- `total_gmv` 用于 GMV 排名
|
||
|
||
|
||
---
|
||
|
||
# 2. Dashboard 图表设计（25~30 个）
|
||
|
||
## **📌 第一部分：KPI 总览（最顶层，一眼看爆品）**
|
||
|
||
**指标卡：**
|
||
|
||
- 总产品数
|
||
|
||
- 热卖产品数（sold > X）
|
||
|
||
- 平均评分
|
||
|
||
- 平均最终价格
|
||
|
||
- 总 GMV
|
||
|
||
- 平均折扣比例
|
||
|
||
|
||
---
|
||
|
||
## **📌 第二部分：核心爆品分析**
|
||
|
||
### **图表 1：爆品榜（TOP 20）**
|
||
|
||
- 图表类型：Bar Chart
|
||
|
||
- 指标：`sold`
|
||
|
||
- 维度：`title`（限制展示 20 个）
|
||
|
||
|
||
### **图表 2：GMV 榜（TOP 20）**
|
||
|
||
指标：`total_gmv`
|
||
|
||
### **图表 3：评分高但销量低（潜力品）**
|
||
|
||
- 类型：Scatter
|
||
|
||
- X：rating
|
||
|
||
- Y：sold
|
||
|
||
- Size：rating_count
|
||
|
||
|
||
用于发现“评分优秀但销量没爆发”的机会。
|
||
|
||
---
|
||
|
||
## **📌 第三部分：价格带与销量关系**
|
||
|
||
### **图表 4：价格 vs 销量 气泡图**
|
||
|
||
- 类型：Scatter
|
||
|
||
- X：final_price
|
||
|
||
- Y：sold
|
||
|
||
- Size：rating_count
|
||
|
||
- Color：category
|
||
|
||
|
||
### **图表 5：价格带销量分布（直方图）**
|
||
|
||
- 类型：Histogram
|
||
|
||
- 字段：final_price 分箱
|
||
|
||
- 指标：count(*)
|
||
|
||
|
||
---
|
||
|
||
## **📌 第四部分：类目机会分析**
|
||
|
||
### **图表 6：类目销量榜**
|
||
|
||
维度：category
|
||
指标：SUM(sold)
|
||
|
||
### **图表 7：类目价格箱线图**
|
||
|
||
分析每个类目的价格带。
|
||
|
||
### **图表 8：类目评分热力图**
|
||
|
||
- 维度：category × rating
|
||
|
||
- 值：COUNT(*)
|
||
|
||
|
||
### **图表 9：类目竞争度分析**
|
||
|
||
SQL（示例）：
|
||
|
||
```sql
|
||
SELECT
|
||
category,
|
||
COUNT(*) AS product_count,
|
||
SUM(sold) AS total_sold,
|
||
AVG(rating) AS avg_rating
|
||
FROM view_products_cleaned
|
||
GROUP BY category;
|
||
```
|
||
|
||
在 Superset 使用 Table + Conditional Formatting
|
||
→ 找出“产品少但销量大”的类目（典型蓝海）。
|
||
|
||
---
|
||
|
||
## **📌 第五部分：选品评分模型（自动推荐产品）**
|
||
|
||
创建一个可排序表格 Chart：
|
||
|
||
### **SQL：选品评分模型**
|
||
|
||
```sql
|
||
SELECT
|
||
id,
|
||
title,
|
||
category,
|
||
final_price,
|
||
sold,
|
||
rating,
|
||
rating_count,
|
||
discount_percent,
|
||
(
|
||
sold * 0.4 +
|
||
COALESCE(rating, 0) * 12 +
|
||
rating_count * 0.2 +
|
||
COALESCE(discount_percent, 0) * 0.5
|
||
) AS score
|
||
FROM view_products_cleaned
|
||
ORDER BY score DESC
|
||
LIMIT 100;
|
||
```
|
||
|
||
**这是选品最核心图表之一。**
|
||
|
||
---
|
||
|
||
# ------------------------------------
|
||
|
||
# 🟦 PART 2：竞争对手监控 Dashboard
|
||
|
||
# ------------------------------------
|
||
|
||
目标：
|
||
|
||
> 跟踪“目标店铺 + 竞争对手”销量、价格策略、上新节奏、评分趋势。
|
||
|
||
适合追踪 3–10 个关注店铺。
|
||
|
||
---
|
||
|
||
# 1. 顶部过滤器
|
||
|
||
- Store Name（支持多选）
|
||
|
||
- Category（可选）
|
||
|
||
- 时间范围（timestamp）
|
||
|
||
|
||
---
|
||
|
||
# 2. 核心图表设计
|
||
|
||
## **📌 店铺整体表现**
|
||
|
||
### **图表 1：店铺 GMV 排名**
|
||
|
||
维度：store_name
|
||
指标：SUM(total_gmv)
|
||
|
||
### **图表 2：店铺销量排名**
|
||
|
||
指标：SUM(sold)
|
||
|
||
### **图表 3：店铺平均评分排行**
|
||
|
||
---
|
||
|
||
## **📌 店铺产品结构分析**
|
||
|
||
### **图表：店铺产品价格分布（箱线图）**
|
||
|
||
维度：store_name
|
||
指标：final_price
|
||
|
||
### **图表：店铺类目构成饼图**
|
||
|
||
维度：category
|
||
指标：count(*)
|
||
Filter：store_name=某店铺
|
||
|
||
---
|
||
|
||
## **📌 店铺上新节奏（重点）**
|
||
|
||
你已经有字段：`timestamp`（抓取时间）
|
||
|
||
可以做趋势图：
|
||
|
||
### **图表：上新数量趋势（Time-series Bar）**
|
||
|
||
- 时间：timestamp
|
||
|
||
- 指标：count(id)
|
||
|
||
- 过滤：store_name
|
||
|
||
|
||
这能判断：
|
||
|
||
- 哪家店铺最近疯狂上新
|
||
|
||
- 是否在做活动冲 GMV
|
||
|
||
|
||
---
|
||
|
||
## **📌 价格策略对比**
|
||
|
||
### **图表：店铺最终价格均值趋势**
|
||
|
||
- 时间：timestamp
|
||
|
||
- 指标：avg(final_price)
|
||
|
||
- 按 store_name 分组
|
||
|
||
|
||
用于分析成本、价格策略变化。
|
||
|
||
---
|
||
|
||
## **📌 店铺爆品分析**
|
||
|
||
### **图表：每家店铺的爆品 TOP 10**
|
||
|
||
SQL 示例：
|
||
|
||
```sql
|
||
SELECT
|
||
store_name,
|
||
title,
|
||
sold,
|
||
final_price,
|
||
rating
|
||
FROM view_products_cleaned
|
||
ORDER BY sold DESC
|
||
LIMIT 10;
|
||
```
|
||
|
||
这种表格适合分析竞争对手的主推商品。
|
||
|
||
---
|
||
|
||
# ------------------------------------
|
||
|
||
# 🟦 PART 3：Dashboard 布局结构
|
||
|
||
# ------------------------------------
|
||
|
||
---
|
||
|
||
# 🟩 电商选品分析 Dashboard 布局
|
||
|
||
### **第一行：KPI**
|
||
|
||
6–10 个 KPI 卡片（两行）
|
||
|
||
### **第二行：爆品榜 + GMV 榜**
|
||
|
||
2 列布局
|
||
|
||
### **第三行：价格 vs 销量（气泡图）**
|
||
|
||
1 列大图
|
||
|
||
### **第四行：类目分析（3 图）**
|
||
|
||
- 左：类目销量榜
|
||
|
||
- 中：类目评分热力图
|
||
|
||
- 右：类目价格箱线图
|
||
|
||
|
||
### **第五行：选品评分模型（表格）**
|
||
|
||
全宽
|
||
|
||
---
|
||
|
||
# 🟩 竞争对手监控 Dashboard 布局
|
||
|
||
### 第一行：过滤器
|
||
|
||
### 第二行：KPI（店铺数量、总销量、平均评分）
|
||
|
||
### 第三行：店铺表现（GMV / 销量 / 评分）
|
||
|
||
三张 Bar Chart
|
||
|
||
### 第四行：店铺上新趋势
|
||
|
||
Time-series Area
|
||
|
||
### 第五行：店铺类目结构（饼图 + 表格）
|
||
|
||
### 第六行：爆品榜（按店铺分组）
|
||
|
||
---
|
||
|
||
# ------------------------------------
|
||
|
||
# 🟦 PART 4：下一步（我可以自动为你生成 Dashboard JSON）
|
||
|
||
# ------------------------------------
|
||
|
||
Superset 支持将整个 Dashboard 导出成 JSON，因此我可以为你：
|
||
|
||
### **✓ 自动生成：两个 Dashboard 的 JSON 文件**
|
||
|
||
你可以直接在 Superset 中：
|
||
|
||
**Settings → Import Dashboard → 选择 JSON**
|
||
即可一键导入完整成品 Dashboard。
|
||
|
||
包含完整的：
|
||
|
||
- 每个图表
|
||
|
||
- 每个字段映射
|
||
|
||
- 每个 SQL
|
||
|
||
- 布局
|
||
|
||
- Filter Box
|
||
|
||
- 控制面板配置
|
||
|
||
|
||
就像企业 BI 交付一样开箱即用。
|
||
|
||
---
|
||
|
||
# ❓ 请告诉我下面的问题（非常重要）
|
||
|
||
## 1. 你希望我生成：
|
||
|
||
- **单个 Dashboard（综合选品 + 竞争分析）**
|
||
还是
|
||
|
||
- **两个 Dashboard（选品分析 / 竞争对手监控）分开？**
|
||
|
||
|
||
## 2. 你现在使用的 Superset 版本是？（例如：2.1、3.0 等）
|
||
|
||
不同版本 JSON 格式略有差异。
|
||
|
||
## 3. 你的业务偏好：
|
||
|
||
- 偏向“百货类”、“电子产品”，还是“美妆类”？
|
||
（会影响哪些字段更重要）
|
||
|
||
|
||
---
|
||
|
||
回答这 3 点，我就可以直接给你 **可导入的 Superset Dashboard JSON**（强烈推荐）。 |