Mga istatistika ng site at sarili mong maliit na storage

Nakatulong sa akin ang Webalizer at Google Analytics na magkaroon ng insight sa kung ano ang nangyayari sa mga website sa loob ng maraming taon. Ngayon naiintindihan ko na ang mga ito ay nagbibigay ng napakakaunting kapaki-pakinabang na impormasyon. Ang pagkakaroon ng access sa iyong access.log file, napakadaling maunawaan ang mga istatistika at magpatupad ng mga pangunahing tool, tulad ng sqlite, html, ang sql language at anumang scripting programming language.

Ang data source para sa Webalizer ay ang access.log file ng server. Ganito ang hitsura ng mga bar at numero nito, kung saan ang kabuuang dami ng trapiko lang ang malinaw:

Ang mga tool tulad ng Google Analytics ay nangongolekta ng data mula sa na-load na pahina mismo. Nagpapakita sila sa amin ng isang pares ng mga diagram at linya, batay sa kung saan madalas na mahirap gumawa ng mga tamang konklusyon. Siguro mas maraming pagsisikap ang dapat ginawa? hindi ko alam.

Kaya, ano ang gusto kong makita sa mga istatistika ng bisita sa website?

Trapiko ng user at bot

Kadalasan ang trapiko sa site ay limitado at ito ay kinakailangan upang makita kung gaano karaming kapaki-pakinabang na trapiko ang ginagamit. Halimbawa, tulad nito:

Query sa ulat ng SQL

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_DT

Ipinapakita ng graph ang patuloy na aktibidad ng mga bot. Magiging kagiliw-giliw na pag-aralan nang detalyado ang mga pinaka-aktibong kinatawan.

Nakakainis na mga bot

Inuri namin ang mga bot batay sa impormasyon ng ahente ng gumagamit. Ang mga karagdagang istatistika sa pang-araw-araw na trapiko, bilang ng matagumpay at hindi matagumpay na mga kahilingan ay nagbibigay ng magandang ideya ng aktibidad ng bot.

Query sa ulat ng 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 != 'n.a.'
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10

Sa kasong ito, ang resulta ng pagsusuri ay ang desisyon na paghigpitan ang pag-access sa site sa pamamagitan ng pagdaragdag nito sa robots.txt file

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Ang unang dalawang bot ay nawala mula sa talahanayan, at ang mga MS robot ay lumipat pababa mula sa mga unang linya.

Araw at oras ng pinakamalaking aktibidad

Ang mga upswing ay makikita sa trapiko. Upang pag-aralan ang mga ito nang detalyado, kinakailangang i-highlight ang oras ng kanilang paglitaw, at hindi kinakailangang ipakita ang lahat ng oras at araw ng pagsukat ng oras. Gagawin nitong mas madali ang paghahanap ng mga indibidwal na kahilingan sa log file kung kailangan ng detalyadong pagsusuri.

Query sa ulat ng 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!='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

Inoobserbahan namin ang pinakaaktibong oras 11, 14 at 20 ng unang araw sa chart. Ngunit sa susunod na araw sa 13:XNUMX aktibo ang mga bot.

Average na pang-araw-araw na aktibidad ng user sa bawat linggo

Medyo inayos namin ang mga bagay sa aktibidad at trapiko. Ang susunod na tanong ay ang aktibidad ng mga gumagamit mismo. Para sa mga naturang istatistika, ang mahabang panahon ng pagsasama-sama, tulad ng isang linggo, ay kanais-nais.

Query sa ulat ng 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='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_DT

Ipinapakita ng mga lingguhang istatistika na sa karaniwan ay nagbubukas ang isang user ng 1,6 na pahina bawat araw. Ang bilang ng mga hiniling na file sa bawat user sa kasong ito ay depende sa pagdaragdag ng mga bagong file sa site.

Lahat ng mga kahilingan at kanilang mga katayuan

Palaging nagpapakita ang Webalizer ng mga partikular na code ng pahina at gusto kong makita lang ang bilang ng mga matagumpay na kahilingan at error.

Query sa ulat ng 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

Ang ulat ay nagpapakita ng mga kahilingan, hindi mga pag-click (mga hit), hindi tulad ng LINE_CNT, ang REQUEST_CNT na sukatan ay kinakalkula bilang COUNT(DISTINCT STG.REQUEST_NK). Ang layunin ay ipakita ang mga epektibong kaganapan, halimbawa, ang mga bot ng MS ay nag-poll sa robots.txt na file nang daan-daang beses sa isang araw at, sa kasong ito, ang mga naturang botohan ay bibilangin nang isang beses. Nagbibigay-daan ito sa iyo na pakinisin ang mga pagtalon sa graph.

Mula sa graph maaari kang makakita ng maraming mga error - ito ay hindi umiiral na mga pahina. Ang resulta ng pagsusuri ay ang pagdaragdag ng mga pag-redirect mula sa mga malalayong pahina.

Mga masamang kahilingan

Upang suriin ang mga kahilingan nang detalyado, maaari kang magpakita ng mga detalyadong istatistika.

Query sa ulat ng 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

Ang listahang ito ay maglalaman din ng lahat ng mga tawag, halimbawa, isang kahilingan sa /wp-login.php Sa pamamagitan ng pagsasaayos ng mga patakaran para sa muling pagsusulat ng mga kahilingan ng server, maaari mong ayusin ang reaksyon ng server sa mga naturang kahilingan at ipadala ang mga ito sa panimulang pahina.

Kaya, ang ilang simpleng ulat batay sa server log file ay nagbibigay ng medyo kumpletong larawan ng kung ano ang nangyayari sa site.

Paano makakuha ng impormasyon?

Ang isang sqlite database ay sapat. Gumawa tayo ng mga talahanayan: pantulong para sa pag-log ng mga proseso ng ETL.

Table stage kung saan magsusulat tayo ng mga log files gamit ang PHP. Dalawang pinagsama-samang talahanayan. Gumawa tayo ng pang-araw-araw na talahanayan na may mga istatistika sa mga ahente ng gumagamit at mga status ng kahilingan. Oras-oras na may mga istatistika sa mga kahilingan, pangkat ng katayuan at ahente. Apat na talahanayan ng mga nauugnay na sukat.

Ang resulta ay ang sumusunod na relational model:

Modelo ng data

Script upang lumikha ng isang bagay sa isang sqlite database:

Paglikha ng bagay ng 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 '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);

Yugto

Sa kaso ng access.log file, kinakailangang basahin, i-parse at isulat ang lahat ng mga kahilingan sa database. Maaari itong gawin nang direkta gamit ang isang scripting language o gamit ang sqlite tool.

Format ng log file:

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

Key pagpapalaganap

Kapag ang raw data ay nasa database, kailangan mong isulat ang mga key na wala doon sa mga talahanayan ng pagsukat. Pagkatapos ay posible na bumuo ng isang sanggunian sa mga sukat. Halimbawa, sa DIM_REFERRER table, ang susi ay isang kumbinasyon ng tatlong field.

Query sa pagpapalaganap ng SQL key

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

Ang pagpapalaganap sa talahanayan ng user agent ay maaaring maglaman ng bot logic, halimbawa ang sql snippet:

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

Pinagsama-samang mga talahanayan

Panghuli, ilo-load namin ang pinagsama-samang mga talahanayan; halimbawa, ang pang-araw-araw na talahanayan ay maaaring i-load tulad ng sumusunod:

SQL query para sa pag-load ng pinagsama-samang

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

Ang sqlite database ay nagpapahintulot sa iyo na magsulat ng mga kumplikadong query. WITH naglalaman ng paghahanda ng data at mga susi. Kinokolekta ng pangunahing query ang lahat ng mga sanggunian sa mga sukat.

Hindi papayagan ng kundisyon ang pag-load muli ng history: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kung saan ang parameter ay ang resulta ng kahilingan
‘PUMILI NG COALESCE(MAX(EVENT_DT), ‘3600’) BILANG LAST_EVENT_EPOCH MULA FCT_ACCESS_USER_AGENT_DD’

Maglo-load lang ang kundisyon sa buong araw: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, date(‘now’, ‘start of day’))

Ang pagbibilang ng mga pahina o file ay isinasagawa sa primitive na paraan, sa pamamagitan ng paghahanap ng isang punto.

Mga Ulat

Sa mga kumplikadong sistema ng visualization, posibleng lumikha ng meta-modelo batay sa mga object ng database, dynamic na pamahalaan ang mga filter at mga panuntunan sa pagsasama-sama. Sa huli, lahat ng disenteng tool ay bumubuo ng isang SQL query.

Sa halimbawang ito, gagawa kami ng mga yari na SQL query at i-save ang mga ito bilang mga view sa database - ito ay mga ulat.

Pagpapakita

Bluff: Ginamit ang magagandang graph sa JavaScript bilang visualization tool

Upang gawin ito, kinakailangang dumaan sa lahat ng mga ulat gamit ang PHP at bumuo ng isang html file na may mga talahanayan.

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

Inilarawan lamang ng tool ang mga talahanayan ng mga resulta.

Pagbubuhos

Gamit ang pagsusuri sa web bilang isang halimbawa, inilalarawan ng artikulo ang mga mekanismong kinakailangan upang bumuo ng mga warehouse ng data. Tulad ng makikita mula sa mga resulta, ang pinakasimpleng mga tool ay sapat para sa malalim na pagsusuri at visualization ng data.

Sa hinaharap, gamit ang repositoryong ito bilang halimbawa, susubukan naming ipatupad ang mga istruktura tulad ng dahan-dahang pagbabago ng mga dimensyon, metadata, mga antas ng pagsasama-sama at pagsasama ng data mula sa iba't ibang pinagmulan.

Gayundin, tingnan natin ang pinakasimpleng tool para sa pamamahala ng mga proseso ng ETL batay sa isang talahanayan.

Bumalik tayo sa paksa ng pagsukat ng kalidad ng data at pag-automate ng prosesong ito.

Pag-aaralan namin ang mga problema ng teknikal na kapaligiran at pagpapanatili ng mga imbakan ng data, kung saan ipapatupad namin ang isang server ng imbakan na may kaunting mga mapagkukunan, halimbawa, batay sa isang Raspberry Pi.

Pinagmulan: www.habr.com