用户与转化仪表盘

目标

回答“用户如何分层、复购是否值得关注、行为漏斗和路径中哪里流失严重”。

建议仪表盘名称：

LZ-组6_用户与转化

图表1：用户购买频次分层

保存名称：

柱状-用户分层行为量

可视化：Bar

SELECT
  CASE segment
    WHEN 'non_buyer' THEN '未购买用户'
    WHEN 'single_day_buyer' THEN '单日购买用户'
    WHEN 'repeat_day_buyer' THEN '跨日复购用户'
    WHEN 'high_frequency_buyer' THEN '高频购买用户'
    ELSE segment
  END AS `用户分层`,
  user_count AS `用户数`,
  pv_count AS `浏览量`,
  cart_count AS `加购量`,
  buy_count AS `购买行为数`,
  user_share AS `用户占比`,
  buy_share AS `购买占比`
FROM user_segment
ORDER BY user_count DESC;

图表2：RF 分层用户数

保存名称：

柱状-RF分层用户数

可视化：Bar

SELECT
  rf_segment AS `RF分层`,
  segment_rule AS `分层规则`,
  user_count AS `用户数`,
  buy_count AS `购买行为数`,
  avg_recency_days AS `平均最近购买天数`,
  avg_buy_days AS `平均购买天数`,
  buyer_user_share AS `购买用户占比`
FROM rf_segment
ORDER BY user_count DESC;

卡片1：复购率

保存名称：

KPI-跨日复购率

可视化：Number，格式为百分比。

SELECT
  repeat_day_buyer_rate AS `跨日复购率`
FROM repurchase_metrics
WHERE scope = 'all';

卡片2：多次购买行为用户占比

保存名称：

KPI-多次购买行为用户占比

可视化：Number，格式为百分比。

SELECT
  multiple_buy_event_user_rate AS `多次购买行为用户占比`
FROM repurchase_metrics
WHERE scope = 'all';

图表3：Cohort 留存趋势

保存名称：

趋势-Cohort留存

可视化：Line

SELECT
  retention_type AS `留存类型`,
  cohort_date AS `队列日期`,
  day_number AS `第N天`,
  cohort_users AS `队列用户数`,
  retained_users AS `留存用户数`,
  retention_rate AS `留存率`
FROM cohort_retention_metrics
WHERE is_observable = 1
ORDER BY cohort_date, day_number;

图表4：行为渗透漏斗

保存名称：

漏斗-行为渗透

可视化：Funnel

阶段字段：阶段

数值字段：用户数

SELECT
  CASE stage
    WHEN 'pv' THEN '浏览'
    WHEN 'fav' THEN '收藏'
    WHEN 'cart' THEN '加购'
    WHEN 'buy' THEN '购买'
    ELSE stage
  END AS `阶段`,
  stage_order AS `阶段顺序`,
  user_count AS `用户数`,
  event_count AS `行为次数`,
  first_stage_rate AS `相对浏览用户比例`,
  note AS `说明`
FROM funnel_metrics
WHERE funnel_type = 'behavior_penetration'
ORDER BY stage_order;

说明：这是行为渗透漏斗，不是严格线性漏斗。收藏和加购是并行意向行为。

图表5：严格路径转化率

保存名称：

柱状-严格路径转化率

可视化：Bar

SELECT
  path_type AS `路径类型`,
  entity_type AS `实体类型`,
  eligible_pairs AS `可观察对象数`,
  converted_pairs AS `转化对象数`,
  conversion_rate AS `转化率`,
  path_rule AS `路径规则`
FROM path_metrics
ORDER BY conversion_rate DESC;

表格1：意向后未购买

保存名称：

表格-意向后未购买

可视化：Table

SELECT
  intent_type AS `意向类型`,
  eligible_pairs AS `意向对象数`,
  converted_pairs AS `已购买对象数`,
  not_converted_pairs AS `未购买对象数`,
  conversion_rate AS `购买转化率`,
  not_converted_rate AS `未购买率`,
  definition AS `定义`
FROM intent_not_buy_metrics
ORDER BY not_converted_rate DESC;

表格2：转化耗时

保存名称：

表格-转化耗时

可视化：Table

SELECT
  path_type AS `路径类型`,
  converted_pairs AS `转化对象数`,
  avg_duration_minutes AS `平均耗时分钟`,
  median_duration_minutes AS `中位耗时分钟`,
  p75_duration_minutes AS `P75耗时分钟`,
  p90_duration_minutes AS `P90耗时分钟`,
  limitation AS `局限说明`
FROM conversion_duration_metrics
ORDER BY avg_duration_minutes;

图表6：用户生命周期分布

保存名称：

柱状-用户生命周期分布

可视化：Bar

SELECT
  activity_level AS `活跃层级`,
  purchase_lifecycle AS `购买生命周期`,
  user_count AS `用户数`,
  pv_count AS `浏览量`,
  cart_count AS `加购量`,
  buy_count AS `购买行为数`,
  avg_active_days AS `平均活跃天数`,
  user_share AS `用户占比`
FROM user_lifecycle_metrics
ORDER BY user_count DESC;