Refinando Dados Brutos do Turn no BigQuery
Embora o Turn.io ofereça um painel interno de Insights, bem como métricas integradas para recursos como Campanhas, sabemos que sempre haverá casos específicos ou consultas de dados personalizadas que os clientes precisarão realizar.
Para atender a essa necessidade, os clientes podem exportar seus dados para o BigQuery. Isso permite que eles executem consultas eficientes em todos os seus dados, além de sincronizá-los com ferramentas avançadas de visualização, como o Looker Studio (anteriormente chamado de Data Studio).
No entanto, trabalhar com dados brutos diretamente do banco de dados pode ser desafiador. Além disso, existem algumas armadilhas que muitos de nossos clientes enfrentam ao lidar com dados no BigQuery. O principal problema que abordaremos neste artigo é como lidar com dados desatualizados.
Quando transmitimos dados para o BigQuery, não sobrescrevemos os dados existentes, apenas adicionamos novos dados à tabela.
Isso significa que podem existir várias linhas no banco de dados representando o mesmo contato. Portanto, executar uma consulta como:
SELECT count(id)
FROM contacts
Retornará uma contagem inflada de contatos. Você pode confirmar isso executando o seguinte comando:
SELECT id, count(*) as count
FROM contacts
GROUP BY id
ORDER BY count DESC
LIMIT 10
Para obter uma tabela contendo apenas os dados mais recentes de contato, utilize a seguinte consulta:
WITH
ranked_contacts as (
SELECT
*,
-- isso nos ajuda a lidar com linhas duplicadas
ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) as rank
FROM contacts
)
SELECT *
FROM ranked_contacts
WHERE rank = 1
Para mensagens, a abordagem é semelhante:
WITH
ranked_messages as (
SELECT
*,
-- isso nos ajuda a lidar com linhas duplicadas
ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) as rank
FROM messages
)
SELECT *
FROM ranked_messages
WHERE rank = 1
Outra forma de utilizar essa estratégia é ao lidar com a tabela de status das mensagens. Nesse caso, temos status duplicados por mensagem em circunstâncias muito raras. O que queremos fazer é obter o último status atribuído a uma mensagem. Podemos fazer isso usando o mesmo padrão PARTITION BY __ ORDER BY __:
WITH ranked_statuses AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY message_id ORDER BY inserted_at DESC) as rank
FROM statuses)
SELECT *
FROM ranked_statuses
WHERE rank = 1;
