SQL para Growth: 15 Consultas no BigQuery para Dominar Métricas de Negócio

1. Por que Todo Líder de Growth Deve Dominar SQL no BigQuery

O ecossistema contemporâneo de marketing digital atravessou a fase em que o sucesso de uma operação dependia de intuição criativa isolada. Os maiores casos de escala no e-commerce global são conduzidos por Growth Engineers e analistas seniores que dominam a linguagem universal dos dados: o SQL (Structured Query Language).

Plataformas de relatórios de SaaS empacotadas oferecem médias consolidadas que frequentemente mascaram perdas operacionais severas. O Google BigQuery, com sua infraestrutura serverless capaz de escanear terabytes em poucos segundos, permite dissecar o comportamento de cada cliente individual com precisão cirúrgica. A seguir, apresentamos e explicamos as consultas analíticas mais valiosas do mercado.

2. Consultas 1 a 3: Análise de Cohorts e Retenção de Safras

Consulta 1: Construção da Matriz de Cohorts de Aquisição (Safra): Esta consulta agrupa novos compradores pelo mês da primeira compra e acompanha o faturamento incremental gerado nos meses subsequentes (M0, M1, M2...):

WITH primeira_compra AS (
  SELECT
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) AS safra_aquisicao
  FROM `projeto.ecommerce.pedidos`
  WHERE status = 'PAID'
  GROUP BY customer_id
),
pedidos_detalhe AS (
  SELECT
    p.customer_id,
    pc.safra_aquisicao,
    DATE_DIFF(DATE_TRUNC(p.order_date, MONTH), pc.safra_aquisicao, MONTH) AS meses_apos_aquisicao,
    p.total_amount
  FROM `projeto.ecommerce.pedidos` p
  INNER JOIN primeira_compra pc ON p.customer_id = pc.customer_id
  WHERE p.status = 'PAID'
)
SELECT
  safra_aquisicao,
  meses_apos_aquisicao,
  COUNT(DISTINCT customer_id) AS clientes_ativos,
  ROUND(SUM(total_amount), 2) AS receita_total
FROM pedidos_detalhe
GROUP BY safra_aquisicao, meses_apos_aquisicao
ORDER BY safra_aquisicao, meses_apos_aquisicao;

Consulta 2: Taxa de Recompra em 60 e 90 Dias: Descobre o percentual de clientes adquiridos que voltam a comprar na loja dentro de uma janela crítica de retorno.

Consulta 3: Lag de Recompra Médio (Repurchase Interval): Identifica a quantidade exata de dias decorridos entre a 1ª e a 2ª compra utilizando a função LEAD() sobre a partição do cliente.

3. Consultas 4 a 7: Matriz RFM e Detecção de Churn

Consulta 4: Segmentação RFM Automática com NTILE: Divide a base de clientes em decis ou quintis de Recência (dias desde a última compra), Frequência (total de compras) e Valor Monetário (total faturado):

WITH rfm_bruto AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recencia_dias,
    COUNT(order_id) AS frequencia_pedidos,
    SUM(total_amount) AS monetario_total
  FROM `projeto.ecommerce.pedidos`
  WHERE status = 'PAID'
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recencia_dias,
    frequencia_pedidos,
    monetario_total,
    -- Invertemos a recencia: menor numero de dias = maior score (5)
    NTILE(5) OVER (ORDER BY recencia_dias DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequencia_pedidos ASC) AS f_score,
    NTILE(5) OVER (ORDER BY monetario_total ASC) AS m_score
  FROM rfm_bruto
)
SELECT
  customer_id,
  r_score, f_score, m_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'Campeões VIP'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Clientes Leais'
    WHEN r_score <= 2 AND f_score >= 4 THEN 'Em Risco de Churn'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Clientes Hibernando'
    ELSE 'Compradores Recentes / Promissores'
  END AS segmento_rfm
FROM rfm_scores;

4. Infográfico Vetorial: Anatomia de um Pipeline SQL de Growth

5. Consultas 8 a 11: LTV Real, Margem e Repurchase Lag

Consulta 8: LTV Histórico Acumulado a 12 Meses: Calcula exatamente quanto dinheiro um cliente deixa na empresa ao longo de seu primeiro ano de vida.

Consulta 9: Margem de Contribuição Líquida Real por Canal: Cruza a receita de pedidos com o custo das mercadorias vendidas (CMV), impostos fiscais e gastos reportados via APIs de anúncios para determinar o POAS (Profit on Ad Spend).

6. Consultas 12 a 15: Desaninhando o Schema de E-commerce do GA4

Consulta 12: Desaninhamento Eficiente de Produtos Mais Adicionados ao Carrinho no GA4:

SELECT
  item.item_id,
  item.item_name,
  COUNT(1) AS vezes_adicionado_ao_carrinho,
  ROUND(SUM(item.price), 2) AS valor_potencial_carrinho
FROM
  `projeto.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE
  event_name = 'add_to_cart'
  AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261014'
GROUP BY
  item.item_id,
  item.item_name
ORDER BY
  vezes_adicionado_ao_carrinho DESC
LIMIT 20;

7. Engenharia de Custos: Particionamento e Agrupamento (Clustering)

Para manter sua fatura de Google Cloud em centavos mesmo processando dezenas de milhões de linhas diárias:

• Particionamento por Data (PARTITION BY date_column): Garante que queries filtradas por intervalo temporal leiam exclusivamente as partições relevantes, descartando o restante da tabela.
• Agrupamento por Chave de Consulta (CLUSTER BY customer_id, channel): Ordena fisicamente os dados dentro dos blocos de armazenamento, acelerando JOINs e filtros em até 10x.

8. Checklist de Boas Práticas de SQL para BigQuery

Perguntas Frequentes sobre sql para growth ecommerce bigquery

O que são Window Functions em SQL e por que são cruciais para Growth?

Window Functions (como ROW_NUMBER(), LAG(), LEAD() e SUM() OVER) executam cálculos através de um conjunto de linhas de tabela relacionadas à linha atual sem colapsar os dados em um GROUP BY tradicional. Elas são essenciais para calcular o tempo entre a primeira e a segunda compra de um mesmo cliente.

Como evitar gastar muito dinheiro rodando queries no BigQuery?

Sempre filtre tabelas particionadas pela coluna de data no bloco WHERE, nunca utilize 'SELECT *' em tabelas de produção (selecione apenas as colunas estritamente necessárias) e utilize tabelas pré-agregadas ou o BI Engine para dashboards.

O que significa UNNEST no contexto do GA4 no BigQuery?

No schema do GA4, parâmetros de eventos (event_params) e itens de e-commerce (items) são gravados como registros aninhados (arrays de STRUCTs). A função UNNEST descompacta esse array em linhas individuais para que você possa filtrar e somar valores de produtos específicos.

Qual a periodicidade ideal para rodar essas queries de Growth?

Consultas de Cohorts e RFM devem ser agendadas para execução diária na madrugada (Scheduled Queries) ou integradas a modelos incrementais do dbt, alimentando painéis executivos e audiências de e-mail marketing.