Cree un panel de control personalizado de Hubspot automatizado con Google Spreadsheet y Data Studio

Resultado final de un panel automatizado utilizando Hubspot, Google Spreadsheet y Google Datastudio

Introducción

Este artículo está dirigido a gerentes que desean automatizar las cosas de una manera MVP sin la necesidad de su equipo técnico. En este caso, crearemos un panel de control personalizado de Hubspot desde cero utilizando Google Spreadsheet y Google Data Studio.

Soy Alex, uno de los fundadores de Make it Group. Construimos startups en IoT. Visítenos aquí: makeit-group.com

Mi compromiso:

  • Es gratis
  • Es sencillo
  • Hace el trabajo

Saber Javascript no es necesario si solo copia y pega mi código, pero podría ser una ventaja personalizar aún más su tablero.

Si solo está buscando un ejemplo simple sobre cómo recuperar sus contactos de Hubspot y escribirlos en una hoja de cálculo de Google, le recomiendo leer este otro artículo que escribí: Cómo recuperar sus contactos de Hubspot en la hoja de cálculo de Google usando App Script

Si no usa Hubspot pero solo desea crear un panel personalizado, puede consultar mi artículo anterior: ¿Cómo crear un panel personalizado para los KPI de su inicio con la Hoja de cálculo de Google y una Raspberry Pi?

Contexto

Nuestra empresa invierte mucho dinero para poner nuestra marca, productos o servicios a la venta. Pero, ¿qué genera los leads más calificados? ¿En qué punto nuestros leads se convierten o desaparecen? Al final del día, queremos invertir en los canales que funcionan mejor, pero necesitamos esos datos para tomar decisiones inteligentes.

En el pasado, no usábamos ninguna métrica, y las decisiones se basaban en nuestra intuición, lo cual está bien hasta cierto punto. Sin embargo, uno podría sorprenderse de lo que los números nos pueden decir sobre nuestros negocios y, a menudo, nuestras intuiciones nos dan señales falsas.

Vamos a profundizar en

Para lograr nuestra misión, necesitaremos hacer lo siguiente:

  1. Crea una cuenta de desarrollo en Hubspot
  2. Use una hoja de cálculo de Google para autenticarse en Hubspot utilizando sus API
  3. Recupere las ofertas de Hubspot utilizando sus API
  4. Utilice la hoja de cálculo de Google para preparar los datos de acuerdo con nuestras necesidades.
  5. Conecte nuestra hoja de cálculo de Google con Google Data Studio y cree un informe

Vamonos.

Crea una cuenta de desarrollo en Hubspot

Esto es bastante sencillo al crear una cuenta de desarrollador en Hubspot.

Una vez que nuestra cuenta esté lista, necesitaremos crear una aplicación para recuperar nuestro ID de cliente y nuestro secreto de cliente. Se utilizarán para conectar nuestra hoja de cálculo de Google y nuestra cuenta de CRM de Hubspot.

Asegúrese de indicar los ámbitos correctos en la configuración de su aplicación Hubspot como se muestra a continuación:

Indique los alcances correctos en su aplicación Hubspot

Use una hoja de cálculo de Google para autenticarse en Hubspot utilizando sus API

He usado este tutorial si necesita más información, pero veremos cada paso.

Primero necesitamos crear una nueva hoja de cálculo de Google en nuestro Google Drive. Una vez que se abra la hoja de cálculo, vamos a Herramientas> Editor de secuencias de comandos, que abrirá una nueva pestaña en el navegador.

Tendremos que agregar una biblioteca para autenticarnos usando OAuth2:

  1. Haga clic en el elemento de menú Recursos> Bibliotecas ...
  2. En el cuadro de texto Buscar una biblioteca, ingrese el ID de script 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF y haga clic en el botón Seleccionar. Esta es la forma más fácil de agregar la biblioteca a nuestro proyecto, pero consulte este artículo si desea agregar el código manualmente en el proyecto.
  3. Elija una versión en el cuadro desplegable (generalmente es mejor elegir la última versión).
  4. Haz clic en el botón Guardar.

Ahora, escribamos un código paso a paso. Si eres un técnico con cierta experiencia en JS, es posible que solo quieras leer el código completo aquí. Para los otros, solo siga los pasos.

Primero necesitaremos definir algunas variables para la parte de autenticación:

var CLIENT_ID = '...'; // Ingrese su ID de cliente
var CLIENT_SECRET = '...'; // Ingrese su secreto de cliente
var SCOPE = 'contactos';
var AUTH_URL = 'https://app.hubspot.com/oauth/authorize';
var TOKEN_URL = 'https://api.hubapi.com/oauth/v1/token';
var API_URL = 'https://api.hubapi.com';

Agreguemos las siguientes funciones que manejarán la parte de autenticación:

función getService () {
   return OAuth2.createService ('hubspot')
      .setTokenUrl (TOKEN_URL)
      .setAuthorizationBaseUrl (AUTH_URL)
      .setClientId (CLIENT_ID)
      .setClientSecret (CLIENT_SECRET)
      .setCallbackFunction ('authCallback')
      .setPropertyStore (PropertiesService.getUserProperties ())
      .setScope (ALCANCE);
}
función authCallback (solicitud) {
   servicio var = getService ();
   var autorizado = service.handleCallback (solicitud);
   si (autorizado) {
      return HtmlService.createHtmlOutput ('¡Éxito!');
   } más {
      return HtmlService.createHtmlOutput ('Denegado');
   }
}

Ahora necesitamos la función para ejecutar la autenticación:

función autenticar () {
   servicio var = getService ();
   if (service.hasAccess ()) {
      // ... lo que sea necesario hacer aquí ...
   } más {
      var authorUrl = service.getAuthorizationUrl ();
      Logger.log ('Abra la siguiente URL y vuelva a ejecutar el script:% s' ,autorUrl);
   }
}

Probemos la parte de autenticación. Si ha completado el ID del cliente y el secreto del Cliente y ejecuta la función de autenticación, deberá:

  1. Recupere la URL de autorización que se mostrará en los Registros (Ver> Registros).
  2. Abra esa URL en su navegador, se mostrarán sus cuentas de Hubspot.
  3. Seleccione la cuenta de Hubspot que desea conectar a su hoja de cálculo

¡Bien hecho, estás dentro!

Recupere las ofertas de Hubspot utilizando sus API

Vamos a simplificarlo, hagamos lo mínimo para recuperar esos datos y completar nuestra hoja de cálculo. Luego, utilizaremos las herramientas de la hoja de cálculo de Google para preparar los datos de acuerdo con nuestras necesidades.

(Si usted es un desarrollador, se sentirá tentado a volverse loco y trabajar en los datos en JavaScript, sin embargo, sugeriría no reinventar la rueda y usar las funciones integradas de la hoja de cálculo de Google).

Comencemos por obtener las etapas de su canalización. Puede copiar y pegar la siguiente función en su editor de Script debajo del código existente:

función getStages () {
  // Preparar autenticación para Hubspot
  servicio var = getService ();
  encabezados var = {encabezados: {'Autorización': 'Portador' + service.getAccessToken ()}};
  
  // solicitud de API
  var pipeline_id = "default"; // Ingrese su identificación de tubería aquí.
  var url = API_URL + "/ crm-pipelines / v1 / pipelines / Deals";
  respuesta var = UrlFetchApp.fetch (url, encabezados);
  resultado var = JSON.parse (response.getContentText ());
  etapas var = matriz ();
  
  // Recorriendo las diferentes tuberías que podrías tener en Hubspot
  result.results.forEach (function (item) {
    if (item.pipelineId == pipeline_id) {
      var result_stages = item.stages;
      // Ordenemos las etapas por displayOrder
      result_stages.sort (function (a, b) {
        devuelve a.displayOrder-b.displayOrder;
      });
  
      // Pongamos todas las etapas utilizadas (id y etiqueta) en una matriz
      result_stages.forEach (function (stage) {
        stage.push ([stage.stageId, stage.label]);
      });
    }
  });
  
  etapas de retorno;
}

Este fragmento de código:

  1. Conéctese a Hubspot.
  2. Obtenga la configuración de canalización predeterminada * completa, incluidas sus etapas.
  3. Clasificará las etapas en consecuencia.
  4. Coloque los resultados en una matriz que podamos usar más adelante.

* Hubspot CRM le permite configurar múltiples canalizaciones. Debe cambiar el valor predeterminado por cualquier otra identificación de canalización que pueda tener configurada. Consulte esta página si desea obtener más información sobre la API de canalizaciones CRM de Hubspot.

Ahora también podemos recuperar todas las ofertas que fluyen a través de nuestra cartera. Este código se encargará de eso:

NB: He agregado un campo personalizado "fuente" a nuestras ofertas en nuestra configuración de Hubspot. Este no es un campo predeterminado de Hubspot. Por lo tanto, le sugiero que agregue esto a sus ofertas o cambie la "fuente" por cualquier otro campo personalizado que desee de sus ofertas.

función getDeals () {
   // Preparar autenticación para Hubspot
   servicio var = getService ();
   encabezados var = {encabezados: {'Autorización': 'Portador' + service.getAccessToken ()}};
   // Preparar paginación
   // Hubspot te permite tomar un máximo de 250 ofertas por solicitud.
   // Necesitamos realizar múltiples solicitudes hasta que obtengamos todas las ofertas.
   var keep_going = true;
   desplazamiento var = 0;
   ofertas var = matriz ();
   while (keep_going) {
      // Tomaremos tres propiedades de las ofertas: la fuente, la etapa y la cantidad de la oferta
      var url = API_URL + "/ ofertas / v1 / oferta / paginada? propiedades = oferta y propiedades = fuente y propiedades = cantidad y límite = 250 y desplazamiento =" + desplazamiento;
      respuesta var = UrlFetchApp.fetch (url, encabezados);
      resultado var = JSON.parse (response.getContentText ());
      // ¿Hay más resultados? ¿Deberíamos detener la paginación?
      keep_going = result.hasMore;
      offset = result.offset;
      // Para cada oferta, tomamos el stageId, fuente y cantidad
      result.deals.forEach (function (deal) {
         var stageId = (deal.properties.hasOwnProperty ("dealstage"))? deal.properties.dealstage.value: "desconocido";
         var source = (deal.properties.hasOwnProperty ("fuente"))? deal.properties.source.value: "desconocido";
         cantidad var = (deal.properties.hasOwnProperty ("cantidad"))? deal.properties.amount.value: 0;
         Deals.push ([stageId, fuente, cantidad]);
      });
   }
   ofertas de devolución;
}

Este fragmento de código:

  1. Conéctese a Hubspot.
  2. Obtenga todas las ofertas y agregue las siguientes propiedades: etapa, fuente y cantidad de la oferta.
  3. Coloque los resultados en una matriz que podamos usar más adelante.

Puede encontrar más información sobre cómo recuperar las ofertas aquí.

Ok, tenemos los datos en matrices, pero ahora si queremos poder jugar con ellos usando Google Spreadsheet, necesitamos imprimir los datos en hojas. Primero creemos dos hojas en nuestra hoja de cálculo, llamémoslas Etapas y Ofertas.

var sheetNameStages = "Etapas";
var sheetNameDeals = "Ofertas";
función writeStage (etapas) {
   var ss = SpreadsheetApp.getActiveSpreadsheet ();
   var sheet = ss.getSheetByName (sheetNameStages);
   // Pongamos algunos encabezados y agreguemos las etapas a nuestra tabla
   matriz var = matriz (["StageID", "Etiqueta"]);
   matriz = matriz.concat (etapas);
   // Escribir la tabla en la hoja de cálculo
   rango var = sheet.getRange (1,1, matrix.length, matrix [0] .length);
   range.setValues ​​(matriz);
}
función writeDeals (ofertas) {
   var ss = SpreadsheetApp.getActiveSpreadsheet ();
   var sheet = ss.getSheetByName (sheetNameDeals);
   // Pongamos algunos encabezados y agreguemos las ofertas a nuestra tabla
   matriz var = matriz (["StageID", "Fuente", "Cantidad"]);
   matriz = matriz.concat (ofertas);
   // Escribir la tabla en la hoja de cálculo
   rango var = sheet.getRange (1,1, matrix.length, matrix [0] .length);
   range.setValues ​​(matriz);
}

Este código escribirá respectivamente las etapas y las ofertas en las hojas correctas mediante los siguientes pasos:

  1. Conéctese a la hoja de la derecha.
  2. Prepare una tabla con las columnas correctas.
  3. Agregar los datos a la tabla.
  4. Escribir los datos en la hoja.

¡Eso es! La mayor parte del código está hecho, solo necesitamos crear una rutina simple a la que podamos llamar recurrentemente usando los disparadores del Proyecto:

función refrescar () {
   servicio var = getService ();
   if (service.hasAccess ()) {
      var etapas = getStages ();
      writeStage (etapas);
      ofertas var = getDeals ();
      writeDeals (ofertas);
   } más {
      var authorUrl = service.getAuthorizationUrl ();
      Logger.log ('Abra la siguiente URL y vuelva a ejecutar el script:% s' ,autorUrl);
   }
}

Esta rutina hará lo siguiente:

  1. Intenta conectarte a Hubspot
  2. Si tiene éxito, obtendrá los datos y los imprimirá en las hojas correspondientes.
  3. Si no tiene éxito (probablemente la primera vez que ejecuta el script), imprimirá la URL de autorización en los registros (Ver> Registros) que deberá copiar y pegar en su navegador.

Ahora podemos decirle a nuestro script que ejecute la función refresh () cada hora para seguir recuperando los datos más actualizados. Puede ir a Editar> Desencadenantes del proyecto actual.

Preparación de desencadenantes de proyecto en la hoja de cálculo de Google para actualizar los datos de forma recurrente

Puede encontrar todo el código aquí para su comodidad, así como la hoja de cálculo de Google.

Es hora de probar nuestro script y ver qué sucede. No olvide que la primera vez que ejecutará el script, deberá autenticarse en Hubspot como se explicó anteriormente. Luego, si el código se ejecuta sin errores (lo que debería ser si hiciera mi trabajo correctamente ;-)), puede ir a su hoja de cálculo de Google y ver los datos poblados en las hojas correspondientes.

Utilice la hoja de cálculo de Google para preparar los datos de acuerdo con nuestras necesidades.

Pudimos recuperar los datos de Hubspot con el script anterior. Ahora, podemos usar la hoja de cálculo de Google para preparar los datos como queramos antes de enviarlos a Google Data Studio. Entonces, ¿qué quiero saber?

  • ¿Hay cuellos de botella en mi tubería? En otras palabras: ¿cuál es el número actual de ofertas por etapa en la tubería?
  • ¿Está mi tubería con suficiente dinero para poder pronosticar mi flujo de caja? En otras palabras: ¿Cuál es la cantidad actual de dinero en cada etapa?
  • ¿Qué canales funcionan mejor y en cuál debo invertir más? En otras palabras: ¿Cuáles son las tasas de conversión y las cantidades de dinero generadas por fuente?

Para todos esos números y con los datos que obtuve de Hubspot, solo necesito crear algunas tablas Pivot. Para crear uno, vamos a nuestra hoja de cálculo de Google y hacemos clic en Datos> Tabla dinámica.

Sin embargo, antes de crear nuestra primera tabla dinámica, necesitaremos vincular nuestra hoja Etapas con nuestra hoja Ofertas. De hecho, nuestra hoja de Ofertas contiene nuestros StageID, pero preferimos tener una tabla completa que contenga la etiqueta de nuestras Etapas, de lo contrario, nuestro informe solo mostrará las ID que no son realmente fáciles de usar.

En la hoja de Ofertas, vaya a la columna D y agregue esta línea en la primera celda de la columna:

= ARRAYFORMULA (IFERROR (VLOOKUP (A: A, Stages! A: B, 2, FALSE), "Desconocido"))

Descompongamos esta línea:

  1. BUSCARV (A: A, ¡Etapas! A: B, 2, FALSO): Haga coincidir la columna A con la tabla en Etapas y obtenga el valor en la segunda columna.
  2. IFERROR: Si no encuentra ninguna coincidencia, hágamelo saber como "Desconocido"
  3. FORMATO DE ARRAY: Aplicar esta regla a toda mi columna.
Vinculación de etapas a StageID con VLOOKUP para recuperar las etiquetas adecuadas

Bien, estamos listos, creemos nuestra primera tabla dinámica para obtener el número actual de ofertas por etapa.

Cree una nueva tabla dinámica, generará una nueva hoja a la que puede cambiar el nombre por Etapas: Conteo. Siga los siguientes pasos en el editor de tabla dinámica:

  1. Seleccione un rango de datos. El rango de datos debe ser todo el rango de datos en su hoja de Ofertas.
  2. Agregue una fila y elija StageName
  3. Agregue un valor y elija StageID y establezca Resumir por: COUNTA

Ahora tiene una tabla que contiene las cantidades de ofertas por etapa. Utilizaremos esta tabla en nuestro informe de Google Data Studio.

Crear una tabla dinámica con la hoja de cálculo de Google para obtener el número de ofertas por etapa en la tubería

Podemos hacer lo mismo para recuperar la cantidad de dinero en cada etapa. Creemos una nueva tabla dinámica, pero en lugar de elegir StageID como Valor, tomemos Cantidad y establezcamos Resumir por: SUMA.

Crear una tabla dinámica con la hoja de cálculo de Google para obtener la cantidad de ofertas por etapa en la tubería

Para obtener las tasas de conversión por fuente, podemos crear una siguiente tabla dinámica con los siguientes parámetros:

  • Fila: fuente
  • Columna: StageName
  • Valor: StageID Resumir por COUNTA

Luego podemos agregar una columna al final de la tabla dinámica donde calculamos la tasa de conversión. Esto dependerá de sus etapas, pero en mi caso la etapa donde ocurren las conversiones se llama Won cerrado. Por lo tanto, calculo mi tasa de conversión dividiendo la columna de Ganado Cerrado con la de Gran Total.

Crear una tabla dinámica con la hoja de cálculo de Google para calcular las tasas de conversión

Finalmente, para obtener la cantidad de dinero generada por la fuente, creo una última tabla dinámica con los siguientes parámetros:

  • Fila: fuente
  • Columna: StageName
  • Valor: Cantidad resumida por SUM

La columna Closed Won indica la cantidad de dinero generada por cada fuente.

Estamos listos con todos los conjuntos de datos requeridos hasta ahora, vamos ahora a Google Data Studio para la última pieza ;-)

Conecte nuestra hoja de cálculo de Google con Google Data Studio y cree un informe

En los pasos anteriores, recuperamos los datos de Hubspot con su API usando OAuth2. Luego preparamos los datos con la hoja de cálculo de Google. Ahora estamos listos para mostrar esos datos en un bonito panel personalizado con Google Data Studio.

Es bastante sencillo, así que no voy a entrar demasiado en detalles, solo te diré lo básico para comenzar. Primero, necesitaremos crear un nuevo informe. Entonces necesitaremos conectar una fuente de datos. Data Studio le permite elegir entre diferentes fuentes de datos y, en particular, una hoja de cálculo de Google, que es la que obviamente necesitaremos.

Por supuesto, es posible que también desee verificar todos los demás, especialmente Google Analytics, Adwords o cualquier otra herramienta que ya pueda utilizar para su negocio. Incluso hay conectores de la comunidad también (desafortunadamente, ningún conector Hubspot existe hasta la fecha).

Creemos nuestro primer gráfico de barras que muestre la tasa de conversión por fuente.

Al agregar un gráfico, aparece un menú a la derecha donde podemos editar la fuente de datos. En este momento, necesitamos agregar la fuente de datos de la hoja Fuentes: tasas de conteo y conversión en la que creamos la tabla dinámica que contiene las tasas de conversión.

Cuando agrega una fuente de datos de hoja de cálculo de Google, le permitirá configurar los diferentes campos que se detectan en la hoja elegida. Por lo general, rellena automáticamente la columna Agregación con SUM cuando detecta números. En la mayoría de los casos, solo alterno todos esos campos como Ninguno.

Conectar una fuente de datos a Google Data StudioCrear un diagrama de barras con Google Data Studio

Una vez que se ha realizado la conexión, debemos agregar las dimensiones y métricas adecuadas. Por ejemplo, podemos configurar la Fuente para la Dimensión y la Tasa de conversión como métricas para obtener el gráfico anterior.

Tendremos que agregar una fuente de datos en Data Studio para cada hoja o tabla dinámica que creamos en la hoja de cálculo de Google. Una vez que se hayan agregado todas las fuentes de datos, podemos replicar los pasos anteriores para crear todos los gráficos, cuadros de mando o cualquier otra herramienta sofisticada que desee agregar a su tablero personalizado.

Bonificación: ¿Qué pasa con tener los datos a lo largo del tiempo para ver la evolución de nuestro rendimiento día a día?

Por ejemplo, quiero saber cómo evolucionan los leads con el tiempo, dónde nuestro equipo mejora o carece, qué canales debo revisar, ya que no han enviado ningún leads desde hace unos días o, por último, cuál fue el impacto que tuvo en la cartera de ventas cuando un vendedor de nuestro equipo estuvo enfermo la semana pasada.

Hay una manera fácil de registrar los datos a lo largo del tiempo. Aquí hay un código que puede agregar al script para lograr esto:

función logSources () {
   var ss = SpreadsheetApp.getActiveSpreadsheet ();
   var sheet = ss.getSheetByName ("Fuentes: recuento y tasas de conversión");
   var getRange = sheet.getRange ("M3: M13");
   var fila = getRange.getValues ​​();
   row.unshift (nueva fecha);
   matriz var = [fila];
   var ss = SpreadsheetApp.getActiveSpreadsheet ();
   var sheet = ss.getSheetByName ("Log: Sources");
   var lastRow = sheet.getLastRow ();
   var lastCol = sheet.getLastColumn ();
   // Escribir al final de la hoja de cálculo
   var setRange = sheet.getRange (lastRow + 1,1,1, row.length);
   setRange.setValues ​​(matriz);
}

Esto es lo que hace:

  1. Copia la columna derecha en la hoja derecha y la almacena en una matriz.
  2. Agrega la fecha al comienzo de la matriz.
  3. Selecciona y pega los datos en la última fila de la hoja Registro: Fuentes.

Podemos hacer lo mismo para las etapas.

Por último, podemos agregar los desencadenantes del proyecto para que los datos se registren una vez al día.

Los nuevos datos se pueden conectar a nuestro Google Data Studio de manera similar a como se explicó anteriormente.

Resultado final

Encontrará una captura de pantalla de nuestro panel de control personalizado final a continuación después de seguir los pasos anteriores. Es bastante sencillo, y ya podemos tener una visión general impresionante de lo que está sucediendo en nuestra cartera de ventas. Solo tenemos que esperar un par de días para ver la evolución en el tiempo y ver cómo se desempeña nuestro equipo de ventas.

Para su comodidad, encuentre los siguientes documentos usados:

  • Guión completo de la aplicación Google: haga clic aquí
  • Ejemplo de hoja de cálculo de Google: haga clic aquí
  • Panel de muestra de Google Data Studio: haga clic aquí

Próximos pasos ?

En el momento en que escribo este tutorial, no existe un conector de comunidad para conectar Hubspot CRM a Datastudio. Puede ser una buena idea construirlo para que otros puedan beneficiarse de él. Sin embargo, el uso de la hoja de cálculo de Google como un paso intermedio le brinda más flexibilidad y poder para hacer lo que se adapte a sus necesidades. Si Hubspot es su fuente de datos, Data Studio su panel de control, es posible que desee considerar mantener su Hoja de cálculo como un "cerebro" para interpretar sus datos antes de enviarlos y mostrarlos con Data Studio.

Por favor comente para obtener sus ideas.

Preguntas o sugerencias? Contáctame en LinkedIn o échanos un vistazo aquí.