Combinación de Google Analytics y Shopify: creación de un almacén de datos de comercio electrónico en BigQuery

Publicado: 2023-02-15

Google Analytics es una gran herramienta que le permite comprender el comportamiento de los usuarios que visitan su sitio web. Considerando que, Shopify es fantástico para proporcionar una cobertura detallada del rendimiento desde una perspectiva de comercio electrónico. Ambos son excelentes en lo que hacen, pero llega un punto en el que los datos de Shopify y Google Analytics (GA) de forma aislada no brindan los conocimientos más profundos que podría desear. Si bien cada uno tiene la capacidad de proporcionar información poderosa, se puede obtener un nivel mucho mayor de conocimiento al combinar los datos.

En este ejemplo en particular, cubriremos cómo logramos esto para nuestros amigos en Pooch & Mutt, una empresa con sede en el Reino Unido que produce alimentos para perros naturales y saludables. Queríamos mejorar las perspectivas tomando los datos recopilados en Google Analytics y usándolos junto con los datos de comercio electrónico de Shopify, así como los datos de suscripción de Recharge y uniéndolos con los datos de suscripción heredados de Bold. Usando esta variedad de fuentes de datos, podríamos construir una imagen más rica y completa.

Este blog buscará brindar una descripción general de cómo agregamos estos datos y luego describir los procesos de pensamiento detrás de combinarlos para extraer y visualizar estos conocimientos basados ​​en datos. Específicamente, explicaremos cómo respondimos las siguientes tres preguntas;

  1. ¿Qué canales impulsan los registros de suscripción?
  2. Combinaciones de cestas más allá de GA; ¿Cuáles son las cestas más populares para suscriptores y no suscriptores y cuáles son las más rentables?
  3. ¿Cuánto tiempo y cuántas compras en promedio toma antes de que un usuario se convierta en suscriptor?

Metodología

Antes de responder a estas preguntas, primero veamos cómo recopilamos todos los datos de sus respectivas plataformas en una ubicación centralizada y construimos una tabla que podríamos usar para cotejar estos datos.

Estructura de datos

Elegimos usar BigQuery (BQ) para centralizar los datos debido a su versatilidad y capacidad para manejar la combinación de conjuntos de datos. El siguiente diagrama de flujo destaca las conexiones generales entre los datos y la forma en que esto se logró:

Puntada

Stitch es una herramienta asequible que le permite extraer y cargar datos en almacenes de datos prediseñados.

Nos conectamos fácilmente a Shopify y Recharge a través de Stitch, donde simplemente seleccionamos las mesas que queríamos. En este caso, las tablas más importantes son 'clientes' y 'suscripciones' de Recharge y 'pedidos' de Shopify.

Luego, esta información se sincroniza con BQ cada 6 horas a través del proceso de programación automatizado de Stitch.

Atrevido

Para los datos históricos de suscriptores, realizamos una exportación final y la subimos a BQ a través de Hojas de cálculo de Google.

Google analitico

Si bien Stitch también se puede usar para esto, optamos por usar RStudio para obtener estos datos en BQ. Si está interesado en obtener más información sobre cómo puede hacer esto, puede leer un blog de nuestro propio experto en R Studio, Danny Smith, sobre cómo usar la API de GA con R.

Creación de una tabla maestra

Una vez que tuviéramos todos los datos requeridos en BQ, podríamos comenzar a combinarlos. Los pasos que tomamos para hacer esto son los siguientes:

  1. Combinamos y alineamos los datos de suscriptores heredados de Bold con los datos de suscriptores actuales de Recharge.
  2. Luego extrajimos datos de comercio electrónico de GA que incluyen ID de pedido (por ejemplo, SPFYXXXXX), junto con la fuente, el medio y el canal.
  3. Creamos nuevas columnas basadas en las tablas de GA y datos de suscriptores sindicalizados antes de unirnos a los datos de pedidos de Shopify. En este caso, algunas de estas columnas adicionales incluidas;
    1. Identificador de suscriptor 'activo'
    2. Fecha original de suscripción
    3. Fuente, medio y canal de pedido
    4. Primer o repetido pedido del cliente
  4. A continuación, seleccionamos los campos relevantes de los datos del pedido de Shopify a los que queríamos unir estos nuevos datos. Algunos ejemplos clave son;
    1. Identificación del cliente
    2. Solicitar ID
    3. Fecha de orden
    4. SKU
    5. cantidad de objetos
    6. Precio del articulo
    7. Precio total del pedido
    8. Código de descuento
    9. Importe de descuento
    10. Etiqueta (utilizada como un método adicional para categorizar a los suscriptores frente a los no suscriptores)
  5. La etapa final aquí fue crear algunas columnas adicionales para manejar más fácilmente las filas duplicadas para diferentes elementos en el mismo orden;
    1. Avgitempprice
    2. descuento medio
    3. Estado del suscriptor (etiquetas + si están presentes en la tabla Bold & Recharge)

Luego programamos esta consulta para que se ejecutara una vez al día y usamos el resultado como tabla maestra para consultas adicionales. Hacer esto significaba que no teníamos que ejecutar la consulta completa varias veces, ya que es una tabla estática que solo se actualiza cuando está programada, en lugar de cada vez que se realiza una solicitud a través del Panel de control de Looker Studio.

Perspectivas

Ahora que cubrimos la metodología y describimos los pasos tomados para obtener todos los datos que necesitamos, podemos pasar a discutir cómo usamos estos datos para responder nuestras preguntas.

¿Qué canales impulsan los registros de suscripción?

  1. Para calcular esto, observamos los primeros pedidos realizados por los clientes, usando el campo que creamos en la tabla maestra que devuelve 'Primero' o 'Repetir' según la fecha del primer pedido.
  2. Usando las columnas adicionales de agrupación de canales y estado de suscriptor dentro de la tabla maestra, podemos ver los primeros pedidos de personas que se convirtieron en suscriptores, ya sea en la primera compra o en la compra repetida, y a qué canal se atribuyeron en GA .
  3. Al eliminar el paso para mirar solo a los suscriptores, también pudimos ver qué canales impulsan predominantemente las primeras compras.

Combinaciones de cestas más allá de GA; ¿Cuáles son las cestas más populares para suscriptores y no suscriptores y cuáles son las más rentables?

  1. Usando la función STRING_AGG, pudimos combinar los diferentes productos de pedidos individuales en una sola fila, separados por ' | '. Básicamente, esto crea una cesta para cada pedido.
  2. Junto con esta información, sumamos los ingresos de cada uno de estos artículos como 'Total de la cesta', con el objetivo de tener una fila por pedido con toda la información relevante.
  3. Por supuesto, hay mucha variación entre los productos (sabor, tamaño, etc.), por lo que agregamos una columna que eliminó esta variación para observar las combinaciones de la canasta de productos base.
  4. Luego usamos 'ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier' para asignar valores secuenciales a cada orden. Esto luego se usó como un filtro en el tablero para aislar filas individuales que aún contenían toda la información.

¿Cuánto tiempo y cuántas compras en promedio toma antes de que un usuario se convierta en suscriptor?

  1. Utilizando nuestra tabla maestra, extrajimos todos los ID de clientes que sabíamos que habían sido suscriptores y tomamos su primera fecha de suscripción.
  2. Luego tomamos la fecha mínima de pedido y usamos la función DATE_DIFF para calcular la diferencia entre estas fechas en 'días'.
  3. El paso final incorporó el canal y la cantidad promedio de días antes de suscribirse, lo que resultó en una tabla que muestra la cantidad promedio de días antes de que los clientes se suscriban.
  4. Para calcular la cantidad promedio de pedidos que los clientes realizaron antes de convertirse en suscriptores, simplemente dividimos el recuento de ID de pedidos únicos por el recuento de ID de clientes únicos donde eran 'Suscriptores' y tenían fechas de pedido menores que la fecha de suscripción mínima.

Estos son solo algunos ejemplos superficiales de las vistas que generamos con estos informes. Con los datos, hay infinitas posibilidades; otros ejemplos de este proyecto incluyen observar el valor de por vida del cliente desde varias perspectivas y evaluar el uso de códigos de descuento y su impacto en el comportamiento de por vida de los clientes. Con estos conocimientos, se pueden utilizar para complementar las decisiones comerciales. Por ejemplo, sabiendo que se necesitan un promedio de 3 pedidos antes de convertirse en suscriptor, puede buscar compartir ofertas e incentivos que animen a los clientes a convertirse en suscriptores después de 2 compras. Los resultados de esto se pueden medir en un momento posterior.

Resumen

Este estudio de caso demuestra el potencial de información increíblemente valiosa basada en datos que se puede obtener a través de la combinación de múltiples fuentes de datos, desde GA hasta Shopify y más. Una vez que se crea esta infraestructura, se puede mantener de forma dinámica y utilizar para informar sobre numerosos aspectos diferentes del negocio.

Si las preguntas que respondemos en este blog son similares a las que te pueden interesar para tu propio negocio, puedes ponerte en contacto con nosotros llenando nuestro formulario de contacto.