《AIGC高效办公：Excel数据处理与分析》课件 第8章 客户数据分析

第8章客户数据分析8.1案例要求内容导航8.2案例准备8.3案例操作8.4综合实训8.5课后练习案例要求某经营童鞋的企业为了更好地了解客户需求，给客户提供更优质的商品和服务，现需要完成客户数据分析工作，包括客户画像的分析、客户留存率与流失率的分析、客户忠诚度的分析以及客户价值的分析等。该企业的数据分析部门需要对收集到的相关数据进行分析并得出结论，为企业完善运营策略提供数据支持，以便企业提升客户黏性和购买力。右图所示为本案例的部分效果展示。8.1案例要求内容导航8.2.1客户画像8.2.2客户留存率与流失率

8.2.3客户忠诚度

8.2.4RFM模型8.2案例准备8.3案例操作8.4综合实训8.5课后练习8.2.1客户画像对于商务数据分析而言，客户画像的侧重点在于将客户分群，分析各群体的数量、占比、变换等情况，从而了解所有客户的情况。下图所示为百度指数平台统计的2024年3月1日—2024年3月31日搜索“童鞋”的客户画像。由图可知，在该期间，30~39岁的客户搜索“童鞋”的占比最高，女性客户的占比要略高于男性客户。8.2.2客户留存率与流失率客户留存率与流失率能够直观体现企业客户的留存与流失情况。这两个指标的变动对企业的收入和利润有极大影响。客户留存率增加，企业的销售就会提升；相反，客户流失率增加，不仅表示企业商品和服务的质量受到质疑，也表示之前由客户带来的稳定收入将大打折扣。

客户留存率的计算公式：客户留存率=当期客户数/上期客户数×100%。

客户流失率的计算公式：客户流失率=当期流失客户数/上期客户数×100%=（上期客户数－当期客户数）/上期客户数×100%。8.2.3客户忠诚度客户忠诚度指的是客户出于对企业商品或服务的喜好而产生重复购买行为的程度。影响客户忠诚度的指标较多，其中购买频次和重复购买率比较常用。重复购买率也叫复购率，是客户忠诚度的核心指标，需要通过购买频次进行计算。方法主要有两种。第一种的计算公式：重复购买率=重复购买客户数量/客户样本数量×100%。假设客户样本为100人，其中50人重复购买（不考虑重复购买了几次），则重复购买率=50/100×100%=50%。第二种的计算公式：重复购买率=客户重复购买行为次数（或交易次数）/客户样本数量×100%。假设客户样本为100人，其中50人重复购买，这50人中有35人重复购买1次（即购买2次），有15人重复购买2次（即购买3次），则重复购买率=（35×1+15×2）/100×100%=65%。实际工作中选择哪种方法计算重复购买率，企业应根据自身运营要求来选择。8.2.4RFM模型RFM模型是一种描述客户价值的工具，主要包含3个指标，分别是最近一次交易时间（Rencency）、交易频率（Frequency）和交易金额（Monetary）。下表所示为某企业使用RFM模型细分客户价值的示例。8.1案例要求内容导航8.3.1借助文心一言统计并分析客户画像

8.3.2分析客户留存率与流失率

8.3.3借助通义统计人数并分析客户忠诚度8.3.4细分客户价值8.2案例准备8.3案例操作8.4综合实训8.5课后练习8.3.1借助文心一言统计并分析客户画像某童鞋企业为了解最近新增的一批客户的基本情况，收集了这些客户的姓名、性别、年龄、喜爱的商品类目和喜爱的商品颜色等数据。下面首先利用文心一言统计客户数据，然后在Excel2019中利用这些数据并结合COUNTIF函数和COUNTIFS函数建立客户画像，分析这批客户的性别、年龄分布情况，以及喜爱哪些商品类目和颜色。其具体操作如下。STEP01登录文心一言官方网站，单击文本框下方的”文件“按钮，如图所示如右图所示。

STEP02在显示的页面中单击“点击上传或拖入文档”区域，如右图所示。8.3.1借助文心一言统计并分析客户画像STEP03打开“打开”对话框，选择“客户画像数据.xlsx”素材文件，单击”打开“按钮，如右图所示。STEP04在文本框中输入需求，这里请求文心一言统计表格数据，并分析客户的情况，按【Enter】键，如上图所示。STEP05文心一言将按照要求统计数据并分析统计结果，如右图所示。8.3.1借助文心一言统计并分析客户画像STEP06在Excel中打开“客户画像数据.xlsx”素材文件，按前文所述方法，将G2:H3单元格区域的字体格式设置为“方正宋三简体”，字号设置为“10”，适当调整G列和H列的列宽，加粗并左对齐G列的数据，居中对齐H列的数据；为G2:H3单元格区域添

加边框，并在G2和G3单元格中分别输入“男”和“女”，如右图所示。STEP07选择H2单元格，在编辑框中输入“=COUNTIF(B2:B61,"男")”，表示统计B2:B61单元格区域中数据为“男”的单元格数量，按【Ctrl+Enter】组合键返回统计结果，如右图所示。8.3.1借助文心一言统计并分析客户画像STEP08选择H3单元格，编辑框中输入“=COUNTIF(B2:B61,"女")”，表示统计B2:B61单元格区域中数据为“女”的单元格数量，按【Ctrl+Enter】组合键返回统计结果，如下图所示。STEP09相

同

方

法

对G5:H7单

元

格

区

域

进

行

格

式

设

置

，并

在G5:G7单

元

格

区

域

中

依次

输

入“20~30岁”“31~40岁”和“40岁以上”。如左图所示。STEP10选择H6单元格，在编辑框中输入“=COUNTIFS(C2:C61,">30",C2:C61,"<=40")”，表示统计C2:C61单元格区域中数据大于30且小于或等于40的单元格数量，按【Ctrl+Enter】组合键返回统计结果，如右图所示。8.3.1借助文心一言统计并分析客户画像STEP11选择H7单元格，在编辑框中输入“=COUNTIF(C2:C61,">40")”，表示统计C2:C61单元格区域中数据大于40的单元格数量，按【Ctrl+Enter】组合键返回统计结果，如左图所示。STEP12按照相同方法建立统计商品类目和商品颜色的单元格区域，并利用COUNTIF函数统计相应的数据，结果如左图所示。STEP13按前文所述方法，以G2:H3单元格区域数据为数据源创建柱形图，图表标题修改为“企业客户性别分布”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“性别”和“人数/人”。如左图所示。8.3.1借助文心一言统计并分析客户画像STEP14按相同方法以G5:H7单元格区域数据为数据源创建柱形图，将图表标题修改为“企业客户年级分布”，设置图表布局，并美化图表，效果如左图所示。STEP15按前文所述，以G9:H14单元格区域数据为数据源创建条形图，将图表标题修改为“企业客户喜爱商品类目分布”，设置图表布局，并美化图表，效果如左图所示。8.3.1借助文心一言统计并分析客户画像STEP16按相同方法以G16:H22单元格区域数据为数据源创建条形图，将图表标题修改为“企业客户喜爱商品颜色分布”，设置图表布局，并美化图表，效果如左图所示。STEP17将创建的4张图表放置在一起，调整每张图表的尺寸至合适，如左图所示。由图可知，该企业新增的一批客户中，男性人数远高于女性人数，年龄主要集中在31~40岁，对企业的皮鞋和运动鞋较为偏爱，颜色则更偏爱蓝色和红色。8.3.2分析客户留存率与流失率为了针对不同城市的客户采取合适的运营策略，某童鞋企业收集了一定时期不同城市期初和期末的客户数量。下面在Excel2019中利用这些数据计算不同城市的客户留存率和流失率，然后建立组合图分析数据。其具体操作如下。STEP1打开“客户数据.xlsx”素材文件，在D1单元格中输入“客户留存率”，选择D2:D11单元格区域，在编辑框中输入“=C2/B2”，按【Ctrl+Enter】组合键计算不同城市的客户留存率，如右图所示。STEP2在E1单元格中输入“客户流失率”，选择E2:E11单元格区域，在编辑框中输入“=(B2-C2)/B2”，按【Ctrl+Enter】组合键计算不同城市的客户流失率，如上图所示。8.3.2分析客户留存率与流失率STEP3按前文所述方法，以客户所在城市、客户留存率和客户流失率为数据源建立组合图，其中客户流失率对应的数据系列以“次坐标轴”的方式呈现。将图表标题修改为“各城市客户留存率与流失将率”，图例移至图表上方，添加横坐标轴、主要纵坐标轴和次要纵坐标轴标题，内容分别修改为“城市”“客户留存率”和“客户流失率”。STEP4按前文所述方法，将客户留存率数据系列的填充颜色设置为“绿色”，将客户流失率数据系列的线条设置为“1磅”，虚线-长划线”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，调整图表尺寸至合适，效果如上图所示。8.3.3借助通义统计人数并分析客户忠诚度为分析企业特定期间内客户的忠诚度情况，某童鞋企业收集了客户的姓名、性别和交易次数等数据。下面首先利用通义获取统计公式，然后在Excel2019中分析不同交易次数的占比情况，然后进一步分析客户忠诚度情况。其具体操作如下。STEP01登录通义官方网站，单击文本框左侧的“上传文件”按钮

，弹出的下拉列表中选择“上传文档”选项，如左图所示。STEP02打开“打开”对话框，选择“客户忠诚度数据.xlsx”素材文件，单击”打开“按钮，如右图所示。8.3.3借助通义统计人数并分析客户忠诚度STEP03在文本框中输入需求，这里请求通义千问根据表格数据统计不同交易次数的客户数量需要使用什么公式，按【Enter】键。通义千问将根据需求给出反馈，如左图所示。STEP04打开“客户忠诚度数据.xlsx”素材文件，在F2:F4单元格区域中输入文本，如右图所示，以统计不同交易次数的人数。8.3.3借助通义统计人数并分析客户忠诚度STEP05选择G2单元格，在编辑框中输入“=COUNTIF(D2:D61,3)”，表示统计D2:D61单元格区域中数据为“3”的单元格数量，按【Ctrl+Enter】组合键返回统计结果，如下图所示。STEP06选择G2单元格编辑框中的公式内容，按【Ctrl+C】组合键复制。选择G3单元格，在编辑框中单击鼠标左键定位插入点，按【Ctrl+V】组合键粘贴，然后将公式中的“3”修改为“2”，按【Ctrl+Enter】组合键确认，结果如上图所示。8.3.3借助通义统计人数并分析客户忠诚度STEP07按相同方法统计交易1次的人数，结果在G4单元格显示，如下图所示。STEP08按前文所述方法，以F2:G4单元格区域数据为数据源创建饼图，删除图表标题和图例，在饼图扇区外侧添加数据标签，将内容设置为“类别名称”，“百分比”，“显示引导线”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，字号设置为“10”，然后调整图表尺寸和数据标签位置至合适，如上图所示。8.3.3借助通义统计人数并分析客户忠诚度STEP09选择A1:D61单元格区域，在【数据】/【排序和筛选】组中单击“排序”按钮

，打开“排序”对话框，在“主要关键字”下拉列表中选择“性别”；单击”添加条件“按钮，在“次要关键字”下拉列表中选择“交易次数/次”，在“次序”栏的下拉列表中选择“降序”，单击”确定“按钮，如右图所示。STEP10在F6单元格和F7单元格中分别输入“男性”和“女性”。选择G6单元格，按照“重复购买率=客户重复购买行为次数/客户样本数量×100%”的计算公式，在编辑框中输入“=(COUNT(D2:D14)*2+COUNT(D15:D33))/COUNT(D2:D45)”，表示计算重复购买2次和重复购买1次的男性客户的交易次数与所有男性客户数量的比率，按【Ctrl+Enter】组合键返回计算结果，如上图所示。8.3.3借助通义统计人数并分析客户忠诚度STEP11选

择G7单

元

格

，

在

编

辑

框

中

输

入“=(COUNT(D46:D48)*2+COUNT(D49:D56))/COUNT(D46:D61)”，表示计算重复购买2次和重复购买1次的女性客户的交易次数与所有女性客户数量的比率，按【Ctrl+Enter】组合键返回计算结果，如右图所示。STEP12按前文所述方法，以F6:G7单元格区域数据为数据源创建柱形图，将图表标题修改为“男女客户忠诚度对比”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“性别”和“重复购买率”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，将数据系列的填充颜色设置为“绿色”，在数据系列外侧添加数据标签，并调整图表尺寸，效果如上图所示。8.3.4细分客户价值为针对不同价值的客户采取不同的促销方案，某童鞋企业需要借助RFM模型细分客户价值，并统计各类客户的数量。本案例收集了特定期间内该企业客户的姓名、交易次数、交易金额、上次交易时间等数据。下面在Excel2019中计算上次交易时间到现在的时间间隔，然后利用IF函数对客户进行细分管理，最后统计各类型客户的数量。其具体操作如下。STEP1打开“客户价值数据.xlsx”素材文件（配套资源:\素材文件\第8章\客户价值数据.xlsx），在G1单元格中输入“时间间隔/天”，选择G2:G61单元格区域，在编辑框中输入“=TODAY()-F2”，按【Ctrl+Enter】组合键得到时间间隔数据，如右图所示。8.3.4细分客户价值STEP2选择C63单元格，输入“平均值”，按【Tab】键确认输入并选择D63单元格，在编辑框中输入“=AVERAGE(D2:D61)”，按【Ctrl+Enter】组合键计算所有客户的平均交易次数，结果如下图所示，以此数据为标准评价RFM模型中的交易频率指标。STEP3按相同方法依次统计所有客户的平均交易金额和平均交易时间间隔，结果如上图所示，以这两个数据为标准评价RFM模型中的交易金额指标和最近一次交易时间指标。8.3.4细分客户价值STEP4在H1、I1、J1单元格中分别输入“R”“F”“M”，用于存放时间间隔、交易频次和交易金额3个指标的评价结果。STEP5选择H2:H61单元格区域，在编辑框中输入“=IF(G2>$G$63,"低","高")”，表示将每位客户的交易时间间隔数据与平均交易时间间隔作比较，如果大于平均交易时间间隔则评价为“低”，如果小于或等于平均交易时间间隔则评价为“高”，按【Ctrl+Enter】组合键返回结果，如上图所示。8.3.4细分客户价值STEP6选择I2:I61单元格区域，在编辑框中输入“=IF(D2>=$D$63,"高","低")”，表示将每位客户的交易次数数据与平均交易次数作比较，大于或等于平均交易次数则评价为“高”，如果小于平均交易次数则评价为“低”，按【Ctrl+Enter】组合键返回结果，如下图所示。STEP7选择J2:J61单元格区域，在编辑框中输入“=IF(E2>=$E$63,"高","低")”，表示将每位客户的交易金额数据与平均交易金额作比较，大于或等于平均交易金额则评价为“高”，如果小于平均交易金额则评价为“低”，按【Ctrl+Enter】组合键返回结果，如上图所示。8.3.4细分客户价值STEP8在K1单元格输入“价值细分”，再利用IF函数并结合RFM中每个指标的评价结果来细分客户的价值类型。如下图所示。STEP9以A1:K61单元格区域数据为数据源，按前文所述方法，在新工作表中创建数据透视表，将“价值细分”字段添加到“行”列表中，将“姓名”字段添加到“值”列表中，此时数据透视表将统计不同价值客户的人数，如上图所示。8.3.4细分客户价值STEP10按前文所述方法，在数据透视表的基础上创建数据透视图，类型为柱形图，删除图例，将图表标题修改为“不同价值的客户数量”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“价值细分”和“人数/人”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，将数据系列的填充颜色设置为“绿色”，在数据系列外侧添加数据标签，调整图表尺寸至合适，效果如下图所示。8.3.4细分客户价值STEP11在数据系列上单击鼠标右键，在弹出的快捷菜单中选择【排序】/【降序】命令，调整数据系列的显示顺序，结果如下图所示。由下图可知，在该特定期间内，属于“重要价值”类型的客户数量最多，这类客户的RFM模型3个指标表现都很高，说明企业在客户维护、日常运营及商品销售等方面都有不错的成绩。同时，属于“重要发展”价值和“重要挽留”价值的客户数量较少，需要企业进一步改善运营策略，将属于“一般价值”和“一般保持”价值的客户发展为重要客户，如通过促销、打折等方式提高“一般保持”价值客户的交易金额，可以将其发展为“重要保持”价值的客户。8.1案例要求内容导航8.4.1分析男装企业的客户画像

8.4.2借助智谱清言分析男装企业的客户价值8.2案例准备8.3案例操作8.4综合实训8.5课后练习8.4.1分析男装企业的客户画像客户画像可以展现客户的社会属性、偏好特征、消费行为等特性，能够为企业分析客户信息和挖掘客户价值提供帮助。【实训背景】某男装企业需要全面了解特定期间内客户的性别、年龄、所在地、购物次数、购物金额的分布情况，现需要利用收集到的性别、年龄、年龄阶段、所在地、购物次数、购物总金额等数据完成分析客户画像的工作。【操作要求】使用数据透视表和数据透视图分析客户的性别、年龄、所在地、购物次数和购物金额的分布情况。8.4.1分析男装企业的客户画像（1）使用IF函数对年龄和购物总金额进行分级；

（2