Webalizer och Google Analytics har hjälpt mig att få insikt i vad som händer på webbplatser i många år. Nu förstår jag att de ger väldigt lite användbar information. Genom att ha tillgång till din access.log-fil är det mycket lätt att förstå statistiken och att implementera ganska grundläggande verktyg, såsom sqlite, html, sql-språket och alla skriptspråk.
Datakällan för Webalizer är serverns access.log-fil. Så här ser dess staplar och siffror ut, från vilka endast den totala trafikvolymen är tydlig:
Verktyg som Google Analytics samlar själva in data från den inlästa sidan. De visar oss ett par diagram och linjer, utifrån vilka det ofta är svårt att dra korrekta slutsatser. Kanske borde ha ansträngts mer? Vet inte.
Så, vad ville jag se i webbplatsens besöksstatistik?
Användar- och bottrafik
Ofta är webbplatstrafiken begränsad och det är nödvändigt att se hur mycket användbar trafik som används. Till exempel, så här:
SQL-rapportfråga
SELECT
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!='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 '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_DTGrafen visar botarnas konstanta aktivitet. Det skulle vara intressant att i detalj studera de mest aktiva företrädarna.
Irriterande bots
Vi klassificerar bots baserat på användaragentinformation. Ytterligare statistik om daglig trafik, antal lyckade och misslyckade förfrågningar ger en god uppfattning om botaktivitet.
SQL-rapportfråga
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 != 'n.a.'
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10I det här fallet var resultatet av analysen beslutet att begränsa åtkomsten till webbplatsen genom att lägga till den i robots.txt-filen
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
De två första robotarna försvann från bordet och MS-robotarna flyttade ner från de första raderna.
Dag och tid för största aktivitet
Uppgångar syns i trafiken. För att studera dem i detalj är det nödvändigt att markera tidpunkten för deras förekomst, och det är inte nödvändigt att visa alla timmar och dagar av tidsmätning. Detta kommer att göra det lättare att hitta enskilda förfrågningar i loggfilen om detaljerad analys behövs.
SQL-rapportfråga
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!='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_DTVi observerar de mest aktiva timmarna 11, 14 och 20 den första dagen på diagrammet. Men nästa dag klockan 13:XNUMX var botarna aktiva.
Genomsnittlig daglig användaraktivitet per vecka
Vi ordnade upp lite med aktivitet och trafik. Nästa fråga var användarnas egen aktivitet. För sådan statistik är långa aggregeringsperioder, till exempel en vecka, önskvärda.
SQL-rapportfråga
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='n.a.' /* users only */
AND HST.STATUS_GROUP IN ('Successful') /* good pages */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTVeckostatistik visar att en användare i genomsnitt öppnar 1,6 sidor per dag. Antalet begärda filer per användare i detta fall beror på att nya filer läggs till på webbplatsen.
Alla förfrågningar och deras status
Webalizer visade alltid specifika sidkoder och jag ville alltid se bara antalet lyckade förfrågningar och fel.
SQL-rapportfråga
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_DTRapporten visar förfrågningar, inte klick (träffar), till skillnad från LINE_CNT beräknas måttet REQUEST_CNT som COUNT(DISTINCT STG.REQUEST_NK). Målet är att visa effektiva händelser, till exempel att MS-robotar pollar robots.txt-filen hundratals gånger om dagen och i det här fallet kommer sådana omröstningar att räknas en gång. Detta gör att du kan jämna ut hopp i grafen.
Från grafen kan du se många fel - det här är obefintliga sidor. Resultatet av analysen var tillägget av omdirigeringar från avlägsna sidor.
Dåliga förfrågningar
För att granska förfrågningar i detalj kan du visa detaljerad statistik.
SQL-rapportfråga
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 20Denna lista kommer också att innehålla alla anrop, till exempel en begäran till /wp-login.php Genom att justera reglerna för omskrivning av förfrågningar från servern kan du justera serverns reaktion på sådana förfrågningar och skicka dem till startsidan.
Så, några enkla rapporter baserade på serverloggfilen ger en ganska komplett bild av vad som händer på sajten.
Hur får man information?
Det räcker med en SQLite-databas. Låt oss skapa tabeller: hjälpmedel för loggning av ETL-processer.
Tabellsteg där vi kommer att skriva loggfiler med PHP. Två sammanlagda tabeller. Låt oss skapa en daglig tabell med statistik över användaragenter och status för begäranden. Varje timme med statistik över förfrågningar, statusgrupper och agenter. Fyra tabeller med relevanta mått.
Resultatet är följande relationsmodell:
Datamodell
Skript för att skapa ett objekt i en SQLite-databas:
Skapande av DDL-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);Skede
När det gäller filen access.log är det nödvändigt att läsa, analysera och skriva alla förfrågningar till databasen. Detta kan göras antingen direkt med ett skriptspråk eller med sqlite-verktyg.
Loggfilformat:
//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-]+) "(.*)" "(.*)"$/';
Nyckelförökning
När rådata finns i databasen måste du skriva nycklar som inte finns där i mättabellerna. Då blir det möjligt att bygga en referens till måtten. Till exempel i tabellen DIM_REFERRER är nyckeln en kombination av tre fält.
SQL-nyckelförökningsfråga
/* 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 NULLSpridning till användaragenttabellen kan innehålla botlogik, till exempel sql-kodavsnittet:
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_BOTSamlade tabeller
Slutligen kommer vi att ladda de aggregerade tabellerna; till exempel kan den dagliga tabellen laddas enligt följande:
SQL-fråga för att ladda 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_IDSQLite-databasen låter dig skriva komplexa frågor. WITH innehåller förberedelse av data och nycklar. Huvudfrågan samlar alla referenser till dimensioner.
Villkoret tillåter inte att historiken laddas igen: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, där parametern är resultatet av begäran
'VÄLJ COALESCE(MAX(EVENT_DT), '3600') SOM LAST_EVENT_EPOCH FRÅN FCT_ACCESS_USER_AGENT_DD'
Villkoret laddar endast hela dagen: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Att räkna sidor eller filer utförs på ett primitivt sätt, genom att söka efter en punkt.
Rapporter
I komplexa visualiseringssystem är det möjligt att skapa en metamodell baserad på databasobjekt, dynamiskt hantera filter och aggregeringsregler. I slutändan genererar alla anständiga verktyg en SQL-fråga.
I det här exemplet kommer vi att skapa färdiga SQL-frågor och spara dem som vyer i databasen - det här är rapporter.
Visualisering
Bluff: Vackra grafer i JavaScript användes som ett visualiseringsverktyg
För att göra detta var det nödvändigt att gå igenom alla rapporter med PHP och skapa 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 resultattabeller.
Utgång
Med hjälp av webbanalys som exempel beskriver artikeln de mekanismer som krävs för att bygga datalager. Som framgår av resultaten räcker de enklaste verktygen för djupgående analys och visualisering av data.
I framtiden, med hjälp av detta arkiv som exempel, kommer vi att försöka implementera sådana strukturer som långsamt föränderliga dimensioner, metadata, aggregeringsnivåer och integration av data från olika källor.
Låt oss också ta en närmare titt på det enklaste verktyget för att hantera ETL-processer baserat på en enda tabell.
Låt oss återgå till ämnet att mäta datakvalitet och automatisera denna process.
Vi kommer att studera problemen med teknisk miljö och underhåll av datalagringar, för vilka vi kommer att implementera en lagringsserver med minimala resurser, till exempel baserad på en Raspberry Pi.
Källa: will.com