Un problema al que solemos enfrentarnos a menudo es el de automatizar la extracción de datos de una fuente. Hoy os voy a hablar de esto, concretamente de la automatización del reporting de ecommerce utilizando un WordPress con Woocommerce.
Vaya por delante que existen conectores que sincronizan la base de datos de WordPress (MySQL) con Google BigQuery y, desde ahí, crear un informe con Google Data Studio es pan comido, pero hoy me voy a saltar a BigQuery como intermediario y vamos a conectar directamente nuestra base de datos con Data Studio. ¿Es la forma correcta de hacerlo? Pues depende.
Si nuestro cms, sea WordPress, Prestashop o cualquier otro, tiene un volumen de datos considerable, lo mejor es que utilicemos alguno de esos conectores y comencemos a trabajar un dataset en BigQuery con esa información, siendo ésta la herramienta para centralizar los datos. Ahora bien, si trabajamos con una plataforma con poco volumen de datos, generalmente de alguna pyme o microempresa y solo queremos crear una visualización de datos con una o dos fuentes de información que permite al cliente entender correctamente la información, podemos hacer esta conexión directa que os voy a explicar.
Vamos a hablar de...
¿Qué queremos plasmar en la visualización de datos?
Para el ejemplo de este artículo me voy a centrar en datos de una tienda online con Woocommerce. En este caso, voy a tratar de extraer la información necesaria como para tener datos de pedidos y de ventas de productos para poder ver la evolución de las ventas a lo largo del año y qué productos se venden más.
De igual modo, si únicamente nos interesara la información de un blog, podríamos extraer datos como el número de artículos de cada autor, sus títulos y, para cada uno, ver información de las urls de Google Analytics combinando datos, que nos permitieran saber el engagement de cada post y determinar qué autor genera mayor complicidad con los lectores. Por ejemplo.
Así pues, antes de lanzarnos a la aventura del SQL o a crear paneles en Data Studio, como sucede siempre en la analítica digital, debemos definir qué KPI queremos extraer y mostrar y con qué objetivo. Por supuesto, como he comentado refiriéndome a la información del blog, cualquier dato que extraigamos de una base de datos y que pueda ser combinado a través de una clave primaria o tecla de unión (por ejemplo un ID de transacción o un path) con Google Analytics, nos permitirá conocer mucha más información, con un mayor contexto, de los elementos de nuestra web.
Imagina que puedo listar los productos más vendidos y, además, saber el tiempo medio en página, el porcentaje de rebote o el porcentaje de scroll que realizan en la ficha de esos productos. Esto nos puede dar información sobre si esta ficha puede mejorarse o, por el contrario, provoca mucho interés.
¿Dónde se encuentra esa información en la base de datos?
El esquema de la base de datos de WordPress + Woocommerce es bastante sencillo. Lo primero que te tienes que grabar a fuego es que todo son wp_posts, da igual que hablemos de artículos, de pedidos o de productos. Siempre podremos aislar la tipología de post mediante la tabla wp_postmeta, que contiene la información de los posts, y su columna meta_key. Sin embargo, como en este artículo estamos tratando el apartado ecommerce, voy a utilizar las tablas propias del plugin de Woocommerce, que hace la localización de los datos mucho más sencillas.
La mejor manera de comenzar a familiarizarse con el esquema de la base de datos de WordPress es echando un vistazo a sus relaciones en la siguiente imagen extraída de codex.wordpress.org:
Como veis, wp-posts es la tabla central que conecta a través de claves primarias y foráneas con el resto de tablas de WordPress. Pero, ¿y las tablas de Woocommerce? Pues tiene las suyas propias, si quieres conocerlas, echa un vistazo a este enlace. Éstas almacenan principalmente toda la información de pedidos, productos, cupones, gastos de envío y todo lo relacionado con las funcionalidades de ecommerce que añade este plugin.
Siguiendo con el ejemplo a explicar, para extraer toda la información relacionada con pedidos y productos tan solo necesitaremos consultar 3 tablas: la de pedidos (_order_product_lookup), la de estados de pedido (_order_stats) y la principal de WordPress (_posts). ¿Cómo se descubre esto? Pues simplemente echando un vistazo a los valores de cada una y empapándote del esquema. Para ello, claro, debes tener acceso a la base de datos del WordPress.
Permisos de lectura para Google Data Studio
Ahora ya sabemos de dónde extraer la información, pero para ello, podemos ir a lo loco o tener un poco de cabeza y crearnos un usuario de base de datos únicamente con permisos de lectura, pues vamos a tener que dar acceso a Google a esos datos y no queremos que, por lo que sea, se pierdan por el camino y alguien pueda provocar un destrozo.
Los host que tendréis que autorizar los podéis encontrar en la documentación de Data Studio. Se trata de los siguientes:
64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
108.177.8.0/21
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.58.192.0/19
216.239.32.0/19Pero antes de eso, volvemos a la parte de usuarios. Si disponéis de un panel de administrador tipo cPanel, simplemente tendréis que acceder al apartado de bases de datos y seguir los pasos de creación de usuario estableciéndole únicamente permisos de lectura:
Ahora sí, para dar permisos, deberéis dirigiros al apartado MySQL Remoto o similar que tenga vuestro panel de administración. Tenéis que tener en cuenta los rangos de las IP, si utilizáis esta herramienta, por ejemplo, y colocáis cualquiera de las IP de Google Data Studio, veréis que los rangos abarcan el cambio de los dos últimos dígitos de cada IP. De este modo, debemos contemplar esas modificaciones con comodines, si no queremos añadir todas las IP, lo que nos llevaría unas cuantas horas. Quedaría algo como lo siguiente:
Creación de sentencia SQL con la información necesaria
Una vez tenemos nuestro usuario con solo lectura de la tabla que nos interesa consultar y los permisos de los diferentes hosts abiertos a Google, llega la parte divertida, que es la de hacer consultas en MySQL a las tablas de WordPress.
Como he dicho antes, lo más fácil es analizar las tablas de este cms, bien desde PhpMyAdmin o el gestor de SQL que utilices, bien desde la documentación de WordPress, y empezar a establecer los SELECT, los JOIN o las instrucciones que necesites para plasmar la información que deseas.
A continuación os dejo un par de consultas para crear una tabla de pedidos y otra de productos, serán sentencias que deberéis ejecutar en vuestro cliente, asegurándoos que las tablas se llaman igual (revisad siempre el prefijo), para comprobar que funcionan, pues luego las llevaremos a Google Data Studio.
SELECT con pedidos, estado y valores económicos
SELECT
wc_product.order_id,
wc_product.date_created AS date,
wc_stats.status,
SUM(wc_product.product_qty) AS product_quantity,
SUM(wc_product.product_net_revenue) AS net_revenue,
SUM(wc_product.product_gross_revenue) AS gross_revenue,
SUM(wc_product.coupon_amount) AS coupon_amount,
SUM(wc_product.tax_amount) AS tax_amount,
SUM(wc_product.shipping_amount) AS shipping_amount,
SUM(wc_product.shipping_tax_amount) AS shipping_tax_amount
FROM `wp_wc_order_product_lookup` wc_product
LEFT JOIN
`wp_wc_order_stats` wc_stats
ON wc_product.order_id = wc_stats.order_id
GROUP BY wc_product.order_id;
SELECT de lista de productos y sus ventas totales por fecha
SELECT
product_id,
order_id,
date_created AS date,
(SELECT sku FROM `wp_wc_product_meta_lookup` WHERE product_id = wc_product.product_id) AS sku,
(SELECT post_title FROM `wp_posts` WHERE ID = wc_product.product_id) AS product_name,
(SELECT max_price FROM `wp_wc_product_meta_lookup` WHERE product_id = wc_product.product_id) AS price
FROM `wp_wc_order_product_lookup` wc_product;
Creación de fuente de datos desde MySQL
Y, por fin, solo queda conectarnos a la base de datos desde Google Data Studio. Para ello, dentro del informe donde vayamos a trabajar, accederemos a su versión de edición y, en el menú superior a “Añadir datos”. Una vez ahí, buscaremos el conector de MySQL.
Entramos en él y rellenamos la información necesaria para conectarnos a nuestra base de datos: host, nombre de la base de datos, nombre de usuario y contraseña. Al acceder, veremos dos opciones de configuración, además de todas las tablas de nuestra BD.
En nuestro caso, iremos a CONSULTA PERSONALIZADA y pegaremos el SELECT que he puesto en la sección anterior. La añadimos al informe y ya la tendremos disponible para poder mostrarla en nuestro dashboard de Google Data Studio. Deberemos repetir este proceso con tantas consultas como tengamos:
Pero ojo, antes de nada, deberemos asignar a cada campo el formato adecuado, pues si no lo hacemos, tendremos problemas a la hora de configurar nuestros gráficos.
Y, ahora sí, solo nos queda mostrar la información obtenida en los diferentes gráficos que deseemos. Eso sí, cómo habrás visto, en la consulta de pedidos he incluido el post_status, eso nos permitirá crear filtros en el propio Data Studio por si queremos mostrar, por ejemplo, únicamente los pedidos válidos, los reembolsados, los cancelados, etc. Como idea, esta sentencia nos permitiría crear una gráfico de serie temporal en el que ver los ingresos mensuales de todo el año.
Por otro lado, la consulta de productos, nos permite ver los más vendidos por el segmento de tiempo que queramos, por ejemplo el mes del año, y sacar la suma total de ingresos que ha producido ese producto. Entre otras cosas.
Las posibilidades de conectar Google Data Studio a una base de datos son tantas como la información que ésta contenga, da igual que sea a través de MySQL, BigQuery o el tipo de bases de datos y plataforma que uses, siempre que exista el conector.
Y hasta aquí este pequeño tutorial que os permitirá crear informes en tiempo real para vuestros clientes. ¡Espero que os haya servido!
