职场Excel：如何分析常用的指标？

​【业务问题】

下面的Excel记录了某款电商产品在1月1日发布，1个月后的新增及留存数据、商品销售数据、商品详情页浏览数据、及商品信息表：

（文末有数据下载）

现在业务部门需要你分析出：

(1)1月5日的DAU是多少？

(2)从留存的角度来看，质量最高的新增用户来自哪一天？

(3)在1月15日当天，SKU销售激活率是多少？

(4)商品“品类T582”的详情页购买转化率在哪天最高？

(5)1月10日当天的ARPU值是多少？

下面就这个案例讲解如何用excel分析常用指标，不过文字比较长，而且案例和案例只间也有不同，想要玩转指标分析，还得需要有完善的数据分析思维，面对不同情况也能灵活分析。如果真的想入门数据分析，可以跟着我在知乎知学堂新上线的数据分析课程一起学学试试，课程同时带大家学习数据分析工具使用和建立数据分析思维，视频讲解比文字更清晰，而且从基础讲起，内容版本更新，迭代更及时，对于0基础入门的人也很友好。

【分析思路】

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来计算指标会涉及到常用的函数，要把数据分析常用的函数掌握才能高效完成职场中的工作。

分析常用指标，只是众多 Excel诸多用法中的冰山一角，还有许多数据处理和分析的技能，学会了的话既能帮我们节省很多时间，还能提高我们决策的科学性，如果你的工作也经常和数据打交道，或者对数据处理和数据分析感兴趣的话，可以报名我在知乎知学堂上开设的数据分析训练营。

一共3天的时间，直播讲解+学习社群的形式，带你用最短的时间掌握职场上常用的工具操作、分析技巧方法、和数据思维，解决常见的数据处理、分析问题是没有问题的。

推荐：常用的业务指标有哪些？