En una entrada anterior os comentaba como crear Dashboards de analítica web en Excel. Entre otras cosas se explicaba como partiendo de hojas de datos ya creados se llegaban a formular dashboards que resumiesen y procesasen la información. Si no lo viste en su momento, ahora tienes otra oportunidad (ahora o cuando quieras).

En el post comenté varias herramientas que podemos usar para conseguir extraer datos de Google Analytics e importarlos a nuestros Exceles para trabajarlos. Para mi lo más adecuado será siempre usar macros para crear funciones propias. Pierdes la intuición de los menús, pero ganas mucha funcionalidad.

Sin embargo los macros para excel se han complicado con las actualizaciones de la API de analytics ya que muchos de los macros de Excel para extraer datos de la API de Google Analytics han dejado de funcionar debido a que Google dejó de dar soporte a las versiones más viejas de su API.

En este post, lo que veremos es un código propio (basado en la idea de AutomateAnalytics, para que negarlo) que nos permitirá seguir trabajando con Excel de forma gratuita. Veámoslo.

Descargando el material

Lo primero es disponer de un archivo Excel con los macros adecuados, que nos permitan usar la API de analytics con una autenticación válida.

Puedes descarga el archivo ikhuertaGAExtractor.xlsm.

Este es un archivo Excel con Macros. Nada más abrirlo el sistema nos indicará justo encima de las celdas que las macros no están habilitadas. Nuestro primer paso deberá ser por lo tanto habilitarlas con el botón que aparece. Puede incluso que se queje dos veces: una por ser un archivo de internet y otra por los macros.

Como decía este Excel es propio, programado según mis necesidades. Pero no puedo atribuirme el mérito. Primero, es evidente que la idea inicial es la de automateAnaltytics, las funciones no son las mismas pero quien haya usado automate verá claras similitudes. Para continuar he de decir que fue Gianluca Giovinazzo, el primero que me indicó la posibilidad de «arreglar» el código de Automate cambiando el tipo del login y cambiando en definitiva la versión de la API usada por la nueva. Misma idea, pero muy actualizada. A partir de ahí trabajé en esta versión de Excel que lleva hecha unos meses pero que aún no había publicado.

En este archivo encontraremos una primera hoja de configuración y dos hojas para carga de datos vacías. La misma hoja de configuración ya incluye la documentación sobre el uso de sus distintas funciones de forma que las tengamos siempre a mano.

Funciona: pero sigue siendo un método antiguo

Google quiere que sus conexiones a la API sean seguras. Por eso está cerrando poco a poco el grifo. Haciendo que todos los sistemas pasen por OAuth2.0 y por eso las viejas versiones de Excel dejaron de funcionar.

El código de este Excel utiliza un sistema de autenticación más moderno que los viejos Excel pero sigue sin ser OAuth2.0 (es sumamente difícil conseguir OAuth2.0 con la programación de Excel). ¿Que quiero decir con esto? Pues que la autenticación a día de hoy funcionará, pero terminará caducando algún día. Estáis avisados.

Consideraciones previas… ¿seguro que quieres cargar datos todo el tiempo?

Con este tipo de importaciones por Macros es conveniente mencionar que el proceso que podemos estar encargándole a Excel puede ser muy grande. Piensa que por cada cambio que hagas en las hojas Excel va a vover a lanzar muchos procesos… algo que puede ponernos el ordenador a templar y provocar el tipico «(no responde)»…

Por ese motivo tenemos que ser conscientes de cuanto trabajo le estamos encargando a Excel y bloquear la actualización automática de datos si lo estamos empezando a sobrecargar. Por lo general una o dos importaciones de datos con las Macros de Automate Analytics no nos van a dar mucho problema, pero si empezamos a hacer un mayor número o a crear un Dashboard a partir de los datos procesados quizás si que es conveniente que bloquees la actualización.

Para bloquear la actualización de datos automática debemos ir a Menu > Fórmulas > Opciones para el Cálculo y ahi seleccionamos «Manual». A partir de ese momento las fórmulas solo se recalcularán cuando presionemos MAYUSCULAS + F9. Esto puede resultar un poco incómodo cuando no estás acostumbrado pero aligera mucho la carga del ordenador.

En todos los ejemplos que se mostrarán a partir de ahora se ha deshabilitado la actualización automática así que para actualizar los datos de cualquier hoja será necesario presionar MAYUSCULAS + F9 o volver a habilitar la actualización automática.

Generando el Token de Analytics

Para poder trabajar con la carga de datos de Analytics vamos a necesitar un Token. Este es un identificador de nuestra conexión validada con analytics. Sin el, Analytics no va a darnos permiso para leer nada.

El Excel entregado está sin autenticar. Por ese motivo vemos las celdas B3:B6 sin datos y por ese motivo la consulta de ejemplo que existe en las celdas I3:J7 muestra un error. Así que lo que necesitamos es indicar esa configuración para que en la celda B7 se generé un token (una serie de carácteres que sirven como llave en cada petición de analytics).

Para generar este token necesitamos 3 campos:

• Nuestro email
• Nuestro password
• La Simple API KEY

Los dos primeros ya los tenemos. Pero el último hay que generarlo. Sin mayor explicación indico los pasos a seguir para conseguir la API Key de y paso configurar la conexión para que sea más rápida que por defecto.

• Accede a Google APIs Console
• Crea un proyecto con un nombre concreto
• Ve al menú Servicies y ahí pasa a «on» la pestaña de Google Analytics API para activar tu acceso
• Ve a Quotas. Clica en el botón «Set per-user limits» y pasa las peticiones a Google Analytics de 1 a 10 por segundo (si indicáis más de 10, no nos dejará).
• Ve a API access y en la parte baja de la página encontrarás el API Key para el Simple API Access. Copia esa cadena de texto exacta (sin espacios)

Ahora rellena las celdas.

• B3 con tu email
• B4 con tu password (se verá codificado, pero cuidado que puede ser copiado si le das acceso a otra persona a tu excel.
• B5 con la API Key que has copiado.

Es importante destacar que la API Key solo funcionará con la cuenta que la ha creado, por lo que no podemos usar una API Key de un email e intentar logarnos con otro.

Solo nos queda indicar el ID de perfil. Este es un número que identifica cada perfil creado en nuetras propiedades de Analytics. El id NO ES el «UA-123456-78», es un número concreto.

La forma fácil de encontrarlo es accediendo en Analytics a Administrador > Entrar en tu perfil > Configuración del Perfil. Ahi veremos un campo no editable con el ID de perfil de nuestra cuenta.

Las viejas funciones de AutomateAnalytics te permitían sacar tus IDs de perfil disponibles. Este Excel no lo permite pues yo no suelo necesitarlos.

Una vez copiemos un id de perfil al que nuestro email tenga acceso, veremos como se actualiza la consulta por defecto con los datos correctos.

Asociando Fórmulas a matrices o rangos de celdas

Excel permite asignar formulas no solo a una celda, sino a un conjunto de ellas. La forma de conseguir esto es de todo menos lógica. Para hacerlo lo que tenemos que hacer es…

1.- Seleccionar con el ratón el rango de celdas al que aplicamos la formula de matriz.

2.- Vamos a la casilla de fórmulas y escribimos ahí nuestra fórmula.

3.- Al terminar, en lugar de presionar ENTER como de costumbre, tenemos que presionar CTRL+MAYUSCULAS+ENTER. Esto asignará la fórmula a toda la selección.

Podremos comprobar que lo hemos hecho bien revisando ahora todas las celdas de nuestra selección y viendo como en todas ellas aparece nuestra fórmula pero indicada entre llaves ( {=nuestraformula()} )

Indicando los campos básicos de nuestras consultas

Si bien ya tenemos todos los datos necesarios para hacer consultas a Analytics, veremos más adelante que la función para sacar datos, sin ser compleja, tiene muchos campos que indicar. Es por ello que es preferible que al menos algunos de ellos (los que tu desees) los indiquemos antes en celdas de forma que tengamos fácil editarlos y visualizarlos.

Así que lo que haremos será destacar 2 datos más a nuestra hoja de bbdd.analytics: la fecha de inicio de la extracción y la fecha de fin de la misma.

Lo más seguro es que todo nuestro excel trate sobre la misma web y trabaje el mismo rango de fechas, por eso hemos incluido estos tres. Si no es así, tal vez quieras incluir más rangos para hacer más consultas… Tu mismo!

Como verás ya hemos preparado en las celdas B9 y B10 estas dos fechas. Por defecto nos muestran los datos desde el inicio de mes hasta hoy. Para ello se han usado las fechas de ayuda que encontraremos en el lateral del Excel, pero eres libre de usar las fechas que tu quieras (incluso directamente sin referencias).

En nuestro caso los valores usados son:

B9: J27
B10: J14

A su vez estas celdas contienen estas fórmulas:

J14: HOY()
J27: =HOY()-DIA(HOY())+1

Pero en definitiva, puedes incluir las fechas que quieras mientras sean pasadas.

Definiendo nombres a nuestras celdas más usadas

Como es bastante probable que queramos usar constantemente el valor de ciertas celdas debemos conocer las opciones de nombre de las que disponemos con excel. Básicamente podemos seleccionar cualquier celda o grupos de celdas y ponerles un nombre con el que acceder a ellas más fácilmente.

Para ello.

1. Seleccionamos la celda
2. Clicamos con el botón derecho del ratón, se abrirá un menú.
3. Seleccionamos «definir nombre», se abrirá una nueva ventana
4. Le ponemos un nombre sencillo que podamos recordar.

En micaso seleccionaré las celdas de Conection Key, Fecha inicio y Fecha Fin. Poniéndoles los nombres respectivamente de «token», «start» y «end». A partir de ahora ya no tendré que usar GAconfig.B7 para referirme al token de conexión sino que valdrá con poner «=token» y Excel ya lo entenderá.

Creando hojas de datos a partir de Google Analytics

Bien, ya lo tenemos todo configurado, solo queda sacar las hojas de datos.

Para ello utilizaremos la función principal de este excel: ga(). Esta función usa 8 parametros seguidos para hacer la extracción. Esto es porque esta usando el mismo tipo de petición que realmente se hace hace con la API de analytics y para permitirnos aprovechar toda su complejidad se da la oportunidad de usarlo todo. Así que tomémosnoslo como una ventaja, no como un inconveniente 😉

Los datos a incluir son los siguientes:

=ga(
1º Connection Key (autogenerado)
2º Fecha Inicio (opcional, hace 31 dias)
3º Fecha Fin (opciona, ayer)
4º Métricas a extraer (opcional, ga:visits)
5º Dimensiones a extraer (opcional)
6º Orden (opciona, -primera metrica)
7º Filtros a aplicar (opcional)
8º Filtro a aplicar como segmento (opcio.)
     (segmento dinámico o gaid:: para los ya definidos)
);

También disponemos de la función gaData() que funciona exactamente igual, pero que no incluye las cabeceras en las tablas de las extracciones sacadas (son la misma funcion, una con cabeceras y otra sin).

Muchos de ellos ya los hemos creado y el resto son parte la propia solicitud que estamos deseando insertar. Para simplificar nuestras solicitudes lo que nosotros haremos será fijarnos de momento solo en los datos más básicos: qué metricas queremos y en función de que dimensiones.

Así que creamos una página nueva en nuestro archivo excel: datos.landings&KW que es la hoja donde queremos que queden nuestros datos brutos de analytics de visitas y rebotes por landings + KW.

Lo que queremos son 2 dimensiones y 2 métricas por lo que en nuestra selección de datos sabemos que tendremos un total de 4 columnas (2 para dimensiones y 2 para métricas).

Así en esa nueva hoja seleccionamos las columnas A:D (de la A a la D) lanzamos la función ga() con el siguiente código:

=ga(token;start;end;"ga:visits,ga:bounces";"ga:landingPagePath,ga:keyword";"-ga:visits")

Presionamos CTRL+MAY+ENTER para aplicar la fórmula a toda la matriz y veremos como se completan los datos de la hoja con nuestros datos de Google Analytics según la cuenta seleccionada y el rango de fechas indicado.

Ya tenemos importaciones de Analytics directas en hojas de datos de Excel. 🙂

Ahora solo nos queda elegir las exportaciones correctas y crear nuestros informes.

¿Qué dimensiones puedo incluir?

Puedes utilizar cualquier conjunto de dimensiones y métricas de analytics. La API así lo permite. El único problema es que tienes que incluir exactamente el nombre que usa analytics para cada dimensión y métrica. Por suerte para nosotros, se nos brinda una lista de opciones muy clara en la documentación de la API de analytics:

• Dimensiones y Métricas de Google Analytics

Otros tutoriales para seguir aprendiendo:

• Crea tus informes y dashboards de analitica con excel