Diff -- /var/www/vhosts/3dshawn.com/crm.3dshawn.com/MODS/ContactForge/Stats.pm
Diff

/var/www/vhosts/3dshawn.com/crm.3dshawn.com/MODS/ContactForge/Stats.pm

added on local at 2026-07-01 15:02:55

Added
+835
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 6a0470534c9e
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1package MODS::ContactForge::Stats;
2#======================================================================
3# ContactForge -- analytics rollups for the admin dashboards.
4# Single-tenant: every query covers ALL traffic to the CRM marketing
5# site -- no site_id filter anywhere.
6#
7# Schema (db_schema.sql): visitors / tracking_sessions / pageviews /
8# events / heatmap_clicks / heatmap_scrolls.
9#======================================================================
10use strict;
11use warnings;
12
13sub new { bless {}, shift }
14
15# ---- Range model ----------------------------------------------------
16# Every query method honors the active range set via set_range($days).
17# $days can be:
18# * an integer N -- rolling window: NOW() - N DAY .. NOW()
19# * a hashref {from, to} with 'YYYY-MM-DD HH:MM:SS' strings -- absolute
20# * undef -- default 30-day rolling window
21#
22# Methods read $self->_since and $self->_until and inline them as
23# raw SQL fragments -- both are quoted/wrapped appropriately by _range().
24sub _range {
25 my ($spec) = @_;
26 if (ref($spec) eq 'HASH' && $spec->{from} && $spec->{to}) {
27 my $f = $spec->{from}; $f =~ s/[^0-9 :-]//g;
28 my $t = $spec->{to}; $t =~ s/[^0-9 :-]//g;
29 if ($f && $t) { return ("'$f'", "'$t'"); }
30 }
31 my $d = (defined $spec && !ref($spec) && $spec =~ /^\d+$/) ? int($spec) : 30;
32 $d = 1 if $d < 1;
33 $d = 730 if $d > 730; # 2-year safety cap
34 return ("DATE_SUB(NOW(), INTERVAL $d DAY)", 'NOW()');
35}
36sub set_range {
37 my ($self, $spec) = @_;
38 my ($s, $u) = _range($spec);
39 $self->{_since} = $s;
40 $self->{_until} = $u;
41 return $self;
42}
43sub _since { $_[0]->{_since} // 'DATE_SUB(NOW(), INTERVAL 30 DAY)' }
44sub _until { $_[0]->{_until} // 'NOW()' }
45
46# ---- KPIs in a trailing N-day window --------------------------------
47sub kpis {
48 my ($self, $db, $dbh, $DB, $days) = @_;
49 my $since = $self->_since;
50 my $until = $self->_until;
51 $days = int($days || 30); $days = 365 if $days > 365; $days = 1 if $days < 1;
52 my $kpi = {};
53
54 my $r = $db->db_readwrite($dbh, qq~
55 SELECT COUNT(*) AS pageviews
56 FROM `${DB}`.pageviews
57 WHERE occurred_at BETWEEN $since AND $until
58 ~, $ENV{SCRIPT_NAME}, __LINE__);
59 $kpi->{pageviews} = ($r && $r->{pageviews}) ? $r->{pageviews} : 0;
60
61 $r = $db->db_readwrite($dbh, qq~
62 SELECT COUNT(DISTINCT visitor_id) AS visitors
63 FROM `${DB}`.tracking_sessions
64 WHERE last_seen_at BETWEEN $since AND $until
65 ~, $ENV{SCRIPT_NAME}, __LINE__);
66 $kpi->{visitors} = ($r && $r->{visitors}) ? $r->{visitors} : 0;
67
68 $r = $db->db_readwrite($dbh, qq~
69 SELECT COUNT(*) AS sessions,
70 AVG(duration_seconds) AS avg_seconds,
71 AVG(CASE WHEN pageviews <= 1 THEN 1 ELSE 0 END) AS bounce_rate,
72 AVG(pageviews) AS pv_per_session
73 FROM `${DB}`.tracking_sessions
74 WHERE last_seen_at BETWEEN $since AND $until
75 ~, $ENV{SCRIPT_NAME}, __LINE__);
76 $kpi->{sessions} = ($r && $r->{sessions}) ? $r->{sessions} : 0;
77 $kpi->{avg_seconds} = sprintf('%.0f', ($r && $r->{avg_seconds}) || 0);
78 $kpi->{bounce_rate} = sprintf('%.1f', 100 * (($r && $r->{bounce_rate}) || 0));
79 $kpi->{pv_per_session} = sprintf('%.2f', ($r && $r->{pv_per_session}) || 0);
80
81 return $kpi;
82}
83
84# ---- Daily pageviews + visitors series ------------------------------
85sub daily_series {
86 my ($self, $db, $dbh, $DB, $days) = @_;
87 my $since = $self->_since;
88 my $until = $self->_until;
89 $days = int($days || 30);
90 my @pv = $db->db_readwrite_multiple($dbh, qq~
91 SELECT DATE(occurred_at) AS d, COUNT(*) AS n
92 FROM `${DB}`.pageviews
93 WHERE occurred_at BETWEEN $since AND $until
94 GROUP BY DATE(occurred_at)
95 ORDER BY d
96 ~, $ENV{SCRIPT_NAME}, __LINE__);
97 my @vs = $db->db_readwrite_multiple($dbh, qq~
98 SELECT DATE(last_seen_at) AS d, COUNT(DISTINCT visitor_id) AS n
99 FROM `${DB}`.tracking_sessions
100 WHERE last_seen_at BETWEEN $since AND $until
101 GROUP BY DATE(last_seen_at)
102 ORDER BY d
103 ~, $ENV{SCRIPT_NAME}, __LINE__);
104
105 my %pv_by = map { $_->{d} => $_->{n} } @pv;
106 my %vs_by = map { $_->{d} => $_->{n} } @vs;
107 my (@labels, @pvv, @vsv);
108 for (my $i = $days - 1; $i >= 0; $i--) {
109 my @t = localtime(time - $i * 86400);
110 my $d = sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
111 push @labels, $d;
112 push @pvv, ($pv_by{$d} || 0) + 0;
113 push @vsv, ($vs_by{$d} || 0) + 0;
114 }
115 return { labels => \@labels, pageviews => \@pvv, visitors => \@vsv };
116}
117
118sub by_country {
119 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
120 my $since = $self->_since;
121 my $until = $self->_until;
122 $days = int($days || 30); $limit = int($limit || 20);
123 return $db->db_readwrite_multiple($dbh, qq~
124 SELECT COALESCE(country,'') AS country,
125 COUNT(DISTINCT visitor_id) AS visitors,
126 COUNT(*) AS sessions
127 FROM `${DB}`.tracking_sessions
128 WHERE last_seen_at BETWEEN $since AND $until
129 GROUP BY country
130 ORDER BY visitors DESC LIMIT $limit
131 ~, $ENV{SCRIPT_NAME}, __LINE__);
132}
133
134sub by_device {
135 my ($self, $db, $dbh, $DB, $days) = @_;
136 my $since = $self->_since;
137 my $until = $self->_until;
138 $days = int($days || 30);
139 return $db->db_readwrite_multiple($dbh, qq~
140 SELECT COALESCE(device_type,'unknown') AS device_type,
141 COUNT(*) AS sessions,
142 COUNT(DISTINCT visitor_id) AS visitors
143 FROM `${DB}`.tracking_sessions
144 WHERE last_seen_at BETWEEN $since AND $until
145 GROUP BY device_type
146 ORDER BY sessions DESC
147 ~, $ENV{SCRIPT_NAME}, __LINE__);
148}
149
150sub by_browser {
151 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
152 my $since = $self->_since;
153 my $until = $self->_until;
154 $days = int($days || 30); $limit = int($limit || 15);
155 return $db->db_readwrite_multiple($dbh, qq~
156 SELECT COALESCE(NULLIF(browser_name,''),'Unknown') AS browser,
157 COUNT(*) AS sessions
158 FROM `${DB}`.tracking_sessions
159 WHERE last_seen_at BETWEEN $since AND $until
160 GROUP BY browser_name
161 ORDER BY sessions DESC LIMIT $limit
162 ~, $ENV{SCRIPT_NAME}, __LINE__);
163}
164
165sub by_os {
166 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
167 my $since = $self->_since;
168 my $until = $self->_until;
169 $days = int($days || 30); $limit = int($limit || 15);
170 return $db->db_readwrite_multiple($dbh, qq~
171 SELECT COALESCE(NULLIF(os_name,''),'Unknown') AS os,
172 COUNT(*) AS sessions
173 FROM `${DB}`.tracking_sessions
174 WHERE last_seen_at BETWEEN $since AND $until
175 GROUP BY os_name
176 ORDER BY sessions DESC LIMIT $limit
177 ~, $ENV{SCRIPT_NAME}, __LINE__);
178}
179
180sub by_resolution {
181 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
182 my $since = $self->_since;
183 my $until = $self->_until;
184 $days = int($days || 30); $limit = int($limit || 15);
185 return $db->db_readwrite_multiple($dbh, qq~
186 SELECT CONCAT(COALESCE(screen_w,'?'),' x ',COALESCE(screen_h,'?')) AS resolution,
187 COUNT(*) AS sessions
188 FROM `${DB}`.tracking_sessions
189 WHERE last_seen_at BETWEEN $since AND $until
190 GROUP BY screen_w, screen_h
191 ORDER BY sessions DESC LIMIT $limit
192 ~, $ENV{SCRIPT_NAME}, __LINE__);
193}
194
195sub by_source {
196 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
197 my $since = $self->_since;
198 my $until = $self->_until;
199 $days = int($days || 30); $limit = int($limit || 15);
200 return $db->db_readwrite_multiple($dbh, qq~
201 SELECT COALESCE(NULLIF(source_kind,''),'direct') AS source_kind,
202 COUNT(*) AS sessions
203 FROM `${DB}`.tracking_sessions
204 WHERE last_seen_at BETWEEN $since AND $until
205 GROUP BY source_kind
206 ORDER BY sessions DESC LIMIT $limit
207 ~, $ENV{SCRIPT_NAME}, __LINE__);
208}
209
210sub by_referrer_host {
211 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
212 my $since = $self->_since;
213 my $until = $self->_until;
214 $days = int($days || 30); $limit = int($limit || 20);
215 return $db->db_readwrite_multiple($dbh, qq~
216 SELECT COALESCE(NULLIF(referrer_host,''),'(direct)') AS host,
217 COUNT(*) AS sessions
218 FROM `${DB}`.tracking_sessions
219 WHERE last_seen_at BETWEEN $since AND $until
220 GROUP BY referrer_host
221 ORDER BY sessions DESC LIMIT $limit
222 ~, $ENV{SCRIPT_NAME}, __LINE__);
223}
224
225sub by_utm_campaign {
226 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
227 my $since = $self->_since;
228 my $until = $self->_until;
229 $days = int($days || 30); $limit = int($limit || 20);
230 return $db->db_readwrite_multiple($dbh, qq~
231 SELECT COALESCE(NULLIF(utm_campaign,''),'(none)') AS campaign,
232 COALESCE(NULLIF(utm_source,''),'(none)') AS source,
233 COALESCE(NULLIF(utm_medium,''),'(none)') AS medium,
234 COUNT(*) AS sessions
235 FROM `${DB}`.tracking_sessions
236 WHERE last_seen_at BETWEEN $since AND $until
237 AND (utm_campaign IS NOT NULL OR utm_source IS NOT NULL)
238 AND NOT (COALESCE(utm_campaign,'')='' AND COALESCE(utm_source,'')='')
239 GROUP BY utm_campaign, utm_source, utm_medium
240 ORDER BY sessions DESC LIMIT $limit
241 ~, $ENV{SCRIPT_NAME}, __LINE__);
242}
243
244sub by_page {
245 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
246 my $since = $self->_since;
247 my $until = $self->_until;
248 $days = int($days || 30); $limit = int($limit || 25);
249 return $db->db_readwrite_multiple($dbh, qq~
250 SELECT path AS page_path,
251 COUNT(*) AS pageviews,
252 COUNT(DISTINCT session_id) AS sessions
253 FROM `${DB}`.pageviews
254 WHERE occurred_at BETWEEN $since AND $until
255 GROUP BY path
256 ORDER BY pageviews DESC LIMIT $limit
257 ~, $ENV{SCRIPT_NAME}, __LINE__);
258}
259
260sub by_landing_page {
261 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
262 my $since = $self->_since;
263 my $until = $self->_until;
264 $days = int($days || 30); $limit = int($limit || 20);
265 return $db->db_readwrite_multiple($dbh, qq~
266 SELECT COALESCE(NULLIF(landing_path,''),'/') AS entry_page,
267 COUNT(*) AS sessions,
268 AVG(CASE WHEN pageviews <= 1 THEN 1 ELSE 0 END) AS bounce_rate
269 FROM `${DB}`.tracking_sessions
270 WHERE last_seen_at BETWEEN $since AND $until
271 GROUP BY landing_path
272 ORDER BY sessions DESC LIMIT $limit
273 ~, $ENV{SCRIPT_NAME}, __LINE__);
274}
275
276# Count of distinct visitors currently active (last 2 minutes).
277sub live_visitors {
278 my ($self, $db, $dbh, $DB) = @_;
279 my $since = $self->_since;
280 my $until = $self->_until;
281 my $r = $db->db_readwrite($dbh, qq~
282 SELECT COUNT(DISTINCT visitor_id) AS live
283 FROM `${DB}`.tracking_sessions
284 WHERE last_seen_at >= DATE_SUB(NOW(), INTERVAL 2 MINUTE)
285 ~, $ENV{SCRIPT_NAME}, __LINE__);
286 return ($r && $r->{live}) ? $r->{live} : 0;
287}
288
289# Recent sessions (with their current page).
290sub live_sessions {
291 my ($self, $db, $dbh, $DB, $limit) = @_;
292 my $since = $self->_since;
293 my $until = $self->_until;
294 $limit = int($limit || 20);
295 return $db->db_readwrite_multiple($dbh, qq~
296 SELECT s.id AS session_id, s.visitor_id, s.country, s.city,
297 s.device_type, s.browser_name, s.os_name,
298 s.referrer_host, s.landing_path AS entry_page,
299 s.exit_path AS current_page,
300 s.pageviews, s.duration_seconds, s.last_seen_at,
301 TIMESTAMPDIFF(SECOND, s.last_seen_at, NOW()) AS idle_seconds
302 FROM `${DB}`.tracking_sessions s
303 WHERE s.last_seen_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
304 ORDER BY s.last_seen_at DESC LIMIT $limit
305 ~, $ENV{SCRIPT_NAME}, __LINE__);
306}
307
308# Hourly heat-strip for the last 7 days (24 bins x 7 rows).
309sub hourly_heat {
310 my ($self, $db, $dbh, $DB) = @_;
311 my $since = $self->_since;
312 my $until = $self->_until;
313 my @rows = $db->db_readwrite_multiple($dbh, qq~
314 SELECT DATE(occurred_at) AS d, HOUR(occurred_at) AS h, COUNT(*) AS n
315 FROM `${DB}`.pageviews
316 WHERE occurred_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
317 GROUP BY DATE(occurred_at), HOUR(occurred_at)
318 ~, $ENV{SCRIPT_NAME}, __LINE__);
319 my %m;
320 foreach my $r (@rows) { $m{"$r->{d}-$r->{h}"} = $r->{n}; }
321 my (@days, @grid);
322 for (my $i = 6; $i >= 0; $i--) {
323 my @t = localtime(time - $i * 86400);
324 my $d = sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
325 push @days, $d;
326 my @row;
327 for my $h (0..23) { push @row, ($m{"$d-$h"} || 0) + 0; }
328 push @grid, \@row;
329 }
330 return { days => \@days, grid => \@grid };
331}
332
333# Recent pageviews timeline -- visitor + path + when.
334sub recent_pageviews {
335 my ($self, $db, $dbh, $DB, $limit) = @_;
336 my $since = $self->_since;
337 my $until = $self->_until;
338 $limit = int($limit || 30);
339 return $db->db_readwrite_multiple($dbh, qq~
340 SELECT pv.id, pv.visitor_id, pv.session_id, pv.path, pv.title,
341 pv.referrer, pv.occurred_at,
342 TIMESTAMPDIFF(SECOND, pv.occurred_at, NOW()) AS ago_seconds,
343 s.country, s.device_type, s.browser_name, s.os_name
344 FROM `${DB}`.pageviews pv
345 LEFT JOIN `${DB}`.tracking_sessions s ON s.id = pv.session_id
346 ORDER BY pv.occurred_at DESC LIMIT $limit
347 ~, $ENV{SCRIPT_NAME}, __LINE__);
348}
349
350# Top pages ranked by click activity (for the heatmap page picker).
351sub heatmap_top_pages {
352 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
353 my $since = $self->_since;
354 my $until = $self->_until;
355 $days = int($days || 30);
356 $limit = int($limit || 50);
357 return $db->db_readwrite_multiple($dbh, qq~
358 SELECT path AS page_path,
359 COUNT(*) AS clicks,
360 COUNT(DISTINCT session_id) AS sessions
361 FROM `${DB}`.heatmap_clicks
362 WHERE occurred_at BETWEEN $since AND $until
363 GROUP BY path
364 ORDER BY clicks DESC LIMIT $limit
365 ~, $ENV{SCRIPT_NAME}, __LINE__);
366}
367
368# Click dots for a single page-path.
369sub heatmap_dots {
370 my ($self, $db, $dbh, $DB, $path, $limit) = @_;
371 my $since = $self->_since;
372 my $until = $self->_until;
373 return () unless defined $path && length $path;
374 $limit = int($limit || 500);
375 my $p = $path; $p =~ s/'/''/g; $p = substr($p, 0, 500);
376 return $db->db_readwrite_multiple($dbh, qq~
377 SELECT x_pct, y_pct, selector, viewport_w, viewport_h
378 FROM `${DB}`.heatmap_clicks
379 WHERE path='$p' AND x_pct IS NOT NULL AND y_pct IS NOT NULL
380 ORDER BY occurred_at DESC
381 LIMIT $limit
382 ~, $ENV{SCRIPT_NAME}, __LINE__);
383}
384
385#======================================================================
386# Tracker parity extensions (added with _tracker_parity_migration)
387#
388# Globe / per-country drill + breakdowns for the new fingerprint cols
389# (touch_capable, viewport_*, color_depth, pixel_ratio).
390# Lifted ISO-2 country centroid table from PTMatrix.
391#======================================================================
392
393# ISO-3166-1 alpha-2 -> [lat, lng, display name]
394my %COUNTRY_CENTROIDS = (
395 US => [ 39.5, -98.5, 'United States' ],
396 CA => [ 56.1, -106.3, 'Canada' ],
397 MX => [ 23.6, -102.5, 'Mexico' ],
398 GT => [ 15.8, -90.2, 'Guatemala' ],
399 CR => [ 9.7, -83.7, 'Costa Rica' ],
400 PA => [ 8.5, -80.7, 'Panama' ],
401 CU => [ 21.5, -77.7, 'Cuba' ],
402 DO => [ 18.7, -70.1, 'Dominican Republic' ],
403 BR => [ -14.2, -51.9, 'Brazil' ],
404 AR => [ -38.4, -63.6, 'Argentina' ],
405 CL => [ -35.6, -71.5, 'Chile' ],
406 CO => [ 4.6, -74.0, 'Colombia' ],
407 PE => [ -9.2, -75.0, 'Peru' ],
408 VE => [ 6.4, -66.6, 'Venezuela' ],
409 EC => [ -1.8, -78.2, 'Ecuador' ],
410 UY => [ -32.5, -55.8, 'Uruguay' ],
411 GB => [ 55.4, -3.4, 'United Kingdom' ],
412 IE => [ 53.4, -8.2, 'Ireland' ],
413 FR => [ 46.2, 2.2, 'France' ],
414 DE => [ 51.2, 10.4, 'Germany' ],
415 NL => [ 52.1, 5.3, 'Netherlands' ],
416 BE => [ 50.5, 4.5, 'Belgium' ],
417 LU => [ 49.8, 6.1, 'Luxembourg' ],
418 CH => [ 46.8, 8.2, 'Switzerland' ],
419 AT => [ 47.5, 14.6, 'Austria' ],
420 IT => [ 41.9, 12.6, 'Italy' ],
421 ES => [ 40.5, -3.7, 'Spain' ],
422 PT => [ 39.4, -8.2, 'Portugal' ],
423 DK => [ 56.3, 9.5, 'Denmark' ],
424 NO => [ 60.5, 8.5, 'Norway' ],
425 SE => [ 60.1, 18.6, 'Sweden' ],
426 FI => [ 61.9, 25.7, 'Finland' ],
427 PL => [ 51.9, 19.1, 'Poland' ],
428 CZ => [ 49.8, 15.5, 'Czechia' ],
429 SK => [ 48.7, 19.7, 'Slovakia' ],
430 HU => [ 47.2, 19.5, 'Hungary' ],
431 RO => [ 45.9, 25.0, 'Romania' ],
432 BG => [ 42.7, 25.5, 'Bulgaria' ],
433 GR => [ 39.1, 21.8, 'Greece' ],
434 UA => [ 48.4, 31.2, 'Ukraine' ],
435 RU => [ 61.5, 105.3, 'Russia' ],
436 TR => [ 38.9, 35.2, 'Turkey' ],
437 IL => [ 31.0, 34.9, 'Israel' ],
438 SA => [ 23.9, 45.1, 'Saudi Arabia' ],
439 AE => [ 23.4, 53.8, 'United Arab Emirates' ],
440 IR => [ 32.4, 53.7, 'Iran' ],
441 PK => [ 30.4, 69.3, 'Pakistan' ],
442 IN => [ 20.6, 78.9, 'India' ],
443 BD => [ 23.7, 90.4, 'Bangladesh' ],
444 LK => [ 7.9, 80.8, 'Sri Lanka' ],
445 NP => [ 28.4, 84.1, 'Nepal' ],
446 CN => [ 35.9, 104.2, 'China' ],
447 JP => [ 36.2, 138.3, 'Japan' ],
448 KR => [ 35.9, 127.8, 'South Korea' ],
449 TW => [ 23.7, 121.0, 'Taiwan' ],
450 HK => [ 22.4, 114.1, 'Hong Kong' ],
451 SG => [ 1.4, 103.8, 'Singapore' ],
452 MY => [ 4.2, 101.9, 'Malaysia' ],
453 TH => [ 15.9, 100.9, 'Thailand' ],
454 VN => [ 14.1, 108.3, 'Vietnam' ],
455 PH => [ 12.9, 121.8, 'Philippines' ],
456 ID => [ -0.8, 113.9, 'Indonesia' ],
457 AU => [ -25.3, 133.8, 'Australia' ],
458 NZ => [ -40.9, 174.9, 'New Zealand' ],
459 ZA => [ -30.6, 22.9, 'South Africa' ],
460 NG => [ 9.1, 8.7, 'Nigeria' ],
461 EG => [ 26.8, 30.8, 'Egypt' ],
462 MA => [ 31.8, -7.1, 'Morocco' ],
463 KE => [ -0.0, 37.9, 'Kenya' ],
464 ET => [ 9.1, 40.5, 'Ethiopia' ],
465 GH => [ 7.9, -1.0, 'Ghana' ],
466 DZ => [ 28.0, 1.7, 'Algeria' ],
467 TN => [ 33.9, 9.5, 'Tunisia' ],
468);
469
470sub country_centroid {
471 my ($self, $code) = @_;
472 return undef unless defined $code;
473 $code = uc $code;
474 $code =~ s/[^A-Z]//g;
475 return undef unless length($code) == 2;
476 return $COUNTRY_CENTROIDS{$code};
477}
478
479# Country breakdown enriched with lat/lng for the globe widget.
480# Returns rows: { code, name, lat, lng, visitors, sessions }.
481# Filters to country_iso IS NOT NULL so the globe only plots known dots.
482sub by_country_geo {
483 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
484 my $since = $self->_since;
485 my $until = $self->_until;
486 $days = int($days || 30);
487 $limit = int($limit || 80);
488 my @rows = $db->db_readwrite_multiple($dbh, qq~
489 SELECT UPPER(country_iso) AS code,
490 COUNT(DISTINCT visitor_id) AS visitors,
491 COUNT(*) AS sessions
492 FROM `${DB}`.tracking_sessions
493 WHERE country_iso IS NOT NULL AND country_iso <> ''
494 AND last_seen_at BETWEEN $since AND $until
495 GROUP BY country_iso
496 ORDER BY visitors DESC
497 LIMIT $limit
498 ~, $ENV{SCRIPT_NAME}, __LINE__);
499 my @out;
500 foreach my $r (@rows) {
501 my $c = $self->country_centroid($r->{code});
502 next unless $c;
503 push @out, {
504 code => $r->{code},
505 name => $c->[2],
506 lat => $c->[0],
507 lng => $c->[1],
508 visitors => ($r->{visitors} || 0) + 0,
509 sessions => ($r->{sessions} || 0) + 0,
510 };
511 }
512 return @out;
513}
514
515# Click-on-globe drill: regions + cities + per-day inside a single country.
516sub country_drill {
517 my ($self, $db, $dbh, $DB, $iso, $days) = @_;
518 my $since = $self->_since;
519 my $until = $self->_until;
520 $iso =~ s/[^A-Za-z]//g if defined $iso;
521 return {} unless defined($iso) && length($iso) == 2;
522 $iso = uc $iso;
523 $days = int($days || 30);
524 my @regions = $db->db_readwrite_multiple($dbh, qq~
525 SELECT COALESCE(NULLIF(region,''),'(unknown)') AS region,
526 COUNT(DISTINCT visitor_id) AS visitors,
527 COUNT(*) AS sessions
528 FROM `${DB}`.tracking_sessions
529 WHERE UPPER(country_iso)='$iso'
530 AND last_seen_at BETWEEN $since AND $until
531 GROUP BY region
532 ORDER BY visitors DESC
533 LIMIT 25
534 ~, $ENV{SCRIPT_NAME}, __LINE__);
535 my @cities = $db->db_readwrite_multiple($dbh, qq~
536 SELECT COALESCE(NULLIF(city,''),'(unknown)') AS city,
537 COALESCE(NULLIF(region,''),'') AS region,
538 COUNT(DISTINCT visitor_id) AS visitors,
539 COUNT(*) AS sessions
540 FROM `${DB}`.tracking_sessions
541 WHERE UPPER(country_iso)='$iso'
542 AND last_seen_at BETWEEN $since AND $until
543 GROUP BY city, region
544 ORDER BY visitors DESC
545 LIMIT 25
546 ~, $ENV{SCRIPT_NAME}, __LINE__);
547 my $tot = $db->db_readwrite($dbh, qq~
548 SELECT COUNT(DISTINCT visitor_id) AS visitors,
549 COUNT(*) AS sessions
550 FROM `${DB}`.tracking_sessions
551 WHERE UPPER(country_iso)='$iso'
552 AND last_seen_at BETWEEN $since AND $until
553 ~, $ENV{SCRIPT_NAME}, __LINE__);
554 return {
555 iso => $iso,
556 name => ($self->country_centroid($iso) || [0,0,$iso])->[2],
557 visitors => ($tot && $tot->{visitors}) ? $tot->{visitors} + 0 : 0,
558 sessions => ($tot && $tot->{sessions}) ? $tot->{sessions} + 0 : 0,
559 regions => [ @regions ],
560 cities => [ @cities ],
561 };
562}
563
564# ---- Fingerprint breakdowns (new cols from parity migration) ----------
565sub by_touch_capable {
566 my ($self, $db, $dbh, $DB, $days) = @_;
567 my $since = $self->_since;
568 my $until = $self->_until;
569 $days = int($days || 30);
570 return $db->db_readwrite_multiple($dbh, qq~
571 SELECT IF(touch_capable=1,'Touch','No touch') AS touch,
572 COUNT(*) AS sessions
573 FROM `${DB}`.tracking_sessions
574 WHERE last_seen_at BETWEEN $since AND $until
575 GROUP BY touch_capable
576 ORDER BY sessions DESC
577 ~, $ENV{SCRIPT_NAME}, __LINE__);
578}
579
580sub by_viewport {
581 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
582 my $since = $self->_since;
583 my $until = $self->_until;
584 $days = int($days || 30);
585 $limit = int($limit || 12);
586 return $db->db_readwrite_multiple($dbh, qq~
587 SELECT CONCAT(COALESCE(viewport_width,'?'),' x ',COALESCE(viewport_height,'?')) AS viewport,
588 COUNT(*) AS sessions
589 FROM `${DB}`.tracking_sessions
590 WHERE last_seen_at BETWEEN $since AND $until
591 AND viewport_width IS NOT NULL
592 AND viewport_height IS NOT NULL
593 GROUP BY viewport_width, viewport_height
594 ORDER BY sessions DESC LIMIT $limit
595 ~, $ENV{SCRIPT_NAME}, __LINE__);
596}
597
598sub by_pixel_ratio {
599 my ($self, $db, $dbh, $DB, $days) = @_;
600 my $since = $self->_since;
601 my $until = $self->_until;
602 $days = int($days || 30);
603 return $db->db_readwrite_multiple($dbh, qq~
604 SELECT CAST(pixel_ratio AS CHAR(8)) AS pixel_ratio,
605 COUNT(*) AS sessions
606 FROM `${DB}`.tracking_sessions
607 WHERE last_seen_at BETWEEN $since AND $until
608 AND pixel_ratio IS NOT NULL
609 GROUP BY pixel_ratio
610 ORDER BY sessions DESC
611 ~, $ENV{SCRIPT_NAME}, __LINE__);
612}
613
614#---------------------------------------------------------------------
615# State-level rollup for globe.js zoom-in state dots.
616# Returns: { US => { 'New York' => 25, 'Colorado' => 8, ... }, ... }
617#---------------------------------------------------------------------
618sub state_rollup {
619 my ($self, $db, $dbh, $DB) = @_;
620 my $since = $self->_since; my $until = $self->_until;
621 my @rows = $db->db_readwrite_multiple($dbh, qq~
622 SELECT UPPER(country_iso) AS code, region,
623 COUNT(DISTINCT visitor_id) AS visitors
624 FROM `${DB}`.tracking_sessions
625 WHERE country_iso IS NOT NULL AND CHAR_LENGTH(country_iso)=2
626 AND region IS NOT NULL AND region<>''
627 AND last_seen_at BETWEEN $since AND $until
628 GROUP BY country_iso, region
629 ~, $ENV{SCRIPT_NAME}, __LINE__);
630 my %out;
631 foreach my $r (@rows) {
632 $out{ $r->{code} } ||= {};
633 $out{ $r->{code} }{ $r->{region} } += ($r->{visitors} || 0);
634 }
635 return \%out;
636}
637
638#---------------------------------------------------------------------
639# Per-country dimension breakdowns (browser/OS/device/etc) for the modal.
640# Returns: { US => { browsers => {Chrome=>200, ...}, os=>{...}, ... }, ... }
641#---------------------------------------------------------------------
642sub country_dimensions_rollup {
643 my ($self, $db, $dbh, $DB) = @_;
644 my $since = $self->_since; my $until = $self->_until;
645 my @dims = (
646 { key => 'browsers', col => "COALESCE(NULLIF(browser_name,''),'Unknown')" },
647 { key => 'os', col => "COALESCE(NULLIF(os_name,''),'Unknown')" },
648 { key => 'devices', col => "COALESCE(device_type,'unknown')" },
649 { key => 'resolutions', col => "CONCAT(COALESCE(screen_w,'?'),' x ',COALESCE(screen_h,'?'))" },
650 { key => 'viewports', col => "CONCAT(COALESCE(viewport_width,'?'),' x ',COALESCE(viewport_height,'?'))" },
651 { key => 'languages', col => "COALESCE(NULLIF(language,''),'Unknown')" },
652 { key => 'timezones', col => "COALESCE(NULLIF(timezone,''),'Unknown')" },
653 );
654 my %out;
655 foreach my $d (@dims) {
656 my @rows = $db->db_readwrite_multiple($dbh, qq~
657 SELECT UPPER(country_iso) AS code, $d->{col} AS v, COUNT(*) AS n
658 FROM `${DB}`.tracking_sessions
659 WHERE country_iso IS NOT NULL AND CHAR_LENGTH(country_iso)=2
660 AND last_seen_at BETWEEN $since AND $until
661 GROUP BY country_iso, $d->{col}
662 ~, $ENV{SCRIPT_NAME}, __LINE__);
663 foreach my $r (@rows) {
664 my $v = defined($r->{v}) ? $r->{v} : 'Unknown';
665 next if $v eq '? x ?';
666 $out{ $r->{code} } ||= { browsers => {}, os => {}, devices => {}, resolutions => {}, viewports => {}, languages => {}, timezones => {} };
667 $out{ $r->{code} }{ $d->{key} }{ $v } += ($r->{n} || 0);
668 }
669 }
670 return \%out;
671}
672
673#---------------------------------------------------------------------
674# Per-(country, region) dimension breakdowns for the state-level modal.
675# Returns: { US => { 'New York' => { browsers => {...}, os => {...}, ... } } }
676#---------------------------------------------------------------------
677sub state_dimensions_rollup {
678 my ($self, $db, $dbh, $DB) = @_;
679 my $since = $self->_since; my $until = $self->_until;
680 my @dims = (
681 { key => 'browsers', col => "COALESCE(NULLIF(browser_name,''),'Unknown')" },
682 { key => 'os', col => "COALESCE(NULLIF(os_name,''),'Unknown')" },
683 { key => 'devices', col => "COALESCE(device_type,'unknown')" },
684 { key => 'resolutions', col => "CONCAT(COALESCE(screen_w,'?'),' x ',COALESCE(screen_h,'?'))" },
685 { key => 'viewports', col => "CONCAT(COALESCE(viewport_width,'?'),' x ',COALESCE(viewport_height,'?'))" },
686 { key => 'languages', col => "COALESCE(NULLIF(language,''),'Unknown')" },
687 { key => 'timezones', col => "COALESCE(NULLIF(timezone,''),'Unknown')" },
688 );
689 my %out;
690 foreach my $d (@dims) {
691 my @rows = $db->db_readwrite_multiple($dbh, qq~
692 SELECT UPPER(country_iso) AS code,
693 COALESCE(NULLIF(region,''),'(unknown)') AS region,
694 $d->{col} AS v,
695 COUNT(*) AS n
696 FROM `${DB}`.tracking_sessions
697 WHERE country_iso IS NOT NULL AND CHAR_LENGTH(country_iso)=2
698 AND region IS NOT NULL AND region<>''
699 AND last_seen_at BETWEEN $since AND $until
700 GROUP BY country_iso, region, $d->{col}
701 ~, $ENV{SCRIPT_NAME}, __LINE__);
702 foreach my $r (@rows) {
703 my $v = defined($r->{v}) ? $r->{v} : 'Unknown';
704 next if $v eq '? x ?';
705 $out{ $r->{code} } ||= {};
706 $out{ $r->{code} }{ $r->{region} } ||= { browsers => {}, os => {}, devices => {}, resolutions => {}, viewports => {}, languages => {}, timezones => {} };
707 $out{ $r->{code} }{ $r->{region} }{ $d->{key} }{ $v } += ($r->{n} || 0);
708 }
709 }
710 return \%out;
711}
712
713#---------------------------------------------------------------------
714# Full country -> state -> city tree for the accordion drill.
715#---------------------------------------------------------------------
716sub full_drill_tree {
717 my ($self, $db, $dbh, $DB) = @_;
718 my $since = $self->_since; my $until = $self->_until;
719 my @rows = $db->db_readwrite_multiple($dbh, qq~
720 SELECT UPPER(country_iso) AS code,
721 COALESCE(NULLIF(region,''),'(unknown)') AS region,
722 COALESCE(NULLIF(city,''),'(unknown)') AS city,
723 COUNT(DISTINCT visitor_id) AS visitors,
724 COUNT(*) AS sessions
725 FROM `${DB}`.tracking_sessions
726 WHERE country_iso IS NOT NULL AND CHAR_LENGTH(country_iso)=2
727 AND last_seen_at BETWEEN $since AND $until
728 GROUP BY country_iso, region, city
729 ORDER BY visitors DESC
730 ~, $ENV{SCRIPT_NAME}, __LINE__);
731 my (%by_code, %tot);
732 foreach my $r (@rows) {
733 my $code = $r->{code};
734 my $reg = $r->{region};
735 my $cty = $r->{city};
736 my $v = ($r->{visitors} || 0) + 0;
737 my $s = ($r->{sessions} || 0) + 0;
738 $by_code{$code} ||= { visitors => 0, sessions => 0, states => {} };
739 $by_code{$code}{visitors} += $v;
740 $by_code{$code}{sessions} += $s;
741 my $st = $by_code{$code}{states}{$reg} ||= { visitors => 0, sessions => 0, cities => [] };
742 $st->{visitors} += $v;
743 $st->{sessions} += $s;
744 push @{ $st->{cities} }, { city => $cty, visitors => $v, sessions => $s };
745 $tot{v} += $v; $tot{s} += $s;
746 }
747 my @countries;
748 foreach my $code (sort { $by_code{$b}{visitors} <=> $by_code{$a}{visitors} } keys %by_code) {
749 my $centroid = $self->country_centroid($code);
750 my $name = $centroid ? $centroid->[2] : $code;
751 my $cd = $by_code{$code};
752 my @states;
753 foreach my $rn (sort { $cd->{states}{$b}{visitors} <=> $cd->{states}{$a}{visitors} } keys %{$cd->{states}}) {
754 my $st = $cd->{states}{$rn};
755 my @cities = sort { $b->{visitors} <=> $a->{visitors} } @{ $st->{cities} };
756 push @states, {
757 region => $rn,
758 visitors => $st->{visitors},
759 sessions => $st->{sessions},
760 cities => \@cities,
761 };
762 }
763 push @countries, {
764 code => $code,
765 name => $name,
766 visitors => $cd->{visitors},
767 sessions => $cd->{sessions},
768 states => \@states,
769 };
770 }
771 return {
772 total_visitors => $tot{v} || 0,
773 total_sessions => $tot{s} || 0,
774 countries => \@countries,
775 };
776}
777
778sub by_color_depth {
779 my ($self, $db, $dbh, $DB, $days) = @_;
780 my $since = $self->_since;
781 my $until = $self->_until;
782 $days = int($days || 30);
783 return $db->db_readwrite_multiple($dbh, qq~
784 SELECT CONCAT(color_depth,'-bit') AS color_depth,
785 COUNT(*) AS sessions
786 FROM `${DB}`.tracking_sessions
787 WHERE last_seen_at BETWEEN $since AND $until
788 AND color_depth IS NOT NULL
789 GROUP BY color_depth
790 ORDER BY sessions DESC
791 ~, $ENV{SCRIPT_NAME}, __LINE__);
792}
793
794# Enriched per-country breakdown for the Geo tab leaderboard.
795# Returns: { country, visitors, sessions, avg_duration_seconds,
796# bounce_rate (0..1), pv_per_session }.
797sub by_country_enriched {
798 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
799 my $since = $self->_since;
800 my $until = $self->_until;
801 $days = int($days || 30);
802 $limit = int($limit || 30);
803 return $db->db_readwrite_multiple($dbh, qq~
804 SELECT COALESCE(NULLIF(country,''),'(unknown)') AS country,
805 COUNT(DISTINCT visitor_id) AS visitors,
806 COUNT(*) AS sessions,
807 COALESCE(AVG(duration_seconds), 0) AS avg_duration_seconds,
808 SUM(IF(pageviews<=1,1,0)) / NULLIF(COUNT(*),0) AS bounce_rate,
809 COALESCE(AVG(pageviews), 0) AS pv_per_session
810 FROM `${DB}`.tracking_sessions
811 WHERE last_seen_at BETWEEN $since AND $until
812 GROUP BY country
813 ORDER BY visitors DESC
814 LIMIT $limit
815 ~, $ENV{SCRIPT_NAME}, __LINE__);
816}
817
818# Timezone breakdown (IANA timezone string -> session count).
819sub by_timezone {
820 my ($self, $db, $dbh, $DB, $days, $limit) = @_;
821 my $since = $self->_since;
822 my $until = $self->_until;
823 $days = int($days || 30);
824 $limit = int($limit || 15);
825 return $db->db_readwrite_multiple($dbh, qq~
826 SELECT COALESCE(NULLIF(timezone,''),'(unknown)') AS timezone,
827 COUNT(*) AS sessions
828 FROM `${DB}`.tracking_sessions
829 WHERE last_seen_at BETWEEN $since AND $until
830 GROUP BY timezone
831 ORDER BY sessions DESC LIMIT $limit
832 ~, $ENV{SCRIPT_NAME}, __LINE__);
833}
834
8351;
Keyboard: j next diff k previous diff g top G bottom r restore c compile-check ? help