Content Marketing ROI: Measuring What Matters for SaaS Growth

// First touch: the first piece of content a user encountered
export const getFirstTouchAttribution = createServerFn({ method: "GET" }).handler(
  async ({ data, context }: { data: { period: string } }) => {
    const result = await context.env.DB.prepare(`
      SELECT
        first_utm_source as source,
        first_utm_campaign as campaign,
        first_utm_content as content,
        COUNT(*) as signups,
        SUM(CASE WHEN subscription_status = 'active' THEN 1 ELSE 0 END) as conversions
      FROM users
      WHERE created_at > datetime('now', ?)
      GROUP BY first_utm_source, first_utm_campaign
      ORDER BY signups DESC
    `).bind(data.period).all()

    return result.results
  }
)

// Last touch: the content that drove the final conversion
export const getLastTouchAttribution = createServerFn({ method: "GET" }).handler(
  async ({ data, context }: { data: { period: string } }) => {
    const result = await context.env.DB.prepare(`
      SELECT
        source,
        campaign,
        COUNT(*) as conversions
      FROM utm_clicks
      WHERE user_id IN (
        SELECT id FROM users
        WHERE created_at > datetime('now', ?)
        AND subscription_status = 'active'
      )
      ORDER BY created_at DESC
      LIMIT 1
    `).bind(data.period).all()

    return result.results
  }
)

// Track content production costs
export const trackContentCost = createServerFn({ method: "POST" }).handler(
  async ({ data }: { data: {
    contentId: string
    hoursSpent: number
    hourlyRate: number
    distributionCost: number
  }}) => {
    const totalCost = data.hoursSpent * data.hourlyRate + data.distributionCost

    await contentDB.prepare(`
      INSERT INTO content_costs
        (id, content_id, hours_spent, hourly_rate,
         distribution_cost, total_cost, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).bind(
      crypto.randomUUID(),
      data.contentId,
      data.hoursSpent,
      data.hourlyRate,
      data.distributionCost,
      totalCost,
      Date.now()
    ).run()

    return { totalCost }
  }
)

export const getContentCac = createServerFn({ method: "GET" }).handler(
  async ({}, { context }) => {
    const results = await context.env.DB.prepare(`
      SELECT
        c.content_type,
        SUM(cc.total_cost) as total_cost,
        COUNT(DISTINCT u.id) as attributed_signups,
        SUM(cc.total_cost) / COUNT(DISTINCT u.id) as cac
      FROM content_costs cc
      JOIN content c ON c.id = cc.content_id
      LEFT JOIN users u ON u.first_utm_content = c.slug
      GROUP BY c.content_type
      ORDER BY cac ASC
    `).all()

    return results.results
  }
)

export const getLtvByContentSource = createServerFn({ method: "GET" }).handler(
  async ({}, { context }) => {
    const results = await context.env.DB.prepare(`
      SELECT
        first_utm_source as source,
        COUNT(*) as users,
        AVG(CASE WHEN status = 'active' THEN mrr ELSE 0 END) as avg_mrr,
        SUM(CASE WHEN status = 'active' THEN mrr ELSE 0 END) as total_mrr
      FROM users u
      LEFT JOIN subscriptions s ON s.user_id = u.id
      WHERE u.created_at > datetime('now', '-180 days')
      GROUP BY first_utm_source
      HAVING users > 5
      ORDER BY total_mrr DESC
    `).all()

    return results.results
  }
)

export const getContentROIDashboard = createServerFn({ method: "GET" }).handler(
  async ({}, { context }) => {
    const [performance, cac, ltv] = await Promise.all([
      // Content performance overview
      context.env.DB.prepare(`
        SELECT
          c.title,
          c.slug,
          c.published_at,
          c.views,
          c.avg_read_time,
          at.conversions
        FROM content c
        LEFT JOIN attribution at ON at.content_slug = c.slug
        ORDER BY at.conversions DESC
        LIMIT 20
      `).all(),

      // CAC by content type
      getContentCac(),

      // LTV analysis
      getLtvByContentSource(),
    ])

    return { performance, cac, ltv }
  }
)

export const createContentPlan = createServerFn({ method: "POST" }).handler(
  async ({ data, context }: { data: {
    title: string
    type: string
    targetKeyword: string
    estimatedCost: number
    expectedSignups: number
  }}) => {
    const roiProjection = {
      cac: data.estimatedCost / data.expectedSignups,
      projectedMonthlyRevenue: data.expectedSignups * 29, // $29 avg MRR
      breakEvenMonths: data.estimatedCost / (data.expectedSignups * 29),
      sixMonthReturn: (data.expectedSignups * 29 * 6) / data.estimatedCost,
    }

    // Store in database for tracking
    await context.env.DB.prepare(`
      INSERT INTO content_plan
        (title, type, target_keyword, estimated_cost,
         expected_signups, projected_cac, projected_roi, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).bind(
      data.title, data.type, data.targetKeyword,
      data.estimatedCost, data.expectedSignups,
      roiProjection.cac, roiProjection.sixMonthReturn,
      Date.now()
    ).run()

    return roiProjection
  }
)