Diff -- /var/www/vhosts/3dshawn.com/affiliate.3dshawn.com/MODS/AnonPaths.pm -- DriftSense

1

package MODS::AnonPaths;

2

#======================================================================

3

# Anonymous visitor path rendering -- shared across all 7 sites.

4

#

5

# Public entry: MODS::AnonPaths::render_for_admin($db, $dbh, $days,

6

# $selected_path, $entered_signups)

7

# Returns one HTML string containing:

8

# 1. <style> block with all the path-panel CSS (idempotent — caller can

9

# include it once per page, repeated includes are harmless)

10

# 2. "Top Acquisition Paths" panel with up-to-10 most-common sequences

11

# 3. (Optional) per-step funnel drill-in when $selected_path is set

12

#

13

# Assumes the dbh is already bound to the site's schema and that

14

# anon_sessions + anon_pageviews tables exist (see _anon_path_tracking.sql).

15

#======================================================================

16

use strict;

17

use warnings;

18

use Time::Local ();

19

20

my $ARROW = chr(0xE2) . chr(0x86) . chr(0x92); # → as UTF-8 bytes

21

22

sub render_for_admin {

23

my ($db, $dbh, $days, $selected_path, $entered_signups) = @_;

24

$days = ($days && $days =~ /^\d+$/) ? $days + 0 : 30;

25

$days = 365 if $days > 365;

26

$days = 1 if $days < 1;

27

$selected_path = '' unless defined $selected_path;

28

$selected_path =~ s/[\x00-\x1f]//g;

29

$selected_path = substr($selected_path, 0, 1000);

30

$entered_signups = 0 unless $entered_signups;

31

32

my @top_paths = _query_top_paths($db, $dbh, $days);

33

my @drill_steps = ();

34

my ($sel_total, $sel_signups, $sel_abandons) = (0, 0, 0);

35

if (length $selected_path) {

36

(@drill_steps) = _build_drilldown($db, $dbh, $days, $selected_path);

37

if (@drill_steps) {

38

$sel_total = $drill_steps[0]->{reached};

39

$sel_signups += $_->{signup} for @drill_steps;

40

$sel_abandons += $_->{abandon} for @drill_steps;

41

}

42

}

43

44

my $css = _css();

45

my $panel = _render_panel(\@top_paths, $entered_signups, $selected_path, $days);

46

my $drill = (@drill_steps && length $selected_path)

47

? _render_drilldown(\@drill_steps, $selected_path, $sel_total, $sel_signups, $sel_abandons)

48

: '';

49

return $css . $panel . $drill;

50

}

51

52

# =====================================================================

53

# external_funnel_stages($db, $dbh, $opts) -> ($entered, @bars)

54

#

55

# Real-page-path funnel: stages are the actual pages visitors took on

56

# the modal (most-common) journey. Final stage is "Signed up" within

57

# that journey. Each bar also carries a `hover` payload with top

58

# referrers / utm sources / next pages / conversion split so the CGI

59

# can render rich tooltips.

60

#

61

# $opts may be:

62

# - integer N -> "last N days" (backwards compat)

63

# - hashref { days => N }

64

# - hashref { from => 'YYYY-MM-DD', to => 'YYYY-MM-DD' }

65

#

66

# Bar shape:

67

# { label, page_path, count, pct, drop_pct, depth, terminal,

68

# hover => { referrers => [...], utm_sources => [...],

69

# next_pages => [...], signups, abandons } }

70

# =====================================================================

71

sub external_funnel_stages {

72

my ($db, $dbh, $opts) = @_;

73

my ($where, $window_label) = _build_window_where($opts);

74

75

my @rows = _silenced_query($db, $dbh, qq~

76

SELECT s.id, s.end_reason, s.first_seen_at,

77

s.referer_host, s.utm_source, s.utm_medium, s.utm_campaign,

78

GROUP_CONCAT(p.page_path ORDER BY p.sequence ASC SEPARATOR '\x01') AS seq

79

FROM anon_sessions s

80

LEFT JOIN anon_pageviews p ON p.session_id=s.id

81

WHERE $where

82

GROUP BY s.id

83

~);

84

85

my @sessions;

86

foreach my $r (@rows) {

87

my @pages = (defined $r->{seq} && length $r->{seq}) ? split(/\x01/, $r->{seq}) : ();

88

push @sessions, {

89

id => $r->{id},

90

end_reason => $r->{end_reason} // '',

91

referer_host => $r->{referer_host} // '',

92

utm_source => $r->{utm_source} // '',

93

utm_medium => $r->{utm_medium} // '',

94

utm_campaign => $r->{utm_campaign} // '',

95

pages => \@pages,

96

};

97

}

98

99

my $total = scalar @sessions;

100

return (0, ()) unless $total;

101

102

my $selected_path = (ref($opts) eq 'HASH') ? ($opts->{selected_path} // '') : '';

103

$selected_path =~ s/^\s+|\s+$//g if length $selected_path;

104

105

my @bars;

106

107

if (length $selected_path) {

108

# ---- Drill-in mode: build the funnel from THIS specific path's

109

# stages, not from the modal journey. Lets the user click a row

110

# in Top Acquisition Paths and see its funnel here.

111

my @steps = split /\s+\Q$ARROW\E\s+/, $selected_path;

112

@steps = grep { defined && length } @steps;

113

if (@steps) {

114

my $cohort = [@sessions];

115

my $prev_for_drop = $total;

116

for (my $depth = 1; $depth <= scalar @steps; $depth++) {

117

my $want = $steps[$depth - 1];

118

my @narrowed = grep {

119

defined $_->{pages}->[$depth-1] && $_->{pages}->[$depth-1] eq $want

120

} @$cohort;

121

my $n = scalar @narrowed;

122

push @bars, _make_stage_bar($want, $want, $n, $total, $prev_for_drop, \@narrowed, $depth, 0);

123

$cohort = \@narrowed;

124

$prev_for_drop = $n;

125

}

126

my @signed_in_cohort = grep { $_->{end_reason} eq 'signed_up' } @$cohort;

127

my $signed_n = scalar @signed_in_cohort;

128

push @bars, _make_stage_bar('Signed up', '', $signed_n, $total, $prev_for_drop,

129

\@signed_in_cohort, scalar(@bars) + 1, 1);

130

return ($total, @bars);

131

}

132

}

133

134

# ---- Modal mode (default): the most-common journey across all sessions

135

my $cohort = [@sessions];

136

my $prev_for_drop = $total;

137

for (my $depth = 1; $depth <= 5; $depth++) {

138

my %histo;

139

foreach my $s (@$cohort) {

140

my $p = $s->{pages}->[$depth - 1];

141

next unless defined $p && length $p;

142

$histo{$p}++;

143

}

144

last unless %histo;

145

my @sorted = sort { $histo{$b} <=> $histo{$a} || $a cmp $b } keys %histo;

146

my $top = $sorted[0];

147

my $n = $histo{$top};

148

last if $n < 1;

149

150

my @narrowed = grep {

151

defined $_->{pages}->[$depth-1] && $_->{pages}->[$depth-1] eq $top

152

} @$cohort;

153

154

push @bars, _make_stage_bar($top, $top, $n, $total, $prev_for_drop, \@narrowed, $depth, 0);

155

$cohort = \@narrowed;

156

$prev_for_drop = $n;

157

last if scalar @narrowed < 2;

158

}

159

160

my @signed_in_cohort = grep { $_->{end_reason} eq 'signed_up' } @$cohort;

161

my $signed_n = scalar @signed_in_cohort;

162

push @bars, _make_stage_bar('Signed up', '', $signed_n, $total, $prev_for_drop,

163

\@signed_in_cohort, scalar(@bars) + 1, 1);

164

165

return ($total, @bars);

166

}

167

168

sub _make_stage_bar {

169

my ($label, $path, $n, $entered, $prev_n, $cohort, $depth, $terminal) = @_;

170

my $pct = $entered ? sprintf('%.1f', 100 * $n / $entered) : 0;

171

my $drop = ($depth > 0 && $prev_n) ? sprintf('%.1f', 100 * ($prev_n - $n) / $prev_n) : 0;

172

$drop = 0 if $drop < 0;

173

174

my (%ref, %utm, %next);

175

foreach my $s (@$cohort) {

176

my $r = $s->{referer_host} || '(direct)';

177

my $u = $s->{utm_source} || '(none)';

178

$ref{$r}++;

179

$utm{$u}++;

180

if (!$terminal) {

181

my $nx = $s->{pages}->[$depth];

182

$next{$nx}++ if defined $nx && length $nx;

183

}

184

}

185

# Bound the slice to the available keys. The naive `[0..2]` slice

186

# produces undef entries when there are fewer than 3 keys, which fires

187

# "Use of uninitialized value" warnings during the map. Under Plesk's

188

# CGI handler stderr is merged with stdout so those warnings land

189

# BEFORE the Content-Type header and Apache 500s the entire response.

190

my @sorted_refs = sort { $ref{$b} <=> $ref{$a} || $a cmp $b } keys %ref;

191

my @sorted_utms = sort { $utm{$b} <=> $utm{$a} || $a cmp $b } keys %utm;

192

my @sorted_next = sort { $next{$b} <=> $next{$a} || $a cmp $b } keys %next;

193

my $cap_refs = @sorted_refs > 3 ? 2 : $#sorted_refs;

194

my $cap_utms = @sorted_utms > 3 ? 2 : $#sorted_utms;

195

my $cap_next = @sorted_next > 3 ? 2 : $#sorted_next;

196

my @top_refs = ($cap_refs >= 0) ? map { { host => $_, n => $ref{$_} } } @sorted_refs[0 .. $cap_refs] : ();

197

my @top_utms = ($cap_utms >= 0) ? map { { src => $_, n => $utm{$_} } } @sorted_utms[0 .. $cap_utms] : ();

198

my @top_next = ($cap_next >= 0) ? map { { path => $_, n => $next{$_} } } @sorted_next[0 .. $cap_next] : ();

199

200

my $signups = scalar grep { $_->{end_reason} eq 'signed_up' } @$cohort;

201

my $abandons = scalar grep { $_->{end_reason} eq 'abandoned' || $_->{end_reason} eq 'bounced' } @$cohort;

202

203

return {

204

label => $label,

205

page_path => $path,

206

count => $n,

207

pct => $pct,

208

drop_pct => $drop,

209

depth => $depth,

210

terminal => $terminal,

211

hover => {

212

referrers => \@top_refs,

213

utm_sources => \@top_utms,

214

next_pages => \@top_next,

215

signups => $signups,

216

abandons => $abandons,

217

},

218

};

219

}

220

221

sub _build_window_where {

222

my ($opts) = @_;

223

if (ref($opts) eq 'HASH') {

224

my $from = $opts->{from} || '';

225

my $to = $opts->{to} || '';

226

$from = '' unless $from =~ /^\d{4}-\d{2}-\d{2}$/;

227

$to = '' unless $to =~ /^\d{4}-\d{2}-\d{2}$/;

228

if ($from && $to) {

229

return ("s.first_seen_at BETWEEN '$from 00:00:00' AND '$to 23:59:59'",

230

"$from to $to");

231

}

232

my $d = $opts->{days};

233

$d = ($d && $d =~ /^\d+$/) ? $d + 0 : 30;

234

$d = 365 if $d > 365; $d = 1 if $d < 1;

235

return ("s.first_seen_at >= DATE_SUB(NOW(), INTERVAL $d DAY)", "last $d days");

236

}

237

my $d = ($opts && $opts =~ /^\d+$/) ? $opts + 0 : 30;

238

$d = 365 if $d > 365; $d = 1 if $d < 1;

239

return ("s.first_seen_at >= DATE_SUB(NOW(), INTERVAL $d DAY)", "last $d days");

240

}

241

242

sub _silenced_query {

243

my ($db, $dbh, $sql) = @_;

244

local *OLDOUT;

245

open(OLDOUT, '>&', \*STDOUT) or do {};

246

open(STDOUT, '>', '/dev/null') or do {};

247

my @rows = eval { $db->db_readwrite_multiple($dbh, $sql, $ENV{SCRIPT_NAME} || 'anonpaths', __LINE__) };

248

@rows = () unless @rows;

249

open(STDOUT, '>&', \*OLDOUT) or do {};

250

return @rows;

251

}

252

253

# Render a rich tooltip popover for one stage bar. Returns HTML.

254

# Caller injects this inside the .afnl-step / hover container.

255

sub render_stage_popover {

256

my ($bar, $window_label) = @_;

257

$window_label //= '';

258

my $h = $bar->{hover} || {};

259

my $refs = $h->{referrers} || [];

260

my $utms = $h->{utm_sources} || [];

261

my $nxts = $h->{next_pages} || [];

262

my $sn = $h->{signups} || 0;

263

my $abn = $h->{abandons} || 0;

264

my $cnt = $bar->{count} || 0;

265

my $pct = $bar->{pct} // 0;

266

my $drop = $bar->{drop_pct} // 0;

267

my $lbl = _h($bar->{label} // '');

268

269

my $refs_html = _pop_list('Top referrers', $refs, 'host', '🌐');

270

my $utms_html = _pop_list('Top UTM sources', $utms, 'src', '🏷');

271

my $nxts_html = ($bar->{terminal})

272

? qq~<div class="ap-pop-sec"><div class="ap-pop-sec-ttl">✅ Outcome</div><div class="ap-pop-row"><span class="ap-pop-name">Signed up</span><span class="ap-pop-cnt">~ . _fmt_n($sn) . qq~</span></div></div>~

273

: _pop_list('Top next page', $nxts, 'path', '➞');

274

275

my $sn_fmt = _fmt_n($sn);

276

my $abn_fmt = _fmt_n($abn);

277

my $cnt_fmt = _fmt_n($cnt);

278

279

return qq~

280

281

282

283

<div class="ap-pop-meta">$cnt_fmt visitors · $pct% of landed</div>

284

</div>

285

286

<span class="ap-pop-stat ok">$sn_fmt signed up</span>

287

<span class="ap-pop-stat bad">$abn_fmt abandoned</span>

288

289

</div>

290

291

$refs_html

292

$utms_html

293

$nxts_html

294

</div>

295

</div>~;

296

}

297

298

sub _pop_list {

299

my ($title, $items, $key, $emoji) = @_;

300

if (!scalar @$items) {

301

return qq~<div class="ap-pop-sec"><div class="ap-pop-sec-ttl">$emoji $title</div><div class="ap-pop-empty">No data yet.</div></div>~;

302

}

303

my $rows = '';

304

foreach my $i (@$items) {

305

my $name = _h($i->{$key} // '');

306

my $n = _fmt_n($i->{n} // 0);

307

$rows .= qq~<div class="ap-pop-row"><span class="ap-pop-name" title="$name">$name</span><span class="ap-pop-cnt">$n</span></div>~;

308

}

309

return qq~<div class="ap-pop-sec"><div class="ap-pop-sec-ttl">$emoji $title</div>$rows</div>~;

310

}

311

312

sub popover_css {

313

return <<'CSS';

314

<style>

315

.ap-pop { position: absolute; z-index: 90; top: calc(100% + 8px); left: 50%; transform: translate(-50%, -6px); min-width: 360px; max-width: 480px; padding: 16px 18px; background: rgba(14,18,26,0.98); border: 1px solid rgba(168,85,247,0.32); border-radius: 14px; box-shadow: 0 24px 60px rgba(0,0,0,0.6); opacity: 0; pointer-events: none; transition: opacity 0.16s ease, transform 0.16s ease; }

316

.ap-pop-host { position: relative; }

317

.ap-pop-host:hover .ap-pop { opacity: 1; transform: translate(-50%, 0); }

318

.ap-pop-head { display: flex; justify-content: space-between; align-items: baseline; padding-bottom: 10px; border-bottom: 1px solid rgba(255,255,255,0.06); margin-bottom: 12px; gap: 12px; flex-wrap: wrap; }

319

.ap-pop-stage { font-family: 'SF Mono', Menlo, monospace; font-size: 13px; color: #fff; font-weight: 700; word-break: break-all; }

320

.ap-pop-meta { font-size: 11px; color: #8893a4; font-weight: 600; }

321

.ap-pop-stats { display: flex; gap: 8px; flex-wrap: wrap; margin-bottom: 14px; }

322

.ap-pop-stat { font-size: 10.5px; font-weight: 700; padding: 4px 10px; border-radius: 14px; letter-spacing: 0.02em; }

323

.ap-pop-stat.ok { background: rgba(34,197,94,0.18); color: #4ade80; }

324

.ap-pop-stat.bad { background: rgba(239,68,68,0.18); color: #f87171; }

325

.ap-pop-stat.dim { background: rgba(255,255,255,0.06); color: #cbd5e1; }

326

.ap-pop-cols { display: grid; grid-template-columns: 1fr 1fr 1fr; gap: 14px; }

327

.ap-pop-sec { min-width: 0; }

328

.ap-pop-sec-ttl { font-size: 10px; color: #c4a3f0; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; margin-bottom: 8px; }

329

.ap-pop-row { display: flex; justify-content: space-between; gap: 8px; padding: 4px 0; font-size: 12px; min-width: 0; }

330

.ap-pop-name { color: #cfd6e0; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; min-width: 0; font-family: 'SF Mono', Menlo, monospace; font-size: 11px; }

331

.ap-pop-cnt { color: #fff; font-weight: 700; flex-shrink: 0; }

332

.ap-pop-empty { color: #6f7787; font-size: 11px; font-style: italic; }

333

@media (max-width: 800px) { .ap-pop-cols { grid-template-columns: 1fr; } .ap-pop { min-width: 280px; max-width: 90vw; } }

334

</style>

335

CSS

336

}

337

338

# Canonical chip-style date-range picker (matches admin_traffic.cgi's UX).

339

# Args: ($preset, $from, $to, $action_url). $action_url optional -- defaults

340

# to $ENV{SCRIPT_NAME}. Returns a self-contained HTML fragment (form + CSS +

341

# JS).

342

sub render_range_controls {

343

my ($preset, $from, $to, $action) = @_;

344

$preset = defined($preset) ? "$preset" : '30';

345

$from //= ''; $to //= '';

346

$action //= ($ENV{SCRIPT_NAME} || '');

347

348

my %active = (1 => '', 7 => '', 30 => '', 90 => '', 365 => '', custom => '');

349

if ($preset eq '1' || $preset eq '24' || $preset eq '24h') { $active{1} = ' is-active'; }

350

elsif ($preset eq '7' || $preset eq '7d') { $active{7} = ' is-active'; }

351

elsif ($preset eq '30' || $preset eq '30d') { $active{30} = ' is-active'; }

352

elsif ($preset eq '90' || $preset eq '90d') { $active{90} = ' is-active'; }

353

elsif ($preset eq '365' || $preset eq '1y') { $active{365} = ' is-active'; }

354

elsif ($preset eq 'custom') { $active{custom} = ' is-active'; }

355

else { $active{30} = ' is-active'; }

356

357

my $is_custom = ($preset eq 'custom');

358

my $custom_display = $is_custom ? 'flex' : 'none';

359

my $range_label =

360

$active{1} ? 'Last 24h'

361

: $active{7} ? 'Last 7 days'

362

: $active{30} ? 'Last 30 days'

363

: $active{90} ? 'Last 90 days'

364

: $active{365} ? 'Last 1 year'

365

: $active{custom} ? "$from -- $to"

366

: 'Last 30 days';

367

368

my $from_input = $from; $from_input .= 'T00:00' if $from_input =~ /^\d{4}-\d{2}-\d{2}$/;

369

my $to_input = $to; $to_input .= 'T23:59' if $to_input =~ /^\d{4}-\d{2}-\d{2}$/;

370

371

return qq~

372

<form id="rangeForm" method="get" action="$action"

373

style="display:flex;flex-wrap:wrap;align-items:center;gap:8px;margin:8px 0 14px;padding:10px 14px;background:var(--col-surface);border:1px solid var(--col-border);border-radius:12px">

374

<span style="font-size:11.5px;letter-spacing:1px;text-transform:uppercase;color:var(--col-text-muted);font-weight:700">Range</span>

375

376

377

378

379

380

<button type="button" class="tr-chip$active{custom}" id="customBtn">Custom…</button>

381

382

383

384

385

386

<button type="submit" style="padding:5px 12px;border-radius:999px;font-size:12px;font-weight:600;border:1px solid var(--col-border);background:var(--col-surface);color:var(--col-text);cursor:pointer">Apply</button>

387

</div>

388

<span style="margin-left:auto;font-size:11.5px;color:var(--col-text-muted)">Showing: <strong style="color:var(--col-text)">$range_label</strong></span>

389

</form>

390

<style>

391

.tr-chip{display:inline-flex;align-items:center;padding:5px 11px;border-radius:999px;font-size:12px;font-weight:600;color:var(--col-text-muted);text-decoration:none;background:transparent;border:1px solid var(--col-border);cursor:pointer;transition:all .15s ease;font-family:inherit}

392

.tr-chip:hover{color:var(--col-text);border-color:var(--col-brand, var(--col-brand-2, var(--col-accent, #5aa9ff)))}

393

.tr-chip.is-active{background:var(--col-brand, var(--col-brand-2, var(--col-accent, #5aa9ff)));color:#0c1018;border-color:var(--col-brand, var(--col-brand-2, var(--col-accent, #5aa9ff)))}

394

</style>

395

396

(function(){

397

var btn = document.getElementById('customBtn');

398

var wrap = document.getElementById('customWrap');

399

if (!btn || !wrap) return;

400

btn.addEventListener('click', function(){

401

var open = wrap.style.display !== 'none';

402

wrap.style.display = open ? 'none' : 'flex';

403

if (!open) { var f = wrap.querySelector('input[type=datetime-local]'); if (f) f.focus(); }

404

});

405

})();

406

</script>~;

407

}

408

409

# Parse from query-params into the opts shape expected by external_funnel_stages.

410

# Accepts ?days=N (chip presets) or ?from=&to= (custom). Legacy ?preset=X

411

# still honored for backward-compat URLs. Returns ($opts, $preset, $from, $to).

412

sub parse_range_params {

413

my ($params) = @_;

414

my $days = $params->{days} // '';

415

my $preset = $params->{preset} // '';

416

my $from = $params->{from} // '';

417

my $to = $params->{to} // '';

418

419

# Strip datetime-local time portion for the underlying consumers,

420

# which expect YYYY-MM-DD.

421

my $from_date = $from; $from_date =~ s/T\d{2}:\d{2}(:\d{2})?$//;

422

my $to_date = $to; $to_date =~ s/T\d{2}:\d{2}(:\d{2})?$//;

423

424

if ($from_date =~ /^\d{4}-\d{2}-\d{2}$/ && $to_date =~ /^\d{4}-\d{2}-\d{2}$/) {

425

return ({ from => $from_date, to => $to_date }, 'custom', $from_date, $to_date);

426

}

427

428

if ($days =~ /^\d+$/) {

429

my $d = $days + 0;

430

$d = 30 if $d < 1; $d = 730 if $d > 730;

431

return ({ days => $d }, "$d", '', '');

432

}

433

434

if ($preset eq 'this_month') {

435

my @t = localtime();

436

my $fy = $t[5] + 1900; my $fm = $t[4] + 1;

437

my $f = sprintf('%04d-%02d-01', $fy, $fm);

438

my $tt = sprintf('%04d-%02d-%02d', $fy, $fm, $t[3]);

439

return ({ from => $f, to => $tt }, 'custom', $f, $tt);

440

} elsif ($preset eq 'last_month') {

441

my @t = localtime();

442

my $y = $t[5] + 1900; my $m = $t[4] + 1;

443

$m--; if ($m == 0) { $m = 12; $y--; }

444

my $f = sprintf('%04d-%02d-01', $y, $m);

445

my $nm = $m + 1; my $ny = $y; if ($nm > 12) { $nm = 1; $ny++; }

446

require Time::Local;

447

my $ts_first_next = eval { Time::Local::timelocal(0,0,0,1,$nm-1,$ny-1900) } || 0;

448

my @lt = localtime($ts_first_next - 86400);

449

my $tt = sprintf('%04d-%02d-%02d', $lt[5]+1900, $lt[4]+1, $lt[3]);

450

return ({ from => $f, to => $tt }, 'custom', $f, $tt);

451

} elsif ($preset =~ /^\d+$/) {

452

my $d = $preset + 0; $d = 30 if $d < 1; $d = 730 if $d > 730;

453

return ({ days => $d }, "$d", '', '');

454

}

455

456

return ({ days => 30 }, '30', '', '');

457

}

458

459

sub _query_top_paths {

460

my ($db, $dbh, $days) = @_;

461

my @rows;

462

local *OLDOUT;

463

open(OLDOUT, '>&', \*STDOUT) or do {};

464

open(STDOUT, '>', '/dev/null') or do {};

465

@rows = eval { $db->db_readwrite_multiple($dbh, qq~

466

SELECT path_sequence, COUNT(*) AS n,

467

SUM(CASE WHEN end_reason='signed_up' THEN 1 ELSE 0 END) AS signups,

468

SUM(CASE

469

WHEN end_reason IN ('abandoned','bounced') THEN 1

470

WHEN end_reason='active' AND last_seen_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE) THEN 1

471

ELSE 0

472

END) AS abandons

473

FROM (

474

SELECT s.id, s.end_reason, s.last_seen_at,

475

GROUP_CONCAT(p.page_path ORDER BY p.sequence SEPARATOR ' $ARROW ') AS path_sequence

476

FROM anon_sessions s

477

JOIN anon_pageviews p ON p.session_id=s.id

478

WHERE s.first_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)

479

GROUP BY s.id

480

) AS sess

481

WHERE path_sequence IS NOT NULL AND path_sequence != ''

482

GROUP BY path_sequence

483

ORDER BY n DESC

484

LIMIT 10

485

~, $ENV{SCRIPT_NAME} || 'anonpaths', __LINE__) };

486

@rows = () unless @rows;

487

open(STDOUT, '>&', \*OLDOUT) or do {};

488

return @rows;

489

}

490

491

sub _build_drilldown {

492

my ($db, $dbh, $days, $selected_path) = @_;

493

my @steps = split /\s+\Q$ARROW\E\s+/, $selected_path;

494

return () unless @steps;

495

my @all;

496

{

497

local *OLDOUT;

498

open(OLDOUT, '>&', \*STDOUT) or do {};

499

open(STDOUT, '>', '/dev/null') or do {};

500

@all = eval { $db->db_readwrite_multiple($dbh, qq~

501

SELECT s.id, s.end_reason, s.last_seen_at,

502

GROUP_CONCAT(p.page_path ORDER BY p.sequence SEPARATOR ' $ARROW ') AS path_sequence

503

FROM anon_sessions s

504

JOIN anon_pageviews p ON p.session_id=s.id

505

WHERE s.first_seen_at >= DATE_SUB(NOW(), INTERVAL $days DAY)

506

GROUP BY s.id

507

~, $ENV{SCRIPT_NAME} || 'anonpaths', __LINE__) };

508

@all = () unless @all;

509

open(STDOUT, '>&', \*OLDOUT) or do {};

510

}

511

my @out;

512

for (my $i = 0; $i < @steps; $i++) {

513

my $prefix = join(" $ARROW ", @steps[0..$i]);

514

my ($reached, $abandon_here, $signup_here) = (0, 0, 0);

515

foreach my $s (@all) {

516

my $seq = $s->{path_sequence} // '';

517

next unless index($seq, $prefix) == 0;

518

$reached++;

519

my $is_full = ($seq eq $prefix);

520

my $is_abandon =

521

$s->{end_reason} eq 'abandoned' || $s->{end_reason} eq 'bounced' ||

522

($s->{end_reason} eq 'active' && _stale($s->{last_seen_at}));

523

$abandon_here++ if $is_full && $is_abandon;

524

$signup_here++ if $is_full && $s->{end_reason} eq 'signed_up';

525

}

526

push @out, { idx => $i, step => $steps[$i], reached => $reached, abandon => $abandon_here, signup => $signup_here };

527

}

528

return @out;

529

}

530

531

sub _stale {

532

my ($ts) = @_;

533

return 1 unless $ts;

534

if ($ts =~ /^(\d{4})-(\d{2})-(\d{2})\s+(\d{2}):(\d{2}):(\d{2})/) {

535

my $ep = eval { Time::Local::timelocal($6,$5,$4,$3,$2-1,$1-1900) } || 0;

536

return ($ep > 0 && (time - $ep) > 1800) ? 1 : 0;

537

}

538

return 0;

539

}

540

541

sub _h {

542

my $s = shift // '';

543

$s =~ s/&/&/g; $s =~ s/</</g; $s =~ s/>/>/g; $s =~ s/"/"/g;

544

return $s;

545

}

546

547

sub _fmt_n {

548

my $n = int(shift // 0);

549

1 while $n =~ s/(\d)(\d{3})(?!\d)/$1,$2/;

550

return $n;

551

}

552

553

sub _render_panel {

554

my ($rows, $entered, $selected, $days) = @_;

555

if (!scalar @$rows) {

556

return qq~

557

558

559

<div class="anp-title">Top Acquisition Paths</div>

560

<div class="anp-sub">Page sequences anonymous visitors take before they sign up or abandon. Click a row to drill into its per-step funnel.</div>

561

</div>

562

<div class="anp-empty">No anonymous-visitor data yet for the last $days days. Once visitors land on a marketing page, their paths will appear here.</div>

563

</div>~;

564

}

565

566

my $max = 1;

567

foreach my $r (@$rows) { $max = $r->{n} if $r->{n} > $max; }

568

my $cells = '';

569

my $rank = 0;

570

foreach my $r (@$rows) {

571

$rank++;

572

my $path_disp = _h($r->{path_sequence});

573

my $cnt = _fmt_n($r->{n});

574

my $sn = _fmt_n($r->{signups} || 0);

575

my $an = _fmt_n($r->{abandons} || 0);

576

my $bar_pct = int(100.0 * $r->{n} / $max + 0.5);

577

my $is_sel = (defined($selected) && length($selected) && $selected eq $r->{path_sequence}) ? 'is-selected' : '';

578

my $share_pct = $entered ? sprintf('%.1f', 100.0 * $r->{n} / $entered) : '0.0';

579

my $enc = $r->{path_sequence}; $enc =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/eg;

580

$cells .= qq~

581

582

583

584

585

586

<div class="anp-share">${share_pct}%</div>

587

588

</a>~;

589

}

590

return qq~

591

592

593

<div>

594

<div class="anp-title">Top Acquisition Paths</div>

595

<div class="anp-sub">Page sequences anonymous visitors take before they sign up or abandon. Click a row to see that path as a step-by-step funnel.</div>

596

</div>

597

598

<span><span class="dot ok"></span> signed up</span>

599

<span><span class="dot bad"></span> abandoned</span>

600

</div>

601

</div>

602

<div class="anp-list">$cells</div>

603

</div>~;

604

}

605

606

sub _render_drilldown {

607

my ($steps, $path_raw, $total, $signups, $abandons) = @_;

608

return '' unless ref($steps) eq 'ARRAY' && scalar @$steps;

609

return '' unless length($path_raw // '');

610

611

my $path_disp = _h($path_raw);

612

my $total_fmt = _fmt_n($total);

613

my $signup_fmt = _fmt_n($signups);

614

my $aban_fmt = _fmt_n($abandons);

615

my $continued = $total - $signups - $abandons; $continued = 0 if $continued < 0;

616

my $cont_fmt = _fmt_n($continued);

617

my $entered = $total || 1;

618

619

my $bars = '';

620

my $idx = 0;

621

foreach my $s (@$steps) {

622

$idx++;

623

my $step_path = _h($s->{step});

624

my $r = $s->{reached}; my $sn = $s->{signup}; my $an = $s->{abandon};

625

my $reached_pct = $entered ? sprintf('%.1f', 100 * $r / $entered) : '0.0';

626

my $bar_w = $entered ? int(100 * $r / $entered) : 0;

627

$bar_w = 4 if $bar_w < 4 && $r > 0;

628

my $sn_n = _fmt_n($sn); my $an_n = _fmt_n($an); my $reached_n = _fmt_n($r);

629

my $sn_block = $sn ? qq~<span class="anp-marker ok">✓ $sn_n</span>~ : '';

630

my $an_block = $an ? qq~<span class="anp-marker bad">✕ $an_n</span>~ : '';

631

$bars .= qq~

632

633

634

635

636

<div class="anp-step-meta">$reached_n · ${reached_pct}%</div>

637

</div>

638

639

<div class="anp-step-markers">$sn_block $an_block</div>

640

</div>~;

641

}

642

return qq~

643

644

645

<div class="lbl">Path drill-in</div>

646

647

648

<span>Sessions <strong>$total_fmt</strong></span>

649

<span class="ok">Signed up <strong>$signup_fmt</strong></span>

650

<span class="bad">Abandoned <strong>$aban_fmt</strong></span>

651

<span class="dim">Still active <strong>$cont_fmt</strong></span>

652

</div>

653

</div>

654

655

</div>~;

656

}

657

658

sub _css {

659

return <<'CSS';

660

<style>

661

.anp-wrap { position: relative; padding: 22px 24px 18px; background: linear-gradient(160deg, rgba(255,255,255,0.03), rgba(255,255,255,0.01)); border: 1px solid rgba(255,255,255,0.06); border-radius: 16px; margin: 0 0 18px; overflow: hidden; }

662

.anp-head { display: flex; justify-content: space-between; align-items: flex-start; margin-bottom: 16px; gap: 12px; flex-wrap: wrap; }

663

.anp-title { font-size: 17px; font-weight: 700; color: #fff; }

664

.anp-sub { font-size: 12px; color: #8893a4; margin-top: 4px; line-height: 1.55; max-width: 640px; }

665

.anp-legend { display: flex; gap: 14px; font-size: 11px; color: #8893a4; }

666

.anp-legend .dot { display: inline-block; width: 8px; height: 8px; border-radius: 50%; vertical-align: 1px; margin-right: 4px; }

667

.anp-legend .dot.ok { background: #22c55e; }

668

.anp-legend .dot.bad { background: #ef4444; }

669

.anp-list { display: flex; flex-direction: column; gap: 4px; }

670

.anp-row { position: relative; display: grid; grid-template-columns: 24px 1fr 70px 70px 60px 60px; align-items: center; gap: 10px; padding: 10px 12px; background: rgba(255,255,255,0.02); border-radius: 8px; font-size: 13px; overflow: hidden; text-decoration: none; color: inherit; cursor: pointer; }

671

.anp-row:hover { background: rgba(255,255,255,0.05); }

672

.anp-row.is-selected { background: rgba(168,85,247,0.12); border: 1px solid rgba(168,85,247,0.3); }

673

.anp-row::before { content: ""; position: absolute; left: 0; top: 0; bottom: 0; width: var(--pct, 0%); background: linear-gradient(90deg, rgba(34,211,238,0.18) 0%, rgba(168,85,247,0.06) 100%); border-radius: 8px 0 0 8px; z-index: 0; }

674

.anp-row > * { position: relative; z-index: 1; }

675

.anp-rank { color: #6f7787; font-weight: 700; font-size: 11px; text-align: center; }

676

.anp-name { color: #cfd6e0; font-family: 'SF Mono', Menlo, monospace; font-size: 12px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }

677

.anp-stat { font-size: 11px; font-weight: 600; text-align: right; display: inline-flex; align-items: center; justify-content: flex-end; gap: 4px; }

678

.anp-stat.ok { color: #4ade80; }

679

.anp-stat.bad { color: #f87171; }

680

.anp-stat .dot { display: inline-block; width: 6px; height: 6px; border-radius: 50%; }

681

.anp-stat.ok .dot { background: #22c55e; }

682

.anp-stat.bad .dot { background: #ef4444; }

683

.anp-share { color: #8893a4; font-size: 11px; text-align: right; font-weight: 600; }

684

.anp-cnt { color: #fff; font-weight: 700; font-size: 13px; text-align: right; }

685

.anp-empty { text-align: center; padding: 30px 12px; color: #8893a4; font-size: 13px; }

686

687

.anp-drill { position: relative; padding: 24px 26px; background: linear-gradient(160deg, rgba(168,85,247,0.06), rgba(255,255,255,0.01)); border: 1px solid rgba(168,85,247,0.18); border-radius: 16px; margin: 0 0 22px; }

688

.anp-drill-head .lbl { font-size: 11px; color: #c4a3f0; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; margin-bottom: 6px; }

689

.anp-drill-head .path-string { color: #fff; font-family: 'SF Mono', Menlo, monospace; font-size: 13px; word-break: break-all; margin-bottom: 14px; }

690

.anp-drill-stats { display: flex; gap: 18px; flex-wrap: wrap; font-size: 12px; color: #8893a4; padding-bottom: 16px; border-bottom: 1px solid rgba(255,255,255,0.06); margin-bottom: 18px; }

691

.anp-drill-stats strong { color: #fff; margin-left: 6px; }

692

.anp-drill-stats .ok strong { color: #4ade80; }

693

.anp-drill-stats .bad strong { color: #f87171; }

694

.anp-drill-stats .dim strong { color: #cbd5e1; }

695

.anp-drill-steps { display: flex; flex-direction: column; gap: 14px; }

696

.anp-step { display: grid; gap: 6px; }

697

.anp-step-head { display: grid; grid-template-columns: 28px 1fr auto; align-items: center; gap: 10px; }

698

.anp-step-num { width: 26px; height: 26px; border-radius: 7px; background: rgba(168,85,247,0.25); color: #c4a3f0; font-weight: 800; font-size: 12px; display: flex; align-items: center; justify-content: center; }

699

.anp-step-name { color: #cfd6e0; font-family: 'SF Mono', Menlo, monospace; font-size: 13px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }

700

.anp-step-meta { color: #8893a4; font-size: 12px; font-weight: 600; }

701

.anp-step-bar { height: 10px; background: rgba(255,255,255,0.04); border-radius: 5px; overflow: hidden; }

702

.anp-step-fill { height: 100%; background: linear-gradient(90deg, #a855f7 0%, #22d3ee 100%); border-radius: 5px; }

703

.anp-step-markers { display: flex; gap: 8px; margin-left: 38px; }

704

.anp-marker { display: inline-flex; align-items: center; gap: 4px; padding: 3px 8px; border-radius: 12px; font-size: 11px; font-weight: 700; }

705

.anp-marker.ok { background: rgba(34,197,94,0.18); color: #4ade80; }

706

.anp-marker.bad { background: rgba(239,68,68,0.18); color: #f87171; }

707

</style>

708

CSS

709

}

710

711

1;