prefix . 'prli_clicks';
$links_table = $wpdb->prefix . 'prli_links';
/* ====== EDIT THESE TWO MAPS TO MATCH YOUR PRETTY LINK SLUGS ====== */
// Platform source links (slug => platform name). One per platform, all pointing to your bio page.
$SOURCES = array(
'yt' => 'YouTube',
'tt' => 'TikTok',
'ig' => 'Instagram',
'th' => 'Threads',
'li' => 'LinkedIn',
'fb' => 'Facebook',
);
// Your bio page buttons (slug => button label). Leave as-is or change to your real slugs.
$BUTTONS = array(
'car' => 'Need a Car?',
'workbook' => 'The RAE Method Workbook',
'vault' => 'Access The VONSHAE Vault',
'funding' => 'Get Business Funding',
);
/* ================================================================= */
// Fixed color per platform (also used for the chart legend).
$COLORS = array(
'YouTube' => '#E24B4A',
'TikTok' => '#0F6E56',
'Instagram' => '#D4537E',
'Threads' => '#888780',
'LinkedIn' => '#185FA5',
'Facebook' => '#7F77DD',
);
// Confirm the Pretty Links tables exist before querying.
$tables_ok = ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $clicks_table ) ) === $clicks_table );
// Date windows (uses your WordPress timezone).
$now = current_time( 'timestamp' );
$days = 30;
$labels = array();
$dateKeys = array();
for ( $i = $days - 1; $i >= 0; $i-- ) {
$ts = strtotime( "-$i days", $now );
$dateKeys[] = date( 'Y-m-d', $ts );
$labels[] = date( 'M j', $ts );
}
$start30 = date( 'Y-m-d 00:00:00', strtotime( '-' . ( $days - 1 ) . ' days', $now ) );
$start7 = date( 'Y-m-d H:i:s', strtotime( '-7 days', $now ) );
$srcSlugs = array_keys( $SOURCES );
$btnSlugs = array_keys( $BUTTONS );
$lineData = array(); // platform => [dateKey => count]
$srcTotals30 = array(); // platform => 30-day total
$visits7 = 0;
$topSource = '';
$topSourceCnt = 0;
$taps7 = 0;
$topButtons = array();
if ( $tables_ok && $srcSlugs ) {
$in = implode( ',', array_fill( 0, count( $srcSlugs ), '%s' ) );
// Per-day per-platform visits, last 30 days.
$sql = "SELECT l.slug AS slug, DATE(c.created_at) AS d, COUNT(*) AS n
FROM $clicks_table c JOIN $links_table l ON l.id = c.link_id
WHERE l.slug IN ($in) AND c.created_at >= %s
GROUP BY l.slug, d";
$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( $srcSlugs, array( $start30 ) ) ), ARRAY_A );
foreach ( (array) $rows as $r ) {
$plat = isset( $SOURCES[ $r['slug'] ] ) ? $SOURCES[ $r['slug'] ] : $r['slug'];
$lineData[ $plat ][ $r['d'] ] = (int) $r['n'];
$srcTotals30[ $plat ] = ( isset( $srcTotals30[ $plat ] ) ? $srcTotals30[ $plat ] : 0 ) + (int) $r['n'];
}
// Visits + top source, last 7 days.
$sql = "SELECT l.slug AS slug, COUNT(*) AS n
FROM $clicks_table c JOIN $links_table l ON l.id = c.link_id
WHERE l.slug IN ($in) AND c.created_at >= %s
GROUP BY l.slug";
$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( $srcSlugs, array( $start7 ) ) ), ARRAY_A );
foreach ( (array) $rows as $r ) {
$n = (int) $r['n'];
$visits7 += $n;
$plat = isset( $SOURCES[ $r['slug'] ] ) ? $SOURCES[ $r['slug'] ] : $r['slug'];
if ( $n > $topSourceCnt ) {
$topSourceCnt = $n;
$topSource = $plat;
}
}
}
if ( $tables_ok && $btnSlugs ) {
$in = implode( ',', array_fill( 0, count( $btnSlugs ), '%s' ) );
$sql = "SELECT l.slug AS slug, COUNT(*) AS n
FROM $clicks_table c JOIN $links_table l ON l.id = c.link_id
WHERE l.slug IN ($in) AND c.created_at >= %s
GROUP BY l.slug ORDER BY n DESC";
$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( $btnSlugs, array( $start7 ) ) ), ARRAY_A );
foreach ( (array) $rows as $r ) {
$n = (int) $r['n'];
$taps7 += $n;
$topButtons[] = array(
'label' => isset( $BUTTONS[ $r['slug'] ] ) ? $BUTTONS[ $r['slug'] ] : $r['slug'],
'n' => $n,
);
}
}
$tapRate = $visits7 > 0 ? round( $taps7 / $visits7 * 100 ) : 0;
// Build the chart payloads.
arsort( $srcTotals30 );
$total30 = array_sum( $srcTotals30 );
$lineDatasets = array();
foreach ( $srcTotals30 as $plat => $tot ) {
$series = array();
foreach ( $dateKeys as $dk ) {
$series[] = isset( $lineData[ $plat ][ $dk ] ) ? $lineData[ $plat ][ $dk ] : 0;
}
$lineDatasets[] = array(
'label' => $plat,
'data' => $series,
'color' => isset( $COLORS[ $plat ] ) ? $COLORS[ $plat ] : '#7F77DD',
'total' => $tot,
'pct' => $total30 > 0 ? round( $tot / $total30 * 100 ) : 0,
);
}
$barLabels = array_keys( $srcTotals30 );
$barData = array_values( $srcTotals30 );
$barColors = array();
foreach ( $barLabels as $p ) {
$barColors[] = isset( $COLORS[ $p ] ) ? $COLORS[ $p ] : '#7F77DD';
}
$has_data = $total30 > 0;
?>
Traffic Back Office - Rae Vonshae
Traffic Back Office
Where your visitors are coming from — pulled live from your Pretty Links data.
I could not find your Pretty Links tables (
No traffic recorded yet.
Create your six source Pretty Links (
Create your six source Pretty Links (
/yt, /tt, /ig, /th, /li, /fb) all pointing to your bio page, then put the matching link in each platform's bio. As soon as people start tapping, your charts fill in here automatically. If you used different slugs, edit the $SOURCES list at the top of this file to match.
Visits, last 7 days
Link taps, last 7 days
Top source
Tap rate
%
Visits by platform
Last 30 days, by day.
%
Total visits by source
Last 30 days.
What people tapped
Bio page buttons, last 7 days.
| Button | Taps |
|---|---|
No button taps recorded in the last 7 days yet.
Times use your WordPress timezone. Numbers come straight from Pretty Links, so they match what you see in the plugin.