Dopo aver esportato i dati di Firebase in BigQuery, puoi eseguire query per segmenti di pubblico specifici.
Questo articolo fornisce una serie di modelli utilizzabili come base per le tue query. Ricordati di modificare le query di esempio secondo le specifiche dei tuoi dati, ad esempio cambiando i nomi delle tabelle e gli intervalli di date.
Queste query restituiscono il numero di utenti nel segmento di pubblico. Se invece vuoi recuperare l'elenco degli User-ID nel segmento di pubblico, rimuovi la funzione COUNT () più esterna, ad esempio COUNT (DISTINCT user_id) --> DISTINCT user_id.
Queste query utilizzano SQL standard, perciò assicurati di selezionare tale opzione prima di eseguire una query. In BigQuery > Area di lavoro SQL, fai clic su Altro > Impostazioni query. In Impostazioni aggiuntive > Dialetto SQL, seleziona Standard.
Al momento questi dati sul pubblico sono solo informativi, non utilizzabili praticamente.
Ci piacerebbe sapere se ritieni utili le query di esempio e se vorresti eseguire query per altri tipi di segmenti di pubblico. Puoi rispondere attraverso una richiesta di funzione nel Centro assistenza Firebase.
Contenuti di questo articolo:
Acquirenti
/**
* Calcola il segmento di pubblico degli acquirenti.
*
* Acquirenti = utenti che hanno registrato acquisti in-app o
* acquisti.
*/
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';
Utenti attivi in N giorni
/**
* Crea un pubblico composto dagli utenti attivi in N giorni.
*
* Utenti attivi in N giorni = utenti che hanno registrato almeno un evento con il parametro evento
* engagement_time_msec > 0 negli ultimi N giorni.
*/
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';
Utenti inattivi in N giorni
/**
* Crea un pubblico composto dagli utenti inattivi in N giorni.
*
* Utenti non attivi in N giorni = utenti che negli ultimi M giorni non hanno registrato un
* evento con il parametro evento engagement_time_msec > 0 negli ultimi N giorni
* dove 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;
Utenti spesso attivi
/**
* Crea un pubblico composto dagli utenti spesso attivi.
*
* Utenti attivi attivamente = utenti che hanno registrato almeno un
* evento con il parametro evento engagement_time_msec > 0 in N
* degli ultimi M giorni in cui 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
);
Utenti molto attivi
/**
* Crea un segmento di pubblico composto dagli utenti molto attivi.
*
* Utenti molto attivi = utenti che sono stati attivi per più di N minuti
* negli ultimi M giorni, dove 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
);
Utenti acquisiti
/**
* Crea un segmento di pubblico composto dagli utenti acquisiti.
*
* Utenti acquisiti = utenti che sono stati acquisiti tramite una sorgente, un mezzo, una campagna.
*/
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';
Coorti con filtri
/**
* Crea un pubblico composto da utenti acquisiti la settimana precedente
* tramite campagne Google, ovvero coorti con filtri.
*
* La coorte è definita come utenti acquisiti la settimana precedente, ovvero tra 7 e 14
* giorni prima. Il filtro della coorte è per utenti acquisiti tramite una campagna
* diretta.
*/
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';