Responder às perguntas do negócio em SQL sobre os dados brutos

Com a exportação ligada, a primeira consulta de quase todo mundo é um SELECT de tudo na tabela de ontem, e ela devolve colunas que se abrem em listas dentro de listas. O parâmetro que interessa está lá, mas dentro de um par chave e valor, e o valor pode estar em qualquer uma de quatro colunas.

O custo de não dominar essa estrutura é duplo. A consulta que junta eventos com o CRM na granularidade errada multiplica linhas e infla a receita. A consulta que lê a tabela inteira todo dia gasta orçamento do projeto com bytes que ninguém precisava ler. O BigQuery cobra por volume lido, e a fatura chega antes da resposta.

O capítulo monta o conjunto de consultas da Verde Vivo em ordem: desaninhar, reconstruir a sessão, responder às perguntas comerciais, cruzar com HubSpot e Shopify, e só então tornar tudo barato e agendado.

A unidade é a tabela do dia. Para ler um período, a consulta usa o curinga events_* com filtro pelo sufixo do nome da tabela, e esse filtro é o que impede o BigQuery de varrer o histórico inteiro. Cada linha traz o nome do evento, a data, o carimbo de tempo em microssegundos, o user_pseudo_id (o identificador do navegador ou do aparelho), o user_id quando existe, a origem da sessão e as colunas aninhadas.

UNNEST (a operação que abre uma coluna de lista em linhas) é a ferramenta central. O parâmetro page_location, por exemplo, mora na lista event_params, com chave page_location e valor na coluna string_value. Um número inteiro, como ga_session_id, mora em int_value. A consulta abre a lista, filtra a chave e escolhe a coluna do tipo certo.

A subconsulta dentro do SELECT é o padrão para extrair um parâmetro por vez. Quando a análise precisa de dez parâmetros, o mesmo padrão se repete dez vezes, e é por isso que a tabela intermediária, mais adiante, vale tanto: ela faz a extração uma vez por dia e deixa colunas planas para todo mundo.

Sessão não existe como linha na exportação; ela é reconstruída. A chave é a combinação de user_pseudo_id com ga_session_id, porque o mesmo número de sessão pode se repetir entre pessoas diferentes. Com essa chave, a consulta agrupa eventos, encontra o primeiro carimbo de tempo, marca se houve purchase e lê a origem que a sessão recebeu.

O COUNT DISTINCT sobre transaction_id é a proteção contra pedido contado duas vezes: se a página de confirmação recarregou e o purchase disparou de novo, a consulta conta um pedido. A receita, no entanto, seria somada duas vezes. Quando há suspeita de duplicidade, a consulta primeiro escolhe uma linha por transaction_id e só depois soma.

As perguntas comerciais seguem a mesma mecânica. Aquisição: sessões e pedidos por origem, mídia e campanha, como acima. Funil: pessoas que dispararam begin_checkout e, na mesma sessão, add_payment_info e purchase, contadas por etapa. Produto: a lista items aberta com UNNEST, agrupada por item_id, com quantidade e receita. Recompra: pedidos por user_id ao longo de doze meses, separando quem comprou uma vez de quem voltou.

Coorte é a consulta que a exportação faz melhor que a interface, porque não há retenção de 14 meses no BigQuery. A mecânica: uma subconsulta encontra o mês do primeiro purchase de cada user_id; a consulta principal junta cada pedido posterior a esse mês de origem e soma a receita por mês desde a entrada. O resultado é uma grade em que cada linha é uma coorte e cada coluna é o mês de vida.

Cruzar com o CRM começa pela chave. A Verde Vivo passa o identificador do negócio do HubSpot como parâmetro de generate_lead, e o HubSpot exporta uma tabela com esse identificador, a etapa e a data de fechamento. A cardinalidade é o risco: um lead gera dezenas de eventos no GA4, e juntar na granularidade do evento multiplica cada contrato por dezenas. A consulta agrega o GA4 por lead antes do JOIN, e só depois junta uma linha com uma linha.

Com a Shopify, a chave é o número do pedido, que a Verde Vivo envia como transaction_id. A conciliação junta os purchase do GA4 com os pedidos pagos da loja pelo número e classifica cada pedido em três grupos: está nos dois, só na Shopify, só no GA4. O grupo "só na Shopify" é a medição que falhou; o grupo "só no GA4" é pedido não pago ou duplicado.

Eficiência tem quatro regras. Filtrar pelo sufixo da tabela, sempre, porque é o que limita os bytes lidos. Selecionar colunas em vez de tudo, porque o BigQuery cobra pelas colunas que lê. Criar uma tabela intermediária plana, com os parâmetros já extraídos e uma linha por evento, para que ninguém repita o UNNEST. Agendar a consulta que alimenta essa tabela para rodar uma vez ao dia, depois do lote.

Armadilha comum: juntar a tabela de eventos com a tabela de contratos do HubSpot pela chave do lead sem agregar antes, e apresentar uma receita B2B dez vezes maior que a real. O erro passa despercebido porque a consulta roda sem aviso e o número parece bom. A defesa é contar linhas antes e depois de cada JOIN: se o total de contratos mudou, a junção multiplicou.

Na Verde Vivo, Marina escreveu 14 consultas em três semanas e guardou cada uma num repositório com o nome da pergunta. A conciliação de agosto encontrou 1.590 purchase no GA4 contra 1.650 pedidos pagos na Shopify: 96 por cento de cobertura. Dos 60 pedidos sem par, 41 eram Pix confirmado depois da sessão, e esse número foi direto para o capítulo seguinte.

A consulta de coorte mostrou que 31 por cento de quem comprou pela primeira vez em setembro de 2025 voltou a comprar nos doze meses seguintes, e que a coorte vinda de busca orgânica valia 18 por cento mais em receita acumulada do que a vinda de mídia paga. A tabela plana diária reduziu o volume lido por consulta do painel de cerca de 38 GB para 2 GB, e o custo mensal do projeto caiu junto.

O que mudou foi a velocidade entre pergunta e resposta: uma pergunta nova de gestão passou a levar uma tarde, e a conciliação com a Shopify virou rotina diária em vez de auditoria trimestral.

Escreva hoje a consulta de conciliação entre os purchase da sua exportação e os pedidos pagos da sua plataforma, no mês passado, e anote a cobertura; abaixo de 95 por cento, liste os pedidos sem par por meio de pagamento antes de seguir. O próximo capítulo resolve o caso mais comum dessa lista: o pagamento que o navegador nunca viu.