Skip to content

Database

Table: utm_tracking

Column Type Description
id int unsigned, PK Auto-increment primary key
order_id bigint WooCommerce order ID (wp_posts.ID)
subscription_id bigint, nullable WooCommerce Subscriptions ID (wp_posts.ID)
source varchar(255) utm_source
medium varchar(255) utm_medium
campaign varchar(255) utm_campaign
term varchar(255) utm_term
content varchar(255) utm_content
entry_date datetime When the customer visited the site with this UTM data
conversion tinyint(1) 1 if this UTM was the last touch before checkout

One order may have up to two rows: one for the initial UTM touch and one for the most recent UTM touch before checkout. Because the plugin tracks a UTM history of 2, only the latest row is marked conversion = 1. See Tracking flow for details.

ER Diagram

erDiagram
    wp_posts {
        bigint ID PK
        string post_type
        string post_status
    }
    utm_tracking {
        int id PK
        bigint order_id FK
        bigint subscription_id FK
        varchar source
        varchar medium
        varchar campaign
        varchar term
        varchar content
        datetime entry_date
        tinyint conversion
    }

    wp_posts ||--o{ utm_tracking : "order_id"
    wp_posts ||--o| utm_tracking : "subscription_id"

Creating the table

Call UTMTrackingStore::create_table() during plugin activation:

register_activation_hook( __FILE__, function () {
    UTMTracker\Storage\UTMTrackingStore::create_table();
} );