基于R与SQL的分版本应用留存/流失率可视化技术问询

看来你已经搞定了多次使用用户的唯一识别工作，接下来聚焦版本维度做月度留存/流失的可视化其实很清晰，我结合R和SQL给你一套可直接复用的方案：

一、先把数据整理成可视化需要的格式 #

因为你已经拿到了目标用户集合，接下来要做的就是按应用版本和留存月份统计对应的留存、流失百分比。这里分两种场景处理：

1. 数据库端用SQL聚合数据（如果数据集存在数据库里） #

假设你的数据集包含user_id（用户ID）、app_version（应用版本）、first_login_time（首次登录时间）、login_time（每次登录时间），可以用CTE分层计算：

WITH user_first_login AS (
    -- 锁定你已经识别的多次使用的唯一用户，提取他们的首次登录月份和版本
    SELECT 
        user_id,
        app_version,
        DATE_TRUNC('month', first_login_time) AS first_login_month
    FROM your_dataset
    WHERE user_id IN (SELECT DISTINCT user_id FROM your_dataset WHERE login_count > 1)
),
user_monthly_activity AS (
    -- 关联用户的所有登录记录，计算每个用户的留存月份差（比如首次登录1月，2月登录就是1个月留存）
    SELECT 
        ufl.user_id,
        ufl.app_version,
        ufl.first_login_month,
        DATE_TRUNC('month', ula.login_time) AS login_month,
        EXTRACT(MONTH FROM AGE(ula.login_time, ufl.first_login_time)) AS retention_month
    FROM user_first_login ufl
    JOIN your_dataset ula ON ufl.user_id = ula.user_id
)
-- 最终聚合出每个版本、每个首次登录月的各留存阶段的留存率/流失率
SELECT 
    app_version,
    first_login_month,
    retention_month,
    COUNT(DISTINCT user_id) AS retained_users,
    -- 用窗口函数取首次登录当月的用户数作为基数
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY app_version, first_login_month ORDER BY retention_month) AS total_new_users,
    ROUND(COUNT(DISTINCT user_id)::FLOAT / total_new_users * 100, 2) AS retention_rate,
    ROUND((1 - COUNT(DISTINCT user_id)::FLOAT / total_new_users) * 100, 2) AS churn_rate
FROM user_monthly_activity
GROUP BY app_version, first_login_month, retention_month
ORDER BY app_version, first_login_month, retention_month;

2. R端用dplyr整理数据（如果数据已经导入R环境） #

如果已经把数据拉到R里，用dplyr和lubridate处理更灵活：

library(dplyr)
library(lubridate)

# 假设你的原始数据框是user_data，unique_users_list是你之前识别的唯一用户集合
cleaned_data <- user_data %>%
  filter(user_id %in% unique_users_list) %>%
  mutate(
    first_login_month = floor_date(first_login_time, "month"), # 把时间转成月份粒度
    login_month = floor_date(login_time, "month"),
    retention_month = interval(first_login_month, login_month) %/% months(1) # 计算留存月数差
  ) %>%
  group_by(app_version, first_login_month, retention_month) %>%
  summarise(retained_users = n_distinct(user_id), .groups = "drop") %>%
  group_by(app_version, first_login_month) %>%
  mutate(
    total_new_users = first(retained_users), # 首次登录当月的用户数作为基数
    retention_rate = round((retained_users / total_new_users) * 100, 2),
    churn_rate = round((1 - retained_users / total_new_users) * 100, 2)
  ) %>%
  ungroup()

二、分版本可视化实现（用ggplot2） #

接下来制作聚焦版本的图表，这里给你两种常用的样式，按需选择：

1. 多版本留存率对比折线图 #

适合直观对比不同版本的留存趋势：

library(ggplot2)

ggplot(cleaned_data, aes(x = retention_month, y = retention_rate, color = app_version, group = app_version)) +
  geom_line(linewidth = 1.2) + # 加粗折线，提升可读性
  geom_point(size = 3) + # 标注每个数据点
  labs(
    title = "各版本应用月度用户留存率",
    x = "留存月份（距首次登录月数）",
    y = "留存率 (%)",
    color = "应用版本"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    axis.title = element_text(size = 12),
    legend.position = "bottom" # 把图例放底部，避免遮挡图表
  ) +
  scale_x_continuous(breaks = unique(cleaned_data$retention_month)) # 显示所有留存月份的刻度

2. 分版本的留存&流失组合图表 #

如果需要同时展示留存和流失的对比，用分面+长格式数据实现：

# 先把宽格式数据转成长格式，方便同时展示两个指标
long_data <- cleaned_data %>%
  select(app_version, first_login_month, retention_month, retention_rate, churn_rate) %>%
  pivot_longer(cols = c(retention_rate, churn_rate), names_to = "metric", values_to = "percentage")

ggplot(long_data, aes(x = retention_month, y = percentage, color = metric, group = interaction(app_version, metric))) +
  geom_line(linewidth = 1) +
  geom_point(size = 2) +
  facet_wrap(~app_version) + # 每个版本单独成图，避免拥挤
  labs(
    title = "各版本应用月度留存率与流失率",
    x = "留存月份（距首次登录月数）",
    y = "百分比 (%)",
    color = "指标类型"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    axis.title = element_text(size = 12),
    legend.position = "bottom"
  ) +
  scale_color_manual(values = c("retention_rate" = "#2ECC71", "churn_rate" = "#E74C3C")) # 用绿色和红色区分留存/流失

三、几个实用的优化细节 #

• 版本简化：如果版本太多（比如有十几个小版本），可以合并大版本（比如把v1.1、v1.2合并成v1），避免图表过于杂乱。
• 数据过滤：过滤掉数据不足的月份（比如首次登录当月之后不足3个月的，留存数据可能不完整）。
• 关键数据标注：用geom_text()在图表上标注核心节点（比如30天、90天留存率），让报告更直观。
• 高清导出：用ggsave()导出高清图表，适合放进分析报告：

  ggsave("版本留存流失分析图.png", plot = last_plot(), width = 12, height = 8, dpi = 300)
  

内容的提问来源于stack exchange，提问作者J_p