Skip to content

AutoGPT is the vision of accessible AI for everyone, to use and to build on. Our mission is to provide the tools, so that you can focus on what matters.

10.3 KB
Raw Blame
-- =============================================================
-- View: analytics.unit_economics_monthly
-- Looker source alias: (new)  |  Charts: 0
-- =============================================================
-- DESCRIPTION
--   One row per (user, calendar month): what the user did, what it
--   cost us in real provider spend, and what we charged them. Built
--   for pricing questions ("what does a free trial with a card on
--   file cost us per user per month?") and margin questions.
--
--   - tasks_human        human-started agent runs + human chat turns (a run
--                        the copilot started is counted once, via its turn)
--   - tasks_automated    schedule / webhook runs + scheduled follow-up turns
--   - platform_cost_usd  our provider cost (PlatformCostLog), split into
--                        agent / copilot / background (dream passes)
--   - credits_spent_usd  what we charged in credits (USAGE)
--   - gross_margin_usd   credits_spent_usd - platform_cost_usd (approximate:
--                        credits are prepaid, cost is incurred at use)
--   - cost_per_task_usd  platform_cost_usd / (tasks_human + tasks_automated)
--
--   subscription_tier is the user's CURRENT tier, not a snapshot
--   (there is no tier history table). Sum across users for platform
--   totals; average for "per user".
--
-- SOURCE TABLES
--   platform.AgentGraphExecution, platform.ChatMessage/ChatSession,
--   platform.PlatformCostLog, platform.CreditTransaction, platform.User
--
-- OUTPUT COLUMNS
--   user_id, email, subscription_tier, month (DATE, first of month),
--   tasks_human, tasks_automated, agent_runs, autopilot_turns, expert_turns,
--   active_days, platform_cost_usd, agent_cost_usd, copilot_cost_usd,
--   background_cost_usd, credits_spent_usd, credits_purchased_usd,
--   gross_margin_usd, cost_per_task_usd, cost_per_active_day_usd
--
-- WINDOW
--   Rolling 12 months
--
-- EXAMPLE QUERIES
--   -- Average cost to us per active user per month, by tier
--   SELECT month, subscription_tier,
--          AVG(platform_cost_usd) AS avg_cost_usd,
--          PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY platform_cost_usd) AS p90_cost_usd,
--          COUNT(*) AS users
--   FROM analytics.unit_economics_monthly
--   WHERE tasks_human + tasks_automated > 0
--   GROUP BY 1, 2 ORDER BY 1, 2;
--
--   -- Platform-wide margin per month
--   SELECT month, SUM(credits_spent_usd) AS charged, SUM(platform_cost_usd) AS cost,
--          SUM(gross_margin_usd) AS margin
--   FROM analytics.unit_economics_monthly GROUP BY 1 ORDER BY 1;
-- =============================================================

WITH runs AS (
  SELECT
    ge."userId"                                                          AS user_id,
    DATE_TRUNC('month', ge."createdAt")::date                            AS month,
    COUNT(*)                                                             AS agent_runs,
    COUNT(*) FILTER (WHERE ge."triggerSource" IS NULL
                        OR ge."triggerSource" IN ('manual', 'api', 'copilot'))
                                                                         AS agent_runs_human,
    COUNT(*) FILTER (WHERE ge."triggerSource" IN ('schedule', 'webhook'))
                                                                         AS agent_runs_automated,
    COUNT(*) FILTER (WHERE ge."triggerSource" = 'copilot')               AS agent_runs_copilot
  FROM platform."AgentGraphExecution" ge
  WHERE ge."createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
    AND ge."isDeleted" = FALSE
    AND ge."parentGraphExecutionId" IS NULL
    AND COALESCE(ge."stats"::jsonb->>'is_dry_run', 'false') <> 'true'
  GROUP BY 1, 2
),
turns AS (
  SELECT
    s."userId"                                                           AS user_id,
    DATE_TRUNC('month', m."createdAt")::date                             AS month,
    COUNT(*) FILTER (WHERE s."expertId" IS NULL
                       AND COALESCE(s."metadata"::jsonb->>'origin', 'interactive') <> 'automation')
                                                                         AS autopilot_turns,
    COUNT(*) FILTER (WHERE s."expertId" IS NOT NULL
                       AND COALESCE(s."metadata"::jsonb->>'origin', 'interactive') <> 'automation')
                                                                         AS expert_turns,
    COUNT(*) FILTER (WHERE COALESCE(s."metadata"::jsonb->>'origin', 'interactive') = 'automation')
                                                                         AS scheduled_turns
  FROM platform."ChatMessage" m
  JOIN platform."ChatSession" s ON s."id" = m."sessionId"
  WHERE m."role" = 'user'
    AND m."createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
    AND COALESCE(s."metadata"::jsonb->>'kind', 'normal') <> 'dream'
  GROUP BY 1, 2
),
-- Distinct calendar days on which a person did something (a human-started
-- run or a human turn; same predicate as tasks_human), across both surfaces,
-- so a user who runs agents on Monday and chats on Tuesday has two active
-- days, while a schedule firing every night adds none.
activity_days AS (
  SELECT user_id, DATE_TRUNC('month', day)::date AS month, COUNT(DISTINCT day) AS active_days
  FROM (
    SELECT ge."userId" AS user_id, ge."createdAt"::date AS day
    FROM platform."AgentGraphExecution" ge
    WHERE ge."createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
      AND ge."isDeleted" = FALSE
      AND ge."parentGraphExecutionId" IS NULL
      AND COALESCE(ge."stats"::jsonb->>'is_dry_run', 'false') <> 'true'
      AND (ge."triggerSource" IS NULL OR ge."triggerSource" IN ('manual', 'api'))
    UNION
    SELECT s."userId", m."createdAt"::date
    FROM platform."ChatMessage" m
    JOIN platform."ChatSession" s ON s."id" = m."sessionId"
    WHERE m."role" = 'user'
      AND m."createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
      AND COALESCE(s."metadata"::jsonb->>'kind', 'normal') <> 'dream'
      AND COALESCE(s."metadata"::jsonb->>'origin', 'interactive') <> 'automation'
  ) d
  GROUP BY 1, 2
),
costs AS (
  SELECT
    "userId"                                                             AS user_id,
    DATE_TRUNC('month', "createdAt")::date                               AS month,
    SUM(COALESCE("costMicrodollars", 0)) / 1000000.0                     AS platform_cost_usd,
    SUM(COALESCE("costMicrodollars", 0)) FILTER (
      WHERE COALESCE("blockName", '') NOT ILIKE 'copilot:%') / 1000000.0 AS agent_cost_usd,
    SUM(COALESCE("costMicrodollars", 0)) FILTER (
      WHERE "blockName" ILIKE 'copilot:%'
        AND COALESCE("metadata"::jsonb->>'source', 'copilot') <> 'dream_pass'
        AND "blockName" NOT ILIKE 'copilot:dream%') / 1000000.0          AS copilot_cost_usd,
    SUM(COALESCE("costMicrodollars", 0)) FILTER (
      WHERE "metadata"::jsonb->>'source' = 'dream_pass'
         OR "blockName" ILIKE 'copilot:dream%') / 1000000.0              AS background_cost_usd
  FROM platform."PlatformCostLog"
  WHERE "userId" IS NOT NULL
    AND "createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
  GROUP BY 1, 2
),
credits AS (
  -- Personal ledger plus org-billed rows attributed to the user who
  -- initiated them, so an org member's spend sits next to their cost.
  SELECT
    user_id,
    DATE_TRUNC('month', created_at)::date                                AS month,
    -COALESCE(SUM(amount) FILTER (WHERE type = 'USAGE'), 0) / 100.0      AS credits_spent_usd,
    COALESCE(SUM(amount) FILTER (WHERE type IN ('TOP_UP', 'SUBSCRIPTION')), 0) / 100.0
                                                                         AS credits_purchased_usd
  FROM (
    SELECT "userId" AS user_id, "amount" AS amount, "type"::text AS type, "createdAt" AS created_at
    FROM platform."CreditTransaction"
    WHERE "isActive" = TRUE
      AND "createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
    UNION ALL
    SELECT "initiatedByUserId", "amount", "type"::text, "createdAt"
    FROM platform."OrgCreditTransaction"
    WHERE "isActive" = TRUE AND "initiatedByUserId" IS NOT NULL
      AND "createdAt" > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'
  ) c
  GROUP BY 1, 2
),
keys AS (
  SELECT user_id, month FROM runs
  UNION SELECT user_id, month FROM turns
  UNION SELECT user_id, month FROM costs
  UNION SELECT user_id, month FROM credits
),
assembled AS (
  SELECT
    k.user_id,
    u."email"                                                            AS email,
    u."subscriptionTier"::text                                           AS subscription_tier,
    k.month,
    COALESCE(r.agent_runs_human, 0) - COALESCE(r.agent_runs_copilot, 0)
      + COALESCE(t.autopilot_turns, 0) + COALESCE(t.expert_turns, 0)     AS tasks_human,
    COALESCE(r.agent_runs_automated, 0) + COALESCE(t.scheduled_turns, 0) AS tasks_automated,
    COALESCE(r.agent_runs, 0)                                            AS agent_runs,
    COALESCE(t.autopilot_turns, 0)                                       AS autopilot_turns,
    COALESCE(t.expert_turns, 0)                                          AS expert_turns,
    COALESCE(ad.active_days, 0)                                          AS active_days,
    COALESCE(c.platform_cost_usd, 0)                                     AS platform_cost_usd,
    COALESCE(c.agent_cost_usd, 0)                                        AS agent_cost_usd,
    COALESCE(c.copilot_cost_usd, 0)                                      AS copilot_cost_usd,
    COALESCE(c.background_cost_usd, 0)                                   AS background_cost_usd,
    COALESCE(cr.credits_spent_usd, 0)                                    AS credits_spent_usd,
    COALESCE(cr.credits_purchased_usd, 0)                                AS credits_purchased_usd
  FROM keys k
  LEFT JOIN platform."User" u ON u."id" = k.user_id
  LEFT JOIN runs    r  ON r.user_id  = k.user_id AND r.month  = k.month
  LEFT JOIN activity_days ad ON ad.user_id = k.user_id AND ad.month = k.month
  LEFT JOIN turns   t  ON t.user_id  = k.user_id AND t.month  = k.month
  LEFT JOIN costs   c  ON c.user_id  = k.user_id AND c.month  = k.month
  LEFT JOIN credits cr ON cr.user_id = k.user_id AND cr.month = k.month
)
SELECT
  a.*,
  a.credits_spent_usd - a.platform_cost_usd                              AS gross_margin_usd,
  a.platform_cost_usd / NULLIF(a.tasks_human + a.tasks_automated, 0)     AS cost_per_task_usd,
  a.platform_cost_usd / NULLIF(a.active_days, 0)                         AS cost_per_active_day_usd
FROM assembled a