数据来源:某SLG类型手机游戏用户注册7日内的游戏数据
数据大小:828934条数据
字段解释:
理解字段:该表格的主键为user_id
该游戏充值主要获得以下几种物品:资源类(木头、石头、象牙、肉、魔法等),士兵类(勇士、驯兽师、萨满等),加速卷类(通用加速、建筑加速、科研加速、训练加速、治疗加速等)
在线时长被命名为avg_online_minutes,且一部分用户该字段的值为小于一的小数,可以理解为该在线时长为此用户七天平均的在线分钟数
各种物品的获得数量,根据对数据的观察,该字段存在很多值为0的记录。然后对游戏试玩发现这些物品可以免费获得很多,因此猜测这些获得次数理解为付费后购买获得的数量
主要游戏方式:
分析目的:
根据已有数据对该游戏7日内的游戏玩家付费行为进行分析。
一、玩家注册信息
总注册人数:
SELECT COUNT(DISTINCT user_id)
FROM tap_fun_test
共有82W+条的游戏用户数据,7天内共有828934个用户注册。
每日注册人数分布:
SELECT DATE(register_time),COUNT(DISTINCT user_id)
FROM tap_fun_test
GROUP BY DATE(register_time)
在3月10日迎来了一次注册的高峰,3月13日又迎来一次注册的小高峰。但两次高峰过后每天的注册人数相较于高峰前并没有明显增长,可见这两次活动只是带来了短期的注册高峰,对于游戏的整体热度并没有很大提升。
每日付费人数分布:
SELECT DATE(register_time),COUNT(DISTINCT user_id)
FROM tap_fun_test
WHERE pay_count != 0
GROUP BY DATE(register_time)
付费玩家占比:
SELECT test1.paying_user ,test1.paying_user/test2.total_user AS rate_for_paying
FROM
(SELECT COUNT(user_id) AS paying_user
FROM tap_fun_test
WHERE pay_count!=0) AS test1,
(SELECT COUNT(user_id) AS total_user
FROM tap_fun_test) AS test2
七天之内付费的玩家有19549人,付费人数占注册总人数的2.36%。
二、用户在线时长信息
SELECT AVG(avg_online_minutes)
FROM tap_fun_test
首先我们知道了共有828934条数据,求得下四分位数、中位数、上四分位数的位置分别为207233.75,414467.5,621701.25
SELECT avg_online_minutes
FROM tap_fun_test
ORDER BY avg_online_minutes
LIMIT 207232,2
下四分位数为0
SELECT avg_online_minutes
FROM tap_fun_test
ORDER BY avg_online_minutes
LIMIT 414466,2
中位数为2
SELECT avg_online_minutes
FROM tap_fun_test
ORDER BY avg_online_minutes
LIMIT 621700,2
上四分位数为5
可见有75%的玩家7日内的平均在线时间在5分钟左右,猜测是只有第一天登陆了约5*7=35分钟左右,玩家流失的可能性很大。但是由于没有更详细的登陆数据,无法对玩家的流失程度进行分析。
付费用户在线信息:
SELECT AVG(avg_online_minutes)
FROM tap_fun_test
WHERE pay_count!=0
SELECT COUNT(user_id)
FROM tap_fun_test
WHERE pay_count!=0
SELECT avg_online_minutes
FROM tap_fun_test
WHERE pay_count!=0
ORDER BY avg_online_minutes
LIMIT 4886,2
SELECT avg_online_minutes
FROM tap_fun_test
WHERE pay_count!=0
ORDER BY avg_online_minutes
LIMIT 9774,1
SELECT avg_online_minutes
FROM tap_fun_test
WHERE pay_count!=0
ORDER BY avg_online_minutes
LIMIT 14661,2
下四分位数为31,中位数为84,上四分位数为191.平均数为135.7898
将平均在线时长大于30分钟的玩家定为活跃玩家。
将平均在线时长大于30分钟,且要塞登记不低于10级的玩家定位高端玩家;将平均在线时长大于30分钟,而要塞等级低于10级的玩家定位一般玩家。
三、计算付费指标
付费率:
SELECT test1.paying_user,test2.active_user,test1.paying_user/test2.active_user AS pay_user_rate
FROM
(SELECT COUNT(user_id) AS paying_user
FROM tap_fun_test
WHERE pay_count!=0) AS test1,
(SELECT COUNT(user_id) AS active_user
FROM tap_fun_test
WHERE avg_online_minutes>=30) AS test2
付费率为30.87%,即付费人数占活跃人数的30.87%
每日付费率波动:
SELECT test1.time1,test1.paying_user,test2.active_user,test1.paying_user/test2.active_user AS pay_user_rate
FROM
(SELECT COUNT(user_id) AS paying_user,DATE(register_time) AS time1
FROM tap_fun_test
WHERE pay_count!=0
GROUP BY DATE(register_time)) AS test1,
(SELECT COUNT(user_id) AS active_user,DATE(register_time) AS time2
FROM tap_fun_test
WHERE avg_online_minutes>=30
GROUP BY DATE(register_time)) AS test2
WHERE test1.time1=test2.time2
ARPPU:
SELECT test1.paying_user,test2.paying_price,test2.paying_price/test1.paying_user AS ARPPU
FROM
(SELECT COUNT(user_id) AS paying_user
FROM tap_fun_test
WHERE pay_count!=0) AS test1,
(SELECT SUM(pay_price) AS paying_price
FROM tap_fun_test
) AS test2
平均每付费用户收入28.5元。
ARPU:
SELECT test1.active_user,test2.paying_price,test2.paying_price/test1.active_user AS ARPU
FROM
(SELECT COUNT(user_id) AS active_user
FROM tap_fun_test
WHERE avg_online_minutes>=30) AS test1,
(SELECT SUM(pay_price) AS paying_price
FROM tap_fun_test
) AS test2
平均每用户收入8.8元。
目前较好的手游ARPU超过5元;一般的手游ARPU在3~5元之间;ARPU低于3元则说明表现较差。
可见该手游的盈利能力较好。
四、付费习惯分析
资源类使用率:
SELECT SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value) AS resource_reduce_value,
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_add_value,
SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value)/
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_usage
FROM tap_fun_test
高端玩家资源利用率:
SELECT SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value) AS resource_reduce_value,
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_add_value,
SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value)/
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家资源利用率:
SELECT SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value) AS resource_reduce_value,
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_add_value,
SUM(wood_reduce_value+stone_reduce_value+ivory_reduce_value+meat_reduce_value+magic_reduce_value)/
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
高端玩家各类资源使用率:
SELECT SUM(wood_reduce_value + stone_reduce_value + ivory_reduce_value + meat_reduce_value + magic_reduce_value)/
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_usage,
SUM(wood_reduce_value)/SUM(wood_add_value) AS wood_usage,
SUM(stone_reduce_value)/SUM(stone_add_value) AS stone_usage,
SUM(ivory_reduce_value)/SUM(ivory_add_value) AS ivory_usage,
SUM(meat_reduce_value)/SUM(meat_add_value) AS meat_usage,
SUM(magic_reduce_value)/SUM(magic_add_value) AS magic_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家各类资源使用率:
SELECT SUM(wood_reduce_value + stone_reduce_value + ivory_reduce_value + meat_reduce_value + magic_reduce_value)/
SUM(wood_add_value+stone_add_value+ivory_add_value+meat_add_value+magic_add_value) AS resource_usage,
SUM(wood_reduce_value)/SUM(wood_add_value) AS wood_usage,
SUM(stone_reduce_value)/SUM(stone_add_value) AS stone_usage,
SUM(ivory_reduce_value)/SUM(ivory_add_value) AS ivory_usage,
SUM(meat_reduce_value)/SUM(meat_add_value) AS meat_usage,
SUM(magic_reduce_value)/SUM(magic_add_value) AS magic_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
可以看出高端玩家和低端玩家在象牙使用率上有很大的差距,整体来看木头使用率最高,象牙的使用率最低。木头资源对于玩家来说比较重要,而象牙资源可能相对来说没那么重要。
士兵类损耗率:
SELECT SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value) AS soldier_reduce_value,
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_add_value,
SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value)/
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_usage
FROM tap_fun_test
高端玩家士兵损耗率:
SELECT SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value) AS soldier_reduce_value,
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_add_value,
SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value)/
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家士兵损耗率:
SELECT SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value) AS soldier_reduce_value,
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_add_value,
SUM(infantry_reduce_value+cavalry_reduce_value+shaman_reduce_value)/
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
高端玩家各类士兵损耗率:
SELECT SUM(infantry_reduce_value + cavalry_reduce_value + shaman_reduce_value)/
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_usage,
SUM(infantry_reduce_value)/SUM(infantry_add_value) AS infantry_usage,
SUM(cavalry_reduce_value)/SUM(cavalry_add_value) AS cavalry_usage,
SUM(shaman_reduce_value)/SUM(shaman_add_value) AS shaman_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家各类士兵损耗率:
SELECT SUM(infantry_reduce_value + cavalry_reduce_value + shaman_reduce_value)/
SUM(infantry_add_value+cavalry_add_value+shaman_add_value) AS soldier_usage,
SUM(infantry_reduce_value)/SUM(infantry_add_value) AS infantry_usage,
SUM(cavalry_reduce_value)/SUM(cavalry_add_value) AS cavalry_usage,
SUM(shaman_reduce_value)/SUM(shaman_add_value) AS shaman_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
同时求出标准差,可见高端玩家相对于一般玩家使用的兵种更加平衡,从整体来看,高端玩家的士兵损失率明显低于一般玩家。
高端玩家加速券使用率:
SELECT SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value) AS acceleration_reduce_value,
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_add_value,
SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value)/
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家加速券使用率:
SELECT SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value) AS acceleration_reduce_value,
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_add_value,
SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value)/
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
高端玩家各类加速券使用率:
SELECT SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value)/
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_usage,
SUM(general_acceleration_reduce_value)/SUM(general_acceleration_add_value) AS general_acceleration_usage,
SUM(building_acceleration_reduce_value)/SUM(building_acceleration_add_value) AS building_acceleration_usage,
SUM(reaserch_acceleration_reduce_value)/SUM(reaserch_acceleration_add_value) AS reaserch_acceleration_usage,
SUM(training_acceleration_reduce_value)/SUM(training_acceleration_add_value) AS training_acceleration_usage,
SUM(treatment_acceleration_reduce_value)/SUM(treatment_acceleraion_add_value) AS treatment_acceleraion_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level >= 10
一般玩家各类加速券使用率:
SELECT SUM(general_acceleration_reduce_value+building_acceleration_reduce_value+reaserch_acceleration_reduce_value+training_acceleration_reduce_value+treatment_acceleration_reduce_value)/
SUM(general_acceleration_add_value+building_acceleration_add_value+reaserch_acceleration_add_value+training_acceleration_add_value+treatment_acceleraion_add_value) AS acceleraion_usage,
SUM(general_acceleration_reduce_value)/SUM(general_acceleration_add_value) AS general_acceleration_usage,
SUM(building_acceleration_reduce_value)/SUM(building_acceleration_add_value) AS building_acceleration_usage,
SUM(reaserch_acceleration_reduce_value)/SUM(reaserch_acceleration_add_value) AS reaserch_acceleration_usage,
SUM(training_acceleration_reduce_value)/SUM(training_acceleration_add_value) AS training_acceleration_usage,
SUM(treatment_acceleration_reduce_value)/SUM(treatment_acceleraion_add_value) AS treatment_acceleraion_usage
FROM tap_fun_test
WHERE avg_online_minutes>30 AND bd_stronghold_level < 10
高端玩家的加速券的使用率明显高于一般玩家,玩家对建筑加速券的需求量较高,对治疗加速券的需求较低。