UTM Tracking System

What We Capture

UTM parameters from URLs plus referer header:

• utm_source, utm_medium, utm_campaign, utm_term, utm_content
• referer_url from HTTP Referer header

When We Capture

1. Session Storage: UTMs stored in server session (primary method) when user arrives with UTM parameters
2. Cookie Backup: UTMs also stored in browser cookie for 7 days as fallback
3. Database Storage: UTMs saved to database during:
   • Order creation → subscription_init or retail_init event
   • User registration → register event

Important: Capture happens at order creation, NOT payment completion (prevents data loss during external payment gateway redirects).

Data Storage

UTMs are stored using a session-first approach with cookie fallback:

• Primary: Server-side session storage (works in all browsers including Instagram)
• Backup: Browser cookies (7-day duration)
• Database: Final storage when orders are created or users register

Analysis Queries

Conversion by Source

SELECT 
    utm_source,
    COUNT(*) as orders_created,
    SUM(CASE WHEN orders.status = 'approved' THEN 1 ELSE 0 END) as approved,
    ROUND((SUM(CASE WHEN orders.status = 'approved' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2) as conversion_rate
FROM utms 
JOIN orders ON utms.order_id = orders.id 
WHERE utms.event IN ('subscription_init', 'retail_init')
  AND utms.tenant_id = :tenant_id
GROUP BY utm_source
ORDER BY orders_created DESC;

Revenue by Campaign

SELECT 
    utm_campaign,
    orders.currency,
    COUNT(*) as orders,
    SUM(orders.amount) as total_revenue,
    AVG(orders.amount) as avg_order_value
FROM utms 
JOIN orders ON utms.order_id = orders.id 
WHERE orders.status = 'approved'
  AND utms.event IN ('subscription_init', 'retail_init')
  AND utms.tenant_id = :tenant_id
GROUP BY utm_campaign, orders.currency
ORDER BY total_revenue DESC;

Event Types

• subscription_init: Recurring subscription order created
• retail_init: One-time purchase order created
• register: New user registration

Key Features

• Universal compatibility: Works in all browsers including Instagram in-app browser and Safari with privacy settings
• Attribution window: UTMs remain valid for 7 days to prevent incorrect attribution from old campaigns
• Automatic cleanup: Expired UTMs are automatically cleared
• No JavaScript required: Tracking works server-side without client dependencies