Po exportu ze služby Firebase do nástroje BigQuery lze exportovaná data používat k dotazům na určité publikum.
Tento článek uvádí některé šablony, které můžete při vytváření dotazů použít. Vzorové šablony musíte upravit tak, aby odpovídaly vašim konkrétním datům, např. změnit název tabulky nebo období.
Dotaz najde počet uživatelů v daném segmentu publika. Pokud vás zajímá seznam ID uživatelů, odstraňte nejzazší funkci COUNT(), např. COUNT(DISTINCT user_id) --> DISTINCT user_id.
Dotazy používají standardní SQL, proto před spuštěním dotazu zkontrolujte, zda jste zvolili tuto možnost. (V části BigQuery > Pracovní prostor SQL klikněte na Další možnosti > Nastavení dotazu. V části Další nastavení > Dialekt SQL vyberte Standardní.)
V současné době jsou tyto údaje o publiku pouze informativní.
Zajímalo by nás, zda jsou tyto vzorové dotazy užitečné a zda nejsou nějaké další typy publika, které by vás zajímaly. Odpovědět můžete prostřednictvím žádosti o funkci adresované podpoře Firebase.
Obsah tohoto článku:
Nakupující
/**
* Spočítá publikum nakupujících.
*
* Nakupující = uživatelé zaprotokolovaní do události in_app_purchase nebo 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';
Aktivní uživatelé (N dnů)
/**
* Vytvoří publikum aktivních uživatelů za N dní.
*
* Uživatelé aktivní N dnů = uživatelé, u kterých byla v posledních N dnech zaznamenána alespoň jedna událost s parametrem ngagement_time_msec > 0.
*/
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';
Neaktivní uživatelé (N dnů)
/**
* Vytvoří publikum neaktivních uživatelů za N dní.
*
* Uživatelé neaktivní N dnů = uživatelé za posledních M dnů, u kterých nebyla v posledních N dnech zaznamenána žádná událost s parametrem engagement_time_msec > 0 (kde 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;
Často aktivní uživatelé
/**
* Vytvoří publikum vysoce aktivních uživatelů.
*
* Často aktivní uživatelé = uživatelé za posledních M dnů, u kterých byla v posledních N dnech zaznamenána alespoň jedna událost s parametrem engagement_time_msec > 0 (kde 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
);
Vysoce aktivní uživatelé
/**
* Vytvoří publikum vysoce aktivních uživatelů.
*
* Vysoce aktivní uživatelé = uživatelé, kteří byli v posledních M dnech aktivní více než N minut (kde 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
);
Získaní uživatelé
/**
* Vytvoří publikum získaných uživatelů.
*
* Získaní uživatelé = uživatelé získaní přes určitý zdroj/médium/kampaň.
*/
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';
Kohorty s filtry
/**
* Vytvoří publikum sestávající z uživatelů získaných minulý týden prostřednictvím kampaní Google, tj. kohorty s filtry.
*
* Kohorta je definovaná jako uživatelé získaní minulý týden, tj. před 7–14 dny. Filtr kohorty je pro uživatele získané prostřednictvím přímé kampaně.
*/
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';