3 Queries SQL Básicas para consultar o Schema do GA em BigQuery

Apesar de durante os anos de 2023 e 2024 ter feito algumas formações de SQL e de SQL for GA4 BigQuery, no final deste ano tive a necessidade de dar os primeiros passos na coleta de dados do GA via BigQuery. E por isso, resolvi publicar este artigo com as primeiras queries que fiz para recolha de algumas métricas: Users, Active Users e Sessions.

São queries simples e o objetivo com este artigo é, para além de colocar a query SQL para cada um dos casos, é fornecer alguns comentários sobre as mesmas e sobre o resultado das mesmas.

Se tiverem respostas aos comentários, não hesitem em enviar-me uma mensagem.

Total Users

Contagem de “Total Users”

SELECT

COUNT(DISTINCT user_pseudo_ id) as Total_Users

FROM

`your_ga_table_scema.events_*`

WHERE

stream_id in (‘123456789’, ‘987654321’) AND

_table_suffix between ‘20240801’ and ‘20240810’

Algumas definições de dimensões importantes utilizadas nesta query

• user_pseudo_id

O user_pseudo_id é o identificador único que o Google utiliza para identificar cada utilizador. Quer dizer, na realidade, é o identificador único que o Google utiliza para identificar o web browser pelo qual o utilizador está a aceder ao website/app.

Ou seja, se um utilizador visitar um website a partir de um desktop, e depois a partir de um laptop e mais tarde a partir de um smartphone, o número de “Total Users” contabilizado pelo Google (Analytics) vai ser 3.

• user_id

O user_id pelo contrário, é um identificador utilizado pelo Google na esperança de identificar efetivamente os “Users”. Digo na esperança, porque o user_id só funciona, primeiro se o utilizador estiver autenticado no website, ou seja, o website tem de ter esta funcionalidade de Registo/Login. E segundo, o user_id para ser coletado pelo Google, tem de ser implementado.

Na query SQL acima, podemos retirar a “clause” WHERE, uma vez que o seu significado neste âmbito é para se poder filtrar uma determinada stream de dados presente no GA e, por outro lado, ter resultados só para os primeiros 10 dias de Agosto de 2024.

Active Users

Antes de explicarmos as dimensões, vamos explicar o conceito de Active User, através da própria definição da Google:

O número de “Uniqiue Active Users” que interagiram com o website ou app num determinado período de tempo especificado.

Um Active User é qualquer utilizador que tenha uma “engaged_session” ou quando o GA coleta:

• O evento “first_visit” ou o parâmetro “engagement_time_msec” de um website
• O evento “first_open” ou parâmetro “engagement_time_msec” de uma app Android
• O evento “first_open” ou “user_engagement” de uma app iOS

O utilizador é considerado ativo assim que o evento user_engagement é detetado num segundo.

• is_active_user

A partir de Julho de 2023 o Google criou uma nova coluna no schema do GA denominada de “is_acive_user” to tipo booleano (true or false). E desta forma tornou a contagem de “Active Users” um processo muito mais fácil.

Antes desta novidade, era necessário recorrer a outra query que utilizava a dimensão “engagement_time_msecs” que é um parâmetro do evento “page_view” ou “screen_view” se estivermos a analisar dados de Apps.

Ainda não consegui perceber porque razão o resultado dos “Active Users” dado pela nova fórmula onde utilizamos a coluna “is_active_user” é diferente do resultado proporcionado pela query antiga. Acredito que tenha a ver com as dimensões “engagement_time_msecs”

Se alguém me conseguir explicar, envie mensagem, pf.

Sessions

Contagem de Sessions

SELECT

count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = ‘ga_session_id’))) as Total_Sessions

FROM

`your_ga_table_scema.events_*`

WHERE

stream_id in (‘123456789’, ‘987654321’) AND

_table_suffix between ‘20240801’ and ‘20240810’

Sessions

• user_pseudo_id

O “user_pseudo_id” já vimos acima quanto estivemos a analisar a query de contagem de “Total Users” o que significava.

• ga_session_id

O ga_session_id ou session_id é supostamente a dimensão que identifica “exclusivamente” a sessão. Mas tal não é bem assim, porque:

1 — O “ga_session_id” é uma dimensão que é do tipo “string”, ou seja é na realidade um timestamp indicativo de quando a sessão foi iniciada.

2 — Se o “ga_session_id” é um timestamp que indica (ao segundo) quando foi iniciada a sessão, então podemos ter diferentes utilizadores (“user_pseudo_id”) a despoletar simultaneamente (à mesma hora, minuto e segundo) o evento de “session_start”. Por isso ser necessário fazer a concatenação entre o “user_pseudo_id” + “ga_session_id” para garantir que existe uma “única_sessão”

Mas podemos-nos perguntar: E porque não utilizar o evento session_start, por exemplo através da seguinte SQL query:

Bom, existem inúmeras e aleatórias razões para o evento de session_start não disparar quando o utilizador visita um website. Uma delas é por exemplo por causa de um bug momentâneo (ou não) do GA, porque o evento “page_view” não foi enviando…

Por último, não sei se a SQL query apresentada acima será a SQL query mais utilizada para fazer a contagem de Sessões do GA, mas é aquela que percebo ser a mais comum e no caso particular que apresentou resultados muito semelhantes aos apresentados no GA UI.

Artigo originalmente publicado em: 3 Queries SQL Básicas para consultar o Schema do GA em BigQuery | João Carlos Matos