背景
某年1月1日 全新上线 的社区电商平台，下面提供了该电商平台 经过处理的 1-3 月的订单流水数据完成如下任务：
1.将这个文本文件导入 Excel，并将分类处理为一级分类和二级分类。
2.计算3 个月中各月的成单团长数量、按月的团长新增数和团长留存率。
3.计算：
各一级、二级分类的销量（参团每人限购一件）
各一级、二级分类的销售额以及毛利润率（定义：毛利润 = 订单金额 - 返利费用）
4.推测该电商平台的返利机制
5.如果要评估不同“团长”的贡献价值，你会选择哪些指标并如何评估？给出你设计这种评估方法（评分体系/等级体系）的理由，并利用 Excel 计算。
一、将这个文本文件导入 Excel，并将分类处理为一级分类和二级分类。
1.将TXT文本复制到excel中，并通过符号“,”分列
2.在分类列后面插入一列，将分类列通过“-”分列，得到一级分类和二级分类（图片截取上半部分数据）
-通过筛选查看各列数据，没有脏数据
二、计算3 个月中各月的成单团长数量、按月的团长新增数和团长留存率。
成单团长数量：1月635人；2月625人，3月623人
1.通过筛选日期，分月份筛选
2.分别复制3个月的团长数量到新表
3.每列去重，得到各月成单团长数量为：1月635人；2月625人，3月623人
按月团长新增数和留存
结果：
过程：
1. 1月份团长默认都是新增，共635人
2. 复制1月份和2月份团长ID，在后面一列插入vlookup函数并向下填充
筛选可得，#N/A为2月份新增ID，人数是26人；其余为1月份新增在2月份的留存数据，人数为599人
计算可得：1月份的首月留存率=1月份新增在2月份留存人数/1月份新增总数*100%=599/635*100%=94.33
3. 如上操作复制2月份新增团长ID和3月份团长ID，插入vlookup函数可得出2月份新增在3月的留存数据，人数为25人
计算可得：2月份的次月留存率=2月份新增在3月份的留存人数/2月份新增总数*100%=25/26*100%=96.15%
4. 复制1月份+2月份团长数量和3月份团长数量，插入vlookup函数可得3月份新增为1人。
5. 复制1月份团长ID和3月份团长ID，插入vlookup函数可得1月份新增在3月份的留存人数为598人，1月份的二月留存=1月份新增在3月份的留存人数/1月新增总数*100%=598/635*100%=94.17%
三、1.各一级、二级分类的销量（参团每人限购一件）
1.使用数据透视表，展示总体销量：
2.一级分类销量
3.二级分类销量
三、2.各一级、二级分类的销售额以及毛利润率（定义：毛利润 = 订单金额 - 返利费用）
总体销售额：
一级分类销售额
二级分类销售额
毛利润率：大致都在94%左右
四、推测该电商平台的返利机制
返利机制：返利金额=订单金额*5%＋人数
1. 返现金额/订单金额算出每笔订单返现比例，添加进数据透视表。平均返现比例在6%左右，且各分类相差不大，推测返现金额主要与人数、金额有关
2. 通过排序，返现比例在5.05%-18.27% 。假设返利金额=订单金额5%+人数*x 带入一组数据计算发现70.7=1214*0.5%+10*1 得x=1 用其他数据验证可行。
评估团长价值
1. 指标选取
2. 评估体系
公式：团长等级=0.2*订单数+0.0005*金额+0.03*人数
公式建立过程：
1. 利用数据透视表展示团长完成订单数量、订单总金额、参团人数，复制到新表
2.依次排序查看数据是否有过高或过低。发现一团长ID订单金额过低，该团长数据不计入计算
3. 计算每项平均值得出如下数据
订单数：金额：人数 大致为1:1000:10 三项指标数额相差较大，所以需要按照比例对结果进行换算。
4. 算上权重比，最后团长评分体系公式为：
团长等级=订单数*0.2+(金额*0.5*1/1000)+(人数*0.3*1/10)
简化公式：团长等级=0.2*订单数+0.0005*金额+0.03*人数
将公式带入excel，可计算出团长等级
查看全部
0 0条评论 助教点评
张颖
请将这个文本文件导入 Excel，并将分类处理为一级分类和二级分类。
将TXT文本复制到excel中，并通过符号“,”分列
在分类列后面插入一列，将分类列通过“-”分列，得到一级分类和二级分类（图片截取上半部分数据）
通过筛选查看各列数据，没有脏数据
请计算3 个月中各月的成单团长数量、按月的团长新增数和团长留存率。
1.成单团长数量：1月635人；2月625人，3月623人
通过筛选日期，分月份筛选
分别复制3个月的团长数量到新表
每列去重，得到各月成单团长数量为：1月635人；2月625人，3月623人
2.按月团长新增数和留存
结果：
过程：
1. 1月份团长默认都是新增，共635人
2. 复制1月份和2月份团长ID，在后面一列插入vlookup函数并向下填充
筛选可得，#N/A为2月份新增ID，人数是26人；其余为1月份新增在2月份的留存数据，人数为599人
计算可得：1月份的首月留存率=1月份新增在2月份留存人数/1月份新增总数*100%=599/635*100%=94.33
3. 如上操作复制2月份新增团长ID和3月份团长ID，插入vlookup函数可得出2月份新增在3月的留存数据，人数为25人
计算可得：2月份的次月留存率=2月份新增在3月份的留存人数/2月份新增总数*100%=25/26*100%=96.15%
4. 复制1月份+2月份团长数量和3月份团长数量，插入vlookup函数可得3月份新增为1人。
5. 复制1月份团长ID和3月份团长ID，插入vlookup函数可得1月份新增在3月份的留存人数为598人，1月份的二月留存=1月份新增在3月份的留存人数/1月新增总数*100%=598/635*100%=94.17%
各一级、二级分类的销量（参团每人限购一件）
使用数据透视表，展示总体销量：
一级分类销量
二级分类销量
各一级、二级分类的销售额以及毛利润率（定义：毛利润 = 订单金额 - 返利费用）
总体销售额：
一级分类销售额
二级分类销售额
毛利润率：大致都在94%左右
推测该电商平台的返利机制
返利机制：返利金额=订单金额*5%＋人数
1. 返现金额/订单金额算出每笔订单返现比例，添加进数据透视表。平均返现比例在6%左右，且各分类相差不大，推测返现金额主要与人数、金额有关
2. 通过排序，返现比例在5.05%-18.27% 。假设返利金额=订单金额5%+人数*x 带入一组数据计算发现70.7=1214*0.5%+10*1 得x=1 用其他数据验证可行。
评估团长价值
1. 指标选取
2. 评估体系
公式：团长等级=0.2*订单数+0.0005*金额+0.03*人数
公式建立过程：
1. 利用数据透视表展示团长完成订单数量、订单总金额、参团人数，复制到新表
2.依次排序查看数据是否有过高或过低。发现一团长ID订单金额过低，该团长数据不计入计算
3. 计算每项平均值得出如下数据
订单数：金额：人数 大致为1:1000:10 三项指标数额相差较大，所以需要按照比例对结果进行换算。
4. 算上权重比，最后团长评分体系公式为：
团长等级=订单数*0.2+(金额*0.5*1/1000)+(人数*0.3*1/10)
简化公式：团长等级=0.2*订单数+0.0005*金额+0.03*人数
将公式带入excel，可计算出团长等级
