【业务问题】
下面的Excel记录了某款电商产品在1月1日发布，1个月后的新增及留存数据、商品销售数据、商品详情页浏览数据、及商品信息表：
（文末有数据下载）
现在业务部门需要你分析出：
(1)1月5日的DAU是多少？
(2)从留存的角度来看，质量最高的新增用户来自哪一天？
(3)在1月15日当天，SKU销售激活率是多少？
(4)商品“品类T582”的详情页购买转化率在哪天最高？
(5)1月10日当天的ARPU值是多少？
【分析思路】
1. 1月5日的DAU是多少？
DAU是日活跃用户数。定义指标日活跃用户数=当日新增的用户数+之前每日的留存老用户数。
求1月5日的日活跃用户数=1月5日当日新增用户数+1月5日前每一日新增用户在1月5日的留存用户。
(1)1月5日当日新增用户数
在“新增及留存”表格中，可直接得1月5日当日新增用户数是6680。
(2)1月5日前每一日新增用户在1月5日的留存用户
也就是1月4日的1日留存数（1月4日的1日就是1月5日），1月3日的2日留存数（1月3日的2日就是1月5日），1月2日的3日留存数（1月2日的3日就是1月5日），1月1日的4日留存（1月1日的4日以后是1月5日），这些留存数可以在“新增及留存”表中可得到，如下图。
因此，1月5日前每一日新增用户在1月5日的留存用户数= 1月4日的1日留存数（2966）+1月3日的2日留存数（2628）+1月2日的3日留存（2775）+1月1日的4日留存数（3432）=11801
所以，1月5日DAU=1月5日新增用户数+1月5日的留存老用户数=6680+11801=18481。
2.从留存的角度来看，质量最高的新增用户来自哪一天？
如何定义质量高的新增用户呢？
可以用留存率这个指标来比较，看哪天的留存率最高。
用户在某天开始使用某产品，一段时间后仍继续使用即被认为是留存用户数，也就是有多少用户留下来了。这部分留存下来的用户占当时新增用户的比例即是留存率。
在本案例中，我们可以用7日留存来定义新增用户的质量。在计算过程中将每日的7日留存作为比较的基础。
7日留存指的是一个用户留存了7日，第1日新增的用户，在第7日仍然活跃，那么他就可算做是7日留存的用户。
第7日留存率=(第一天新增的用户中，在第7天还有登录的用户数)/第一天新增总用户数=6日留存数/当日新增数
在Excel中如何计算留存率呢？
以下图为例，用6日留存数据（C3列）/当日新增（B3列）
在“新增及留存”表中计算出的第7日留存率如下：
对第7日留存率进行降序排序，结果如下：
可以看到，在这一个月内，根据第7日留存率来比较，1月9日的第7日留存率最高，达到52.35%，因此，该日的新增用户的质量最高；其次是1月17日。
3.在1月15日当天，SKU销售激活率是多少？
SKU=stock keeping unit(库存量单位)，针对电商而言，一款商品每个品类就是一个SKU，用以区分单品，便于电商品牌识别商品。
SKU销售激活率=当日有销售记录的品类数/SKU总数
(1)SKU总数
在本案例中，可将一个品类认为是一个SKU，所以，由“商品信息表”可知，该款电商产品的SKU数量=108（也就是商品名这一列有多少行数据）
(2)当日有销售记录的品类数
“商品销售情况”表，记录的是每个商品品类在1月1日到1月31日的销售数量，如果某品类当天销售记录为0，表示当天该品类没有销售记录。
例如下面的“品类T441”在1月1日的销量为0，说明在这一天该品类没有销售记录。
我们要求1月15日当天有销售记录的品类数，就是求“商品销售情况”表中，1月15日的有多少行数据是大于0的。
因此，可以用条件计数函数（countif）来对“1月15日”这一列销售记录进行计数。公式为：
=countif(商品销售情况!$P$2:$P$109,">0")
返回结果是90，也就是说1月15日当天有销售记录的品类数为90。
(3)SKU销售激活率
所以，1月15日当天，该款电商产品的SKU销售激活率=当日有销售记录的品类数/SKU总数=90/108=83.3%
4.商品“品类T582”的详情页购买转化率在哪天最高？
在本案例中，定义详情页购买转化率=当日售卖件数/当日页面浏览次数，用于衡量商品在当天的售卖情况。
(1)品类T582的每日售卖数
在这里，我们要用查找函数(vlookup)来查找品类T582的每日售卖数，公式如下：
=vlookup("品类T582",商品销售情况!$A$2:$AF$109,column (B1),0)
公式解读：vlookup语法为
vlookup(要查找谁,在哪个范围里找,返回第几列,是否精确匹配)
第一个参数：要查找谁，要查找品类T582；
第二个参数：在哪个范围里找，在“商品销售情况”表的A2:AF109这个范围里找，也就是说，在“商品销售情况”表里1月1日到1月31日的所有商品销售记录里找；
第三个参数：返回第几列，找到之后，返回第几列的记录呢？要返回1月1日，1月2日，1月3日…直到1月31日的销售记录，也就是说，要返回第2列，第3列，第4列…第N列的记录。因此，在这里，我用列函数column函数来构建一个自然数序列。
column函数返回给定单元格引用的列号。如公式=column（B1），返回的是单元格B1所在的列号，即第2列，所以，返回2；公式=column（B10），返回的是单元格B10所在的列号，同样也是返回2；公式=column（C1）,返回的是单元格C1所在的列号，即第3列，所以返回3。
在此案例中，我就是用列函数column函数来构建一个自然数序列，当公式往右填充时，column (B1)就会自然地变成column (C1), column (D1),column (E1)…作为vlookup函数的第三个参数，为其提供自然数序列。
第四个参数：是否精确匹配。0，代表精确匹配，1，代表模糊匹配。在本案例演示中，要精确匹配，所以用0
(2)品类T582的每日页面浏览次数
同样的原理，我们用vlookup函数来查找品类T582在1月1日到1月31日每日的浏览次数，公式为：
=vlookup("品类T582",商品浏览情况!$A$2:$AF$109,column(B1),0)
(3)每日详情页购买转化率
如下图我们已分别查找出品类T582在1月1日到1月31日的每日售卖数及每日浏览数。因此，当日的详情页购买转化率=当日售卖件数/当日页面浏览次数，结果如下：
可对详情页购买转化率进行降序排序，得到转化率最大值是1月29日的71.1%，也就是说，“品类T582”这个商品的详情页购买转化率在1月29日这天最高。
5.计算1月9日当天的ARPU值
ARPU=当日销售总额/当日DAU=销售数量*单价/DAU
(1)当日销售总额
销售总额=销售数量*单价。
销售数量在“商品销售情况”里，单价在“商品信息表”里，涉及到多个Excel表，需要使用查找函数（vlookup），找到匹配每个商品的单价，公式为
=vlookup(A2,商品信息表!$A$2:$B$109,2,0)
然后，当日销售总额=销售数量*单价，求得每日销售总额，如图：
1月9日的DAU，根据第一个问题的方式可得：24053
那么，1月9日的ARPU=当日销售总额/当日DAU=销售数量*单价/DAU=242900/24053=10.1元。
6.总结
(1)首先需要知道常用的指标有哪些，就像《业务指标》里讲过的，要从下面4个问题出发来明确指标的统计口径。
业务含义：该指标在业务上表示什么；
数据来源：从什么地方收集的原始数据；
统计时间：统计数据的时间范围是什么。
指标定义：如果是占比、比例，得定义清楚是什么除以什么；如果是相加，得定义清楚是什么加上什么。
(2)用Excel来计算指标会涉及到常用的函数，要把数据分析常用的函数掌握才能高效完成职场中的工作。
（3）案例数据如何下载？
推荐：常用的业务指标有哪些？
网友评论