Ao analisar as estatísticas do site, temos uma idéia do que está acontecendo com ele. Comparamos os resultados com outros conhecimentos sobre o produto ou serviço e, assim, melhoramos nossa experiência.
Quando a análise dos primeiros resultados é concluída, as informações foram compreendidas e as conclusões são tiradas, a próxima etapa começa. Surgem ideias: o que acontecerá se você olhar os dados de outra perspectiva?
Neste ponto, existem limitações para as ferramentas de análise. Essa é uma das razões pelas quais a ferramenta do Google Analytics não foi suficiente para mim, ou seja, devido à capacidade limitada de ver e manipular meus dados.
Eu sempre quis carregar rapidamente os dados básicos (dados mestre), adicionar outro nível de agregação ou interpretar os valores existentes.
Isso é fácil de fazer em
seu pequeno repositório com base no arquivo access.log e a linguagem SQL é suficiente para isso.
Então, para quais perguntas eu gostaria de encontrar a resposta?
O que e quando mudou no site
O histórico de alterações nos dados subjacentes (dados mestre) é sempre de interesse.
Solicitação de relatório SQLSELECT 1 as 'SideStackedBar: Content Updates by Months', strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Day', COUNT(CASE WHEN PAGE_TITLE != 'na' THEN DIM_REQUEST_ID END) AS 'Web page updates', COUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Image uploads', COUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video uploads', COUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audio uploads' FROM DIM_REQUEST WHERE PAGE_TITLE != 'na' OR PAGE_DESCR != 'na' GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) ORDER BY UPDATE_DT
Por exemplo, em algum momento, foi realizada a otimização do mecanismo de pesquisa ou foi adicionado novo conteúdo ao site. Nesse sentido, é esperado um aumento no tráfego.
Grupos de usuários
O exemplo mais simples de um grupo é um agente de usuário ou o nome de um sistema operacional.
A dimensão do agente do usuário acumulou cerca de mil registros e fiquei interessado em ver a dinâmica da distribuição de agentes dentro do grupo.
Solicitação de relatório SQL SELECT 1 AS 'SideStackedBar: User Agents', AGENT_OS AS 'OS', SUM(CASE WHEN AGENT_BOT = 'na' THEN 1 ELSE 0 END ) AS 'User Agent of Users', SUM(CASE WHEN AGENT_BOT != 'na' THEN 1 ELSE 0 END ) AS 'User Agent of Bots' FROM DIM_USER_AGENT WHERE DIM_USER_AGENT_ID != -1 GROUP BY AGENT_OS ORDER BY 3 DESC
A maioria das várias combinações de agentes chega ao site no mundo Windows. Entre os incertos estavam o WhatsApp, PocketImageCache, PlayStation, SmartTV, etc.
Atividade semanal do grupo de usuários
Ao combinar alguns grupos, podemos observar a distribuição de suas atividades.
Por exemplo, os usuários de um cluster Linux consomem mais tráfego em um site do que todos os outros.
Solicitação de relatório SQL SELECT 1 as 'StackedBar: Traffic Volume by User OS and by Week', strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week', SUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Android/Linux Users', SUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Windows Users', SUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Mac/iOS Users', SUM(CASE WHEN USG.AGENT_OS IN ('na', 'BlackBerry') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Other' 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
Consumo de tráfego pesado
A tabela mostra os grupos de usuários mais ativos e o dia de sua atividade.
Os mais ativos pertencem ao cluster Linux.
Solicitação de relatório SQL SELECT 1 AS 'Table: User Agent with Havy Usage', strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', ROUND(1.0*SUM(FCT.BYTES)/1000000, 1) AS 'Traffic MB', ROUND(1.0*SUM(FCT.IP_CNT)/SUM(1), 1) AS 'IPs', ROUND(1.0*SUM(FCT.REQUEST_CNT)/SUM(1), 1) AS 'Requests', USA.DIM_USER_AGENT_ID AS 'ID', MAX(USA.USER_AGENT_NK) AS 'User Agent', MAX(USA.AGENT_BOT) AS 'Bot' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_USER_AGENT USA WHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 day') GROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT LIMIT 10
Usando os atributos day e ID do agente, você pode localizar e rastrear estatísticas rapidamente nos dias de grupos de usuários individuais. Se necessário, você pode encontrar rapidamente informações detalhadas na tabela de etapas.
Como obter informações?
As informações do arquivo access.log podem ser ainda mais eficazes, integrando fontes de dados adicionais e introduzindo novos níveis de agregação e agrupamento.
Dados e entidades básicos
Os dados básicos incluem informações sobre entidades: páginas da web, imagens, conteúdo de vídeo e áudio, no caso de uma loja, produtos.
As próprias entidades desempenham o papel de dimensões, e o processo de salvar alterações de atributo é chamado historicização. Em um banco de dados, esse processo é frequentemente implementado na forma de dimensões que mudam lentamente (SCD).
Uma variedade de sistemas pode ser a fonte de dados básicos, portanto quase sempre precisam ser integrados.
Mudando lentamente a dimensão
A dimensão DIM_REQUEST conterá informações sobre consultas no site na forma histórica.
Tabela SCD2 CREATE TABLE DIM_REQUEST ( DIM_REQUEST_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1, REQUEST_NK TEXT NOT NULL DEFAULT 'na', PAGE_TITLE TEXT NOT NULL DEFAULT 'na', PAGE_DESCR TEXT NOT NULL DEFAULT 'na', PAGE_KEYWORDS TEXT NOT NULL DEFAULT 'na', DELETE_FLAG INTEGER NOT NULL DEFAULT 0, UPDATE_DT INTEGER NOT NULL DEFAULT 0, UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST) ); INSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);
Além disso, crie uma exibição que sempre exiba todos os registros no último estado. É necessário carregar a própria medição.
Visão atual do SCD2 SELECT HI.DIM_REQUEST_ID, HI.DIM_REQUEST_ID_HIST, HI.REQUEST_NK, HI.PAGE_TITLE, HI.PAGE_DESCR, HI.PAGE_KEYWORDS, NK.CNT AS HIST_CNT, HI.DELETE_FLAG, strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT FROM ( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT FROM DIM_REQUEST GROUP BY REQUEST_NK ) NK, DIM_REQUEST HI WHERE 1 = 1 AND NK.REQUEST_NK = HI.REQUEST_NK AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;
E uma visão de onde as informações históricas são coletadas para cada registro. É necessário construir uma conexão historicamente correta com os fatos.
Visão histórica do SCD2 SELECT SCD.DIM_REQUEST_ID, SCD.DIM_REQUEST_ID_HIST, SCD.REQUEST_NK, SCD.PAGE_TITLE, SCD.PAGE_DESCR, SCD.PAGE_KEYWORDS, SCD.DELETE_FLAG, CASE WHEN HIS.UPDATE_DT IS NULL THEN 1 ELSE 0 END ACTIVE_FLAG, SCD.DIM_REQUEST_ID_HIST AS ID_FROM, SCD.DIM_REQUEST_ID AS ID_TO, CASE WHEN SCD.DIM_REQUEST_ID_HIST=-1 THEN 3600 ELSE IFNULL(SCD.UPDATE_DT,3600) END AS TIME_FROM, CASE WHEN HIS.UPDATE_DT IS NULL THEN 253370764800 ELSE HIS.UPDATE_DT END AS TIME_TO, CASE WHEN SCD.DIM_REQUEST_ID_HIST=-1 THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch')) ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch')) END AS ACTIVE_FROM, CASE WHEN HIS.UPDATE_DT IS NULL THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch')) ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch')) END AS ACTIVE_TO FROM DIM_REQUEST SCD LEFT OUTER JOIN DIM_REQUEST HIS ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;
Agregação de dados
A compactação (agregação) permite avaliar dados em um nível mais alto e detectar anomalias e tendências que não são visíveis em relatórios detalhados.
Por exemplo, na dimensão com códigos de status da solicitação DIM_HTTP_STATUS, adicione o grupo:
ESTADO / GRUPO
0xx / na
1xx / Informativo
2xx / Bem sucedido
3xx / redirecionamento
4xx / erro do cliente
5xx / Erro no servidor
A dimensão do agente do usuário DIM_USER_AGENT conterá os atributos AGENT_OS e AGENT_BOT para os grupos. Eles podem ser preenchidos durante o processo ETL:
Faça o download de DIM_USER_AGENT INSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT) WITH CLS AS ( SELECT BROWSER FROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1 GROUP BY BROWSER ) SELECT CLS.BROWSER AS USER_AGENT_NK, CASE WHEN INSTR(CLS.BROWSER,'Macintosh')>0 THEN 'Macintosh' WHEN INSTR(CLS.BROWSER,'iPhone')>0 OR INSTR(CLS.BROWSER,'iPad')>0 OR INSTR(CLS.BROWSER,'iPod')>0 OR INSTR(CLS.BROWSER,'Apple TV')>0 OR INSTR(CLS.BROWSER,'Darwin')>0 THEN 'iOS' WHEN INSTR(CLS.BROWSER,'Android')>0 THEN 'Android' WHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0 THEN 'Linux' WHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0 THEN 'BlackBerry' WHEN INSTR(CLS.BROWSER,'Windows')>0 THEN 'Windows' ELSE 'na' END AS AGENT_OS,
Integração de dados
Inclui a organização da transferência de dados do sistema operacional para o sistema de relatórios. Para fazer isso, crie uma tabela de estágio com uma estrutura semelhante à origem.
As informações sobre páginas da Web chegam ao estágio a partir do backup do CMS na forma de solicitações de inserção.
O carregamento da tabela DIM_REQUEST histórica com dados básicos leva três etapas: carregando novas chaves e atributos, atualizando as existentes e corrigindo registros excluídos.
Baixe novas entradas do SCD2 INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Atualizar atributos do SCD2 INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Entradas de SCD2 excluídas INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Cada fonte de dados deve ser acompanhada de uma descrição formal, por exemplo, no arquivo readme.txt:
Destinatário formal / tecnicamente: nome, endereço de email
Fornecedor de dados formal / tecnicamente: nome, email
Fonte de dados: caminho do arquivo, nomes de serviço
Informações de acesso a dados: usuários e senhas
O esquema de movimentação de dados ajudará no processo de manutenção e atualização, por exemplo, em forma de texto:
Movendo um arquivo. Origem: ftp.domain.net: /logs/access.log Destino: /var/www/access.log
Leitura no palco. Alvo: STG_ACCESS_LOG
Download e transformação. Alvo: FCT_ACCESS_REQUEST_REF_HH
Download e transformação. Alvo: FCT_ACCESS_USER_AGENT_DD
Relatório. Segmentação: /var/www/report.html
Conclusão
Assim, o artigo descreve mecanismos como a integração de dados básicos e a introdução de novos níveis de agregação. Eles são necessários na construção de data warehouses para obter conhecimento adicional e melhorar a qualidade das informações.