extra_segment is back
<?php
// ✅ Enable error reporting for debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// ✅ Include necessary files
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/yoti-config.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/add-debug-messages.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/generate_tubes_join_link_nats_API.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/yoti-class-simple.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/db.php';
addDebugMessage("📌 sessions-init-db.php STARTED - All Required Files Included");
// ✅ **Ensure DEFAULT_AVS_STATES is defined**
if (!defined('DEFAULT_AVS_STATES')) {
define('DEFAULT_AVS_STATES', []);
}
// ✅ **Retrieve Root Domain & Site Version (Make site_version optional)**
function getRootDomain($host) {
$parts = explode('.', $host);
return (count($parts) >= 3) ? implode('.', array_slice($parts, -2)) : $host;
}
$root_domain = getRootDomain($_SERVER['HTTP_HOST']);
$site_version_parts = explode('.', $_SERVER['HTTP_HOST']);
$site_version = (count($site_version_parts) > 2) ? $site_version_parts[0] : 'www'; // Default to 'www'
// ✅ **Get Current Path (Where User Landed)**
$current_path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
addDebugMessage("🌍 Current Path Detected: {$current_path}");
// ✅ **Extract Extra Segment from URL (Track Separately)**
function getExtraSegmentFromUrl() {
$pathSegments = explode('/', trim(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), '/'));
return count($pathSegments) > 1 ? $pathSegments[1] : null;
}
$extra_segment = getExtraSegmentFromUrl();
addDebugMessage("🔄 Detected Extra Segment: " . ($extra_segment ?? 'NONE'));
// ✅ **Generate or Retrieve Unique User ID (Persistent Across Pages)**
$yoti_data_id = $_COOKIE['yoti_data'] ?? bin2hex(random_bytes(16));
setcookie("yoti_data", $yoti_data_id, time() + 86400, "/", "", true, true);
addDebugMessage("🆔 User's Yoti Data ID: {$yoti_data_id}");
// ✅ **Fetch Existing Session Data from DB**
$query = $pdo->prepare("SELECT * FROM yoti_sessions WHERE yoti_data_id = :yoti_data_id LIMIT 1");
$query->execute(['yoti_data_id' => $yoti_data_id]);
$session_data = $query->fetch(PDO::FETCH_ASSOC);
// ✅ **Ensure status is always defined**
$status = $session_data['status'] ?? 'PENDING';
addDebugMessage("📢 Yoti Status: {$status}");
// ✅ **Get & Store NATS Code**
$default_nats_codes = [
'tubes' => 'MTA2MDgzLjYzLjEuMTY1LjAuMC4wLjAuMA',
'tubes2' => 'MTA2MDgzLjYzLjEuMTY3LjAuMC4wLjAuMA'
];
$nats_code = $_REQUEST['nats'] ?? $_COOKIE['nats'] ?? $default_nats_codes[$site_version] ?? null;
addDebugMessage("🔍 Final NATS Code: " . ($nats_code ?? 'NONE'));
if (!empty($nats_code) && !isset($_COOKIE['nats'])) {
setcookie("nats", $nats_code, time() + 2592000, "/", "", false, true);
}
// ✅ **Construct Base URL (Keep Correct Site Version)**
$base_url = ($site_version !== $root_domain)
? "https://{$site_version}.{$root_domain}"
: "https://{$root_domain}";
// ✅ **Construct Final Return URL (With Path + NATS)**
$return_url = "{$base_url}{$current_path}";
if (!empty($nats_code)) {
$return_url .= (strpos($return_url, '?') === false ? '?' : '&') . "nats={$nats_code}";
}
addDebugMessage("🚀 Final Return URL: {$return_url}");
// ✅ **Store Session Data Including Extra Segment**
$insert = $pdo->prepare("
INSERT INTO yoti_sessions (
yoti_data_id, site_version, root_domain, extra_segment, nats_code, return_url, status
) VALUES (
:yoti_data_id, :site_version, :root_domain, :extra_segment, :nats_code, :return_url, 'PENDING'
)
ON DUPLICATE KEY UPDATE
site_version = VALUES(site_version),
root_domain = VALUES(root_domain),
extra_segment = VALUES(extra_segment),
nats_code = VALUES(nats_code),
return_url = VALUES(return_url),
status = VALUES(status)
");
// ✅ **Execute the query with updated variables**
$insert->execute([
'yoti_data_id' => $yoti_data_id,
'site_version' => $site_version,
'root_domain' => $root_domain,
'extra_segment' => $extra_segment,
'nats_code' => $nats_code,
'return_url' => $return_url,
]);
addDebugMessage("✅ Inserted/Updated Yoti session in database.");
// ✅ **Redirect User**
addDebugMessage("🚀 Redirecting user to: {$return_url}");
header("Location: {$return_url}");
exit; // 🚀 **STOP execution after redirect**
Separate new form return
<?php
// ✅ Enable error reporting for debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// ✅ Include necessary files
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/yoti-config.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/add-debug-messages.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/generate_tubes_join_link_nats_API.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/yoti-class-simple.php';
require_once '/home/httpd/html/tubes.nakedsword.com/php_includes/db.php';
addDebugMessage("📌 sessions-init-db.php STARTED - All Required Files Included");
// ✅ **Ensure DEFAULT_AVS_STATES is defined**
if (!defined('DEFAULT_AVS_STATES')) {
define('DEFAULT_AVS_STATES', []);
}
// ✅ **Retrieve Root Domain & Site Version (Make site_version optional)**
function getRootDomain($host) {
$parts = explode('.', $host);
return (count($parts) >= 3) ? implode('.', array_slice($parts, -2)) : $host;
}
$root_domain = getRootDomain($_SERVER['HTTP_HOST']);
$site_version_parts = explode('.', $_SERVER['HTTP_HOST']);
$site_version = (count($site_version_parts) > 2) ? $site_version_parts[0] : 'www'; // Default to 'www'
// ✅ **Get Current Path (Where User Landed)**
$current_path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
addDebugMessage("🌍 Current Path Detected: {$current_path}");
// ✅ **Extract Extra Segment from URL (Track Separately)**
function getExtraSegmentFromUrl() {
$pathSegments = explode('/', trim(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), '/'));
return count($pathSegments) > 1 ? $pathSegments[1] : null;
}
$extra_segment = getExtraSegmentFromUrl();
addDebugMessage("🔄 Detected Extra Segment: " . ($extra_segment ?? 'NONE'));
// ✅ **Generate or Retrieve Unique User ID (Persistent Across Pages)**
$yoti_data_id = $_COOKIE['yoti_data'] ?? bin2hex(random_bytes(16));
setcookie("yoti_data", $yoti_data_id, time() + 86400, "/", "", true, true);
addDebugMessage("🆔 User's Yoti Data ID: {$yoti_data_id}");
// ✅ **Fetch Existing Session Data from DB**
$query = $pdo->prepare("SELECT * FROM yoti_sessions WHERE yoti_data_id = :yoti_data_id LIMIT 1");
$query->execute(['yoti_data_id' => $yoti_data_id]);
$session_data = $query->fetch(PDO::FETCH_ASSOC);
// ✅ **Ensure status is always defined**
$status = $session_data['status'] ?? 'PENDING';
addDebugMessage("📢 Yoti Status: {$status}");
// ✅ **Get & Store NATS Code**
$default_nats_codes = [
'tubes' => 'MTA2MDgzLjYzLjEuMTY1LjAuMC4wLjAuMA',
'tubes2' => 'MTA2MDgzLjYzLjEuMTY3LjAuMC4wLjAuMA'
];
$nats_code = $_REQUEST['nats'] ?? $_COOKIE['nats'] ?? $default_nats_codes[$site_version] ?? null;
addDebugMessage("🔍 Final NATS Code: " . ($nats_code ?? 'NONE'));
if (!empty($nats_code) && !isset($_COOKIE['nats'])) {
setcookie("nats", $nats_code, time() + 2592000, "/", "", false, true);
}
// ✅ **Construct Base URL (Keep Correct Site Version)**
$base_url = ($site_version !== $root_domain)
? "https://{$site_version}.{$root_domain}"
: "https://{$root_domain}";
// ✅ **Construct Clean Return URL (Use Extra Segment When Available)**
if (!empty($extra_segment)) {
$return_url = "{$base_url}/{$extra_segment}";
} else {
$return_url = "{$base_url}{$current_path}";
}
// ✅ **Append NATS Code If Needed**
if (!empty($nats_code)) {
$return_url .= (strpos($return_url, '?') === false ? '?' : '&') . "nats={$nats_code}";
}
addDebugMessage("🚀 Final Clean Return URL: {$return_url}");
// ✅ **Construct NATS TRACK URL (For Special Cases)**
$track_url = "{$base_url}/track/{$nats_code}/{$extra_segment}";
addDebugMessage("🔄 Possible TRACK URL (If Needed): {$track_url}");
// ✅ **Store Session Data Including Extra Segment**
$insert = $pdo->prepare("
INSERT INTO yoti_sessions (
yoti_data_id, site_version, root_domain, extra_segment, nats_code, return_url, track_url, status
) VALUES (
:yoti_data_id, :site_version, :root_domain, :extra_segment, :nats_code, :return_url, :track_url, 'PENDING'
)
ON DUPLICATE KEY UPDATE
site_version = VALUES(site_version),
root_domain = VALUES(root_domain),
extra_segment = VALUES(extra_segment),
nats_code = VALUES(nats_code),
return_url = VALUES(return_url),
track_url = VALUES(track_url),
status = VALUES(status)
");
// ✅ **Execute the query with updated variables**
$insert->execute([
'yoti_data_id' => $yoti_data_id,
'site_version' => $site_version,
'root_domain' => $root_domain,
'extra_segment' => $extra_segment,
'nats_code' => $nats_code,
'return_url' => $return_url,
'track_url' => $track_url,
]);
addDebugMessage("✅ Inserted/Updated Yoti session in database.");
// ✅ **Redirect User to Return URL**
addDebugMessage("🚀 Redirecting user to: {$return_url}");
header("Location: {$return_url}");
exit; // 🚀 **STOP execution after redirect**
Some Redirect Logic
1️⃣ Where yoti_data_id Comes From
yoti_data_id is generated (or retrieved) at the start of the script:
$yoti_data_id = $_COOKIE['yoti_data'] ?? bin2hex(random_bytes(16));
setcookie("yoti_data", $yoti_data_id, time() + 86400, "/", "", true, true);
addDebugMessage("🆔 User's Yoti Data ID: {$yoti_data_id}");
• If a yoti_data cookie exists, we reuse it.
• Otherwise, we generate a new random ID and store it in a cookie.
• Why? This ensures the user has a persistent ID across page loads.
✅ Should it be in the database?
Yes! yoti_data_id is a unique identifier, so we should store it.
2️⃣ Add track_url & current_path to the Database
We already store return_url and extra_segment, but we should also store:
• ✅ track_url → Gives us the option to push users through /track/.
• ✅ current_path → Helps debugging and logging—it tells us where the user landed before AVS.
🔥 Updated Database Insert Code
Now, we add yoti_data_id, track_url, and current_path to the database.
// ✅ **Store Session Data Including Yoti ID, Extra Segment, Track URL, and Current Path**
$insert = $pdo->prepare("
INSERT INTO yoti_sessions (
yoti_data_id, site_version, root_domain, extra_segment, current_path,
nats_code, return_url, track_url, status
) VALUES (
:yoti_data_id, :site_version, :root_domain, :extra_segment, :current_path,
:nats_code, :return_url, :track_url, 'PENDING'
)
ON DUPLICATE KEY UPDATE
site_version = VALUES(site_version),
root_domain = VALUES(root_domain),
extra_segment = VALUES(extra_segment),
current_path = VALUES(current_path),
nats_code = VALUES(nats_code),
return_url = VALUES(return_url),
track_url = VALUES(track_url),
status = VALUES(status)
");
// ✅ **Execute the query with all variables**
$insert->execute([
'yoti_data_id' => $yoti_data_id, // ✅ Unique User ID
'site_version' => $site_version, // ✅ Site version (www/tubes/tubes2)
'root_domain' => $root_domain, // ✅ Root domain
'extra_segment' => $extra_segment, // ✅ Extra path segment
'current_path' => $current_path, // ✅ Where the user actually landed
'nats_code' => $nats_code, // ✅ NATS tracking code
'return_url' => $return_url, // ✅ Clean return URL
'track_url' => $track_url, // ✅ Optional /track/ URL
]);
addDebugMessage("✅ Inserted/Updated Yoti session in database.");
🔥 What This Fixes
✔ Stores yoti_data_id in the database → Now we can track users properly.
✔ Stores current_path → Helps debugging & logging.
✔ Stores track_url → Gives us the option to push users through NATS tracking when needed.
🔥 Final Expected Behavior in DB
| Field | Example Value | Purpose |
|---|---|---|
| yoti_data_id | abc123xyz456 | Unique user ID (stored in a cookie). |
| site_version | tubes | Tells us which subdomain they landed on. |
| root_domain | sitename.com | Keeps track of the site. |
| extra_segment | free | Extra part of the URL (/free, /dailyplayer, etc.). |
| current_path | /free | The actual page the user landed on. |
| nats_code | MTA2MDgzLjYz… | The NATS tracking code. |
| return_url | https://tubes.sitename.com/free?nats=CODE | Where we send users back after AVS. |
| track_url | https://tubes.sitename.com/track/CODE/free | Optional NATS tracking URL. |
🔥 Now, Everything is Tracked Correctly!
🚀 Users will always return correctly—whether it’s clean URLs or /track/ URLs.
🚀 We store everything we need for tracking, debugging, and flexible redirects.
🚀 No data is lost—we can always reconstruct the correct flow.
Matrix
✅ This is Now Fully Fixed & Ready for Testing!
Want any final refinements before we run with this? 😃
1. Final Version of Sorting Out First Timer vs Matrix Users
We’ll start by distinguishing the “First Timer” and “Matrix” users and managing their data accordingly.
PHP Code for Handling First Timer vs Matrix User
<?php
// Fetch the Yoti Reference ID and pricing metric from the URL
$yoti_reference_id = $_GET['yoti_reference_id'] ?? null;
$pricing_metric = $_GET['pricing_metric'] ?? 'default'; // Default to 'default' if not set
// Check if the user has visited before (first time vs matrix user)
if ($yoti_reference_id) {
// Fetch the NATS code and stored asset folder for this user
$query = $pdo->prepare("SELECT assets_folder, nats_code FROM yoti_sessions WHERE yoti_reference_id = :yoti_reference_id LIMIT 1");
$query->execute(['yoti_reference_id' => $yoti_reference_id]);
$user_data = $query->fetch(PDO::FETCH_ASSOC);
if ($user_data) {
// Matrix User (returning user with assets folder and NATS code)
$assets_folder = $user_data['assets_folder'];
$nats_code = $user_data['nats_code'];
addDebugMessage("Returning Matrix User: Assets Folder: {$assets_folder}, NATS Code: {$nats_code}");
} else {
// First Timer (new user, no data in the DB)
addDebugMessage("New User - First Timer");
// Default values for first time users
$assets_folder = 'assets'; // Default assets folder
$nats_code = generateNewNatsCode(); // New NATS code for first-time users
addDebugMessage("Generated new NATS Code: {$nats_code}");
}
} else {
addDebugMessage("No Yoti Reference ID found.");
// Handle the case when Yoti Reference ID is not present (e.g., show an error or request the user to log in)
}
?>
2. Final Version of DB Schema
Here’s the DB schema showing what you have and the new additions related to NATS tracking, pricing metrics, and user states.
Existing Schema (Yoti Sessions Table)
CREATE TABLE yoti_sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
yoti_reference_id VARCHAR(255) UNIQUE NOT NULL,
assets_folder VARCHAR(255) DEFAULT 'assets', -- Folder containing user-specific assets (default: 'assets')
nats_code VARCHAR(255) DEFAULT NULL, -- Store NATS code for tracking
program_id INT DEFAULT NULL, -- Store the pricing program ID
pricing_metric VARCHAR(50) DEFAULT 'default', -- Track the pricing metric (metrix_1, metrix_2, etc.)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
New Schema Additions (If Needed)
• Refresh Count Tracking (Optional): To track how many times the user has refreshed to calculate dynamic pricing.
ALTER TABLE yoti_sessions
ADD COLUMN refresh_count INT DEFAULT 0; -- Tracks how many times a user has refreshed
3. Important Values to Keep Track Of
Complete Example Implementation
<?php
// Fetch Yoti Reference ID from URL (or wherever it's stored)
$yoti_reference_id = $_GET['yoti_reference_id'] ?? null;
// Fetch pricing metric (e.g., 'metrix_1', 'metrix_2', etc.)
$pricing_metric = $_GET['pricing_metric'] ?? 'default';
// Fetch refresh count (this could be from session or DB)
$refresh_count = 2; // Just as an example, usually you'd fetch this dynamically
// Determine the appropriate assets folder based on pricing metric
switch ($pricing_metric) {
case 'metrix_1':
$assets_folder = 'metrix_1_assets';
$assets_folder_g = 'metrix_1_assets_g';
break;
case 'metrix_2':
$assets_folder = 'metrix_2_assets';
$assets_folder_g = 'metrix_2_assets_g';
break;
case 'metrix_3':
$assets_folder = 'metrix_3_assets';
$assets_folder_g = 'metrix_3_assets_g';
break;
default:
$assets_folder = 'assets'; // Default folder
$assets_folder_g = 'assets_g'; // Default G folder
break;
}
// Set the pricing info based on the refresh count
switch ($refresh_count) {
case 3:
$pricing_status = 'Full Price';
$price = 100;
break;
case 2:
$pricing_status = 'Regional Price';
$price = 75;
break;
case 1:
$pricing_status = 'Cheap Price';
$price = 50;
break;
default:
$pricing_status = 'Default Price';
$price = 50; // Default price
break;
}
// Decode NATS code (for extracting details)
function decodeNats($nats_code) {
// You would decode your NATS code here based on how it's structured
$decodedData = base64_decode($nats_code); // Simple example, adjust as needed
// Simulated decoded values (you would replace this with actual decoding logic)
$decodedValues = [
'nats_AffiliateID' => '12345',
'nats_ProgramID' => '67890',
'nats_SiteID' => '10',
'nats_CampaignID' => '999',
// Add more as necessary
];
return $decodedValues;
}
// Example NATS code for the demo (in practice, you'd fetch this from the URL or another source)
$nats_code = $_GET['nats_code'] ?? 'MTA1OTUyLjYzLjEuMTY1LjAuMC4wLjAuMA'; // Default example NATS code
$decodedNats = decodeNats($nats_code); // Decode the NATS code
// Construct the TRACK link
$base_url = "https://tubes.nakedsword.com/";
// You might want to modify the track link to include user-specific parameters
$track_link = "{$base_url}track/{$nats_code}/{$pricing_status}-price";
// Now we can render the page
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Dynamic Pricing Page</title>
</head>
<body>
<!-- Hidden Field for Yoti Reference ID -->
<input type="hidden" id="yoti_reference_id" name="yoti_reference_id" value="<?= htmlspecialchars($yoti_reference_id) ?>">
<h1>Welcome to our Special Offer Page</h1>
<p>Pricing Status: <?= htmlspecialchars($pricing_status) ?></p>
<p>Price: $<?= htmlspecialchars($price) ?></p>
<!-- Dynamically load the asset folder for the graphics -->
<div>
<img src="<?= $assets_folder ?>/image1.jpg" alt="Special Image 1">
<img src="<?= $assets_folder ?>/image2.jpg" alt="Special Image 2">
<!-- Add more images or assets based on the pricing metric -->
</div>
<!-- Redirect the user to the TRACK link -->
<p>Click below to access the special pricing page:</p>
<a href="<?= $track_link ?>" target="_blank">Get My Special Offer</a>
<!-- JS to auto-submit the form or handle dynamic behavior -->
<script>
// For example, you could auto-submit the hidden form or dynamically load assets
document.querySelector('a').addEventListener('click', function() {
alert("Redirecting you to your special pricing offer.");
});
</script>
</body>
</html>
These are the key values that need to be tracked across users:
For First Timers:
• yoti_reference_id: Unique ID to identify the user (from Yoti verification).
• nats_code: New tracking code generated when a first-time user joins (for tracking user behavior).
• assets_folder: Initially assigned default folder (e.g., ‘assets’ or ‘assets_g’ depending on whether AVS is required).
• pricing_metric: Default pricing (e.g., default or metrix_1, metrix_2, metrix_3 for dynamic pricing logic).
For Matrix Users:
• yoti_reference_id: Used to fetch the user’s existing data.
• assets_folder: Stores which folder the user is associated with (e.g., ‘assets’, ‘assets_g’, or a metrix folder).
• nats_code: Track the user’s specific journey (e.g., through tracking programs, campaigns, etc.).
• program_id: To differentiate between programs linked to pricing.
• refresh_count: To determine the pricing tier (cheap, regional, full price).
How the System Should Work:
• First-Time Users: They are assigned a new nats_code, and their assets_folder is set to the default folder (assets). They go through the sign-up flow, and after that, they receive dynamic content and pricing, based on pricing_metric and refresh behavior.
• Matrix Users: These users are returning, so they get their existing nats_code, assets_folder, and pricing_metric. If they are returning after more than a month, they will get a new URL (with -RE) to re-render the page and fetch any new content.
4. Summary
To summarize, here’s the flow and what needs to be tracked for First-Time Users and Matrix Users:
First-Time Users:
• Generate and store a new nats_code and yoti_reference_id for tracking.
• Assign a default assets_folder (e.g., ‘assets’ or ‘assets_g’).
• Track their visit and offer dynamic pricing and asset folders based on their journey.
Matrix Users:
• Fetch existing nats_code, assets_folder, and pricing_metric based on yoti_reference_id.
• If they return after more than a month, append -RE to the URL to trigger a page refresh with updated content.
• Track how many times they refresh to manage pricing changes (refresh_count).
What We Need to Track:
1. yoti_reference_id: A unique identifier for the user, fetched from the URL.
2. nats_code: Track users’ specific journey for better targeting and dynamic content.
3. assets_folder: Used for content and image folder structure (could be ‘assets’, ‘assets_g’, or ‘metrix_*’).
4. pricing_metric: Tracks which pricing structure is applied based on their activity and behavior (first-time, metrix_1, etc.).
5. refresh_count: Used to determine pricing tiers based on how many times the user refreshes the page.
This setup should help streamline your workflow for both first-time and returning users while handling dynamic pricing and asset loading efficiently. Let me know if you need anything else or any further clarification!
Yes, storing all the parts of the original NATS code (which you’ve decoded into precomputedNatsValues) is a great idea for tracking and managing user data. It allows you to easily link back to the original NATS code, while also capturing detailed event information related to the user and their sign-up activity. You can use these values for personalized tracking, segmentation, and improving analytics.
Here’s a breakdown of the key information you should store and how it maps back to your event details:
Key NATS Code Parts to Store:
• NATS Affiliate ID (nats_AffiliateID)
• NATS Program ID (nats_ProgramID)
• NATS Site ID (nats_SiteID)
• NATS Tour ID (nats_TourID)
• NATS Campaign ID (nats_CampaignID)
• NATS Adtool IDs (nats_AdtoolID, nats_AdToolSub1ID, nats_AdToolSub2ID)
These values will be pulled from the decoded NATS string and stored in the database, allowing you to reference them later.
What to Store in the Database:
• Yoti Reference ID: So you can always link back the user’s data to their specific session.
• Decoded NATS Values: Store the decoded values (nats_SiteName, nats_TourID, nats_AffiliateID, etc.) so you can easily refer to them for tracking.
• User Interaction Data: For each sign-up event, store the event details that you’ve mentioned (user ID, email hash, pricing, etc.).
Example of Storing NATS Information in Database:
CREATE TABLE `nats_sign_up_events` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`yoti_reference_id` VARCHAR(255) NOT NULL,
`nats_AffiliateID` VARCHAR(255),
`nats_ProgramID` VARCHAR(255),
`nats_SiteID` VARCHAR(255),
`nats_TourID` VARCHAR(255),
`nats_CampaignID` VARCHAR(255),
`nats_AdtoolID` VARCHAR(255),
`nats_AdToolSub1ID` VARCHAR(255),
`nats_AdToolSub2ID` VARCHAR(255),
`event_category` VARCHAR(255),
`event_action` VARCHAR(255),
`event_label` VARCHAR(255),
`event_currency` VARCHAR(255),
`event_value` DECIMAL(10,2),
`price` DECIMAL(10,2),
`quantity` INT DEFAULT 1,
`coupon` VARCHAR(255),
`discount` DECIMAL(10,2) DEFAULT 0,
`item_name` VARCHAR(255),
`item_id` VARCHAR(255),
`item_brand` VARCHAR(255),
`affiliation` VARCHAR(255),
`item_category` VARCHAR(255),
`method` VARCHAR(50),
`time_sign_up` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`hashed_email` VARCHAR(255),
`hostname` VARCHAR(255),
`currency` VARCHAR(50),
`value` DECIMAL(10,2)
);
Store Event Details When User Signs Up:
After you’ve decoded the NATS values (as you mentioned in your JavaScript), you can store those details along with the event information (price, item details, etc.) in the database for tracking.
Here’s an example of how to insert the event details into the database:
// Example of storing the event details in the database
$insert = $pdo->prepare("
INSERT INTO nats_sign_up_events (
yoti_reference_id, nats_AffiliateID, nats_ProgramID, nats_SiteID, nats_TourID,
nats_CampaignID, nats_AdtoolID, nats_AdToolSub1ID, nats_AdToolSub2ID,
event_category, event_action, event_label, event_currency, event_value,
price, quantity, coupon, discount, item_name, item_id, item_brand, affiliation,
item_category, method, time_sign_up, hashed_email, hostname, currency, value
) VALUES (
:yoti_reference_id, :nats_AffiliateID, :nats_ProgramID, :nats_SiteID, :nats_TourID,
:nats_CampaignID, :nats_AdtoolID, :nats_AdToolSub1ID, :nats_AdToolSub2ID,
:event_category, :event_action, :event_label, :event_currency, :event_value,
:price, :quantity, :coupon, :discount, :item_name, :item_id, :item_brand, :affiliation,
:item_category, :method, :time_sign_up, :hashed_email, :hostname, :currency, :value
)
");
// Bind the parameters (example with your precomputed NATS values and other fields)
$insert->execute([
'yoti_reference_id' => $yoti_reference_id,
'nats_AffiliateID' => $precomputedNatsValues['nats_AffiliateID'],
'nats_ProgramID' => $precomputedNatsValues['nats_ProgramID'],
'nats_SiteID' => $precomputedNatsValues['nats_SiteID'],
'nats_TourID' => $precomputedNatsValues['nats_TourID'],
'nats_CampaignID' => $precomputedNatsValues['nats_CampaignID'],
'nats_AdtoolID' => $precomputedNatsValues['nats_AdtoolID'],
'nats_AdToolSub1ID' => $precomputedNatsValues['nats_AdToolSub1ID'],
'nats_AdToolSub2ID' => $precomputedNatsValues['nats_AdToolSub2ID'],
'event_category' => 'nats_pre_join',
'event_action' => $optionId,
'event_label' => $_SERVER['HTTP_REFERER'] ?? 'Direct/No Referrer',
'event_currency' => $signUpValue === 0 ? "USD" : $priceCurrency,
'event_value' => $signUpValue,
'price' => $signUpValue,
'quantity' => 1,
'coupon' => '',
'discount' => 0,
'item_name' => "{$precomputedNatsValues['nats_SiteName']} | OptionId {$optionId} - {$priceCurrency}{$signUpValue}",
'item_id' => "{$precomputedNatsValues['nats_SiteName']} | OptionId {$optionId}",
'item_brand' => "{$precomputedNatsValues['nats_SiteName']} | Tour {$precomputedNatsValues['nats_TourID']}",
'affiliation' => "{$precomputedNatsValues['nats_SiteName']} | Affiliate {$precomputedNatsValues['nats_AffiliateID']}",
'item_category' => 'Membership',
'method' => 'email',
'time_sign_up' => date('Y-m-d H:i:s'),
'hashed_email' => $hashedEmail,
'hostname' => $_SERVER['HTTP_HOST'],
'currency' => $signUpValue === 0 ? "USD" : $priceCurrency,
'value' => $signUpValue
]);