Como sabéis (si me leeis, claro) no me gusta quedarme en las modas y juzgar la utilidad de las nuevas tecnologías por sus aspectos más espectaculares, sino por lo que aportan en su día a día. El mundo del desarrollo web esta plagado de estas modas y valoraciones de tecnologías completas por sus detalles puntuales más vistosos. Pasó con ajax, con los frameworks javascript, con canvas y ahora con HTML5, CSS3 y las nuevas funciones de javascript.

En este caso quisiera tocar una de las mejoras más importantes que se nos vienen encima con CSS3 y que sin duda, una vez se estandarice del todo conseguirá que la mayor parte de los problemas de maquetación actuales desaparezcan: el modelo de caja flexible.

Antecedentes del modelo de caja

Css nunca ha sido un sistema cómodo. Recordemos que CSS es el sistema por el cual se crean las indicaciones para aportar los elementos de diseño a los elementos del HTML. Un HTML por si solo (salvo aberraciones varias) no tiene estilos: es texto, titulos y listados agrupados en una estructura más o menos semántica. Bien pues este sistema, como tantos otros, se quedó anclado en el pasado durante mucho tiempo -demasiado!-y ahora empeza a dar su siguiente paso. Actualmente la inmesa mayoría de páginas web están maquetadas con CSS 2.1 y cada vez más vamos viendo como distintas etiquetas de CSS 3 empiezan a ser usadas por distintos motivos.

Algunas nos son muy comunes: Bordes redondeados, fondos con degradados, sombras, fuentes, etc. Hemos leido mil artículos sobre como implementar estas propiedades de css3 en distintos navegadores con un éxito cada vez mayor. Como siempre el lastre nos lo trae Internet Explorer en sus versiones antiguas: de la 6 a la 8 o incluso la 9. Pero la verdad es que el tema tiene miga y hay que perder mucho tiempo aprendiendo a sacar partido a CSS3 en la mayoría de los navegadores.

Entre las distintas propiedades de CSS3 hay unas que realmente pueden cambiar nuestra forma de maquetar las webs haciéndolo todo mucho más cercano a como siempre debería haber sido: El modelo de caja flexible.

Para entender porque este detalle es tan importante debemos comprender el modelo de caja clásico de CSS. En este modelo cada elemento HTML puede ser una Caja -un bloque, un rectángulo en el diseño, etc. Como queramos llamarlo-. Pero en css2.1 las distintas formas por las que las cajas van colocándose en el diseño de nuestras páginas empujándose unas a otras para ir formando nuestras páginas es bastante peculiar y sobretodo poco cercano a como concebimos esos objetos en el espacio de forma natural. Efectivamente, el CSS2.1 actual las cajas se empujan unas a otras lo que hace que muchos elementos aparezcan donde los vemos como resultado de ser empujados por sus elementos anteriores. Eso unido a distintas peculariedades sobre como se realiza este proceso forma un pequeño caos que trae de cabeza a los maquetadores menos experimentados y que solo con experiencia se supera.

Pero todo eso va a desaparecer. Era demasiado evidente que esto suponía un problema y por lo tanto en CSS3 se ha creado un nuevo modelo de Caja que se crea con distintas propiedades y que permite ubicar los elementos indicando las relaciones entre distintos elementos y sus tamaños de forma que la forma de crear el CSS resulta mucho más cercana a como la percibimos nosotros.

¿lo vemos?

Pasos previos para poder usar el modelo de caja flexible de CSS3

Lo primero que tenemos que solucionar si queremos maquetar de esta forma es cómo conseguiremos que nuestros diseños se vean correctametne en un número decente de navegadores. Deberemos validar que lo que creemos se vea bien en un volumen de usuarios de nuestro site bastante elevado (99%, 98%...) por lo que sí o sí nos vamos a encontrar con Internet explorer.

Aquí os indico una metodología pero podréis encontrar muchas más por internet basadas en otros sistemas.

1) Evolucionar Internet Explorer al menos hasta IE9

Para esto, tenemos un código Javascript en Google Code que podemos usar y que realiza la mayor parte de las aproximaciones por si solo. Si lo cargamos en un tag html condicional conseguiremos este proceso creando más carga de página tan solo para los navegadores antiguos. Yo lo tengo claro: si eres un usuario que aun sigues con ese tipo de herramientas el tiempo de proceso no te preocupa mucho. Pero es mi opinión.

Bueno, pues incluimos este tag al principio del "head" de nuestro HTML y ya tendremos unos navegadores mucho más compatibles:

<!--[if lt IE 9]><script src="//ie7-js.googlecode.com/svn/version/2.1(beta4)/IE9.js"></script><![endif]-->

Ni que decir, que si además nos bajamos el JS en nuestro servidor para no llevarnos sorpresas y no provocar demasiada resolución de DNS al navegador, mejor que mejor.

Bien, ya hemos solucionado la mayor parte de nuestros problemas.

2) Llevar al menos el modelo de caja flexible al de IE10

Pero esto no resulta suficiente. El modelo de caja flexible no funciona ni siquiera en Internet Explorer 9. Si que podremos usar gran parte de las propiedades nuevas de CSS3 el modelo de caja no acabará de funcionar.

Solución? La misma, usaremos una librería Javascript con un tag condicional para IE menores que el 10 que arregle estos problemas por si solo.

En este caso os presento Flexie.js un script que realiza el trabajo perfectamente por si solo y que está probado en multitud de webs. En este caso no nos va a quedar otra que bajarnos el código y copiarlo en el servidor. Así que deberemos acudir a la web de Flexie.js y bajarnos el archivo. Después lo añadiremos al final de nuestro "head"

<!--[if lt IE 10]><script type="text/javascript" src="/js/flexie.js"></script><![endif]-->

Por si lo deseamos y pese a que no suele ser muy importante, flexie.js también hará funcionar el modelo de caja flexible en Opera 10 y superiores y corregirá algun detallito de otros navegadores. Así es decisión nuestra si lo indicamos con un tag condicional o se lo hacemos cargar a todos los usuarios.

3) Evitar el uso de los prefijos para CSS3 que usan algunos navegadores

Otro de los detalles de la implantación de CSS3 es que muchos navegadores se lanzaron a la aventura de implementarlo pero dado que eran sus primeros pasos en esta aplicación, no se atrevieron a usar los nombres depropiedades reales de CSS. Así que crearon propiedades nuevas añadiéndoles un prefijo a las reales. (por ejemplo firefox usa -moz-[nombre de propiedad]). Esto obliga a inundar nuestro CSS de gran cantidad de etiquetas de este tipo para poder tocar un poco de CSS3. A mi no me resulta nada adecuado este sistema: haces más grandes tus CSS, los haces mucho más difíciles de mantener y te facilita más equivocarte.

Por eso, y como recomendación final, os indico un tercer código javascript que se encarga de estos problemas. Se trata de cssFX y hace precisamente eso: transformar un CSS3 sin prefijos ni tonterias en la versión que necesita el navegador que está cargando la web. Por lo tanto nos permite maquetar a día de hoy con las propiedades reales de CSS.

Así que otra vez más, lo subimos a nuestro servidor y lo incluimos detrás de la carga del CSS en nuestro head del HTML y etiquetamos las llamadas a archivos CSS que contengan propiedades CSS con la clase "cssfx"

<link class="cssfx" rel="stylesheet" type="text/css" href="/css/style.css" media="screen" />
<script type="text/javascript" src="/js/cssfx.min.js"></script>

Y esto, ¿No es mucho javascript?

Sí, es mucho. Vivimos un momento en el que la carga de página es un factor relevante de nuestras webs y además de todo esto seguramente vamos a cargar un framework Javascript y algunos eventos. Muchos de estos archivos tan solo van a afectar a algunas versiones de Internet Explorer pero eso no es excusa: Sin duda es mucho.

Pero estos añadidos no solo nos van a solucionar tan solo el tema del modelo de caja flexible sino que nos van a facilitar en gran medida la maquetación en CSS3 permitiéndonos adelantarnos unos años en nuestros trabajos. Sin duda sigue siendo pronto para hacerlo todo en CSS3, pero algunos proyectos con mucha continuidad pueden tener sentido.

En definitiva tu decides... Para mi, por ejemplo, usar cssfx para ahorrarme gran cantidad de carácteres en el CSS merece la pena y usar CSS3 para ahorrarme muchas imágenes de fondo también. Pero sin duda depende del uso que vayas a hacer.

EL nuevo modelo de caja flexible de css3

Bien, nuestro sistema ya debería soportar el nuevo modelo de caja flexible. Así que vayamos a ver de que va todo esto:

Vayamos con la base: El nuevo modelo de caja se basa en un sistema de contenedores flexibles que afecta todos los elementos HTML con posicionamiento no absoluto que sean directamente hijos de uno de estos contenedores. En otras palabras, este modelo debe declararse en algunos elemetos de bloque del HTML; pero al declarar cualquier elemento con este modelo a quien realmente afectaremos será a todos sus elementos hijos (pero solo al primer nivel de estos).

Así pues hay dos tipos de elemento a tener en cuenta al usar este modelo de caja:

- El contenedor: que se comportará como si fuese un elemento con display:block.

- Y sus hijos inmediatos: que entrarán en este nuevo mundo del modelo de caja flexible.

Nota: Este modelo no se va a comportar bien con elementos "float", por lo que al usarlo debemos evitar usar estas propiedades en todos los elementos hijos. Por otro lado, gracias a este nuevo modelo, los float van a carecer de sentido en nuestras webs.

Nota 2: Esto solo va a afectar a los elementos hijos inmediatos sin posicionamiento absoluto. Esto significa que los elementos dentro de estos elementos hijos no se verán afectados y que si posicionamos algún elemento en "position:absolute" este elemento ni será afectado por el modelo de caja flexible ni se tendrá en cuenta para calcular como se comportan el resto de sus elementos hermanos. Esto que en un principio puede parecer rebuscado, en realidad nos da la oportunidad de jugar mucho con este sistema.

Como funciona

Para implementarlo usaremos al menos 3 propiedades -2 nuevas y la tipica de display- en el elemento contenedor que serán las que definan pero luego veremos que podemos añair algunas más.

Esto es lo más curioso de este sistema: Nosotros vamos a ir incluyendo propiedades en el elemento contenedor, pero estas no van a afectar a la apariencia, tamaño o posición del contenor, sino de sus elementos hijos inmediatos no posicionados en absoluto.

Así pues, lo que tenemos que hacer es comprender qué propiedades son estas y como van a afectar al resultado:

display:box;

Este es el principio de la declaración de modelo de caja. Recordemos: nuestro elementos se comportará como si fuese un display:block pero serán sus elementos hijos los afectados.

#miContenedor{ display:box; }

box-orient: (vertical, horizontal )

Esta propiedad indíca en que eje se van a repartir las cajas flexibles hijas de este contenedor. Si usamos "box-orient:horizontal", estas se distribuirán en el eje X si indicamos "box-orient:vertical" lo harán en el "Y".

Por lo general en la mayoría de los casos usaremos el horizontal para crear columnas o separaciones equitativas entre elementos. El vertical se usará para aquellos casos puntuales en los que necesitabamos hacer crecer las alturas de todos los elementos para que sean iguales y para distribuir elementos en un bloque vertical (como un sidebar, por ejemplo).

Por defecto (si no se indica) esta propiedad está marcada como "horizontal" pero yo os recomendaría empezar indicándola siempre o os podéis liar un poco al principio.

Nota: También podéis encontrar documentación que os hable de block-axis o inline-axis como valores para box-orient. Pero por lo general no son necesarios.

#miContenedor{ display:box; box-orient:horizontal;}

box-align: ( stretch, start, end, center)

Box-align es la propiedad complementaria de box-orient y se encarga de definir como ocuparán es espacio disponible en el eje contrario al definido a box-orient. Es decir, si yo indico un "box-orient:horizontal", al estar los elementos hijos distribuidos por el eje X, box-align definirá como se colocan estos en el eje Y. Al revés sucederá para "box-orient:vertical".

Así pues los distintos valores determinan distintas formas de ocupar este espacio:

• box-align:stretch
  Hace que los elementos hijos se estiren por si solos hasta ocupen todo el espacio disponible en el elemento contenedor.
  Así pues en un "box-orient:horizontal; box-align:stretch" los elementos hijos se distribuirán por el eje X ocupando de alto toda la altura del elemento contenedor.
• box-align:start
  Hace que los elementos no se estiren por si solos y se alineen al principio del espacio disponible (arriba o a la izquierda dependiendo de la orientación).
  Así pués en un "box-orient:horizontal; box-align:start" los elementos hijos se distribuirán por el eje X ocupando tan solo lo que les corresponde y situándose alineados a la izquierda del elemento contenedor.
• box-align:end
  Actúa exactamente al revés que "box-align:end"
  Así pués en un "box-orient:horizontal; box-align:end" los elementos hijos se distribuirán por el eje X ocupando tan solo lo que les corresponde y situándose alineados a la derecha del elemento contenedor.
• box-align:center
  Hace que los elementos no se estiren por si solos y se alineen en el centro del espacio disponible del eje contrario.
  Así pués en un "box-orient:horizontal; box-align:center" los elementos hijos se distribuirán por el eje X ocupando tan solo lo que les corresponde y situándose alineados en el centro del contenedor para el eje Y.

Por defecto esta propiedad (si no se indica) está asignada a "stretch" -que por otro lado es la que se usa el 90% de las veces- pero para empezar, como antes es recomendable indicarla igualmente para ser consciente del modelo de cajas que estamos creando.

#miContenedor{ display:box; box-orient:horizontal; box-align:stretch;}

Parémonos a pensar...

La explicación es algo liosa, pero una vez comprendida no es un concepto complejo. Al final lo que nos aportan estas propiedades es un sistema por el cual distribuir una lista de items en el espacio que aporta el bloque contenedor. Por lo tanto tenemos que dejar de ver como los elementos se empujan y pasar a crear contenedores con espacios fijos en nuestro diseño y a distribuir sus elementos internos.

Esto es lo que comentábamos al principio. Este modelo es mucho más cercano a como comprendemos los diseños. Diseñamos un bloque luego nos preocupamos de rellenar su interior lo cual nos da mucha más libertad en nuestros diseños y va a hacer más fácil cumplir alguno de esos diseños imposibles que a veces nos llegan.

Para terminar de entenderlo nada mejor que una herramienta práctica. Flexie.js, la misma herramienta que nos permitía aplicar este modelo de caja a varios navegadores nos da una herramienta con la que poder probar como van cambiando distintas cajas al cambiar sus propiedades.

Así que podemos entrar en Flexbox Playground y jugar con los distintos valores de "Box Orient" y "Box Align" para teminar de comprender estos conceptos. ¡Pruebalo!

En esta misma herramienta también encontraréis otras propiedades con las que jugar y que dan aún más posibilidades al sistema. Veamoslas...

Otras propiedades del contenedor en el modelo de caja flexible de css3

Siguiendo con propiedades que asignar al contenedor para afectar a sus cajas hijas, tenemos dos más que pueden resultarnos útiles para algún caso concreto. Estas propiedades son ya más particulares por lo que quizás es mejor empezar sin usarlas mucho y añadirlas solo para solucionar algún caso concreto.

box-direction: (normal, reverse)

Esta propiedad nos permite cambiar el orden en el que se colocan las cajas hijas en el eje indicado por la propiedad "box-orient". Básicamente podemos colcarlas según el orden de lectura del html ("normal") o al revés de como aparecen en el orden de lectura del html ("reverse").

Básicamente nos va permitir que el aspecto de nuestras webs dependa aun menos del HTML. Ahora que un elemento vaya antes que el otro no significa que no podamos darles la vuelta.

Por defecto (si no se indica), esta propiedad queda indicada como "normal". Sin embargo, el uso de esta propiedad no es muy necesario en la mayor parte de maquetas por lo que es preferible indicarla tan solo en los casos en los que se vaya realmente a jugar con estos elementos.

box-pack: (start, end, center, justify, distribute)

Por lo general, si usamos el modelo de caja flexible de css3 lo que buscaremos es repartir los elementos hijos de forma equitativa en el espacio disponible en el elemento contenedor y durante el eje marcado por "box-orient". No nos va a preocupar mucho más.

Sin embargo en ocasiones querremos indicar como se reparten estos elementos por el propio eje en el que se distribuyen. Esto podemos querer hacerlo sobretodo en aquellos casos en los que definamos el el ancho (en orientaciones horizontales) o el alto (en orientaciones verticales) de los elementos hijos.

Hasta ahora estábamos dejando que fuese el propio modelo de caja flexible el que decidiese esos valores, creando por ejemplo anchos equitativos para todas las columnas de un contenedor con "box-orient:horizontal". Pero es posible que algunos de estos elementos haya definido sus valores:

A - En el caso de definir el ancho o el alto de solo algunos de los elementos Hijos, no tendremos que preocuparnos. Los elemenos que no han sido definidos se adaptarán al espacio sobrante...

Ejemplo:

#miContenedor{ width:300px; display:box; box-orient:horizontal; box-align:stretch;}
#hijo_1{ width:100px }
#hijo_2{ /* sin definir width */ }

En este caso, al distribuirse los elementos #hijo_1 tendrá un ancho de 100px, pues así ha sido definido, dejando a #hijo_2 ocupando el resto del espacio.

Ejemplo 2:

#miContenedor{ width:200px; display:box; box-orient:horizontal; box-align:stretch;}
#hijo_1{ width:100px }
#hijo_2{ /* sin definir width */ }
#hijo_3{ /* sin definir width */ }

Al igual que antes #hijo_1 tendrá un ancho de 100px, pero en este caso, al quedar 100px por repartir entre los hijos 2 y 3 estos cogerán un ancho de 50px cada uno.

B - En el caso de de que todos los elementos tengan definido su ancho o alto (dependiendo de la orientación) hará falta indicar como distribuir los espaciados entre ellos. Para ello es para lo que se usa fundamentalmente box-pack indicando la alineación que deberán tener estos elementos:

• start
  Colocará todos los elementos al principio del Eje, sin dejar espacio entre ellos
  Por ejemplo: un "box-orient:horizontal; box-pack:start" con hijos con elementos con width definido, los colocará todos apretados y seguidos a la izquierda del elemento contenedor.
• end
  Colocará todos los elementos al final del Eje, sin dejar espacio entre ellos
  Por ejemplo: un "box-orient:horizontal; box-pack:end" con hijos con elementos con width definido, los colocará todos apretados y seguidos a la derecha del elemento contenedor.
• center
  Colocará todos los elementos en el centro del Eje, sin dejar espacio entre ellos
  Por ejemplo: un "box-orient:horizontal; box-pack:center" con hijos con elementos con width definido, los colocará todos apretados y seguidos en el centro horizontal del elemento contenedor.
• justify
  Colocará todos los elementos repartidos durante todo el eje, para ello creará espacios equitativos entre ellos, pero no a los lados del primer y último elemento.
• distribute
  Colocará todos los elementos repartidos durante todo el eje, para ello creará espacios equitativos entre ellos y los bordes del contenedor. Es decir, también dejará el mismo espacio a los lados del primer y último elemento hijos.

Nota: realmente box-pack puede usarse con elementos sin width definido y si miramos distintas documentaciones este es un elemento principal y más importante que box-align. Pero en la práctica resulta confuso usarlo de esta forma por lo que mi recomendación es la de solo usarlo para el caso concreto de widths definidos y manipular en ellos esta distribución de espacios. Ya habrá tiempo más adelante para liarse con este tema pero de momento aprendamos lo básico.

Propiedades asignables a los elementos hijos del modelo de caja flexible de CSS3

Como no podía ser de otra forma, también hay aspectos de los propios elementos que están siendo distribuidos y que deben marcarse en los mismos. Estos son los aspectos que pueden afectar puntualmente a cada uno de ellos por separado.

Existen un par de propiedades nuevas de este modelo pero antes de verlas veamos como afectan algunas propiedades que ya conocemos:

width y height

Como decíamos anteriormente estos elementos serán respetados en la distribución de las cajas. Por lo general, cualquier elemento que tenga un width o un height definido se mantiene con sus valores siendo el resto de elementos los que deberán adaptarse con el espacio sobrante.

position

"position:relative" no afecta al modelo de caja, pero cualquier elemento con "position:absolute" al pasar a no estar situado realmente en el orden lógico de la página se saca de la distribución. Así si yo tengo 4 elementos hijos de un ejemplo del tipo "display:box; box-orient:horizontal;" y uno de ellos está posicionado en absoluto, los otros tres cogeran cada uno un tercio del ancho del elemento padre.

float

Declarar un elemento como flotante rompe el modelo de caja y provoca comportamientos no deseados. Sencillamente no declares los elementos hijos nunca como float.

box-flex

Esta es una de las nuevas propiedades derivadas del modelo de caja flexible. Se trata de un punto intermedio entre declarar width y no hacerlo. Básicamente se trata de una medida relativa de las cajas donde podemos indicar cuantos espacios ocupa cada elemento del total. Así si en un contenedor del tipo "display:box; box-orient:horizontal;" si un elemento tiene un "box-flex:1" y otro un box-flex:2", el segundo tendrá un ancho del doble que el primero al realizar la distribución entre el espacio total del elemento contenedor.

Por defecto (si no está indicado) box-flex es igual a "0". Esto significa que se anula su flexibilidad. Es decir, todo lo mencionado hasta el momento sobre distribución de espacios es asumiendo que los elementos hijos tienen todos definidos su box-flex para que sean flexibles, de otra forma cogerán su ancho o alto mínimo según su contenido (como cualquier otro elemento sin ancho o alto) algo que no tiene sentido para casi ningún tipo de diseño.

La norma que debes aplicar al empezar (y hasta que controles lo suficiente este sistema) es que un elemento hijo de una caja flexible debe estar en una de estas dos situaciones:

A) Con box-flex definido con algún numero.

B) Con "box-flex:0" o no definido y width o height (dependiendo del eje de box-orient) definidos para suplir que no se esté definiendo el solo automáticamente.

También es recomendable definir en los elementos hijos siempre el box-flex por dos motivos:
1. Tener claro si su comportamiento es flexible o no de forma rápida al mirar el CSS.
2. Idenfiticar en el CSS rápidamente que elementos están dentro de una caja flexible.

Ejemplo:

#miContenedor{ width:300px; display:box; box-orient:horizontal; box-align:stretch;}
#miContenedor div{ box-flex:1}

De esta forma estamos haciendo que todos los "div" dentro del contenedor sean flexibles y por lo tanto todas las reglas comentadas hasta ahora funcionarán sin problemas.

Ejemplo 2:

#miContenedor{ width:300px; display:box; box-orient:horizontal; box-align:stretch;}
#hijo_1{ box-flex:5 }
#hijo_2{ box-flex:1 }

En este caso, al distribuirse los elementos #hijo_1 tendrá un ancho de 250px, y el #hijo_2 uno de 50px pues así #hijo_1 es 5 veces mayor que #hijo_2

Ejemplo 3:

#miContenedor{ width:300px; display:box; box-orient:horizontal; box-align:stretch;}
#hijo_1{ box-flex:0; width:100px; }
#hijo_2{ box-flex:3 }
#hijo_3{ box-flex:1 }

Al estar definido su width #hijo_1 tendrá un ancho de 100px, para el resto de elementos se recoge el espacio sobrante (200px) y se reparte según su box-flex quedando #hijo_2 con 150px de ancho e #hijo_3 con los 50 restantes.

box-ordinal-group

Por último esta propiedad nos va a permitir reordenar desde el CSS cada uno de nuestros elementos hijos del contenedor. Al igual que "box-direction" esta propiedad esta hecha para que el HTML no nos obligue a un orden de elementos determinado permitiéndonos definir nosotros mismos este orden. Aún así es una propiedad que es raro que useis mucho.

Por defecto (si no se indica) "box-ordinal-group" tiene el valor de 1, lo que significa que podemos pasar cualquier elemento al final de la lista de elementos simplemente marcándolo como "box-ordinal-group:2". En el caso de que varios elementos coincidan con el mismo número estos si que se ordenarán por el orden normal del HTML y según el valor de "box-direction".

Volvamos a pensar...

El modelo de caja flexible de CSS3 es realmente potente y va a solucionarnos todos los problemas que teníamos para poder crear nuestras webs tal cual están diseñadas. Permite un sin fin de aplicaciones dinámicas con elementos que se reajustan, centran y reordenan según van apareciendo. Esto aplicado a sistemas con un CMS por detrás o a aplicaciones con Javascript y/o ajax abre nuevas posibilidades al desarrollo al hacerlo simplemente mucho más sencillo y rápido: Todo esto ya podía hacerse, pero muchos hacks y horas de pruebas.

Ahora que conoceis todas las propiedades os invito a volver a visitar esa fabulosa herramienta de Flexie.js: Flexbox Playground para terminar de entender el uso de las distintas propiedades.

Nota: Ten en cuenta que algunas propiedades no producirán ningún efecto si otras están indicadas de forma especial:

- "Box Pack" no será percibido si los "Box Flex" no están todos a "0".
- "Box Direction" no será percibido si no están todos los elementos con "Box Ordinal Group" al mismo número.

Hacks interesantes para el modelo de caja flexible de CSS3

Por el momento solo he requerido de un hack para un comportamiento extraño en Firefox:

Resulta que cuando un elemento se define con "display:box" y "width:100%" firefox no asigna correctamente este 100% en la caja contenedora. Esto es realmente frustrante sobretodo cuando queremos que un elemento ocupe todo el ancho de página y sea a su vez una caja flexible.

Esto se soluciona con un pequeño código CSS que no es muy correcto, pero que hasta que Firefox no arregle este bug (ya les ha sido reportado) es necesario:

#tuElemento {width:100%; display:box; box-orient:vertical; box-align:center; }
/* Fix FF width */ html>/**/body #tuElemento, x:-moz-any-link, x:default  {width:9999px !important;}

Creando nuestro Layout

A partir de aquí el primer paso que hay que dar para poder crear un layout que no requiera usar elementos flotantes para todo. Pero cada uno aquí seguirá distintas técnicas.

Yo os dejo un pequeño ejemplo de layout creado con cajas flexibles para que tengáis alguna guía de todo lo hablado.

• Ejemplo Layout con Cajas Flexibles

Solo tienes que entrar y examinar su código HTML y CSS.

Esto es todo... ¿Seguro?

No. Existen más propiedades que puedes aplicar a tus cajas flexibles. Sobretodo propiedades que te permiten indicar el comportamiento de estas cuando empieza a ocupar más de una línea y la caja flexible empieza a crecer en alguno de sus Ejes.

Temas muy interesantes que tendrás que terminar dominando pero que me parecen demasiado complejos para lanzarnos a ellos en una primera aproximación.

Espero que esta explicación te haya servido y te animes a hacer tus pinitos con cajas flexibles. Así mismo si encontras algún error o te apetece compartir algo con los demás no dudes en dejar tu comentario.

Algunos enlaces para seguir aprendiendo

• Introducing the flexible box layout module
• CSS3.com: Referencia a todas las propiedades de CSS3
• Sporte de CSS3 por los navegadores
• Flexible box model o el modelo de caja flexible en CSS3 a fondo
• Resultados rápidos con flexible box model a fondo

Posts Relacionados:

1. Maquetación SEO en HTML5: Div, Article, Section o Aside?
   La maquetación en HTML5 poco a poco va imponiendose en el desarrollo web actual. La desgracia es que como tantas otras veces estamos haciendo las...

El problema que estamos teniendo realmente es que aunque parezca mentira -al menos, para el que entienda un poco de todo esto- actualmente empiezan a proliferar este tipo de webs:

• Webs en HTML5 hechas con dreamweaver y en tablas
• Webs en HTML5 con 200 "div"
• Webs en HTML5 en las que simplemente se han reemplazado los 200 divs por 200 "section"

En mi opinión aun no estamos en el momento de desarrollar en HTML5, pero queda tan poco para que lo sea que empieza a ser importante que nos tomemos todo esto en serio.

Las etiquetas Html5 solo tienen una ventaja respecto a XHTML: Son semánticas de por si... si no vamos a usar esta semántica, ¿quiere alguien decirme que coño estamos haciendo?

Html semántico

Llamamos html semántico a un documento que usa correctamente las etiquetas html para que la estructura resultante, quitandole la capa de diseño, tenga sentido por si sola. Así, si yo uso 200 divs o sections en mi página por mucho que con CSS dibuje cajas y añada fondos de mil colores sin este CSS no estoy diciendo que es cada parte de mi web. Es cuando me preocupo de que cada caja del HTML tenga sentido cuando mi HTML empieza a tener significado y por tanto los buscadores van a poder entender mejor cada uno de nuestros contenidos (leer más sobre html semántico)

Las nuevas Etiquetas Semánticas HTML5

El problema del html "antiguo" es que no existían etiquetas capaces de agrupar bloques enteros y que tuviesen significado por si mismas y por eso la gente terminó usando el div como etiqueta para todo. Si bien con los atributos "role" se mejora bastante este problema, estos nunca se han usado de forma muy extendida.

Entre otras cosas HTML5 nos aporta una serie de etiquetas nuevas que permiten mejorar la semántica de nuestra página:

• <header/>
• <footer/>
• <nav/>
• <hgroup/>
• <aside/>
• <section/>
• <article/>

Las 4 primeras apenas se prestan a confusión dado que a poco que nos informemos sobre ellas es difícil usarlas mal y de echo mucha gente dice maquetar en html5 solo por usar un par de ellas...

• <header/> : Nos permite identificar la cabecera de la página (que no forma parte del contenido único)
• <footer/> : Hace otro tanto con el footer (que tampoco forma parte del contenido único)
• <nav/> : Nos permite marcar conjunto de links como menús y por tanto ayudar a lo sbuscadores a detectar nuestra estructura web
• <hgroup/> : Permite agrupar titulares y nos permite jugar más con el peso de estos.

Donde empieza a encontrase un auténtico caos es en el uso del antiguo <div/> y sus nuevos hermanos con significado semántico: <section/> y <article/>

Como siempre es suficiente con mirar un poco la documentación y poner un poco de lógica en todo esto. Por ese motivo me gustaría repasar su uso, a ver si puedo poner mi granito de arena en que el estándar triunfe y no se pervierta... (por soñar).

Uso de <div/> en HTML5 orientado a SEO

La etiqueta div no ha muerto, sigue ahí y sigue significando lo que siempre ha significado: conjunto de elementos. En ningún momento se ha transformado a una etiqueta "tonta" que puedas usar libremente para ayudarte con el CSS ni ha sido sustituida totalmente por la etiqueta <section/>.

Por lo tanto nuestro deber es usarla como ayuda a la estructura de página: como base para crear el layout de de nuestras páginas, siempre y cuando no exista otra etiqueta de conjunto que represente mejor este papel... En otras palabras, div es la herramienta para unir elementos cuando no podemos asociar significado semántico a este conjunto.

Uso de <section/> en HTML5 orientado a SEO

El uso de seción es muy parecido al de div pero aportando la carga de significado mínima posible al contenido. Englobando distintos elmentos dentro de una etiqueta <section/> lo que estamos haciendo es declarar que todo su contenido está relacionado y forma parte de un mismo significado o elemento.

La diferencia es sutil pero muy importante: cuando creamos bloques cuyo contenido forma un todo usaremos <section/>, cuando estos bloques responden más a diseño o a necesidades de estructura usaremos <div/&gt.

Esta diferenciación también provoca que en todas estas maquetas que usan 200 divs para conseguir que con pocos conociemientos de CSS el diseño tome forma no pasan a estar bien hechas, pero si que podemos decir que en html5 estan "menos mal" (dado que al menos hemos marcado con sections los conjuntos que si que tienen significado sin liar tanto a los buscadores).

Uso de <article/> en HTML5 orientado a SEO

Por último la etiqueta <article/> es el siguiente paso a la hora de declarar que un cojunto de elmentos en nuestra página tiene significado. En este caso el paso dado es el dar a entender que ese conjunto tiene significado claro incluso si lo sacamos totalmente de la página. Es decir, al incluir algo dentro dentro de la etiqueta <article/> lo que estamos diciéndole a los buscadores es que dentro de este article podrá encontrar contenidos únicos con su propio significado.

Esto nos lleva casi siempre a que en páginas normales nuestro contenido será el único <article/> de la página y en páginas con listados de elementos tendremos un <article/> por cada elmento del listado (que si queremos identificar bien, tendrá un link con rel="bookmark" hacia el contenido real).

Para que la etiqueta article termine de entenderse comentar que idealmente esta al ser un contenido con significado propio podría contener en su interior etiquetas <header/>, <section/>, <aside/> y <footer/> ya que... al ser contenido propio, ¿porque no pueden tener sus mismas carácteristicas?

Uso de <aside/> en HTML5 orientado a SEO

Por ultimo nos queda la etiqueta aside que viene a ser una etiqueta <section/> venida a menos. Y es que aside sirve para indicar que ese bloque es solo un añadido a los bloques que tiene al lado. Son datos extra sin lo que podríamos pasar perfectamente pero que hemos decidido añadir en el documento.

Así pues, un <aside/> como hijo de la etiqueta <body/> nos dice que se trata de un contenido añadido por temas que no tienen nada que ver con el contenido de página (normalmetne esas columnas laterales llenas de tags y banners). Cuando lo incluimos dentro de un <article/> nos indica que esa información complementa el artículo pero no forma parte de él (listas de datos, testimoniales, banners relacionados, etc...).

Un ejemplo práctico de uso de etiquetas contenedoras

Imáginemos una página con:

- Su Cabecera con logo y con menú de navegación.
- Justo debajo El contenido que debes leer, con una serie de datos de referencia sobre este contenido.
- Y para terminar dos bloques en 2 columnas que el diseñador ha incluido, debajo que hablan de contenidos de forma libre y poco controlada.

Nuestra maqueta html5 partiría de lo siguiente:

<body>
<header>
  <p class="logo"><a href="...">...</a></p>
  <nav><ul>
    <li><a href ... </li>
    [etc...]
  </ul></nav>
</header>
<article>
  <header>
    <hgroup><h1>Mi titulo</h1><h3>Mi subtítulo</h3></hgroup>
  </header>
  <section>
    <p>Contenido contenido contenido</p>
  </section>
  <aside>
    <p>Datos extra para completar el contenido</p>
  </aside>
</article>
<div class="blocks">
  <section>
    <hgroup><h2>Bloque 1 que nos ha puesto el diseñador</h2></hgroup>
    <ul><li>...</li></ul>
  </section>
  <section>
    <hgroup><h2>Bloque 2 que nos ha puesto el diseñador</h2></hgroup>
    <ul><li>...</li></ul>
  </section>
</div>
<footer>
<ul><li><a href="... [links del footer]</li></ul>
</footer>

Posts Relacionados:

1. Guía de Maquetación SEO
   Después de la elección de palabras clave y la estructura web creo que la maquetación es el tercero de los puntos más importantes en el...
2. CSS3 “display:box”: Maquetación con el modelo de caja flexible
   Hacía mucho que no escribía un post sobre desarrollo puro. Quizás demasiado. Esta claro que a algunos no os va a a interesar en absoluto,...
3. html semantico
   ¿Qué es html semántico? ¿No es todo el html igual? Cuando hablamos de html semántico no nos referimos a ninguna nueva tecnología, ni siquiera aportamos...

Aún siendo lo largo que es el post -que reconozco que se las trae-, por comentarios que he ido recibiendo, parece que a la gente le sigue faltando algo que muchos consideramos esencial: conseguir esas hojas de datos a partir de Google Analytics.

En el post comenté varias herramientas que podemos usar par conseguirlo, pero la que parece más adecuada (los macros de AutomateAnalytics ) no acaban de adaptarse a la forma de trabajar que propiniamos. Sí, podemos descargar una muestra con la que recoger datos de analytics pero en un sitema un poco más guarro que al parecer no todo el mundo sabe limpiar. Por eso en esta ocasión lo que haremos será ver como usando los macros de Automate Analytics ir generando hojas separadas con exportaciones automatizadas de Google Analytics por las dimensiones, metricas, filtros y segmentos que deseemos.

Decargando el material

Lo suyo sería, ya que vamos a hacer un trabajo limpio desde 0, no partir de ningún Excel base y simplemente importar los Marcos de Automate Analytics. Pero sintiéndolo mucho, me parece complicaros mucho el trabajo empezar a hablaros del editor de Macros de Excel, cómo activarlo y demás. Asi que hagámoslo más sencillo...

Entramos en la web de Automate Analytics y descargamos su Excel ya montado, el GA data fetch, que es un exportador de datos de analytics, pero con una organización a lo bestia: todo en una misma página.

De este archivo no queremos su fantástica hoja de datos sino simplemente un Excel con las Macros ya cargadas para que sus 3 nuevas funciones ya estén activas. Lo bajamos y lo abrimos. Ale! Ya no necesitamos meternos a editar Macros, ya las tenemos incorporadas.

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.

Así que como lo que vamos a hacer es indicar datos de configuración lo que hacemos es crear una nueva hoja de excel: bbdd.analytics.

En celdas A2:B4 es donde vamso a crear nuestra conexión. Usaremos la columna A para poder poner una leyenda a cada dato y la B para los datos en Si.

Así pues, indicamos en la celda B2 nuestro email con el que nos logamos a analytics. En la B3 nuestro password y en la B4 la formula con la que generaremos nuestro tocken:

B4: =getGAauthenticationToken(B2;B3)

Si nuestros datos son correctos, veremos como al cabo de unos instantes se autogenera un código larguísimo en esa celda. Ya tenemos Tocken.

• DESCARGAR EXCEL 1

Nota: El excel viene sin email ni contraseña, debes indicar tu los de tu cuenta de analytics. Recuerda Actualizar con MAY+F9 las hojas.

Sacando las listas de Cuentas y de Segmentos de Analytics

Ahora usaremos las funciones para sacar datos básicos de nuestra cuenta. Estos no son estrictamente para configurar nuestra carga de datos, pero dado que analytics nos va a pedir ids concretos de nuestros perfiles y no nos permitirá indicar nuestra web, por ejemplo, por URL, nos será de mucha ayuda tenerlos a mano.

Para esto usaremos la siguiente función:

=getGAaccountData( Token ; datos a extraer ; Mostrar Cabeceras)

Aquí tendremos que saber crear funciones aplicadas a matrices y no a celdas. Como esto es algo que no se explicó en el anterior tutorial vamos a verlo ahora...

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()} )

Sacando las cuentas existentes en nuestro Analytics

Ahora lo que necesitamos hacer es coger un rango de 3 columnas completas y asignarle a todas ellas la fórmula de matriz getGAaccountData()

1. Seleccionamos en nuestra hoja ddbb.analytics las columnas E, F y G

2. Escribimos la fórmula que deseamos

=getGAaccountData($B$4;;1)

puesto que el token lo hemos colocando anteriormente en B4, el campo 2 lo dejamos vacio pues no queremos ningún dato especial y marcamos a 1 ver cabeceras para que nos las añada en la primera fila de datos.

3. En vez de usar ENTER, terminamos la declaración usando CTRL+MAYUSCULAS+ENTER.

Si lo hemos hecho bien, veremos como las columnas se autocompletan con la información de todas las cuentas a las que tenemos acceso con nuestro email en Analytics.

Sacando los segmentos existentes en nuestro Analytics

Para esto realizaremos exactamente la misma operación, pero en las columnas I, J y K. y la fórmula que crearemos si que indicará el segundo campo, para decir que ahora queremos los segmentos:

=getGAaccountData($B$4;"segments";1)

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á incluir 3 datos más a nuestra hoja de bbdd.analytics: el id de la cuenta que queremos usar, 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!

Por lo tanto seguiremos llenando datos en nuestra fila B. En mi caso indico lo siguiente:

B6: G23
B7: HOY()-30
B8: HOY()

Esto me selecciona el Id de mi blog (la cuenta numero 22 que me aparece listada en analytics) y me saca la fecha de hoy la de hace 30 días. Así conseguiré que cada vez que entre en mi Excel me de los datos de los últimos 30 días de mi blog.

• DESCARGAR EXCEL 2 (comprimido en .rar)

Nota: El excel viene sin email ni contraseña, debes indicar tu los de tu cuenta de analytics. Recuerda Actualizar con MAY+F9 las hojas.

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 que crean las Macros de Automate Analytics: getgadata(). Esta función usa 10 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, el creador ha querido darnos la oportunidad de usarlo todo. Así que tomémosnoslo como una ventaja, no como un inconveniente

Los datos a incluir son los siguientes:

=gegadata(
Token (referencia a la celda del token);
Profile ID (referencia a la celda de ID de cuenta de analytics);
Metric (metrica de Analytics sin "ga:" delante);
Start date (referencia a la celda de fecha);
End date (referencia a la celda de fecha);
Filters (tal cual los indica la API de analytics); 
Dimensions (dimensiones de Analytics sin "ga:" delante);
Advanced segments (según la API de analytics);
Sort (según la API de analytics);
Include headers  (siempre a 1 si queremso ver las cabeceras)
);

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 gegadata() con el siguiente código:

=getgadata(bbdd.analytics!$B$4;bbdd.analytics!$B$5;"visits,bounces";bbdd.analytics!$B$6;bbdd.analytics!$B$6;"";"ga:landingPagePath,keyword";"";1;1)

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.

• DESCARGAR EXCEL 3 (comprimido en .rar)

Nota: El excel viene sin email ni contraseña, debes indicar tu los de tu cuenta de analytics. Recuerda Actualizar con MAY+F9 las hojas.

No se vayan todavía, ¡aun hay más!

Para muchos será suficiente con la explicación que hemos explicado, pero hay gente que necesita las cosas un poco más másticadas y visuales. Sí, quizá una fórmula con 10 campos no es lo más intuitivo del mundo... cierto. Pero señores, es que estamos mirando de hacer analítica un mínimo avanzada, hay que acostumbrarse a estas cosas... Bueno... ok, una ayudita, ¡pero sólo una! ¿vale?

Vamos a crear una hoja previa hoja que nos permita trabajar todas estas exportaciones de una forma un poco más cómoda. No vamos a llegar a una interfaz estilo analytics porque no tiene sentido pero si a algo más sencillo de usar.

Empezaremos por crear una nueva hoja de bbdd como apoyo. Una hoja que contenga todas las metricas y dimensiones que podríamos querer usar. Así que creamos bbdd.autoAnalytics (por ponerle un nombre) y sacamos de la documentación de la API, todos los posibles valores de métricas y dimensiones y ponemos uno en cada columna. Al principio de la misma añadiremos tanto a métricas como a dimensiones el valor "(none)" por si el usuario no quiere seleccionar ninguna.

• DESCARGAR EXCEL 4 (comprimido en .rar)

Nota: El excel viene sin email ni contraseña, debes indicar tu los de tu cuenta de analytics. Recuerda Actualizar con MAY+F9 las hojas.

Ahora creamos una nueva hoja, datos.autoAnalytics y en ella vamos a crear en la columna A una serie de ayudas para generar nuestras consultas:

- De las celdas A2 a la A9 crearemos 8 campos de posibles métricas. Les asignaremos validación de tipo "lista" seleccionando todas nuestras métricas de la hoja bbdd.autoAnalytics (incluida la casilla "(none)").

- En la celda A10 crearemos el campo métrica con todas las posibles selecciones en las filas anteriores. Para ello comprobaremos que el campo no haya sido marcado a "(none)" antes de añadirlo. La fórmula final será la siguiente:

=CONCATENAR(SI(A2<>bbdd.autoAnalytics!A2;A2;"");SI(A3<>bbdd.autoAnalytics!A2;CONCATENAR(",";A3);"");SI(A4<>bbdd.autoAnalytics!A2;CONCATENAR(",";A4);"");SI(A5<>bbdd.autoAnalytics!A2;CONCATENAR(",";A5);"");SI(A6<>bbdd.autoAnalytics!A2;CONCATENAR(",";A6);"");SI(A7<>bbdd.autoAnalytics!A2;CONCATENAR(",";A7);"");SI(A8<>bbdd.autoAnalytics!A2;CONCATENAR(",";A8);"");SI(A9<>bbdd.autoAnalytics!A2;CONCATENAR(",";A9);""))

Que complicada no es, pero si larguita...

- Hacemos exactamente la misma operación con las celdas de de la 12 a la 19 pero en ese caso con la validación tipo "lista" sobre las dimensiones.

- Volvemos a generar una casilla que lo resuma todo en la A20 con la siguiente fórmula:

=CONCATENAR(SI(A12<>bbdd.autoAnalytics!B2;A12;"");SI(A13<>bbdd.autoAnalytics!B2;CONCATENAR(",";A13);"");SI(A14<>bbdd.autoAnalytics!B2;CONCATENAR(",";A14);"");SI(A15<>bbdd.autoAnalytics!B2;CONCATENAR(",";A15);"");SI(A16<>bbdd.autoAnalytics!B2;CONCATENAR(",";A16);"");SI(A17<>bbdd.autoAnalytics!B2;CONCATENAR(",";A17);"");SI(A18<>bbdd.autoAnalytics!B2;CONCATENAR(",";A18);"");SI(A19<>bbdd.autoAnalytics!B2;CONCATENAR(",";A19);""))

- Indicamos los posibles filtros (estos a mano, porque quien juegue con filtros ya sabe lo que se hace) en la celda A22.

- Creamos otra validación en modo lista para los segmentos en la celda A24. En este caso, como ya los habíamos sacado en la hoja bbdd.analytics los sacamos de ahi mismo, indicando la columna de nombres del segmento.

- Como analytics no entendera el nombre del segmento, en la casilla inmediatamente inferior (la A25) creamos un BUSCARV() que saque la definición del segmento a partir del nombre que ha seleccionado el usaurio:

=BUSCARV(A24;bbdd.analytics!J:K;2;0)

Y por último, ahora que ya tenemos todas las casillas de la fórmula de getgadata() en distintas celdas indicadas por el usuario creamos la fórmula para las columnas de la C a la R (16 columnas pues hemos permitido hasta 8 métricas y hasta 8 dimensiones). A esa matriz le asignamos la siguiente fórmula:

=getgadata(bbdd.analytics!$B$4;bbdd.analytics!$B$5;$A$10;bbdd.analytics!$B$6;bbdd.analytics!$B$6;$A$22;$A$20;$A$25;1;1)

Ya ya tenemos un exportador de consultas a analytics más o menos usable. Tan solo tendremos que ir duplicando esa hoja cuando queramos realizar nuevas consultas. ¿no está mal no?

• DESCARGAR EXCEL 5 (comprimido en .rar)

Nota: El excel viene sin email ni contraseña, debes indicar tu los de tu cuenta de analytics. Recuerda Actualizar con MAY+F9 las hojas.

Otros tutoriales para seguir aprendiendo:

• Crea tus informes y dashboards de analitica con excel

Posts Relacionados:

1. Crea tus informes y dashboards de Analítica con Excel
   Algo que me preguntan bastante ultimamente -no se por qué será - es cómo trabajar esos bonitos y complejos Dashboards en Excel de los que...
2. Google Analytics Tracking API: Como funciona el Javascript de Google Analytics
   La nueva API de Google Analytics, aparte de dar muchísima visibilidad sobre su herramienta aporta 2 bloques de posibilidades muy importantes a los desarrolladores. La...
3. Forzar con javascript las campañas capturadas por Google Analytics
   Debo reconocer que llevo más de un año buscando la manera de connseguir esto y creo que por fin he dado con una forma cómoda...

Y es que más o menos, todos creemos que sabemos manejar Excel, pero son muy pocos los que han tenido que masticarlo lo suficiente como para sacar sus verdaderas posibilidades a la que es, sin duda, la mejor herramienta de la Suite Office de Microsoft. En este artículo veremos un pequeño tutorial sobre como empezar a dar tus primeros pasos en la creación de paneles de control serios a la par que atractivos con Excel.

Repasaremos la utilidad real de Excel, cómo importar datos, cómo usar las fórmulas de resumen más comunes, como aplicar tablas dinámicas y veremos pequeños trucos de diseño. Al final crearemos 2 Dashboards sencillos pero que deberían bastaros para poder fabricar vuestros propios informes de negocio:

Bueno, empezamos ya con la explicación tutorial. Aunque antes de que sigas, avisarte de que este documento es bastante largo y tiene muchos pasos y detalles como para sacarle partido si solo dispones de unos minutos. Quizás prefieras guardar este link para poder abordar el tutorial cuando realmente dispongas de tiempo. ¡es un aviso! También es posible que quieras saltarte algunos capítulos si ya tienes su contenido dominado...

Tabla de Contenidos

1. Cuando usar Excel
2. Definiendo el Entorno
3. Las fuentes de datos
4. Hoja de BBDD interna
5. Hojas Resumen
6. Hojas Informe
7. Las Bases de Excel
8. Fórmulas más comunes
9. Tablas dinámicas
10. Gráficos Dinámicos y Gráficos a Medida
11. Segmentos en Tablas dinámicas
12. Diseñando una página de Dashboard
13. Dashboards Estáticos

Cuando usar Excel

Para este tipo de soluciones, la verdad es que, sabiendo programar yo soy más amigo de soluciones estilo Zoho Reports o desarrollo propio, pero existen ventajas muy claras para trabajar con Excel en lugar de con programación:

• La más obvia, no necesitas saber programar (sin duda ayuda, pero no es obligatorio)
• Tu trabajo queda en un único archivo que fabricas en cualquier y se mueve con este, pudiendo ser enviado y trasladado con todo su potencial.
• Otras personas, podrán trabajar con las fuentes de datos que se manejen ahí y realmente ir mejorando el Dashboard poco a poco con el tiempo.
• De la parte gráfica se encarga Excel y no pierdes el tiempo con maquetación de gráficos.

En conclusión, son archivos muy útiles para manejar en el interior de un negocio, con personas involucradas en el mismo.

Como contras tendríamos:

• Al dar acceso a tanta gente, estos archivos son inestables, si le sumamos el poco respeto que tiene la gente que no lo maneja bien por Excel, tenemos que cualquiera puede romperlos en su intento pormejorarlos
• Necesitas Excel para poder verlos, incluso algunos lectores de Éxcel no tienen todas sus funcionalidades.
• No se puede trabajar con grandisimas cantidades de datos, Excel es limitado en numero de filas y tu ordenador te limitará en la cantidad de proceso.
• Sin duda, y aunque siempre podemos recurrir a progamar dentro de un Excel, la capacidad de proceso de las formulas de Excel no llega ni por asomo a lo que puede hacerse con programación. Sencillamente hay cosas que con Excel "a pelo", no pueden hacerse
• Si te encuentras con gente desordenada, editar un Excel más o menos trabajado puede ser un autentico infierno (bueno, vale, esto también pasa con la programación)

Entendido todo esto, pongamos que si que vamos a realizar un panel de control mediante esta fantástica herramienta. Genial! Pues empecemos...

Nota:Lo que sigue es una forma muy personal de trabajar; si bien cada uno es libre de organizarse de la forma que quiera. Además de lo que es el propio "cómo se hace" voy a ir dando pautas que nos ayuden a ordenar el trabajo final que hacemos. Debemos entender que estas pautas concretas son lo de menos, lo que si que es importante es que cada uno, o cada empresa, escoja un patron por el que trabajar que le ayude a identificar rápidamente donde entrar a cambiar las cosas en un Excel complejo. De otra forma no aprovecharemos nunca un trabajo de hace varios meses...

Creación de Informes y Dashboards en Excel: Definiendo el entorno.

Norma: Usemos siempre 1 hoja de Excel para cada cosa.

Lo primero que tenemos que entender es cual es la mejor forma de organizar un Excel. Mucha gente cuando se lanza a esta aventura intenta, por los medios que sea, realizar todo su trabajo en la misma hoja de Excel y eso resulta a la larga muy incómodo de mantener. Excel nos brinda la posibilidad de crear varias hojas en un solo archivo (o incluso ligar datos entre varios archivos, pero eso no vamos a verlo aquí) lo que significa que podemos -o más bien debemos- separar nuestros contenidos en varias hojas para facilitar el trabajo:

Así que lo primero es diseñar para que vamos a usar las hojas de Excel, yo os propongo que en este ejercicio las usemos para 4 cosas distintas marcando en el nombre cada una de esas hojas cual es su función:

• 1.- Hojas con tablas de fuentes de datos. Marcadas con el nombre "datos.Nombre de la hoja".
• 2.- Hojas con referencia, equivalencias y traducciones de datos. Marcadas con el nombre "bbdd.Nombre de la hoja". En referencia a que actuan como base de datos interna del archivo
• 3.- Hojas con resumenes o procesos de datos. Marcadas con el nombre "resumen.Nombre del resumen".
• 4.- Hojas gráficas con informes creados a partir de las otras. Marcadas con el nombre "informe.Nombre del informe".

Así mismo, no está de más que nos acostumbremos a usar un orden en el tipo de hojas según la importancia que tengan: por ejemplo, que las primeras hojas sean siempre informes, luego vengan los resúmenes, luego los datos y por ultimo las bases de datos. De esta forma quien mire el éxcel irá profundizando en hojas solo a medida que lo necesite.

Otro tema es el nombre de las hojas en sí. No las dejemos como "hoja1", "hoja2" o tendremos que entrar en todas las hojas para saber lo que son. Usemos un nombre descriptivo. Además a ser posible evitemos nuestros espacios, así nuestras formulas serán mucho más legibles al no necesitar comillas al referenciar una hoja. Un truco sencillo es reemplazar espacios por guion bajo (_).

Es posible, que para algunos tipos de informes requieras otro tipo de hojas, o que prefieras diferenciar distintos tipos de datos, resumenes e informes. Lo mismo da, simplemente intenta ser ordenado y clasificar lo máximo posible tus nombres de hojas de Excel. Sino, a la larga te volverás loco para entender que es lo que hiciste hace un año.

Creación de Informes y Dashboards en Excel: Las fuentes de datos

Bien, empecemos a trabajar. Lo primero que necesitaremos para hacer un informe son los datos de los que surje este informe. Es obvio, pero no por ello menos importante. No podremos hacer ningún informe si nos faltan datos básicos de los que sacar conclusiones. Así pues necesitamos crear una Hoja de DATOS para cada fuente de datos que tenegamos.

Para ello, miramos lo que nos ofrece la herramienta y vemos que Excel nos brinda, en su sección de datos, 4 tipos de importaciones distintas:

Importando cualquier fuente de datos en nuestras hojas

Una vez hayamos escogido la fuente de datos -sea cual sea- Se nos solicitará a partir de que celda deseamos importarla. Lo suyo, como decíamos sería siempre crear una hoja completa para cada fuente de datos y escoger siempre la casilla A1 (por lo de ser ordenados y encontrar las cosas rápido).

Una vez importados no hemos terminado. Deberemos definirle la actualización de los datos, para que Excel los extraiga el solo cada cierto tiempo. Esto es una gran ventaja que no muchos usan, nos permite no solo trabajar con los datos en el momento sino reaprovechar ese mismo Excel en futuras ocasiones:

Clicamos con el botón derecho en la casilla A1 de esa hoja de datos -la casilla en la que hemos creado la importación- y en entramos en "Propiedades del Rango de Datos". Ahí declaramos como queremos que sea esta actualización. Si sabemos que los datos siempre tendrán el mismo formato, podemos establecer cada cuantos minutos queremos que se actualicen -cuidado ahí con no empezar a provocar procesos muy costosos a nuestro ordenador o al servidor del que sacamos los datos-. Si no nos fiamos de la fuente de datos y no queremos sorpresas nos interesará desmarcar la actualización en segundo plano e ir dandole cada vez que queramos a actualizar manualmente.

Importando "Desde Access"...

Nos permitirá sacar los datos de una BBDD en Access. Poco útil salvo que acostumbremos a trabajar realmente con esta herramienta -que yo personalmente no os aconsejaría nunca-. Si tenemos esa suerte -o mala suerte, según se mire- la importación de datos es sencilla y creándola podremos actualizar los datos directamente desde ese archivo, lo que siempre es una ventaja.

Los detalles de la importación son muy simples, creamos una consulta en Access y la importamos tal cual a Excel seleccionándola de un listado.

Importando "Desde Web"...

Nos permitirá importar tablas HTML desde una URL que le indiquemos. Sin duda se trata de un puente de conexión interesante cuando tenemos que ir sacando datos de cualquier entorno web e importarlo directamente en Excel.

Esta opción resulta util en 2 casos:

a. Cuando encontramos publicada y actualizada una tabla en alguna página de internet, podemos importarla directamente y ir mirando sus actualizaciones para actualizar nuestros datos directamente.

b. Cuando buscamos una forma rápida de importar nuestros datos internos (en nuestras propias BBDD) en el Excel, nos bastará con pedir que una URL concreta los muestre en formato de tabla HTML para importarlos y actualizarlos en nuestro Excel. Cuidado con esta forma puesto que si nuestros programadores no tienen cuidado y no ponen algo de seguridad (por Ip, por contraseñas, etc.) en la petición podríamos hacer publicos datos internos de la empresa.

Para realizar esta operación se nos abrirá una especie de navegador web simplificado en el que podremos navegar hasta encontrar nuestra tabla. Entonces la seleccionaremos y le daremos a importar.

Una vez importado recordemos entrar a configurar la actualización de datos.

Importando desde "Desde Texto"...

Esta seguramente la opción que más veces habremos usado. No tiene mucho misterio, elegimos un archivo concreto de nuestro ordenador en un formato de texto plano (normalmente .txt o .csv) y definimos como importarlo. Aquí todo el problema suele estar en que normalmente estos archivos se delimitan por comas (",") o punto y coma (";") y por defecto Excel intentará usar el tablulador como indicación de separación... Pero bueno, con cambiarlo ya está bien. También es una buena costumbre pedir a nuestros programadores que permitan exportar "CSV para Excel" separando por tabulador en lugar de por punto y coma (acaba siendo más cómodo).

En este caso debemos saber que también podremos definir la actualización automática de los datos. La mayor parte de la gente piensa que una vez han importado los datos Excel no guarda registro de como hizo esa importación, pero esto no es así.

Excel guardará la ruta del archivo que hemos importado y la forma que hemos usado para interpretarlo. Así que podemos pedirle que lo vuelva a importar cada cierto tiempo o sencillamente decirle que olvide de donde ha sacado esos datos. En esta importación automática cabe destacar que si no desmarcamos la casilla "Solicitar nombre de archivo al actualizar" Excel nos volverá a abrir el navegador de archivos cada vez (lo cual es bastante molesto si nos fiamos de esos datos).

Esta opción de actualización automática puede tener mucho sentido cuando cargamos archivos en carpetas en red que más gente puede ir actualizando (red de la intranet, Ftps, Dropbox, etc.).

Importando "De Otras Fuentes"...

Aquí ya entramos en conexiones directas con Bases de Datos en forma de Query concreta o con sistemas de datos que tengamos. Sin duda es la mejor forma de importar datos, pero también la más compleja y por eso no podemos pararnos a explicarla. Al final cada menú es suficientemente intuitivo como para que cualquiera que sepa más o menos lo que está haciendo pueda llegar a un buen resultado.

Algunas cosas muy interesantes que podemos llegar a hacer son:

• Conectarnos directamente con ODBC a una base de datos y lanzar consultas. Una lastima que no puedas conectarte directamente a un MySQL, pero bueno, siempre puedes añadir la conexión ODBC al MySQL que tengas instalado.
• Importar consultas concretas a archivos de listados (sacar solo cierta información de otros Excel, XML, BBDDD, etc.)

Como siempre, podremos configurar la actualización automática en todos estos casos y siempre es recomendable revisarla tras cualquier importación.

Revisando las "Conexiones Existentes"...

Este apartado nos va a permitir visualizar todas las conexiones que tenemos en nuestro documento. Al editarlas podemos ver en que hoja y casilla del excel están situadas y editar su actualización. A veces es la forma más cómoda de revisar que datos hay en un Excel.

Añadiendo datos... "¿a mano?"...

Por supuesto y aunque no sea una opción del Excel siembre habrá datos que incluyamos a mano... Aunque sea para poner nombres más humanos a algunos valores que incluyamos de una fuente externa.

Importando datos de fuentes más complejas

En ocasiones será preciso conectarse a distintas APIs para extraer los datos. Este es por ejemplo el caso de Google Analytics, del que si te dedicas al mundo web sin duda querras extraer datos concretos para tus informes y que por supuesto, vemos que Excel no maneja como posible fuente de importación de datos.

Por desgracia no existe una vía 100% simple e integrada para hacer estas importaciones. Cada API está programada según han deseado sus programadores y es imposible que todo sea fácil y sencillo. Pero por suerte para nosotros, hay muchísima gente que usa Excel, lo que significa que en muchos casos podremos bajar gratuitamente o comprar alguna solución ya implementada por otros. Solo hay que usar Google e investigar un poco...

para guiarnos un poco, veamos los distintos métodos por los que pueden realizarse estas importaciones más complejas:

1º) Creando una página html, que haga de puente en nuestro servidor.

Al igual que haciamos para poder importar datos internos mediante "Desde Web" podemos crear un sistema de solicitudes que nos haga de puente entre lo que Excel comprende y la API. Así, podemos desarrollar la integración de la API que deseemos con los datos que necesitemos en nuestro servidor y en formato que a Excel le gusta: las tablas html.

Si vamos a tirar por este camino, no olvidemos nunca añadir una capa de seguridad a lo que montemos.

2) Usando las Macros de Excel.

Las macros de Excel son una via por la que Exel te permite aplicar tu propia programacion en VBA en el mismo. Sin duda es una herramienta muy util si sabes programar en ese lenguaje y estas familiarizado con su uso en Exel, pues te permite hacer practicamente cualquier cosa que podrías hacer con Visual Basic pero aplicando los resultados a las celdas, columnas y hojas de Excel en lugar de al HTML.

Asi pues, una forma de importar datos de una API cualquiera es programando su integración con Macros. Podemos encontrar un ejemplo de este método aplicado a la API de Google Analytics en AutomateAnalytics. Ahi se nos permitirá acceder a este mundillo a cualquier nivel: desde decargar un informe ya totalmetne integrado con macros para partir de ahi y solo cambiar lo que te apetezca hasta descargarte cada uno de los códigos VBA para aplicarlos en tus hojas a tu manera.

Otros macros interesantes que podemos descargar nos permitirán acceder a APIs tambien muy interesantes como las de SEOmoz,

3) Instalando plugins (llamados add-ins)

También existen desarrollos completos que pueden comprarse/crearse/instalarse en nuestro ordenador y que dotan a Excel de nuevas funcionalidades. Existen Plugins de Excel para infinidad de cosas, que dotan transforman a Excel en herramientas más complejas o añaden nuevas vias de recuperación de datos.

Estos plugins resultan mucho más comodos y rápidos que las macros pero tienen una gran contra: todo el que use el Excel debe tenerlos instalados para sacarle partido, algo que no siempre podemos garantizar ni nos termina de convenir.

Dentro de estos plugins encontramos a Excellent Analytics un plugin que te genera una nueva pestaña en Excel con la que identificarte en Google Analytics y lanzar con su API capturas de datos en tus páginas.

Otro de los plugins interesantes, de cara a la recogida de datos es SQL Drill que nos va a permitir conectarnos a distintos tipos de bases de datos (incluidos Oracle y MySql) e importar consultas o tablas enteras (aunque debo comentar que su instalación a veces falla en W7 y excel 2010 y debe terminarse a mano).

Sin duda, los plugins son una gran opción tanto para necesidades puntuales como para iniciarse en la analítica web en Excel, ya que no requieren ningún conocimiento específico.

Creación de Informes y Dashboards en Excel: La hoja de Base de datos interna

Es bastante normal que los datos, tal y como se importan en la cuenta no nos acaben de valernos. Para solucionar esto mucha gente lo que hace es hacer reemplazos masivos en la hoja de datos que acaba de cargar, pero eso tiene un serio problema: no podremos actualizar nunca esos datos o tendremos que volver a repetir el trabajo de reemplazos.

Para solucionar esto lo que podemos hacer es crear una Hoja de Base de Datos donde incluiremos equivalencias concretas entre datos. Un ejemplo típico sería asociar una lista de datos que tenemos solo por ID y al que le creamos en una hoja de bbdd a sus nombres más comunes asociados a estos Ids. Para ello, usaríamos dos columnas de nuestra Hoja de base de datos donde iriamos especificando en cada fila ID y Nombre. De esta forma ya tendríamos esta asociación en nuestro Excel y luego podríamos usarla en nuestras formulas para autotraducir los datos cargados.

Creación de informes y Dashboards en Excel: Las Hojas Resumen

Estas hojas deberiamos tratarlas exactamente igual que las de datos solo que sabiendo que sus datos serán en realidad formulas que precalculen datos en base a otras hojas de datos que hemos ido importado. Por eso las llamamos resumen. No aportan datos nuevos, solo resumen los importantes en una sola hoja.

Estas tablas no siempre son necesarias, muchas veces podemos evitarlas y hacer los calculos directamente en los informes así que marcate dos únicos motivos para crearlas:

1) No te ves capaz de poder crear el informe sin este apollo intermedio que te procese los datos. Normalmente no significará que no pueda hacerse, pero uno debe ser consciente de sus propias limitaciones y no todo el mundo conoce todas las funciones de Excel ni sabe anidar varias en una sola celda. Además, para Dashboards complejos muchas veces es más rápido crear este resumen que ir teniendo en cuenta varias hojas para nuestras fórmulas.

2) Visualizarlas vaya a tener algún sentido para los usuarios. Esto ya es más comun. Nosotros en nuestro informe podemos mostrar un gráfico, por ejemplo de evolución de ingresos por tipo producto. Pero sabemos que alguien al observarlo puede preguntarse... ¿y este tipo de producto por qué tiene este resultado? Así que le ofrecemos una hoja resumen que le ayude a ver el desglose de cada venta. Si le obligasemos a visualizar cada hoja de datos seguramente tardaría mucho más en dar respuesta a su pregunta, así que debemos ayudarle.

Creación de infomres y Dashboards en Excel: Las hojas Informe

Los informes son al final lo que la gente viene a mirar a nuestro Excel por lo que deben ser claros, concretos y lo más automáticos posibles. Para listados de datos enormes ya tenemos las hojas de datos y de resúmenes, aqui lo que buscamos son datos que "nos digan cosas" y para generarlos tendremos que hacer muchas veces uso de complejas formulas y recursos gráficos y de usabilidad que ayuden a ver mejor todos los datos que incluimos y sobretodo a que destaquen aquellos en los que el usuario final debe fijarse.

Como decíamos pondremos en este tutorial un par de ejemplos sobre como crear este tipo de hojas pasando por todas las anteriores. Pero de momento, vayamos repasando como funciona Excel internamente o todo este ejercicio se resumirá en un corta y pega del que no entenderemos ni la mitad...

Creación de infomres y Dashboards en Excel: Las bases de Excel

Antes de entrar en los propios informes o Dashboards debemos conocer con que herramientas vamos a trabajar los datos. Para eso es esencial concer algunas funciones un poco más avanzadas que el "=SUMA()" o "=A1+B1". Así que perdamos unas cuantas líneas en ver las funciones que más van a usarse para crear estos informes, de hecho, es posible que solo con estas y las básicas ya no necesites nada más para crearlos.

Entendiendo la selección de celdas y de rangos de datos

Al definir celdas en Excel debemos conocer minimamente el lexico de las mismas. Tenemos un asistente que nos ayuda a crearlas, pero al visualizarlas tenemos que poder entender lo que dicen con solo leer el texto de la casilla correspondiente. Aquí os dejo algunos apuntes.

• Las filas se indican como conjunto de letra y Numero Seguidos. Por ejemplo A1 es siempre la primera casilla de la hoja actual.
• Si detallamos rangos concretos lo hacemos indicando las dos celdas que delimitan ese rango. Por ejemplo A1:C3 es el cuadro de 3x3 celdas del principio y A1:A3 las tres primeras celdas de la primera columna.
• Podemos marcar tambien rangos de Filas o columnas completas indicando solo ese numero o letra en el rango. Por ejemplo A:C son todas las celdas de las 3 primeras columnas de la hoja, 1:3 las 3 primeras filas y A:A todas las celdas de la columna A.
• También podemos definir datos de hojas distintas empezando la declaración por el nombre de la hoja seguido de "!" y la declaración de celdas. Por ejemplo, La celda A1 de la hoja datos.ingresos (la hoja de datos sobre nuestros ingresos) la marcariamos como datos.ingresos!A:1 y datos.gastos!A:A se refiere a todas las celdas de la columna A de la hoja de gastos.

Y con esto tenemos claras casi todas las selecciones. Ni que decir que normalmente será más cómo hacer las selecciones directamente con el ratón, pero si luego no entendemos lo que hay escrito lo vamos a tener mucho más dificil para detectar errores futuros por lo que es conveniente que practiques a realizar algunas selecciones manualmente.

Entendiendo las formulas

Crear una fórmula en Excel no tiene mucho misterio. Incluso tenemos un asistente (que lanzamos con el símbolo "fx") que nos facilitará saber que hacemos en cada momento. Pero, como antes, debemos entender lo que queda escrito al final en la celda o perderemos mucha agilidad detectando cambios y tendremos que reformular hojas enteras cuando se produzca un error. El Debug de Excel normalmente no pasa de indicar que hay un error con un valor y es nuestra labor detectar por qué.

Así pues una formula siempre tiene este aspecto:

=NOMBREFORMULA( Valor1 ; Valor2 ; Valor3 ; ...)

Donde:

• NOMBREFORMULA es la formula que estamos usando.
• Y cada valor es los valores que le incluimos que en cada formula, por su definición significan algo distinto. Estos valores pueden ser realmente que incluimos como número o texto entre comillas (") o una seleccion del celdas del tipo de las que hemos comentado antes.

De esta forma...

=SUMA(1000;A1:C1)

Será igual a la suma de 1000+ el valor de la celda A1 + el valor de la celda B1 + el valor de la celda C1.

Sencillo, ¿verdad? Aparte de las propias fórmulas el hecho de estar dentro de una formulación nos permite usar símbolos matemáticos para unir distintas partes de nuestra declaración. Así que la formula anterior podríamos haberla expresado también de esta forma:

=1000+A1+B1+C1

o como...

=1000+SUMA(A1:C1)

Incluso podemos ir uniendo distintas formulas de esta forma...

=(SUMA(A1:C1)*0,25)+(SUMA(B2:C2)*0,50)

Esta formula nos daría directamente un cuarto del valor la suma de las celdas A1,B1 y C1 sumado a la mitad del valor de las celdas B2 y C2.

Algunas consideraciones en las selecciones para las formulas

Cuando creamos formulas en Excel normalmente se nos permiten definir rangos de datos o matrices completas. Esto tiene cierto peligro con datos importados puesto que podemos caer en el error de marcar con el ratón una zona de los datos y que estos al volver a importarse aumenten de número de filas dejando nuevos datos sin procesarse.

Por lo tanto, siempre que sea posible haremos lo siguiente:

• Para matrices completas seleccionaremos Todas las columnas afectadas directamente. Por ejemplo una selección referente a una fuente de datos con 12 parametros sería: datos.ingresos!A:L
• Para marcar rangos, también siempre que sea posible lo haremos con columnas completas. Por ejemplo, para seleccionar el precio (4a culumna de la hoja de ingresos) de todos los productos, lanzariamos datos.ingresos!D:D incluyendo la cabecera en la selección en lugar de datos.ingresos:D2:D45 ya que de otra forma si alguna vez tenemos más de 45 ingresos no nos los contabilizaría.

Trabajando de esta forma nos evitamos muchos problemas pero también generamos alguno nuevo al tener en cuenta las cabeceras y las filas vacias como parte de los rangos seleccionados. Esto a poco que lo pensemos tampoco es un problema puesto que con rangos numéricos se interpretará cabecera y filas vacias como valor = 0 y en los textuales o de fechas donde normalmetne buscaremos coincidencias es dificil que encontremos justo esa coincidencia si trabajamos con cuidado. El único problema real vendrá al crear tablas dinámicas, que tendremos que esconder siempre el valor vacío de nuestras tablas.

Otra buena costumbre a seguir es no usar casi nunca valores concretos en las formulas. Siempre que podamos referenciemos el resultado al contenido de una celda. ¿Por qué? pues porque ante cualquier cambio criterio o evolución de nuestros informes se nos obliga a buscar en la formula para editarla cuando hubiese sido tan fácil como editar una celda. Esta celda podemos indicarla en una hoja de BBDD, como elemento editable de nuestro Dashboard, o como columna especifica en nuestas hojas de datos.

Por ejemplo, imaginemos que tenemos una hoja de productos (datos.productos) en la que hay que calcular el IVA de un producto. Podríamos hacer lo siguietne:

=datos.productos!A1*(18/100)

Pero cualquier día nos arriegamos a tener ingresos a otro porcentaje de IVA... con lo correcto sería crear en la celda contigua el porcentaje de IVA a aplicar y cambiar el calculo:

=datos.productos!A1*(datos.productos!B1/100)

Como norma: ni un solo dato que no sea una referencia a una celda. A la larga saldreis ganando.

El copia/corta y pega y las selecciones fijas

Por ultimo, otra cosa que tenemos que entender es que cuando en excel hacemos un corta y y pega (o un copia y pega) se entienden por defecto todas las declaraciones como en referencia a celda en la que estamos...

Así si yo en la celda C1 indico que su resultado es =A1+B1 al copiarla en la casilla C2 automaticamente la formula de esa casilla será =A2+B2 puesto que lo que se copia es una formula que hace referencia a las casillas a la izquierda de la actual y no las casillas fijas.

Esto es ideal cuando estamos haciendo formulaciones consecutivas de nuestros datos y nos ahorra mucho trabajo, pero es un poco engorroso cuando hacemos formulas que usan un valor fijo de una tabla que no debe moverse.

Para evitar esto podemos incluir el símbolo dolar ($) delante de cada elemento la selección y esto indicará al sistema que esa seleccion es fija y no relativa a la casilla en la que nos encontremos:

Asi si yo en la celda C1 indico que su resultado es =$A$1+B1 al copiarla en la casilla C2 la formual de esa casilla será =$A$1+B2 ya que $A$1 no se ha movido con la copia.

Creación de Informes y Dashboards en Excel: Las Fórmulas más comunes

Que empiece el tutorial! Ve abriendo tu Excel

Despues de tanta explicación de las bases de Excel y de buenas costumbres al crearlos, quizás ha llegado el momento de entrar en materia y empezar a trabajar sobre un ejemplo que nos deje las cosas más claras, ¿no?

A partir de ahora y a modo de ilustración práctica, a medida que vayamos hablando de nuevos conceptos iremos explicando como poco a poco va tomando forma un Excel de informes sobre ciertos datos Ficticios.

Partiremos de un archivo Excel de ejemplo con una única hoja: una hoja de datos de 42 facturas emitidas a clientes entre el 1 de enero y el 11 de febrero de 2010.

• DESCARGAR EXCEL DE INICIO

Fórmulas BUSCARV() y BUSCARH()

Estas dos formulas son iguales, salvo que BUSCARV se aplica en filas y BUSCARH se aplica en columnas. Lo más normal es usar BUSCARV, ya que los datos suelen presentarse por columnas y por lo tanto nos suele interesar examinar filas.

Esta formula lo que hace es buscar la primera aparición de un elemento en una columna para darte como resultado el elemnto de otra columna. Su formulación es la siguiente:

=BUSCARV( valor que buscamos; matriz deonde se busca; Columna que realmente queremos ; Ordenado)

Donde:

• El valor que buscamos es la coincidencia que tiene que haber para que la formula busque un dato.
• la matriz debe ser una selección de varias filas y columnas en la que la primera columna es SIEMPRE en la que buscamos la coincidencia.
• Columna que realmente queremos representa cuantas columnas debemos movernos en la matriz para sacar el dato que queremos.
• Ordenado es un valor que le indica a Excel si la fuente que se está mirando está ordenada de forma ascendente o no. Por defecto está indicado como "Verdadero" (1) y podemos pasarlo a "Falso" (0) indicandolo en la fórmula. Este valor, indicado a "Verdadero" (1) es útil solo cuando necesitamos que BUSCARV() siempre devuelva algo, en ese caso aun no encontrándolo coincidencia devolverá la más cercana posible al valor indicado. Si trabajos con valores no ordenados o no deseamos que siempre haya resultado debemos indicarlo siempre a "Falso" (0).

La verdad es que esta, a pesar de ser una de las fórmulas que más vamos a usar, es un poco tosca e incomoda... eso de que siempre se elija la columna 1 de la matriz y contar columnas hasta el valor que realmente queremos es bastante engorroso. Pero es lo que se nos da, y vamos a tener que funcionar con estas reglas...

Esta formula resulta ideal para cruzar datos entre dos fuentes distintas, montando sobre la marcha tablas de resumen o traducciones de datos con datos de distintas hojas.

Empecemos a poner en práctica el uso de BUSCARV() con el Excel de Ejemplo que mencionabamos antes. Para empezar si observamos nuestros datos de ejemplo veremos 2 cosas que no acaban de cuadrar. Por un lado se nos da un importe bruto y se nos indica el tipo de iva a aplicar pero no se nos calcula ni el valor del IVA ni el total Neto que hemos cobrado a los clientes por esa factura. Por otro lado tenemos la fecha, pero sabemos que la facturación en nuestra empresa la cuentan por meses así que deseamos saber el mes de cada factura, y lo queremos saber con su nombre no con un número que nadie entiende...

Seguramente nos pueden hacer falta estos esos datos cuando avancemos en la elaboración de nuestro dashboard así que es mejor que mejoremos nuestra fuente de datos con columnas extra que calculen esos datos. Esas columnas no se sobreescribirán al actualizar datos, pues no forman parte de la importación, además si en la configuración de la importación de datos marcasemos la casilla para respetar las formulas de la hoja, si se cargasen más datos de los actuales Excel añadiría formulas a los nuevos datos cargados...

Bien, para hacer este trabajo requeriremos de una hoja extra: nuestra primera hoja de Base de datos. Así que creamos una nueva hoja y la llamamos "bbdd.facturas" ya que es el apoyo de base de datos para "datos.facturas". Ahi generamos 4 columnas: 2 para trabajar el IVA y una para trabajar los meses del año (hay otras formas de hacer esta traducción pero para el ejemplo usaremos la hoja de bbdd.

Así pues nuestra nueva hoja contendrá estas equivalencias:

Tipos de iva (columnas A y B):
- normal: 18
- reducido: 8

Numero de Mes (columnas C y D)
- 1: Enero
- 2: Febrero
- 3: Marzo
- 4: Abril
- 5: Mayo
- 6: Junio
- 7: Julio
- 8: Agosto
- 9: Septiembre
- 10: Octubre
- 11: Noviembre
- 12: Diciembre

Ahora solo nos falta unir esta traducción a los datos que tenemos den datos.facturas.

Añadimos tres nuevas columnas (preferiblemente de otro color para saber que son datos calculados) a nuestros datos: iva, Total pagado y Mes. Ahí usamos las siguientes formulas.

Iva:

=D2*(BUSCARV(E2;bbdd.facturas!A:B;2)/100)

Donde multiplicamos el valor del importe, por la traducción del tipo de iva y dividimos entre 100.

Total Pagado:

=D2+G2

Sí, esta muy facilita...

Mes:

=BUSCARV(MES(B2);bbdd.facturas!D:E;2)

Sacamos con la función MES() el numero de més de la fecha y aplicamos la traducción con la Base de datos.

Ahora solo nos hace falta rellenar el resto de filas con estas fórmulas.

Puedes validar el trabajo realizado descargando el Excel tal cual deberías tenerlo ahora mismo:

• DESCARGAR EXCEL 1

Esto ha sido fácil, compliquemoslo un poco más. Vamos a crear nuestra primera tabla de resumen. Para ello hemos supuesto que teniamos una nueva fuente de datos: Google Analytics en la cual tenemos asociados a cada id de factura un par de datos de analítica web.

• DESCARGAR EXCEL 2

Ahora lo que haremos será crear una nueva hoja en el excel: resumen.facturas_analytics que que nos debe aportar los datos asociados de nuestras dos hojas de datos anteriores.

Empezaremos por crear las cabeceras de la nueva hoja:

id, Fecha, Mes, cliente, medio, keyword, importe, iva, Total, pagada

Y a partir de la Fila 2 empezaremos a formular cada celda para que recoja los datos que debe de ambas hojas de datos...

A2: =datos.facturas!A1
B2: =datos.facturas!B1
C2: =datos.facturas!I2
D2: =datos.facturas!C2
E2: =BUSCARV($A2;datos.analytics!$A:$C;2;0)
F2: =BUSCARV($A2;datos.analytics!$A:$C;3;0)
G2: =datos.facturas!D2
H2: =datos.facturas!G2
I2: =datos.facturas!H2
J2: =datos.facturas!F2

La mayor parte de las fórmulas han sido triviales, pero hay que destacar las celdas E2 y F2, donde hemos usado BUSCARV() para poder asociar ambas tablas.

En ambas lo que hemos hecho ha sido seleccionar la celda A2 como id a buscar, les hemos indicado el rango de columnas de la A a la C de la hoja datos.analytics y le hemos indicado de que columna sacar el valor. Notese que como los datos de la hoja datos.analytics no estaban ordenados ascendentemente por ID hemos tenido que marcar el cuarto parámetro (ordenado) de BUSCARV() a 0 ("Falso"). De otra forma los resultados habrían sido muy extraños...

Una vez tenemos esta fila lista, tan solo tenemos que rellenar el resto de filas hasta tener una hoja ordenadita con todos los datos asociados a la vez a los IDs

Estos dos ejemplos han sido bastante sencillos, pero tu mismo puedes ver el potencial de este tipo de hojas resumen una vez mas mezclando datos con distintos criterios. Al final, el potencial es cercano al de muchas sentencias SQL solo que mucho menos directo.

• DESCARGAR EXCEL 3

SUMAR.SI() y CONTAR.SI()

Ahroa vamos a tratar las funciones de .SI(), donde la más interesante va a ser SUMAR.SI(), una función muy útil para hacer nuestros cálculos globales en el Dashboard o en tablas de resumen. Esta función nos permite seleccionar un rango de datos (normalmente una columna de una hoja de datos o de resumen) y que solo sume los datos si en otro rango especificado el dato coincide con nuestors intereses.

La formula se aplica de la siguiente forma:

=SUMAR.SI( rango al que se aplica el criterio ; criterio para saber si sumar el dato ; rango que se suma )

De esta forma elegimos de nuestras tablas de datos, en relación a una busqueda que nos interesa que elementos sumamos y que elementos no. Los criterios pueden ser booleanos ">=1","<0",etc... o simplemente un valor al que tiene que ser igual el campo del rango al que se aplica el criterio.

Esta formula es ideal para declararla en columnas enteras de datos: Cojemos una fuente de datos cargada en el Excel y para cada fila de la misma si el dato de otra de las columnas es el que queremos,sumamos el dato al total.

Variación: SUMAR.SI.CONJUNTO()

Esta variacion sigue la misma lógica que SUMAR.SI() pero nos permite especificar más de un rango de criterios. A veces no queremos simplemente que un valor coincida con otro, sino que queremos observar varias columnas para decidir si sumamos el valor o no a nuestro conteo final.

Su formula es la siguiente:

=SUMAR.SI.CONJUNTO( Rango a sumar ; Rango sobre el que se aplica el criterio 1 ; criterio 1 ; Rango sobre el que se aplica el criterio 2 ; criterio 2; ... )

Pasemos a la práctica con nuestros Exceles de facturas y analytics. Recordemos que teníamos el Excel ya con 4 hojas de datos: resumen.facturas_analytics, datos.facturas, datos.analytics y bbdd.facturas. Bien, pues vamos a añadir una 4a hoja, los primeros datos de nuestro primer informe: informe.global

En este crearemos antes de nada en una celda un campo de ayuda en el que indicaremos el mes que estamos visualizando en este informe global. Crearemos el campo y lo rellenaremos con la palabra "Enero". Es más, como no queremos que nadie pueda escribir mal uno de los meses después de indicar el campo le aplicaremos "Validación de datos" (Menú >> Datos >> Validación de Datos), ahí le indicaremos que permita una "Lista" de datos "omitiendo Blancos" y con "lista desplegable" y le daremos como Origen de los mismos los nombres de los 12 meses que ya indicamos en la hoja bbdd.facturas. De esta forma, como los origenes son exactamente iguales será imposible equivocarse en la declaración del informe. Veremos como al lado de esta celda aparece un selector que nos permite indicar cualquier mes.

Ahora en base a este selector vamos a indicar las sumas de ingresos e IVA del mes que estamos observando. Así crearemos 3 celdas consecutivas, una para ingresos, otra para IVA normal y otra para IVA reducido...

Ingresos: =SUMAR.SI(datos.facturas!I:I;informe.global!$C$2;datos.facturas!D:D)

Donde Buscamos en la columna de los meses de la hoja de datos.facturas, el mes indicado actualmente en el informe global (en este caso yo lo indiqué en la celda C2) y cuando encontramos coincidencia sumamos la cantidad de la columna D de la hoja datos.facturas (la de importes).

Iva Normal: =SUMAR.SI.CONJUNTO(datos.facturas!G:G;datos.facturas!I:I;informe.global!$C$2;datos.facturas!E:E;"normal")

Aquí hacemos un ejercicio similar, pero aparte de comparar el Mes con el indicado en nuestro informe global, también queremos que la columna E (tipo de iva) valga "normal" o no la sumaremos.

Iva Reducido: =SUMAR.SI.CONJUNTO(datos.facturas!G:G;datos.facturas!I:I;informe.global!$C$2;datos.facturas!E:E;"reducido")

Exactamente la misma operación pero ahora buscando que el tipo de iva sea "reducido".

Por último añadimos 2 filas más para validar que hemos hecho bien el trabajo:

=SUMA(C4:C6)

- Por un lado crearemos una celda en la que sumaremos las otras 3, y que nos dará el total de ingresos del mes indicado.

=SUMAR.SI(datos.facturas!I:I;informe.global!$C$2;datos.facturas!H:H)

- Por otro sumaremos desde la hoja datos.facturas los importes de la columana de Total pagado. Ambos deberían ser iguales...

Ahora ya podemos jugar un poco con nuestro informe global y ver que cuando cambiamos de mes (de enero a febrero, porque recordemos que no hay datos en el resto de meses aun) los cálculos cambiarán...

• DESCARGAR EXCEL 4

Las tablas Dinamicas

Aprender a usar las Tablas de Dinámicas de Excel resulta esencial para hacer cualquier tipo de informe o Dashboard. Su potencial para cruzar datos es inmenso y nos permite, con solo seleccionar y arrastrar unos cuantos datos generar matrices con información muy interesante.

Lo primero que tenemos que hacer para generar tablas dinámicas es escoger nuestra fuente de datos. Si hemos hecho las cosas de forma ordenada la mayor parte de las veces hablaremos de seleccionar hojas enteras de datos o de resúmenes. Aquí lo mejor es acudir al asistente para crear las tablas dinámicas y de ahi pasar a la visualización de "lista de campos", una herramienta que esconde muchísimas posibilidades. Vamos paso a paso y de paso, sigamos con nuestro ejemplo...

Vamos a crear un nuevo informe, que nos de datos de calidad tanto de nuestros clientes como de los medios por los que traemos tráfico a nuestra web. En el caso de los clientes mediremos su calidad examinando cuanto han pagado de todo lo que han ido comprando, en el caso de los medios querremos ver cuantos ingresos nos aporta cada medio por separado. Además le añadiremos un control de ingresos por día. En total tres tablas dinámicas.

Por lo tanto creamos una nueva hoja en nuestro Excel: informe.calidad_medios_clientes. En esta hoja crearemos primero una tabla dinámica más sencilla donde veamos los ingresos de cada medio y que porcentaje representan del total y otra un poco más compleja donde veamos cada cliente cuantas facturas ha pagado vs cuantas no y los ingresos recividos vs los pendientes.

Como crear las tablas dinámicas

Para crear la tabla fácilmente seleccionamos la celda a partir de la cual surgirá esta. Pongamos que es la celda A1 de una nueva hoja que como decíamos vamos a llamar informe.calidad_medios_clientes. Vamos a Menú: "Insertar" >> "Tabla Dinamica" y veremos como nos abre una ventana donde nos solicitan datos ya se ha rellenado lugar donde se indica a partir de que celda va a crear la tabla. Por lo tanto solo nos queda rellenar el "Rango" de que sacará datos la tabla dinámica, Así que vamos a nuestro resumen recién creado (resumen.facturas_analytics) y seleccionamos todas las columnas del mismo (resumen.facturas_analytics!A:F). Ya tenemos los datos de la tabla dinámica definidos... ya solo falta configurarla.

Para configurar la tabla usaremos la vista especial de "Lista de Campos" que Excel nos brinda... Esta lista nos identificará las cabeceras del origen de datos (cuidado, porque las cabeceras en toda tabla dinámica son obligatorias) y nos permitirá arrastrarlas a 4 zonas distintas: Filtro, Etiquetas de Columna, Etiquetas de Fila Y Valores.

Como estamos justo empezando a trabajar con tablas dinámicas olvidemonos de los dos primeros. Pueden llegar a ser muy útiles para informes realmente potentes, pero ya llegaremos algún dia a usarlos, de momento contentemonos con esos 2 que ya dan bastante juego

El campo "Etiquetas de Fila": Cada dato incluido en "Etiquetas de Fila" es una segmentación en el informe que mostremos. -Si eres usuario de Analytics, sería algo similar a las dimensiones de analytics-. Podemos añadir cuantos campos deseemos en este cuadro, pero lo normal es añadir solo 1 o 2, ya que cuantos más pongamos más hacemos crecer nuestro informe que puede acabar siendo demasiado detallado.

El campo "Valores": A cada uno de esos segmentos que hemos creado añadiendo campos a "Etiquetas de Fila" le iremos añadiendo los datos que queramos ver sobre ellos en el cuadro de "Valores". Podemos añadir cuantos datos queramos e incluso repetir el mismo dato varias veces. Sin embargo lo que no debemos olvidar nunca es editarlos una vez añadidos para que se procesen y se muestren justo como necesitamos. Ese es el problema que tiene mucha gente con las tablas dinámicas, simplemente añaden datos al cuadro "Valores" pero no los editan después de añadirlos y por defecto solo les cuenta el numero de repeticiones de ese valor, lo cual es muy limitado...

Poniéndolo en práctica

Sigamos con nuestro ejemplo. Recordemos que acabamos de crear una tabla dinámica partiendo de los datos de resumen.facturas_analytics...

La vista de "Lista de Campos" nos ofrecerá para seleccionar las cabeceras que elegimos al crear la tabla resumen: id, Fecha, Mes, cliente, medio, keyword, importe, iva, Total, pagada.

Vamos a por el primer informe, que dijimos que queríamos que nos mostrase los ingresos que hemos obtenido mediante los distintos medios y el porcentaje que representan estos en el total. Así pues, lo que hacemos es añadir el campo "medio" al cuadro de "Etiquetas de Fila" y añadimos dos veces el campo "importe" al cuadro "Valores".

Ahora nos toca editar los dos campos añadidos a Valores: el primero lo pasaremos a "Suma" y ya nos valdrá su resultado (bueno, también lo pasaremos a tipo "Moneda", pero eso es más por apariencia que por el dato). El segundo campo de Importe también lo pasaremos a "Suma" pero además iremos a "Mostrar Valores como" y seleccionaremos "% del total general". De esta forma Excel sumará todos los valores que va a mostrar en la tabla e indicará el porcentaje directamente en esta posición. Cerramos la "Lista de Campos" y veremos como ya tenemos una tabla que nos indica para cada medio que ingresos hemos conseguido y el porcentaje que representa del total.

Bien! Primera tabla conseguida, ahora a por la segunda. El proceso va a ser muy similar: seleccionamos una celda de la hoja informe.calidad_medios_clientes y creamos una tabla dinámica que tome por datos todas las columnas de la hoja resumen.facturas_analytics. En este caso recordemos que buscamos saber para cada cliente cuantas facturas ha pagado vs las que no ha pagado y ver ese mismo dato en relación a sus ingresos.

Por lo tanto añadimos dos campos al cuadro de "Etiquetas de Fila": "cliente" y "pagada", lo que nos provocará que la tabla primero presente los clientes y dentro de cada cliente se desglosen sus dos posibles valores del campo pagada: "si" y "no". Ahora añadimos el campo id y dos veces el campo importe al cuadro de "Valores".

Ya solo nos queda configurar estos valores. El primero de ellos representará cuantas facturas distintas se han creado de la segmentación que estamos observando y por tanto lo dejaremos como resumir valores por "cuenta". Los otros dos, los trataremos igual que hemos hecho antes en la anterior tabla dinámica pudiendo ver así ingresos en € y en porcentaje. La diferencia la haremos en el de porcentaje en el que esta vez escogeremos "% del total de filas principales". Esta opción hará que en lugar de mostrarnos el porcentaje total que representan esos ingresos lo que nos muestre sea el % que representa de su grupo principal. Es decir, cuando vaya a darnos el dato de cuantos ingresos ya están pagados y cuantos no, nos calculará el porcentaje por cada cliente y no por el total de ingresos de todos los clientes a la vez.

Por último, nos queda nuestra tercera tabla dinámica; la de ingresos por días. Aquí lo suyo hubiese sido poder mostrar también estos ingresos por día también divididos por medios y poder crear una tabla más compleja, pero por desgracia la muestra de datos que tenemos solo incluye una factura por día y si hiciesemos eso quedarían ingresos muy salteados con la mayor parte de los valores a 0. Aún así mi deber es animaros a que con otras fuentes de datos probeis a añadir más segmentos a vuestras tablas.

Como siempre, seleccionamos una celda y asignamos como rango todas las columnas con datos de la hoja resumen.facturas_analytics. Vamos a la vista de Lista de campos y ahí nada tan fácil como añadir las fechas como "Etiquetas de Fila" y el importe como único "Valor". Al añadirlo recordemos cambiarlo de resumen tipo "cuenta" a "suma". Y ya tendemos la nueva tabla. Esta un poco más larga que las anteriores...

Si hemos hecho bien todo este trabajo nuestro informe responderá perfectamente a las preguntas que nos hacíamos: nos dirá que medios son los que más tráfico nos aportan y que clientes son los que tienen porcentajes de impago altos en comparación con sus porcentajes de pago. Sin duda, para estos datos ficticios el tráfico orgánico parece muy interesante y el clienteC debería preocuparnos ya que apenas ha pagado lo mismo que lo que debe.

• DESCARGAR EXCEL 5

Pero la verdad es que la información así presentada es un poco tosca...

Los gráficos dinámicos y los gráficos a medida

Vamos a añadir un poco de grafísmo a nuestro informe. Recordemos que los gráficos de Excel son muy capaces de resumir visualmente la información. Empecemos por dos gráficos sencillos. Seleccionamos nuestra tabla dinámica de fuentes y le añadimos un gráfico dinámico (Herramientas de tabla dinámica >> Opciones >> Gráfico Dinámico). Un gráfico dinámico es simplemente un gráfico que toma como fuente de datos directamente todo lo que haya en la tabla, lo cual ahora mismo nos va bastante bien. Así que le asignamos un gráfico de quesito y lo ponemos a nuestro gusto.

El siguiente gráfico va a ser igual de sencillo. Repetimos el proceso pero ahora con la tabla de ingresos por día que creamos anteriormente. Como son muchos datos y ordenados cronológicamnete en este caso escogemos un gráfico de líneas que nos muestre las tendencias de los ingresos.

Bien, hasta ahora ha sido muy fácil, pero cuando nos encontramos con el gráfico que representa el estado de págo de los clientes vemos que los gráficos no nos lo ponen fácil para resumir la información que deseamos. Esto es porque hemos ordenado la información de una forma distinta a como la requieren los gráficos de Exel... podríamos crear otra tabla dinámica paralela pero eso puede terminar haciendo que creemos cientos de tablas para un solo documento. Así que decidimos jugar con los gráficos creados a medida.

Los gráficos creados a medida se realizan insertando un gráfico pero sin indicarle los valores reales. Por ejemplo, situamos el cursor en una celda sin datos y vamos a Menu >> Insertar >> Gráficos de Barra y seleccionamos el tercero. Excel nos creará un cuadro de gráfico pero totalmente en blanco. Clickamos con el botón derecho y escogemos "Seleccionar Datos" para asignárselos nosotros manualmente. Ahí se nos abre una pantalla donde escoger Series y Categorías. Debemos entender que las series son conjuntos de datos que representará el gráfico y las categorías los nombres de esos datos. Así que le damos a Agregar y escogemos crear 2 series: una con los ingresos ya pagados de cada cliente (seleccionados uno a uno al agregar la serie) y otra con los ingresos no pagados. Como categorías seleccionaremos también uno a uno los 4 nombres de cliente de la tabla. El gráfico final serán por tanto 4 barras (una por cliente) que mostrarán que parte del total ha sido ya pagada y que parte no.

Estos gráficos no nos aportarán ninguna funcionalidad o dato nuevo, pero sin duda hacen más evidentes las conclusiones y nos ayudarán a saber en que datos fijarnos.

Añadiendo Segmentación a las tablas dinámicas

Por último, en nuestro informe, vamos a añadir un control de la segmentación. Estos controles van a permitir a quien mire nuestro cuadro poder ver solo una franja de datos y no todos a la vez. En nuestro caso queremos hacer un control para permitir a los usuarios ver todos los datos introducidos o fijarse tan solo en un mes sobre el que tengan dudas.

Añadir la segmentación es muy sencillo: Seleccionamos una de nuestras tablas dinámicas y clickamos en Menú >> Insertar >> "Segm". Se nos habrá una ventana que nos preguntará por que campo realizar la segmentación y seleccionamos "Mes". Ahora se nos añadirá al Excel un cuadro nuevo donde poder seleccionar los meses y veremos que al seleccionarlos los datos de la tabla dinámica y del gráfico asociado cambian.

Pero tenemos un problema, nosotros si cambiamos de mes querriamos que todos los datos que hay en el informe cambiasen a ese mes (de otra forma, que tedioso sería todo). Por suerte Excel nos permite aplicar una segmentación a más de una tabla dinámica al mismo tiempo. Para ello tenemos que asegurarnos de que todas las tablas dinámicas que compartan segmentación surjan de exactamente la misma selección de datos para crearla. En nuestro caso esto nos va genial ya que en todas nuestras tablas hemos usado la misma selección de datos: todas las columnas de la hoja resumen.facturas_analytics, así que podemos hacerlo.

Para hacer la asociación clickamos con el botón derecho en el nuevo cuadro de segmentación y seleccionamos "conexiones de tabla dinámica". Ahora se nos abrirá una ventana que nos permite elegir con que tablas está asociada esa segmentación. Si no te salen todas las tablas que deseas, seguramente es porque no seleccionaste los mismos datos para las distntas tablas, lo que significa que sería mejor que las repasases.

Bien, ya tenemos toda la información y funcionalidades de nuestro primer dashboard:

• DESCARGAR EXCEL 6

El problema, es que no resulta nada atractivo ni usable... Tenemos que ir haciendo Scroll y la información no acaba de destacar...

Diseñando la página del Dashboard

Llegamos a la parte artística. La que tiene que conseguir que nuestros datos no solo den respuestas, sino que luzcan por si solos y guíen a los usuarios hacia las partes más importantes del mismo.

Para conseguirlo vamos a hacer uso de 4 conceptos distintos:

• 1.- El diseño sencillo de las celdas: dandoles tamaños de fuente distintos, colores y fondos. Tenemos que ser coherentes al hacer este trabajo y aprender a hacer cosas "bonitas" con las herramientas de las que disponemos.
• 2.- El diseño de gráficos: Los gráficos que nos da Excel son muy manipulables. Por lo general el programa nos los configura para ser mostrados en solitario. Por ese motivo nos pone bordes, fondo, títulos, leyenda, etc. Pero mucha información dentro de nuestro dashboard va a sobrar. Por lo general eliminaremos el fondo blanco de los gráficos, su titular y sus bordes, ya que no nos aportarán nada cuando mezclemos ese tráfico con el resto de nuestro diseño. Luego podemos querir ir más allá y borrar más elementos o cambiarlos de formato o color para cuadrarlos con nuestros intereses.
• 3.- Las herramientas de formato condicional de celdas: Situadas en Menu >> Inicio >> Formato Condicional, estas herramientas nos van a permitir destacar unos datos sobre otros. Con ellas podemos cambiar fondos, colores o crear minigráficos o iconos dependiendo de los valores que tengan las celdas. Así que en el diseño de nuestro dashboard es obligado pensar que cosas queremos que puedan llamar la atención.
• 4.- La posiblidad de ocultar filas y/o columnas: Excel nos permite seleccionar columnas o filas completas y ocultarlas. Esto a nosotros nos permite no eliminar datos de nuestros informes pero si hacerlos invisibles a los usuarios. Esto resulta ideal cuando algunas partes de la tabla dinámica no aportan nada o cuando queremos escoender tablas porque ya están representadas por su gráfico asociado

Así pues, y con estas 4 posibilidades vamos a darle formato a nuestro Dashboard.

- Movemos los elementos para dar forma al dashboard.
- Destacamos la segmentación al principio de todo.
- Añadimos titulos con fuentes mucho más grandes.
- Escondemos las cabeceras de las tablas dinámicas y las reemplazamos por textos propios.
- Usamos los fondos de celda para marcar separaciones en la información.

Y ya empieza a coger algo de forma.

Ahora otras cosas que se nos han ocurrido...

Para los ingresos por fuente:

- Quitamos todos los datos del quesito de ingresos por fuente de tráfico. Incluso la leyenda y cambiamos el color de los distintos datos en la tabla dinámica para que actuen al mismo tiempo como dato de la tabla y como leyenda.
- Añadimos a los informes el formato condicional de barras de colores y asi destacamos todavía más los medios que más ingresan sobre los que menos.

Para los Ingresos por Día:

- Como solo nos interesa ver la gráfica para poder detectar dias extraños, nos llevamos a otras filas la tabla y las ocultamos para que no moleste.
- El propio gráfico lo limpiamos de datos que no nos aporten... como son las fechas concretas que ocupan mucho espacio para lo poco importante que resulta el gráfico.

Para el estado de pago de los clientes:

- Cambiamos los colores del gráfico a algo que nos resulte más evidente: verde para pagadas, rojo para no pagadas.
- Añadimos el formato condicional de barras a los porcentajes totales de cada cliente para que nos llame la atención más aquellos clientes que más ingresos nos producen.
- Añadimos a cada una de las celdas de porcentaje de ingresos no pagados de cada cliente un formato condicional que nos añada una banderita roja si ese cliente nos debe un más de un 40% del total facturado (¡es la hora de meterle caña!)
- Por último creamos 2 gráficos, hechos a medida nuevos para cada cliente: un quesito que nos compare facturas pagadas con no pagadas y otro que nos compare importes pagados con no pagados. Como de estos quesos solo nos interesa el propio gráfico les eliminamos absolutamente todo lo que no es el gráfico y le damos colores tanto a las celdas de datos como a los quesos para identificar cada uno de esos datos que significa.

Despùés de tanto detallito acabamos con un dashboard bastante elegante en el que ir viendo cada mes lo que está sucendiendo...

• DESCARGAR EXCEL 7

Bien! Pues ya sabemos crear Dashboards de tablas dinámicas... pero por desgracia aun no hemos terminado. Nos falta crear otro dashboard con datos más concretos.

Dashboards Estáticos

Bien, Ya sabemos crear dashboards en base a tablas dinámicas pero esto no siempre es suficiente. De echo, si os fijáis en los dashboards que van usandose en distintas empresas nos encontramos con que no mucha gente acaba usando tablas dinámicas para sus dashboards. ¿Y esto por qué? Con lo rápido que es una vez lo dominas! Pues sencillamente porque las tablas dinámicas no son capaces de recibir datos de otros elementos de Excel y eso nos limita mucho las posibilidades...

Intento explicar un poco mejor el problema que tenemos con las tablas dinámicas: son potentes y versátiles pero tan solo nos dejan indicarles una selección de datos y el resto lo manejan ellas solas, no podemos hacer que sus distintos criterios se vean modificados por los valores de otras celdas y eso hace que por ejemplo no podamos hacer, como hicimos antes, que el valor de una celda afecte a los valores de las tablas dinámicas... Aparte de eso, su tamaño está descontrolado, si añadimos elemtos crecen y si se los quitamos encogen. En resumen, cuando queremos un cuadro muy cerrado no nos terminan de ayudar...

Por lo tanto tenemos que ver vías para poder crear dashboards CELDA A CELDA. Si, suena duro, pero es que la mayor parte de los Dashboards que veis por ahi son precisamente eso, un diseño montado en Excel en el que cada celda es una fórmula más o menos compleja... La inmensa mayoría de las veces se trata de empezar por definir algunos detalles a mano o con una tabla dinámica como origen de nombres de etiquetas y a partir de ahí usar sobretodo SUMAR.SI(), CONTAR.Si() y PROMEDIO.SI() -que ya hemos visto- para poder completar los valores.

Asi que vamos a crear un Dashboard estático de la forma que acabamos de comentar. Partimos de nuestra hoja de informe.global, en la que ya indicamos el Mes como filtro del informe y vamos a ir sacando nuestro dashboard desde ahi.

Los datos principales

Lo primero que montamos es de forma gráficamente destacada los valores principales de nuestro negocio. Esto lo hacemos con CONTAR.SI() para contar facturas del mes, SUMAR.SI() para conocer importes antes y despúes del IVA, SUMAR.SI.CONJUNTO() para calcular los ivas por separado y formulas simples para calcular el total y el precio medio de la factura.

Además añadiremos dos pequeñas tablas dinámicas que nos ayuden a tomar nombres concretos de los datos que vamos a mostrar. En este caso queremos saber los mejores 4 medios y las mejores 4 keywords que existen según nuestros datos de resumen.facturas_analytics.

Así que creamos dos tablas dinámicas muy simples, una que muestre el dato de medio y conteo de facturas y otra que muestre el dato de keyword y conteo de facturas.

• DESCARGAR EXCEL 8

El desarrollo de los datos

Ahora lo que vamos a hacer es ir creando el desglose del dato Ingresos con 3 desarrollos consecutivos: un cuadro de distribución de esos ingresos por fuente en el que se diferencien facturas pagadas de no pagadas, un par de gráficos que nos ayuden a resumir esa misma información y otro pequeño cuadro que nos permita ver, para esos mismos ingresos que keywords son las responsables de las fuentes "organic" y "other".

1) Tabla de Ingresos por fuente

El primer cuadro podríamos hacerlo con una tabla dinámica, pero en este caso preferimos usar las funciones de SUMAR.SI y SUMAR.SI.CONJUNTO() para completar las celdas.

- Creamos la tabla sacando los nombres de las celdas de la tabla dinámica que ya tenemos en el dashboard. Esto lo hacemos así por si algún dia una fuente cambiase de nombre. Así que estas serán referencias muy simples: =B17, =B18, =B19 e =B20.

- Ahora creamos la siguiente columna: la parte pagada de esa fuente en concreto. Usamos SUMAR.SI.CONJUNTO() pues lo que queremos es la suma de los importes de la hoja resumen.facturas_analytics cuando el Mes coincida con el del Dashboard, el medio coincida con la fuente actual y el estado de pago sea "si". Es decir, la siguiente formula:

=SUMAR.SI.CONJUNTO(resumen.facturas_analytics!G:G;resumen.facturas_analytics!C:C;informe.global!$C$2;resumen.facturas_analytics!E:E;informe.global!E6;resumen.facturas_analytics!J:J;"si")

Esta luego la copiamos en el resto de filas de la columna.

- Para la columna de pendientes de pago hacemos exactamente lo mismo pero con el estado de pago a "no". Es decir:

=SUMAR.SI.CONJUNTO(resumen.facturas_analytics!G:G;resumen.facturas_analytics!C:C;informe.global!$C$2;resumen.facturas_analytics!E:E;informe.global!E6;resumen.facturas_analytics!J:J;"no")

- Por ultimo creamos los totales de ingresos por medio y de pagados y no pagados sumando las celdas creadas o en horizontal o en vertical:

Suma horizontal: =SUMA(F6:G6)
Suma vertical: =SUMA(F6:F9)

Ya tenemos esta primera tabla estática, solo nos queda darle un poco de color y aplicarle algun efectillo de los que ya vimos en el anterior dashboard.

• DESCARGAR EXCEL 9

2) Tabla de ingresos por fuente y keyword

Ahora añadiremos el desglose por keywords siguiendo exactamente la misma metodología: sacaremos los nombres de las 4 mejores keywords de la tabla dinámica y empezaremos a cruzar las columnas de medios con las de keywords:

=SUMAR.SI.CONJUNTO(resumen.facturas_analytics!$G:$G;resumen.facturas_analytics!$C:$C;informe.global!$C$2;resumen.facturas_analytics!$E:$E;informe.global!$O7;resumen.facturas_analytics!$F:$F;informe.global!K$11)

Esto lo cpiamos en cada celda de la rejilla que acabamos de montar y aprovechamos para realizar las columnas de suma de cada celda.

=SUMA(K7:K8)

3) Gráficos asociados

Ahora nos falta crear los gráficos. Para ello usaremos los totales creados:

- Crearemos un gráfico de queso con los totales de ingresos por fuente.
- Un gráfico de queso con los totales pagados y no pagadados.
- Y un gráfico de barras para comprar los ingresos por cada keyowrd.

Ya solo nos falta decorar un poco todo esto...

- Creamos un efectillo que demuestre que nuestros datos de keywords se desprenden de los totales de las fuentes "organic" y "other"
- Marcamos con formato condicional todos los resultados de keyword-fuente para que resalten en color más oscuro los importes más altos (y llamen más la atención)
- Añadimos el gráfico de barras de total de keywords encima del gráfico para que al estar integrado no haga falta añadir leyendas ni titulos y ahorrar espacio.
- Añadimos formato condicional de banderas de colores a los totales de keyword para destacar en verde las keywords que más importe nos aportan.
- Remarcamos con bordes amarillos los otros dos totales que tenemos con grácfico y les añadimos una forma de llamada que muestre como cada gráfico sale de esos totales.

Y ya está! y tenemos nuestro segundo dashboard, en el que los datos más importantes destacan por encima del resto y en poco espacio resumimos gran cantidad de la información importante.

• DESCARGAR EXCEL 10 (FINAL)

Conclusión

Creo que he mostrado las metodologías más utilizadas para crear dashboards en Excel con algún que otro truco que podéis poner en práctica. Pero al final el resultado hace lo que promete pero es dificil que puedas aplicarlo directamente a tu negocio... Esto es porque la fuente de datos que hemos usado no es una de las fuentes más comunes del mundo. Tus datos seguramente diferirán mucho de lo mostrado, no solo en sus valores sino en su organización. Es más que probable que quieras importar tablas completas de ventas con artículos vendidos por sus categorías y que importes las Landings y campañas de tu Analytics. ¿O quizá quieras importar tus datos de fans de Facebook o tus retweets?

Adelante! Yo solo he pretendido mostrarte el camino, no hacerte el trabajo... ¿no crees que es mucho más divertido así?

Otros tutoriales para seguir aprendiendo:

• Importando datos de Google Analytics

Posts Relacionados:

1. Importando datos de Google Analytics en Excel
   En mi anterior entrada os comentaba como crear Dashboards de analítica web en Excel. Entre otras cosas se explicaba como partiendo de hojas de datos...
2. Clinic SEO Analitica Web: Lo que se se vio y lo que no se vio…
   Bueno, como ya anuncié en el blog y en twitter este pasado martes 17 se celebró el 5º Clinic SEO, en este caso centrado en...
3. Informes personalizados de Google Analytics
   Google Analytics guarda la información de los usuarios que visitan nuestras páginas en base a dimensiones. Estas son distintos segmentos a tomar del total de...

Jaime tiene 29 años. Trabaja en una agencia de marketing online en la que poco a poco ha ido ascendiendo hasta director de grandes cuentas. Jaime suele planificar las inversiones online de 10 de las grandes marcas del país. Bajo su responsabilidad queda una facturación de 3 millones de euros. Jaime está muy contento porque en la evaluación del cierre de año va a poder demostrar que algo más de 300.000 euros de beneficio neto de la empresa han surgido de ideas suyas. Pero Jaime sabe que eso no le va a suponer una subida de ningún tipo, solo le validará en un puesto en el que por lo general la gente dura poco. Jaime sabe que a su edad y al haber subido desde abajo ha tocado techo, y mira de vez en cuando otras agencias para ver surge si alguna oportunidad de cambiar.

La madre de Marta, quedó el otro día con sus amigas. Al poco que empezaron a hablar de sus vástagos, ella les contó a todas que su hija trabaja en publicidad. Se dedica a idear anuncios, pero de los buenos, dijo ella. Y pronto la véreis por la tele, culminó. Todas sus amigas se quedaron maravilladas, hay que ver como son estas cosas modernas, dijo una. Es que siempre se la vio una chica muy creativa y trabajadora y es normal, sentenció su mejor amiga.

La madre de Jaime, quedó el otro día con sus amigas. Cuando todas empezaron a repasar su descendencia ella, por temor a no saber muy bien que decir, evitó el tema. Al final le llegó la pregunta directa: ¿y tu hijo a que se dedica?. Ella explico que trabajaba de un "noseque-de-internet", en cosas de webs y ordenadores, y bajo la cabeza porque no sabía como continuar. Ahh! Como paquito, el hijo del quiosquero que le hizo la "pagina wes" a su padre, dijo una. Oye! ¿Y no estaba mirando de cambiar de trabajo? Pues en la tienda de informatica de aquí abajo ponian que buscaban a alguien para montar ordenadores, le dijo su mejor amiga. Te he cogido el papelito.

Hoy Marta ha recibido la visita de su madre. Le ha dado un beso en la frente y le ha comentado lo muy orgullosa que está de ella. Seguidamente le ha preguntado por el anuncio de televisión, y se han puesto al día sobre los las ideas que Marta tenía y como las han recibido en el trabajo. La madre de Marta casi podía notarse ahí con su hija la ejecutiva dominandolo todo. Al final, la madre de Marta le ha pedido un favor de parte de una de sus amigas: que si podía llamar a Sarita, una adolescente que no sabe muy bien que estudiar y seguramente Marta sabría indicarle el camino del éxito...

Hoy Jaime ha recibido la visita de su madre. Le ha dado un beso en la frente y le ha preguntado que tal el trabajo. El ha contestado que "bien, con lío, como siempre" y su madre se ha quejado de que nunca le cuenta nada. Luego ha dicho que "Marilú" le habia encontrado un trabajo muy cerquita de su casa y le ha dado "el papelito". Ante las quejas de Jaime le ha hecho prometer que llamaría, aunque cree que va a tener que insistirle bastente más porque no parecía muy convencido. Al final, la madre de Jaime le ha peido un favor de parte de parte de una de sus amigas: que si podía acercarse a casa de Pedrito, que se le ha roto internet y no puede mirar el mail ni el feisbuc y seguramente Jaime sabrá como arreglar esas cosas...

-

Con todo mi cariño para la gente del marketing offline para que veais que lo nuestro también tiene sus cosas malas...

La organización por parte de la gente del Clinic también fue estupenda, con avisos constantes por distintos medios y lo mejor: esa sensación que te dejan de que todo está controlado. Como pegas podríamos decir que el streaming los primeros minutos no fue muy fino (aunque luego bcntalks se lo curró y consiguió arreglarlo) y que el tiempo se nos quedó corto en muchos sentidos: por un lado al disponer de solo 1 hora y media, a la hora de decidir que comentar fue obligarorio dejar muchas cosas fuera de la presentación o pasarlas muy rápidamente y por otro al tener la hora muy cerrada y haber esperado a que la gente se sentase para empezar, las conclusiones del proyecto -que sin duda eran lo más interesante-, no se pudieron explicar con calma...

Aún con esto, repito que yo estoy muy contento con el evento, pero está claro que no soy yo quien debe juzgarlo.

Un repaso al caso en general

Al ser un "Clinic", lo que hicimos fue acercar lo máximo posible la experiencia a la realidad. Para ello nada mejor que coger un caso real de un proyecto que por sus características aun no había podido sacar partido de la analítica ni de la inteligencia de negocio. Para ello Kico puso en el asador a uno de sus proyectos secundarios: itv.com.es, un site que inicialmente se creo por temas de link building (es decir, que no pensaba en potenciarse directamente sino que nació para apoyar a otros) y que fue creciendo hasta conseguir ser un proyecto interesante por si solo.

Sobre este site decidimos desarrollar lo que sería un proyecto de Analítica Web serio, donde incluso planteamos adoptar distintos roles, dejando a Kico el papel de cliente necesitado y haciendo xavi y yo de consultores de analítica web que reciben a su cliente.

Ante esto se planteo una metodología sencilla que seguir en todo proyecto de analítica web y que sinceramente esperabamos que fuese lo que más hondo calase en los asistentes: Necesitamos una metodología clara. Un roadmap de lo que vamos a hacer... de otra forma la analítica se transforma en informes sueltos y si no estás profundamente metido en el proyecto es fácil perder el norte y quedarse en los detalles. Xavi como responsable de Analítica en Atrapalo llevo este apartado de una forma muy lógica que sin duda fue la clave para dar sentido a toda la charla.

En esta metodología tratamos varios puntos que voy a resumir:

1) - Alineación - Definición del Proyecto
2) - Aproximación - Evaluación de la Situación Actual
3) - Dirección - Definir Objetivos
4) - Recogida de Datos - Implementación de herramientas
5) - Conocimiento - Análisis de las distintas fuentes de datos
6) - Acción - Recomendaciones, Cambios y Testing

Un plan claro, que puede ser aplicado como mapa general a cualquier proyecto de analítica web. Dentro del mismo desarrollamos los pasos uno a uno de lo que fué el proyecto de análisis de itv.com.es. Resumiré algunos puntos, pero está claro que no puedo reproducir toda la conferencia en este post...

En la definición del proyecto dejamos claro que lo importante es la entrevista detallada con el cliente y la extracción de objetivos claros de la misma. En este caso vimos que el "cliente" tenía dos necesidades claras con este proyecto:

1. “Necesita rentabilizar el proyecto”
2. “Antes, quiere asegurarse de que da los pasos correctos”.

De ahí pasamos a evaluar la situación actual del mismo: que lo conforma, que tipología de usuarios tiene y en que basa su éxito.

El primer punto dentro de esto fue el análisis heurístico o dicho de otra forma: una primera aproximación que busque conclusiones rápidas sobre el proyecto. Dentro de este área destacamos algo obvio que pero muchas agencias no llegan a hacer: navegar por la web y convertirse en uno más de sus usuarios. Con este análisis identificamos las temáticas del site, su carencia de objetivos con los usuarios y las 3 zonas de las que estaba formado: "home y páginas", "centros de ITV" y "blog".

Siguiendo con la evaluación de la situación actual, pasamos a clasificar a los usuarios gracias a Google Analytics, destacando que no tiene ningún sentido este paso hasta haber conociedo bien la web.

Dentro de este análisis rápido vimos las 4 métricas imprescindibles:

- 119.000 visitas
- Rebote de un solo 27%
- 4,2 páginas por visita
- y fidelización de solo un 23%

También desechamos intencionadamente parte de los valores que nos da como clave analytics: visitantes únicos, paginas vistas y tiempo en el sitio (este último por resultar poco creible).

De ahí pasamos a analizar de donde vienen los usuarios dejando claro que Google era sin duda la fuente de tráfico con lo que nos encontrabamos ante un proyecto puramente SEO.

Además vimos como, la asignación de referencias no siempre es correcta y en este caso el 50% del tráfico de referencia también correspondía a buscadores.

El siguiente paso lógico fue examinar las palabras clave que usaban los usuarios para llegar al site. Para ello destacamos que mirar keywords sueltas no sirve de mucho si lo que buscamos es un análisis global de la web. No queremos decir que este informe esté mal, de hecho puede resultar muy útil para algunas tareas SEO y SEM, pero en analítica no podemos perder el tiempo con keywords concretas, tenemos que hablar siempre de segmentos de usuarios más o menos importantes en el global de la web.

Así pues, comentamos como a base de ir lanzando filtros en el informe de analytics de keywords llegabamos a descubrir los segementos de usuarios más importantes del site:

(ejemplo de uno de estos filtros rápidos)

Así pues detallamos que el tráfico venía de estas fuentes.

Keywords que contienen “itv”: 109.202 visitas (75%)

Y dentro de estas:

“itv” exacta: 57.095 (50%)
“itv” + “cita” : 28.409 (20%)
“itv” + provincias de españa: 13.789 (12%)
“itv” + “servicio” : 6.296 (5%)
“itv” + “telefono” : 908 (0,7%)
Resto de “itv” : 10% (sobretodo keywords con localidades)

Así pues, nuestra conclusión después de disponer de estos datos fue la siguiente:

- Sin Objetivos, no sabemos la calidad del tráfico
- Google es LA fuente de tráfico.
- El tráfico fundamentalmente llega interesado por la ITV o por conseguir una Cita para la ITV.
- El tráfico está interesado pero es fugaz: no hay fidelidad y no hay apenas retorno del usuario.

El siguiente paso, como no, fue definir objetivos para el tráfico que acabamos de identificar.

Después de un análisis del entorno de la temática ITV y la propia web, decidimos estos dos objetivos:

1) Captar Leads de los sectores servicios para motor y motor
2) Seguir conservando ingresos por publicidad.

A partir de ahí hicimos un paso que consideramos muy importante: Definir objetivos de analítica a partir de los objetivos de negocio:

1) Probar la capacidad de las distintas páginas de la web de captar Leads fáciles y de calidad media/baja.
2) Saber qué cantidad de datos podemos llegar a solicitar al usuario
3) Empezar a detectar puntos calientes en la web.
4) Debemos adecuar las métricas de la web a nuestro objetivo secundario: la publicidad.

Con el análisis realizado, realmente pudimos responder a todos estos objetivos, pero como os decía, por desgracia, apenas pudimos desarrollar esta respuesta durante la conferencia.

A partir de ahí ya teníamos lista la base de nuestro proyecto... lo que quedaba era implementar formas de medir todos los datos que necesitabamos, analizarlos y tomar acciones. En esos 3 bloques tuvimos que pasar solo por encima de algunas cosas y espero podais verlas un poco más en este post...

La implementación

En Google analytics y en la propia web lanzamos cambios que debían ponernos en una mejor situación para responder a nuestras dudas. Algunos de ellos fueron los siguientes...

Duplicando el código de Seguimiento de google Analytics) Por necesidades del "cliente" fue necesario que creasemos un nuevo código de analytics conviviendo con el antiguo.

Aquí destacamos como mucha gente se equivoca y simplemente copia dos códigos de seguimiento en la web, uno tras otro, cuando lo que hay que hacer es añadir a cada orden dada en analytics una variable (ponerle nombre a cada llamad). Esto lo hacemos indicando en cada función de analytics (identificadas porque empiezan por "_") ese nombre seguido de un punto.

Así pues pasamos de:

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'CODIGO_ANTIGUO_DE_ANALYTICS']);
  _gaq.push(['_trackPageview']);
 
(function() {
  var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
  ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
  var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();

A la versión con dos códigos de seguimiento:

  var _gaq = _gaq || [];
 
  _gaq.push(['historic._setAccount', 'CODIGO_ANTIGUO_DE_ANALYTICS']);
  _gaq.push(['historic._trackPageview']);
 
  _gaq.push(['testing._setAccount', 'CODIGO_NUEVO_DE_ANALYTICS']);
  _gaq.push(['testing._trackPageview']);
 
(function() {
  var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
  ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
  var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();

De esta forma, a partir de ahora cuando usemos analytics lo haremos siempre con "testing." delante de cada orden y así solo actuaremos sobre esa cuenta de analytics.

El siguiente paso fue manipular el rebote en analytics, dejando claro que que el rebote sea una página vista no nos aportaba nada. Aquí quisimos igualar el rebote a usuarios que no han realizado ningún click en la página y para ello presentamos el siguiente código que usa jquery para hacer esta tarea más simple:

var firstBodyClick = false;
$(document).ready(function(){
  $('body').click(function(){
    if (!firstBodyClick)
    {
      _gaq.push(['testing._trackEvent', 'NoBounce', 'NoBounce', 'One Click']);
      firstBodyClick = true;
    }
  });
});

Y que viene a hacer que la primera vez que el usuario haga click en una página se guarde un evento para que Analytics entienda que esa visita no es un rebote.

Lo siguiente que configuramos fue un test A/B, para averigurar en un nuevo formulario en TODAS las páginas del site como funcionaba la captación de Leads con solo un campo o con 3.

Aqui presentamos tres formas de hacer A/B cláicas.

1) Usando Google Optimizer: Un servicio gratuito pero que funciona sobretodo con tests individuales montados uno a uno y que por tanto no nos era cómodo.

2) Optimizely, una herramienta que personalmente me encanta. Te permite crear Test A/B sobre diseño de forma muy directa (con un editor sobre tu web que te permite en cada variante del test mover con el raton piezas de la web, editar textos, imágenes etc.). Es un servicio de pago -con prueba gratuita-, pero realmente brutal cuando estamos optimizando landings. Por desgracia, en este caso y dado que el test era sobre campos del formulario, no tenía sentido usarlo.

3) Por último comentamos la posiblidad de hacerlo "a mano" con programación a medida y añaidendo datos a Google Analytics. Nos decantamos por esta opción al ser un test sencillo de implementar técnicamente.

Aquí montamos la típica base del A/B, un random que hacía que al llegar cada nuevo visitante tuviese la mitad de posibilidades de ver un formulario con 3 campos y la otra mitad de ver uno con un solo campo.

Dependiendo de la versión que el usuario viese se cambiaba el código de analytics añadiendo una Variable personalizada para indicar a analytics que versión vió el usuario.

Versión Formulario con 3 campos:

 var _gaq = _gaq || [];
  _gaq.push(['testing._setAccount', 'CODIGO_ANALYTICS']);
  _gaq.push(['testing._setCustomVar', 5, 'AB Test', 'big_form', 2]);
  _gaq.push(['testing._trackPageview']);

Versión Formulario con 1 campo:

 var _gaq = _gaq || [];
  _gaq.push(['testing._setAccount', 'CODIGO_ANALYTICS']);
  _gaq.push(['testing._setCustomVar', 5, 'AB Test', 'small_form', 2]);
  _gaq.push(['testing._trackPageview']);

En ambos casos guardamos la variable en el slot 5 (por si kico quería usar el 1 para otra cosa) y las guardamos en ámbito "2" que si vemos la documentación de analytics vemos que se refiere a que esa variable contará para todas las páginas vistas que ese usuario vea durante su sesión. Así ya teníamos el A/B configurado.

Por último, lanzamos un test para detectar puntos calientes en la web, en el que buscabamos detectar si el teléfono era un elemento muy importante para los usuarios, lo suficiente como para pensar en realizar acciones con los usuarios que lo busquen...

Para ello trasformamos los teléfonos de la web en links con el texto "Click para ver Teléfono" de forma que obligabamos a los usuarios a clickar para saber los telefonos. No se trataba de ponérselo más difícil, eso nunca es bueno, sino averiguar a cuantos de los que veían esas páginas les interesaba el teléfono. Así, y como había dos tipos de muestras de teléfono en la web, lanzamos 2 posibles eventos: Telefonos vistos - Desde los listados y Telefonos vistos - Desde fichas. Para ello implementamos el siguiente código con jquery:

$(document).ready(function () {
 
 // Ocultando los teléfonos
$('.phone-list').each(function (i,el) {
  $el = $(el);
  $el.data( "phone", $el.html() );
  $el.html( "<a href='#' class='show-phone-list'>Click para ver Teléfono</a>" );
});
 
$('.phone-record').each(function (i,el) {
  $el = $(el);
  $el.data( "phone", $el.html() );
  $el.html( "<a href='#' class='show-phone-record'>Click para ver Teléfono</a>" );
});
 
// Acciones para mostrarlos:
$("a.show-phone-list").click(function () {
  $p = $(this).parent();
  $p.html( $p.data("phone") );
  _gaq.push(['testing._trackEvent', 'Telefonos vistos', 'Desde los listados']);
  return false;
});
 
$("a.show-phone-record").click(function () {
  $p = $(this).parent();
  $p.html( $p.data("phone") );
  _gaq.push(['testing._trackEvent', 'Telefonos vistos', 'Desde fichas']);
  return false;
});
 
});

A partir de ahí configuramos los objetivos en Google Analytics para poder medir todo lo que nos interesaba: los leads conseguidos (para medirlos y para medir el A/B) y los telefonos clickados. Os dejo capturas de esta configuración:

Depués, tendríamos además que configurar el propio google analytics para poder sacar partido a nuestras clasificaciones de tráfico y a los objetivos indicados.

Para ello hicimos 3 cosas:

1) Configurar Segmentos por Keywords.

Donde configuramos sementos avanzados para poder ver por separado el tráfico de los distintos grupos de keywords que analizamos. Os dejo capturas de esa configuración:

2) Configurar Segmentos por Landings.

También configuramos segmentos avanzados para poder ver el tráfico por grupos de landings. Como habíamos detectado que la web se dividia en 3 zonas claras configuramos esos 3 segmentos: home + paginas, centros de Itv y Blog:

Divertido el último de ellos, ya que como el proyecto lanzaba tanto posts como páginas desde la raiz del dominio, hubo que hacerlo a la inversa: todo lo que no fuese centro o página tenía que ser blog.

3) Configurar informes personalizados

Y para poder ver los datos que realmente más nos interesaban, configuramos nuevos informes en google analytics que poder lanzar para ver el tráfico global o con los segmentos creados. Así creamos 2 informes: uno para leads, donde ver en 3 pestañas "leads por keywords", "por landigs" o ver los resultados del "test A/B" y otro para ver resultados de los clicks en teléfonos "por keywords" o "por landings":

A partir de ahí ya pudimos disfrutar de una serie de informes en analytics que nos permitieron ir tomando conclusiones. Aquí algunos ejemplos:

1) El resultado global del Test A/B en analytics:

Que luego, al añadirle el double-optin con MailChimp se desmentiría totalmente, dándole la vuelta al resuttado.

2) La capacidad de distintas páginas de traer tráfico que capte leads:

Donde vemos lo poco útil que nos resulta el blog para todo esto.

3) El mismo informe pero orientado a teléfono

4) La capacidad de los distintos grupos de keywords de traer tráfico que capte leads.

5) Y el mismo informe para teléfonos

6) Un ejemplo de como cruzar datos e informes, viendo las landings que más leads traen cuando la keyword es de "ITV+Cita"

7) El mismo informe aplicado a teléfonos...

8 ) y por último, un ejemplo más simple para ver los top10 de cada grupo de keywords y entenderlas un poco mejor:

El Análisis

A partir de ahí cogio las riendas Xavi y empezo a hacer analítica avanzada, donde estaba claro que Google Analytics se quedaba corto y era necesario extraer sus datos y mezclarlos con otras fuentes.

No voy a explicar aquí todo lo que digo sobre el método AIDA(s) y sobre el cuadro de control que ofreció... más que nada porque ese es material que le da pie a unos cuantos posts en su blog y no quiero chafarselos.

El tema es que dio un gran repaso a distintas formas de presentar la información y a como al ver algunos datos se demuestra que trabajo seguir en SEO: cuando atacar una keyword, cuando cambiarla por otra, cuando trabajar sus textos... etc. Remató todo esto con la demostración práctica de como trabajar con analítica cualitativa, algo que muy poca gente sabe hacer actualmente y que él domina a la perfección.

Comentaré tan solo el análisis que hicimos con los resultados de CrazyEgg, ya que esa parte si que "me tocó" comentarla.

Por un lado analizamos la Home, del site viendo dos cosas muy claras:

- Que el mapa era el gran "culpable" de la bajisima tasa de rebote del site.

- Que la página "cita itv" del menú superior era la zona más accedida y por tanto un punto caliente donde deberíamos actuar. Eso se completaba con que esa página es actualmente textual y sin llamadas a la acción lo que significa una perdida de oportunidades muy grande.

También analizamos una de las páginas de listados:

Y volvimos a descubrir que el mapa -en este caso de Google Maps- se llevaba todo el protagonismo de la página. En este caso hasta el punto de hacer que el click en los teléfonos disminuyese y confirmando lo que ya estabamos empezando a intuir... los teléfonos no son un punto caliente. Analytics nos daba solo un 20% de CTR en los eventos de teléfono incluidos lo cual no podemos considerarlo punto caliente...

Conclusiones

Al final, creo que demostramos como siguiendo la metodología adecuada uno aprende que debe preguntarle a la analítica web y termina obteniendo respuestas... en este caso expusimos unas conclusiones muy claras sobre el proyecto.

- La captación de Leads es un modelo de negocio válido: lo hemos comprobado por volumen de registros, por la validación de estos con mailchimp.

- Tenemos el conocimiento para optimizar este proceso: El A/B y los resultados de captación divididos por landings o por grupos de landings nos muestran claramente el camino a seguir en este proceso: nuestras mejores landings y el tipo de acciones a realizar.

- Hemos identificado a nuestros usuarios y entendemos sus motivaciones y necesidades: Sabemos para cada tipo de usuarios y para cada segmento que es lo que les interesa. A donde van y como conviernten.

- Hemos identificado las palabras clave y landing críticas para nuestro site: Sabemos donde está el core de nuestro negocio.

- Podemos definir los test que aportan valor al negocio: El resultado de los tests nos da pie a nuevos tests: con el lead, con la página de cita previa, con los mapas, etc.

- Tenemos una herramienta de seguimiento que nos ahorrará muchísimo tiempo: Gracias a Xavier se crea un control total del proceso de captación vía SEO y dispondemos de un panel de control para ir haciendo seguimiento del negocio semana a semana.

Algunos posts más sobre el evento:

• El post de Kico sobre el Clinic SEO
• Resumen del evento en analistaseo.es

Posts Relacionados:

1. Clinic SEO sobre analítica web, el próximo 17 de Enero en Barcelona
   Buenas, espero que disculpeis un poco el autobombo pero creo que con tan pocos eventos a los que voy estaba obligado a comunicar en el...
2. Google quiere cargarse la analítica web para SEO
   En el blog de Analytics de Google aparecía este martes 18 una noticia que a cualquiera que esté en este mundillo, como poco, le ha...
3. Simplifica el código de Google Analytics Asíncrono con Jquery
   Hace no demasiado google Analytics se actualizaba y lanzaba su versión asíncrona. Para enseñárnoslo nos mostraban un fragmento de código de ejemplo que como todo...

El próximo 17 de Enero se celebrará el 5º Clinic SEO, en esta ocasión sobre analítica web en campañas SEO. La gente del Clinic ha tenido a bien invitarnos a mi y a Xavier Colomes y a Kico Pascual a este evento donde analizaremos un proyecto propio y su orientación hacia negocio con las herramientas típicas de analitica web.

A mi o habéis caido de rebote en este blog o ya me conocéis, la analítica es un tema que me apasiona y en el que no paro de probar y experimentar. Es un área en la que siempre puedes descubrir y probar cosas. Estos descubrimientos o conocimientos consensuados voy plasmándolos cada cierto tiempo en este blog, pero sin duda va a ser muy interesante poder compartirlos en un auditorio con más gente del ramo.

Xavier, dentro de la analítica web apenas necesita presentación. Tiene un largo recorrido y es sin duda uno de los referentes actuales en este campo. Actualmente trabaja como Responsable de Analítica en Atrapalo.com. También es la persona que hay detrás del blog Top-rankin que sin duda deberías seguir si te interesan estos temas.

Kico, tambien es muy dificil que no te suene. Es uno de los SEOs más conocidos en España, siempre con técnicas muy novedosas. Es el director de Interdigital y el "culpable" de la existencia del site que vamos a analizar. A los pocos que no lo conozcan saber que alguién esta en Internet desde el '97 y que su blog personal es kico.es (con 4 minúsculas letras) debería decirles algo...

Seguidamente os paso los datos del evento:

• Martes 17 de Enero, a las 19:30
• En el "Espai Jove La Fontana", Barcelona
• La entrada es gratuita, pero debe reservarse plaza en la web del Clinic SEO (PUEDES RESERVAR ENTRADA YA MISMO)
• Habrá servicio de Video Streaming a cargo de BcnTalks, (link directo al streaming)

Para terminar, os dejo aquí el video de presentación del evento, con Kico en portada y una producción bastante trabajada:

Posts Relacionados:

1. Clinic SEO Analitica Web: Lo que se se vio y lo que no se vio…
   Bueno, como ya anuncié en el blog y en twitter este pasado martes 17 se celebró el 5º Clinic SEO, en este caso centrado en...