Hive 常用指标分析

常用指标分析

• 用户留存率
• 路径分析
• 7日品牌复购率
• 7天内连续三天登录
• 每分钟在线人数

  1. 用户留存率

  在使用 Hive 统计用户留存率时，我们通常会涉及到两个主要日期：用户注册日期和用户在后续某天的活跃日期。留存率通常是指用户在注册后的某一天仍然活跃的百分比。

  
  （图片来源网络，侵删）

  假设我们有一个名为 user_activity 的表，这个表包含用户 ID、注册日期和活跃日期。表的结构如下：

  CREATE TABLE user_activity (
      user_id STRING,
      registration_date DATE,
      active_date DATE
  );
  

  我们将分步骤计算用户留存率。假设我们要计算 D+1 留存率（即用户在注册后的第二天是否仍然活跃）。

  
  （图片来源网络，侵删）

  步骤一：计算每个用户的注册日期

  首先，我们获取每个用户的最早注册日期。

  CREATE TABLE user_registration_date AS
  SELECT 
      user_id, 
      MIN(registration_date) AS registration_date
  FROM 
      user_activity
  GROUP BY 
      user_id;
  

  步骤二：计算指定日期的活跃用户数

  接着，我们找出在注册后第二天活跃的用户。我们可以使用 Hive 的日期函数来进行日期加减。

  CREATE TABLE d1_retained_users AS
  SELECT 
      u.user_id, 
      u.registration_date, 
      a.active_date
  FROM 
      user_registration_date u
  JOIN 
      user_activity a 
  ON 
      u.user_id = a.user_id
  WHERE 
      a.active_date = DATE_ADD(u.registration_date, 1);
  

  步骤三：计算留存率

  现在，我们可以计算 D+1 留存率，即在注册后第二天仍然活跃的用户占总注册用户的比例。

  SELECT 
      COUNT(DISTINCT d1.user_id) AS retained_users,
      COUNT(DISTINCT ur.user_id) AS total_users,
      (COUNT(DISTINCT d1.user_id) * 100.0 / COUNT(DISTINCT ur.user_id)) AS retention_rate
  FROM 
      user_registration_date ur
  LEFT JOIN 
      d1_retained_users d1 
  ON 
      ur.user_id = d1.user_id;
  

  步骤四：计算其他天数的留存率

  我们可以使用类似的方法计算其他天数（例如 D+7、D+30）的留存率，只需调整 DATE_ADD 的天数即可。例如，计算 D+7 留存率：

  CREATE TABLE d7_retained_users AS
  SELECT 
      u.user_id, 
      u.registration_date, 
      a.active_date
  FROM 
      user_registration_date u
  JOIN 
      user_activity a 
  ON 
      u.user_id = a.user_id
  WHERE 
      a.active_date = DATE_ADD(u.registration_date, 7);
  

  然后计算 D+7 留存率：

  SELECT 
      COUNT(DISTINCT d7.user_id) AS retained_users,
      COUNT(DISTINCT ur.user_id) AS total_users,
      (COUNT(DISTINCT d7.user_id) * 100.0 / COUNT(DISTINCT ur.user_id)) AS retention_rate
  FROM 
      user_registration_date ur
  LEFT JOIN 
      d7_retained_users d7 
  ON 
      ur.user_id = d7.user_id;
  

  2. 路径分析

  路径分析通常用于分析用户在网站或应用中的行为路径，以了解他们如何到达特定目标或退出。在 Hive 中，可以使用窗口函数和自联接来分析用户路径。假设我们有一个用户行为表 user_activity，包含以下字段：

  CREATE TABLE user_activity (
      user_id STRING,
      activity_time TIMESTAMP,
      page STRING
  );
  

  步骤一：为每个用户的行为排序

  首先，为每个用户的行为按照时间排序，以便我们能够按照用户访问的顺序来分析路径。

  CREATE TABLE user_activity_sorted AS
  SELECT
      user_id,
      activity_time,
      page,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_time) AS rn
  FROM
      user_activity;
  

  步骤二：创建自联接表来获取用户的下一个页面

  然后，我们通过自联接将每个行为与下一个行为进行配对，以便分析用户从一个页面到另一个页面的路径。

  CREATE TABLE user_paths AS
  SELECT
      a.user_id,
      a.page AS current_page,
      b.page AS next_page,
      a.activity_time AS current_time,
      b.activity_time AS next_time
  FROM
      user_activity_sorted a
  LEFT JOIN
      user_activity_sorted b
  ON
      a.user_id = b.user_id
      AND a.rn = b.rn - 1;
  

  步骤三：统计各个路径的频次

  接下来，我们统计每条路径出现的次数，以了解用户最常走的路径。

  CREATE TABLE path_counts AS
  SELECT
      current_page,
      next_page,
      COUNT(*) AS path_count
  FROM
      user_paths
  GROUP BY
      current_page,
      next_page
  ORDER BY
      path_count DESC;
  

  步骤四：分析特定路径

  如果我们想分析特定页面路径的细节，比如从首页（home）到购买页面（purchase）的路径，我们可以筛选出相关数据。

  SELECT
      user_id,
      current_page,
      next_page,
      current_time,
      next_time
  FROM
      user_paths
  WHERE
      current_page = 'home'
      AND next_page = 'purchase';
  

  进一步分析

  我们还可以进一步细化路径分析，比如计算用户在每个页面的停留时间，或者分析特定用户群体的路径行为。

  计算停留时间

  CREATE TABLE user_stay_time AS
  SELECT
      user_id,
      current_page,
      next_page,
      next_time - current_time AS stay_duration
  FROM
      user_paths
  WHERE
      next_time IS NOT NULL;
  

  分析特定用户群体

  假设我们有一个用户表 user_info 包含用户的详细信息（如用户类别），我们可以结合 user_paths 进行分析。

  CREATE TABLE user_info (
      user_id STRING,
      user_category STRING
  );
  CREATE TABLE category_paths AS
  SELECT
      u.user_category,
      p.current_page,
      p.next_page,
      COUNT(*) AS path_count
  FROM
      user_paths p
  JOIN
      user_info u
  ON
      p.user_id = u.user_id
  GROUP BY
      u.user_category,
      p.current_page,
      p.next_page
  ORDER BY
      path_count DESC;
  

  3. 7日品牌复购率

  在 Hive 中计算最近7天的品牌复购率需要几个步骤，包括识别首次购买日期、检查复购行为，并计算复购率。假设我们有一个包含购买记录的表 purchase_log，结构如下：

  CREATE TABLE purchase_log (
      user_id STRING,
      purchase_date DATE,
      brand STRING
  );
  

  步骤一：定义时间窗口

  首先，我们定义一个时间窗口来选择最近7天的记录。假设当前日期为 2024-05-18。

  步骤二：识别首次购买

  接下来，我们找到每个用户对每个品牌在最近7天的首次购买日期。

  CREATE TABLE first_purchase_last_7_days AS
  SELECT
      user_id,
      brand,
      MIN(purchase_date) AS first_purchase_date
  FROM
      purchase_log
  WHERE
      purchase_date BETWEEN DATE_SUB('2024-05-18', 7) AND '2024-05-18'
  GROUP BY
      user_id, brand;
  

  步骤三：识别复购行为

  然后，我们找出这些用户在首次购买后7天内再次购买同一品牌的记录。

  CREATE TABLE repurchase_last_7_days AS
  SELECT
      fp.user_id,
      fp.brand
  FROM
      first_purchase_last_7_days fp
  JOIN
      purchase_log pl
  ON
      fp.user_id = pl.user_id
      AND fp.brand = pl.brand
      AND pl.purchase_date > fp.first_purchase_date
      AND pl.purchase_date