Geekly articles weekly

Lebih banyak statistik situs di repositori kecil Anda

Dengan menganalisis statistik situs, kami mendapat gambaran tentang apa yang terjadi dengannya. Kami membandingkan hasilnya dengan pengetahuan lain tentang produk atau layanan dan dengan demikian meningkatkan pengalaman kami.

Ketika analisis hasil pertama selesai, informasi telah dipahami dan kesimpulan diambil, tahap selanjutnya dimulai. Muncul ide: apa yang akan terjadi jika Anda melihat data dari perspektif lain?

Pada titik ini, ada keterbatasan pada alat analisis. Ini adalah salah satu alasan mengapa alat Google Analytics tidak cukup bagi saya, yaitu karena kemampuan terbatas untuk melihat dan memanipulasi data saya.

Saya selalu ingin memuat data dasar (data master) dengan cepat, menambahkan level agregasi lain, atau menafsirkan nilai yang ada.

Ini mudah dilakukan di repositori kecil Anda berdasarkan file access.log dan bahasa SQL sudah cukup untuk ini.

Jadi, pertanyaan apa yang ingin saya temukan jawabannya?

Apa dan kapan telah berubah di situs


Sejarah perubahan dalam data yang mendasarinya (data master) selalu menarik.

gambar

Permintaan laporan SQL
SELECT 1 as 'SideStackedBar: Content Updates by Months', strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Day', COUNT(CASE WHEN PAGE_TITLE != 'na' THEN DIM_REQUEST_ID END) AS 'Web page updates', COUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Image uploads', COUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video uploads', COUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audio uploads' FROM DIM_REQUEST WHERE PAGE_TITLE != 'na' OR PAGE_DESCR != 'na' GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) ORDER BY UPDATE_DT


Misalnya, pada beberapa titik, optimasi mesin pencari dilakukan atau konten baru ditambahkan ke situs, dalam hal ini, peningkatan lalu lintas diharapkan.

Grup pengguna


Contoh paling sederhana dari grup adalah agen pengguna atau nama sistem operasi.

Dimensi agen pengguna telah mengumpulkan sekitar seribu catatan, dan saya tertarik untuk melihat dinamika distribusi agen di dalam grup.

gambar

Permintaan laporan SQL
 SELECT 1 AS 'SideStackedBar: User Agents', AGENT_OS AS 'OS', SUM(CASE WHEN AGENT_BOT = 'na' THEN 1 ELSE 0 END ) AS 'User Agent of Users', SUM(CASE WHEN AGENT_BOT != 'na' THEN 1 ELSE 0 END ) AS 'User Agent of Bots' FROM DIM_USER_AGENT WHERE DIM_USER_AGENT_ID != -1 GROUP BY AGENT_OS ORDER BY 3 DESC


Sebagian besar berbagai kombinasi agen datang ke situs dari dunia Windows. Di antara yang tidak pasti adalah seperti WhatsApp, PocketImageCache, PlayStation, SmartTV, dll.

Aktivitas grup pengguna mingguan


Dengan menggabungkan beberapa kelompok, kita dapat mengamati distribusi kegiatan mereka.

Sebagai contoh, pengguna cluster Linux mengkonsumsi lebih banyak lalu lintas di situs daripada orang lain.

gambar

Permintaan laporan SQL
 SELECT 1 as 'StackedBar: Traffic Volume by User OS and by Week', strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week', SUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Android/Linux Users', SUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Windows Users', SUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Mac/iOS Users', SUM(CASE WHEN USG.AGENT_OS IN ('na', 'BlackBerry') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Other' 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' /* 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


Konsumsi lalu lintas yang padat


Tabel menunjukkan grup pengguna paling aktif dan hari aktivitas mereka.
Yang paling aktif adalah milik cluster Linux.

gambar

Permintaan laporan SQL
 SELECT 1 AS 'Table: User Agent with Havy Usage', strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', ROUND(1.0*SUM(FCT.BYTES)/1000000, 1) AS 'Traffic MB', ROUND(1.0*SUM(FCT.IP_CNT)/SUM(1), 1) AS 'IPs', ROUND(1.0*SUM(FCT.REQUEST_CNT)/SUM(1), 1) AS 'Requests', USA.DIM_USER_AGENT_ID AS 'ID', MAX(USA.USER_AGENT_NK) AS 'User Agent', MAX(USA.AGENT_BOT) AS 'Bot' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_USER_AGENT USA WHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 day') GROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT LIMIT 10


Menggunakan hari atribut dan ID agen, Anda dapat dengan cepat menemukan dan melacak statistik pada hari masing-masing grup pengguna. Jika perlu, Anda dapat dengan cepat menemukan informasi terperinci di tabel panggung.

Bagaimana cara mendapatkan informasi?


Informasi dari file access.log dapat dibuat lebih efektif dengan mengintegrasikan sumber data tambahan dan memperkenalkan tingkat agregasi dan pengelompokan baru.

Data dan entitas dasar


Data dasar mencakup informasi tentang entitas: halaman web, gambar, konten video dan audio, dalam hal toko, produk.

Entitas sendiri memainkan peran dimensi, dan proses menyimpan perubahan atribut disebut historisisasi. Dalam database, proses ini sering diimplementasikan dalam bentuk dimensi yang berubah perlahan (SCD).

Berbagai sistem dapat menjadi sumber data dasar, sehingga hampir selalu perlu diintegrasikan.

Dimensi berubah perlahan


Dimensi DIM_REQUEST akan berisi informasi tentang kueri di situs dalam bentuk historis.

Tabel SCD2
 CREATE TABLE DIM_REQUEST ( /* scd table for user requests */ DIM_REQUEST_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1, REQUEST_NK TEXT NOT NULL DEFAULT 'na', /* request without ?parameters */ PAGE_TITLE TEXT NOT NULL DEFAULT 'na', PAGE_DESCR TEXT NOT NULL DEFAULT 'na', PAGE_KEYWORDS TEXT NOT NULL DEFAULT 'na', DELETE_FLAG INTEGER NOT NULL DEFAULT 0, UPDATE_DT INTEGER NOT NULL DEFAULT 0, UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST) ); INSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);


Selain itu, buat satu tampilan yang selalu menampilkan semua catatan di status terakhir. Perlu memuat pengukuran itu sendiri.

gambar

Tampilan SCD2 saat ini
 /* Content: actual view on scd table */ SELECT HI.DIM_REQUEST_ID, HI.DIM_REQUEST_ID_HIST, HI.REQUEST_NK, HI.PAGE_TITLE, HI.PAGE_DESCR, HI.PAGE_KEYWORDS, NK.CNT AS HIST_CNT, HI.DELETE_FLAG, strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT FROM ( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT FROM DIM_REQUEST GROUP BY REQUEST_NK ) NK, DIM_REQUEST HI WHERE 1 = 1 AND NK.REQUEST_NK = HI.REQUEST_NK AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;


Dan pandangan di mana informasi historis dikumpulkan untuk setiap catatan. Penting untuk membangun koneksi historis yang benar dengan fakta.

gambar

Pandangan historis tentang SCD2
 /* Content: actual view on scd table */ SELECT SCD.DIM_REQUEST_ID, SCD.DIM_REQUEST_ID_HIST, SCD.REQUEST_NK, SCD.PAGE_TITLE, SCD.PAGE_DESCR, SCD.PAGE_KEYWORDS, SCD.DELETE_FLAG, CASE WHEN HIS.UPDATE_DT IS NULL THEN 1 ELSE 0 END ACTIVE_FLAG, SCD.DIM_REQUEST_ID_HIST AS ID_FROM, SCD.DIM_REQUEST_ID AS ID_TO, CASE WHEN SCD.DIM_REQUEST_ID_HIST=-1 THEN 3600 ELSE IFNULL(SCD.UPDATE_DT,3600) END AS TIME_FROM, CASE WHEN HIS.UPDATE_DT IS NULL THEN 253370764800 ELSE HIS.UPDATE_DT END AS TIME_TO, CASE WHEN SCD.DIM_REQUEST_ID_HIST=-1 THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch')) ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch')) END AS ACTIVE_FROM, CASE WHEN HIS.UPDATE_DT IS NULL THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch')) ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch')) END AS ACTIVE_TO FROM DIM_REQUEST SCD LEFT OUTER JOIN DIM_REQUEST HIS ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;


Agregasi data


Kompresi (agregasi) memungkinkan Anda untuk mengevaluasi data di tingkat yang lebih tinggi dan mendeteksi anomali dan tren yang tidak terlihat dalam laporan terperinci.

Misalnya, dalam dimensi dengan kode status permintaan DIM_HTTP_STATUS, tambahkan grup:
STATUS / KELOMPOK
0xx / na
1xx / Informasi
2xx / Berhasil
3xx / pengalihan
4xx / Kesalahan Klien
5xx / Kesalahan Server
Dimensi agen pengguna DIM_USER_AGENT akan berisi atribut AGENT_OS dan AGENT_BOT untuk grup. Mereka dapat diisi selama proses ETL:

Unduh DIM_USER_AGENT
 /* Propagate the user agent from access log */ INSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT) WITH CLS AS ( SELECT BROWSER FROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1 GROUP BY BROWSER ) SELECT CLS.BROWSER AS USER_AGENT_NK, CASE WHEN INSTR(CLS.BROWSER,'Macintosh')>0 THEN 'Macintosh' WHEN INSTR(CLS.BROWSER,'iPhone')>0 OR INSTR(CLS.BROWSER,'iPad')>0 OR INSTR(CLS.BROWSER,'iPod')>0 OR INSTR(CLS.BROWSER,'Apple TV')>0 OR INSTR(CLS.BROWSER,'Darwin')>0 THEN 'iOS' WHEN INSTR(CLS.BROWSER,'Android')>0 THEN 'Android' WHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0 THEN 'Linux' WHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0 THEN 'BlackBerry' WHEN INSTR(CLS.BROWSER,'Windows')>0 THEN 'Windows' ELSE 'na' END AS AGENT_OS, -- OS CASE WHEN INSTR(CLS.BROWSER,'AppleCoreMedia')>0 THEN 'AppleWebKit' WHEN INSTR(CLS.BROWSER,') ')>1 AND LENGTH(CLS.BROWSER)>INSTR(CLS.BROWSER,') ') THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,') ')+2, LENGTH(CLS.BROWSER) - INSTR(CLS.BROWSER,') ')-1), 'N/A') ELSE 'na' END AS AGENT_ENGINE, -- Engine CASE WHEN INSTR(CLS.BROWSER,'iPhone')>0 THEN 'iPhone' WHEN INSTR(CLS.BROWSER,'iPad')>0 THEN 'iPad' WHEN INSTR(CLS.BROWSER,'iPod')>0 THEN 'iPod' WHEN INSTR(CLS.BROWSER,'Apple TV')>0 THEN 'Apple TV' WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'Build')>0 THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'Build')-INSTR(CLS.BROWSER,'Android ')), 'na') WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'MIUI')>0 THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'MIUI')-INSTR(CLS.BROWSER,'Android ')), 'na') ELSE 'na' END AS AGENT_DEVICE, -- Device 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),'jobboersebot')>0 OR INSTR(LOWER(CLS.BROWSER),'jobkicks')>0 THEN 'job.de' WHEN INSTR(LOWER(CLS.BROWSER),'mail.ru')>0 THEN 'mail.ru' WHEN INSTR(LOWER(CLS.BROWSER),'baiduspider')>0 THEN 'baidu' WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0 THEN 'majestic-12' WHEN INSTR(LOWER(CLS.BROWSER),'duckduckgo')>0 THEN 'duckduckgo' WHEN INSTR(LOWER(CLS.BROWSER),'bytespider')>0 THEN 'bytespider' WHEN INSTR(LOWER(CLS.BROWSER),'360spider')>0 THEN 'so.360.cn' 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, -- Bot STRFTIME('%s','now') AS UPDATE_DT FROM CLS LEFT OUTER JOIN DIM_USER_AGENT TRG ON CLS.BROWSER = TRG.USER_AGENT_NK WHERE TRG.DIM_USER_AGENT_ID IS NULL


Integrasi data


Ini mencakup pengaturan transfer data dari sistem operasi ke sistem pelaporan. Untuk melakukan ini, buat tabel panggung dengan struktur yang mirip dengan sumbernya.

Informasi tentang halaman web sampai ke tahap dari cadangan CMS dalam bentuk permintaan memasukkan.

Memuat tabel DIM_REQUEST historis dengan data dasar membutuhkan tiga langkah: memuat kunci dan atribut baru, memperbarui yang sudah ada dan memperbaiki catatan yang dihapus.

Unduh entri SCD2 baru
 /* Load request table SCD from master data */ INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS ( -- prepare keys SELECT '/' || NAME AS REQUEST_NK, TITLE AS PAGE_TITLE, CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL THEN 'na' ELSE DESCRIPTION END AS PAGE_DESCR, CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL THEN 'na' ELSE KEYWORDS END AS PAGE_KEYWORDS FROM STG_CMS_MENU WHERE CONTENT_TYPE != 'folder' -- only web pages AND PAGE_TITLE != 'na' -- master data which make sense ) /* new records from stage: CLS */ SELECT -1 AS DIM_REQUEST_ID_HIST, CLS.REQUEST_NK, CLS.PAGE_TITLE, CLS.PAGE_DESCR, CLS.PAGE_KEYWORDS, 0 AS DELETE_FLAG, STRFTIME('%s','now') AS UPDATE_DT FROM CLS LEFT OUTER JOIN ( SELECT DIM_REQUEST_ID, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS FROM DIM_REQUEST_V_ACT ) TRG ON CLS.REQUEST_NK = TRG.REQUEST_NK WHERE TRG.REQUEST_NK IS NULL -- no such record in data mart


Perbarui Atribut SCD2
 /* Load request table SCD from master data */ INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS ( -- prepare keys SELECT '/' || NAME AS REQUEST_NK, TITLE AS PAGE_TITLE, CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL THEN 'na' ELSE DESCRIPTION END AS PAGE_DESCR, CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL THEN 'na' ELSE KEYWORDS END AS PAGE_KEYWORDS FROM STG_CMS_MENU WHERE CONTENT_TYPE != 'folder' -- only web pages AND PAGE_TITLE != 'na' -- master data which make sense ) /* updated records from stage: CLS and build reference to history: HIST */ SELECT HIST.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST, HIST.REQUEST_NK, CLS.PAGE_TITLE, CLS.PAGE_DESCR, CLS.PAGE_KEYWORDS, 0 AS DELETE_FLAG, STRFTIME('%s','now') AS UPDATE_DT FROM CLS, DIM_REQUEST_V_ACT TRG, DIM_REQUEST HIST WHERE CLS.REQUEST_NK = TRG.REQUEST_NK AND TRG.DIM_REQUEST_ID = HIST.DIM_REQUEST_ID AND ( CLS.PAGE_TITLE != HIST.PAGE_TITLE /* changes only */ OR CLS.PAGE_DESCR != HIST.PAGE_DESCR OR CLS.PAGE_KEYWORDS != HIST.PAGE_KEYWORDS )


Entri SCD2 yang dihapus
 /* Load request table SCD from master data */ INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS ( -- prepare keys SELECT '/' || NAME AS REQUEST_NK, TITLE AS PAGE_TITLE FROM STG_CMS_MENU WHERE CONTENT_TYPE != 'folder' -- only web pages AND PAGE_TITLE != 'na' -- master data which make sense ) /* deleted records in data mart: TRG */ SELECT TRG.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST, TRG.REQUEST_NK, TRG.PAGE_TITLE, TRG.PAGE_DESCR, TRG.PAGE_KEYWORDS, 1 AS DELETE_FLAG, STRFTIME('%s','now') AS UPDATE_DT FROM ( SELECT DIM_REQUEST_ID, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS FROM DIM_REQUEST_V_ACT WHERE PAGE_TITLE != 'na' -- track master data only AND DELETE_FLAG = 0 -- not already deleted ) TRG LEFT OUTER JOIN CLS ON TRG.REQUEST_NK = CLS.REQUEST_NK WHERE CLS.REQUEST_NK IS NULL -- no such record in stage


Setiap sumber data harus disertai dengan deskripsi formal, misalnya, dalam file readme.txt:
Penerima secara formal / teknis: nama, alamat email
Penyedia Data Secara Resmi / Teknis: Nama, Email
Sumber data: jalur file, nama layanan
Informasi Akses Data: Pengguna dan Kata Sandi

Skema perpindahan data akan membantu dalam proses pemeliharaan dan pembaruan, misalnya, dalam bentuk teks:
Memindahkan file. Sumber: ftp.domain.net: /logs/access.log Target: /var/www/access.log
Membaca di panggung. Target: STG_ACCESS_LOG
Pengunduhan dan transformasi. Target: FCT_ACCESS_REQUEST_REF_HH
Pengunduhan dan transformasi. Target: FCT_ACCESS_USER_AGENT_DD
Laporkan. Target: /var/www/report.html

Kesimpulan


Dengan demikian, artikel tersebut menjelaskan mekanisme seperti integrasi data dasar dan pengenalan tingkat agregasi baru. Mereka diperlukan saat membangun gudang data untuk mendapatkan pengetahuan tambahan dan meningkatkan kualitas informasi.

Source: https://habr.com/ru/post/id463493/

More articles:


All Articles