marketing-analytics

Marketing Analytics

UTM Tracking Setup

UTM Parameter Standartlari

https://example.com/landing?
  utm_source=google           # Trafik kaynagi (google, facebook, newsletter)
  &utm_medium=cpc             # Kanal tipi (cpc, email, social, organic)
  &utm_campaign=spring_2026   # Kampanya adi
  &utm_term=saas+analytics    # Arama terimi (paid search)
  &utm_content=hero_banner    # Reklam varyanti (A/B test)

UTM Naming Convention

UTM Builder (TypeScript)

interface UTMConfig {
  baseUrl: string;
  source: string;
  medium: string;
  campaign: string;
  term?: string;
  content?: string;
}

function buildUTMUrl(config: UTMConfig): string {
  const params = new URLSearchParams();
  params.set("utm_source", config.source.toLowerCase());
  params.set("utm_medium", config.medium.toLowerCase());
  params.set("utm_campaign", config.campaign.toLowerCase().replace(/\s+/g, "_"));
  if (config.term) params.set("utm_term", config.term.toLowerCase());
  if (config.content) params.set("utm_content", config.content.toLowerCase());

  const separator = config.baseUrl.includes("?") ? "&" : "?";
  return `${config.baseUrl}${separator}${params.toString()}`;
}

// UTM parametrelerini parse et ve kaydet
function captureUTM(): UTMParams | null {
  const params = new URLSearchParams(window.location.search);
  const utm: UTMParams = {
    source: params.get("utm_source") || undefined,
    medium: params.get("utm_medium") || undefined,
    campaign: params.get("utm_campaign") || undefined,
    term: params.get("utm_term") || undefined,
    content: params.get("utm_content") || undefined,
  };

  if (utm.source) {
    // First-touch ve last-touch ayri kaydet
    if (!localStorage.getItem("utm_first_touch")) {
      localStorage.setItem("utm_first_touch", JSON.stringify({ ...utm, timestamp: Date.now() }));
    }
    localStorage.setItem("utm_last_touch", JSON.stringify({ ...utm, timestamp: Date.now() }));
    return utm;
  }
  return null;
}

Attribution Modeling

Attribution Modelleri

Multi-Touch Attribution Query

-- U-Shaped Attribution
WITH touchpoints AS (
  SELECT
    conversion_id,
    user_id,
    channel,
    touch_timestamp,
    ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY touch_timestamp) AS touch_order,
    COUNT(*) OVER (PARTITION BY conversion_id) AS total_touches
  FROM marketing_touches
  WHERE conversion_id IS NOT NULL
),
attributed AS (
  SELECT
    conversion_id,
    channel,
    CASE
      WHEN total_touches = 1 THEN 1.0
      WHEN total_touches = 2 THEN 0.5
      WHEN touch_order = 1 THEN 0.4                            -- first touch
      WHEN touch_order = total_touches THEN 0.4                -- last touch
      ELSE 0.2 / (total_touches - 2)                           -- middle touches
    END AS attribution_weight
  FROM touchpoints
)
SELECT
  channel,
  ROUND(SUM(attribution_weight), 2) AS attributed_conversions,
  ROUND(SUM(attribution_weight * c.revenue), 2) AS attributed_revenue
FROM attributed a
JOIN conversions c ON a.conversion_id = c.id
GROUP BY channel
ORDER BY attributed_revenue DESC;

Markov Chain Attribution

interface TransitionMatrix {
  [fromState: string]: {
    [toState: string]: number;  // probability
  };
}

// Removal effect: Her kanalin conversion'a katki oranini hesapla
function calculateRemovalEffect(
  matrix: TransitionMatrix,
  channels: string[]
): Record<string, number> {
  const baseConversionRate = simulateConversions(matrix, channels);
  const effects: Record<string, number> = {};

  for (const channel of channels) {
    const withoutChannel = channels.filter(c => c !== channel);
    const reducedRate = simulateConversions(matrix, withoutChannel);
    effects[channel] = (baseConversionRate - reducedRate) / baseConversionRate;
  }

  // Normalize to sum to 1
  const total = Object.values(effects).reduce((a, b) => a + b, 0);
  for (const channel of channels) {
    effects[channel] = effects[channel] / total;
  }

  return effects;
}

CAC (Customer Acquisition Cost)

CAC Hesaplama

interface CACMetrics {
  totalMarketingSpend: number;        // Toplam marketing harcamasi
  totalSalesSpend: number;            // Toplam sales harcamasi (maas dahil)
  newCustomers: number;               // Kazanilan musteri sayisi
  period: string;                     // "2026-Q1"
}

function calculateCAC(metrics: CACMetrics): {
  blendedCAC: number;
  paidCAC: number;
  organicCAC: number;
} {
  const totalSpend = metrics.totalMarketingSpend + metrics.totalSalesSpend;
  return {
    blendedCAC: totalSpend / metrics.newCustomers,
    paidCAC: metrics.totalMarketingSpend / (metrics.newCustomers * 0.6),  // %60 paid
    organicCAC: (metrics.totalSalesSpend * 0.3) / (metrics.newCustomers * 0.4),
  };
}

CAC by Channel Query

SELECT
  channel,
  SUM(spend) AS total_spend,
  COUNT(DISTINCT conversion_user_id) AS new_customers,
  ROUND(SUM(spend) / NULLIF(COUNT(DISTINCT conversion_user_id), 0), 2) AS cac,
  ROUND(AVG(first_order_value), 2) AS avg_first_order
FROM (
  SELECT
    a.channel,
    a.spend,
    c.user_id AS conversion_user_id,
    c.revenue AS first_order_value
  FROM ad_spend a
  LEFT JOIN conversions c ON c.attributed_channel = a.channel
    AND c.conversion_date BETWEEN a.date AND a.date + INTERVAL '30 days'
  WHERE a.date >= CURRENT_DATE - INTERVAL '90 days'
) channel_data
GROUP BY channel
ORDER BY cac;

CAC Benchmarks

ROAS (Return on Ad Spend)

ROAS Calculator

function calculateROAS(
  revenue: number,
  adSpend: number
): { roas: number; roasPercentage: number; profitable: boolean } {
  const roas = revenue / adSpend;
  return {
    roas: Math.round(roas * 100) / 100,
    roasPercentage: Math.round(roas * 100),
    profitable: roas > 1,
  };
}

// Hedef ROAS hesapla (break-even icin)
function targetROAS(grossMargin: number): number {
  // Minimum ROAS = 1 / Gross Margin
  // %70 margin -> minimum 1.43 ROAS
  return Math.round((1 / grossMargin) * 100) / 100;
}

ROAS Dashboard Query

SELECT
  campaign_name,
  channel,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) AS ctr_pct,
  SUM(spend) AS spend,
  SUM(conversions) AS conversions,
  ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cost_per_conversion,
  SUM(revenue) AS revenue,
  ROUND(SUM(revenue) / NULLIF(SUM(spend), 0), 2) AS roas,
  ROUND(SUM(revenue) - SUM(spend), 2) AS profit
FROM campaign_performance
WHERE date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY campaign_name, channel
ORDER BY roas DESC;

ROAS Benchmarks

Conversion Tracking

Conversion Event Setup

interface ConversionEvent {
  event_name: string;
  value: number;
  currency: string;
  conversion_type: "micro" | "macro";
  attribution_window_days: number;
}

const conversionEvents: ConversionEvent[] = [
  // Macro conversions (primary goals)
  { event_name: "purchase_completed", value: 0, currency: "USD", conversion_type: "macro", attribution_window_days: 30 },
  { event_name: "subscription_started", value: 0, currency: "USD", conversion_type: "macro", attribution_window_days: 30 },

  // Micro conversions (leading indicators)
  { event_name: "trial_started", value: 0, currency: "USD", conversion_type: "micro", attribution_window_days: 14 },
  { event_name: "demo_requested", value: 50, currency: "USD", conversion_type: "micro", attribution_window_days: 7 },
  { event_name: "email_subscribed", value: 5, currency: "USD", conversion_type: "micro", attribution_window_days: 7 },
];

// Server-side conversion tracking
async function trackConversion(
  event: ConversionEvent,
  userId: string,
  metadata: Record<string, unknown>
): Promise<void> {
  // 1. Internal analytics
  await analytics.track(event.event_name, {
    ...metadata,
    conversion_type: event.conversion_type,
    value: metadata.value || event.value,
  });

  // 2. Facebook Conversions API
  await sendFacebookConversion(event, userId, metadata);

  // 3. Google Ads offline conversion
  await sendGoogleOfflineConversion(event, userId, metadata);
}

Conversion Funnel Query

-- Marketing funnel: Visit -> Lead -> MQL -> SQL -> Customer
SELECT
  'Visit' AS stage, COUNT(DISTINCT session_id) AS count, 100.0 AS pct
FROM sessions WHERE date >= CURRENT_DATE - INTERVAL '30 days'
UNION ALL
SELECT
  'Lead', COUNT(DISTINCT user_id),
  ROUND(100.0 * COUNT(DISTINCT user_id) /
    (SELECT COUNT(DISTINCT session_id) FROM sessions WHERE date >= CURRENT_DATE - INTERVAL '30 days'), 1)
FROM leads WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
UNION ALL
SELECT
  'MQL', COUNT(DISTINCT user_id),
  ROUND(100.0 * COUNT(DISTINCT user_id) /
    (SELECT COUNT(DISTINCT user_id) FROM leads WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'), 1)
FROM leads WHERE status = 'mql' AND created_at >= CURRENT_DATE - INTERVAL '30 days'
UNION ALL
SELECT
  'SQL', COUNT(DISTINCT user_id),
  ROUND(100.0 * COUNT(DISTINCT user_id) /
    (SELECT COUNT(DISTINCT user_id) FROM leads WHERE status = 'mql' AND created_at >= CURRENT_DATE - INTERVAL '30 days'), 1)
FROM leads WHERE status = 'sql' AND created_at >= CURRENT_DATE - INTERVAL '30 days'
UNION ALL
SELECT
  'Customer', COUNT(DISTINCT user_id),
  ROUND(100.0 * COUNT(DISTINCT user_id) /
    (SELECT COUNT(DISTINCT user_id) FROM leads WHERE status = 'sql' AND created_at >= CURRENT_DATE - INTERVAL '30 days'), 1)
FROM conversions WHERE date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY
  CASE stage
    WHEN 'Visit' THEN 1 WHEN 'Lead' THEN 2 WHEN 'MQL' THEN 3
    WHEN 'SQL' THEN 4 WHEN 'Customer' THEN 5
  END;

Landing Page Optimization

Key Metrics

Landing Page A/B Test Template

interface LandingPageTest {
  name: string;
  hypothesis: string;
  element: "headline" | "cta" | "hero_image" | "social_proof" | "pricing" | "layout";
  control: string;
  treatment: string;
  primary_metric: string;
  traffic_split: number;
  duration_days: number;
}

const tests: LandingPageTest[] = [
  {
    name: "headline_benefit_vs_feature",
    hypothesis: "Benefit-focused headline, feature-focused'a gore %15 daha yuksek conversion verir",
    element: "headline",
    control: "AI-Powered Analytics Dashboard",
    treatment: "Get Insights 10x Faster With AI",
    primary_metric: "cta_click_rate",
    traffic_split: 0.5,
    duration_days: 14,
  },
];

Email Marketing Metrics

KPI Dashboard

Email Performance Query

SELECT
  campaign_name,
  sent_at::date AS send_date,
  COUNT(*) AS sent,
  SUM(CASE WHEN delivered THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN opened THEN 1 ELSE 0 END) AS opens,
  ROUND(100.0 * SUM(CASE WHEN opened THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN delivered THEN 1 ELSE 0 END), 0), 1) AS open_rate,
  SUM(CASE WHEN clicked THEN 1 ELSE 0 END) AS clicks,
  ROUND(100.0 * SUM(CASE WHEN clicked THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN delivered THEN 1 ELSE 0 END), 0), 1) AS ctr,
  SUM(CASE WHEN converted THEN 1 ELSE 0 END) AS conversions,
  SUM(revenue) AS total_revenue,
  ROUND(SUM(revenue) / NULLIF(SUM(CASE WHEN delivered THEN 1 ELSE 0 END), 0), 2) AS revenue_per_email
FROM email_campaigns
WHERE sent_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY campaign_name, send_date
ORDER BY send_date DESC;

Social Media Analytics

Platform Metrics

Social ROI Template

interface SocialROI {
  platform: string;
  totalSpend: number;           // paid + organic (time cost)
  impressions: number;
  engagements: number;
  websiteTraffic: number;
  conversions: number;
  revenue: number;
}

function calculateSocialROI(data: SocialROI): {
  cpm: number;                  // Cost per 1000 impressions
  cpe: number;                  // Cost per engagement
  cpc: number;                  // Cost per click (to website)
  cpa: number;                  // Cost per acquisition
  roi: number;                  // Return on Investment %
} {
  return {
    cpm: (data.totalSpend / data.impressions) * 1000,
    cpe: data.totalSpend / data.engagements,
    cpc: data.totalSpend / data.websiteTraffic,
    cpa: data.totalSpend / data.conversions,
    roi: ((data.revenue - data.totalSpend) / data.totalSpend) * 100,
  };
}

SEO Metrics

Core SEO KPIs

Content Performance Scoring

interface ContentScore {
  url: string;
  organic_traffic_30d: number;
  avg_position: number;
  ctr: number;
  conversions: number;
  backlinks: number;
  word_count: number;
  last_updated: string;
}

function scoreContent(content: ContentScore): {
  score: number;
  action: "keep" | "update" | "consolidate" | "remove";
} {
  let score = 0;

  // Traffic (0-30)
  if (content.organic_traffic_30d > 1000) score += 30;
  else if (content.organic_traffic_30d > 100) score += 20;
  else if (content.organic_traffic_30d > 10) score += 10;

  // Rankings (0-25)
  if (content.avg_position <= 3) score += 25;
  else if (content.avg_position <= 10) score += 15;
  else if (content.avg_position <= 20) score += 5;

  // Conversions (0-25)
  if (content.conversions > 10) score += 25;
  else if (content.conversions > 1) score += 15;
  else if (content.conversions > 0) score += 5;

  // Freshness (0-10)
  const daysSinceUpdate = (Date.now() - new Date(content.last_updated).getTime()) / 86400000;
  if (daysSinceUpdate < 90) score += 10;
  else if (daysSinceUpdate < 180) score += 5;

  // Backlinks (0-10)
  if (content.backlinks > 10) score += 10;
  else if (content.backlinks > 0) score += 5;

  let action: "keep" | "update" | "consolidate" | "remove";
  if (score >= 70) action = "keep";
  else if (score >= 40) action = "update";
  else if (score >= 20) action = "consolidate";
  else action = "remove";

  return { score, action };
}

Marketing Funnel Optimization

Funnel Stage Metrics

TOFU (Awareness)           MOFU (Consideration)        BOFU (Decision)
-------------------        ----------------------      ------------------
Impressions                Email subscribers            Demo requests
Website visitors           Content downloads            Trial signups
Social followers           Webinar attendees            Quote requests
Blog readers               Return visitors              Free trial users
                           Newsletter opens             Pricing page visits

Channel Efficiency Matrix

SELECT
  channel,
  SUM(spend) AS spend,
  COUNT(DISTINCT visitor_id) AS visitors,
  COUNT(DISTINCT lead_id) AS leads,
  COUNT(DISTINCT customer_id) AS customers,
  ROUND(SUM(spend) / NULLIF(COUNT(DISTINCT visitor_id), 0), 2) AS cost_per_visit,
  ROUND(SUM(spend) / NULLIF(COUNT(DISTINCT lead_id), 0), 2) AS cost_per_lead,
  ROUND(SUM(spend) / NULLIF(COUNT(DISTINCT customer_id), 0), 2) AS cac,
  ROUND(100.0 * COUNT(DISTINCT lead_id) / NULLIF(COUNT(DISTINCT visitor_id), 0), 1) AS visit_to_lead_pct,
  ROUND(100.0 * COUNT(DISTINCT customer_id) / NULLIF(COUNT(DISTINCT lead_id), 0), 1) AS lead_to_customer_pct,
  SUM(customer_revenue) AS revenue,
  ROUND(SUM(customer_revenue) / NULLIF(SUM(spend), 0), 2) AS roas
FROM marketing_data
WHERE date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY channel
ORDER BY roas DESC;

Anti-Patterns