אחרי שמייצאים את נתוני Firebase ל-BigQuery, ניתן ליצור שאילתות לגבי הנתונים האלה המתמקדות בקהלים ספציפיים.
במאמר הזה מוצגות מספר תבניות שיכולות לשמש כבסיס ליצירת שאילתות. חשוב לזכור לשנות את השאילתות לדוגמה ולהגדיר אותן בהתאם לצורכי הנתונים הספציפיים שלכם. למשל, יש לשנות את שמות הטבלאות ואת טווחי התאריכים לפי הצורך.
השאילתות האלה מחזירות את מספר המשתמשים בקהל. אם רוצים לקבל במקום זאת את רשימות פרטי ה-User ID בקהל, צריך להסיר את הפונקציה COUNT() החיצונית ביותר. למשל: COUNT(DISTINCT user_id) יהפוך ל-DISTINCT user_id.
השאילתות האלה משתמשות ב-SQL סטנדרטי, לכן חשוב לבחור באפשרות הזאת לפני שמריצים את השאילתה. (ב-BigQuery > SQL Workspace (סביבת עבודה של SQL), לוחצים על More > Query Settings (עוד > הגדרות שאילתה). בקטע Additional Settings > SQL dialect (הגדרות נוספות > ניב SQL) בוחרים באפשרות Standard (רגיל).)
נכון לעכשיו, נתוני הקהל מוצגים לידיעה בלבד, ולא ניתן להשתמש בהם לנקיטת פעולה.
נשמח לשמוע אם השאילתות לדוגמה היו שימושיות עבורכם, ואם יש סוגי קהלים נוספים שהייתם רוצים ליצור עבורם שאילתות. כדי להשיב, ניתן לשלוח בקשה לתכונה לתמיכה של Firebase.
במאמר זה:
רוכשים
/**
* חישוב קהל הרוכשים.
*
* רוכשים = משתמשים שתועדה לגביהם פעולת in_app_purchase או
* 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';
משתמשים פעילים ב-N ימים
/**
* יצירת קהל של משתמשים שהיו פעילים ב-N הימים האחרונים.
*
* משתמשים פעילים ב-N ימים = משתמשים שתועד לגביהם לפחות אירוע אחד עם פרמטר האירוע
* engagement_time_msec > 0 ב-N הימים האחרונים.
*/
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';
משתמשים לא פעילים ב-N ימים
/**
* יצירת קהל של משתמשים שלא היו פעילים ב-N הימים האחרונים.
*
* משתמשים לא פעילים ב-N ימים = משתמשים ב-M הימים האחרונים שלא תועד לגביהם לפחות
* אירוע אחד עם פרמטר האירוע engagement_time_msec > 0 ב-N הימים האחרונים
* כאשר 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;
משתמשים שפעילים לעתים קרובות
/**
* יצירת קהל של משתמשים שפעילים לעתים קרובות.
*
* משתמשים שפעילים לעתים קרובות = משתמשים שתועד לגביהם לפחות אירוע
* אחד עם פרמטר האירוע engagement_time_msec > 0 ב-N מתוך
* M הימים האחרונים, כאשר 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
);
משתמשים פעילים מאוד
/**
* יצירת קהל של משתמשים פעילים מאוד.
*
* משתמשים פעילים מאוד = משתמשים שהיו פעילים למשך יותר מ-N דקות
* ב-M הימים האחרונים, כש-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
);
משתמשים שצורפו
/**
* יצירת קהל של משתמשים שצורפו.
*
* משתמשים שצורפו = משתמשים שצורפו דרך מקור/אמצעי הגעה לאתר/קמפיין כלשהו.
*/
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';
קבוצות בעלות מאפיינים משותפים עם מסננים
/**
* יצירת קהל המורכב ממשתמשים שצורפו בשבוע שעבר
* דרך קמפיינים ב-Google, כלומר קבוצות בעלות מאפיינים משותפים עם מסננים.
*
* קבוצה בעלת מאפיינים משותפים מוגדרת כמשתמשים שצורפו בשבוע שעבר, כלומר לפני 7 עד 14
* ימים. המסנן של הקבוצה בעלת המאפיינים משותפים נועד למשתמשים שצורפו דרך קמפיין
* ישיר.
*/
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';