Oi pessoal! Foi uma experiência de honra e de muito aprendizado explorar os dados e participar desse primeiro teste. Antes das perguntas, deixo algumas informações essenciais sobre as etapas realizadas e como acessar.

Criei um novo projeto para o teste e utilizei os arquivos CSV para criar tabelas no Google BigQuery, poder salvar as queries e compartilhar de forma fácil o resultado com vocês.

Link do projeto/console no BigQuery

Os assets criados a partir dos arquivos fonte são (sintaxe: projeto.conjunto.tabela):

• table_sales.csv --> shopper-teste-01.shopper.sales;
• table_customers.csv --> shopper-teste-01.shopper.customers;
• table_sales_items.csv --> shopper-teste-01.shopper.sales_items.

Além das tabelas, criei duas views para auxiliarem:

• shopper-teste-01.shopper.customer_numbers - Contém metadados sobre a tabela customers que são importantes para a questão 1;
• shopper-teste-01.shopper.customer_age - Contém o customer_id e a idade já calculada.

Por fim, as soluções de cada exercício estão no dropdown/menu Consultas (clássicas), também sob o projeto shopper-teste-01. O link de cada solução no BigQuery estará logo abaixo de seu enunciado.

⚠️ IMPORTANTE - Além do ambiente no GCP/BigQuery, deixo os arquivos de cada exercício hospedados nesse repositório também:

• solutions/ contém os arquivos respectivos à cada solução/pergunta;
• views/ contém as queries que definem as duas views auxiliares.

• Solução 1 no BigQuery

Antes de verificarmos o que a questão pede, identifiquei, na tabela customers, um problema relacionado à qualidade de dados. Analisando os metadados na view customer_numbers, podemos ver melhor:

SELECT
  total_customers_qty,
  null_birth_qty,
  null_birth_percent,
  with_birth_qty,
  with_birth_percent
FROM
  ``.shopper.customer_numbers`

Resultado:

De 10964 clientes, apenas 24,52% possui data de nascimento preenchida. Os outros 75,48%, quase 80%, estão com a data nula/vazia. Isso altera o significado do resultado da questão referente à clientes com mais de 50 anos, pois a amostra realizada foi de apenas 24,52%, ou seja, 2688 dos 10964 clientes existentes.

Para responder à primeira pergunta, criei primeiro a view chamada customer_age para ter as idades calculadas. Essa é a aparência da view:

SELECT
    c.customer_id,
    DATE_DIFF(current_date, c.birth, year) AS age
  FROM
    `shopper-teste-01.shopper.customers` c
  WHERE
    c.birth IS NOT NULL

Resultado:

Depois, a instrução final consulta a view para obter o número de clientes com mais de 50 anos:

SELECT
  COUNT(*) AS customers_quantity
FROM
  `shopper-teste-01.shopper.customer_age` ca
WHERE
  ca.age > 50

Resultado:

• Solução 2 no BigQuery

SELECT
  si.product_name,
  COUNT(si.product_id) as sales_qty
FROM
  `shopper-teste-01.shopper.sales_items` si
GROUP BY
  si.product_name
ORDER BY
  2 DESC
LIMIT
  5

Resultado:

• Solução 3 no BigQuery

Para resolver essa questão, primeiro criei as duas CTEs sales_by_month e customers_by_month para mostrarem as vendas e os clientes ambos por ano e mês. a função EXTRACT() é utilizada para separar o ano e o mês da data/timestamp integral.

WITH
  sales_by_month AS (
  SELECT
    EXTRACT(year
    FROM
      s.sales_datetime) AS year,
    EXTRACT(month
    FROM
      s.sales_datetime) AS month,
    COUNT(*) AS sales_qty
  FROM
    `shopper-teste-01.shopper.sales` s
  GROUP BY 1,2
  ORDER BY 1,2),
  
  customers_by_month AS (
  SELECT
    EXTRACT(year
    FROM
      c.created) AS year,
    EXTRACT(month
    FROM
      c.created) AS month,
    COUNT(DISTINCT(c.customer_id)) AS customer_qty
  FROM
    `shopper-teste-01.shopper.customers` c
  GROUP BY 1,2
  ORDER BY 1,2)

Depois, a query principal faz um left join em ambas as views e na última coluna calcula a Taxa de Conversão. A fórmula considerada foi (TotalVendasMês / TotalClientesMês) * 100 e um LEFT JOIN foi escolhido pois existem vendas após 2019, mas não novos clientes.

SELECT
  sbm.year,
  sbm.month,
  sbm.sales_qty,
  ROUND((sbm.sales_qty / cbm.customer_qty) * 100, 2) AS conversion_rate,
  ROUND((sbm.sales_qty / (
      SELECT
        total_customers_qty
      FROM
        `shopper-teste-01.shopper.customer_numbers`)) * 100, 2) AS conversion_rate_total_customers
FROM
  sales_by_month sbm
LEFT JOIN
  customers_by_month cbm
ON
  sbm.year = cbm.year
  AND sbm.month = cbm.month

Resultado:

⚠️ IMPORTANTE - Na questão sobre a Taxa de Conversão, me surgiu a grande dúvida sobre se o mais correto para calcular a métrica seria considerar o número de clientes do mês (como também estamos olhando pra vendas por mês) ou seria considerar o número do tempo todo, como um só, 10964.

Realizei pesquisas tanto em Português quanto em Inglês e por não ter encontrado uma resposta clara, adicionei a última coluna que considera a mesma fórmula para a Taxa de Conversão, mas utilizando como divisor a quantidade total de clientes, obtida na coluna total_customers_qty da view de metadados customer_numbers.

Confesso não ter compreendido totalmente se existe um mais correto ou se isso depende da regra específica de cada negócio, mas decidi colocar ambas as opções para mostrar o caminho do raciocínio. :)

• Solução 4 no BigQuery

Primeiro, criei a CTE para ordenar e enumerar as vendas por cada cliente. Utilizei a Window Function ROW_NUMBER() para particionar por cada cliente e também ordenar pela venda mais recente, como pede a observação:

WITH
  customer_sales AS (
  SELECT
    s.*,
    ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY s.sales_datetime) AS sales_number,
    ca.age
  FROM
    `shopper-teste-01.shopper.sales` s
  JOIN
    `shopper-teste-01.shopper.customer_age` ca
  ON
    s.customer_id = ca.customer_id
  ORDER BY
    s.customer_id)

Depois, calculei a média de idade por cada plataforma, filtrando o sales_number pela venda mais recente:

SELECT
  DISTINCT(cs.platform),
  CAST(AVG(cs.age) AS INT64) AS average_customer_age
FROM
  customer_sales cs
WHERE
  sales_number = 1
GROUP BY 1

Resultado:

Com as informações que descobrimos a partir dos dados, poderíamos conduzir algumas ações de marketing, como:

• Envio de notificação/lembrete para clientes, baseando-se, por exemplo, no produto mais comprado por cada um;
• Elaboração de estratégia de promoções baseadas em tendências como qual produto é mais vendido por mês ou época do ano
• Com melhorias relacionadas a Qualidade dos Dados, como fixar mais de 70% dos clientes sem data de nascimento, poderia também nos permitir olhar pra esse tipo de variável (faixa etária) e ampliar o número de estratégias relacionadas à isso.

Caso haja algum problema com a disponibilidade ou compreensão das soluções, basta entrar em contato comigo via qualquer meio como WhatsApp ou e-mail. Me coloco à total disposição!