数据来源：某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
高端玩家的加速券的使用率明显高于一般玩家，玩家对建筑加速券的需求量较高，对治疗加速券的需求较低。
570
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
