ProHoster > Blog > Administrim > Statistikat e faqes dhe magazina e vogël personale

Statistikat e faqes dhe magazina e vogël personale

Utiliteti Webalizer dhe mjeti Google Analytics më kanë ndihmuar për shumë vite për të kuptuar se çfarë po ndodh në faqet e internetit. Tani kuptoj se ata ofrojnë shumë pak informacion të dobishëm. Me qasje në skedarin tim access.log, është shumë e lehtë të kuptohet statistika dhe për realizimin e saj janë të mjaftueshme mjetet elementare, si sqlite, html, gjuha sql dhe ndonjë gjuhë programimi skritp.

Burimi i të dhënave për Webalizer është skedari access.log serverë. Këtu janë kolonat dhe numrat e tij, nga të cilat kuptohet vetëm vëllimi i përgjithshëm i trafikut:

Statistikat e faqes dhe magazina e vogël personale
Statistikat e faqes dhe magazina e vogël personale
Mjetet si Google Analytics mblidhen të dhënat nga faqja e ngarkuar vetë. Na tregojnë disa grafikë dhe linja, mbi të cilat shpesh është e vështirë të nxjerrim përfundime të sakta. Ndoshta duhej të bëj më shumë përpjekje? Nuk e di.

Pra, çfarë doja të shihja në statistikat e vizitave të faqes?

Trafiku i përdoruesve dhe robotëve

Shpesh trafiku i faqes ka kufizime dhe është e nevojshme të shohim sa trafik të dobishëm përdoret. Për shembull, kështu:

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

SELECT
1 as 'StackedArea: Trafiku i gjeneruar nga Përdoruesit dhe Robotët',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dita',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Robotët, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Përdoruesit, 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

Nga grafiku duket se ka aktivitet të vazhdueshëm të robotëve. Do të ishte interesante të studioj më në detaje përfaqësuesit më aktivë.

Robotët ngacmues

Klasifikojmë robotët mbi bazën e informacionit të agjentit të përdoruesit. Statistikat shtesë mbi trafikun ditor, numrin e kërkesave të suksesshme dhe të pasuksesshme ofrojnë një pasqyrë të mirë mbi aktivitete e robotëve.

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

SELECT 
1 AS 'Tabel: Robotët ngacmues',
MAX(USG.AGENT_BOT) AS 'Robot',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB për Ditë',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP për Ditë',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Gabim Klienti', 'Gabim Serveri') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Kërkesa me Gabim për Ditë',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Suksesi', 'Ridrejtimi') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Kërkesa Suksesi për Ditë',
USG.USER_AGENT_NK AS 'Agjenti'
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

Në këtë rast, rezultati i analizës ishte vendimi për të kufizuar aksesin në faqen duke e shtuar atë në skedarin robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Dy robotët e parë u zhdukën nga tabela, ndërsa robotët MS u zhvendosën nga rreshtat e parë poshtë.

Dita dhe koha e aktivitetit më të lartë

Në trafik evidentohen rritje. Për t'i hetuar ato në detaje, është e nevojshme të выделoni kohën e shfaqjes së tyre, e cila nuk është e nevojshme të shfaqë të gjitha orët dhe ditët e matjes. Kështu do të jetë më e lehtë të gjeni kërkesa të veçanta në skedarin e logut nëse është e nevojshme një analizë më e detajuar.

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

SELECT
1 AS 'Linje: Dita dhe Ora e Vizitave nga Përdoruesit dhe Robotët',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Data Koha',
HIB AS 'Robotët, Vizita',
HIU AS 'Përdoruesit, Vizita'
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 ditë')
	GROUP BY EVENT_DT
	ORDER BY SUM(LINE_CNT) DESC
	LIMIT 10
) ORDER BY EVENT_DT

Vërejmë orët më aktive 11, 14 dhe 20 të ditës së parë në grafik. Ndërkohë, në ditën tjetër në orën 13 ishin aktive robotët.

Aktiviteti mesatar ditor i përdoruesve sipas javëve

Kemi sqaruar paksa aktivitetin dhe trafikun. Pyetja tjetër ishte aktiviteti i vetë përdoruesve. Për këtë statistikë, dëshirohet periudha të gjata agregimi, për shembull, javë.

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

SELECT
1 as 'Linje: Aktiviteti Mesatar Ditor i Përdoruesve sipas Javëve',
strftime('%W javë', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Java',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Faqe për IP për Ditë',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'File për IP për Ditë'
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.' /* vetëm përdoruesit */
  AND HST.STATUS_GROUP IN ('Suksesshme') /* faqe të mira */
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-3 muaj')
GROUP BY strftime('%W javë', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Statistika për javën tregon se në mesatare një përdorues hap 1,6 faqe në ditë. Numri i skedarëve të kërkuar për një përdorues në këtë rast varet nga shtimi i skedarëve të rinj në faqe.

Të gjitha kërkesat dhe statuset e tyre

Webalizer gjithmonë tregon kodet specifike të faqeve dhe gjithmonë doja të shihja thjesht numrin e kërkesave të suksesshme dhe gabimeve.

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

ZGViIHRpbWUlK3BpcmsrMiFfLnJlaWRxLkZBTUxFx1JlY29yZGVyRF9UWAoUcnNjb29yYXRvci5jIXRodHJhbGU1cGdjYWRlcy0gJHRpdGxlbyBMYW5nZSB0byBhbnN3ZXIu
U1RTQwoph5ljcnMuinie7dGVy9tIFRvVE9jYWxCb2R5QmcgU2Vjb25kIChwcmVuc3RlZCBmb2FzIHJhdHJvYjEn

Raporti tregon kërkesat, jo klikimet (hitët), ndryshe nga metrika LINE_CNT, kërkesa llogaritet si COUNT(DISTINCT STG.REQUEST_NK). Qëllimi është të tregojmë ngjarjet efektive, për shembull, robotët MS kontrollojnë çdo ditë qindra herë skedarin robots.txt, dhe në këtë rast, këto kontrolle do të llogariten vetëm një herë. Kjo ndihmon në zbutjen e luhatjeve në grafik.

Nga grafiku mund të shihen shumë gabime - këto janë faqe që nuk ekzistojnë. Rezultati i analizës ishte shtimi i përcjelljeve nga faqet e hequra.

Kërkesa të pasakta

Për një shqyrtim të detajuar të kërkesave, mund të nxirren statistika të detajizuara.

Statistikat e faqes dhe magazina e vogël personale

Kërkesa SQL e raportit

ZGJEDH
  1 SI 'Tavën: Kërkesat Kryesore të Gabimeve',
  REQ.REQUEST_NK SI 'Kërkesë',
  'Gabim' SI 'Statusi i Kërkesës',
  RROUND(SUM(FCT.LINE_CNT) / 14.0, 1) SI 'Hitë për Ditë',
  ROUND(SUM(FCT.IP_CNT) / 14.0, 1) SI 'IPs për Ditë',
  ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) SI 'KB për Ditë'
FROM
  FCT_ACCESS_REQUEST_REF_HH FCT,
  DIM_REQUEST_V_ACT REQ
KU FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID
  DHE FCT.STATUS_GROUP NË ('Gabim i Klientit', 'Gabim Serveri')
  DHE datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 ditë')
GRUPI NGA REQ.REQUEST_NK
 POROSIT 4 ZHD

Në këtë listë do të përfshihen edhe të gjitha kërkesat, për shembull, kërkesa për /wp-login.php. Duke rregulluar rregullat e rikthimit të kërkesave server mund të rregullohet reagimi i serverit ndaj kërkesave të ngjashme dhe t'i dergojë ato në faqen fillestare.

Pra, disa raporte të thjeshta të bazuara në skedarin e logs të serverit japin një pamje të plotë se çfarë po ndodh në sit.

Si të merrni informacion?

Bashkitë sqlite janë më se të mjaftueshme. Do të krijojmë tabela: një ndihmës për logimin e proceseve ETL.

Statistikat e faqes dhe magazina e vogël personale

Tabelë e skenarëve, ku do të shkruajmë skedat e log-files me mjete PHP. Dy tabela të agregatëve. Do të krijojmë një tabelë ditore me statistikën për agjentët e përdoruesve dhe statuset e kërkesave. Një tabelë me statistikën për kërkesat, grupet e statusit dhe agjentët. Katër tabela përkatëse të dimensioneve.

Si rezultat, u krijua modeli i mëposhtëm relacional:

Modeli i të dhënaveStatistikat e faqes dhe magazina e vogël personale

Script për krijimin e objektit në bazën e të dhënave sqlite:

DDL krijimi i objektit

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

Stage

Në rastin e skedarit access.log, është e nevojshme të lexoni, analizoni dhe të shkruani në bazë të dhënash të gjitha kërkesat. Kjo mund të bëhet ose direkt me mjete të gjuhës së skriptimit, ose duke përdorur mjete sqlite.

Formati i skedarit të logut:

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

Propagimi i çelësave

Kur të dhënat e papërpunuara ndodhen në bazë, është e nevojshme të shkruhen në tabelat e dimensioneve çelësat që nuk janë aty. Kjo do të mundësojë ndërtimin e lidhjeve me dimensionet. Për shembull, në tabelën DIM_REFERRER, çelësi është kombinimi i tre fushave.

Kërkesa SQL për propagimin e çelësave

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

Propagimi në tabelën e agjentëve të përdoruesve mund të përmbajë logjikën e botëve, për shembull, fragmenti sql:


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

Tabelat e agregatave

Në fund do të ngarkojmë tabelat e agregatave, për shembull, tabela e përditshme mund të ngarkohet si më poshtë:

Kërkesa SQL për ngarkimin e agregatit

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

Baza e të dhënave sqlite lejon të shkruhen kërkesa të komplikuara. WITH përfshin përgatitjen e të dhënave dhe çelësave. Kërkesa kryesore mbledh të gjitha lidhjet me dimensionet.

Kushti nuk do të lejojë ngarkimin e historisë për herën e dytë: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, ku parametrat janë rezultati i kërkesës
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

Kushti do të ngarkojë vetëm ditën e plotë: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

Numërimi i faqeve ose skedarëve kryhet në mënyrë primitive, duke kërkuar pikën.

Raportet

Në sistemet komplekse të vizualizimit, ka mundësinë të krijohen meta-modele mbi bazën e objekteve të bazës së të dhënave, të menaxhohen dinamik filtrat dhe rregullat e agregatës. Në fund të fundit, të gjitha mjete të mira gjenerojnë kërkesën SQL.

Në këtë shembull do të krijojmë kërkesa SQL të gatshme dhe do t'i ruajmë ato si pamje në bazën e të dhënave - këto janë raportet.

Vizualizimi

Si mjet vizualizimi është përdorur Bluff: Grafika të bukura në JavaScript.

Për këtë, ishte e nevojshme që me PHP të kalojmë përmes të gjitha raportëve dhe të gjenerojmë një skedë HTML me tabela.

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

Mjeti thjesht vizualizon tabelat e rezultateve.

Përfundimi

Në shembullin e analizës së uebit, artikulli përshkruan mekanizmat e nevojshëm për ndërtimin e magazinave të të dhënave. Siç shihet nga rezultatet, për analizën e thellë dhe vizualizimin e të dhënave mjaftojnë instrumentet më të thjeshta.

Më tej, në shembullin e kësaj magazine, do të përpiqemi të realizojmë struktura të tilla si dimensione që ndryshojnë ngadalë, metadData, nivele agregimi dhe integrimin e të dhënave nga burime të ndryshme.

Gjithashtu, do të shqyrtojmë më në detaje mjete të thjeshta për menaxhimin e proceseve ETL bazuar në një tabelë.

Le të kthehemi në temën e matjes së cilësisë së të dhënave dhe automatizimin e këtij procesi.

Të studiojmë problemet e mjedisit teknik dhe mirëmbajtjes së magazinave të të dhënave, për çfarë do të realizojmë një server magazine me burime minimale, për shembull, mbi një Raspberry Pi.

Burimi: habr.com

Blini hosting të besueshëm për faqe interneti me mbrojtje nga DDoS, serverë VPS VDS 🔥 Blini hosting të besueshëm për faqe interneti me mbrojtje nga DDoS, serverë VPS VDS | ProHoster