大數(shù)據(jù)應(yīng)用基礎(chǔ)（微課版）-課件 項(xiàng)目三 MySQL數(shù)據(jù)查詢操作 實(shí)訓(xùn)五 分類客戶價值VIP

大數(shù)據(jù)應(yīng)用基礎(chǔ)信息學(xué)院MySQL數(shù)據(jù)查詢操作05實(shí)訓(xùn)五分類客戶價值https:///item/RFM%E6%A8%A1%E5%9E%8B/7070365?fr=aladdinRFM模型拆解https:///video/BV1Cz4y1D78J?spm_id_from=333.337.search-card.all.click使用Excel實(shí)現(xiàn)RFM模型課前自學(xué)任務(wù)：https:///video/BV1WY41177wi?spm_id_from=333.337.search-card.all.clickRFM模型學(xué)習(xí)目標(biāo)知識目標(biāo)11.了解RFM分析模型；2.明確R、F、M計(jì)算規(guī)則；3.了解視圖的作用；4.明確創(chuàng)建和使用視圖的方法。2能力目標(biāo)3素質(zhì)目標(biāo)4思政目標(biāo)1.能夠確定RFM模型的適用場景；2.能夠計(jì)算R、F、M的值；3.能夠創(chuàng)建和使用視圖；4.能夠整合運(yùn)用R、F、M值。自主學(xué)習(xí)能力；邏輯表達(dá)思維能力；問題分析方法。1.通過RFM模型，強(qiáng)調(diào)大數(shù)據(jù)分析中的化繁為簡的思維方法；2.通過挖掘客戶價值，將數(shù)據(jù)背后的信息集中和提煉，讓“數(shù)據(jù)會說話、能決策”，理解過去的“經(jīng)驗(yàn)決策”逐漸向“數(shù)據(jù)決策”轉(zhuǎn)變。認(rèn)知分類客戶價值（RFM模型）0101認(rèn)知分類客戶價值（RFM模型）主要是運(yùn)用一些分析方法從大量的數(shù)據(jù)中發(fā)現(xiàn)未知且具有價值信息的過程，常用方法包括：RFM分析、聚類分析、因子分析、對應(yīng)分析等。在銷售過程中需要根據(jù)客戶的屬性進(jìn)行歸類，通過探索性分析數(shù)據(jù)對用戶進(jìn)行歸類，以便于在營銷活動的推送及轉(zhuǎn)化過程中更加精準(zhǔn)化從而獲得更大的收益。RFM模型是分析客戶行為的最流行、最簡單、最有效的客戶分類方法之一。RFM模型探索性分析客戶關(guān)系管理要將數(shù)據(jù)背后的信息集中和提煉，研究對象內(nèi)在規(guī)律，由過去的“經(jīng)驗(yàn)決策”逐漸向“數(shù)據(jù)決策”轉(zhuǎn)變，已經(jīng)逐漸成為眾多企業(yè)經(jīng)營者和管理者的共識。（1）數(shù)據(jù)分析可以糾正不當(dāng)?shù)纳a(chǎn)和營銷措施。（2）數(shù)據(jù)分析可以對計(jì)劃進(jìn)度實(shí)時跟蹤（3）數(shù)據(jù)分析可以使管理者及時了解成本的管制情況，掌握員工的思想動態(tài)。（4）完善的數(shù)據(jù)管理和分析可以實(shí)現(xiàn)生產(chǎn)流程的科學(xué)管理，最大限度地降低生產(chǎn)管理風(fēng)險?！緮?shù)據(jù)會說話】1Recency最近一次消費(fèi)2Frequency消費(fèi)頻率3Monetary消費(fèi)金額根據(jù)美國數(shù)據(jù)庫營銷研究所的研究，客戶數(shù)據(jù)庫中有3個奇特的要素，這3個要素構(gòu)成了數(shù)據(jù)分析有效的指標(biāo)：01認(rèn)知分類客戶價值（RFM模型）01認(rèn)知分類客戶價值（RFM模型）12345+根據(jù)消費(fèi)頻率指標(biāo)，我們把客戶分成五等分，這個五等分分析相當(dāng)于是一個“忠誠度的階梯”(loyaltyladder)，讓消費(fèi)者順著階梯逐步演進(jìn)。新客戶潛力客戶老客戶成熟客戶忠實(shí)客戶理論125類實(shí)際8組將三個指標(biāo)都細(xì)分出5份，這樣就能夠細(xì)分出5x5x5=125類用戶，再根據(jù)每類用戶精準(zhǔn)營銷……顯然125類用戶已超出普通人腦的計(jì)算范疇了，更別說針對125類用戶量體定制營銷策略。實(shí)際運(yùn)用上，我們只需要把每個維度做一次兩分即可，這樣在3個維度上我們依然得到了8組用戶。01認(rèn)知分類客戶價值（RFM模型）01認(rèn)知分類客戶價值（RFM模型）客戶分類R(間隔)F(頻次)M(金額)類型重要價值客戶高高高優(yōu)質(zhì)客戶，需要重點(diǎn)服務(wù)重要發(fā)展客戶高低高需重點(diǎn)維持重要保持客戶低高高需喚醒召回重要挽留客戶低低高需挽留一般價值客戶高高低需要挖掘一般發(fā)展客戶高低低新用戶，有推廣價值一般保持客戶低高低貢獻(xiàn)不大，一般維持一般挽留客戶低低低即將流失將有限資源集中于高價值客戶，采用不同的營銷策略和服務(wù)方案，實(shí)現(xiàn)企業(yè)利潤最大化目標(biāo)。計(jì)算近期購買行為（R）02計(jì)算近期購買行為（R）客戶視圖與訂單表連接查詢，按客戶分類，MAX聚集函數(shù)計(jì)算到客戶最后一次交易的日期，現(xiàn)在是2021年1月1日，DATEDIFF函數(shù)計(jì)算出最后的一次交易與現(xiàn)在日期的差值天數(shù)。SELECTv_customers.cust_id,cust_name,DATEDIFF('2021-01-01',MAX(order_date))daysFROMordersJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameORDERBYDATEDIFF('2021-01-01',MAX(order_date));數(shù)據(jù)為近3年的交易數(shù)據(jù)，從差異1天到1093天不等，需要選擇1個值為區(qū)分高低的參數(shù)。計(jì)算近期購買行為（R）經(jīng)過各種嘗試，選擇1年即365天為近期購買行為的高低區(qū)分參數(shù)值。SELECTv_customers.cust_id,cust_name,DATEDIFF('2021-01-01',MAX(order_date))daysFROMordersJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameHAVINGDATEDIFF('2021-01-01',MAX(order_date))<365ORDERBYDATEDIFF('2021-01-01',MAX(order_date));計(jì)算近期購買行為（R）-

二分法在子查詢中進(jìn)行訂單表查詢，按客戶編號分組，MAX()聚集函數(shù)計(jì)算到客戶最后一次交易的日期，DATEDIFF()函數(shù)計(jì)算出最后的一次交易與分析日期的差值天數(shù)。在外層查詢中計(jì)算交易間隔的平均天數(shù)。SELECTROUND(AVG(天數(shù)),1)R值FROM(SELECTcust_id客戶編號,DATEDIFF('2021-01-01',MAX(order_date))天數(shù)FROMordersGROUPBYcust_id)recency;計(jì)算購買的總體頻率（F）03計(jì)算購買的總體頻率（F）客戶視圖與訂單表連接查詢，按客戶分類，COUNT聚集函數(shù)計(jì)算客戶交易的訂單數(shù)。SELECTv_customers.cust_id,cust_name,COUNT(order_id)onumsFROMordersJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameORDERBYCOUNT(DISTINCTorder_id)DESC;訂單數(shù)只有1和2的區(qū)分，需要選擇1個值為區(qū)分高低的參數(shù)。選擇2為購買頻率高，1為購買頻率低。SELECTv_customers.cust_id,cust_name,COUNT(order_id)onumsFROMordersJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameHAVINGCOUNT(DISTINCTorder_id)>=2ORDERBYCOUNT(DISTINCTorder_id)DESC;計(jì)算購買的總體頻率（F）計(jì)算客戶的交易訂單數(shù)，取其平均交易訂單數(shù)作為F值。SELECTROUND(COUNT(cust_id)/count(DISTINCTcust_id),1)F值FROMorders;計(jì)算購買的總體頻率（F）-二分法計(jì)算購買金額（M）04計(jì)算購買金額（M）客戶視圖、訂單表、訂單明細(xì)表3表連接查詢，按客戶分類，SUM聚集函數(shù)計(jì)算出客戶交易金額。SELECTv_customers.cust_id,cust_name,SUM(item_quantity*item_price)amounsFROMorderitemsJOINordersONorders.order_id=orderitems.order_idJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameORDERBYSUM(item_quantity*item_price)DESC;從差異20元到20多萬元不等，需要選擇1個值為區(qū)分高低的參數(shù)。經(jīng)過各種嘗試，選擇10萬元為高低區(qū)分參數(shù)值。SELECTv_customers.cust_id,cust_name,SUM(item_quantity*item_price)amounsFROMorderitemsJOINordersONorders.order_id=orderitems.order_idJOINv_customersONorders.cust_id=v_customers.cust_idGROUPBYv_customers.cust_id,cust_nameHAVINGSUM(item_quantity*item_price)>=100000ORDERBYSUM(item_quantity*item_price)DESC;計(jì)算購買金額（M）計(jì)算客戶的交易金額，取其平均交易訂單數(shù)作為M值。SELECTROUND(SUM(item_quantity*item_price)/COUNT(DISTINCTcust_id),2)M值FROMorderitemsiJOINordersoONo.order_id=i.order_id;計(jì)算購買金額（M）-二分法計(jì)算客戶的RFM值進(jìn)行客戶分類05計(jì)算客戶的RFM值進(jìn)行客戶分類依據(jù)前3步的計(jì)算合并，合并后需客戶視圖、訂單表、訂單明細(xì)表3表連接查詢，但需顯示出所有客戶的RFM；將設(shè)定的參數(shù)值作為casewhen語句的判斷條件賦予1或0的高低的值，R的參數(shù)設(shè)定為365天、F的參數(shù)設(shè)定為2次，M的參數(shù)設(shè)定為10萬元。

注：R、F、M的參數(shù)設(shè)定應(yīng)當(dāng)根據(jù)前面分析、計(jì)算的實(shí)際結(jié)果來確定。CREATEORREPLACEVIEWv_crm_rfmASSELECTv_customers.*,DATEDIFF('2021-01-01',MAX(order_date))days,COUNT(DISTINCTorders.order_id)onums,SUM(item_quantity*item_price)amouns,CASEWHENDATEDIFF('2021-01-01',MAX(order_date))<=365THEN1ELSE0ENDR,CASEWHENCOUNT(DISTINCTorders.order_id)>=2THEN1ELSE0ENDF,CASEWHENSUM(item_quantity*item_price)>=100000THEN1ELSE0ENDMFROMv_customersJOINordersONorders.cust_id=v_customers.cust_idJOINorderitemsONorders.order_id=orderitems.order_idGROUPBYv_customers.cust_id,cust_name;計(jì)算客戶的RFM值進(jìn)行客戶分類視圖就是封裝查詢，通常是為簡化SELECT語句表達(dá)，視圖的用法基本同數(shù)據(jù)表【化繁為簡的思想】。創(chuàng)建好視圖后查詢視圖。SELECT*FROMv_crm_rfm;計(jì)算客戶的RFM值進(jìn)行客戶分類利用任務(wù)1建立的視圖，查詢各類客戶名單，用RFM值作為條件進(jìn)行查詢：--重要價值用戶（R高F高M(jìn)高,111)SELECT*FROMv_crm_rfmWHERER=1ANDF=1ANDM=1;--重要發(fā)展用戶（R高F低M高,101)SELECT*FROMv_crm_rfmWHERER=1ANDF=0ANDM=1;--重要保持用戶（R低F高M(jìn)高,011)SELECT*FROMv_crm_rfmWHERER=0ANDF=1ANDM=1;--重要挽留用戶（R低F低M高,001)SELECT*FROMv_crm_rfmWHERER=0ANDF=0ANDM=1;--一般價值用戶（R高F高M(jìn)低,110)SELECT*FROMv_crm_rfmWHERER=1ANDF=1ANDM=0;--一般發(fā)展用戶（R高F低M低,100)SELECT*FROMv_crm_rfmWHERER=1ANDF=0ANDM=0;--一般保持用戶（R低F高M(jìn)低,010)SELECT*FROMv_crm_rfmWHERER=0ANDF=1ANDM=0;--一般挽留用戶（R低F低M低,000)SELECT*FROMv_crm_rfmWHERER=0ANDF=0ANDM=0;計(jì)算客戶的RFM值進(jìn)行客戶分類查詢各類客戶名單，如重要價值客戶名單，查詢結(jié)果如下：計(jì)算客戶的RFM值進(jìn)行客戶分類計(jì)算8類客戶的數(shù)量及其占比，用RFM值作為條件進(jìn)行查詢：SELECTSUM(CASEWHENR=1ANDF=1ANDM=1then1else0end)重要價值用戶數(shù),SUM(CASEWHENR=1ANDF=0ANDM=1then1else0end)重要發(fā)展用戶數(shù),SUM(CASEWHENR=0ANDF=1ANDM=1then1else0end)重要保持用戶數(shù),SUM(CASEWHENR=0ANDF=0ANDM=1then1else0end)重要挽留用戶數(shù),SUM(CASEWHENR=1ANDF=1ANDM=0then1else0end)一般價值用戶數(shù),SUM(CASEWHENR=1ANDF=0ANDM=0then1else0end)一般發(fā)展用戶數(shù),SUM(CASEWHENR=0ANDF=1ANDM=0then1else0end)一般保持用戶數(shù),SUM(CASEWHENR=0ANDF=0ANDM=0then1else0end)一般挽留用戶數(shù),ROUND(SUM(CASEWHENR=1ANDF=1ANDM=1then1else0end)/SUM(1)*1