Marketing Analytics

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)

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;
}

-- 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;

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;
}

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),
  };
}

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;

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;
}

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;

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);
}

-- 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;

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,
  },
];

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;

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,
  };
}

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 };
}

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

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;