一.背景和目标描述
Airbnb是一个旅行房屋租赁网站,airbnb是一家联系旅游人士和家有空房出租的房主的服务型网站,为用户提供多样的住宿信息。成立于2008年,在2011年其服务增长了800%。其社区平台在191个国家、65,000个城市为旅行者们提供数以百万计的入住选择。(来自百度)
在浏览和参考了网络上该项目的分析后,本文将从用户画像、流量分析和漏斗分析角度进行项目分析,查看各环节转化率,为提高民宿付款成交量提出一些建议。
本文取了部分数据集,其中sessions表的数据取自2014-01-01之后,train_users_2取自2010年之后。
仅做练习找工作用,欢迎交流指正!
二.思维导图
三.数据含义解释
sessions表多为用户行为,而train_users_2表则是用户特征(用户表)。
数据来源:工作台 - Heywhale.com
user_id | 用户ID |
action | 用户行为 |
action_type | 用户行为类型 |
action_detail | 用户行为描述 |
device_type | 设备类型 |
secs_elapsed | 停留时长 |
id | 用户ID |
date_account_created | 账户创建日期 |
timestamp_first_active | 首次活跃时间(可以早于账户创建时间或首次订房时间) |
date_first_booking | 首次订房时间 |
gender | 性别 |
age | 年龄 |
signup_method | 注册方式 |
signup_flow | 注册流程 |
language | 语言 |
affiliate_channel | 付费市场渠道 |
affiliate_provider | 付费市场名称 |
first_affiliate_tracked | 注册前用户接触的第一个市场 |
signup_app | 注册APP |
first_device_type | 首次设备类型 |
first_browser | 首次浏览器 |
country_destination | 目的地国家 |
四.数据清洗
1.导入数据:
使用Navicat导入:在root中‘新建数据库’——在新建好的数据库中选择表——右键‘导入向导’——选择‘CSV文件’——添加文件,之后每个都选择‘下一步’即可。
2.查找重复值
select *
from sessions
group by user_id,action,action_type,action_detail,device_type,secs_elapsed
having count(*)>1;#sessions表可以有重复,但也可能是登记人员失误,这里对重复行不作处理
select *
from train_users_2
group by id,date_account_created,timestamp_first_active,date_first_booking,gender,age,signup_method,
signup_flow,language,affiliate_channel,affiliate_provider,first_affiliate_tracked,signup_app,
first_device_type,first_browser,country_destination
having count(*)>1;#train_users_2表无重复行
3.查找空值
查找空值
select count(1) from sessions;#10567737
select count(user_id),count(action),count(action_type),count(action_detail),count(device_type),count(secs_elapsed)
from sessions;#sessions中除device_type外每列都有空缺值
select count(1) from train_users_2;#213451
select count(id),count(date_account_created),count(timestamp_first_active),count(date_first_booking),
count(gender),count(age),count(signup_method),count(signup_flow),count(language),count(affiliate_channel),
count(affiliate_provider),count(first_affiliate_tracked),count(signup_app),
count(first_device_type),count(first_browser),count(country_destination)
from train_users_2;#train_users_2表中date_first_booking有124543个空缺,age有87990个空缺,first_affiliate_tracked有6065个空缺
4.查看异常值
select min(age),max(age)
from train_users_2;#1岁、2014岁明显不可能
5.异常值处理
异常值处理
--sessions中action_type和action_detail有13200多的空缺,用-unknown-填充,由于原数据较大,其他选择删除空缺值
update sessions
set action_detail='-unknown-'
where action_detail is null;
update sessions
set action_type='-unknown-'
where action_type is null;
delete from sessions
where (user_id is null) or (action is null) or (secs_elapsed is null);
select count(1) from sessions;
#最后sessions表有10333696行
--train_users_2表中date_first_booking空缺应该指未预定,用'0000/00/00'表示,age用0填充,first_affiliate_tracked数据量较少删除
update train_users_2
set date_first_booking = '0000/00/00'
where date_first_booking is null;
update train_users_2
set age =0
where age is null;
delete from train_users_2
where first_affiliate_tracked is null;
select count(1) from train_users_2;
#train_users_2有207386行
--人为挑选年龄在16-70岁的数据,将其他年龄改为0
update train_users_2
set age=0
where id in(
select c.id from
(
select id from train_users_2
where (age<16) or (age>70)
)c);
五.用户画像
通过用户画像,描绘出Airbnb网站受众的特征,有助于网站了解他们的消费者,也有助于后续挖掘商业价值。
性别分布上,在Airbnb网站新用户预定中,女性占到54%,女性比男性人数略多
年龄分布上,25-35岁人群达到了5.5w,接下来是35-45岁的人群,人次为3w之多。55-70岁人数最少。这可能与中青年的高收入和较高的消费需求密切相关,同时当时租房软件正兴起, 更倾向于在旅游出行时选择网络租房。
美国、中国、法国、西班牙、韩国、德国、意大利、俄罗斯、葡萄牙,美国在人数上遥遥领先,因为Airbnb网站是在美国创立的,其次为欧洲地区。网站在本土推广效果较好,可能是刚成立没多久,国际化推广相对不乐观。
人们在选择旅游目的地时倾向于美国,其他国家,法国,意大利等欧洲国家、中国,这与用户地区也比较重合,一部分原因可能是选择了本土游玩,另一部分应用可能是虽然这些国家有着较高的消费力,同时国内旅游资源也较为丰富,吸引游客。
在APP使用上,网页端占到85%,其次苹果系统,安卓和Moweb最少。
在注册方式上,使用basic的用户最多,其次facebook,谷歌也有小部分。
在设备类型上,mac desktop最多,其次是windows desktop,更多用户使用电脑访问网站;iPhone、iPad分别在2w,1.4w用户数,未知渠道、安卓手机等有几千的用户数,部分用户使用苹果产品、手机访问网站。从设备中可以看出,这些消费者的购买力较高。
在首次的浏览器分布中,用户数呈现不均衡的状态,较受欢迎的浏览器有Chrome、Safari、Firefox、未知、IE、Mobile Safari,这些浏览器的用户数超过了平均值,值得关注。同时也有不少浏览器的用户数较低,用户可通过不同的浏览器接触和搜索到Airbnb网站。
--用户性别分布
select distinct gender,count(gender)
from(select gender
from train_users_2
where gender= 'MALE' or gender='FEMALE')a
group by gender;
--用户年龄分布
select
case when age >=16 and age <25 then '16-25'
when age>=25 and age <35 then '25-35'
when age >=35 and age <45 then '35-45'
when age >=45 and age <55 then '45-55'
when age >=55 and age <70 then '55-70'
end as '年龄区间',
count(age) as '用户数'
from train_users_2
group by 1
order by left(年龄区间,2)
;
--用户地区分布
select language,count(language) as lag_cnt
from train_users_2
group by language
order by count(language) desc;
--用户目的地国家分布
select country_destination,count(country_destination) as country_cnt
from train_users_2
where country_destination !='NDF'
group by country_destination
order by count(country_destination) desc;
--用户注册APP分布
select signup_app,count(signup_app) as app_cnt
from train_users_2
group by signup_app
order by app_cnt desc;
--用户注册方式分布
select signup_method,count(signup_method) as method_cnt
from train_users_2
group by signup_method
order by count(signup_method) desc;
--用户首次设备类型分布
select first_device_type,count(first_device_type) as device_cnt
from train_users_2
group by first_device_type
order by count(first_device_type) desc;
--用户首次浏览器分布
select first_browser,count(first_browser) as browser_cnt
from train_users_2
group by first_browser
order by browser_cnt desc;
六.流量分析
网站新增用户数可以体现该网站流量如何,新增付费人数、平均停留时间是用来衡量流量品质优劣的指标,流量结构中有渠道结构,渠道品质需要由渠道流量占比、转化率和roi配合在一起来衡量。
2010年2月到2014年6月,新增用户数呈增长的趋势,在每年的7、8、9月附近达到最高点,之后人数减少,在12月达到最低谷,后用户数回升。猜测与气温有关,夏天温度高,人们旅游意愿较高,而冬天则太寒冷,人们的旅游意愿下降。
付费转化率在每年7、8、9月相对有所下降,同时,逐年呈现下降趋势,新用户数的不断增加使得基数变大,转化率也就有所下降了。
渠道注册量呈现种类多样,但分布不均的状态,注册量在平均值以上的渠道有direct_direct、sem-brand_google、sem-non-brand_google、api_other,这些渠道转化率也在平均值以上,是不错的渠道。有些渠道如other_wayn、sem-non-brand_facebook、content_yandex注册量较低,同时转化率为0,可以适量减少该渠道推广的投放。sem-brand_baidu渠道虽然注册量较少,但转化率最高。
交互网站中未追踪到的注册量最多,其次linked、omg,local_ops最少。marketing网站虽然注册量不多,但转化率最高,local_ops转化率也是最低的。
活跃用户较多的渠道有direct_direct、sem-brand_google、sem-non-brand_google、seo_google,这与注册用户较多的渠道相似。说明这些渠道用户粘性较高。
平均停留时间最多的几个渠道和活跃较多的渠道相比,重合度不高。分析两个相关性,从趋势上看是负相关,但不明显。
交互网站中活跃人数最多的是未追踪,其次omg,linked,和注册情况相符。
--每月新增用户数及新增付费人数、转化率
select
left(date_account_created,7) as '年月',
count(distinct id) as '新增用户数',
count(case when date_first_booking != '0000/00/00' then 1 else null end) as '新增付费人数',
concat(round(count(case when date_first_booking != '0000/00/00' then 1 else null end)/count(distinct id)*100,0),'%') as '付费转化率'
from train_users_2
group by 年月
order by 年月;
--不同推广渠道注册量和转化率
select concat(affiliate_channel,'_',affiliate_provider) as 'affiliate_channel_provider',
count(distinct id) as '渠道注册量',
concat(round(count(case when date_first_booking != '0000/00/00' then 1 else null end)/count(distinct id)*100,0),'%') as '转化率'
from train_users_2
group by affiliate_channel_provider
order by 渠道注册量 desc;
--不同的第一个交互网站带来的注册量和转化率
select first_affiliate_tracked,
count(distinct id) as '初次交互网页注册量',
concat(round(count(case when date_first_booking != '0000/00/00' then 1 else null end)/count(distinct id)*100,0),'%') as '初次交互网页转化率'
from train_users_2
group by first_affiliate_tracked
order by 初次交互网页注册量 desc;
--不同推广渠道活跃用户
#定义用户记录数大于10的为活跃用户
create view active_user as select distinct user_id from sessions group by user_id having count(user_id) >10;
select concat(affiliate_channel,'_',affiliate_provider) as 'affiliate_channel_provider',
count(id) as '渠道活跃用户'
from train_users_2 where id in(select user_id from active_user)
group by affiliate_channel_provider
order by 渠道活跃用户 desc;
--不同渠道平均停留时长
select concat(affiliate_channel,'_',affiliate_provider) as 'affiliate_channel_provider',
round(avg(secs_elapsed),2) as '平均停留时长'
from train_users_2
left join sessions
on train_users_2.id=sessions.user_id
group by affiliate_channel_provider
order by 平均停留时长 desc;
--不同第一个交互网站的活跃用户
select first_affiliate_tracked,count(distinct id) as '网站活跃用户'
from train_users_2 where id in(select user_id from active_user)
group by first_affiliate_tracked
order by 网站活跃用户 desc;
七.漏斗分析
将用户在产品中的行为数据看作是行为路径,有助于分析各环节的转化率,便于精确定位转化率较低的环节,进一步分析该环节下的原因,对症下药,提高最终的成交数量。漏斗分析可以将整个转化过程可视化,帮助我们更好地理解用户行为和需求,从而优化产品和服务。
AARRR模型对应用户生命周期5个阶段,是常用的漏斗分析模型。分别有‘获取’、‘激活’、‘留存’、‘变现’、‘自传播或推荐’。
考虑到是对sessions行为表中用户的各项行为指标进行分析,所以选定了sessions表的所有用户数作为用户总数量;选定在sessions表中出现次数大于10次的为活跃用户;train_user_2中用户ID是唯一且都是注册用户,进而挑选sessions表中的注册用户;在action_detail中有‘’reservations‘’行为的认为是下单用户,有‘’payment_instruments"的认为是支付用户,支付行为2次以上认为是复购用户。
通过各环节的转化,可以发现活跃用户转化到注册用户转化率有66%,说明不少用户可能有相关租房需求,或是旅游打算在随时观望。
注册用户转化为下单用户仅有14%,这一环节流失了大量的客户,可能是网站推送机制存在一些问题,定位不精准、无法满足客户的需求,或是关键字搜索中没有客户需要的产品,中间环节太多导致用户使用体验差,房源数量少预定满了,或是房东信息不完善无法联系信息没有及时更新等等 ,可以查看下具体原因,网站也可以举办优惠活动,在旅游旺季做满减、折扣等,更好的吸引用户下单。
下单用户转化为支付用户为87%,有可能是网络问题,或者支付页面跳转不了。支付用户转化为复购用户的有46%,说明有不少的用户对网站提供的服务是满意的,网站有不错的用户粘性。
select count(distinct id) from train_users_2;#207386
--用户总数量
select count(distinct user_id) from sessions; #133898
--活跃用户
select count(distinct user_id) from active_user;
--注册用户
select count(distinct user_id) from sessions
where user_id in
(select t.id FROM
(select id from train_users_2 )t);
--下单用户
select count(distinct user_id) from sessions
where action_detail='reservations';
--支付用户
select count(distinct user_id) from sessions
where action_detail='payment_instruments';
--复购用户
select count(1)
from(
select distinct user_id from sessions
where action_detail='payment_instruments'
group by user_id
having count(user_id) >=2
)t;
八.总结、建议、看板
总结
1.Airbnb网站用户性别分布均衡,女性偏多;中青年群体较多,有一定的消费能力;多数用户经常使用Chrome、Safari、Firefox、未知、IE、Mobile Safari等浏览器;旅游目的地多倾向于美国和欧洲国家;用户地区多分布在美国、中国、欧洲国家。
2.Airbnb网站在2010到2014年间新用户不断增加,direct_direct、sem-brand_google、sem-non-brand_google、api_other等推广渠道用户注册量多,推广效果好,且用户活跃度高。渠道停留时间和活跃性呈负相关。
3.用户在Airbnb网站的行为路径中,注册到下单的转化人数少,可能是推送机制、关键词搜索、价格、房源等无法满足客户需求。复购用户转化率可观,网站有不错的用户粘性。
建议
1.要注意尽量简化中间环节,方便用户注册、下单和支付。完善推送机制,为不同的客户人群推送需要的产品,提高租房意愿;完善房源信息,对接好和房东的各项安全事宜;可以通过发送短信、邮件向用户推送产品服务唤起沉睡客户,采取会员制、积分,提高复购率;拓展国际市场,随着Airbnb网站各方面的成熟完善,可以进一步拓展在国际上的业务。对高价值的渠道direct_direct、sem-brand_google、sem-non-brand_google、api_other等增加资金投放,对低价值渠道减少推广的资金投入。