Truy vấn mẫu cho đối tượng dựa trên dữ liệu BigQuery

30/11/2019

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 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 chi tiết cụ thể trong dữ liệu của bạn, ví dụ như 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.

Các truy vấn này sử dụng SQL chuẩn, vì vậy hãy đảm bảo bạn chọn tùy chọn đó trước khi chạy truy vấn.

Hiện tại, dữ liệu đối tượng này chỉ nhằm cung cấp thông tin, chứ không thể hành động đượ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.

 

Bài viết này sẽ đề cập đến các vấn đề sau đây:

Người mua

  /*
  Purchasers = users who have logged either in_app_purchase or ecommerce_purchase.
  */
SELECT
  COUNT(DISTINCT user_id)
FROM
  /* PLEASE REPLACE WITH YOUR TABLE NAME */
  `YOUR_TABLE.events_*`
WHERE
  (event_name = 'in_app_purchase' OR event_name = 'ecommerce_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

  /*
  Audience of N-Day Active Users = users who have logged at least one user_engagement event in the last N days.
*/
SELECT
  COUNT(DISTINCT user_id)
FROM
  /* PLEASE REPLACE WITH YOUR TABLE NAME */
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  /* 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

  /*
  N-Day Inactive Users = users in the last M days who have not logged a user_engagement event in the last N days where M > N.
*/
SELECT
  COUNT(DISTINCT M_days.user_id)
FROM (
  SELECT
    user_id
  FROM
    /* PLEASE REPLACE WITH YOUR TABLE NAME */
    `YOUR_TABLE.events_*`
  WHERE
    event_name = 'user_engagement'
    /* 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 M_days
/* 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_*`
  WHERE
    event_name = 'user_engagement'
    /* 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 N_days
ON
  M_days.user_id = N_days.user_id
WHERE
  N_days.user_id IS NULL

Người dùng hoạt động thường xuyên

  /*
  Frequently Active Users = users who have logged at least one user_engagement event on N of the last M days where M > N.
*/
SELECT
  COUNT(DISTINCT user_id)
FROM (
  SELECT
    user_id,
    COUNT(event_date)
  FROM (
    SELECT
      user_id,
      event_date,
      COUNT(*)
    FROM
      /* PLEASE REPLACE WITH YOUR TABLE NAME */
     `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* 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 */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2)
  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

  /*
  Highly Active Users	= users who have been active for more than N minutes in the last M days where M > N.
*/
SELECT
  COUNT(DISTINCT user_id)
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,
    T.event_params
  WHERE
    event_name = 'user_engagement'
    /* User engagement in the last M = 10 days */
    AND 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 hút được

  /*
  Acquired Users = users who were acquired via some Source/Medium/Campaign.
*/
SELECT
  COUNT(DISTINCT user_id)
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

  SELECT
  COUNT(DISTINCT user_id)
FROM
  /* PLEASE REPLACE WITH YOUR TABLE NAME */
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'first_open'
  /* Cohort: open 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 '(direct)' medium */
  AND traffic_source.source = '(direct)'
  /* PLEASE REPLACE YOUR DESIRED DATE RANGE */
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131'

* Nguồn: Google Analytics