类目与商品仪表盘

目标

回答“哪些类目贡献流量和购买、哪些类目有结构机会、商品长尾是否明显”。

建议仪表盘名称：

LZ-组6_类目与商品

图表1：类目浏览 Top20

保存名称：

排行-类目浏览Top20

可视化：Bar

SELECT
  category_id AS `类目ID`,
  pv_count AS `浏览量`,
  buy_count AS `购买行为数`,
  pv_to_buy_user_rate AS `浏览到购买用户转化率`
FROM category_metrics
ORDER BY pv_count DESC
LIMIT 20;

图表2：类目购买 Top20

保存名称：

排行-类目购买Top20

可视化：Bar

SELECT
  category_id AS `类目ID`,
  buy_count AS `购买行为数`,
  pv_count AS `浏览量`,
  sell_through_rate AS `动销率`
FROM category_metrics
ORDER BY buy_count DESC
LIMIT 20;

图表3：类目转化率 Top20

保存名称：

排行-类目转化率Top20

可视化：Bar

SELECT
  category_id AS `类目ID`,
  pv_users AS `浏览用户数`,
  buy_users AS `购买用户数`,
  pv_to_buy_user_rate AS `浏览到购买用户转化率`
FROM category_metrics
WHERE pv_users >= 1000
ORDER BY pv_to_buy_user_rate DESC
LIMIT 20;

说明：增加 pv_users >= 1000 是为了避免极小样本类目的转化率误导。

图表4：流量份额与购买份额差异

保存名称：

散点-类目流量购买份额

可视化：Scatter

X轴：浏览份额

Y轴：购买份额

SELECT
  category_id AS `类目ID`,
  pv_share AS `浏览份额`,
  buy_share AS `购买份额`,
  traffic_purchase_gap AS `流量购买差异`,
  opportunity_type AS `机会类型`,
  pv_to_buy_user_rate AS `浏览到购买用户转化率`,
  sell_through_rate AS `动销率`
FROM category_opportunity_metrics;

表格1：类目机会清单

保存名称：

表格-类目机会清单

可视化：Table

SELECT
  category_id AS `类目ID`,
  pv_rank AS `浏览排名`,
  buy_rank AS `购买排名`,
  pv_share AS `浏览份额`,
  buy_share AS `购买份额`,
  traffic_purchase_gap AS `流量购买差异`,
  structure_quadrant AS `结构象限`,
  opportunity_type AS `机会类型`,
  pv_to_buy_user_rate AS `浏览到购买用户转化率`,
  sell_through_rate AS `动销率`
FROM category_opportunity_metrics
ORDER BY ABS(traffic_purchase_gap) DESC
LIMIT 50;

图表5：商品 ABC 长尾分布

保存名称：

饼图-商品ABC长尾分布

可视化：Pie

SELECT
  abc_class AS `ABC分层`,
  COUNT(*) AS `商品数`,
  SUM(pv_count) AS `浏览量`
FROM item_long_tail_metrics
GROUP BY abc_class
ORDER BY abc_class;

表格2：高浏览未购买商品

保存名称：

表格-高浏览未购买商品

可视化：Table

SELECT
  item_id AS `商品ID`,
  category_id AS `类目ID`,
  pv_count AS `浏览量`,
  cart_count AS `加购量`,
  buy_count AS `购买行为数`,
  pv_users AS `浏览用户数`,
  buy_users AS `购买用户数`,
  pv_to_buy_user_rate AS `浏览到购买用户转化率`
FROM item_metrics
WHERE has_exposure = 1
  AND has_buy = 0
ORDER BY pv_count DESC
LIMIT 100;

表格3：商品长尾 Top100

保存名称：

表格-商品长尾Top100

可视化：Table

SELECT
  item_id AS `商品ID`,
  category_id AS `类目ID`,
  pv_count AS `浏览量`,
  buy_count AS `购买行为数`,
  pv_rank AS `浏览排名`,
  pv_share AS `浏览份额`,
  cumulative_pv_share AS `累计浏览份额`,
  abc_class AS `ABC分层`,
  item_status AS `商品状态`
FROM item_long_tail_metrics
ORDER BY pv_rank
LIMIT 100;