Cómo crear un panel digital todo en uno en Hojas de cálculo de Google

Publicado: 2020-12-14

Es difícil imaginar el trabajo de un comercializador de Internet sin hojas de cálculo. Anteriormente, la herramienta principal era Excel de MS Office, pero ahora cada vez más especialistas se están cambiando a Google Sheets. Y no tiene nada de extraño, considerando las siguientes ventajas:

Las funciones básicas de Google son gratuitas.
Cooperación conveniente: no es necesario enviarse varios archivos todo el tiempo.
Todos los cambios se guardan automáticamente.
Acceso al historial: puede volver al momento en el que todo salió mal.
Importación automática de datos de terceros: servicios de análisis y publicidad, herramienta para rastrear llamadas, etc.

La integración de hojas de Google de Coupler.io es una herramienta versátil y funcional con un montón de características y scripts únicos para usar. Usando solo fórmulas, puede escribir un libro, sin convertir su contenido en una monstruosa lectura larga. Por no hablar de las funciones avanzadas como trabajar con scripts, entrar en detalles y realizar instrucciones detalladas para cada función.

Para un comercializador en línea, la elaboración de informes es una tarea rutinaria. Sin embargo, sin ellos, será difícil organizar el trabajo, conocer sus resultados y mostrárselos a los clientes y a la dirección. Google Sheets puede ayudarlo a crear y almacenar informes de la manera más conveniente. En esta publicación, comprenderemos los parámetros del servicio y aprenderemos cómo crear un panel de control todo en uno definitivo.

Estar involucrado en el comercio electrónico significa estar siempre al tanto de lo que está sucediendo. Si vigila sus tasas de conversión y ventas, siempre podrá identificar los puntos débiles de su negocio y comprender dónde invertir. Es por eso que necesita datos estructurados para ayudarlo con los aspectos comerciales anteriores, así como con muchos más problemas. Después de todo, la calidad y la velocidad de la toma de decisiones dependen directamente de cómo se proporciona la información y qué tan rápido es procesada por nuestro cerebro. Es por eso que los paneles digitales son tan populares en los negocios en línea de hoy en día: visualizan datos críticos de una manera perfecta, lo que le permite controlar su negocio con riesgos mínimos.

Hoy le mostraremos cómo crear un panel digital todo en uno para su empresa en Hojas de cálculo de Google.

Métricas de comercio electrónico

Primero, debe decidir qué métricas enviará a su futuro tablero digital.

Tenga en cuenta que todas las métricas son importantes, sin embargo, no debe sobrecargar su tablero. Determine las métricas más importantes para realizar un seguimiento del rendimiento de su modelo de negocio de comercio electrónico. Tal vez tenga una tienda de servicios o una tienda en línea estándar, realmente no importa. Lo que importa son las siguientes 7 métricas de comercio electrónico que le ofrecemos para que elija para realizar un seguimiento de su negocio. Por supuesto, también puede agregar sus opciones.

Para este artículo, hemos elegido datos de un pequeño minorista de comercio electrónico que vende sándwiches en línea en el Área de la Bahía de San Francisco. En esto se basará nuestro panel de control.

Estas son las métricas de marketing que elegimos:

Distribución de ventas por región (condado de SF Bay Area)
Valor total del pedido y tasa de conversión
Ingresos esperados y totales
Valor medio de la orden
Vida media de la orden
Mejor desempeño en ventas e ingresos

Datos iniciales del tablero digital

Cada métrica de su panel requiere datos específicos de la siguiente manera:

1. Distribución de ventas por región. Para ver cuántas ventas se realizaron y los ingresos en varios condados del Área de la Bahía de San Francisco, se requieren los siguientes datos:

Cada información de venta;
Cada información del cliente.

2. Valor total del pedido y tasa de conversión. Esta es la suma de todos los pedidos realizados, incluidos los que se perdieron. La tasa de conversión de ventas es la relación ventas / número de clientes potenciales calificados. Para calcular esta métrica, necesitará información sobre cada pedido y cada venta.

3. Ingresos esperados y totales. Los ingresos totales = la suma de todas las ventas. Y los ingresos esperados son todas las ventas + pedidos abiertos. Para calcular esta métrica, necesitará información sobre cada pedido por separado.

4. Valor medio del pedido. Ingresos totales al número o proporción de pedidos.

5. Vida media de los pedidos. Necesitará información sobre cada venta para determinar cuánto tiempo lleva realizar una venta.

6. Mejor desempeño en ventas e ingresos . Esta métrica requiere clasificar los 5 productos más vendidos (en nuestro caso, sándwiches) por ventas e ingresos. Y nuevamente, aquí necesitará información sobre cada venta y producto.

Fuentes de datos iniciales

Si su tienda en línea o centro de servicios se ejecuta en una plataforma de comercio electrónico como 3dcart, la mayoría de sus datos estarán allí. De lo contrario, si, por ejemplo, vendes en Instagram o tienes un sitio web con una cartera, puedes optar por almacenar tus datos comerciales donde quieras. Puede utilizar Airtable como base de datos para obtener información sobre productos, clientes, ventas, cuentas bancarias, etc. Su conjunto básico de herramientas de marketing también puede incluir otras características y servicios de la siguiente manera:

Aplicaciones de CRM (por ejemplo, HubSpot o Pipedrive) para una gestión conveniente de clientes / ventas;
Google Analytics para el tráfico del sitio web y el análisis del comportamiento del cliente, etc.
Servicios de marketing por correo electrónico (por ejemplo, Mailchimp o Sender) para mantenerse en contacto con sus clientes.

Elegimos Pipedrive CRM para la gestión de la canalización de ventas y Airtable para el almacenamiento de información de productos / clientes. En consecuencia, estas son nuestras fuentes de datos.

Importación de datos a hojas de Google

Puede hacerlo manualmente (exportar un conjunto de datos de su fuente de datos en un formato de archivo compatible y luego importarlo a Google Sheets) o automáticamente (use una herramienta especial (por ejemplo, Coupler.ia o Automate.io para conectar su fuente de datos a Google Hojas para sincronizar sus datos automáticamente. Elegimos la versión automática y Coupler.io para nuestro caso porque estábamos construyendo un tablero digital en vivo. Otras razones de nuestra elección fueron las siguientes:

Coupler.io extrae datos de varias fuentes como Airtable, Pipedrive, HubSpot, etc., y automatiza las importaciones de datos por programación (cada hora, 3 horas, diariamente, etc.).

Deberá elegir la categoría de datos (Ofertas en nuestro caso) y conectar la hoja de cálculo a Pipedrive. Esto es para importar datos de Pipedrive a Hojas de cálculo de Google.

Ahora, para importar datos de Airtable, se requiere un enlace de vista compartida de su fuente de datos de Airtable. Lo necesitará para la métrica Productos con mejor rendimiento. Una vez que la hoja de cálculo tiene los datos sin procesar, podemos comenzar a construir nuestro tablero.

Cómo crear un panel de comercio electrónico definitivo en Hojas de cálculo de Google

Le mostraremos las fórmulas que usamos para calcular cada métrica a continuación. También tuvimos cada sección representada en una hoja separada para mayor comodidad.

Ventas por Región

Columna del condado de SF

Aplicamos la siguiente fórmula a la celda A2:

= único ('Datos portátiles'! $ B $ 2: $ B)

Esto significa el rango con los nombres de las regiones por cada venta. La función "única" devolverá todos los valores únicos de este rango.

Columna de ventas

La siguiente fórmula debe aplicarse a la celda B2. Arrástrelo hasta el final del rango:

= countif ('Datos de mesa'! $ B $ 2: $ B, A2)

La función "countif" contará las ventas de cada condado.

Columna de ingresos

Aplique la siguiente fórmula a la celda C2 y arrástrela hacia abajo hasta el final del rango:

= sumif ('Datos de mesa móvil'! $ B $ 2: $ B, A2, 'Datos de mesa móvil'! $ I $ 2: $ I)

Este es el rango con el monto por cada venta. La función "sumif" sumará los ingresos de cada condado.

Insertar un gráfico de burbujas

Elija el rango A1: C10 y vaya a Insertar => Gráfico. Elija un tipo de gráfico de burbujas.

Tasa de conversión de ventas

Aplicar la siguiente fórmula:

= CONTAR.SI ('Ofertas de Pipedrive'! $ AP $ 2: $ AP, ”ganó”) /

COUNTA ('Ofertas de Pipedrive'! $ AP $ 2: $ AP)

La primera fórmula es el rango con el estado del pedido: abierto, ganado y perdido. La función "countif" contará todos los pedidos con el estado "ganó". La función "contar" contará todos los pedidos. La tasa de conversión de ventas es el resultado de dividir la primera fórmula por la segunda fórmula. Elija la celda con el valor e inserte un tipo de gráfico de calibre.

Total de pedidos e ingresos

Órdenes totales

Deberá aplicar la siguiente fórmula:

= COUNTA ('Ofertas de Pipedrive'! $ AP $ 2: $ AP)

La fórmula es el rango con el estado del pedido (abierto, ganado, perdido). La función "contar" contará todos los pedidos.

Los ingresos totales

Aplicar la siguiente fórmula:

= SUMA (

Filtro ('Ofertas de Pipedrive'! $ AI $ 2: $ AI, 'Ofertas de Pipedrive'! $ AP $ 2: $ AP = ”ganado”))

Este es el rango con el valor de cada pedido. La función de "filtro" filtrará los pedidos por el estado "ganado". La función "suma" resumirá los pedidos ganados para contar los ingresos totales.

Los ingresos esperados

Deberá aplicar la siguiente fórmula:

= SUMA (

Filtro ('Ofertas de Pipedrive'! $ AI $ 2: $ AI, 'Ofertas de Pipedrive'! $ AP $ 2: $ AP = ”ganado”),

Filtro ('Ofertas de Pipedrive'! $ AI $ 2: $ AI, 'Ofertas de Pipedrive'! $ AP $ 2: $ AP = ”abierto”))

Aquí, la función de "filtro" filtrará los pedidos por dos estados: "ganado" y "abierto". La función "suma" sumará las órdenes ganadas y abiertas para calcular los ingresos esperados.

Cuadro de cuadro de mando

Inserte un gráfico de cuadro de mando para cada métrica individualmente.

Vida útil promedio del pedido y valor promedio del pedido

Valor medio del pedido

Deberá aplicar la siguiente fórmula:

= SUMA (

Filter ('Ofertas de Pipedrive'! $ AI $ 2: $ AI, 'Ofertas de Pipedrive'! $ AP $ 2: $ AP = ”ganado”)) /

COUNTA ('Ofertas de Pipedrive'! $ AP $ 2: $ AP

Tanto los ingresos totales como los pedidos se explican anteriormente.

Vida media de la orden

Aquí es donde debe averiguar cuántos días se invirtieron en cada venta. Para hacer esto, ve a la hoja de ofertas de Pipedrive, crea 1 columna al principio de la hoja y aplica la siguiente fórmula a la celda A1:

= {"Días por pedido"; ARRAYFORMULA (IF (ISBLANK (AY2: AY), "",

MENOS (AY2: AY, AK2: AK)))}

La función “menos” mostrará la diferencia entre la fecha de creación de la orden (AK2: AK) y la fecha de cierre de la orden (AY2: AY).

Luego, deberá volver al panel y aplicar la siguiente fórmula para calcular la vida útil promedio del pedido:

= SI ERROR (PROMEDIO ('Ofertas de Pipedrive'! $ A $ 2: $ A))

Esta es la gama recién creada con días por pedido. La función "promedio" recuperará el valor promedio del rango especificado.

Cuadro de cuadro de mando

Inserte un gráfico de cuadro de mando para cada métrica individualmente.

Desglose u Órdenes

Necesitará las siguientes fórmulas para desglosar los pedidos por estado:

Ordenes abiertas:

= CONTAR.SI ('Ofertas de Pipedrive'! $ AP $ 2: $ AP, "abrir")

Órdenes perdidas:

= CONTAR.SI ('Ofertas de Pipedrive'! $ AP $ 2: $ AP, "perdido")

Órdenes ganadas (ventas):

= CONTAR.SI ('Ofertas de Pipedrive'! $ AP $ 2: $ AP, "ganó")

Ofertas de Pipedrive '! $ AP $ 2: $ AP es el rango con el estado del pedido (abierto, ganado, perdido). La función "countif" devolverá el número de pedidos ordenados por el estado elegido ("abierto", "perdido" o "ganado").

Elija los valores de todos los pedidos por estado y cree un gráfico circular en 3D.

Mejores productos

Deberá filtrar todos los productos, calcular las ventas + los ingresos por cada producto. La siguiente fórmula "única" "extraerá" todos los productos de la columna de productos ('Datos de Airtable'! $ E $ 2: $ E), exportados desde Airtable:

= único ('Datos de mesa'! $ E $ 2: $ E)

Ahora, deberíamos contar las ventas por cada producto usando la siguiente fórmula "sumif":

= sumif ('Datos de mesa móvil'! $ E $ 2: $ E, A2, 'Datos de mesa móvil'! $ H $ 2: $ H)

Arrástrelo hasta el final del rango. Luego, haz lo mismo con la fórmula "sumif" para calcular los ingresos:

= sumif ('Datos de mesa móvil'! $ E $ 2: $ E, A2, 'Datos de mesa móvil'! $ I $ 2: $ I)

Ahora se supone que debe tener una tabla con 3 columnas: Productos (A1: A11), Ventas (B1: B11) e Ingresos (C1: C11). Para obtener los mejores productos, utilice la función "SORTN". Consulte esta fórmula para conocer los 5 productos principales por ventas.

= ORDENAR (A2: B11,5,1, B2: B11, falso)

Y aquí está la fórmula para los 5 productos principales por ingresos:

= ORDENAR ({A2: A11, C2: C11}, 5,1, C2: C11, falso)

Elija las tablas resultantes e inserte un gráfico de tabla, individualmente para cada tabla.

Conclusión

El propósito principal de este artículo es representar el poder de Google Sheets y mostrar sus funciones y características de la mejor manera. Nuestro panel de control se creó a partir de los datos de Pipedrive y Airtable, pero puede aplicar este conocimiento a su proyecto de comercio electrónico o estudio de caso. Muchas herramientas y complementos le permiten sincronizar hojas de cálculo con casi cualquier fuente de datos. Esto le permite agregar métricas universales a su tablero y mantener más datos requeridos en un solo lugar. ¡No pierda el tiempo y utilice Google Sheets con confianza!