利用Excel计算DAU、商品转化率和ARPU值等

  这个小练习是根据某公众号发布的内容，自己再进行整理的，觉得非常不错，所以拿来练练手。
  Excel记录了某款电商产品在1月1日发布，1个月后的新增及留存数据、商品销售数据、商品详情页浏览数据、及商品信息表。现在业务部门需要你分析出：

• 1月5日的DAU是多少？
• 从留存的角度来看，质量最高的新增用户来自哪一天？
• 在1月15日当天，SKU销售激活率是多少？
• 商品“品类T582”的详情页购买转化率在哪天最高？
• 1月10日当天的ARPU值是多少？

  得知以上的问题后，接下来我们将一步步来解决。（内容涉及一些常用的业务指标和excel函数的使用，如有不懂的地方请提前复习下。）

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月5日之前每一日新增的用户数在1月5日的留存用户数可以由上图计算得到，如1月1日新增的用户数在1月5日的留存用户数为4日留存，如果为5日留存，即为1月1日新增的用户数中有2649在1月6日中留存。所以可以推算得到上图标黄的数据。

则可以计算1月5日的日活跃用户数=6680+3432+2775+2628+2966=18481

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

  判断质量高的新增用户，可以使用留存率来计算，看哪天的留存率比较高。
  用户在某天开始使用产品，一段时间内仍继续使用该产品，则可以认为该用户是留存用户，也就是说有多少用户在使用你的产品后留下来了。这部分留存下来的用户数与时新增的用户数的比例称为留存率。
  用户的留存常见的有次日留存、3日留存、7日留存和30日留存。案例中使用7日留存来定义新增用户的质量。
  7日留存的用户是指在第一天新增的用户中，第7天还在活跃的用户。
  7日留存率=（第一天新增的用户中，第7天还在登录的用户数）/第一天新增的总用户数 = 6日留存数/当日新增用户数。

  从上图中可以发现，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，表示当天该品类没有销售记录。
  我们的目的是获得在1月15日当天有销售记录的商品品类数，则要求在“商品销售情况”表中销售记录数大于0的数，这时候可以使用条件计数函数（countif）来计算。

  根据返回的结果为90，则说明在1月15日当天有销售记录的商品品类数为90。
（3）SKU销售激活率
  根据（1）和（2）的内容，可以计算出1月15日SKU的销售激活率。

SKU销售激活率=当天有销售记录的商品品类数/SKU总数=83.33%

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

  在本案例中，由于数据有限，则详情页购买转化率=当天销售的商品数/当天页面的浏览次数，用来衡量当天商品的售卖情况。
  品类T582的每日销售商品数可以直接在“商品销售情况”表中查询，品类T582的每日页面的浏览次数可以在“商品浏览情况”表中查询得到。
  品类T582的每日详情页购买转化率如下所示：

  从上图中可以看出，品类T582的详情页购买转化率的最大值出现在1月29日，为71.11%。

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

ARPU值=当天的销售总额/当天的DAU=销售数量*单价/DAU

(1)1月10日的销售总额

销售总额=销售数量*单价

  销售数量在“商品销售情况”表中，而单价在“商品信息表中，在这里可以使用vlookup函数去匹配，得到销售额如下所示：

可以计算得到销售总额为235317元

（2）1月10日的DAU数
  1月10日的当天的DAU数可以根据问题1进行计算得到，如下所示：

则1月10日当天的DAU数=6046+3324+3346+3093+1720+2434+2232+1810+1628=24312

（3）1月10日的ARPU值

1月10日的ARPU值=当天的销售总额/当天的DAU=235317/24312=9.68元

  总结：这个案例主要是面向业务，知道各个指标是怎么计算的，那么就可以根据现有的数据计算出想要的指标数据，是比较简单的。但往往数据分析师需要知道前期如何把问题转化为可以量化的指标。（写的不对的地方和说的不对的地方，请大家批评指正，自己也是在慢慢摸索，慢慢学习。）