《Excel數(shù)據(jù)分析與應(yīng)用》課件-項(xiàng)目6 顧客分析

主講：馬元元西安電子科技大學(xué)出版社項(xiàng)目6顧客分析目

錄項(xiàng)目概述任務(wù)1：每日消費(fèi)顧客數(shù)量分析任務(wù)2：客單價(jià)分析任務(wù)3：顧客復(fù)購(gòu)情況分析任務(wù)4：顧客對(duì)促銷的態(tài)度分析任務(wù)5：高價(jià)值顧客購(gòu)物偏好分析拓展延伸：會(huì)員生日自動(dòng)提醒課后技能訓(xùn)練西安電子科技大學(xué)出版社

項(xiàng)目概述西安電子科技大學(xué)出版社西安電子科技大學(xué)出版社項(xiàng)目背景

“和美家”連鎖超市某分店收集了某周的銷售數(shù)據(jù)，為了掌握顧客的消費(fèi)習(xí)慣和偏好，現(xiàn)在需要對(duì)顧客消費(fèi)行為進(jìn)行分析，從而有針對(duì)性的制定營(yíng)銷策略，提升營(yíng)銷水平。西安電子科技大學(xué)出版社對(duì)顧客購(gòu)物信息進(jìn)行分析后得到的結(jié)果如下：項(xiàng)目演示西安電子科技大學(xué)出版社對(duì)顧客購(gòu)物信息進(jìn)行分析后得到的結(jié)果如下：項(xiàng)目演示西安電子科技大學(xué)出版社會(huì)員生日提醒：項(xiàng)目演示思維導(dǎo)圖西安電子科技大學(xué)出版社思政聚焦—信息泄露事件/item/%E6%A3%B1%E9%95%9C%E9%97%A8/6006333?fr=aladdin事件一：棱鏡門事件事件二：個(gè)人信息泄露事件西安電子科技大學(xué)出版社思政聚焦—信息泄露事件/2016/09/24/VIDEePYIYaheqyMLqSRHtt0W160924.shtml/2018/10/07/VIDEHBYLGmnR5LoYZawu3dZc181007.shtml全社會(huì)呼吁對(duì)個(gè)人信息安全進(jìn)行立法保護(hù)，2021年8月20日，十三屆全國(guó)人大常委會(huì)第三十次會(huì)議表決通過(guò)《中華人民共和國(guó)個(gè)人信息保護(hù)法》。自2021年11月1日起施行。作為大數(shù)據(jù)專業(yè)的學(xué)生，畢業(yè)后會(huì)與大量的各種數(shù)據(jù)打交道，具備良好的道德意識(shí)、法律意識(shí)，恪守誠(chéng)實(shí)守信、遵紀(jì)守法、確保數(shù)據(jù)主體隱私、保護(hù)數(shù)據(jù)安全是必須具備的職業(yè)標(biāo)準(zhǔn)和底線。思考與討論：如何個(gè)人信息泄露有哪些危害，信息泄露與詐騙的關(guān)系，如何防止個(gè)人信息泄露除了要防止個(gè)人信息泄露外，還有那些方面的數(shù)據(jù)需要進(jìn)行安全保護(hù)？西安電子科技大學(xué)出版社思政聚焦—信息泄露事件西安電子科技大學(xué)出版社任務(wù)6.1每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社任務(wù)描述統(tǒng)計(jì)每天進(jìn)店消費(fèi)的顧客數(shù)目，掌握本周顧客的消費(fèi)情況。任務(wù)分析（1）篩選本周有消費(fèi)行為的顧客數(shù)據(jù)（2）統(tǒng)計(jì)每日消費(fèi)顧客數(shù)目（3）繪制人形圖任務(wù)6.1：每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社每日消費(fèi)顧客數(shù)：是指每天進(jìn)店消費(fèi)的顧客數(shù)目，與顧客同一天消費(fèi)的次數(shù)和每次消費(fèi)的數(shù)量和金額無(wú)關(guān)。1、篩選本周有消費(fèi)行為的顧客。（1）選中并復(fù)制“顧客編號(hào)”和“銷售日期”兩列，到新工作表【01一周消費(fèi)顧客】。（2）去除重復(fù)數(shù)據(jù)。任務(wù)6.1：每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社2.每日消費(fèi)顧客數(shù)統(tǒng)計(jì)(1)創(chuàng)建數(shù)據(jù)透視表。（2）計(jì)算每日消費(fèi)顧客數(shù)。（3）格式化數(shù)據(jù)。任務(wù)6.1：每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社3.繪制每日銷售數(shù)量圖表（1）繪制簇狀條形圖。（2）在簇狀條形圖中使用人形。復(fù)制人形圖片，選中簇狀條形圖數(shù)據(jù)系列后直接粘貼即可。任務(wù)6.1：每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社（3）設(shè)置人形寬度。在【填充與線條】選項(xiàng)卡中將【伸展】改為【層疊并縮放】，然后在“Units/Picture”(單位/圖片)文本框中輸入10，即一個(gè)人形代表10個(gè)單位。（4）設(shè)置人形高度。在【系列選項(xiàng)】選項(xiàng)卡中，將【分類間距】設(shè)為80%。3月24日周三進(jìn)店消費(fèi)的顧客最多，其次是3月22日周一和3月28日周日，接下來(lái)是3月23日周二、3月26日周五、3月25日周四，3月27日周六進(jìn)店消費(fèi)顧客最少。任務(wù)6.1：每日消費(fèi)顧客數(shù)量分析西安電子科技大學(xué)出版社任務(wù)6.2客單價(jià)分析西安電子科技大學(xué)出版社任務(wù)描述統(tǒng)計(jì)本周顧客消費(fèi)客單價(jià)，掌握本周顧客的消費(fèi)情況。任務(wù)分析（1）統(tǒng)計(jì)每日銷售總金額（2）計(jì)算每日客單價(jià)（3）繪制客單價(jià)圖表任務(wù)6.2：客單價(jià)分析西安電子科技大學(xué)出版社知識(shí)準(zhǔn)備：客單價(jià)：客單價(jià)是指一定時(shí)間內(nèi)一個(gè)用戶的平均銷售金額。客單價(jià)是一定時(shí)間段的客單價(jià)。若用Pi、Ai、Ci分別表示第i天的客單價(jià)、總的銷售額和用戶數(shù)量，則客單價(jià)的計(jì)算公式為：任務(wù)6.2：客單價(jià)分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)客單價(jià)（1）創(chuàng)建數(shù)據(jù)透視表。（2）統(tǒng)計(jì)每日銷售總額。（3）計(jì)算客單價(jià)。（4）表格格式化。任務(wù)6.2：客單價(jià)分析西安電子科技大學(xué)出版社2.繪制客單價(jià)圖表（1）繪制折線圖。（2）添加并設(shè)置數(shù)據(jù)標(biāo)簽。3月24日周三客單價(jià)最高為48.6，接著是周日和周一，分別為47.6和45.7，周五和周六的客單價(jià)都是43.6，周四客單價(jià)為41.1，周二客單價(jià)最低為32.6。任務(wù)6.2：客單價(jià)分析西安電子科技大學(xué)出版社任務(wù)6.3顧客復(fù)購(gòu)情況分析西安電子科技大學(xué)出版社任務(wù)描述復(fù)購(gòu)率是反應(yīng)企業(yè)銷售情況的有力證據(jù)，計(jì)算所有進(jìn)店顧客的購(gòu)物次數(shù)，統(tǒng)計(jì)消費(fèi)1次、2次、3次、4次、5次及以上用戶的人數(shù)，進(jìn)而掌握本周用戶的復(fù)購(gòu)情況，計(jì)算復(fù)購(gòu)率并繪制顧客購(gòu)物次數(shù)圖表。任務(wù)分析（1）計(jì)算用戶復(fù)購(gòu)率①統(tǒng)計(jì)本周每個(gè)顧客的消費(fèi)次數(shù)②統(tǒng)計(jì)消費(fèi)特定次數(shù)的顧客人數(shù)③計(jì)算復(fù)購(gòu)率（2）繪制顧客購(gòu)物次數(shù)圖表任務(wù)6.3：顧客復(fù)購(gòu)情況分析西安電子科技大學(xué)出版社知識(shí)準(zhǔn)備：復(fù)購(gòu)率：復(fù)購(gòu)率是指購(gòu)買兩次或者兩次以上的用戶人數(shù)占占總用戶人數(shù)的比率，復(fù)購(gòu)率越高，則反應(yīng)出用戶對(duì)品牌的忠誠(chéng)度就越高，反之則越低。若用FR表示復(fù)購(gòu)率，R表示購(gòu)買兩次或者兩次以上的用戶數(shù)量，G表示消費(fèi)者總數(shù)量，則復(fù)購(gòu)率的計(jì)算公式如下：任務(wù)6.3：顧客復(fù)購(gòu)情況分析西安電子科技大學(xué)出版社1.統(tǒng)計(jì)本周每個(gè)顧客的消費(fèi)日期。得到每個(gè)本周有購(gòu)物行為的顧客編號(hào)及其購(gòu)物日期數(shù)據(jù)。2.統(tǒng)計(jì)本周每個(gè)顧客的消費(fèi)次數(shù)（1）創(chuàng)建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。任務(wù)6.3：顧客復(fù)購(gòu)情況分析西安電子科技大學(xué)出版社3.統(tǒng)計(jì)特定購(gòu)物次數(shù)的顧客人數(shù)（1）創(chuàng)建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表。任務(wù)6.3：顧客復(fù)購(gòu)情況分析西安電子科技大學(xué)出版社（3）表格格式化。任務(wù)6.3：顧客復(fù)購(gòu)情況分析（4）計(jì)算顧客的復(fù)購(gòu)率本周顧客的復(fù)購(gòu)率為22.92%。西安電子科技大學(xué)出版社4．繪制顧客復(fù)購(gòu)情況圖表（1）繪制三維餅圖。（2）添加數(shù)據(jù)標(biāo)簽。（3）更改數(shù)據(jù)標(biāo)簽格式。（4）修改圖例位置。（5）修改圖表標(biāo)題。任務(wù)6.3：顧客復(fù)購(gòu)情況分析

本周77%的顧客僅購(gòu)物1次，16%的顧客購(gòu)物2次，5%的顧客購(gòu)物3次，另有2%的顧客購(gòu)物四次及以上。西安電子科技大學(xué)出版社任務(wù)6.4顧客對(duì)促銷的態(tài)度分析西安電子科技大學(xué)出版社任務(wù)描述在新零售的背景下，用戶的消費(fèi)觀念開始逐漸向重服務(wù)，重品牌的方向發(fā)展，但仍對(duì)促銷有很大依賴。部分消費(fèi)者非常關(guān)注商品的促銷情況，更傾向于購(gòu)買促銷的商品，如果某顧客平時(shí)較少購(gòu)買，而在促銷時(shí)集中采購(gòu)，也就是促銷類產(chǎn)品的消費(fèi)金額在其總的消費(fèi)金額中占比較大，則這個(gè)顧客可能就是促銷敏感型顧客。分析超市顧客購(gòu)買促銷商品和非促銷商品的金額占比，可以掌握超市顧客對(duì)促銷的態(tài)度。任務(wù)分析（1）計(jì)算本周促銷和非促銷產(chǎn)品的銷售金額（2）繪制促銷與非促銷銷售額對(duì)比圖任務(wù)6.4：顧客對(duì)促銷的態(tài)度分析西安電子科技大學(xué)出版社1.計(jì)算本周促銷和非促銷產(chǎn)品的銷售金額（1）創(chuàng)建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。（3）格式化表格。任務(wù)6.4：顧客對(duì)促銷的態(tài)度分析西安電子科技大學(xué)出版社2.繪制促銷與非促銷銷售額對(duì)比圖（1）繪制三維餅圖。（2）添加并設(shè)置數(shù)據(jù)標(biāo)簽。（3）修改圖例位置。（4）修改圖表標(biāo)題。本周有24%的銷售額由促銷商品產(chǎn)生，76%的銷售額由非促銷商品產(chǎn)生。任務(wù)6.4：顧客對(duì)促銷的態(tài)度分析西安電子科技大學(xué)出版社任務(wù)6.5高價(jià)值顧客購(gòu)物偏好分析西安電子科技大學(xué)出版社任務(wù)描述無(wú)論是消費(fèi)頻率最高或消費(fèi)金額最大的顧客對(duì)商家而言都是最有價(jià)值的顧客。通過(guò)對(duì)消費(fèi)頻率最高的10個(gè)顧客和對(duì)消費(fèi)金額對(duì)多的10個(gè)顧客的消費(fèi)行為分析，嘗試找出顧客消費(fèi)的規(guī)律，為企業(yè)制定營(yíng)銷方案提供依據(jù)。任務(wù)分析（1）篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)（2）篩選消費(fèi)金額Top10顧客的購(gòu)物信息（3）消費(fèi)頻率Top10顧客的購(gòu)物類別偏好（4）消費(fèi)頻率Top10顧客對(duì)促銷的態(tài)度（5）消費(fèi)金額Top10顧客的購(gòu)物類別偏好（6）消費(fèi)金額Top10的顧客對(duì)促銷的態(tài)度任務(wù)6.5：高價(jià)值顧客購(gòu)物偏好分析西安電子科技大學(xué)出版社知識(shí)準(zhǔn)備:RFM模型是衡量顧客價(jià)值和顧客創(chuàng)利能力的重要工具和手段。在眾多的顧客關(guān)系管理分析模式中，RFM模型是應(yīng)用最廣泛的模型。該模型通過(guò)一個(gè)顧客的最近一次消費(fèi)(Recency)、消費(fèi)頻率(Frequency)以及消費(fèi)金額(Monetary)三項(xiàng)指標(biāo)來(lái)描述該顧客的價(jià)值狀況。消費(fèi)金額和消費(fèi)頻率是指一定時(shí)間范圍內(nèi)的消費(fèi)金額和消費(fèi)頻率。任務(wù)6.5：高價(jià)值顧客購(gòu)物偏好分析Recency最近一次消費(fèi)Frequency消費(fèi)頻率Monetary消費(fèi)金額RFM模型西安電子科技大學(xué)出版社在本項(xiàng)目中，因?yàn)閿?shù)據(jù)所表達(dá)的時(shí)間段較短（1個(gè)星期），顧客的最近一次消費(fèi)日期接近，使用最近一次消費(fèi)日期作為判斷依據(jù)意義不大，所以主要從消費(fèi)頻率和消費(fèi)金額角度進(jìn)行分析，當(dāng)兩個(gè)顧客的消費(fèi)金額或者消費(fèi)頻率相同時(shí)，參考最近一次消費(fèi)數(shù)據(jù)對(duì)顧客進(jìn)行排序。任務(wù)6.5：高價(jià)值顧客購(gòu)物偏好分析西安電子科技大學(xué)出版社統(tǒng)計(jì)消費(fèi)頻率Top10的顧客，先統(tǒng)計(jì)出每個(gè)顧客的消費(fèi)頻率，取前10位，如果有相同次數(shù)的，將最后一次消費(fèi)時(shí)間近的排在前面，如果最后一次消費(fèi)時(shí)間也一樣，則取消費(fèi)金額較大的顧客進(jìn)行分析。1.計(jì)算所有顧客的最近消費(fèi)日期6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社2.計(jì)算每個(gè)顧客本周購(gòu)物金額。6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社3.求消費(fèi)頻率Top10的顧客編號(hào)（1）復(fù)制顧客消費(fèi)頻率（消費(fèi)天數(shù)）信息。復(fù)制到G、H兩列。6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社（2）查找顧客最近消費(fèi)日期。在單元格I2輸入公式“=VLOOKUP(G2,$A$2:$B$446,2,FALSE)”（3）查找顧客消費(fèi)金額。在單元格J2輸入公式“=VLOOKUP(G2,$D$2:$E$446,2,FALSE)”6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社（4）按照消費(fèi)頻率、最近消費(fèi)日期、消費(fèi)總金額對(duì)顧客進(jìn)行排序。（5）復(fù)制出前10條數(shù)據(jù)，格式化表格。消費(fèi)頻率Top10的顧客編號(hào)分別為304、2464、113、400、151、1130、339、260、13、1950。6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社4.得到消費(fèi)頻率Top10的顧客數(shù)據(jù)。使用高級(jí)篩選將消費(fèi)頻率Top10的顧客編號(hào)作為篩選條件即可。6.5.1篩選消費(fèi)頻率Top10顧客的購(gòu)物數(shù)據(jù)西安電子科技大學(xué)出版社統(tǒng)計(jì)消費(fèi)金額最多的10位顧客，可以使用分類匯總先計(jì)算出每個(gè)顧客的消費(fèi)金額，然后再利用排序功能找到消費(fèi)金額Top10的顧客，然后利用篩選功能篩選出這10名顧客的消費(fèi)記錄。1.計(jì)算每位顧客的消費(fèi)金額（1）創(chuàng)建新工作表。（2）分類匯總每個(gè)顧客的消費(fèi)金額。顧客編號(hào)分類。6.5.2篩選消費(fèi)金額Top10顧客的消費(fèi)信息西安電子科技大學(xué)出版社（3）排序得到消費(fèi)金額Top10顧客編號(hào)。得到銷售金額Top10的顧客編號(hào)包括：2372、1256、2533、952、74、2516、2522、2550、2553、260。6.5.2篩選消費(fèi)金額Top10顧客的消費(fèi)信息西安電子科技大學(xué)出版社（4）得到消費(fèi)金額Top10的顧客數(shù)據(jù)。得到銷售金額Top10的顧客編號(hào)包括：2372、1256、2533、952、74、2516、2522、2550、2553、260。6.5.2篩選消費(fèi)金額Top10顧客的消費(fèi)信息西安電子科技大學(xué)出版社1.統(tǒng)計(jì)消費(fèi)頻率Top10顧客各大類商品的消費(fèi)金額（1）創(chuàng)建數(shù)據(jù)透視表。（2）設(shè)置數(shù)據(jù)透視表字段。將【大類名稱】拖動(dòng)到【行】區(qū)域，將【銷售金額】拖動(dòng)到【值】區(qū)域。（3）格式化表格。6.5.3消費(fèi)頻率Top10顧客的消費(fèi)類別偏好消費(fèi)頻率Top10顧客消費(fèi)大類西安電子科技大學(xué)出版社2.繪制三維餅圖分析消費(fèi)頻率Top10的顧客購(gòu)物類別偏好（1）繪制三維餅圖。（2）添加數(shù)據(jù)標(biāo)簽。（3）更改數(shù)據(jù)標(biāo)簽格式。（4）修改圖表標(biāo)題。（5）修改圖例位置。6.5.3消費(fèi)頻率Top10顧客的消費(fèi)類別偏好消費(fèi)頻率Top10顧客消費(fèi)最多的大類是蔬果，占總消費(fèi)額的36%，其次是日配和休閑，分別占20%和15%，糧油占12%，洗化和肉禽分別占9%和5%，酒飲占2%。西安電子科技大學(xué)出版社1.計(jì)算消費(fèi)頻率Top10顧客購(gòu)買促銷和非促銷商品的金額2.繪制消費(fèi)頻率Top10顧客促銷敏感度圖表消費(fèi)頻率Top10的顧客，花費(fèi)17%的金額來(lái)購(gòu)買促銷商品，花費(fèi)83%的金額來(lái)購(gòu)買非促銷商品。6.5.4消費(fèi)頻率Top10顧客對(duì)促銷的態(tài)度西安電子科技大學(xué)出版社使用6.5.4相同方法得到消費(fèi)金額Top10顧客消費(fèi)大類偏好數(shù)據(jù)和圖表消費(fèi)金額Top10的顧客消費(fèi)最多的是酒飲，占總金額的34%，其次是日配和糧油，分別占24%和15%，洗化占10%，蔬果占8%，休閑占4%，肉禽占3%，熟食占2%。6.5.5消費(fèi)金額Top10顧客的消費(fèi)類別偏好西安電子科技大學(xué)出版社使用6.5.5同樣方法分析消費(fèi)金額Top10的顧客對(duì)促銷的態(tài)度消費(fèi)金額Top10的顧客，花費(fèi)16%的金額來(lái)購(gòu)買促銷商品，花費(fèi)84%的金額來(lái)購(gòu)買非促銷商品。6.5.6

消費(fèi)金額Top10的顧客對(duì)促銷的態(tài)度西安電子科技大學(xué)出版社拓展延伸會(huì)員生日自動(dòng)提醒西安電子科技大學(xué)出版社任務(wù)描述“和美家”某連鎖超市秉承客戶至上原則，為了提高會(huì)員的幸福指數(shù)，增加會(huì)員粘性，會(huì)在會(huì)員生日推出額外優(yōu)惠或者準(zhǔn)備禮物。為了不漏掉