MYSQL對遊戲用戶付費行為分析

09-04

MYSQL對遊戲用戶付費行為分析

來自專欄猴子聊數據分析21 人贊了文章

數據來源：某SLG類型手機遊戲用戶註冊7日內的遊戲數據

遊戲玩家付費金額預測大賽-競賽信息-DC競賽

數據大小：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_testGROUP BY DATE(register_time)

在3月10日迎來了一次註冊的高峰，3月13日又迎來一次註冊的小高峰。但兩次高峰過後每天的註冊人數相較於高峰前並沒有明顯增長，可見這兩次活動只是帶來了短期的註冊高峰，對於遊戲的整體熱度並沒有很大提升。

每日付費人數分布：

SELECT DATE(register_time),COUNT(DISTINCT user_id)FROM tap_fun_testWHERE pay_count != 0GROUP BY DATE(register_time)

付費玩家佔比：

SELECT test1.paying_user ,test1.paying_user/test2.total_user AS rate_for_payingFROM(SELECT COUNT(user_id) AS paying_userFROM tap_fun_testWHERE 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_minutesFROM tap_fun_testORDER BY avg_online_minutesLIMIT 207232,2

下四分位數為0

SELECT avg_online_minutesFROM tap_fun_testORDER BY avg_online_minutesLIMIT 414466,2

中位數為2

SELECT avg_online_minutesFROM tap_fun_testORDER BY avg_online_minutesLIMIT 621700,2

上四分位數為5

可見有75%的玩家7日內的平均在線時間在5分鐘左右，猜測是只有第一天登陸了約5*7=35分鐘左右，玩家流失的可能性很大。但是由於沒有更詳細的登陸數據，無法對玩家的流失程度進行分析。

付費用戶在線信息：

SELECT AVG(avg_online_minutes)FROM tap_fun_testWHERE pay_count!=0

SELECT COUNT(user_id)FROM tap_fun_testWHERE pay_count!=0

SELECT avg_online_minutesFROM tap_fun_testWHERE pay_count!=0ORDER BY avg_online_minutesLIMIT 4886,2

SELECT avg_online_minutesFROM tap_fun_testWHERE pay_count!=0ORDER BY avg_online_minutesLIMIT 9774,1

SELECT avg_online_minutesFROM tap_fun_testWHERE pay_count!=0ORDER 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_rateFROM(SELECT COUNT(user_id) AS paying_userFROM tap_fun_testWHERE pay_count!=0) AS test1,(SELECT COUNT(user_id) AS active_userFROM tap_fun_testWHERE 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_rateFROM(SELECT COUNT(user_id) AS paying_user,DATE(register_time) AS time1FROM tap_fun_testWHERE pay_count!=0GROUP BY DATE(register_time)) AS test1,(SELECT COUNT(user_id) AS active_user,DATE(register_time) AS time2FROM tap_fun_testWHERE avg_online_minutes>=30GROUP BY DATE(register_time)) AS test2WHERE test1.time1=test2.time2

ARPPU：

SELECT test1.paying_user,test2.paying_price,test2.paying_price/test1.paying_user AS ARPPUFROM(SELECT COUNT(user_id) AS paying_userFROM tap_fun_testWHERE pay_count!=0) AS test1,(SELECT SUM(pay_price) AS paying_priceFROM tap_fun_test) AS test2

平均每付費用戶收入28.5元。

ARPU：

SELECT test1.active_user,test2.paying_price,test2.paying_price/test1.active_user AS ARPUFROM(SELECT COUNT(user_id) AS active_userFROM tap_fun_testWHERE avg_online_minutes>=30) AS test1,(SELECT SUM(pay_price) AS paying_priceFROM 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_usageFROM 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_usageFROM tap_fun_testWHERE 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_usageFROM tap_fun_testWHERE 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_usageFROM tap_fun_test

高端玩家士兵損耗率：

一般玩家士兵損耗率：

高端玩家各類士兵損耗率：

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_usageFROM tap_fun_testWHERE 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_usageFROM tap_fun_testWHERE 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_usageFROM tap_fun_testWHERE 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_usageFROM tap_fun_testWHERE avg_online_minutes>30 AND bd_stronghold_level < 10

高端玩家的加速券的使用率明顯高於一般玩家，玩家對建築加速券的需求量較高，對治療加速券的需求較低。