Các ví dụ truy vấn BigQuery thường gặp – P1

Trong dự án, để theo dõi hành vi người dùng, google cung cấp cho chúng ta công cụ firebase analytics bao gồm đủ các yêu cầu mà 1 hệ thống vừa và nhỏ cần. Tuy nhiên vẫn có 1 số chỉ số mà muốn truy vấn từ firebase rất phức tạp, như lấy thông tin theo riêng từng quốc gia, chỉ số của user theo các event tự định nghĩa… hay đơn giản là export dữ liệu để làm báo cáo.

Những vấn đề trên đều được giải quyết với BigQuery. Nhiệm vụ của bạn là cần cấu hình để import dữ liệu từ Firebase vào BigQuery và thực hiện các truy vấn trên đấy. Dưới đây là 1 số truy vấn mà mình thường sử dụng.

Tỉ lệ giữ chân User trong 7 ngày

SELECT install_date, country, SUM(CASE WHEN days_since_install = 0 THEN users ELSE 0 END) as day_0, SUM(CASE WHEN days_since_install = 1 THEN users ELSE 0 END) as day_1, SUM(CASE WHEN days_since_install = 2 THEN users ELSE 0 END) as day_2, SUM(CASE WHEN days_since_install = 3 THEN users ELSE 0 END) as day_3, SUM(CASE WHEN days_since_install = 4 THEN users ELSE 0 END) as day_4, SUM(CASE WHEN days_since_install = 5 THEN users ELSE 0 END) as day_5, SUM(CASE WHEN days_since_install = 6 THEN users ELSE 0 END) as day_6, SUM(CASE WHEN days_since_install = 7 THEN users ELSE 0 END) as day_7
FROM ( SELECT geo.country as country, DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) AS install_date, DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_realdate, DATE_DIFF(DATE(TIMESTAMP_MICROS(event_timestamp)), DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)), day) AS days_since_install, COUNT(DISTINCT user_pseudo_id) AS users FROM `gemmob-mobile.analytics_177165243.events_*` WHERE event_name = 'user_engagement' AND platform = 'ANDROID' AND app_info.id = 'com.brounceballz.breakernumberpuzzle' AND (_TABLE_SUFFIX BETWEEN '20190810' AND '20190814') GROUP BY install_date, event_realdate, days_since_install, country )
GROUP BY install_date , country
HAVING day_0 > 0
ORDER BY install_date ASC 

Truy vấn này có thể thêm tùy biến như tính thêm tỉ lệ giữ chân từ khi cài đặt đến D14, D30, D60… hay tính cho riêng theo từng quốc gia khu vực riêng.

Truy vấn người chơi Thắng – Thua

SELECT level , sum(win) as win , sum(lose) as lose , sum(firstWin) as firstWin
FROM( SELECT (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = "level" LIMIT 1) AS level, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "result" AND value.int_value = 1 LIMIT 1) AS win, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "result" AND value.int_value = 0 LIMIT 1) AS lose, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "firstWin" AND value.int_value = 1 LIMIT 1) AS firstWin FROM `gemmob-mobile.analytics_177165243.events_*` as e WHERE app_info.id = 'com.airstrikesquadron.galaxyshooteralien' AND platform = 'ANDROID' AND event_name = 'ingame' )
GROUP BY level ORDER BY level ASC

Thời gian chơi trung bình của User theo từng quốc gia

SELECT country , date, COUNT(distinct uid) as users, (SUM(engagement_time_msec)/(1000 * 60))/COUNT(distinct uid) avg_min_per_user, cast(SUM(engagement_time_msec)/(1000 * 60) as Int64) total_engagement_time_min
FROM( SELECT geo.country as country, _TABLE_SUFFIX as date, user_pseudo_id as uid, event_timestamp timestamp_micros, user_first_touch_timestamp first_open_timestamp_micros, max(case when event_dim_params.key = 'engagement_time_msec' then event_dim_params.value.int_value end) engagement_time_msec FROM `overdrive-project.analytics_161082718.events_*`, UNNEST(event_params) as event_dim_params WHERE event_name = 'user_engagement' AND app_info.id = 'com.shadowbattle.overdrive.legend' AND geo.country IN ('Thailand', 'Indonesia', 'Malaysia', 'Philippines', 'United States') AND (_TABLE_SUFFIX BETWEEN '20191028' AND '20191109') GROUP BY geo.country , _TABLE_SUFFIX, event_timestamp, user_first_touch_timestamp, uid
)
GROUP BY country , date

Tính số lượng User chơi các Level và tỉ lệ thắng thua

SELECT level, count(distinct user_pseudo_id) as total , sum(win) as win , sum(lose) as lose , sum(firstWin) as firstWin
FROM( SELECT (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = "level" LIMIT 1) AS level, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "result" AND value.int_value = 1 LIMIT 1) AS win, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "result" AND value.int_value = 0 LIMIT 1) AS lose, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "firstWin" AND value.int_value = 1 LIMIT 1) AS firstWin, e.user_pseudo_id FROM `gemmob-mobile.analytics_177165243.events_*` as e WHERE app_info.id = 'com.airstrikesquadron.galaxyshooteralien' AND platform = 'ANDROID' AND event_name = 'ingame' AND (_TABLE_SUFFIX BETWEEN '20190711' AND '20190718')
)
GROUP BY level ORDER BY level ASC

Bài tiếp theo mình sẽ hướng dẫn các bạn về sử dụng BigQuery để tính toán các chỉ số liên quan đến chiến dịch quảng cáo. Các chỉ số này đều được Firebase ghi nhận nhưng mà rất khó theo dõi qua Firebase.