🥇Statistik för webbplatsen och mitt lilla datalager

Verktyget Webalizer och Google Analytics har hjälpt mig i många år att få en uppfattning om vad som händer på webbplatser. Nu inser jag att de ger mycket lite användbar information. Med tillgång till min access.log-fil är det mycket enkelt att förstå statistiken, och för att genomföra detta behövs bara grundläggande verktyg som sqlite, html, sql-språk och vilket skriptspråk som helst.

Datakällan för Webalizer är access.log-filen servern. Så här ser dess kolumner och siffror ut, som bara ger en översikt över den totala trafiken:

Sådana verktyg som Google Analytics samlar in data från den laddade sidan på egen hand. De visar oss några diagram och linjer, utifrån vilka det ofta är svårt att dra korrekta slutsatser. Kanske borde ytterligare insatser ha gjorts? Jag vet inte.

Så vad ville jag se i webbplatsens besöksstatistik?

Användartjänsternas och botternas trafik

Ofta har webbplatser trafikbegränsningar och det är nödvändigt att se hur mycket användbar trafik som utnyttjas. Till exempel så här:

SQL-frågan för rapporten

SELECT
1 AS 'StackedArea: Trafik genererad av Användare och Bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',
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 'Användare, 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

Grafen visar konstant aktivitet från bots. Det skulle vara intressant att noggrant studera de mest aktiva representanterna.

Påträngande bots

Vi klassificerar bots baserat på information från användaragenten. Ytterligare statistik över daglig trafik, antal lyckade och misslyckade förfrågningar ger en bra känsla för botarnas aktivitet.

SQL-frågan för rapporten

SELECT 
1 AS 'Tabell: Påträngande Bots',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) AS 'KB per Dag',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Klientfel', 'Serverfel') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Fel förfrågningar per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Lyckad', 'Omdirigering') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Lyckade förfrågningar per Dag',
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 != 'n.a.'
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10

I detta fall resulterade analysen i ett beslut att begränsa åtkomsten till webbplatsen genom att lägga till det i filen robots.txt

Användaragent: AhrefsBot Som inte tillåts: / Användaragent: dotbot Som inte tillåts: / Användaragent: bingbot Kräv fördröjning: 5
De två första botarna försvann från tabellen, medan MS-botarna flyttades neråt.

Tidpunkt för högst aktivitet

I trafiken syns toppar. För att detaljstudera dem är det nödvändigt att särskilja tiden för deras uppkomst, samtidigt som det inte är nödvändigt att visa alla timmar och dagar för tidsmätningen. Det blir enklare att hitta specifika förfrågningar i loggfilen vid behov av detaljerad analys.

SQL-frågan för rapporten

SELECT
1 AS 'Rad: Dag och Timme för Besök från Användare och Botar',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Datum Tid',
HIB AS 'Botar, Besök',
HIU AS 'Användare, Besök'
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_DT

Vi observerar de mest aktiva timmarna 11, 14 och 20 den första dagen på grafen. Nästa dag vid 13-tiden var botarna aktiva.

Genomsnittlig daglig aktivitet för användare per vecka

Vi har fått grepp om aktiviteten och trafiken. Nästa fråga gällde aktivitet hos användarna själva. För sådan statistik är längres perioder av aggregering önskvärda, exempelvis en vecka.

SQL-frågan för rapporten

SELECT
1 as 'Rad: Genomsnittlig Daglig Användaraktivitet per Vecka',
strftime('%W vecka', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Vecka',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Sidor per IP per Dag',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Filer per IP per Dag'
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.' /* endast användare */
  AND HST.STATUS_GROUP IN ('Successful') /* bra sidor */
  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W vecka', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Statistik för veckan visar att i genomsnitt öppnar en användare 1,6 sidor per dag. Antalet begärda filer per användare beror i detta fall på tillägg av nya filer på webbplatsen.

Alla förfrågningar och deras statusar

Webalizer visade alltid specifika koder för sidor och det var alltid önskvärt att bara se mängden lyckade förfrågningar och fel.

SQL-frågan för rapporten

VÄLJ
1 som 'Rad: Alla förfrågningar efter status',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) SOM 'Dag',
SUM(CASE NÄR STS.STATUS_GROUP='Framgångsrik' DÅ FCT.REQUEST_CNT ANNARS 0 SLUT) SOM 'Framgång',
SUM(CASE NÄR STS.STATUS_GROUP='Omdirigering' DÅ FCT.REQUEST_CNT ANNARS 0 SLUT) SOM 'Omdirigera',
SUM(CASE NÄR STS.STATUS_GROUP='Klientfel' DÅ FCT.REQUEST_CNT ANNARS 0 SLUT) SOM 'Klientfel',
SUM(CASE NÄR STS.STATUS_GROUP='Serverfel' DÅ FCT.REQUEST_CNT ANNARS 0 SLUT) SOM 'Serverfel'
FRÅN
  FCT_ACCESS_USER_AGENT_DD FCT,
  DIM_HTTP_STATUS STS
VARFÖR FCT.DIM_HTTP_STATUS_ID=STS.DIM_HTTP_STATUS_ID
  OCH datetime(FCT.EVENT_DT, 'unixepoch') >= date('nu', '-14 dagar')
GRUPPERA EFTER strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDNA EFTER FCT.EVENT_DT

Rapporten visar förfrågningar, inte klick (träffar), till skillnad från LINE_CNT mäts REQUEST_CNT som COUNT(DISTINCT STG.REQUEST_NK). Målet är att visa effektiva händelser, till exempel, MS-botar som flera gånger per dag frågar filen robots.txt och i det här fallet kommer sådana frågor att räknas som en gång. Detta hjälper till att jämna ut toppar på grafen.

Från grafen kan man se många fel — det är icke-existerande sidor. Resultatet av analysen var att lägga till omdirigeringar från borttagna sidor.

Felaktiga förfrågningar

För att noggrant granska förfrågningarna kan detaljerad statistik visas.

SQL-frågan för rapporten

VÄLJ
  1 SOM 'Tabell: Topp-fel-förfrågningar',
  REQ.REQUEST_NK SOM 'Förfrågan',
  'Fel' SOM 'Förfrågningsstatus',
  AVGÄR(SUM(FCT.LINE_CNT) / 14.0, 1) SOM 'Träffar per dag',
  AVGÄR(SUM(FCT.IP_CNT) / 14.0, 1) SOM 'IP-adresser per dag',
  AVGÄR(SUM(FCT.BYTES)/1000 / 14.0, 1) SOM 'KB per dag'
FRÅN
  FCT_ACCESS_REQUEST_REF_HH FCT,
  DIM_REQUEST_V_ACT REQ
VARFÖR FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID
  OCH FCT.STATUS_GROUP I ('Klientfel', 'Serverfel')
  OCH datetime(FCT.EVENT_DT, 'unixepoch') >= date('nu', '-14 dagar')
GRUPPERA EFTER REQ.REQUEST_NK
ORDNA EFTER 4 DESC
BEGRÄNSA 20

I den här listan kommer också alla samtal att finnas, till exempel, förfrågan till /wp-login.php Genom att justera reglerna för omskrivning av förfrågningar servern kan vi justera serverns respons på sådana förfrågningar och skicka dem till startsidan.

Så, några enkla rapporter baserade på serverloggfilen ger en tillräckligt fullständig bild av vad som händer på webbplatsen.

Hur får man information?

SQLite-databaser är helt tillräckliga. Vi skapar tabeller: en hjälptabell för loggning av ETL-processer.

Staging-tabell där vi kommer att skriva loggfiler med hjälp av PHP. Två aggregator-tabeller. Vi skapar en daglig tabell med statistik över användaragenter och förfrågningsstatusar. En timmevis med statistik över förfrågningar, statusgrupper och agenter. Fyra tabeller för de relevanta mätningarna.

Som ett resultat blev den följande relationella modellen:

Datamodell

Script för att skapa objekt i SQLite-databasen:

DDL skapa objekt

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 'n.a.',
  AGENT_OS          TEXT NOT NULL DEFAULT 'n.a.',
  AGENT_ENGINE      TEXT NOT NULL DEFAULT 'n.a.',
  AGENT_DEVICE      TEXT NOT NULL DEFAULT 'n.a.',
  AGENT_BOT         TEXT NOT NULL DEFAULT 'n.a.',
  UPDATE_DT         INTEGER NOT NULL DEFAULT 0,
  UNIQUE (USER_AGENT_NK)
);
INSERT INTO DIM_USER_AGENT (DIM_USER_AGENT_ID) VALUES (-1);

Staging

För access.log-filen måste alla förfrågningar läsas, parsa och skrivas till databasen. Detta kan göras antingen direkt med skriptspråket eller med hjälp av sqlite.

Loggfilsformat:

//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-]+) "(.*)" "(.*)"$/';

Nyckelpådragning

När rådata finns i databasen måste nycklarna för dimensionerna, som inte finns där, skrivas till tabellerna. Då blir det möjligt att bygga kopplingar till dimensionerna. Till exempel är nyckeln i tabellen DIM_REFERRER en kombination av tre fält.

SQL-fråga för nyckelpådragning

/* 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 NULL

Nyckelpådragning i tabellen för användaragenter kan innehålla bot-logik, till exempel en SQL-snutt:


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 'n.a.' END AS AGENT_BOT

Aggregattabeller

Sist men inte minst ska vi ladda aggregattabeller, till exempel kan den dagliga tabellen laddas på följande sätt:

SQL-fråga för laddning av aggregat

/* 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_ID

SQLite-databasen tillåter skrivning av komplexa frågor. WITH innehåller förberedelse av data och nycklar. Huvudfrågan samlar alla kopplingar till dimensionerna.

Villkoret hindrar att historik laddas in igen: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, där parametern är resultatet av frågan
‘SELECT COALESCE(MAX(EVENT_DT), ‘3600’) AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD’

Villkoret laddar endast hela dagar: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, date(‘now’, ‘start of day’))

Antal sidor eller filer räknas på ett primitivt sätt genom att söka efter punkter.

Rapporter

I komplexa visualiseringssystem finns möjlighet att skapa en metamodell baserad på databasen, dynamiskt hantera filter och aggregeringsregler. I slutändan genererar alla seriösa verktyg SQL-frågor.

I detta exempel kommer vi att skapa färdiga SQL-frågor och spara dem som vyer i databasen — det är rapporter.

Visualisering

För visualisering av verktyg användes Bluff: Beautiful graphs in JavaScript

För detta krävdes det att använda PHP för att gå igenom alla rapporter och generera en html-fil med tabeller.

$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'
);

Verktyget visualiserar helt enkelt resultatens tabeller.

Utdata

I exemplet med webbanalys beskriver artikeln de mekanismer som behövs för att bygga datalager. Som resultaten visar, räcker de enklaste verktygen för djup analys och visualisering av data.

I framtiden, med hjälp av detta datalager, kommer vi att försöka implementera strukturer som långsam förändring av dimensioner, metadata, aggregeringsnivåer och dataintegration från olika källor.

Vi kommer också att titta på det enklaste verktyget för att hantera ETL-processer baserat på en enda tabell.

Låt oss återvända till temat om mätning av datakvalitet och automatiseringen av denna process.

Vi kommer att studera problemen med den tekniska miljön och underhållet av datalager, för vilket vi implementerar en lagringsserver med minimala resurser, exempelvis baserat på Raspberry Pi.

Källa: habr.com