Po wyeksportowaniu danych Firebase do BigQuery możesz tworzyć obejmujące te dane zapytania pod kątem określonych odbiorców.
W tym artykule znajdziesz kilka szablonów, których możesz używać podczas tworzenia zapytań. Pamiętaj, aby zmodyfikować przykładowe zapytania i uwzględnić specyfikę danych, np. zmień nazwy tabel i zakresy dat.
Te zapytania zwracają liczbę użytkowników w grupie odbiorców. Jeśli zamiast tego chcesz uzyskać listę identyfikatorów użytkowników, usuń najbardziej zewnętrzną funkcję COUNT(), np. COUNT(DISTINCT user_id) --> DISTINCT user_id.
Te zapytania używają standardowej wersji SQL, więc wybierz tę opcję przed uruchomieniem zapytania. (W sekcji BigQuery > Obszar roboczy SQL kliknij Więcej > Opcje zapytania. W sekcji Ustawienia dodatkowe > Dialekt SQL kliknij Standardowa).
Obecnie te dane o odbiorcach mają tylko charakter informacyjny i nie mogą służyć do wykonywania dalszych działań.
Chętnie dowiemy się, czy te przykłady zapytań są przydatne i czy istnieją inne typy odbiorców, o które chcesz zapytać. Możesz odpowiedzieć, korzystając z Żądania funkcji w Pomocy Firebase.
Tematy w tym artykule:
Kupujący
/**
* Podaje liczbę kupujących.
*
* Kupujący = użytkownicy, którzy wywołali zdarzenie in_app_purchase
* lub 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';
Użytkownicy aktywni w ciągu N dni
/**
* Tworzenie grupy odbiorców Użytkownicy aktywni w ciągu N dni.
*
* Użytkownicy aktywni w ciągu N dni = użytkownicy, którzy w ciągu ostatnich N dni wywołali co najmniej jedno zdarzenie z parametrem zdarzenia
* engagement_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';
Użytkownicy nieaktywni w ciągu N dni
/**
* Tworzenie grupy odbiorców Użytkownicy nieaktywni w ciągu N dni.
*
* Użytkownicy nieaktywni w ciągu N dni = użytkownicy z ostatnich M dni, którzy w ciągu ostatnich N dni
* nie wywołali żadnego zdarzenia z parametrem zdarzenia event_time_msec > 0,
* przy czym 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;
Często aktywni użytkownicy
/*** Tworzenie grupy odbiorców Często aktywni użytkownicy.
*
* Często aktywni użytkownicy = użytkownicy, którzy w ciągu N z ostatnich M dni
* co najmniej raz wywołali zdarzenie z parametrem zdarzenia event_time_msec > 0,
* przy czym 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
);
Bardzo aktywni użytkownicy
/**
* Tworzenie grupy odbiorców Bardzo aktywni użytkownicy.
*
* Bardzo aktywni użytkownicy = użytkownicy, którzy w ciągu ostatnich M dni
* byli aktywni przez ponad N minut, przy czym ponad 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
);
Pozyskani użytkownicy
/**
* Tworzenie grupy odbiorców Pozyskani użytkownicy.
*
* Pozyskani użytkownicy = użytkownicy pozyskani ze źródła, z medium lub z kampanii.
*/
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 z filtrami
/**
* Tworzenie grupy odbiorców złożonej z użytkowników pozyskanych
* w zeszłym tygodniu dzięki kampaniom w Google, czyli kohorty z filtrami.
*
* Kohorta jest zdefiniowana jako użytkownicy pozyskani w zeszłym
* tygodniu, czyli od 7 do 14 dni temu. Filtr kohorty uwzględnia
* użytkowników pozyskanych dzięki kampanii bezpośredniej.
*/
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';