Sau khi xuất dữ liệu Firebase của mình sang BigQuery, bạn có thể truy vấn dữ liệu đó cho các đối tượng cụ thể.
Bài viết này cung cấp một số mẫu mà bạn có thể sử dụng làm cơ sở cho truy vấn của mình. Hãy nhớ sửa đổi các truy vấn mẫu để giải quyết các thông tin cụ thể trong dữ liệu của bạn; ví dụ: thay đổi tên bảng và sửa đổi phạm vi ngày.
Các truy vấn này trả về số lượng người dùng trong đối tượng. Thay vào đó, nếu bạn muốn nhận danh sách User-ID trong đối tượng, hãy xóa hàm COUNT () ngoài cùng; ví dụ: COUNT(DISTINCT user_id) --> DISTINCT user_id.
Bạn nhớ chọn SQL chuẩn trước khi chạy một truy vấn vì những truy vấn này sử dụng SQL chuẩn. (Trong BigQuery > Không gian làm việc SQL, hãy nhấp vào Thêm > Cài đặt truy vấn. Trong phần Cài đặt bổ sung > Phương ngữ SQL, hãy chọn Chuẩn.)
Hiện tại, dữ liệu đối tượng này chỉ dùng để tham khảo, chứ không đưa vào bản phân tích được.
Chúng tôi rất muốn biết liệu bạn có thấy các ví dụ về truy vấn này hữu ích không và bạn có muốn truy vấn các loại đối tượng khác không. Bạn có thể trả lời qua yêu cầu tính năng với hỗ trợ của Firebase.
Nội dung trong bài viết này:
Người mua
/**
* Tính toán đối tượng của người mua.
*
* Người mua = người dùng đã ghi lại in_app_purchase hoặc
* purchase.
*/
SELECT
COUNT(DISTINCT user_id) AS purchasers_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name IN ('in_app_purchase', 'purchase')
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
Người dùng hoạt động trong N ngày
/**
* Xây dựng một đối tượng gồm những Người dùng đang hoạt động trong N ngày.
*
* Người dùng đang hoạt động trong N ngày = những người dùng đã ghi lại ít nhất 1 sự kiện có thông số sự kiện
* engagement_time_msec > 0 trong N ngày qua.
*/
SELECT
COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- Pick events in the last N = 20 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
Người dùng không hoạt động trong N ngày
/**
* Xây dựng một đối tượng gồm những Người dùng không hoạt động trong N ngày.
*
* Người dùng không hoạt động trong N ngày = những người dùng trong M ngày qua chưa ghi lại 1
* sự kiện có thông số sự kiện engagement_time_msec > 0 trong N ngày qua,
* trong đó M > N.
*/
SELECT
COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last M = 7 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS MDaysUsers
-- EXCEPT ALL is not yet implemented in BigQuery. Use LEFT JOIN in the interim.
LEFT JOIN
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*`AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last N = 2 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS NDaysUsers
ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
NDaysUsers.user_id IS NULL;
Người dùng hoạt động thường xuyên
/**
* Xây dựng một đối tượng gồm những Người dùng hoạt động thường xuyên.
*
* Người dùng hoạt động thường xuyên = những người dùng đã ghi lại ít nhất 1
* sự kiện có thông số sự kiện engagement_time_msec > 0 trong N/M
* ngày qua, trong đó M > N.
*/
SELECT
COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
(
SELECT
user_id,
COUNT(DISTINCT event_date)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- User engagement in the last M = 10 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
-- PLEASE REPLACE YOUR DESIRED DATE RANGE. For optimal performance
-- the _TABLE_SUFFIX range should match the INTERVAL value above.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1
-- Having engaged in at least N = 4 days.
HAVING COUNT(event_date) >= 4
);
Người dùng hoạt động tích cực
/**
* Xây dựng đối tượng là Người dùng hoạt động tích cực.
*
* Người dùng hoạt động tích cực = người dùng đã hoạt động trong hơn N phút
* trong M ngày qua, trong đó M > N.
*/
SELECT
COUNT(DISTINCT user_id) AS high_active_users_count
FROM
(
SELECT
user_id,
event_params.key,
SUM(event_params.value.int_value)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
-- User engagement in the last M = 10 days.
event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
AND event_params.key = 'engagement_time_msec'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1, 2
HAVING
-- Having engaged for more than N = 0.1 minutes.
SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
);
Người dùng thu nạp được
/**
* Xây dựng đối tượng của Người dùng thu hút được.
*
* Người dùng thu nạp được = người dùng được thu nạp thông qua một Nguồn/Phương tiện/Chiến dịch.
*/
SELECT
COUNT(DISTINCT user_id) AS acquired_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
traffic_source.source = 'google'
AND traffic_source.medium = 'cpc'
AND traffic_source.name = 'VTA-Test-Android'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
Nhóm thuần tập có bộ lọc
/**
* Xây dựng đối tượng là người dùng thu nạp được trong tuần trước
* thông qua các chiến dịch Google, nghĩa là các nhóm thuần tập có bộ lọc.
*
* Nhóm thuần tập được định nghĩa là một nhóm gồm những người dùng thu nạp được trong tuần trước, tức là từ 7-14
* ngày trước. Bộ lọc nhóm thuần tập dành cho những người dùng thu nạp được thông qua một chiến dịch
* trực tiếp.
*/
SELECT
COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name = 'first_open'
-- Cohort: opened app 1-2 weeks ago. One week of cohort, aka. weekly.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
AND event_timestamp <
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
-- Cohort filter: users acquired through 'google' source.
AND traffic_source.source = 'google'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';