处理电商订单流水(练手excel运算)

背景

某年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条评论  助教点评

上一页

1

下一页

 张颖

请将这个文本文件导入 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,可计算出团长等级

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容