El otro día mostré en directo los pasos a seguir para hacer un mapa de calor de visitas por hora y por día de la semana con excel. Debí explicarlo muy rápido, porque @sofisticlara tuvo que grabarlo con el iphone y hoy @noe me ha preguntado «Y eso ¿cómo se hace?» (por cierto, a ver si me pasáis la grabación)
Bueno, vamos paso a paso, que no es difícil. Los pasos a seguir son:
- Importar los datos de Google Analytics a Excel ( lo haremos de forma que los Mac-eros también puedan)
- Preparamos los datos
- Creamos una tabla( dinámica con los datos)
- Utilizamos «formato condicional» y..
- ¡ya tenemos el mapa!
En Google analytics tenemos que situarnos en el gráfico principal (Público->Visión general) y hacer dos selecciones. La primera es de fechas. Seleccionamos una semana completa. La segunda acción es modificar la visualización del gráfico. deberemos seleccionar la visualización por Hora.
Una vez tenemos todo OK, exportamos a «Csv para Excel» y GUARDAMOS EL ARCHIVO no abrimos directamente Excel.
Para importar los datos de la forma correcta debemos ir a DATOS->DESDE TEXTO, Y seleccionar en el cuadro que nos sale «DATOS SEPARADOS POR COMAS».
Después de hacer esto tendremos dos columnas, una con las horas y otra con las visitas.si os fijáis bien las horas van de 0 a 167.Si tenemos en cuenta que cada día tiene 24 horas, algo no cuadra ¿no? Pues sí, si cuadra. de 0 a 23 es el lunes a partir de 24 es el martes… y así tendremos los 7 días de la semana con intervalos de 24 horas..
Ordenemos estos datos para obtener lo que queremos. Lo primero las horas. En la columna horas, de 0 a 23, no hacemos nada, pero en la 24 volvemos a poner 0 y en la siguiente 2, y 3, etc hasta llegar a 23, que volvemos a empezar.
Una vez completada esta «dura tarea de ctrl+c – ctrl+v » creamos una nueva columna donde ponemos el nombre del día ( lunes, martes, etc). De ésta forma ya tenemos tres columnas con toda la información de días de la semana, horas y visitas( o rebote, o nuevas visitas, o lo que queramos)
Ahora llega un momento glorioso.. Hacer una tabla dinámica que organice los datos en filas y columnas. Las filas serán los días de la semana y las columnas las horas del día…
Nos vamos a INSERTAR-TABLA DINÁMICA, en el cuadro seleccionamos las tres columnas de datos enteras y ponemos «etiquetas de columnas = hora», «etiquetas de filas = días» y en «valores=suma de visitas» nos quedaría algo así…
Ya sólo nos queda un paso, poner colorines. Ésto se consigue con el «FORMATO CONDICIONAL».
Seleccionamos una fila de días, y vamos a «formato condicional»-> «escalas de color»…
Es importante que seleccionéis fila a fila, no todo junto, así os aseguráis de que los colores indican lo que queremos…
Después de hacer esto ya tenéis vuestro mapa de calor. Como podréis comprobar, en mi caso, los días de la semana quedan desordenado… no he sido capaz de ponerlo por orden, si alguien se anima…
Espero que os sirva…
Por cierto, siempre puedes apuntarte a mis cursos gratuitos en escuela.analisis-web.es
Bien Bien! voy a hacer yo otro post para hacerlo de otra manera con sparklines!
Gracias Carlos!
dale Roberto!
con sparklines quedan mas bonitos,porque puedes quitar los números…
Yo uso mucho el formato condicional para representar cosas mediante colores. Con openoffice se pueden «quitar» los números poniéndole a la fuente un tamaño = 0, aunque no se si excel lo permitirá. De todos modos, un tamaño = 1 debe dar el mismo resultado.
¡Muchas gracias por toda la info Carlos! 😉
Genial!
Una recomendación, una vez le demos formato condicional a la primera fila, al Lunes, la seleccionamos, vamos a copiar formato y seleccionamos las filas de los demás días y listo 🙂
Genial la explicación Carlos! Ahora voy a ver la propuesta de Roberto con sparklines. Que hay que aprender de todo!
Carlos Muchas gracias!! eres un crack! me ha gustado mucho! Ya lo tengo listo 😉
Carlos, ¿tengo que tener descargado el pluging Excellent Analytics para llegar hacer el mapa de calor o no hace falta?
Gracias por el post!!
@euloger no es necesario tener excellent analytics, es mas, si lees con atención podras comprobar que yo no lo uso en ningún momento..
Hola Carlos. Lo tenía claro, además me fijé en tú tercer pantallazo que no sale el plugin pero como no hay manera de que me aparezca el recuadro «DATOS SEPARADOS POR COMAS» pues he querido descartar opciones. Seguiré intentando.
Saludos y gracias!
Estoy haciendo pruebas con este material, muy agradecido de antemano, mi pregunta es, alguien se le ha ocurrido usar la api de google para no tener que exportar los datos?
Saludos
Hola Darío,
Gracias por comentar. La verdad es que estoy trabajando con la año de analytics para hacer estas cosas mas fáciles y rápidas, pero mis habilidades programatorias no son muchas y voy bastante lento…
Un saludo.
Bueno, pues al final he tenido que realizarla en office 2007 para windows, office 2008 para mac no me deja hacer escala de colores en tablas condicionadas, una pena porque tengo que acceder a un vnc solo para eso.
Lo mismo me compensa hacer sparklines aunque me gusta tal y como esta.
La otra opción sería hacerla en google docs (no es mala opción pero todavía no me agrada la interfaz)
Por otro lado y si me dejan algo de tiempo empezaré a estudiar la API de google para incorporarla al dashboard, si alguien sabe de un buen tutorial para entender la API, estaría muy agradecido.
Saludos y buen finde!