O utilitário Webalizer e a ferramenta Google Analytics me ajudaram por muitos anos a ter uma idéia do que está acontecendo nos sites. Agora entendo que eles fornecem muito pouca informação útil. Ter acesso ao seu arquivo access.log, lidar com estatísticas é muito simples e para a implementação de ferramentas bastante básicas, como sqlite, html, sql e qualquer linguagem de programação de script.
A fonte de dados para o Webalizer é o arquivo access.log do servidor. É assim que suas colunas e números são exibidos, dos quais apenas a quantidade total de tráfego é clara:
Ferramentas como o Google Analytics coletam dados da página carregada por conta própria. Eles nos mostram alguns diagramas e linhas, com base nos quais muitas vezes é difícil tirar as conclusões corretas. Talvez fosse necessário mais esforço? Eu não sei
Então, o que eu queria ver nas estatísticas das visitas ao site?
Tráfego de usuários e bot
Muitas vezes, o tráfego do site tem um limite e você precisa ver quanto tráfego útil é usado. Por exemplo, assim:
Solicitação de relatório SQLSELECT 1 as 'StackedArea: Traffic generated by Users and Bots', strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', SUM(CASE WHEN USG.AGENT_BOT!='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB', SUM(CASE WHEN USG.AGENT_BOT='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Users, 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_DT
No gráfico, você pode ver a atividade constante dos bots. Seria interessante estudar em detalhes os representantes mais ativos.
Bots irritantes
Classificamos os bots com base nas informações do agente do usuário. Estatísticas adicionais sobre o tráfego diário, o número de solicitações bem-sucedidas e malsucedidas fornecem uma boa idéia sobre a atividade dos bots.
Solicitação de relatório SQL SELECT 1 AS 'Table: Annoying Bots', MAX(USG.AGENT_BOT) AS 'Bot', ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day', ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day', ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Error Requests per Day', ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Success Requests per Day', USG.USER_AGENT_NK AS 'Agent' 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 != 'na' AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY USG.USER_AGENT_NK ORDER BY 3 DESC LIMIT 10
Nesse caso, a análise resultou na decisão de restringir o acesso ao site adicionando robots.txt ao arquivo
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5Os dois primeiros bots desapareceram da mesa e os robôs da MS desceram das primeiras linhas.
Dia e hora da maior atividade
O tráfego aparece. Para estudá-los detalhadamente, é necessário identificar o horário de sua ocorrência, enquanto não é necessário exibir todas as horas e dias de medição do tempo. Portanto, será mais fácil encontrar consultas individuais no arquivo de log, se você precisar de uma análise detalhada.
Solicitação de relatório SQL SELECT 1 AS 'Line: Day and Hour of Hits from Users and Bots', strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Date Time', HIB AS 'Bots, Hits', HIU AS 'Users, Hits' FROM ( SELECT EVENT_DT, SUM(CASE WHEN AGENT_BOT!='na' THEN LINE_CNT ELSE 0 END) AS HIB, SUM(CASE WHEN AGENT_BOT='na' 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_DT
Observamos as horas mais ativas 11, 14 e 20 do primeiro dia no gráfico. Mas no dia seguinte às 13 horas os bots estavam ativos.
Atividade semanal média diária do usuário
Com atividade e tráfego um pouco descoberto. A próxima pergunta foi a atividade dos próprios usuários. Para essas estatísticas, grandes períodos de agregação, por exemplo, uma semana, são desejáveis.
Solicitação de relatório SQL SELECT 1 as 'Line: Average Daily User Activity by Week', strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week', ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pages per IP per Day', ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Files per IP per Day' 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='na' AND HST.STATUS_GROUP IN ('Successful') AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month') GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY FCT.EVENT_DT
As estatísticas da semana mostram que, em média, um usuário abre 1,6 páginas por dia. O número de arquivos solicitados por usuário, neste caso, depende da adição de novos arquivos ao site.
Todas as solicitações e seus status
O Webalizer sempre mostrava códigos de página específicos e sempre desejava ver apenas o número de solicitações e erros bem-sucedidos.
Solicitação de relatório SQL SELECT 1 as 'Line: All Requests by Status', strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Success', SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Redirect', SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Customer Error', SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Server Error' 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_DT
O relatório exibe solicitações, e não cliques (ocorrências), ao contrário de LINE_CNT, a métrica REQUEST_CNT é considerada COUNT (DISTINCT STG.REQUEST_NK). O objetivo é mostrar eventos eficazes, por exemplo, o MS bots centenas de vezes por dia pesquisando um arquivo robots.txt e, nesse caso, essas pesquisas serão contadas uma vez. Isso permite suavizar os saltos no gráfico.
No gráfico, você pode ver muitos erros - essas são páginas inexistentes. O resultado da análise foi a adição de redirecionamentos de páginas remotas.
Pedidos errados
Para uma revisão detalhada das solicitações, você pode exibir estatísticas detalhadas.
Solicitação de relatório SQL SELECT 1 AS 'Table: Top Error Requests', REQ.REQUEST_NK AS 'Request', 'Error' AS 'Request Status', ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits per Day', ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day', ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day' 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 ('Client Error', 'Server Error') AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY REQ.REQUEST_NK ORDER BY 4 DESC LIMIT 20
Esta lista conterá todos os discadores, por exemplo, uma solicitação para /wp-login.php. Ao ajustar as regras para reescrever solicitações pelo servidor, você pode ajustar a resposta do servidor a essas solicitações e enviá-las para a página inicial.
Portanto, alguns relatórios simples baseados no arquivo de log do servidor fornecem uma imagem bastante completa do que está acontecendo no site.
Como obter informações?
O banco de dados sqlite é suficiente. Vamos criar tabelas: auxiliares para o registro de processos ETL.
Tabela de estágio, onde escreveremos arquivos de log usando PHP. Duas tabelas agregadas. Crie uma tabela diária com estatísticas sobre agentes do usuário e solicite status. Por hora, com estatísticas sobre solicitações, grupos de status e agentes. Quatro tabelas de medidas relevantes.
O resultado é o seguinte modelo relacional:
Script para criar um objeto no banco de dados sqlite:
Criação de objeto DDL DROP TABLE IF EXISTS DIM_USER_AGENT; CREATE TABLE DIM_USER_AGENT ( DIM_USER_AGENT_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, USER_AGENT_NK TEXT NOT NULL DEFAULT 'na', AGENT_OS TEXT NOT NULL DEFAULT 'na', AGENT_ENGINE TEXT NOT NULL DEFAULT 'na', AGENT_DEVICE TEXT NOT NULL DEFAULT 'na', AGENT_BOT TEXT NOT NULL DEFAULT 'na', UPDATE_DT INTEGER NOT NULL DEFAULT 0, UNIQUE (USER_AGENT_NK) ); INSERT INTO DIM_USER_AGENT (DIM_USER_AGENT_ID) VALUES (-1);
Estágio
No caso do arquivo access.log, você precisa ler, analisar e gravar todas as solicitações no banco de dados. Isso pode ser feito diretamente usando a linguagem de script ou usando o sqlite.
Formato do arquivo de log:
Key Propaganda
Quando os dados brutos estão no banco de dados, é necessário registrar as chaves que não existem nas tabelas de medidas. Então será possível construir uma referência para as medições. Por exemplo, na tabela DIM_REFERRER, a chave é uma combinação de três campos.
Consulta de propagação de chave SQL 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) 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 NULL
A propagação para a tabela de agente do usuário pode conter lógica bot, por exemplo, um trecho sql:
CASE WHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0 THEN 'yandex' WHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0 THEN 'google' WHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0 THEN 'microsoft' WHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0 THEN 'ahrefs' WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0 THEN 'majestic-12' WHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0 OR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0 OR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0 OR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0 OR INSTR(LOWER(CLS.BROWSER),'wget')>0 THEN 'other' ELSE 'na' END AS AGENT_BOT
Tabelas de unidades
Por fim, carregaremos tabelas agregadas, por exemplo, uma tabela diária pode ser carregada da seguinte maneira:
Solicitação de carregamento agregado do SQL 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 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_ID
O banco de dados sqlite permite escrever consultas complexas. WITH contém a preparação de dados e chaves. A consulta principal coleta todas as referências às dimensões.
A condição não permitirá o carregamento da matéria novamente: CAST (STG.EVENT_DT AS INTEGER)> $ param_epoch_from, em que o parâmetro é o resultado da solicitação
'SELECT COALESCE (MAX (EVENT_DT), \' 3600 \ ') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
A condição será carregada apenas o dia inteiro: CAST (STG.EVENT_DT AS INTEGER) <strftime ('% s', date ('now', 'start of day'))
A contagem de páginas ou arquivos é feita de maneira primitiva, procurando um ponto.
Relatórios
Em sistemas complexos de visualização, é possível criar um metamodelo baseado em objetos de banco de dados, gerenciar dinamicamente filtros e regras de agregação. Por fim, todas as ferramentas decentes geram uma consulta SQL.
Neste exemplo, criaremos consultas SQL prontas e as salvaremos como uma exibição no banco de dados - esses são os relatórios.
Visualização
Blefe: Belos gráficos em JavaScript foram usados como uma ferramenta de visualização.
Para isso, foi necessário usar o PHP para revisar todos os relatórios e gerar um arquivo html com tabelas.
$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' );
A ferramenta simplesmente visualiza as tabelas de resultados.
Conclusão
Usando a análise da Web como exemplo, o artigo descreve os mecanismos necessários para a construção de data warehouses. Como pode ser visto nos resultados, as ferramentas mais simples são suficientes para análise aprofundada e visualização de dados.
No futuro, usando o exemplo desse armazenamento, tentaremos implementar estruturas como
medições que mudam lentamente , dados mestre, níveis de agregação e integração de dados de diferentes fontes.
Além disso, examinaremos mais de perto a
ferramenta mais simples de
gerenciamento de processos ETL com base em uma única tabela.
Voltemos ao tópico de medir a qualidade dos dados e automatizar esse processo.
Estudaremos os problemas do ambiente técnico e da manutenção de data warehouses, para os quais implementamos um servidor de armazenamento com recursos mínimos, por exemplo, com base no Raspberry Pi.