Diff -- /var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/Visitors.pm

O Operator
Diff

/var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/Visitors.pm

added on local at 2026-06-25 01:50:29

Added
+0
lines
Removed
-0
lines
Context
661
unchanged
Blobs
from 29228eeddc3c
to 29228eeddc3c
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
11package MODS::MetaAdmin::Visitors;
22#======================================================================
33# Meta-Admin -- portfolio-wide visitor rollup.
44#
55# Read-only aggregator that queries each sister site's tracking_sessions
66# table via SiteConn and merges results into a single dashboard view.
77#
88# Each site has its own schema flavor:
99# * 5 PT-shape sites (WebSTLs, PTMatrix, AffSoft, ShopCart, RePricer)
1010# use `tracking_sessions` + `tracking_events` with country CHAR(2).
1111# * ABForge uses `mkt_tracking_*` (marketing site tables, NOT the
1212# product's heatmap tables).
1313# * ContactForge uses a CRM-flavored split: `tracking_sessions` keys
1414# by visitor_id rather than session_token; country lives in
1515# `country_iso CHAR(2)`.
1616#
1717# Per-site SQL templates handle the differences.
1818#======================================================================
1919use strict;
2020use warnings;
2121
2222use MODS::MetaAdmin::SiteConn;
2323
2424sub new {
2525 my $class = shift;
2626 return bless({ sc => MODS::MetaAdmin::SiteConn->new }, $class);
2727}
2828
2929# Per-slug schema template
3030my %TPL = (
3131 contactforge => {
3232 sessions => 'tracking_sessions',
3333 events => 'pageviews',
3434 sess_id => 'visitor_id',
3535 country => 'country_iso',
3636 page_filter => '',
3737 screen_w_col => 'screen_w',
3838 screen_h_col => 'screen_h',
3939 },
4040 abforge => {
4141 sessions => 'mkt_tracking_sessions',
4242 events => 'mkt_tracking_events',
4343 sess_id => 'session_token',
4444 country => 'country',
4545 page_filter => "AND event_type='page_view'",
4646 screen_w_col => 'screen_width',
4747 screen_h_col => 'screen_height',
4848 },
4949);
5050# Other 5 sites default to PT-shape
5151my %DEFAULT_TPL = (
5252 sessions => 'tracking_sessions',
5353 events => 'tracking_events',
5454 sess_id => 'session_token',
5555 country => 'country',
5656 page_filter => "AND event_type='page_view'",
5757 screen_w_col => 'screen_width',
5858 screen_h_col => 'screen_height',
5959);
6060
6161sub _tpl {
6262 my ($slug) = @_;
6363 return $TPL{$slug} || \%DEFAULT_TPL;
6464}
6565
6666# Country centroid table for the rollup globe
6767my %COUNTRY_CENTROIDS = (
6868 US=>[39.5,-98.5,'United States'], CA=>[56.1,-106.3,'Canada'], MX=>[23.6,-102.5,'Mexico'],
6969 GT=>[15.8,-90.2,'Guatemala'], CR=>[9.7,-83.7,'Costa Rica'], PA=>[8.5,-80.7,'Panama'],
7070 CU=>[21.5,-77.7,'Cuba'], DO=>[18.7,-70.1,'Dominican Republic'], BR=>[-14.2,-51.9,'Brazil'],
7171 AR=>[-38.4,-63.6,'Argentina'], CL=>[-35.6,-71.5,'Chile'], CO=>[4.6,-74.0,'Colombia'],
7272 PE=>[-9.2,-75.0,'Peru'], VE=>[6.4,-66.6,'Venezuela'], EC=>[-1.8,-78.2,'Ecuador'],
7373 UY=>[-32.5,-55.8,'Uruguay'], GB=>[55.4,-3.4,'United Kingdom'], IE=>[53.4,-8.2,'Ireland'],
7474 FR=>[46.2,2.2,'France'], DE=>[51.2,10.4,'Germany'], NL=>[52.1,5.3,'Netherlands'],
7575 BE=>[50.5,4.5,'Belgium'], LU=>[49.8,6.1,'Luxembourg'], CH=>[46.8,8.2,'Switzerland'],
7676 AT=>[47.5,14.6,'Austria'], IT=>[41.9,12.6,'Italy'], ES=>[40.5,-3.7,'Spain'],
7777 PT=>[39.4,-8.2,'Portugal'], DK=>[56.3,9.5,'Denmark'], NO=>[60.5,8.5,'Norway'],
7878 SE=>[60.1,18.6,'Sweden'], FI=>[61.9,25.7,'Finland'], PL=>[51.9,19.1,'Poland'],
7979 CZ=>[49.8,15.5,'Czechia'], SK=>[48.7,19.7,'Slovakia'], HU=>[47.2,19.5,'Hungary'],
8080 RO=>[45.9,25.0,'Romania'], BG=>[42.7,25.5,'Bulgaria'], GR=>[39.1,21.8,'Greece'],
8181 UA=>[48.4,31.2,'Ukraine'], RU=>[61.5,105.3,'Russia'], TR=>[38.9,35.2,'Turkey'],
8282 IL=>[31.0,34.9,'Israel'], SA=>[23.9,45.1,'Saudi Arabia'], AE=>[23.4,53.8,'United Arab Emirates'],
8383 IR=>[32.4,53.7,'Iran'], PK=>[30.4,69.3,'Pakistan'], IN=>[20.6,78.9,'India'],
8484 BD=>[23.7,90.4,'Bangladesh'], LK=>[7.9,80.8,'Sri Lanka'], NP=>[28.4,84.1,'Nepal'],
8585 CN=>[35.9,104.2,'China'], JP=>[36.2,138.3,'Japan'], KR=>[35.9,127.8,'South Korea'],
8686 TW=>[23.7,121.0,'Taiwan'], HK=>[22.4,114.1,'Hong Kong'], SG=>[1.4,103.8,'Singapore'],
8787 MY=>[4.2,101.9,'Malaysia'], TH=>[15.9,100.9,'Thailand'], VN=>[14.1,108.3,'Vietnam'],
8888 PH=>[12.9,121.8,'Philippines'], ID=>[-0.8,113.9,'Indonesia'], AU=>[-25.3,133.8,'Australia'],
8989 NZ=>[-40.9,174.9,'New Zealand'], ZA=>[-30.6,22.9,'South Africa'], NG=>[9.1,8.7,'Nigeria'],
9090 EG=>[26.8,30.8,'Egypt'], MA=>[31.8,-7.1,'Morocco'], KE=>[-0.0,37.9,'Kenya'],
9191 ET=>[9.1,40.5,'Ethiopia'], GH=>[7.9,-1.0,'Ghana'], DZ=>[28.0,1.7,'Algeria'],
9292 TN=>[33.9,9.5,'Tunisia'],
9393);
9494sub country_centroid {
9595 my ($self, $code) = @_;
9696 return undef unless defined $code;
9797 $code = uc $code; $code =~ s/[^A-Z]//g;
9898 return undef unless length($code) == 2;
9999 return $COUNTRY_CENTROIDS{$code};
100100}
101101
102102#---------------------------------------------------------------------
103103# Roll up portfolio-wide KPIs over the last $days rolling window.
104104# Returns: { total_pageviews, total_visitors, total_sessions, days,
105105# per_site => [ { slug, name, color, pageviews, visitors, sessions, err } ] }
106106#---------------------------------------------------------------------
107107sub kpi_rollup {
108108 my ($self, $days) = @_;
109109 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
110110 my $sites = $self->{sc}->list_sites(active => 1);
111111 my %out = (total_pageviews => 0, total_visitors => 0, total_sessions => 0, days => $days, per_site => []);
112112 foreach my $row (@$sites) {
113113 my $slug = $row->{slug};
114114 my $tpl = _tpl($slug);
115115 my $entry = {
116116 slug => $slug,
117117 display_name => $row->{display_name},
118118 color => $row->{brand_color} || '#807aa8',
119119 pageviews => undef,
120120 visitors => undef,
121121 sessions => undef,
122122 err => '',
123123 };
124124
125125 # pageviews from the events table
126126 my $pv_sql = qq~
127127 SELECT COUNT(*) AS n FROM $tpl->{events}
128128 WHERE occurred_at >= DATE_SUB(NOW(), INTERVAL $days DAY) $tpl->{page_filter}
129129 ~;
130130 my $r = eval { $self->{sc}->query_one($slug, $pv_sql) };
131131 if ($r) { $entry->{pageviews} = ($r->{n} || 0) + 0; $out{total_pageviews} += $entry->{pageviews}; }
132132 else { $entry->{err} = $self->{sc}->last_error || ''; }
133133
134134 # visitors + sessions from sessions table
135135 my $vs_sql = qq~
136136 SELECT COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
137137 COUNT(*) AS sessions
138138 FROM $tpl->{sessions}
139139 WHERE last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
140140 ~;
141141 $r = eval { $self->{sc}->query_one($slug, $vs_sql) };
142142 if ($r) {
143143 $entry->{visitors} = ($r->{visitors} || 0) + 0;
144144 $entry->{sessions} = ($r->{sessions} || 0) + 0;
145145 $out{total_visitors} += $entry->{visitors};
146146 $out{total_sessions} += $entry->{sessions};
147147 } else {
148148 $entry->{err} ||= $self->{sc}->last_error || '';
149149 }
150150 push @{ $out{per_site} }, $entry;
151151 }
152152 return \%out;
153153}
154154
155155#---------------------------------------------------------------------
156156# Roll up portfolio-wide country counts for the globe.
157157# Returns flat list of { code, name, lat, lng, count } merged across all sites.
158158#---------------------------------------------------------------------
159159sub geo_rollup {
160160 my ($self, $days) = @_;
161161 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
162162 my $sites = $self->{sc}->list_sites(active => 1);
163163 my %by_code; # ISO -> total count
164164 foreach my $row (@$sites) {
165165 my $slug = $row->{slug};
166166 my $tpl = _tpl($slug);
167167 my $col = $tpl->{country};
168168 my $sql = qq~
169169 SELECT UPPER($col) AS code, COUNT(*) AS n
170170 FROM $tpl->{sessions}
171171 WHERE $col IS NOT NULL AND CHAR_LENGTH($col)=2
172172 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
173173 GROUP BY $col
174174 ~;
175175 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
176176 next unless ref($rows) eq 'ARRAY';
177177 foreach my $r (@$rows) {
178178 next unless ref($r) eq 'HASH';
179179 $by_code{ $r->{code} } += ($r->{n} || 0);
180180 }
181181 }
182182 my @out;
183183 foreach my $code (sort { $by_code{$b} <=> $by_code{$a} } keys %by_code) {
184184 my $c = $self->country_centroid($code);
185185 next unless $c;
186186 push @out, {
187187 code => $code, name => $c->[2],
188188 lat => $c->[0], lng => $c->[1],
189189 count => $by_code{$code},
190190 };
191191 }
192192 return @out;
193193}
194194
195195#---------------------------------------------------------------------
196196# Per-site drill: ONE site's traffic broken down by country + region.
197197# Returns: { slug, display_name, color, total_visitors, total_sessions,
198198# countries => [ {code, name, visitors, sessions}, ... ],
199199# regions => [ {country, region, visitors, sessions}, ... ] }
200200# Returns undef if the slug isn't a known active site.
201201#---------------------------------------------------------------------
202202sub site_drill {
203203 my ($self, $slug, $days, $country, $region) = @_;
204204 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
205205 return undef unless defined($slug) && length($slug);
206206
207207 my $sites = $self->{sc}->list_sites(active => 1);
208208 my $row;
209209 foreach my $s (@$sites) { $row = $s if $s->{slug} eq $slug; }
210210 return undef unless $row;
211211
212212 # Normalize optional drill keys
213213 $country =~ s/[^A-Za-z]//g if defined $country;
214214 $country = uc(substr($country || '', 0, 2));
215215 $country = '' unless length($country) == 2;
216216 $region //= '';
217217 # Region is a free-form display string -- only used as a literal in
218218 # the GROUP BY filter. Strip dangerous chars before interpolating.
219219 $region =~ s/[\\']//g;
220220 $region = substr($region, 0, 120);
221221
222222 my $tpl = _tpl($slug);
223223
224224 # Total visitors + sessions for the active filter scope
225225 my $where = "last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)";
226226 $where .= " AND UPPER($tpl->{country})='$country'" if length($country);
227227 $where .= " AND region='$region'" if length($region);
228228 my $tot = eval { $self->{sc}->query_one($slug, qq~
229229 SELECT COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
230230 COUNT(*) AS sessions
231231 FROM $tpl->{sessions}
232232 WHERE $where
233233 ~) };
234234 my $tv = (ref($tot) eq 'HASH' && $tot->{visitors}) ? $tot->{visitors} + 0 : 0;
235235 my $ts = (ref($tot) eq 'HASH' && $tot->{sessions}) ? $tot->{sessions} + 0 : 0;
236236
237237 my %out = (
238238 slug => $slug,
239239 display_name => $row->{display_name},
240240 color => $row->{brand_color} || '#807aa8',
241241 total_visitors => $tv,
242242 total_sessions => $ts,
243243 level => length($region) ? 'cities' : length($country) ? 'states' : 'countries',
244244 country_code => $country,
245245 country_name => length($country) ? (($self->country_centroid($country) || [0,0,$country])->[2]) : '',
246246 region_name => $region,
247247 countries => [],
248248 states => [],
249249 cities => [],
250250 );
251251
252252 if (!length($country)) {
253253 # Level 1 -- countries with traffic
254254 my $sql = qq~
255255 SELECT UPPER($tpl->{country}) AS code,
256256 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
257257 COUNT(*) AS sessions
258258 FROM $tpl->{sessions}
259259 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
260260 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
261261 GROUP BY $tpl->{country}
262262 ORDER BY visitors DESC
263263 LIMIT 60
264264 ~;
265265 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
266266 if (ref($rows) eq 'ARRAY') {
267267 foreach my $r (@$rows) {
268268 next unless ref($r) eq 'HASH';
269269 my $c = $self->country_centroid($r->{code});
270270 push @{ $out{countries} }, {
271271 code => $r->{code},
272272 name => $c ? $c->[2] : $r->{code},
273273 visitors => ($r->{visitors} || 0) + 0,
274274 sessions => ($r->{sessions} || 0) + 0,
275275 };
276276 }
277277 }
278278 }
279279 elsif (!length($region)) {
280280 # Level 2 -- states (regions) within the selected country
281281 my $sql = qq~
282282 SELECT COALESCE(NULLIF(region,''),'(unknown)') AS region,
283283 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
284284 COUNT(*) AS sessions
285285 FROM $tpl->{sessions}
286286 WHERE UPPER($tpl->{country})='$country'
287287 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
288288 GROUP BY region
289289 ORDER BY visitors DESC
290290 LIMIT 50
291291 ~;
292292 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
293293 if (ref($rows) eq 'ARRAY') {
294294 foreach my $r (@$rows) {
295295 next unless ref($r) eq 'HASH';
296296 push @{ $out{states} }, {
297297 region => $r->{region},
298298 visitors => ($r->{visitors} || 0) + 0,
299299 sessions => ($r->{sessions} || 0) + 0,
300300 };
301301 }
302302 }
303303 }
304304 else {
305305 # Level 3 -- cities within the selected state of the selected country
306306 my $sql = qq~
307307 SELECT COALESCE(NULLIF(city,''),'(unknown)') AS city,
308308 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
309309 COUNT(*) AS sessions
310310 FROM $tpl->{sessions}
311311 WHERE UPPER($tpl->{country})='$country'
312312 AND region='$region'
313313 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
314314 GROUP BY city
315315 ORDER BY visitors DESC
316316 LIMIT 50
317317 ~;
318318 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
319319 if (ref($rows) eq 'ARRAY') {
320320 foreach my $r (@$rows) {
321321 next unless ref($r) eq 'HASH';
322322 push @{ $out{cities} }, {
323323 city => $r->{city},
324324 visitors => ($r->{visitors} || 0) + 0,
325325 sessions => ($r->{sessions} || 0) + 0,
326326 };
327327 }
328328 }
329329 }
330330 return \%out;
331331}
332332
333333#---------------------------------------------------------------------
334334# State-level rollup for the globe's state-zoom dots. Returns:
335335# { US => { 'New York' => 25, 'Colorado' => 8, ... }, GB => {...}, ... }
336336# Aggregates region counts across every active site in the portfolio.
337337#---------------------------------------------------------------------
338338sub state_rollup {
339339 my ($self, $days) = @_;
340340 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
341341 my $sites = $self->{sc}->list_sites(active => 1);
342342 my %out;
343343 foreach my $row (@$sites) {
344344 my $slug = $row->{slug};
345345 my $tpl = _tpl($slug);
346346 my $sql = qq~
347347 SELECT UPPER($tpl->{country}) AS code,
348348 region,
349349 COUNT(DISTINCT $tpl->{sess_id}) AS visitors
350350 FROM $tpl->{sessions}
351351 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
352352 AND region IS NOT NULL AND region<>''
353353 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
354354 GROUP BY $tpl->{country}, region
355355 ~;
356356 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
357357 next unless ref($rows) eq 'ARRAY';
358358 foreach my $r (@$rows) {
359359 next unless ref($r) eq 'HASH';
360360 $out{ $r->{code} } ||= {};
361361 $out{ $r->{code} }{ $r->{region} } += ($r->{visitors} || 0);
362362 }
363363 }
364364 return \%out;
365365}
366366
367367#---------------------------------------------------------------------
368368# Full country -> state -> city tree for accordion drill rendering.
369369# One SQL per site (or zero if slug not active). Returns:
370370# { slug, display_name, color, total_visitors, total_sessions,
371371# countries => [
372372# { code, name, visitors, sessions,
373373# states => [
374374# { region, visitors, sessions,
375375# cities => [ { city, visitors, sessions }, ... ]
376376# }
377377# ]
378378# }
379379# ]
380380# }
381381#---------------------------------------------------------------------
382382sub full_drill_tree {
383383 my ($self, $slug, $days) = @_;
384384 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
385385 return undef unless defined($slug) && length($slug);
386386
387387 my $sites = $self->{sc}->list_sites(active => 1);
388388 my $row;
389389 foreach my $s (@$sites) { $row = $s if $s->{slug} eq $slug; }
390390 return undef unless $row;
391391
392392 my $tpl = _tpl($slug);
393393 my %out = (
394394 slug => $slug,
395395 display_name => $row->{display_name},
396396 color => $row->{brand_color} || '#807aa8',
397397 total_visitors => 0,
398398 total_sessions => 0,
399399 countries => [],
400400 );
401401
402402 # One SQL pulls every (country, region, city) tuple with counts.
403403 my $sql = qq~
404404 SELECT UPPER($tpl->{country}) AS code,
405405 COALESCE(NULLIF(region,''),'(unknown)') AS region,
406406 COALESCE(NULLIF(city,''),'(unknown)') AS city,
407407 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
408408 COUNT(*) AS sessions
409409 FROM $tpl->{sessions}
410410 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
411411 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
412412 GROUP BY $tpl->{country}, region, city
413413 ORDER BY visitors DESC
414414 ~;
415415 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
416416 return \%out unless ref($rows) eq 'ARRAY';
417417
418418 # Aggregate rows into a nested tree.
419419 my %by_code; # code -> { name, visitors, sessions, states_by_name => { ... } }
420420 foreach my $r (@$rows) {
421421 next unless ref($r) eq 'HASH';
422422 my $code = $r->{code};
423423 my $region = $r->{region};
424424 my $city = $r->{city};
425425 my $v = ($r->{visitors} || 0) + 0;
426426 my $s = ($r->{sessions} || 0) + 0;
427427
428428 $by_code{$code} ||= { name => undef, visitors => 0, sessions => 0, states => {} };
429429 $by_code{$code}{visitors} += $v;
430430 $by_code{$code}{sessions} += $s;
431431 $out{total_visitors} += $v;
432432 $out{total_sessions} += $s;
433433
434434 my $state = $by_code{$code}{states}{$region}
435435 ||= { visitors => 0, sessions => 0, cities => [] };
436436 $state->{visitors} += $v;
437437 $state->{sessions} += $s;
438438 push @{ $state->{cities} }, { city => $city, visitors => $v, sessions => $s };
439439 }
440440
441441 # Resolve country display names + sort country/state lists desc by visitors.
442442 foreach my $code (sort { $by_code{$b}{visitors} <=> $by_code{$a}{visitors} } keys %by_code) {
443443 my $centroid = $self->country_centroid($code);
444444 my $cname = $centroid ? $centroid->[2] : $code;
445445 my $cdata = $by_code{$code};
446446 my @states;
447447 foreach my $rname (sort { $cdata->{states}{$b}{visitors} <=> $cdata->{states}{$a}{visitors} } keys %{$cdata->{states}}) {
448448 my $st = $cdata->{states}{$rname};
449449 my @cities = sort { $b->{visitors} <=> $a->{visitors} } @{ $st->{cities} };
450450 push @states, {
451451 region => $rname,
452452 visitors => $st->{visitors},
453453 sessions => $st->{sessions},
454454 cities => \@cities,
455455 };
456456 }
457457 push @{ $out{countries} }, {
458458 code => $code,
459459 name => $cname,
460460 visitors => $cdata->{visitors},
461461 sessions => $cdata->{sessions},
462462 states => \@states,
463463 };
464464 }
465465 return \%out;
466466}
467467
468468#---------------------------------------------------------------------
469469# Cross-portfolio per-country dimension breakdowns for the modal.
470470# Returns: { US => {
471471# browsers: { 'Chrome'=>200, 'Safari'=>50, ... },
472472# os: { 'Windows'=>150, 'macOS'=>100, ... },
473473# devices: { 'desktop'=>300, 'mobile'=>80, ... },
474474# resolutions: { '1920 x 1080'=>100, ... },
475475# viewports: { ... },
476476# languages: { 'en-US'=>200, ... },
477477# timezones: { 'America/New_York'=>80, ... }
478478# }, ... }
479479# Aggregates across every active site. Each dimension is one SQL per site.
480480#---------------------------------------------------------------------
481481sub country_dimensions_rollup {
482482 my ($self, $days) = @_;
483483 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
484484 my $sites = $self->{sc}->list_sites(active => 1);
485485 my %out;
486486 foreach my $row (@$sites) {
487487 my $slug = $row->{slug};
488488 my $tpl = _tpl($slug);
489489 my $sw = $tpl->{screen_w_col} || 'screen_width';
490490 my $sh = $tpl->{screen_h_col} || 'screen_height';
491491 my @dims = (
492492 { key => 'browsers', col => "COALESCE(NULLIF(browser_name,''),'Unknown')" },
493493 { key => 'os', col => "COALESCE(NULLIF(os_name,''),'Unknown')" },
494494 { key => 'devices', col => "COALESCE(device_type,'unknown')" },
495495 { key => 'resolutions', col => "CONCAT(COALESCE($sw,'?'),' x ',COALESCE($sh,'?'))" },
496496 { key => 'viewports', col => "CONCAT(COALESCE(viewport_width,'?'),' x ',COALESCE(viewport_height,'?'))" },
497497 { key => 'languages', col => "COALESCE(NULLIF(language,''),'Unknown')" },
498498 { key => 'timezones', col => "COALESCE(NULLIF(timezone,''),'Unknown')" },
499499 );
500500 foreach my $d (@dims) {
501501 my $sql = qq~
502502 SELECT UPPER($tpl->{country}) AS code, $d->{col} AS v, COUNT(*) AS n
503503 FROM $tpl->{sessions}
504504 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
505505 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
506506 GROUP BY $tpl->{country}, $d->{col}
507507 ~;
508508 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
509509 next unless ref($rows) eq 'ARRAY';
510510 foreach my $r (@$rows) {
511511 next unless ref($r) eq 'HASH';
512512 $out{ $r->{code} } ||= { browsers => {}, os => {}, devices => {}, resolutions => {}, viewports => {}, languages => {}, timezones => {} };
513513 my $v = defined($r->{v}) ? $r->{v} : 'Unknown';
514514 next if $v eq '? x ?'; # drop totally-empty resolution buckets
515515 $out{ $r->{code} }{ $d->{key} }{ $v } += ($r->{n} || 0);
516516 }
517517 }
518518 }
519519 return \%out;
520520}
521521
522522#---------------------------------------------------------------------
523523# Per-state dimension breakdowns (browser/OS/device/etc) for the
524524# state-level modal. Returns:
525525# { US => { 'New York' => { browsers => {...}, os => {...}, ... } } }
526526# Same dimension set as country_dimensions_rollup, just additionally
527527# grouped by region.
528528#---------------------------------------------------------------------
529529sub state_dimensions_rollup {
530530 my ($self, $days) = @_;
531531 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
532532 my $sites = $self->{sc}->list_sites(active => 1);
533533 my %out;
534534 foreach my $row (@$sites) {
535535 my $slug = $row->{slug};
536536 my $tpl = _tpl($slug);
537537 my $sw = $tpl->{screen_w_col} || 'screen_width';
538538 my $sh = $tpl->{screen_h_col} || 'screen_height';
539539 my @dims = (
540540 { key => 'browsers', col => "COALESCE(NULLIF(browser_name,''),'Unknown')" },
541541 { key => 'os', col => "COALESCE(NULLIF(os_name,''),'Unknown')" },
542542 { key => 'devices', col => "COALESCE(device_type,'unknown')" },
543543 { key => 'resolutions', col => "CONCAT(COALESCE($sw,'?'),' x ',COALESCE($sh,'?'))" },
544544 { key => 'viewports', col => "CONCAT(COALESCE(viewport_width,'?'),' x ',COALESCE(viewport_height,'?'))" },
545545 { key => 'languages', col => "COALESCE(NULLIF(language,''),'Unknown')" },
546546 { key => 'timezones', col => "COALESCE(NULLIF(timezone,''),'Unknown')" },
547547 );
548548 foreach my $d (@dims) {
549549 my $sql = qq~
550550 SELECT UPPER($tpl->{country}) AS code, region, $d->{col} AS v, COUNT(*) AS n
551551 FROM $tpl->{sessions}
552552 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
553553 AND region IS NOT NULL AND region<>''
554554 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
555555 GROUP BY $tpl->{country}, region, $d->{col}
556556 ~;
557557 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
558558 next unless ref($rows) eq 'ARRAY';
559559 foreach my $r (@$rows) {
560560 next unless ref($r) eq 'HASH';
561561 my $v = defined($r->{v}) ? $r->{v} : 'Unknown';
562562 next if $v eq '? x ?';
563563 $out{ $r->{code} } ||= {};
564564 $out{ $r->{code} }{ $r->{region} } ||= { browsers => {}, os => {}, devices => {}, resolutions => {}, viewports => {}, languages => {}, timezones => {} };
565565 $out{ $r->{code} }{ $r->{region} }{ $d->{key} }{ $v } += ($r->{n} || 0);
566566 }
567567 }
568568 }
569569 return \%out;
570570}
571571
572572#---------------------------------------------------------------------
573573# Per-country -> per-state -> per-site breakdown for the state modal.
574574# Returns: { US => { 'New York' => [ {slug,name,color,visitors,sessions}, ... ] } }
575575#---------------------------------------------------------------------
576576sub state_per_site_rollup {
577577 my ($self, $days) = @_;
578578 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
579579 my $sites = $self->{sc}->list_sites(active => 1);
580580 my %out;
581581 foreach my $row (@$sites) {
582582 my $slug = $row->{slug};
583583 my $tpl = _tpl($slug);
584584 my $sql = qq~
585585 SELECT UPPER($tpl->{country}) AS code,
586586 region,
587587 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
588588 COUNT(*) AS sessions
589589 FROM $tpl->{sessions}
590590 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
591591 AND region IS NOT NULL AND region<>''
592592 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
593593 GROUP BY $tpl->{country}, region
594594 ~;
595595 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
596596 next unless ref($rows) eq 'ARRAY';
597597 foreach my $r (@$rows) {
598598 next unless ref($r) eq 'HASH';
599599 $out{ $r->{code} } ||= {};
600600 $out{ $r->{code} }{ $r->{region} } ||= [];
601601 push @{ $out{ $r->{code} }{ $r->{region} } }, {
602602 slug => $slug,
603603 name => $row->{display_name},
604604 color => $row->{brand_color} || '#807aa8',
605605 visitors => ($r->{visitors} || 0) + 0,
606606 sessions => ($r->{sessions} || 0) + 0,
607607 };
608608 }
609609 }
610610 foreach my $code (keys %out) {
611611 foreach my $region (keys %{ $out{$code} }) {
612612 $out{$code}{$region} = [ sort { $b->{visitors} <=> $a->{visitors} } @{ $out{$code}{$region} } ];
613613 }
614614 }
615615 return \%out;
616616}
617617
618618#---------------------------------------------------------------------
619619# Per-country -> per-site breakdown for the globe popover.
620620# Returns a hashref keyed by ISO code:
621621# { US => [ {slug, name, color, visitors, sessions}, ... ], ... }
622622# Only countries with at least one site contribution are included.
623623#---------------------------------------------------------------------
624624sub country_per_site_rollup {
625625 my ($self, $days) = @_;
626626 $days = int($days || 30); $days = 1 if $days < 1; $days = 730 if $days > 730;
627627 my $sites = $self->{sc}->list_sites(active => 1);
628628 my %out;
629629 foreach my $row (@$sites) {
630630 my $slug = $row->{slug};
631631 my $tpl = _tpl($slug);
632632 my $sql = qq~
633633 SELECT UPPER($tpl->{country}) AS code,
634634 COUNT(DISTINCT $tpl->{sess_id}) AS visitors,
635635 COUNT(*) AS sessions
636636 FROM $tpl->{sessions}
637637 WHERE $tpl->{country} IS NOT NULL AND CHAR_LENGTH($tpl->{country})=2
638638 AND last_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)
639639 GROUP BY $tpl->{country}
640640 ~;
641641 my $rows = eval { $self->{sc}->query_many($slug, $sql) };
642642 next unless ref($rows) eq 'ARRAY';
643643 foreach my $r (@$rows) {
644644 next unless ref($r) eq 'HASH';
645645 $out{ $r->{code} } ||= [];
646646 push @{ $out{ $r->{code} } }, {
647647 slug => $slug,
648648 name => $row->{display_name},
649649 color => $row->{brand_color} || '#807aa8',
650650 visitors => ($r->{visitors} || 0) + 0,
651651 sessions => ($r->{sessions} || 0) + 0,
652652 };
653653 }
654654 }
655655 foreach my $code (keys %out) {
656656 $out{$code} = [ sort { $b->{visitors} <=> $a->{visitors} } @{ $out{$code} } ];
657657 }
658658 return \%out;
659659}
660660
6616611;