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

Tiếp theo bài viết các truy vấn về BigQuery phần 1, hôm nay mình tiếp tục chia sẻ các truy vấn mình đã sử dụng với BigQuery

Truy vấn User theo từng quốc gia

Đây là yêu cầu nhiều nhất từ phía GD. Có thể tùy biến riêng cho từng quốc gia, hoặc 1 nhóm quốc gia với lệnh `where geo.country in ()`, theo từng khoảng thời gian nhất định, cũng như từng nền tảng (android, ios). Kèm thêm điều kiện cho `traffic_source.name` trong truy vấn, ta có thể lấy được thông tin user theo từng user theo từng chiến dịch quảng cáo đang chạy. Từ đó có đánh giá chính xác hơn cho chiến dịch quảng cáo đang chạy để có các điều chỉnh phù hợp hơn.

SELECT country, cast((timestamp_micros - first_open_timestamp_micros)/(1000000 * 60 * 60 * 24) as Int64) user_age_in_days, (SUM(engagement_time_msec)/(1000 * 60))/COUNT(distinct uid) avg_min_per_user, COUNT(distinct uid) users, cast(SUM(engagement_time_msec)/(1000 * 60) as Int64) total_engagement_time_min
FROM ( SELECT geo.country as country, user_pseudo_id as uid, event_timestamp timestamp_micros, user_first_touch_timestamp first_open_timestamp_micros, event_name, app_info.version, max(case when event_dim_params.key = 'engagement_time_msec' then event_dim_params.value.int_value end) engagement_time_msec FROM `gemmob-mobile.analytics_177165243.events_*`, UNNEST(event_params) as event_dim_params WHERE event_name = "user_engagement" AND platform = 'ANDROID' AND app_info.id = 'com.airstrikesquadron.galaxyshooteralien' AND (_TABLE_SUFFIX BETWEEN '20191003' AND '20191010') GROUP BY user_pseudo_id , event_timestamp, user_first_touch_timestamp , event_name, app_info.version,geo.country )
GROUP BY user_age_in_days, country
HAVING user_age_in_days >= 0 AND user_age_in_days < 100
ORDER BY user_age_in_days 

Tính hành động của User với các gói In-app

SELECT name , sum(click) as click , sum(failed) as failed , sum(unavailable) as unavailable, sum(buy_failed) as buy_failed
FROM(
SELECT	(SELECT value.string_value FROM UNNEST(e.event_params) WHERE key = "item_name" LIMIT 1) AS name, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "item_category" AND value.string_value = "click" LIMIT 1) AS click, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "item_category" AND value.string_value = "failed" LIMIT 1) AS failed, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "item_category" AND value.string_value = "unavailable" LIMIT 1) AS unavailable, (SELECT 1 FROM UNNEST(e.event_params) WHERE key = "item_category" AND value.string_value = "buy failed" LIMIT 1) AS buy_failed
FROM `overdrive-project.analytics_161082718.events_*` e
WHERE event_name = 'iap_stats' AND platform = 'ANDROID' AND app_info.id = 'com.gsm.overdrivepremium'	AND (_TABLE_SUFFIX BETWEEN '20191010' AND '20191017')
)
GROUP BY name
ORDER BY name ASC

Lấy thông tin từ các chiến dịch Quảng cáo

Để lấy thông tin từ các chiến dịch quảng cáo, chúng ta truy vấn đến trường traffic_source trong bigQuery

SELECT traffic_source.name , count(distinct e.user_pseudo_id) as total FROM `gemmob-mobile.analytics_177165243.events_*` as e
GROUP BY traffic_source.name ORDER BY total DESC
SELECT traffic_source.medium , count(distinct e.user_pseudo_id) as total FROM `gemmob-mobile.analytics_177165243.events_*` as e
GROUP BY traffic_source.medium ORDER BY total DESC
SELECT traffic_source.source , count(distinct e.user_pseudo_id) as total FROM `gemmob-mobile.analytics_177165243.events_*` as e
GROUP BY traffic_source.source ORDER BY total DESC
SELECT geo.country , count(distinct e.user_pseudo_id) as total FROM `overdrive-project.analytics_161082718.events_20190529` as e
WHERE platform = 'ANDROID' AND app_info.install_source = 'manual_install'
GROUP BY geo.country ORDER BY total DESC 

Tùy vào yêu cầu truy vấn hãy dùng SELECT cho phù hợp, mỗi truy vấn đều mất $ đấy.