[Esse post é de MÉDIA complexidade, sendo indicado para pessoas que possuem conhecimento INTERMEDIÁRIO sobre o assunto abordado]

Uma das melhores funcionalidades disponíveis para clientes do Google Analytics 360 é a integração com o Google BigQuery através do BigQuery Export. Isso viabiliza uma maior flexibilidade para análise e exportação de dados formatados, se comparada com a interface visual da ferramenta. Entretanto, existem vários erros comuns cometidos por aqueles não familiarizados com seu uso, tanto devido à complexidade do esquema de exportação quanto à forma de armazenamento de dados do BigQuery. Este texto contém 5 receitas que servem de base para a construção de consultas mais elaboradas.

A principal diferença entre escrever consultas para o BigQuery e para outros bancos de dados relacionais, é o suporte da ferramenta para dados aninhados e repetidos, similar ao formato JSON. Com essa flexibilidade adicional é possível representar todos os dados do GA por meio de um esquema único. Por exemplo, todos os dados com escopo de hit associados a uma sessão estão contidos dentro dela, ao invés de estarem distribuídos em múltiplas tabelas, sendo necessário a manutenção de chaves estrangeiras e o uso de operações de JOIN.

Isso é conveniente pois os dados estão mais diretamente relacionados, mas é necessário desenvolver uma forma diferente de pensar para escrever as consultas. Os exemplos a seguir começam bem similares ao SQL padrão de bancos relacionais, mas serão introduzidos conceitos únicos do BigQuery progressivamente, levando a consultas mais complexas e interessantes.

Atenção: Todas as consultas deste texto utilizam o dialeto Standard SQL do BigQuery. Infelizmente, a opção padrão para escrever as consultas ainda é o Legacy SQL, então é necessário desativar a caixa Use Legacy SQL, visível na imagem abaixo, ou adicionar uma linha com o texto #standardSQL antes das consultas.

post higa 1

Métricas de sessão agregadas por dia:

A primeira coisa que deve-se notar é a equivalência 1:1 de linhas na exportação para sessões no GA. Todas as métricas agregadas, por sua vez, estão disponíveis no registro totals.

Vamos supor que você está interessado em obter o mesmo número exibido na interface visual do GA (sem amostragem de dados) em um relatório quebrado por dia. Seria possível simplesmente contar a quantidade de linhas, conforme a observação anterior, mas é possível que existam dados de sessões no BigQuery que não apareceriam normalmente no GA, pois se tratam de sessões contendo apenas hits de não-interação. Os números nesse caso não seriam equivalentes. Uma forma correta de obter os dados é somar a coluna totals.visits, cujo valor é zero nesses casos, como no exemplo a seguir:

post higa 2

Dica: O campo date utiliza o mesmo fuso horário configurado na sua vista do GA, que é exatamente o que você quer na maioria das vezes.

Contagem de eventos específicos:

Todos os dados de eventos estão disponíveis no registro hits.eventInfo, que é do tipo repeated. Existem algumas formas de consultar dados desse tipo, mas de modo geral um cross join é necessário. Isso pode ser especificado diretamente na query ou feito de forma implícita, por meio de subconsultas de expressão. Sendo esse último o que leva a uma linguagem mais natural, é o utilizado a seguir:

post higa 3

A função especial UNNEST utilizada na subconsulta recebe um array (campo do tipo repeated) e trata seus elementos como linhas, então é possível consultá-lo de forma natural.

Agrupamento por dimensões customizadas com escopo de hit:

É possível estender os exemplos anteriores para isso, mas subconsultas de expressão não são apropriadas para lógicas complexas, então desta vez é provavelmente melhor misturá-las com cláusulas de JOIN explícitas. O exemplo a seguir agrupa nossa contagem de evento anterior por duas dimensões customizadas, cdA e cdB.

post higa 4

O operador vírgula (,) é apenas uma forma mais simples de escrever cross joins em Standard SQL, apesar de que o significado do operador é um pouco especial no caso de dados aninhados. É bem simples de se perder no exemplo acima, então seguimos por partes:

A consulta começa juntando a tabela principal do GA com os dados de hit após aplicar a função UNNEST. Na prática, as linhas da tabela resultante agora tem uma relação 1:1 com os hits, e não mais com a sessão do GA. Além disso, todas as dimensões com escopo de sessão, como a coluna date acima, são replicadas para todos os hits dessa sessão.

A tabela resultante é então filtrada de forma que as linhas observadas no SELECT mais externo são apenas aquelas com correspondência 1:1 com os hits do evento de interesse específico no GA.

Finalmente, sendo hits.customDimensions também um registro do tipo repeated, é possível seguir da mesma forma feita no exemplo anterior, mas usando uma expressão de subconsulta para cada dimensão customizada.

Observe que a soma de totals.visits foi removida no exemplo anterior. A alteração foi realizada pois o número obtido pela soma não faria sentido, sendo cada linha equivalente a um hit e não uma sessão. Existem meios de evitar o problema, mas seria necessário pensar com cuidado na solução e não seria possível abordar isso com detalhes neste texto.

Produtos e dimensões customizadas:

Esse exemplo é muito similar ao anterior. Todos os dados de produtos estão disponíveis no registro hits.product, que por sua vez é também do tipo repeated. Nesse caso, suponha que você precisa extrair os dados de produtos agrupando pela mesma dimensão customizada cdA da consulta anterior e outra, com escopo de produto, dessa vez, chamada cdP. A consulta abaixo funcionaria bem:

post higa 5

Dica: As dimensões customizadas com escopo de usuário ou sessão estão disponíveis no campo customDimensions e é possível usar o mesmo método visto várias vezes ao longo do texto para consultá-las.

Segmentos avançados no BigQuery:

Reproduzir um segmento avançado do GA por meio de uma consulta pode ser complicado. Um truque bastante flexível é usar os campos fullVisitorId e visitStartTime como uma chave composta (na prática, construindo um tipo de id de sessão). Entretanto, isso muitas vezes leva a self-joins, o que não é ideal. Uma alternativa é filtrar os dados de hit na forma de array e usar a função ARRAY_LENGTH. A consulta abaixo exemplifica esse truque, além de ser bem similar à nossa primeira:

post higa 6Próximos passos

Se isso já não estava claro a esta altura, existem vários meios alternativos de escrever consultas para obter os mesmos dados dos exemplos anteriores. Além disso, é provável que apareçam ainda outras formas novas já que o dialeto Standard SQL está constantemente recebendo atualizações. Agora é hora de experimentar e praticar!

Se alguém conhecer alternativas interessantes para os métodos apresentados, por favor, deixe um comentário abaixo!