Refinar datos de turnos sin procesar en BigQuery

Nathan Begbie Actualizado por Nathan Begbie

Si bien Turn.io ofrece un panel de información interno, así como métricas integradas para cosas como Recordatorios, sabemos que siempre habrá casos extremos o consultas de datos específicas que los clientes tendrán. Para satisfacer esta necesidad, los clientes pueden exportar sus datos a BigQuery. Esto les permite ejecutar consultas eficaces en todos sus datos, así como sincronizar estos datos con potentes herramientas de panel como Looker Studio (anteriormente llamado Data Studio).

Sin embargo, a veces puede resultar abrumador trabajar con datos sin procesar, directamente desde la base de datos. También hay algunos problemas a los que se enfrentan muchos de nuestros clientes cuando tratan con datos de BigQuery. El principal que abordaremos en este artículo es cómo lidiar con los datos obsoletos.

Cuando transmitimos datos a BigQuery, no sobrescribimos los datos existentes, simplemente agregamos datos nuevos a la tabla.

Esto significa que hay varias filas en una base de datos que podrían representar el mismo contacto. Entonces, hacer algo como:

SELECCIONAR recuento(id)
DE contactos

Le dará un recuento inflado de contactos. Puede confirmar esto ejecutando lo siguiente:

SELECT id, count(*) as count
FROM contacts
GROUP BY id
ORDER BY count DESC
LIMIT 10

Entonces, para obtener una tabla solo con los datos de contacto más recientes, realice la siguiente consulta:

CON
contactos_clasificados como (
 SELECT 
 *,
 -- esto nos permite tratar con filas duplicadas
 ROW_NUMBER() SOBRE (PARTICIÓN POR id ORDER BY actualizado_at DESC) como rango
 FROM contactos
)
SELECT * 
FROM contactos_clasificados
WHERE rango = 1

  

Para mensajes, adoptaría un enfoque similar:

CON
ranked_messages as (
 SELECT 
 *,
 -- esto nos permite tratar con filas duplicadas
 ROW_NUMBER() OVER (PARTITION BY id ORDER BY actualizado_at DESC) como rango
 FROM mensajes
)
SELECT * 
FROM ranking_messages
WHERE rango = 1

Otra forma en que podríamos querer utilizar esta estrategia es cuando tratamos con la tabla de estado de mensajes. En este caso, sólo tenemos estados duplicados por mensaje en circunstancias muy raras. En cambio, lo que queremos hacer es obtener el último estado que se le dio a un mensaje. Podemos hacerlo usando el mismo patrón PARTICIÓN POR __ ORDER POR __:

CON estados_clasificados AS (
SELECT *,
 ROW_NUMBER() OVER (PARTICIÓN POR id_mensaje ORDENAR POR insertado_en DESC) como rango
DESDE estados)
SELECT * 
DESDE estados_clasificados 
DONDE rango = 1;

¿Cómo lo hicimos?

Comprenda sus datos y consultas de BigQuery

Consultas de ejemplo para sus datos

Contacto