La herramienta Webalizer y Google Analytics me han ayudado durante muchos años a entender lo que sucede en los sitios web. Ahora entiendo que proporcionan muy poca información útil. Con acceso a mi archivo access.log, es muy sencillo analizar las estadísticas y basta con herramientas básicas como sqlite, html, el lenguaje sql y cualquier lenguaje de programación de scripts.
La fuente de datos para Webalizer es el archivo access.log servidores. Así se ven sus columnas y cifras, de las cuales solo se puede entender el volumen general de tráfico:
Herramientas como Google Analytics recopilan datos de la página cargada por sí mismas. Nos muestran un par de gráficos y líneas, a partir de las cuales a menudo es difícil sacar conclusiones correctas. ¿Tal vez debería haber puesto más esfuerzo? No lo sé.
Entonces, ¿qué me gustaría ver en las estadísticas de visitas al sitio?
El tráfico de usuarios y bots
A menudo, el tráfico de los sitios tiene un límite y es necesario ver cuánto tráfico útil se está utilizando. Por ejemplo, así:
Consulta SQL del informe
SELECT
1 as 'StackedArea: Tráfico generado por Usuarios y Bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Día',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Usuarios, KB'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG
WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTEl gráfico muestra una actividad constante de bots. Sería interesante estudiar en detalle los representantes más activos.
Bots molestos
Clasificamos los bots en función de la información del agente de usuario. Estadísticas adicionales sobre el tráfico diario, el número de solicitudes exitosas y fallidas dan una buena idea de la actividad de los bots.
Consulta SQL del informe
SELECT
1 AS 'Tabla: Bots Molestos',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB por Día',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs por Día',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Error del Cliente', 'Error del Servidor') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Solicitudes de Error por Día',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Exitoso', 'Redirección') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Solicitudes Exitosas por Día',
USG.USER_AGENT_NK AS 'Agente'
FROM FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS STS
WHERE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID
AND FCT.DIM_HTTP_STATUS_ID = STS.DIM_HTTP_STATUS_ID
AND USG.AGENT_BOT != 'n.a.'
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10En este caso, el resultado del análisis fue la decisión de restringir el acceso al sitio mediante la adición al archivo robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Los primeros dos bots desaparecieron de la tabla, mientras que los robots de MS se desplazaron hacia abajo en las primeras filas.
Día y hora de mayor actividad
En el tráfico se observan picos. Para investigarlos en detalle, es necesario identificar el momento en que ocurrieron, no es necesario mostrar todas las horas y días de la medición del tiempo. Esto facilitara encontrar solicitudes individuales en el archivo de registro si se requiere un análisis detallado.
Consulta SQL del informe
SELECT
1 AS 'Línea: Día y Hora de Impactos de Usuarios y Bots',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Fecha Hora',
HIB AS 'Bots, Impactos',
HIU AS 'Usuarios, Impactos'
FROM (
SELECT
EVENT_DT,
SUM(CASE WHEN AGENT_BOT!='n.a.' THEN LINE_CNT ELSE 0 END) AS HIB,
SUM(CASE WHEN AGENT_BOT='n.a.' THEN LINE_CNT ELSE 0 END) AS HIU
FROM FCT_ACCESS_REQUEST_REF_HH
WHERE datetime(EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY EVENT_DT
ORDER BY SUM(LINE_CNT) DESC
LIMIT 10
) ORDER BY EVENT_DTObservamos las horas más activas 11, 14 y 20 del primer día en el gráfico. Sin embargo, al día siguiente, los bots estaban activos a las 13 horas.
Actividad promedio diaria de los usuarios por semanas
Ya hemos abordado la actividad y el tráfico. La siguiente pregunta fue la actividad de los propios usuarios. Para tal estadística, son deseables períodos de agregación más largos, por ejemplo, una semana.
Consulta SQL del informe
SELECT
1 AS 'Línea: Actividad Promedio Diaria de Usuarios por Semana',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Semana',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Páginas por IP por Día',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Archivos por IP por Día'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS HST
WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID
AND USG.AGENT_BOT='n.a.' /* solo usuarios */
AND HST.STATUS_GROUP IN ('Successful') /* páginas buenas */
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTLa estadística de la semana muestra que, en promedio, un usuario abre 1.6 páginas al día. La cantidad de archivos solicitados por usuario en este caso depende de la adición de nuevos archivos al sitio.
Todas las solicitudes y sus estados
Webalizer siempre mostraba códigos específicos de páginas y siempre se quería ver simplemente la cantidad de solicitudes exitosas y errores.
Consulta SQL del informe
SELECCIONAR
1 como 'Línea: Todas las Solicitudes por Estado',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Día',
SUM(CASE WHEN STS.STATUS_GROUP='Exitoso' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Éxito',
SUM(CASE WHEN STS.STATUS_GROUP='Redirección' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Redirección',
SUM(CASE WHEN STS.STATUS_GROUP='Error del Cliente' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Error del Cliente',
SUM(CASE WHEN STS.STATUS_GROUP='Error del Servidor' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Error del Servidor'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_HTTP_STATUS STS
WHERE FCT.DIM_HTTP_STATUS_ID=STS.DIM_HTTP_STATUS_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTEl informe muestra solicitudes, no clics (hits); a diferencia de LINE_CNT, la métrica REQUEST_CNT se considera como COUNT(DISTINCT STG.REQUEST_NK). El objetivo es mostrar eventos efectivos, por ejemplo, los bots de MS consultan cientos de veces al día el archivo robots.txt, y en este caso, tales consultas se contarán una sola vez. Esto ayuda a suavizar los picos en el gráfico.
Del gráfico se puede observar muchos errores; se trata de páginas inexistentes. Como resultado del análisis, se añadieron redirecciones desde las páginas eliminadas.
Solicitudes Erróneas
Para analizar las solicitudes en detalle, se puede generar estadísticas detalladas.
Consulta SQL del informe
SELECCIONAR
1 COMO 'Tabla: Principales Solicitudes de Error',
REQ.REQUEST_NK AS 'Solicitud',
'Error' COMO 'Estado de la Solicitud',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits por Día',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs por Día',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB por Día'
FROM
FCT_ACCESS_REQUEST_REF_HH FCT,
DIM_REQUEST_V_ACT REQ
WHERE FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID
AND FCT.STATUS_GROUP IN ('Error del Cliente', 'Error del Servidor')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20En esta lista también estarán todas las llamadas, por ejemplo, solicitudes a /wp-login.php. A través de la corrección de las reglas de reescritura de solicitudes, el servidor se puede ajustar la respuesta del servidor a tales solicitudes y redirigirlas a la página de inicio.
Así, algunos informes sencillos basados en el archivo de registro del servidor proporcionan una imagen bastante completa de lo que está sucediendo en el sitio web.
¿Cómo obtener información?
Las bases de datos sqlite son más que suficientes. Crearemos tablas: una auxiliar para el registro de procesos ETL.
Tabla de staging, donde registraremos archivos de registro mediante PHP. Dos tablas de agregados. Crearemos una tabla diaria con estadísticas sobre agentes de usuario y estados de solicitudes. Una tabla horaria con estadísticas sobre solicitudes, grupos de estados y agentes. Cuatro tablas de dimensiones correspondientes.
Como resultado, se obtuvo el siguiente modelo relacional:
Modelo de datos
Script para crear un objeto en la base de datos sqlite:
DDL creación de objeto
ELIMINAR TABLA SI EXISTE DIM_USER_AGENT;
CREAR TABLA DIM_USER_AGENT (
DIM_USER_AGENT_ID ENTERO NO NULO CLAVE PRIMARIA AUTOINCREMENTAR,
USER_AGENT_NK TEXTO NO NULO DEFECTO 'n.a.',
AGENT_OS TEXTO NO NULO DEFECTO 'n.a.',
AGENT_ENGINE TEXTO NO NULO DEFECTO 'n.a.',
AGENT_DEVICE TEXTO NO NULO DEFECTO 'n.a.',
AGENT_BOT TEXTO NO NULO DEFECTO 'n.a.',
UPDATE_DT ENTERO NO NULO DEFECTO 0,
ÚNICO (USER_AGENT_NK)
);
INSERTAR EN DIM_USER_AGENT (DIM_USER_AGENT_ID) VALORES (-1);Estadio
En el caso del archivo access.log, es necesario leer, analizar y guardar en la base todos los registros. Esto se puede hacer directamente mediante un lenguaje de scripting o utilizando herramientas de sqlite.
Formato del archivo de registro:
//67.221.59.195 - - [28/Dec/2012:01:47:47 +0100] "GET /files/default.css HTTP/1.1" 200 1512 "https://project.edu/" "Mozilla/4.0"
//host ident auth time method request_nk protocol status bytes ref browser
$log_pattern = '/^([^ ]+) ([^ ]+) ([^ ]+) ([[^]]+]) "(.*) (.*) (.*)" ([0-9-]+) ([0-9-]+) "(.*)" "(.*)"$/';
Propagación de claves
Cuando los datos en bruto están en la base, es necesario escribir en las tablas de dimensiones las claves que no están allí. Así será posible construir un vínculo a las dimensiones. Por ejemplo, en la tabla DIM_REFERRER, la clave es una combinación de tres campos.
Consulta SQL de propagación de claves
/* Propagate the referrer from access log */
INSERT INTO DIM_REFERRER (HOST_NK, PATH_NK, QUERY_NK, UPDATE_DT)
SELECT
CLS.HOST_NK,
CLS.PATH_NK,
CLS.QUERY_NK,
STRFTIME('%s','now') AS UPDATE_DT
FROM (
SELECT DISTINCT
REFERRER_HOST AS HOST_NK,
REFERRER_PATH AS PATH_NK,
CASE WHEN INSTR(REFERRER_QUERY,'&sid')>0 THEN SUBSTR(REFERRER_QUERY, 1, INSTR(REFERRER_QUERY,'&sid')-1) /* отрезаем sid - специфика цмс */
ELSE REFERRER_QUERY END AS QUERY_NK
FROM STG_ACCESS_LOG
) CLS
LEFT OUTER JOIN DIM_REFERRER TRG
ON (CLS.HOST_NK = TRG.HOST_NK AND CLS.PATH_NK = TRG.PATH_NK AND CLS.QUERY_NK = TRG.QUERY_NK)
WHERE TRG.DIM_REFERRER_ID IS NULLLa propagación a la tabla de agentes de usuario puede contener lógica de bots, por ejemplo, un fragmento de sql:
CASO
CUANDO INSTR(BAJO(CLS.BROWSER),'yandex.com')>0
ENTONCES 'yandex'
CUANDO INSTR(BAJO(CLS.BROWSER),'googlebot')>0
ENTONCES 'google'
CUANDO INSTR(BAJO(CLS.BROWSER),'bingbot')>0
ENTONCES 'microsoft'
CUANDO INSTR(BAJO(CLS.BROWSER),'ahrefsbot')>0
ENTONCES 'ahrefs'
CUANDO INSTR(BAJO(CLS.BROWSER),'mj12bot')>0
ENTONCES 'majestic-12'
CUANDO INSTR(BAJO(CLS.BROWSER),'compatible')>0 O INSTR(BAJO(CLS.BROWSER),'http')>0
O INSTR(BAJO(CLS.BROWSER),'libwww')>0 O INSTR(BAJO(CLS.BROWSER),'spider')>0
O INSTR(BAJO(CLS.BROWSER),'java')>0 O INSTR(BAJO(CLS.BROWSER),'python')>0
O INSTR(BAJO(CLS.BROWSER),'robot')>0 O INSTR(BAJO(CLS.BROWSER),'curl')>0
O INSTR(BAJO(CLS.BROWSER),'wget')>0
ENTONCES 'otro'
SINO 'n.a.' FIN COMO AGENT_BOTTablas de agregados
Por último, cargaremos las tablas de agregados, por ejemplo, la tabla diaria puede cargarse de la siguiente manera:
Consulta SQL para cargar el agregado
/* Load fact from access log */
INSERT INTO FCT_ACCESS_USER_AGENT_DD (EVENT_DT, DIM_USER_AGENT_ID, DIM_HTTP_STATUS_ID, PAGE_CNT, FILE_CNT, REQUEST_CNT, LINE_CNT, IP_CNT, BYTES)
WITH STG AS (
SELECT
STRFTIME( '%s', SUBSTR(TIME_NK,9,4) || '-' ||
CASE SUBSTR(TIME_NK,5,3)
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11'
ELSE '12' END || '-' || SUBSTR(TIME_NK,2,2) || ' 00:00:00' ) AS EVENT_DT,
BROWSER AS USER_AGENT_NK,
REQUEST_NK,
IP_NR,
STATUS,
LINE_NK,
BYTES
FROM STG_ACCESS_LOG
)
SELECT
CAST(STG.EVENT_DT AS INTEGER) AS EVENT_DT,
USG.DIM_USER_AGENT_ID,
HST.DIM_HTTP_STATUS_ID,
COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')=0 THEN STG.REQUEST_NK END) ) AS PAGE_CNT,
COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')>0 THEN STG.REQUEST_NK END) ) AS FILE_CNT,
COUNT(DISTINCT STG.REQUEST_NK) AS REQUEST_CNT,
COUNT(DISTINCT STG.LINE_NK) AS LINE_CNT,
COUNT(DISTINCT STG.IP_NR) AS IP_CNT,
SUM(BYTES) AS BYTES
FROM STG,
DIM_HTTP_STATUS HST,
DIM_USER_AGENT USG
WHERE STG.STATUS = HST.STATUS_NK
AND STG.USER_AGENT_NK = USG.USER_AGENT_NK
AND CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from /* load epoch date */
AND CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
GROUP BY STG.EVENT_DT, HST.DIM_HTTP_STATUS_ID, USG.DIM_USER_AGENT_IDLa base de datos sqlite permite escribir consultas complejas. WITH contiene la preparación de datos y claves. La consulta principal recopila todos los vínculos a las dimensiones.
La condición no permitirá cargar la historia nuevamente: CAST(STG.EVENT_DT COMO ENTERO) > $param_epoch_from, donde el parámetro es el resultado de la consulta
‘SELECCIONAR COALESCE(MAX(EVENT_DT), ‘3600’) COMO LAST_EVENT_EPOCH DESDE FCT_ACCESS_USER_AGENT_DD’
La condición solo cargará un día completo: CAST(STG.EVENT_DT COMO ENTERO) < strftime(‘%s’, fecha(‘ahora’, ‘inicio del día’))
El conteo de páginas o archivos se realiza de forma primitiva, buscando un punto.
Informes
En sistemas complejos de visualización, existe la posibilidad de crear un meta-modelo basado en objetos de base de datos, gestionando dinámicamente filtros y reglas de agregación. Al final, todas las herramientas decentes generan consultas SQL.
En este ejemplo, crearemos consultas SQL ya preparadas y las guardaremos como vistas en la base de datos; eso son los informes.
Visualización
Se utilizó Bluff: hermosos gráficos en JavaScript como herramienta de visualización.
Para esto, fue necesario recorrer todos los informes con PHP y generar un archivo HTML con tablas.
$sqls = array(
'SELECT * FROM RPT_ACCESS_USER_VS_BOT',
'SELECT * FROM RPT_ACCESS_ANNOYING_BOT',
'SELECT * FROM RPT_ACCESS_TOP_HOUR_HIT',
'SELECT * FROM RPT_ACCESS_USER_ACTIVE',
'SELECT * FROM RPT_ACCESS_REQUEST_STATUS',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_PAGE',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_REFERRER',
'SELECT * FROM RPT_ACCESS_NEW_REQUEST',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_SUCCESS',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_ERROR'
);La herramienta simplemente visualiza las tablas de resultados.
Salida
El artículo utiliza el análisis web como ejemplo para describir los mecanismos necesarios para construir almacenes de datos. Como se puede ver en los resultados, para un análisis profundo y visualización de datos se pueden utilizar herramientas muy simples.
En adelante, usando este almacenamiento como ejemplo, intentaremos implementar estructuras como dimensiones que cambian lentamente, metadatos, niveles de agregación e integración de datos de diferentes fuentes.
Además, examinaremos con más detalle una herramienta básica para la gestión de procesos ETL basada en una sola tabla.
Regresaremos al tema de la medición de la calidad de los datos y a la automatización de este proceso.
Estudiaremos los problemas del entorno técnico y el mantenimiento de los almacenes de datos, para ello implementaremos un servidor de almacenamiento con recursos mínimos, por ejemplo, basado en Raspberry Pi.
Fuente: habr.com
