En analysant les statistiques du site, nous avons une idée de ce qui se passe avec lui. Nous comparons les résultats avec d'autres connaissances sur le produit ou le service et améliorons ainsi notre expérience.
Lorsque l'analyse des premiers résultats est terminée, les informations ont été comprises et des conclusions sont tirées, l'étape suivante commence. Des idées surgissent: que se passera-t-il si vous regardez les données sous un autre angle?
à ce stade, il existe des limites aux outils d'analyse. C'est l'une des raisons pour lesquelles l'outil Google Analytics ne me suffisait pas, notamment en raison de la capacité limitée à voir et à manipuler mes données.
J'ai toujours voulu charger rapidement les données de base (données de base), ajouter un autre niveau d'agrégation ou interpréter d'une autre maniÚre les valeurs existantes.
C'est facile Ă faire dans
votre petit référentiel basé sur le fichier access.log et le langage SQL est suffisant pour cela.
Alors, à quelles questions voulais-je trouver la réponse?
Quoi et quand a changé sur le site
L'historique des modifications des données sous-jacentes (données de base) est toujours intéressant.
Demande de rapport 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
Par exemple, à un moment donné, l'optimisation des moteurs de recherche a été effectuée ou de nouveaux contenus ont été ajoutés au site, à cet égard, une augmentation du trafic est attendue.
Groupes d'utilisateurs
L'exemple le plus simple d'un groupe est un agent utilisateur ou le nom d'un systĂšme d'exploitation.
La dimension d'agent utilisateur a accumulé environ un millier d'enregistrements, et j'ai été intéressé par la dynamique de la répartition des agents au sein du groupe.
Demande de rapport 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
La plupart des diverses combinaisons d'agents proviennent du monde Windows. Parmi les plus incertains, il y avait WhatsApp, PocketImageCache, PlayStation, SmartTV, etc.
Activité hebdomadaire du groupe d'utilisateurs
En combinant certains groupes, nous pouvons observer la répartition de leur activité.
Par exemple, les utilisateurs d'un cluster Linux consomment plus de trafic sur un site que tout le monde.
Demande de rapport 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
Forte consommation de trafic
Le tableau présente les groupes d'utilisateurs les plus actifs et le jour de leur activité.
Les plus actifs appartiennent au cluster Linux.
Demande de rapport 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
En utilisant le jour des attributs et l'ID de l'agent, vous pouvez trouver et suivre rapidement des statistiques sur les jours des groupes d'utilisateurs individuels. Si nécessaire, vous pouvez trouver rapidement des informations détaillées dans le tableau des étapes.
Comment obtenir des informations?
Les informations du fichier access.log peuvent ĂȘtre encore plus efficaces en intĂ©grant des sources de donnĂ©es supplĂ©mentaires et en introduisant de nouveaux niveaux d'agrĂ©gation et de regroupement.
Données et entités de base
Les données de base comprennent des informations sur les entités: pages Web, images, contenus vidéo et audio, dans le cas d'un magasin, produits.
Les entitĂ©s elles-mĂȘmes jouent le rĂŽle de dimensions, et le processus d'enregistrement des modifications d'attributs s'appelle l'historisation. Dans une base de donnĂ©es, ce processus est souvent mis en Ćuvre sous la forme de dimensions Ă Ă©volution lente (SCD).
Une variĂ©tĂ© de systĂšmes peuvent ĂȘtre la source de donnĂ©es de base, ils doivent donc presque toujours ĂȘtre intĂ©grĂ©s.
Dimension changeant lentement
La dimension DIM_REQUEST contiendra des informations sur les requĂȘtes sur le site sous forme historique.
Table 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);
En plus de cela, crĂ©ez une vue qui affiche toujours tous les enregistrements dans le dernier Ă©tat. Il est nĂ©cessaire de charger la mesure elle-mĂȘme.
Vue actuelle de 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;
Et une vue de l'endroit oĂč les informations historiques sont collectĂ©es pour chaque enregistrement. Il est nĂ©cessaire de construire un lien historiquement correct avec les faits.
Vue historique de 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;
Agrégation de données
La compression (agrégation) vous permet d'évaluer les données à un niveau supérieur et de détecter les anomalies et les tendances qui ne sont pas visibles dans les rapports détaillés.
Par exemple, dans la dimension avec les codes d'Ă©tat de demande DIM_HTTP_STATUS, ajoutez le groupe:
STATUT / GROUPE
0xx / na
1xx / informationnel
2xx / réussi
3xx / redirection
Erreur 4xx / client
Erreur 5xx / serveur
La dimension d'agent utilisateur DIM_USER_AGENT contiendra les attributs AGENT_OS et AGENT_BOT pour les groupes. Ils peuvent ĂȘtre renseignĂ©s au cours du processus ETL:
Télécharger 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,
Intégration de données
Il comprend l'organisation du transfert de données du systÚme d'exploitation vers le systÚme de reporting. Pour ce faire, créez une table de scÚne avec une structure similaire à la source.
Les informations sur les pages Web arrivent Ă l'Ă©tape de la sauvegarde CMS sous la forme de demandes d'insertion.
Le chargement de la table historique DIM_REQUEST avec les données de base prend trois étapes: le chargement de nouvelles clés et de nouveaux attributs, la mise à jour de ceux existants et la correction des enregistrements supprimés.
Télécharger de nouvelles entrées SCD2 INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Mettre Ă jour les attributs SCD2 INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Entrées SCD2 supprimées INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Chaque source de donnĂ©es doit ĂȘtre accompagnĂ©e d'une description formelle, par exemple, dans le fichier readme.txt:
Destinataire formellement / techniquement: nom, adresse e-mail
Fournisseur de données formellement / techniquement: nom, e-mail
Source de données: chemin d'accÚs au fichier, noms de service
Informations d'accÚs aux données: utilisateurs et mots de passe
Le schéma de déplacement des données facilitera le processus de maintenance et de mise à jour, par exemple sous forme de texte:
DĂ©placer un fichier. Source: ftp.domain.net: /logs/access.log Cible: /var/www/access.log
Lecture en scĂšne. Cible: STG_ACCESS_LOG
Téléchargement et transformation. Cible: FCT_ACCESS_REQUEST_REF_HH
Téléchargement et transformation. Cible: FCT_ACCESS_USER_AGENT_DD
Rapport. Cible: /var/www/report.html
Conclusion
Ainsi, l'article décrit des mécanismes tels que l'intégration de données de base et l'introduction de nouveaux niveaux d'agrégation. Ils sont nécessaires lors de la construction d'entrepÎts de données afin d'obtenir des connaissances supplémentaires et d'améliorer la qualité des informations.