No nosso projeto de criação de um modelo de machine learning capaz de prever se uma pessoa cliente irá realizar uma compra no site, com base nos dados do Google Analytics, já avançamos significativamente ao carregar essa base de dados no nosso ambiente do BigQuery.
Exploração dos dados para modelagem de machine learning
Para criar um modelo de machine learning, seguimos um pipeline estruturado, e o primeiro passo nesse processo é a exploração dos dados.
É fundamental compreender esses dados antes de desenvolver o modelo, identificar quais colunas devem ser incluídas, verificar se é necessário algum tratamento ou modificação nos valores dessas colunas, e todas essas questões podem ser respondidas por meio da exploração dos dados.
Essas explorações poderiam ser realizadas utilizando linguagens como Python, Spark ou PySpark. No entanto, como a expertise do nosso time é em SQL e o ambiente do BigQuery oferece suporte para esse tipo de exploração, optaremos por utilizar SQL.
Retornando ao nosso ambiente do BigQuery, já havíamos acessado a guia e explorado alguns detalhes do esquema, bem como a visualização da tabela do ga_sessions para uma data específica. Agora, realizaremos essa exploração por meio de consultas SQL.
Executando consultas SQL
Para isso, no canto superior central, encontramos a opção de "Consulta SQL" representado com um ícone de adição dentro de um quadrado azul. Ao clicar nesta opção, podemos digitar a consulta desejada, seguindo o padrão do SQL.
Como desejamos selecionar todas as colunas, usamos SELECT *, indicando que todas as colunas serão selecionadas, e na linha seguinte, usamos FROM para definir a origem dos dados.
Há uma diferença em relação ao que talvez estejamos acostumados com SQL, pois precisaremos descrever todo o caminho. Para isso, retornamos ao "Explorer" no canto superior esquerdo para visualizar o caminho completo.
Após o FROM, digitamos aspas simples e, dentro delas, inserimos "bigquery-public-data", que é o nome do projeto. À esquerda, temos "google_analytics_sample", que corresponde à segunda camada.
Em seguida, temos o nome do projeto, seguido pelo nome do conjunto de dados, que é "google_analytics_sample". Adicionamos mais um ponto e, por fim, o nome da tabela, que é "ga_sessions".
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions`
Nossa base de dados possui uma particularidade: a tabela é composta por várias tabelas separadas por datas. Ela cobre um ano de dados diários, com intervalos de dia, mês e dois anos, que é o período abrangido. Precisamos especificar isso na consulta. Vamos revisar a tabela que estávamos analisando.
Na guia anterior, visualizamos que estávamos observando dados de 8 de janeiro de 2017, ou seja, 2017-08-01, que representa o dia 1º de agosto de 2017.
Após ga_sessions, adicionamos um underline e especificamos a variação da tabela de acordo com o período que desejamos analisar, ou seja, 2017-08-01. Com isso, definimos a origem dos nossos dados e, para limitar a quantidade de registros, que correspondem às pessoas usuárias por dia, colocamos um limite de 100 resultados com LIMIT 100 na próxima linha, seguido de ponto e vírgula.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 100;
Rodamos essa célula selecionando o comando e clicando no botão "Executar" na parte superior central.
O retorno abaixo foi parcialmente transcrito:
| Linha | visitorId | visitNumber | visitId | visitStartTime | date | totals.visits |
|---|
| 1 | null | 1 | 1501591568 | 1501591568 | 20170801 | 1 |
O resultado aparecerá na parte inferior, abaixo da consulta. Há uma barra que podemos arrastar para ajustar o espaço da tela, perdendo um pouco da área onde escrevemos o comando, mas ganhando melhor visualização dos resultados.
Agora, encontramos uma nova aba chamada "Resultado da Consulta", que contém informações sobre o job na aba "Informações do job". Como estamos em um ambiente de cloud, esse processamento ocorreu em outra máquina. A aba traz diversas informações, mas o foco estará na parte do "Resultado".
Ao clicar em "Resultado", visualizamos as mesmas colunas que já havíamos observado anteriormente, como "visitorId", "visitNumber", "visitStartTime", entre outras colunas. Uma coluna que se destaca é a primeira, a de visitorId, que apresenta diversos valores nulos.
Essa coluna é importante, pois funciona como o identificador de cada pessoa usuária. No entanto, por questões de anonimização dos dados, com o objetivo de desvincular as informações do uso de uma pessoa, a fim de evitar a identificação direta, em conformidade com as regras da LGPD e outras questões legais relacionadas ao armazenamento de dados pessoais, é possível que os valores dessa coluna tenham sido removidos.
Para confirmar isso, será necessário analisar todos os conjuntos de dados. No caso, estamos focando no ga_session de um único dia, mas será preciso ampliar a análise para todos os dias e verificar se existe alguma coluna em que os valores não sejam nulos.
Para realizar essa consulta, devemos ajustar o script que já temos. Primeiramente, diminuímos a parte do resultado e adicionamos alguns enters no script para construir a nova consulta.
Começamos com o comando SELECT, como desejamos selecionar todas as colunas, usamos o asterisco "*". Em seguida, usamos o comando FROM, e copiamos o mesmo caminho que utilizamos anteriormente: bigquery-public-data.google_analytics_sample.ga_sessions_20170801.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 100;
Para acessar todos os dias, precisamos remover a parte da data que colocamos no final da consulta. Ao fazer isso, garantimos que a consulta será executada para todos os dias, sem restringir a um único dia.
Em vez de especificar um dia, utilizamos o curinga * para selecionar todas as tabelas, indicando que queremos todas as colunas. Colocamos o asterisco após o sessions, e, na próxima linha, limitamos o resultado novamente, conforme necessário.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
LIMIT 100;
Selecionamos o comando e o executamos. Esse comando demorou mais do que o normal, pois estamos acessando um conjunto de dados bem maior. Talvez fosse mais eficiente se tivéssemos aplicado nossa regra antes de executar, mas ele foi executado, e, embora tenha demorado um pouco, não foi um tempo excessivo, já que o número de cláusulas era reduzido. O comando retornou o nosso resultado.
| Linha | visitorId | visitNumber | visitId | visitStartTime | date |
|---|
| 1 | null | 2 | 1475539225 | 1475539225 | 20161003 |
Agora, temos um conjunto de dados abrangendo todas as informações, mas ainda não conseguimos identificar se o "visitorId" está nulo para todos os registros.
Modificando a consulta
Fazemos uma pequena modificação nesta consulta. Após o FROM, inserimos uma cláusula WHERE, digitamos "WHERE", o nome da coluna que desejamos validar, que é o visitorId, e verificamos se o visitorId não é nulo. Assim, buscamos apenas os resultados onde a coluna visitorId não esteja nula: WHERE visitorId IS NOT NULL.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE visitorId IS NOT NULL
LIMIT 100;
Selecionamos novamente o comando e clicamos no botão de execução, localizado no centro da tela. O sistema processa todo o conjunto de dados, que consiste em 366 tabelas com diversos registros, o que pode levar um tempo, pois o processo percorre cada linha e verifica se a coluna é nula.
No canto inferior esquerdo, é possível observar o tempo decorrido, que indica quanto está sendo consumido pelo processo. Esse tempo está relacionado ao custo, pois, em consultas mais pesadas, podemos estar utilizando mais recursos do que o necessário, o que pode gerar custos adicionais e até travar o serviço. Por isso, as métricas de consulta são informações extremamente importantes.
Não há dados para exibir
Focando no nosso resultado, observamos que não há dados para exibir. Isso confirma que estávamos corretos: não encontramos o valor de "visitorId" para identificar cada pessoa usuária. Por alguma razão, esse dado foi removido, impossibilitando a identificação individual de cada usuário. Continuamos a exploração para entender como podemos correlacionar as linhas de dados de outra maneira.
Selecionando dados por mês
Agora, exploramos um cenário em que desejamos selecionar dados de um mês específico.
Já utilizamos o curinga, o asterisco, para selecionar todos os dados de um dia ao digitar 2017-08-01, e agora o objetivo é filtrar os dados por mês. Para isso, repetimos o comando anterior, copiando até a parte do FROM. O comando se torna SELECT * FROM.
A seguir, teclamos alguns enters para organizar melhor a tela, diminuímos a parte do resultado e mantemos SELECT * FROM bigquery-public-data.google_analytics_sample.ga_sessions, o que nos permite acessar todas as colunas.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
Para filtrar por um mês, utilizamos novamente a cláusula WHERE, mas agora aplicamos uma novidade: a variação que aparece no final do nome da tabela, o sufixo do nome da tabela.
Existe uma maneira de acessar essa informação por meio do _TABLE_SUFFIX. Utilizando o sufixo do nome da tabela, que corresponde exatamente à parte da data, como por exemplo 20170801, conseguimos acessar essa informação. Com isso, podemos definir o intervalo desejado, utilizando o operador BETWEEN. Assim, configuramos um intervalo, como por exemplo, de 20170801 até 20170131, sempre utilizando as aspas simples para indicar as datas.
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170131';
Selecionamos o comando que criamos e clicamos em "Executar".
Como sempre que utilizamos o asterisco, o processo não será instantâneo e levará um tempo devido ao tamanho do conjunto de dados. No entanto, o comando conseguiu retornar o resultado esperado, assim como observamos anteriormente.
O retorno abaixo foi parcialmente transcrito:
| Linha | visitorId | visitNumber | visitId | visitStartTime | date | totals.visits |
|---|
| 1 | null | 1 | 1483858625 | 1483858625 | 20170107 | 1 |
Com a consulta, obtemos os resultados que correspondem ao primeiro mês de 2017.
A visualização está organizada de forma que as linhas estão espaçadas, mas é possível identificar diversos resultados. No canto inferior direito, visualizamos que são apresentados 50 resultados por página, e estamos visualizando do primeiro até o 50º. Ao todo, temos 64.694 retornos, ou seja, um grande volume de dados sobre as pessoas usuárias.
Observamos que a coluna "visitorId" não poderá ser utilizada, mas há diversas outras que podem ser aproveitadas, como a coluna "visitNumber". Esta indica quantas vezes a pessoa usuária visitou o site, sendo a visita número 1, a visita número 2 e assim por diante. Cada uma dessas visitas possui um ID único, o que nos permite combinar as visitas de uma mesma pessoa usuária.
Há uma grande quantidade de colunas disponíveis, o que é possível observar pela barra no canto inferior da tela. Ao percorrermos as colunas, notamos a variedade de dados. Também podemos identificar colunas que são um array ou um record, como vimos anteriormente. Essas colunas são representadas primeiro pelo nome, como em "device.browserSize" e "device.operatingSystem", e são capazes de armazenar dados com uma estrutura diferente das tabelas com as quais normalmente trabalhamos.
A parte referente ao device é particularmente interessante, pois fornece informações como o sistema operacional utilizado, no caso, Linux, além de detalhes sobre o navegador, como o Chrome. Esses dados podem ser cruciais, pois a experiência de compra da pessoa usuária pode variar dependendo do navegador utilizado, o que torna essa informação importante para análise.
Outro ponto importante refere-se às colunas que são mais complexas do que os arrays que já vimos, especificamente os arrays dentro de arrays. Isso pode ser observado, por exemplo, na coluna Heats, ou mais detalhadamente em "hits.page.pagePathLevel4", que contém uma lista dentro de outra lista.
Próximos passos
Precisamos entender como trabalhar com esse tipo de estrutura de dados, como selecionar essas colunas em uma consulta. Para modificar e manipular esses dados, é fundamental aprender a acessar as informações corretamente, e é isso que abordaremos em breve.