CDL Recruiting Ad Spend Attribution: SQL Models & Cost-Per-Hired-Driver Optimization in TMS

The Opaque Recruiting Spend Sinkhole

Motor carriers routinely spend between $35,000 and $85,000 per month on job boards, programmatic advertising networks, social media campaigns, and paid search without knowing which sources actually produce drivers who stay past 90 days. The problem is not the spend itself. The problem is the complete absence of attribution between the advertising platform, the applicant tracking system (typically Tenstreet), the TMS (Truckmate, McLeod, or TMW), and final driver settlement and retention records.

Without a unified data model, recruiting teams continue to fund channels that generate high application volume but deliver poor orientation completion rates and even worse 90-day retention. Meanwhile, high-performing channels such as targeted geographic Facebook/Instagram campaigns and structured driver referral programs remain underfunded.

First-Touch vs Last-Touch vs Multi-Touch Attribution

Most carriers rely on last-touch attribution because it is the easiest to implement. If a driver completes orientation, the final click or job board that delivered the application receives full credit. This approach systematically overvalues job boards that specialize in late-funnel applicants while undervaluing awareness campaigns that plant the first seed.

A robust multi-touch model assigns weighted credit across the entire driver lifecycle: initial lead capture, Tenstreet profile creation, MVR and background check initiation, orientation attendance, first dispatch (EDI 204), first load tender acceptance (EDI 214), driver settlement generation, and ultimately 90-day retention. The model must survive the complex handoffs between recruiting, safety, operations, and payroll departments.

The foundation is a persistent DriverID that travels from the ATS through the TMS and into the payroll and settlement systems. This single key enables accurate joining of recruiting source data with operational outcomes.

Real-World SQL Attribution Queries

The following simplified schema and queries illustrate how carriers connect recruiting channels to true economic outcomes. These patterns work across Truckmate, McLeod, and TMW environments when the proper integration tables are available.

-- Core dimension and fact tables
CREATE TABLE dim_driver (
    driver_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    orientation_complete_date DATE,
    termination_date DATE,
    days_retained INT,
    lead_source VARCHAR(100),
    utm_campaign VARCHAR(100),
    first_touch_channel VARCHAR(50),
    last_touch_channel VARCHAR(50)
);

CREATE TABLE fact_recruiting_spend (
    date DATE,
    channel VARCHAR(50),
    campaign_name VARCHAR(100),
    spend_amount DECIMAL(12,2),
    clicks INT,
    applications INT
);

-- Calculate true Cost Per Seated Truck (CPST) by channel
SELECT 
    d.first_touch_channel,
    COUNT(DISTINCT d.driver_id) as hired_drivers,
    SUM(CASE WHEN d.orientation_complete_date IS NOT NULL THEN 1 ELSE 0 END) as completed_orientation,
    AVG(CASE WHEN d.orientation_complete_date IS NOT NULL THEN 1.0 ELSE 0.0 END) as orientation_pct,
    SUM(CASE WHEN d.days_retained >= 90 THEN 1 ELSE 0 END) as retained_90_days,
    AVG(CASE WHEN d.days_retained >= 90 THEN 1.0 ELSE 0.0 END) as retention_pct,
    SUM(s.spend_amount) as total_spend,
    SUM(s.spend_amount) / NULLIF(SUM(CASE WHEN d.days_retained >= 90 THEN 1 ELSE 0 END), 0) as cost_per_retained_driver
FROM dim_driver d
LEFT JOIN fact_recruiting_spend s 
    ON s.channel = d.first_touch_channel 
    AND s.date BETWEEN DATEADD(month, -3, d.hire_date) AND d.hire_date
GROUP BY d.first_touch_channel
ORDER BY cost_per_retained_driver ASC;

These queries, when run against properly integrated data from your TMS and ATS, reveal the true Cost-Per-Seated-Truck and Cost-Per-Retained-Day. The results almost always differ dramatically from the reports produced by the job boards themselves.

Driver Recruiting Channel Performance Matrix

Sample aggregated data from multiple Truckmate and McLeod carriers (2023-2024). True Cost Per Seated Driver = Total Channel Spend / 90-Day Retained Drivers.

Reallocating Recruitment Capital Based on ROI

Once the SQL attribution model is running, the reallocation decisions become data-driven rather than opinion-driven. Carriers typically terminate or sharply reduce spend on channels with true Cost-Per-Seated-Truck above $4,000 and reallocate those budgets into driver referral programs, organic geographic campaigns, and high-performing social media creative that has demonstrated both strong orientation completion and retention.

The economic impact is substantial. A carrier spending $650,000 annually on recruiting can realistically reduce that number by $200,000-$280,000 while increasing the number of seated trucks by improving the quality of the driver pipeline.

Independent Advisory Perspective