no ☞ 📈 Volcado de Google Analytics a tu Base de Datos _ â–‚ â–ƒ â–… â–† â–ˆ

Volcado de Google Analytics a tu Base de Datos

Publicado por  (Errioxa) el 23 de abril del 2014

Como dijimos el otro día, vamos a crear una serie de post sobre cómo utilizar determinadas APIs que Google nos ofrece, ya que en ocasiones la documentación es escasa en español, e incluso para algunas novedades es escasa hasta en inglés.

Hoy vamos a comenzar con la serie sobre cómo usar y configurar la API de Google Analytics la cual nos puede ser útil para muchas cosas


  • Volcar datos de Google Analytics a tu base de datos
Puede ser interesante para que tu site interactue con datos de Google Analytics, por ejemplo podemos

  • Decidir "al vuelo" que artículos mostrar en el típico "Post más leídos".

  • Saber si una determinada URL está teniendo excesivo rebote
    Y dependiendo de este indexar o no indexar esta url en el buscador (poniendo un meta noindex si el % de rebote es muy alto).
    Por ejemplo, aquellos sites que estén usando las búsquedas internas que hacen los usuarios en el site podemos saber cuáles de ellas son realmente útiles al tener un rebote bajo, y por lo tanto, son factibles de ser indexadas para posicionarlas. Y no indexar aquellas búsquedas internas que tienen un alto rebote y pueden ser califcadas como Thin Content.

  • Dar mayor visibilidad a productos o contenidos con un mayor % de objetvos cumplidos y así ofrecer a tus usuarios aquello que realmente está teniendo éxto esta semana, día o mes.

  • Mayor exactitud de los datos, evitaremos lo máximo posible la toma de muestra que Analytics nos crea al consultar los datos via web



  • Creación de tus propios dashboards mediante acceso a tus datos en la Base de Datos

    • Cruzar datos que desde el interface de Google Analytics no puedes

    • Si quieres "pintar" determinada gráfica que Google Analytics no permite

    • Automatizar dashboards específicos para cada cliente, o para cada departamento de tu empresa.



 

Como estos, tenemos muchos ejemplos casos prácticos para lo que es muy bueno el tener los datos de Google Analytics  en tu base de datos, porque por ejemplo a la hora de crear dashboards por semana de ingresos por canal, por tpo de producto, por precios, etc al hacerlo con consulta a la API de analytics puedes llegar al límite diario rápidamente, y sobretodo puede tardar mucho tiempo en obtener cada dato, porque recuerda que cada combinación de datos es una petición a Google Analytics y eso si son por cada día de un mes puede ser algo que tarde más de 10 minutos de reloj, mientras que teniendo los datos en tu base de datos tardará menos de 10 segundos.


Así que vamos al lio, lo primero que tenemos que hacer es darnos de alta en la consola de APIs de Google, donde le daremos al botón de CREATE PROJECT para crear un nuevo proyecto.

Una vez dado de alta el proyecto, veremos un menú a la izquierda, la segunda opción es "APIs & auth" hacemos click y veremos las distintas APIs que Google nos ofrece, ahora nos interesa la de Google Analytics asi que la activamos poniéndola en ON.

 



 

Ahora vamos a la segunda opción del menú izquierdo, donde pone "Credentials" y vemos que tenemos dos opciones, hoy nos interesa la de "OAuth" que nos permitirá dar credenciales a nuestro server para interactuar con Google Analytics sin tener que estar introduciendo usuario y contraseña cada vez que lo usemos, sino que tendremos un método que se podrá usar de forma automática sin tener que estar delante de nuestra pantalla.





Al darle a "CREATE NEW CLIENT ID" nos saldrán 3 opciones

 

  • Web applicaton
  • Service account
  • Installed application

 


 

Nosotros para este caso escogeremos la segunda opción "Service account" y veremos como nos descarga un fichero. Este archivo lo tendremos que usar en nuestro server y tiene las credenciales para tener acceso a los datos de Analytics así que tener cuidado con él de dónde lo ponéis, debería ser secreto y sólo vosotros tener acceso a él.

 

Una vez descargado y guardado (aquí lo llamaremos fichero-secreto-privatekey.p12), veremos que se nos presenta una serie de claves asociadas a nuestro Service Account, estas son

  • Client ID, que la usaremos luego en nuestro fichero de volcado
  • Email address, este usuario que se ha creado tendremos que darlo de alta en Google Analytics como si de un usuario normal se tratase.
  • Public key fingerprints,lo usaremos luego en nuestro script de volcado


 



 

Ya tenemos los datos necesarios para poder conectarnos a nuestra cuenta de Analytics por medio de un script.

Creación de la tabla en nuestra Base de Datos

Ahora vamos a crear una tabla en nuestra base de datos donde almacenaremos los datos de Google Analytics, esta tabla yo la he creado con los siguientes campos, tú podrías poner otras métricas o dimensiones si quisieras.

CREATE TABLE gAnalytics_Miweb (
  cod_gA int(11) unsigned NOT NULL AUTO_INCREMENT,
  kw_gA varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  landing varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  rebote varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  visitas int(10) unsigned NOT NULL DEFAULT '0',
  tiempo varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  valor varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  fecha date DEFAULT NULL,
  transacciones varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  ROI varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  medium varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (cod_gA),
  KEY index (kw_gA,landing,fecha)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Ya tenemos la tabla donde almacenaremos los datos, ahora sólo nos falta acceder e ir metiendo los datos en ella.

 

Script para volcar los datos

Para poder manejar facilmente las llamadas a la API en PHP podemos descargar esta librería y la ponemos en el directorio donde tendremos el script en mi caso /var/www

Y aquí os dejo mi script en php, que se encarga de volcar los datos de Google Analytics a la tabla antes creada en mi base de datos.

<?
header('content-type: text/html; charset: utf-8');

require_once '/var/www/googleapi/src/Google_Client.php';
require_once '/var/www/googleapi/src/contrib/Google_AnalyticsService.php';

session_start();
/*DATOS DE ACCESO A TU BASE DE DATOS */
    $servidor = "localhost";
    $usuario = "usuarioBBDD";
    $pass_BBDD = "PassBBDD";
    $BBDD = "nombreBBDD";
    $tabla_BBDD = "gAnalytics_miWeb";

/*DATOS DE ACCESO A TU BASE DE DATOS */

$id_Analytics ="xxxxxx"; //el ID de la cuenta de Google Analytics

$linkBBDD = mysql_connect($servidor,$usuario,$pass_BBDD);
mysql_select_db($BBDD);
mysql_set_charset('utf8',$linkBBDD);


$fecha_Analytics_inicio = "2014-01-01";
$contador_dias = 1;
$Fecha = "
2014-01-01";
$datetime1 = new DateTime('
2014-01-01');
$datetime2 = new DateTime('2014-04-23');

$mes = array("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre");
$numeroMes = date_format($datetime1,"m");
$numeroMes = (int)$numeroMes -1;
$tabla =
$tabla_BBDD;
while ($datetime1  < $datetime2){

    $usuario_Analytics = "xxxxxxx@developer.gserviceaccount.com";  //el usuario que nos ha dado la consola de APIs de Analytics
    $client = new Google_Client();
    $client->setApplicationName('enombre proyectp');

    $client->setAssertionCredentials(
        new Google_AssertionCredentials(
            $usuario_Analytics,
            array('https://www.googleapis.com/auth/analytics.readonly'),
            file_get_contents('/var/www/fichero-secreto-privatekey.p12')
        )
    );
    $client->setClientId('xxxxxx.apps.googleusercontent.com'); //el client ID de la consola de APIs
    $client->setAccessType('offline_access');
    $analytics = new Google_AnalyticsService($client);
    //*******************************************************FIN_TODO************************************

     
$results = $analytics->data_ga->get(
'ga:' . $id_Analytics,
$fecha_Analytics_inicio,
$fecha_Analytics_inicio,
'ga:sessions',
array(
    'dimensions' => 'ga:adMatchedQuery,ga:keyword,ga:landingPagePath,ga:medium',
    'metrics' => 'ga:sessions,ga:transactionRevenue,ga:bounceRate,ga:totalValue,ga:transactionRevenue,ga:transactions',
   'filters' => 'ga:medium==organic,ga:medium==cpc',
   'sort' => '-ga:sessions,ga:keyword',
    'max-results' => '50000'));
   

foreach($results["rows"] as $filas){
    $url_limpia = str_replace("/es/","/",explode("?",$filas[2]));
    if ($filas[3] == "cpc")
        $Kw_mejorada = $filas[0];
    else
        $Kw_mejorada = $filas[1];
    $sql = "insert into ".$tabla." (kw_gA,landing,rebote,visitas,tiempo,valor,fecha,transacciones,medium) VALUES ('".$Kw_mejorada."','".$url_limpia[0]."','".round($filas[6],2)."',".$filas[4].",'0','".round($filas[8],2).
"','".$fecha_Analytics_inicio."','".round($filas[9],2)."','".$filas[3]."')";
   
    mysql_query($sql);
   

}

$datetime1 = date_add($datetime1, date_interval_create_from_date_string('1 days'));
$fecha_Analytics_inicio = $datetime1->format("Y-m-d");
$contador_dias++;

}
echo "total dias:".$contador_dias;
?>


Con esto estamos recorriendo día a día los datos de usuarios llegados desde organic o cpc en el 2014 y metiéndolos en la base de datos.

Probarlo, comprobar que se están metiendo los datos correctamente y en breve seguiremos con oro post de cómo podemos usar estos datos en casos reales, de momento ya tenéis el acceso creado (que a veces es lo más complicado) y estamos volcando los datos de Google Analytics  a nuestra base de datos, para luego usar estos datos como mejor nos convenga.

 

PD: puede que haya algún error, aunque está cogido de mi script que funciona perfectamente, pero puede que me esté dejando algo y no me de cuenta, así que si os falla algo ponerlo en los comentarios y lo vemos, pero eso, debería funcionar. Aunque este script no es para copiar / pegar (que también valdría) sino más bien para que podáis ver el proceso y cada uno lo vaya haciendo a su manera y en su lenguaje de programación, pero el proceso de credenciales y acceso desde el servidor es este.


 




Posts anteriores en Google Analytics


Space Needle
Publicado el 24 de mayo del 2017, by Lino Uruñuela Muchas veces los SEOs usamos determinados protocolos y/o directivas para dar determinadas señales a los Bots de los buscadores y otros servicios. Así por ejemplo, podemos usar un meta para indicarles en qué fecha

Seguir leyendo
Space Needle
Publicado el 13 de abril del 2015 por Lino Uruñuela Hace tiempo ya comentamos que podemos saber cómo y cuándo acceden los robots de los buscadores a cada url de nuestro site por medio de los logs. Es muy importante tener visibilidad sobre qué y cómo crawlean

Seguir leyendo
Space Needle
Publicado el jueves 11 de septiembre por Lino Uruñuela Hoy voy a cambiar un poco de tema para variar, pero no me desviaré mucho. Si hay algo que me ha quedado claro en más de 10 años que llevo trabajando entorno a los negocios online , y que se viene cumpliendo co

Seguir leyendo
Space Needle
Publicado el 19 de agosto del, 2014 Hace ya "bastante" que salió a la luz Universal Analytics y ya nos ha dado tiempo a descubrir sus pros y sus contras con respecto a la anterior versión de Google Analytics. Y sí, también tiene contras, bastantes ademá

Seguir leyendo
Space Needle
Publicado por Lino Uruñuela (Errioxa) el 30 de junio del 2014 Hoy me he llevado una sorpresa cuando me he puesto a analizar el tráfico directo a raíz de que Rodrigo Hernandez me comentase su preocupación con el tráfico de un site , y es que normalmente suelo t

Seguir leyendo
  • 0000-00-00 00:00:00

Publicado el lunes  7 de octubre del 2013, by Lino Uruñuela Hoy me siento optimista, no sé si será porque ya estoy casi recuperado de la operación de rodilla a la que me sometí hace un mes, pero estoy optimista. Hace poco Google anunci&oacu

Seguir leyendo
Space Needle
Publicado el 4 de abril del 2013 by Lino Uruñuela Como todos sabemos desde hace más de un año el Not Provided cada vez aglutina más keywords en nuestras estadísticas y aunque aun podemos trabajar casi igual que hace dos años, no creo que podamos hacerlo d

Seguir leyendo
Space Needle
Publicado el 26 de enero del 2012 by Errioxa En el anterior post comenté cómo se podría sacar más provecho si cruzamos los datos que tenemos desde la distintas herramientas como Analytics, WMT, monotorización de posiciones. Así obtendríamos

Seguir leyendo
Space Needle
Publicado el 27 de diciembre del 2011 El otro día desde el blog para webmasters de Google nos anunciaban que habían creado un script para poder descargar los datos de impresiones, clicks, CTR, etc desde WMT. La verdad lo acogí con gran ilusión pero de momento no le

Seguir leyendo
Space Needle
Publicado el miércoles 17 de agosto del 2011 , by Lino Uruñuela, Recordemos que con el nuevo método de Google Analytics de medir las visitas ahora para ver que impacto ha tenido la actualización de Google Panda, no puedes comparar visitas post-Panda con visitas pr

Seguir leyendo
Space Needle
Publicado el 8 ed agosto del 2001 El otro día ya comentamos que Google Analytics había cambiado la forma en que trataba el tráfico que llegaba desde Google Images y ya no lo tomaba como referencia sino como tráfico orgánico. Para mi es importante saber el

Seguir leyendo
Space Needle
Publicado el 27 de julio del 2011 Cualquier día la OMS va a declarar Google como posible factor de riesgo de infarto entre las personas que viven de internet. Y es que ahora desde el sábado Google no muestra las búsquedas desde Google Images como referencia y parece que lo ha

Seguir leyendo

Últimos posts

Últimos comentarios


Lino Urunuela

probando https
Post: Consultor SEO en Donosti

Lino Uruñuela

ll
Post: ¿Cómo ejecuta, interpreta e indexa Google el contenido cargado mediante javascript?

Javier

Bueenas Lino! Gracias por tu respuesta! Efectivamente, cogiendo texto de una página hecha con javascript y poniéndolo en Google entre comi
Post: Meta Robots Noindex

Lino Uruñuela

Hola @Javier repondiendo a tu pregunta "¿significa eso que google no está viendo el contenido en texto y no lo posicionará?" con el mismo
Post: Meta Robots Noindex

Lino Uruñuela

@Alex R , lo primero graciias por a ti por participar :) Sobre tu prebunta ¿Dentro de un mismo artículo o entrada podemos hacer que no s
Post: Meta Robots Noindex

Javier

Buenas Lino Mecagoenlos! (vaya apellido tienes...) Tu artículo me ha dado mucho que pensar... acaban de hacerme una web en una agencia y
Post: Meta Robots Noindex

Alex R

Hola Lino! Te leo desde hace algún tiempo (genial el cambio de look) y casualmente he venido aquí con una duda sobre indexación. Lo
Post: Meta Robots Noindex

Lino Uruñuela

@javier Lorente Aupa Javi, en algún proyecto en el que he aplicado el noindex y que podía analizar los accesos del bot (tenina cierto p
Post: Meta Robots Noindex

Javier Lorente

Kaixo Lino! Habría que ver si baja la frecuencia de rastreo en otras urls que no fuesen la home, muy difícil de medir por cierto. Po
Post: Meta Robots Noindex

pSeo

va como un tiro con el onready!!!
Post: Cómo cargar css y js y no bloquear la carga de contenido

Contacta

Lánzate y pregunta!

Nos gustan los robots, pero no tanto
7 + 3