数据分析与可视化实践- 课件 第5、6章 商品库存数据分析、电商客户信息分析

第五章商品库存数据分析序言PREFACE

为盘活企业流动资金，加快资金周转，在保障供给的前提下，最大限度地降低压库资金，直接牵动着企业的经营效益。根据对我国众多制造业企业的库存管理情况所作的调查和参考有关资料，发现制造业企业在库存管理方面普遍存在“不能及时获得库存信息、库存信息不够准确、无法及时了解发料和生产用料情况”等。本章系统介绍怎样利用Excel对商品入库、库存、出库情况分析与可视化，以便提高协同工作效率，保护敏感资料，深层挖掘业务数据并运用视觉效果展示。PART01年度工作概述PART02工作完成情况PART03项目成果展示PART04工作不足之处PART05明年工作计划目录CONTENTS产品入库情况分析与可视化1PART01按时间入库数量分析例题例5-1表5-1展示了某企业仓库部分产品的入库数据，请对数据进行整理分类，按入库时间统计各产品入库数量，并用图表形式展现。入库月份01月01月01月01月02月02月02月02月02月02月产品规格2201A2202A2201A2202A2203A2203A2203A2203A2204A2205A入库数量8126185060691908460分析：对于制造企业来说，一件产品可能需要多个原料，同样，一种原料也可能被多个产品使用，这就意味着库存的数据可能是海量的，要从大量的数据里筛选出满足要求的数据，最方便的办法就是数据透视。表5-1：某企业仓库部分产品的入库数据按类别入库数量分析库存管理中为了更好的进行精细化管理，对产品的入库信息登记要尽可能的详细，特别对于一些特殊行业要求就更多，比如产品的生产厂家、出厂日期、批次编号、采购人员信息、采购部门信息等都要登记入表例5-2表5-2是某企业的部分入库信息表，请根据这些数据制作数据透视表，并分别按采购部门、业务员、供应商统计约入库数量及占比情况。表5-2某企业的部分入库信息表存货编码仓库入库日期入库单号入库类别部门业务员供应商制单人审核人数量单价金额GXGY023半成品库2021/1/10000000001材料入库采购部秦胜利上海照明公司张健举李美丽20041.888376.07GXGY024半成品库2021/1/10000000001材料入库采购部秦胜利上海照明公司张健举李美丽10023.932393.16QDJC022半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽2018.8376.07qdjc023半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽1330.77330.77QDJC028半成品库2021/2/200000000009材料入库采购部秦胜利天津气动有限公司张健举李美丽2012.82256.41SBCS001半成品库2021/2/160000000010材料入库市场部杜拉拉宁夏输送设备公司张健举李美丽620000120000分析：使用明细表多个维度分析数据，关键点是制作数据透视表选取合适的字段，统计数量对比和占比都是熟悉的操作了按类别入库数量分析按类别入库数量分析计算安全库存、最大库存和最小库存量相关知识（1）安全库存：为了防止不确定性因素（交货期突然延期、临时用量增加、交货误期、突发性设备故障等特殊原因）的发生，满足日常维修备件需求，保证设备的正常运行，生产的顺畅，而保持在采购周期内所有备品备件以及其他一般性消耗材等的物料需求计划外的适量库存量，属动态的库存，也称安全存储量。公式：安全库存=日均需求量*紧急采购周期。日均需求量，是指未来一段时间平均每天的需求数量。紧急采购周期，是订货提前期各环节加急处理或特殊处理的时间总和。（2）最大库存和最小库存：库存不足或库存过剩，均是在订购上、作业上，保管上的疏忽所导致的。为了预防这一现象，一般需先设定必要的库存标准，这个标准就是“最小库量”及“最大库存量”。要预防库存不足缺乏情况，库存量的最低标准就是“最小库存量（安全库存量）”。相对于此，为了不让库存过多，必要的库存标准就是“最大库存量（库存上限数量）”。用这两个标准监控库存状况是必要的。公式：最高库存量(成品)=最高日生产量×最短交付天数+安全系数/天最低库存量(成品)=最低日生产量×最长交付天数+安全系数/天最大库存量=平均日销售（需求）量×最高库存天数最低库存量=安全库存+采购提前期内的消耗量最低库存量=日销售量*到货天数+安全系数/天（3）安全库存不等同于最低库存。为了保证库存处于安全范围内，而又不致产生过多的积压成本，可以设定最低库存、安全库存与最高库存。最低库存可理解为针对确定性的因素和不确定性的因素设定的库存，安全库存可理解为只针对于不确定性的因素设定的库存，最高库存可理解为针对积压成本而设置的库存点。计算安全库存、最大库存和最小库存量例5-3表5-3是某仓库A物料的过去12周也即三个月的实际耗用和平均库存数据。经了解，A物料的采购提前期是10天，紧急采购周期为6天，保质期为30天。试计算该物料的安全库存、最小库存量、最大库存量。并使用可视化视图展示该周平均库存所处水平。表5-3

第1周第2周第3周第4周第5周第6周第7周第8周第9周第10周第11周第12周实际用量344913773932662326593340136285397633855736535355713350037241平均库存295645930437328839802862967386119289143212313161219585742969162分析：题目需要大量的公式计算，将计算结果使用可视化视图进行展示，将数据放在坐标轴中进行展示并区分区间，之前的例题中采用了辅助行+折线图这种方式，这里我们再试试用散点图，看能否达到同样的效果。库存数据分析与可视化2PART02库存类别占比分析例5-4表5-4展示了某企业三种产品全年各月份的平均库存数据，请根据表中数据分析月度各产品库存占比情况。表5-4某企业产品平均库存金额

一月二月三月四月五月六月七月八月九月十月十一月十二月服装59511192252298598605833051366614056371852866516716126758602配件32002938052814423674421040985545125081136551552644917472833鞋689369531117970170007142837167598173864164066126957117819160854191840分析：不同种类的产品库存金额和数量之间的对应关系并不一致，比如鞋类产品和服饰配件类产品，鞋类可能一个款式要正常销售就需要5-6个常用码数，数量和金额自然占比就大。库存周转率分析

库存周转率是指某时间段的出库总金额（总数量）与该时间段库存平均金额（或数量）的比。是指在一定期间（一年或半年）库存周转的速度。提高库存周转率对于加快资金周转，提高资金利用率和变现能力具有积极的作用。公式：库存周转率=（该期间的出库总金额（数量）/该期间的平均库存金额（数量））×100%库存周转率=（该期间出库总金额（数量）×2/期初库存金额（数量）+期末库存金额（数量））×100%库存周转率分析例5-5表5-是某企业仓库部分库存数据，请根据表中数据计算库存周转率，该企业库存周转率的达标标准为65%，请使用可视化视图（1）分析本年度库存周转率达标情况，（2）分析库存周转率和出库金额之间的相关性。表5-5月份1月2月3月4月5月6月7月8月9月10月11月12月期初库存金额461,685489,255538,022382,755403,306329,665447,030410,624359,119376,272401,921442,653期末库存金额489,255538,022382,755403,306329,665447,030410,624359,119376,272401,921442,653338,520月度出库金额443,760457,360711,972446,518802,661699,638678,624763,660670,174581,792812,923756,188分析：库存周转率是通过公式计算得出的数据，需要对表中数据进行加工，达成率可以通过组合折线图或者组合柱状图实现，库存周转率和出库金额的相关性分析，可以使用组合图来视觉观察，再使用散点图来验证。库存周转率分析出库数据分析与可视化3PART03出库数量分析例5-7表5-7列出了某企业4种产品2022年全年的出库数据，请根据这些数量分析四种产品出库量的环比增长率，并用图表展示。表5-7某企业产品出库数据

1月2月3月4月5月6月7月8月9月10月11月12月A10139059829939459199549429179599211104B150130147111129123134138123164142137C877783904810832638846846832940927860D612541626576565641597518589563534542分析：环比增长率=（（当前周期数量-前一周期数量）/前一周期数量）*100%，表格中并没有直接的数据，需要添加辅助行或者另外制作表格。出库类别数量分析例5-8表5-8列出了某企业2021-2022年三种类型商品的出库量，请根据表中数据分析三种类别商品的出货量的同比增长率和环比增长率。表82021-2022商品出库量数据2021年出库量2022年出库量月份服装鞋饰品

服装鞋饰品1月4517013,9331月1,1894913,5252月921243,9782月8801,2276,7263月1,6572796,0853月3,1247985,6634月2,4174625,3684月3,5511,1203,9985月9137161,9335月1,3704471,5466月2171504,1096月9154181,6977月9463,3026,3967月1,7289226,1058月9902,1706,4008月2,0712,1687,9709月7266406,5659月4,1452,0387,64810月1,4339484,38110月2,8014373,73111月7227263,97011月1,7572253,83912月5355815,60912月9841,6414,620分析：表格中并未给出增长率数据，需要对数据进行加工后进行展示。环比增长率=（（当前周期数量-前一周期数量）/前一周期数量）*100%，同比增长率=（（当前数量-去年同一周期数量）/去年同一周期数量）*100%出库类别趋势预测趋势预测分析法亦称时间序列预测分析法，是根据事业发展的连续性原理，应用数理统计方法将过去的历史资料按时间顺序排列，然后再运用一定的数字模型来预计、推测计划期产（销）量或产（销）额的一种预测方法。由于趋势测计划期间的销售数量或销售金额预测分析法所采取的数学方法不同，又可分为：（1）算术平均法。以过去若干时期的销售量（或销售金额）的算术平均数作为计划期的销售预测数。（2）移动加权平均法。是根据过去若干时间的销售量（或销售金额）按其距计划期的远近分别进行加权（近期所加权数大些，远期所加权数小些；然后计算其加权平均数，作为计划期的销售预测数。所谓“移动”是对计算平均数的讨期不断往后推移。（3）指数平滑法。在预测计划期销售量（或销售金额）时，导入平滑系数（或称加权因子）进行计算。指数平滑法与移动加权平均法实质上是近似的，其优点是可以排除在实际销售中所包含的偶然因素的影响。出库类别趋势预测例5-9表5-8是某企业4种商品的出库量，请根据表中数据对全年销售趋势进行预测计算，并进行可视化分析。表5-9某企业主要产品全年出库数据

1月2月3月4月5月6月7月8月9月10月11月12月电脑10139059829939459199549429179599211104扫描仪150130147111129123134138123164142137显示器877783904810832638846846832940927860打印机612541626576565641597518589563534542分析：三种预测方法中，算术平均法没有参考意义，所以我们选取移动平均法和指数平滑法做对比。Excel默认是不包含这两种数据分析工具的，需要手动添加一下菜单里才会显示。THANKS!第六章电商客户信息分析序言PREFACE

电商平台线上购物市场庞大且应用广泛，所有的电商经营者都在努力争取自己市场的利益最大化，从而形成激烈的竞争。面对电商平台强大且众多的竞争对手，每一个运营者要想生存并取得发展，就必须掌握科学的运营方法，及时发现并解决运营中的数据问题，掌握采购的合理时间、规划商品上架的数量和种类等。本章以电商平台运营模拟数据为例，基于客户数据、客户属性分析与可视化系统介绍Excel在运营数据中的应用，科学有效提高市场竞争力。PART01客户数据分析与可视化PART02客户属性分析与可视化目录CONTENTS客户数据分析与可视化1PART01按时间入库数量分析与传统零售业对比，电子商务获得数据的方式便捷且全面，可以通过数据化来监控并发现问题，在营销管理、客户管理等环节，利用数据分析判断企业内部、营销手段、客户体验的不足、了解客户的内在需求等，基于数据分析，总结发展趋势，为网络营销决策提供支持。电子商务行业大数据分析主要是采用以下算法以及模型。1.RFM模型通过分析有购买记录客户的购买行为来判断客户的价值和创立能力，在（Recency）用户购买时间、（Frequency）消费频率、（Monetary）消费金额三个方面进行客户区分，从而制定针对不同的会员的营销活动。2.Apriori算法Apriori算法是一个关联规则挖掘算法。在电子商务中，关联营销是较为重要的营销手段。要做好关联营销，就要知道不同商品之间的关联关系，如衣服和裤子的搭配穿法、客户的购买经历等，通过Apriori算法，可以分析哪两种商品是有关联性的，从而确定商品的陈列等因素，进而组套销售。3.网站分析网站分析是针对发生在一段时间内的网站客户访问及购买情况。如通过对访问量、页面停留、支付金额、转化率、客单价等数据，分析对经营策略的效果，深入分析存在复购行为的客户基本特征。做电子商务运营，不能迷茫、不知所措，不能盲目地做，结合数据分析制定有针对性的策略，无论是在优化销售效果或后期引流操作上出现任何的问题，都可以及时地进行优化。电子商务归根到底是一种商业活动，企业最终还是以盈利来判断运营的效果。影响企业电子商务活动的重要指标主要有流量、转化率、客单价、用户、订单等。客单价分析客单价是指进入企业网站的每一个顾客平均购买商品的金额。即统计时间内，支付金额、支付买家数，即平均每个支付买家的支付金额。分析客单价能够了解帮助企业明确用户定位，以及盈利期望是否合理，继而优化定价策略，有助于促销活动的开展。公式：客单价=销售额/客户数量表6-1是某高校自动售货机2021某一周内的销售数据的部分展示，请根据表中数据分析该售货机的客单价。表6-1自动售货机部分销售数据区域购买日期用户ID支付方式商品类别商品名称消费金额雁塔区2021/9/24220902069微信饮料雪碧-智能3碑林区2021/9/25220902069支付宝饮料可口可乐-智能3雁塔区2021/9/28220902070微信饮料雪碧-智能3碑林区2021/9/29220902070支付宝饮料可口可乐-智能3新城区2021/9/26220902071微信饮料屈臣氏苏打水3分析：客单价指标离不开时间这个关键点，一定是某个时间段内的客户平均消费额，要统计客单价，需要统计不重复的客户数量，这对传统零售业几乎是不可想象的，但对于电子商务来说却很容易。只需要将数据导出为Excel格式，进而使用数据透视表即可进行计算。用户复购率分析

复购率是电商业务分析中经常用到的指标，复购率的含义如下：

复购率是衡量产品受欢迎度的指标。复购率高，说明企业/产品/品牌的忠诚顾客人数越多，这时企业需要把更多的精力和资源投入到吸引、引导在用户的复购上。复购率低说明企业/产品的忠诚顾客不多，需要把资源和精力用在提升顾客的转化率上。复购率一般有2种算法，具体的应用到哪一种需要根据企业的情况和通常的做法。（1）复购的人数复购率

=单位时间内购买次数大于1的人数/所有购买的人数。（2）复购的次数复购率

=单位时间内总的复购次数/所有购买的人数按类别入库数量分析例6-2表6-2是某电商平台2022年3月份部分销售明细字段展示，请根据这些明细数据，分析该平台这段时间内的用户复购率。表6-2电商平台销售明细区域购买日期用户ID支付方式商品类别商品名称消费金额海淀区2020/3/12220902078支付宝膨化食品KSF牌341蛋卷（160g/袋）8.5海淀区2020/3/12220902080现金饼干康师傅蔓越莓曲奇（120g/盒）12海淀区2020/3/12220902085支付宝饮料小茗同学-智能5海淀区2020/3/12220902091现金膨化食品旺仔小馒头9.6海淀区2020/3/12220902095现金其它亿智康师傅草莓味注心饼干13.5海淀区2020/3/12220902112微信饮料屈臣氏苏打水6分析：根据复购率的计算公式，在表6-2中只需要统计用户ID的个数和每个ID重复出现的次数，这里需要使用两次数据透视功能，第一次先统计每个ID重复出现的次数，第二次统计每个次数的ID个数。为了简化过程，第二次数据透视将直接生成透视图。用户复购率分析用户支付偏好分析目前线下销售的支付方式有现金、微信、支付宝、POS刷卡等，线上销售主要就是微信、支付宝、银联等，对用户支付偏好的分析，有助于商户优化支付界面的设计。例题例6-3表6-3列出了某平台商户的部分月度支付统计数据，请根据这些数据统计本年度三种支付方式和支付金额的占比。表6-3某线上商户部分月份支付数据时间10月10月10月11月11月11月12月12月12月支付方式微信银联支付宝微信银联支付宝微信银联支付宝支付次数371111743231217630310193支付金额382131144175743326912481900831209102020844分析：表中的数据已经经过统计了，但没有最终需要的汇总数据，可以使用数据透视图也可以使用Excel的合并计算功能来实现。外圈表示支付次数占比情况，内圈表示支付金额占比情况。最终效果如图6-14所示。用户流失率分析用户（客户）流失率是指企业用户（客户）单位时间内流失的数量占总客户（用户）量的比率。例题例6-4表6-4展示某微商4名销售客户数量1-6月变化情况，请根据表中数据分析4人的客户流失率对比情况。表6-4微商销售客户数量变化表员工姓名1月2月3月4月5月6月张春玲142136143133146150胡雪欣175165189178184184马晓丹168165164190186188李龙福179180174163168160分析：客户流失率计算应以本周期数据为计算基础，如2月的流失率应为2月相比于1月的减少量除以2月的用户量。客户属性分析与可视化2PART02客户属性分析

电子商务数据分析中的数据分析是基于有限资源来最大化撬动效益。通过数据分析进行最大价值的客户的挖掘、创造，实现对客户的针对性营销。精准定位客户，才能够实现精准化的运营，以此获得最大的转化率。

精准式营销最重要的是对客户进行细分，客户细分就是将不同特征的客户进行聚类的全过程，细分后，客户具有相似的需求和购买特征。按照客户的多样化需求进行细分，之后按照每一类客户特征进行人性化营销策略的制定。因为客户地域、属性以及职业等都有差异，客户细分主要是为了体现不同客户的差异化需求，所以客户价值也不一样。

企业怎样充分利用有限的资源，将更多资源分配到能够给企业带来更多效益的客户群中，也是企业进行客户细分的主要目标之一，对于任何企业来说，盈利是其最终的目标，而只有精准定位客户群，才能实现更好的转化率。客户性别属性分析例6-5表6-5展示了某电商平台店铺上半年的顾客访问量和下单量按性别统计结果，请分析访问量和下单量的按性别对比情况。表6-5某电商平台店铺访问数据月份女性访问量男性访问量女性下单量男性下单量1月份360140220802月份5002203201603月份4652103101354月份366150235965月份3841602521056月份456175307111分析：按性别进行数量和占比对比分析是非常熟悉的操作了，这里使用一种新的可视化方案，突出对比效果。客户年龄属性分析例6-6表6-6列出了某电商网站统计的客户年龄分布表，请根据这些数据制作可视化图表，分析客户年龄分布情况。表6-6年龄段20岁以下20-2526-3031-3536-4041-5051以上总计访问量465882985525216118363227分析：单纯的数值分布对于店铺经营者来说没有设么参考意义，需要进一步对数据进行加工计算出各年龄层次客户的占比情况，才能够去制定有针对性的营销方法和策略。客户地域属性分析例6-7表6-7展示了某互联网广告投放1个月内的数据明细，请根据表中数据统计各地区广告投放效果数据，列出前10名占比情况。表6-7广告投放数据明细日期省市展现量点击量花费平均点击花费总成交金额2021/6/26北京7078.351.1902021/6/26福建2410.670.6702021/6/26甘肃1456.821.3602021/6/26广东7822.591.2902021/6/26海南500002021/6/26河北2845.061.270分析：明细数据做统计计算，需要使用数据透视表，广告投放最关心的数据就是点击量和最终消费金额。统计结果需要体现这些数据。客户地域属性分析散点图趋势线的R2离1相差较远，说明两者之间并没有明显的线型相关，也就是说未必看到的用户都回去点击客户终端属性分析目前移动互联网已成为电商平台的主流媒体，手机、平板成了用户的主流终端，传