MySQL礼品电商用户行为分析

项目介绍 ：使用 Kaggle 的 E-Commerce 数据集。 该数据集基于一个英国礼品电商公司 13 个月的真实交易数据。通过用户消费行为分析， 建立 RFM 模型进行用户分层， 针对性维护高价值用户， 实现精细化用户运营管理。

负责内容 ： 1. 使用 SQL 语法 select/alter/update/create 等对数据集进行清洗加工，实现缺失值、异常值和重复值处理，日期格式转换与清洗后数据新表建立。

本项目基于一个英国电商公司从2010年12月1日到2011年12月9日的真实交易数据集。该电商主要销售的商品是各类礼品， 主要客户是来自不同国家的的分销商。本文主要使用SQL语法分析月度消费趋势、个体消费情况、RFM用户分层、用户生命周期、计算复购率与回购率等关键指标，并使用Power BI实现数据可视化。希望从数据中更深入的了解用户消费行为，为商家运营提供洞察。

利用数据探索用户消费趋势，为商家制定营销策略提供分析及建议，并利用RFM模型实现客户精细化管理。

这部分主要包括无关字段的删除，缺失值检查，异常值检查、重复值检查与日期格式统一。

处理完成后共有532621条数据。

接着看下数据是否有缺失值。

上图说明客户编号存在134697条缺失，需与业务确认缺失原因，由于本次分析已客户为主体，故缺失客户编码的数据删除。

清洗后数据共397924条。

其中产品数量和订单时间无异常，但产品单价存在为0的异常值，这类商品可能是赠品，不应该算入用户主动消费行为中，故删除。

清洗后数据共397884条。

最终清洗后数据共392690条。

将订单时间列字符串转化为统一的sql日期格式，方便使用日期函数进行 *** 作。

预处理完成如下：

上图可知，消费金额从2010年12月到2011年8月比较稳定，呈波动上升趋势；2011年8月开始明显上升，在11月达到最高值约110+万，12月却迅速回落。

上图可知，产品消费数量在2011年8月之前沿平均约35万件的位置来回波动，从8月开始上升，在11月达到峰值约67万件，12月回落至不到30万件的位置。

上图可知，消费次数总体大于消费人数，走势基本相同。2011年8月前月均消费约1200次，月均消费人数约900人。在8月两变量开始提升后，到11月均达到峰值，消费次数约为2600，消费人数约为1600。

上图可知，人均消费金额总体大于订单均价，走势基本相同。2011年1月两者均出现小高峰，可能与活动有关。2011年2月到2011年9月较平稳，分别约为450和650元。两变量峰值均出现在2011年12月，分别约为850和670元。

上图可知，超过90%的用户来自英国，且贡献近82%的销售金额。值得一提的是，查看原始数据发现，荷兰和爱尔兰用户数仅有12个，但消费金额却排名第二和三，占有6%。说明荷兰和爱尔兰存在采购量极大的大客户需重点关注。另外，有9个国家只有1个客户，建议衡量投入产出比，适当放弃一些地区，集中优势在英国。进一步看下各国家人均订单量和人均消费量。

上图可知，人均消费量TOP5分别来自爱尔兰、荷兰、新加坡、澳大利亚和瑞典。说明这些地区大客户较多，而英国小客户较多。

去除几个极大值后，我们发现，用户消费金额集中在0~500元，有约1750名用户，占比约40%。

上图中横坐标表示累计用户数量，纵坐标表示累计消费金额百分比。分析可知，50%的用户仅贡献约10%的消费金额，80%的用户贡献约25%的消费金额，也就是说存在20%高价值客户贡献约75%的金额，基本符合消费二八定律。集中精力拓展与这20%高价值客户的合作，比分散精力对所有客户更值得。排名4000以后的300余名用户（不到10%）则贡献了约60%的消费金额，这部分是最需要维系的大客户。

上图可知，用户首购时间最多集中在2010年12月上半月，每日均值大于50人；从2011年1月开始首购人数保持平稳，每日均值约为15人。可能10年12月份因该电商新成立在引流期有优惠引入了大量新用户，但之后新用户流入减少。后续新用户能维持在一定水平，是运营平稳的表现。

上图可知，从2010年12月到2011年9月用户最后一次购买稳定集中在15人左右；而从9月开始大幅上升，12月最高值达到100+人。时间越长，流失部分用户也是比较正常的情况。在新用户大量增加的前期没有大量流失，且大量用户最后一次购买集中在最后的统计日期，说明大多数用户近三个月有购买记录，仍处于活跃购买状态，并没有在引入后快速流失，是比较理想的。

上图可知，从2010年12月开始至2011年6月，新用户占比持续下降，用户流失风险高；直到2011年7月有所回升，可与业务人员确认原因，7月至12月趋于稳定，基本维持在25%左右。

RFM模型包含三个关键维度，最近一次消费 (Recency)、消费频率(Frequency)和消费金额 (Monetary)。使用这三个维度对用户进行分类，可以将用户分为2x2x2=8种类型，之后根据每类用户的消费特征进行精细化运营。

接下来依次求R值、F值、M值。

给RFM模型打分的方法大概有两种，1. 选取分位数打分。 2. 依据行业和业务经验设定阈值。这里选择分位数法，由于SQL不方便进行RFM打分分层，这里使用python计算分位数。

通过pd.cut方法，将用户分层并打上标签，这里我用的分层方法是python中的quantile函数。

将R、F、M分别打分（分层）。

接下来，求出R、F、M的均值，以均值为界，将每个变量分为高低两维度（大于等于均值取1，小于均值取0）。

生成用户分层表

上图分析发现，所有客户中重要价值客户最多，占总数的35.73%；总体来说是一个不错的情况，因为这类型客户的价值最高，应努力提高该类型客户的满意度，增加留存量。其次是流失客户21.99%，新到客户14.91%，重要挽留客户8.78%，重要发展客户7.98%和重要唤回客户7.51%。另外，不难看出在近一年的交易中，有21.99%是处于流失边缘的客户。为什么会流失？哪些地区的流失情况比较严重？流失的客户都有哪些特点？都是需要引起注意的问题。

上图可见，贡献最高金额和订单数的是重要价值客户，金额690万，订单数1550个远超其他客户。重要唤回客户贡献金额位列第二，为60万，这类客户是高频次和高金额的用户，将其恢复购买可以带来可观的收益，流失原因需要结合业务情况和订单进一步研究。

流失客户和新到客户呈现高订单数低消费金额的情况，说明新客户很多是被优惠活动引入，而流失用户可能包含一些羊毛用户。

条形图按消费人数正向排序，总体来看，重要价值客户都保持在四分之一到二分之一的比例，活跃客户群较稳定，维系此类客户可以提供VIP待遇，提升用户满意度。

英国本土要注意流失客户数量较多，对于该国策略可以采取保持重要价值客户，并找到客户流失原因，减少重要挽回向流失用户转化。对于德国来说重要挽留客户第二多，挽留客户有较高流失风险，要主动联系用户并明确流失原因，再次激活用户购物。同时加大新客户引入。法国情况基本与英国本土相同。

· 重要价值客户（111）目标是让其一直留存下来，可以提供专项客服，个性化增值服务等。

· 重要唤回客户（011）目标是唤回近期消费，可以推出活动，精准提供所需资源，推送消息召回等。

· 重要发展客户（101）目标提高消费频次，可以推荐其他产品，提供积分计划或每日任务等。

· 重要挽留客户（001）目标是让其回归再次消费并了解其不继续购买的原因，可以使用电话或短信等联系，调查原因，弥补不足。

· 新到客户（100）目标是提高其消费兴趣，可以通过活动营销，提供社群互动，砍价等策略留住用户。

· 一般客户（010）属于容易流失的群体，一般维持。可以使用赠送优惠券、推动活动信息等方法与客户重新联系。

· 潜力客户（110）目标挖掘其消费潜力，可以多推荐价值更高且用户喜好的产品。

· 流失客户（000）较大概率不是目标客户，可以选择性放弃。

用户生命周期分布受一次性购买用户（用户生命周期0天）影响比较严重，故排除一次性购买用户（共有1548名，占36%）重作图。

总体上用户平均生命周期131天，中位数93天。最大值为373天，最小值0天。

复购率：单位时间内购买次数大于1的人数与当月总购买人数比值。

回购率：单位时间内曾购买用户在下一单位时间内再次购买的人数与单位时间内曾购买用户比值。

（这里由于2011年12月的数据仅有9天，考虑到可能影响整体结果，故不包括12月的复购率与2011年11月和12月的回购率）

上图可知，月度复购率均值为23%，根据《精益数据分析》中的理论，目前此电商处于用户混合模式，在新用户转化和老用户留存、复购上的应该做到平均分配精力和资源。前期复购率较低可能因为初创推广期大量新用户涌入，造成分母被持续扩大。

上图所知，平均次月回购率达到了39%，总体维持在一个不错的水平。该电商复购率与回购率越高，反映消费者对品牌的忠诚度就越高。

消费金额及数量 ：从2010年12月到2011年8月比较稳定，呈波动上升趋势。2011年8月开始明显上升，在11月达到最高值，12月迅速回落。

消费次数及人数 ：消费次数总体大于消费人数，走势基本相同。2011年8月前月均消费约1200次，月均消费人数约900人。在8月两变量开始提升后，到11月均达到峰值，消费次数约为2600，消费人数约为1600。

地域分析 ：发现超过90%的用户来自英国，且贡献近82%的销售金额。但荷兰和爱尔兰存在采购量极大的大客户需重点关注。有9个国家只有1个客户，建议衡量投入产出比，适当放弃。

累计消费金额分析 ：累计金额曲线发现20%高价值客户贡献约75%的金额，基本符合消费二八定律。集中精力拓展与这20%高价值客户的合作，比分散精力对所有客户更值得。

首购与末购分析 ：用户首购时间最多集中在2010年12月上半月，每日均值大于50人；从2011年1月开始首购人数保持平稳，每日均值约为15人。从2010年12月到2011年9月用户最后一次购买稳定集中在15人左右；而从9月开始大幅上升，12月最高值达到100+人。

新老用户分析 ：从2010年12月开始至2011年6月，新用户占比持续下降，用户流失风险高；直到2011年7月有所回升，7月至12月趋于稳定，基本维持在25%左右。

单量、金额与人数分析 ：8种客户中，重要价值客户订单量、消费金额和人数均位列榜首；而重要唤回客户的消费金额排第二位，但订单量和人数均较落后；流失客户人数和订单量排第二，但消费金额仅排第五。

地域分析 ：总体来看，重要价值客户都保持在四分之一到二分之一的比例，活跃客户群较稳定。英国本土要注意流失客户数量较多，对于该国策略可以采取保持重要价值客户，并找到客户流失原因，减少重要挽回向流失用户转化。对于德国来说重要挽留客户第二多，挽留客户有较高流失风险，要主动联系用户并明确流失原因，再次激活用户购物。同时加大新客户引入。法国情况基本与英国本土相同。

营销策略 ：

· 重要价值客户（111）目标是让其一直留存下来，可以提供专项客服，个性化增值服务等。

· 重要唤回客户（011）目标是唤回近期消费，可以主动联系召回（推送、邮件、公众号等），给予优惠券等。

· 重要发展客户（101）目标提高消费频次，可以推荐其他产品，提供积分计划或每日任务等。

· 重要挽留客户（001）目标是让其回归再次消费并了解其不继续购买的原因，可以使用电话或短信等联系，调查原因，弥补不足。

· 新到客户（100）目标是提高其消费兴趣，可以通过活动营销，提供社群互动，砍价等策略留住用户。

· 一般客户（010）属于容易流失的群体，一般维持。可以使用赠送优惠券、推动活动信息等方法与客户重新联系。

· 潜力客户（110）目标挖掘其消费潜力，可以多推荐价值更高且用户喜好的产品。

· 流失客户（000）较大概率不是目标客户，可以选择性放弃。

一次性购买用户占36%，排除这部分影响后，用户平均生命周期131天，中位数93天。最大值为373天，最小值0天。

复购率 ：平均月度复购率为23%，根据《精益数据分析》中的理论，目前此电商处于用户混合模式，在新用户转化和老用户留存、复购上的应该做到平均分配精力和资源。前期复购率较低可能因为初创推广期大量新用户涌入，造成分母被持续扩大。

回购率 ：平均次月回购率达到39%，总体维持在一个不错的水平。该电商复购率与回购率越高，反映消费者对品牌的忠诚度就越高。

礼品销售技巧一、如何开展业务：

（一）．如何寻找客户。

1. 电话号码簿

2. 报纸、杂志、电视及电台广告

3. 路牌、车体广告

4. 各种展览会

5. 互联网

6. 亲戚朋友介绍

7. 客户介绍

8. 实地收集

（二）．拜访客户的方式。

拜访客户的方式有多种，营销人员可以结合自生的特点选则不同的拜访方式，建议采用1、2两种方式。

1．电话拜访

特点：可以在大量电话拜访中寻找有意向的客户（一天上百个电话），适于初次拜访或预约，但对客户了解有限，不方便深层次切入；

2．陌生拜访

特点：在陌生拜访的过程中较为详细得了解企业的性质、规模、市场分布、运作及具体负责人的有关情况，方便进一步沟通。营销代表在采用此方式时，可以分片区集中拜访，要注意提高时间效率。

3．信函拜访

特点：此方式效果有限，建议少用。

（三）．如何面对不同的角色。

营销代表在拜访客户的过程中，要在最短的时间找到关键的负责人至关重要，当然对于不同的公司，负责人的职务不同，营销代表可采取不同的方式。

1．总经理

有自主意识，能决定是否签单，因此能与总经理直接进行谈判是最理想的，只要营销代表把握时机，很有可能当时签单。

注意事项：a、说话要有条理。一般总经理的时间都是比较紧迫的，

b、要有重点。

C、要有自信。不要觉得在老总面前，就显得底人一等，实际上在几年以前，他可能也和你一样是打工者。要深信，在不久的将来，您将和他一样，坐在大班台前面。

2．部门经理（办公室主任）

略有自主意识，但不是决定者，能决定找谁做，但决定不了啥时做，做多少，价钱也定不了。

注意事项：加强感情交流与他交成朋友。因他们的主观意识较老总强，一旦他不认可你，你将很难成交。始终要记住做业务就是做人，只要人做好了，一切就变得容易了。

3．技术部经理（同上）

注意事项： a、（同上）

b、采用拜师学艺法，当你被问到不懂的问题，你可以说：“我是做业务的，在技术这方面，您是专家，我还需要向你学习┈┈”

4．秘书

具体经办人，只是准备材料。但大公司也可起到经理的作用。

5．前台

只是你的敲门人，要想法绕过她，但是又要得到她的帮助。

（四）．如何约见客户。

注意事项：a、要锁定时间由客户选择，而不能说：“你有没有时间？”

b、电话里不要谈的太多，最好要引起他的好奇。

1、先生/女士，你看，在电话里我们也表述得不是很清楚，这样吧，我们抽

个时间当面谈，好吗？那你是今天下午，还是明天上午有空？

2、先生/女士，我们当面谈，好不好，给我十五分钟时间，我相信我们的服

务一定会给你带来收获的！

3、先生/女士，我也知道已有很多家找过您们了，但是，您如果能给我十五

分钟，您会发现我们正好是您们所要找的。

4、先生/女士。我知道您很忙，但是您如果做的话，肯定是要找一家最好的，

给我们一次机会，您也有个比较，我相信不会让你浪费时间的。

（五）．见客户前的准备。

在见客户之前做好各项准备工作。

1．思想准备

一般来说，思想准备必须在出发拜访之前（前一天晚上）完成，思想准备应该包括所拜访客户的行程计划。一个有效的思想准备一般包括以下几个方面：

（1）、分析客户背景情况

一般分析客户背景情况需从以下几方面着手：

a)、公司的性质、规模、市场分布及运作情况；

b)、公司可能存在的问题；

c)、客户对互联网的认知程度如何；是否已有网站；效果如何；

d)、客户所处行业地基本情况；有哪几家较为出名的企业，上网及公司运作情况如何；

e)、所见人是否具体负责人；具体的职务；各人及家人情况；是否了解互联网；

f)、有无竞争对手在联系；是哪一家；

g)、分析竞争对手的情况；价位如何；

（2）、设定具体拜访目标

通过对客户完全的背景情况分析，得出这个客户存在的问题和机会后，你就可以开始设定拜访这个客户的具体目标了。（它是你完成客户拜访中至关重要的一步）。它包括：

a)、即时目标

即时目标即为见客户当次目标。每一个目标应是具体的、现实而又乐观的，同时是可以衡量的。

一般来说，第一次见客户需达到以下目标：

? 全面了解客户的基本情况。

? 全面了解所见人的基本情况。

? 给所见人一个良好的印象，且建立良好的友谊，下一次见面，就象老朋友一样。

? 有无竞争对手在联系。

? 全面介绍公司的实力及服务。

? 针对客户的具体情况，提出建设性方案、拟拓朴图。

? 尽力能一次性促使成交。

对于多次拜访的客户，每一次拜访之前都需设定即时目标，如签单、与客户沟通感情等。

b)、长期目标

并不是每一次见客户都能一次性成交，因此，要做好长期打算，如找到相关具体负责人，与相关负责人交成朋友等。

（3）、准备具体推销主题

有了拜访目标，就需针对客户的情况，准备具体的推销主题，

一般推销主题包括以下几方面：

? 域名

? 网站可促进公司的销售。

? 网站可树立企业形象，提升品牌的价值。

? 方便与国内外客户交流。

? 节约营销成本。

? 获取需求及竞争对手的信息。

? 网站推广的重要性——建议享受我公司的会员服务。

2．销售工具的准备

? 公司资料（包括宣传品、相关剪报、价格表）。

（六）．拜访过程。

1．沟通、交流。

拜访客户时，不可能一见面就进入正题，必须有一个沟通、交流的过程。

这一环节至关重要，处理得好与否，将直接影响整个拜访的效果。沟通、交流的目的包括以下几个方面：

? 加深与客户的感情交流，给对方留下良好的印象。

? 调节气氛，轻松进入主题。

? 了解客户的基本情况，了解所见人的基本情况。

（1）交换名片

a．双手递上，食指弯曲与大拇指夹住片递上，将名片的名字指给对方，并自报家门，如“佰惠恒润商务礼品网。请多多关照。”之类的礼貌用语。

b．空手时要用双手接受。

C．接受后马上过目记住对方的名字，不能随便瞟一眼或看都不看一眼就放在一边。

d．碰到难读的名字或难念的字时马上虑心请教：“对不起，请问这个字怎么读？”对方非但不会轻视你，反而很高兴帮助你。

e．如果同时接受几张名片，千万记住，哪张名片是哪个人的。

f．聊到高兴时，不要把文件或纸巾压在对方名片上。

g．如果没有得到对方名片，不可理解为“他不愿给我名片”，而应请求对方“你介不介意给我张名片”。如对方说名片刚用完，可以请对方在我的便签簿下留个姓名和电话。

（2）所谈的话题

所谈的话题：气候、季节、赚钱的事、新闻、时事、衣食、住、行、娱乐、嗜好、旅行、运动、家人、家庭、对方了解感兴趣的话题、称赞的话。

（3）避讳的话题

1）不谈政治。

2）关于球赛分输赢的话题，一定要先了解清以后再说。

3）顾客颇为忌讳的缺点和优点。

4）不景气、没钱等。

5）竟争对手坏话。

6）上司、同事、邻居、单位的坏话。

7）别的客户的秘密。

8）不要过分自夸。

2．介绍公司

（1）要突出公司的文化及背景

（2）要突出公司的实力

3．介绍产品及服务

4．针对客户的情况推荐礼品。

6．把握促成签单的时机

人的心思是无法掩饰的，总会通过语言或行为表现出来。营销代表在访问时要留心观察。一般来说，以下所述为顾客购买欲望起动的时候：

（1）口头信号

a．讨价还价、要求价格下浮时

b．询问产品的材质规格等

c．询问产品的促销效果，目前为哪些客户带来较好的利益时

d．询问制作周期时

（2）行为上的购买信号

a．不停地翻阅公司的资料时

b．要求到公司参观，参观是表现出对公司有浓厚的兴趣时。

c．开始与第三者商量时。

d．表现出兴奋的表情时。

E．身体向前倾斜，不断点头向前倾，微笑。

7．促使客户作出最后决定。

签约时的注意事项：

1）小心说闲话，以免前功尽弃，不能轻易让价。

2）尽可能在自己的权限内决定事情，实在不行，则打电话请示经理批准，一定要让客户感到你已经尽自己最大努力帮助客户争取最多的利益。

3）不露出过于高兴或得高兴过分的表情。

4）设法消除对方不安心理，让其觉得是最好的选择。

5）尽可能早点告辞。

6）不能与客户争论——到了最后阶段,而不可因客户的挑剔言论而与其争论。

7）立即提出打款，不要担心提到款会把单丢掉，没有到款，即使签单也没用。

（七）．建议成交。

1、既然一切都定下来了，那我们就签个协议吧！

2、您是不是在付款方式上还有疑问呢？

3、您是不是还有什么疑问，还要向人咨询吗？

4、我们先签个协议吧，我也开始准备下面的工作，好早点安排工厂生产。

5、如果现在签协议的话，您觉得我们还有哪些工作要做？

6、你希望您们的产品什么时间入库，如果您们要求很快的话，我们就得赶快做了，譬如签协议、准备资料等。

---