O Google BigQuery é um banco de dados em nuvem popular usado por empresas em todo o mundo. É especialmente conveniente para trabalhar com dados brutos do Google Analytics: no GA 360, a integração com o BigQuery é configurada em alguns cliques e, para a versão gratuita, existem scripts e módulos de terceiros.
Nos dados brutos do Google Analytics, cada registro (linha) corresponde a uma sessão. Dentro dessa entrada, existem campos aninhados que correspondem aos hits da sessão:
Geralmente, essa estrutura de dados aninhada é confusa para usuários que não entendem como trabalhar com ela e descarregam esses dados.
Usando os parâmetros personalizados do Google Analytics como exemplo, tentarei explicar "nos dedos" como os dados incorporados são armazenados no Google BigQuery e como eles podem ser carregados.
Os códigos de consulta fornecidos estão funcionando, eles podem ser usados na solução de problemas, substituindo os nomes das tabelas e os números de índice de dimensões personalizadas necessários.
• Linhas de descarga• Descarregar enquanto mantém a estrutura de aninhamento• Exemplo de substituição de valores de parâmetros do usuárioO básico
O BigQuery suporta 2 dialetos do SQL: Legacy e Standard. O Google recomenda usar o SQL Standard mais recente, no qual escreveremos consultas para descarregamento.
Todo mundo que trabalhou com SQL conhece pelo menos um pouco a construção de consulta padrão:
SELECT * * FROM * * WHERE * *
Esse design funciona se a estrutura da tabela for simples, sem outros campos aninhados nas células:
Consideramos tabelas com campos aninhados. A estrutura dessa tabela (por exemplo, parâmetros de usuário do Google Analytics):
Opções de GA personalizadas no BQNo Google BigQuery, essa tabela terá os seguintes nomes de coluna (o separador "." Mostra a estrutura de aninhamento):
Então, como descarregamos dados de campos aninhados?
Descarregando Linhas
Volte para a
tabela com um exemplo de parâmetros personalizados do GA no BQ.
As colunas
customDimensions.index e
customDimensions.value são os índices e valores da
sessão e dimensões personalizadas
definidas pelo usuário .
As
colunas hits.customDimensions.index e
hits.customDimensions.value são os índices e valores das dimensões personalizadas da ocorrência.
No Google BigQuery, há outro nível de ação dos parâmetros do usuário - o produto. Os nomes e os valores das dimensões personalizadas do
produto no Google BigQuery estão nas colunas
hits.product.customDimensions.index e
hits.product.customDimensions.value . Eles são descarregados por analogia com os parâmetros de usuário atingidos, você só precisa levar em consideração outro nível de aninhamento.
Opções de usuário no nível da sessão e do usuário
O que devemos fazer se precisarmos descarregar os valores das dimensões personalizadas da sessão (personalizadas) para cada data sem salvar a estrutura aninhada (ou seja, linha por linha)?
Para responder à pergunta, vamos dar uma olhada na
tabela com os parâmetros de usuário do GA no BQ.
Mostra que os valores das células na coluna customDimensions
são outra tabela :
Basta fazer uma subconsulta a esta tabela na consulta principal:
SELECT
A saída é uma tabela:
Se precisarmos adicionar uma coluna com o valor de outro parâmetro do usuário, faremos outra subconsulta:
SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1,
Temos o seguinte:
Opções personalizadas no nível da ocorrência
Os parâmetros do usuário de ocorrência são descarregados da mesma maneira que os da sessão (usuário), exceto que a subconsulta precisa ser feita na tabela de ocorrências aninhadas. Em outras palavras, os valores das células na coluna de hits na tabela de dados brutos do Google Analytics são uma tabela aninhada na qual a tabela customDimensions está aninhada:
A solicitação para fazer o download dos parâmetros de usuário de ocorrência linha a linha será a seguinte:
SELECT
O resultado da consulta será uma tabela:
Você pode descarregar vários parâmetros do usuário da ocorrência e adicionar o parâmetro hitNumber (o número de sequência da ocorrência na sessão):
SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3,
Obtenha a tabela:
Sessão (usuário) + parâmetros de usuário atingidos
Se em uma consulta queremos descarregar a sessão e acessar os parâmetros do usuário, basta fazer as subconsultas necessárias para as tabelas principal e aninhada:
SELECT date, h.hitNumber AS hitNumber,
A tabela que será obtida como resultado da consulta:
Descarregar enquanto mantém a estrutura de aninhamento
Essa descarga pode ser necessária ao substituir os valores de um parâmetro do usuário no Google BigQuery.
Exemplo
No Google Analytics, o nome do país em formato completo é transferido para o parâmetro do usuário da sessão com o índice 12 e o parâmetro hit do usuário com o índice 25 para usuários da Rússia: RÚSSIA. É necessário alterar o formato do país para abreviado: RUS.Para fazer isso, você precisa substituir os valores necessários do parâmetro do usuário pelo país do usuário para todo o histórico de dados no Google BigQuery.
O procedimento para resolver o problema:
- Descarregar todos os dados enquanto mantém a estrutura de aninhamento
- Substitua o valor do parâmetro do usuário pelo país
- Reescreva a tabela
Para fazer upload de dados enquanto mantém a estrutura de aninhamento, você deve usar a função ARRAY e a construção SELECT AS STRUCT. Vamos descobrir o que é isso.
A sintaxe da função ARRAY é a seguinte:
ARRAY(**)
Retorna uma matriz de elementos.
Comparando uma matriz com a gravação linha a linha:
À esquerda é uma matriz, à direita é uma gravação de linhaSe quisermos salvar uma estrutura aninhada e descarregar um array com várias colunas, devemos usar
ARRAY (SELECT AS STRUCT ...) :
Matriz aninhadaOpções de usuário no nível da sessão e do usuário
Para descarregar enquanto mantém a estrutura das dimensões personalizadas da sessão (personalizadas), usamos a consulta:
SELECT date,
Como resultado de sua execução, é obtida uma tabela na qual a estrutura de aninhamento dos dados "brutos" do Google Analytics é salva:
Opções personalizadas no nível da ocorrência
Para descarregar os valores dos parâmetros do usuário de ocorrência do Google BigQuery, mantendo a estrutura de aninhamento, é importante observar que a tabela customDimensions está aninhada na tabela de ocorrências. Em outras palavras, você precisa fazer a subconsulta ARRAY 2 vezes (SELECT AS STRUCT ...): primeiro na tabela de ocorrências aninhadas, depois na tabela customDimensions aninhada nela:
SELECT date,
O resultado desta consulta será uma tabela:
Sessão (usuário) + parâmetros de usuário atingidos
Assim como no descarregamento linha a linha, precisamos combinar as subconsultas ARRAY (SELECT AS STRUCT ...) na mesma consulta às tabelas aninhadas desejadas:
SELECT date,
Qual é o resultado:
Exemplo de Substituição de Valores de Parâmetro do Usuário
Vamos voltar ao nosso
exemplo .
Na seção anterior, recebemos uma solicitação para fazer o upload da sessão (usuário) e atingimos os parâmetros do usuário do Google Analytics, mantendo a estrutura de aninhamento.
Complementamos esta consulta com as construções SELECT * REPLACE para descarregar com substituição e CASE para atualizar os valores dos parâmetros de usuário necessários:
Como resultado dessa solicitação, obteremos a tabela original com os dados brutos do Google Analytics. Ela manterá completamente a estrutura de aninhamento original, mas os valores dos parâmetros necessários do usuário serão alterados para novos.
O tópico de trabalhar com uma estrutura de dados aninhada no Google BigQuery não é fácil.
Espero ter conseguido esclarecer esta questão. Mas, deixe-me lembrá-lo, a melhor maneira de aprender a fazer algo é praticar mais.