Cómo elaborar un mapa de calor de visitas en Excel

Cómo elaborar un mapa de calor de visitas en Excel

En este artículo os contamos como elaborar, de forma automática, un mapa de calor o heatmap que nos muestre en que días y horas se concentran las visitas de una web, utilizando para ello Excel y el API de Google Analytics.

Como punto de partida necesitamos una hoja Excel con las funciones de conexión al API de GA. Podemos utilizar, por ejemplo, la que nos proporciona Iñaki Huerta en su blog, pero valdría cualquier función que ya estéis utilizando para la consulta de datos desde Excel. (ACTUALIZACIÓN: desde el 21/4/2015, debido a un cambio en la forma de autenticación de Google, esta hoja deja de funcionar. La alternativa más parecida es Supermetrics Functions.)

Una vez configurados los datos de conexión de la hoja, crearemos una tabla con los días de la semana en el eje X y las horas en el eje Y:

Preparación del mapa de calor Excel

Será necesario dejar una columna vacía entre cada uno de los días de la semana, en la que aparecerán las horas de cada consulta, y que luego tendremos que ocultar.

El siguiente paso consiste en obtener los datos de cada celda. Utilizaremos una llamada al API para cada día de la semana, de manera que con sólo 7 consultas al API, rellenemos las 168 celdas. Para ello seleccionaremos las celdas C7:D30, pulsaremos F2 y escribiremos lo siguiente:

=gaData($B$2;$B$3;$B$4;"visits";"hour";"hour";"dayofweek==1")

En esta función, el primer parámetro es la clave de conexión, los dos siguientes las fechas de inicio y fin, y el resto son la métrica a obtener (visitas), la dimensión (horas), el parámetro por el que queremos ordenar la serie de datos (horas) y el filtro a aplicar. En este caso filtraremos por el día de la semana, teniendo en cuenta que para Google Analytics el domingo es el día 0 y el sábado el día 6).

Primera columna mapa de calorA continuación pulsaremos ctrl+shift+enter (para introducir la fórmula como matriz) y obtendremos los datos. Este proceso lo repetiremos para cada día de la semana, y obtendremos todos los datos de la semana

Captura de datos para el mapa de calor en Excel

Ahora únicamente nos falta dar formato a estos datos para obtener el resultado. Primero ocultaremos las celdas correspondientes a las horas de cada día. Basta con seleccionar las columnas y en el menú contextual elegir “Ocultar”.

Tabla de datos para el mapa de calor en Excel

A continuación utilizaremos el formato condicional “escalas de colores rojo, amarillo, verde” para aplicar los colores del heatmap:

Formato mapa de calorEl último paso es quitar los números para tener únicamente el gradiente de colores. Esto se consigue aplicando un formato personalizado a las celdas. Seleccionaremos todo el cuadro y editaremos su formato de celda, eligiendo dentro de la categoría personalizada el tipo “;;;”:

Formato personalizado en ExcelTras esto ya tendremos el resultado completo:

Mapa de calor en Excel

La gran ventaja de este método es que una vez construida la hoja, podremos utilizarla para cualquier periodo o web, cambiando únicamente los parámetros de conexión y las fechas, y el heatmap se actualizará de forma rápida y automática.

Sobre Fernando Cabello Gil

Ingeniero superior de telecomunicaciones. Máster en Finanzas y Marketing por EOI y ESIC. Más de 15 años de experiencia en el mundo de Internet y Tecnologías de Información, como jefe de proyecto y responsable de sistemas de ISP y de ingeniería de servicios móviles y productos para móviles en el mercado de consumo.

Trackbacks/Pingbacks

  1. @RTSpain - RT @la_metrica: Cómo elaborar un mapa de calor de visitas en Excel http://t.co/iqC9s2QBlW
  2. @ogpeinado - RT @la_metrica: Cómo elaborar un mapa de calor de visitas en Excel http://t.co/vbaW3tLYA7
  3. @piyayo5 - RT @la_metrica: Cómo elaborar un mapa de calor de visitas en Excel http://t.co/vbaW3tLYA7
  4. @richir6 - RT @la_metrica: Cómo elaborar un mapa de calor de visitas en Excel http://t.co/vbaW3tLYA7
  5. @jmlametrica - RT @la_metrica: Cómo elaborar un mapa de calor de visitas en Excel http://t.co/vbaW3tLYA7

Deja un comentario