Metabase活跃留存率计算

日新增和留存率

select *,
 concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率
from (
 select 
  date(a.create_time) as 日期,
  count(distinct a.id) as 日新增用户数,
  count(distinct b.uid) as 次日留存用户,
  count(distinct c.uid) as 三日留存用户,
  count(distinct d.uid) as 七日留存用户
 from (
		SELECT
			id,
			os,
			create_time
		FROM
			user_register_msg #用户注册表
		WHERE {{os}} #渠道 如iOS Android
			AND date(create_time ) BETWEEN {{start_time}} AND {{end_time}}
		) AS  a 
	LEFT JOIN user_active_log #用户活跃表 b ON a.id = b.uid 
		AND a.os = b.os 
		AND DATEDIFF( b.create_time, a.create_time ) = 1
	LEFT JOIN user_active_log c ON a.id = c.uid 
		AND a.os = c.os 
	    AND DATEDIFF( c.create_time, a.create_time ) = 3
	LEFT JOIN user_active_log d ON a.id = d.uid 
		AND a.os = d.os 
		AND DATEDIFF(d.create_time, a.create_time ) = 7
 group by date(a.create_time)
) p;

可制作成组合图趋势图

日活跃

SELECT date(l.create_time) AS `时间`,
    count(distinct l.`uid`) AS `登录用户数`,
    count(distinct l.device_id) AS `登录设备数`,
    count(distinct l.`uid`) - count(distinct r.`id`) AS `老用户登录数`, #日活跃uid数据-今日注册uid
    count(*) AS `登录次数`,
    count(distinct l.uid,if(l.os='iOS',true,null)) as '用户iOS数量',
    count(distinct l.uid,if(l.os='Android',true,null)) as '用户Android数量'
FROM 
( SELECT
	* 
	FROM
		user_active_log 
	WHERE
	 date(create_time ) BETWEEN {{start_time}} AND {{end_time}}
	) l 
left join user_register_msg r on date(r.create_time ) = date(l.create_time )
and l.uid = r.id AND date(r.create_time ) BETWEEN {{start_time}} AND {{end_time}}
GROUP BY date(l.create_time);

月活跃

SELECT DATE_FORMAT(`create_time`,'%Y-%m') AS `create_time`,
    count(distinct `uid`) AS `登录用户数`,
         count(distinct device_id) AS `登录设备数`,
    count(*) AS `登录次数`,
    count(distinct uid,if(os='iOS',true,null)) as '用户iOS数量',
    count(distinct uid,if(os='Android',true,null)) as '用户Android数量'
FROM user_active_log
WHERE [[{{os}} AND]]
 `create_time` BETWEEN {{start_time}} AND{{end_time}}
GROUP BY DATE_FORMAT(`create_time`,'%Y-%m');

注册渠道

SELECT
	date( a.create_time ) AS 日期,
	count( DISTINCT a.id ) AS 日注册总数,
	count( DISTINCT b.id ) AS 苹果注册数,
	count( DISTINCT c.id ) AS oppo注册数,
	count( DISTINCT d.id ) AS vivo注册数,
	count( DISTINCT e.id ) AS 小米注册数,
	count( DISTINCT f.id ) AS 华为注册数 
FROM
	( SELECT id,create_time FROM user_register_msg WHERE {{os}} AND date( create_time ) BETWEEN {{start_time}} AND {{end_time}} ) AS a
	LEFT JOIN user_register_msg b ON a.id = b.id 
	AND b.os = 'iOS' 
	AND date( b.create_time ) = date( a.create_time )
	LEFT JOIN user_register_msg c ON a.id = c.id 
	AND ( c.model LIKE 'OPPO%' OR c.model LIKE 'OnePlus%' OR c.model LIKE 'realme%' ) 
	AND date( c.create_time ) = date( a.create_time )
	LEFT JOIN user_register_msg d ON a.id = d.id 
	AND ( d.model LIKE 'vivo%' OR d.model LIKE 'IQOO%') 
	AND date( d.create_time )= date( a.create_time )
	LEFT JOIN user_register_msg e ON a.id = e.id 
	AND ( e.model LIKE 'Xiaomi%' OR e.model LIKE 'Redmi%' ) 
	AND date( e.create_time ) = date( a.create_time )
	LEFT JOIN user_register_msg f ON a.id = f.id 
	AND ( f.model LIKE 'HONOR%' OR f.model LIKE 'HUAWEI%' ) 
	AND date( f.create_time ) = date( a.create_time )
GROUP BY
	date(a.create_time)