Utilitas Webalizer dan alat Google Analytics telah membantu saya selama bertahun-tahun untuk mendapatkan ide tentang apa yang terjadi di situs web. Sekarang saya mengerti bahwa mereka memberikan informasi yang sangat berguna. Memiliki akses ke file access.log Anda, sangat mudah untuk mengetahui statistik dan mengimplementasikan alat yang cukup mendasar seperti sqlite, html, sql, dan bahasa pemrograman scripting apa pun.
Sumber data untuk Webalizer adalah file access.log server. Beginilah tampilan kolom dan angka, yang hanya jumlah lalu lintas totalnya yang jelas:
Alat seperti Google Analytics mengumpulkan data dari halaman yang dimuat sendiri. Mereka menunjukkan kepada kita beberapa diagram dan garis, yang seringkali sulit untuk menarik kesimpulan yang tepat. Mungkin dibutuhkan lebih banyak usaha? Saya tidak tahu.
Jadi, apa yang ingin saya lihat dalam statistik kunjungan situs?
Lalu lintas pengguna dan bot
Seringkali lalu lintas situs memiliki batas dan Anda perlu melihat seberapa banyak lalu lintas yang bermanfaat digunakan. Misalnya, seperti ini:
Permintaan laporan SQLSELECT 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!='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB', SUM(CASE WHEN USG.AGENT_BOT='na' 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
Dari grafik, Anda dapat melihat aktivitas bot yang konstan. Akan menarik untuk mempelajari secara rinci perwakilan yang paling aktif.
Bot yang mengganggu
Kami mengklasifikasikan bot berdasarkan informasi agen pengguna. Statistik tambahan tentang lalu lintas harian, jumlah permintaan yang berhasil dan tidak berhasil memberikan ide bagus tentang aktivitas bot.
Permintaan laporan 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 != 'na' AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY USG.USER_AGENT_NK ORDER BY 3 DESC LIMIT 10
Dalam hal ini, analisis menghasilkan keputusan untuk membatasi akses ke situs dengan menambahkan robots.txt ke file
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5Dua bot pertama menghilang dari meja, dan robot MS bergerak turun dari baris pertama.
Hari dan waktu dari sebagian besar aktivitas
Lalu lintas muncul. Untuk mempelajarinya secara rinci, perlu untuk mengidentifikasi waktu kemunculannya, sementara itu tidak perlu untuk menampilkan semua jam dan hari pengukuran waktu. Jadi akan lebih mudah untuk menemukan permintaan individual dalam file log jika Anda membutuhkan analisis terperinci.
Permintaan laporan 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!='na' THEN LINE_CNT ELSE 0 END) AS HIB, SUM(CASE WHEN AGENT_BOT='na' 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
Kami mengamati jam paling aktif 11, 14 dan 20 dari hari pertama pada grafik. Tapi hari berikutnya pukul 13.00 bot sudah aktif.
Aktivitas pengguna harian rata-rata mingguan
Dengan aktivitas dan lalu lintas sedikit tahu. Pertanyaan selanjutnya adalah aktivitas para pengguna itu sendiri. Untuk statistik seperti itu, jumlah besar agregasi, misalnya, seminggu, diinginkan.
Permintaan laporan 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='na' AND HST.STATUS_GROUP IN ('Successful') 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
Statistik untuk minggu ini menunjukkan bahwa rata-rata satu pengguna membuka 1,6 halaman per hari. Jumlah file yang diminta per pengguna dalam hal ini tergantung pada penambahan file baru ke situs.
Semua permintaan dan statusnya
Webalizer selalu menunjukkan kode halaman tertentu dan selalu ingin melihat jumlah permintaan dan kesalahan yang berhasil.
Permintaan laporan 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
Laporan menampilkan permintaan, bukan klik (klik), tidak seperti LINE_CNT, metrik REQUEST_CNT dianggap sebagai COUNT (DISTINCT STG.REQUEST_NK). Tujuannya adalah untuk menunjukkan acara yang efektif, misalnya, bot MS ratusan kali sehari melakukan polling file robots.txt dan, dalam hal ini, polling tersebut akan dihitung sekali. Ini memungkinkan Anda untuk menghaluskan lompatan pada grafik.
Dari grafik, Anda dapat melihat banyak kesalahan - ini adalah halaman yang tidak ada. Hasil analisis adalah penambahan pengalihan dari halaman jarak jauh.
Permintaan salah
Untuk tinjauan rinci tentang permintaan, Anda dapat menampilkan statistik terperinci.
Permintaan laporan 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
Daftar ini akan berisi semua dialer, misalnya, permintaan ke /wp-login.php. Dengan menyesuaikan aturan untuk menulis ulang permintaan oleh server, Anda dapat menyesuaikan respons server terhadap permintaan tersebut dan mengirimkannya ke halaman awal.
Jadi, beberapa laporan sederhana berdasarkan file log server memberikan gambaran yang cukup lengkap tentang apa yang terjadi di situs.
Bagaimana cara mendapatkan informasi?
Database sqlite sudah cukup. Mari kita buat tabel: bantu untuk proses log ETL.
Tabel panggung, tempat kami akan menulis file log menggunakan PHP. Dua tabel agregat. Buat tabel harian dengan statistik tentang agen pengguna dan status permintaan. Setiap jam dengan statistik tentang permintaan, grup status, dan agen. Empat tabel pengukuran yang relevan.
Hasilnya adalah model relasional berikut:
Script untuk membuat objek dalam database sqlite:
Pembuatan objek 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 'na', AGENT_OS TEXT NOT NULL DEFAULT 'na', AGENT_ENGINE TEXT NOT NULL DEFAULT 'na', AGENT_DEVICE TEXT NOT NULL DEFAULT 'na', AGENT_BOT TEXT NOT NULL DEFAULT 'na', UPDATE_DT INTEGER NOT NULL DEFAULT 0, UNIQUE (USER_AGENT_NK) ); INSERT INTO DIM_USER_AGENT (DIM_USER_AGENT_ID) VALUES (-1);
Panggung
Dalam kasus file access.log, Anda perlu membaca, mem-parsing dan menulis semua permintaan ke database. Ini dapat dilakukan baik secara langsung menggunakan bahasa scripting, atau menggunakan sqlite.
Format file log:
Propaganda Kunci
Saat data mentah ada di database, Anda perlu merekam kunci yang tidak ada di tabel pengukuran. Maka akan mungkin untuk membangun referensi ke pengukuran. Misalnya, dalam tabel DIM_REFERRER, kuncinya adalah kombinasi dari tiga bidang.
Permintaan perambatan kunci SQL 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) 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
Propagasi ke tabel agen pengguna mungkin mengandung logika bot, misalnya, kutipan 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 'na' END AS AGENT_BOT
Tabel Unit
Terakhir, kami akan memuat tabel agregat, misalnya, tabel harian dapat dimuat sebagai berikut:
Permintaan pemuatan agregat SQL 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 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
Basis data sqlite memungkinkan Anda menulis kueri kompleks. DENGAN berisi persiapan data dan kunci. Kueri utama mengumpulkan semua referensi ke dimensi.
Kondisi tidak akan mengizinkan memuat cerita lagi: CAST (STG.EVENT_DT AS INTEGER)> $ param_epoch_from, di mana parameternya adalah hasil dari permintaan
'SELECT COALESCE (MAX (EVENT_DT), \' 3600 \ ') SEBAGAI LAST_EVENT_EPOCH DARI FCT_ACCESS_USER_AGENT_DD'
Kondisi hanya akan memuat sehari penuh: CAST (STG.EVENT_DT AS INTEGER) <strftime ('% s', date ('now', 'start of day'))
Menghitung halaman atau file dilakukan dengan cara primitif, dengan mencari titik.
Laporan
Dalam sistem visualisasi yang kompleks, dimungkinkan untuk membuat model-meta berdasarkan objek database, mengelola secara dinamis filter dan aturan agregasi. Pada akhirnya, semua alat yang layak menghasilkan kueri SQL.
Dalam contoh ini, kami akan membuat kueri SQL yang sudah jadi dan menyimpannya sebagai tampilan dalam database - ini adalah laporannya.
Visualisasi
Bluff: Grafik indah dalam JavaScript digunakan sebagai alat visualisasi.
Untuk ini, perlu menggunakan PHP untuk memeriksa semua laporan dan menghasilkan file html dengan tabel.
$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' );
Alat ini hanya memvisualisasikan tabel hasil.
Kesimpulan
Menggunakan analisis web sebagai contoh, artikel tersebut menjelaskan mekanisme yang diperlukan untuk membangun gudang data. Seperti dapat dilihat dari hasilnya, alat yang paling sederhana sudah cukup untuk analisis mendalam dan visualisasi data.
Di masa depan, dengan menggunakan contoh penyimpanan ini, kami akan mencoba menerapkan struktur seperti
mengubah pengukuran secara perlahan , data master, tingkat agregasi, dan integrasi data dari sumber yang berbeda.
Selain itu, kami akan melihat lebih dekat pada
alat manajemen proses ETL paling sederhana berdasarkan satu tabel.
Mari kita kembali ke topik pengukuran kualitas data dan mengotomatisasi proses ini.
Kami akan mempelajari masalah lingkungan teknis dan pemeliharaan gudang data, di mana kami menerapkan server penyimpanan dengan sumber daya minimal, misalnya, berdasarkan Raspberry Pi.