Inconsistent UTM Tracking: Why Same Campaign Shows Multiple Values

// Analyze exported data
function findInconsistencies(sources) {
    const normalized = {};
 
    sources.forEach(source => {
        const key = source.toLowerCase().trim();
        if (!normalized[key]) {
            normalized[key] = [];
        }
        normalized[key].push(source);
    });
 
    // Find sources with multiple variations
    const inconsistent = Object.entries(normalized)
        .filter(([, variations]) => variations.length > 1)
        .map(([key, variations]) => ({
            normalized: key,
            variations: variations,
            count: variations.length
        }));
 
    return inconsistent;
}
 
// Usage
const sources = ['facebook', 'Facebook', 'FACEBOOK', 'google', 'Google'];
console.log(findInconsistencies(sources));
// Output: [
//   { normalized: 'facebook', variations: ['facebook', 'Facebook', 'FACEBOOK'], count: 3 },
//   { normalized: 'google', variations: ['google', 'Google'], count: 2 }
// ]

// Clean URLs before deploying
function standardizeUtmUrl(url) {
    const urlObj = new URL(url);
    const params = new URLSearchParams();
    const utmParams = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_content', 'utm_term'];
 
    utmParams.forEach(param => {
        const value = urlObj.searchParams.get(param);
        if (value) {
            // Normalize: lowercase, trim whitespace
            const normalized = value.toLowerCase().trim();
            params.append(param, normalized);
        }
    });
 
    // Preserve non-UTM parameters
    for (const [key, value] of urlObj.searchParams.entries()) {
        if (!utmParams.includes(key)) {
            params.append(key, value);
        }
    }
 
    urlObj.search = params.toString();
    return urlObj.toString();
}
 
// Usage
const messy = 'site.com?utm_source=Facebook&utm_source=fb&utm_campaign=SPRING';
const clean = standardizeUtmUrl(messy);
console.log(clean);
// Output: site.com?utm_source=facebook&utm_campaign=spring

// Validate before campaign launch
function validateUtmConsistency(url, standards) {
    const issues = [];
    const params = new URL(url).searchParams;
 
    Object.entries(standards).forEach(([param, expectedValue]) => {
        const actualValue = params.get(param);
 
        if (!actualValue) {
            issues.push(`Missing ${"{"}{"{"}param{"}"}{"}"}}`);
        } else if (actualValue !== expectedValue) {
            issues.push(`${"{"}{"{"}param{"}"}{"}"}}: expected "${"{"}{"{"}expectedValue{"}"}{"}"}}", got "${"{"}{"{"}actualValue{"}"}{"}"}}"`);
        }
 
        // Check for duplicates
        if (params.getAll(param).length > 1) {
            issues.push(`Duplicate ${"{"}{"{"}param{"}"}{"}"}} detected`);
        }
 
        // Check for case inconsistency
        if (actualValue && actualValue !== actualValue.toLowerCase()) {
            issues.push(`${"{"}{"{"}param{"}"}{"}"}} contains uppercase characters`);
        }
    });
 
    return {
        valid: issues.length === 0,
        issues
    };
}
 
// Usage
const url = 'site.com?utm_source=Facebook&utm_campaign=spring';
const standards = {
    utm_source: 'facebook',
    utm_campaign: 'spring'
};
 
console.log(validateUtmConsistency(url, standards));
// { valid: false, issues: ['utm_source contains uppercase characters'] }

// Create single source of truth for UTM values
const UTM_STANDARDS = {
    sources: {
        facebook: 'facebook',
        google: 'google',
        instagram: 'instagram',
        email: 'email'
    },
    mediums: {
        paid: 'paid',
        organic: 'organic',
        email: 'email',
        social: 'social'
    }
};
 
function buildStandardUtmUrl(base, source, medium, campaign) {
    // Enforce standards
    const standardSource = UTM_STANDARDS.sources[source.toLowerCase()];
    const standardMedium = UTM_STANDARDS.mediums[medium.toLowerCase()];
 
    if (!standardSource || !standardMedium) {
        throw new Error('Use standard UTM values only');
    }
 
    const params = new URLSearchParams({
        utm_source: standardSource,
        utm_medium: standardMedium,
        utm_campaign: campaign.toLowerCase().replace(/\s+/g, '_')
    });
 
    return `${"{"}{"{"}base{"}"}{"}"}}?${params.toString()}`;
}
 
// Usage
const url = buildStandardUtmUrl('site.com', 'Facebook', 'PAID', 'Spring Sale');
console.log(url);
// Output: site.com?utm_source=facebook&utm_medium=paid&utm_campaign=spring_sale
 
// Trying non-standard value throws error
buildStandardUtmUrl('site.com', 'fb', 'paid', 'spring');
// Error: Use standard UTM values only

-- Merge inconsistent sources in BigQuery
SELECT
  LOWER(TRIM(traffic_source.source)) as normalized_source,
  SUM(sessions) as total_sessions,
  SUM(conversions) as total_conversions
FROM `project.dataset.events_*`
WHERE event_name = 'session_start'
GROUP BY normalized_source
 
-- This gives accurate totals despite inconsistent raw data