hive练习题（二）计算用户的复购率和回购率

2024-03-31 05:53| 来源: 网络整理| 查看: 265

数据表ods_sales_orders前10行在这里插入图片描述

复购率

定义：在某时间窗口内重复消费用户（消费两次及以上的用户）在总消费用户中占比

例如，按月作为统计窗口，当前月份购买2次及以上的客户占当前月份所有客户的比例。

第一步：计算每位客户在每月的消费次数 create table temp1 as SELECT customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth, COUNT(customer_key) AS con FROM ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM'); 第二步：对消费2次及以上的客户进行统计，并计算复购率 SELECT umonth, SUM(IF(con>1,1,0))AS repurchase_con,COUNT(1) AS customs_con, concat(round(SUM(IF(con>1,1,0))/COUNT(1)*100,2),'%') AS repurchase_ratio FROM temp1 GROUP BY umonth;

在这里插入图片描述

回购率

定义：是某一个时间窗口内消费的用户，在下一个时间窗口仍旧消费的占比。

例如，按月作为统计窗口,1月份1000人购买了该产品，在2月份这1000人中的300人继续购买，则称1月份的回购率为30%。

第一步：将本月用户的消费情况和下月用户的消费情况做连接，连接方式采用左连接

涉及知识点：concat函数，add_months函数

select * from (SELECT customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth FROM ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM')) as a LEFT JOIN (SELECT customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth FROM ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM')) as b ON a.customer_key = b.customer_key AND CONCAT(a.umonth,'-01')=add_months(CONCAT(b.umonth,'-01'),-1); 第二步：计算本月和下月消费用户数以及相应的回购率 SELECT a.umonth,COUNT(a.customer_key) AS now_count, COUNT(b.customer_key)AS next_count, CONCAT(ROUND(COUNT(b.customer_key)/COUNT(a.customer_key)*100,2),'%')AS ratio FROM (SELECT customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth FROM ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM'))a LEFT JOIN (SELECT customer_key,DATE_FORMAT(create_date,'YYYY-MM') AS umonth FROM ods_sales_orders GROUP BY customer_key,DATE_FORMAT(create_date,'YYYY-MM'))b ON a.customer_key = b.customer_key AND CONCAT(a.umonth,'-01')=add_months(CONCAT(b.umonth,'-01'),-1) GROUP BY a.umonth;

在这里插入图片描述

【本文地址】

公司简介

联系我们