Mga istatistika sa site ug ang imong kaugalingon nga gamay nga pagtipig

Ang Webalizer ug Google Analytics nakatabang kanako nga makakuha og panabut sa kung unsa ang nahitabo sa mga website sa daghang mga tuig. Karon akong nasabtan nga sila naghatag ug gamay kaayo nga mapuslanong impormasyon. Ang pagbaton ug access sa imong access.log file, sayon ​​ra kaayong sabton ang mga estadistika ug ipatuman ang mga batakang himan, sama sa sqlite, html, sql nga pinulongan ug bisan unsang scripting programming language.

Ang tinubdan sa datos alang sa Webalizer mao ang access.log file sa server. Mao kini ang hitsura sa mga bar ug numero niini, diin ang kinatibuk-ang gidaghanon sa trapiko ang klaro:

Ang mga himan sama sa Google Analytics nagkolekta sa datos gikan sa gikarga nga panid mismo. Gipakita nila kanamo ang usa ka pares nga mga diagram ug mga linya, nga gibase sa kanunay lisud ang paghimo sa husto nga mga konklusyon. Tingali mas daghang paningkamot ang kinahanglan nga gihimo? wala kabalo.

Busa, unsa ang gusto nakong makita sa mga istatistika sa bisita sa website?

Ang trapiko sa user ug bot

Kasagaran ang trapiko sa site limitado ug kinahanglan nga makita kung unsa ka daghang mapuslanon nga trapiko ang gigamit. Pananglitan, sama niini:

Pangutana sa report sa 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

Gipakita sa graph ang kanunay nga kalihokan sa mga bot. Makapaikag nga tun-an sa detalye ang labing aktibo nga mga representante.

Makalagot nga mga bot

Giklasipikar namo ang mga bot base sa impormasyon sa ahente sa tiggamit. Ang dugang nga estadistika sa adlaw-adlaw nga trapiko, gidaghanon sa malampuson ug dili malampuson nga mga hangyo naghatag usa ka maayong ideya sa kalihokan sa bot.

Pangutana sa report sa 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 kini nga kaso, ang resulta sa pagtuki mao ang desisyon sa pagpugong sa pag-access sa site pinaagi sa pagdugang niini sa robots.txt file

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Ang unang duha ka mga bot nawala gikan sa lamesa, ug ang MS robots mibalhin gikan sa unang mga linya.

Adlaw ug oras sa labing dako nga kalihokan

Ang mga pag-uswag makita sa trapiko. Aron tun-an sila sa detalye, gikinahanglan nga i-highlight ang oras sa ilang panghitabo, ug dili kinahanglan nga ipakita ang tanan nga mga oras ug mga adlaw sa pagsukod sa oras. Kini makapasayon ​​sa pagpangita sa indibidwal nga mga hangyo sa log file kung gikinahanglan ang detalyadong pagtuki.

Pangutana sa report sa 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

Among giobserbahan ang labing aktibo nga mga oras 11, 14 ug 20 sa unang adlaw sa tsart. Apan sa sunod nga adlaw sa 13:XNUMX aktibo ang mga bot.

Average nga adlaw-adlaw nga kalihokan sa tiggamit matag semana

Gihan-ay namo ang mga butang uban sa kalihokan ug trapiko. Ang sunod nga pangutana mao ang kalihokan sa mga tiggamit mismo. Alang sa ingon nga mga estadistika, ang taas nga mga yugto sa panagsama, sama sa usa ka semana, gitinguha.

Pangutana sa report sa 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

Ang senemanang estadistika nagpakita nga sa aberids usa ka user ang nagbukas ug 1,6 ka panid kada adlaw. Ang gidaghanon sa gihangyo nga mga file matag user niini nga kaso nagdepende sa pagdugang sa bag-ong mga file sa site.

Tanan nga hangyo ug ilang mga status

Kanunay nga gipakita sa Webalizer ang piho nga mga code sa panid ug gusto nako kanunay nga makita ang gidaghanon sa malampuson nga mga hangyo ug sayup.

Pangutana sa report sa 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 report nagpakita sa mga hangyo, dili sa mga pag-klik (mga hit), dili sama sa LINE_CNT, ang REQUEST_CNT metric gikalkulo isip COUNT(DISTINCT STG.REQUEST_NK). Ang tumong mao ang pagpakita sa epektibo nga mga panghitabo, pananglitan, ang MS bots poll sa robots.txt file gatusan ka beses sa usa ka adlaw ug, sa niini nga kaso, ang maong mga poll maihap sa makausa. Kini nagtugot kanimo sa pagpahapsay sa mga paglukso sa graph.

Gikan sa graph makita nimo ang daghang mga kasaypanan - kini mga wala nga mga panid. Ang resulta sa pagtuki mao ang pagdugang sa mga redirect gikan sa hilit nga mga panid.

Dili maayo nga mga hangyo

Aron masusi ang mga hangyo sa detalye, mahimo nimong ipakita ang detalyado nga estadistika.

Pangutana sa report sa 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

Kini nga lista maglangkob usab sa tanan nga mga tawag, pananglitan, usa ka hangyo sa /wp-login.php Pinaagi sa pag-adjust sa mga lagda alang sa pagsulat pag-usab sa mga hangyo sa server, mahimo nimong i-adjust ang reaksyon sa server sa ingon nga mga hangyo ug ipadala kini sa pagsugod nga panid.

Mao nga, ang pipila ka yano nga mga taho nga gibase sa file sa log sa server naghatag usa ka medyo kompleto nga litrato sa kung unsa ang nahitabo sa site.

Unsaon pagkuha ug impormasyon?

Ang usa ka sqlite database igo na. Maghimo kita og mga lamesa: auxiliary para sa pag-log sa mga proseso sa ETL.

Table stage diin kita magsulat og log files gamit ang PHP. Duha ka aggregate nga mga lamesa. Maghimo kita usa ka adlaw-adlaw nga lamesa nga adunay mga estadistika sa mga ahente sa tiggamit ug mga kahimtang sa paghangyo. Kada oras nga adunay mga estadistika sa mga hangyo, mga grupo sa status ug mga ahente. Upat ka mga lamesa sa may kalabutan nga mga sukod.

Ang resulta mao ang mosunod nga relational model:

Modelo sa datos

Script aron makahimo usa ka butang sa usa ka sqlite database:

Paghimo sa DDL object

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

entablado

Sa kaso sa access.log file, gikinahanglan ang pagbasa, pag-parse ug pagsulat sa tanang mga hangyo sa database. Mahimo kini nga direkta gamit ang usa ka sinultian nga script o gamit ang mga himan sa sqlite.

Log file format:

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

Kung ang hilaw nga datos naa sa database, kinahanglan nimo nga isulat ang mga yawe nga wala didto sa mga lamesa sa pagsukod. Unya posible nga maghimo usa ka pakisayran sa mga pagsukod. Pananglitan, sa DIM_REFERRER nga lamesa, ang yawe usa ka kombinasyon sa tulo ka mga natad.

SQL key propagation nga pangutana

/* 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 pagpalapad sa lamesa sa ahente sa gumagamit mahimong adunay bot logic, pananglitan 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

Aggregate nga mga lamesa

Katapusan, atong i-load ang mga aggregate table; pananglitan, ang daily table mahimong ma-load sama sa mosunod:

SQL pangutana alang sa loading aggregate

/* 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 nagtugot kanimo sa pagsulat sa komplikadong mga pangutana. Ang WITH naglangkob sa pag-andam sa datos ug mga yawe. Ang panguna nga pangutana nagkolekta sa tanan nga mga pakisayran sa mga sukat.

Ang kondisyon dili motugot sa pag-load sa kasaysayan pag-usab: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, diin ang parameter mao ang resulta sa hangyo
'PILI COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH GIKAN sa FCT_ACCESS_USER_AGENT_DD'

Ang kondisyon magkarga lang sa tibuok adlaw: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('karon', 'start of day'))

Ang pag-ihap sa mga panid o mga file gihimo sa una nga paagi, pinaagi sa pagpangita sa usa ka punto.

Mga report

Sa komplikado nga mga sistema sa visualization, posible nga maghimo usa ka meta-modelo nga gibase sa mga butang sa database, dinamikong pagdumala sa mga pagsala ug mga lagda sa panagsama. Sa katapusan, ang tanan nga desente nga mga himan makamugna usa ka pangutana sa SQL.

Niini nga pananglitan, maghimo kami og andam nga mga pangutana sa SQL ug i-save kini isip mga panglantaw sa database - kini mga taho.

Paglaraw

Bluff: Ang nindot nga mga graph sa JavaScript gigamit isip visualization tool

Aron mahimo kini, kinahanglan nga susihon ang tanan nga mga taho gamit ang PHP ug maghimo usa ka html file nga adunay mga lamesa.

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

Ang himan naghulagway lamang sa mga talaan sa mga resulta.

konklusyon

Gigamit ang pag-analisar sa web isip usa ka pananglitan, ang artikulo naghulagway sa mga mekanismo nga gikinahanglan sa pagtukod sa mga bodega sa datos. Ingon sa makita gikan sa mga resulta, ang pinakasimple nga mga himan igo na alang sa lawom nga pagtuki ug paghanduraw sa datos.

Sa umaabot, gamit ang kini nga repository isip usa ka pananglitan, sulayan namon nga ipatuman ang mga istruktura sama sa hinay nga pagbag-o sa mga sukat, metadata, lebel sa panagsama ug paghiusa sa mga datos gikan sa lainlaing mga gigikanan.

Usab, atong tan-awon pag-ayo ang pinakasimple nga himan alang sa pagdumala sa mga proseso sa ETL base sa usa ka lamesa.

Balikan nato ang hilisgutan sa pagsukod sa kalidad sa datos ug pag-automate niini nga proseso.

Atong tun-an ang mga problema sa teknikal nga palibot ug pagmentinar sa mga pagtipig sa datos, diin atong ipatuman ang usa ka storage server nga adunay gamay nga mga kapanguhaan, pananglitan, base sa usa ka Raspberry Pi.

Source: www.habr.com