SaaS Magic Number with SQL

What the Magic Number measures

The SaaS Magic Number — popularized by Josh James at Omniture — measures the efficiency of sales and marketing spend. The formula is straightforward:

Magic Number = (Current Quarter ARR − Prior Quarter ARR) × 4 ÷ Prior Quarter S&M Spend

A Magic Number above 1.0 means you are generating more than a dollar of ARR for every dollar of S&M spend in the following four quarters. Between 0.75 and 1.0 is healthy but suggests you may need to optimize spend. Below 0.5 means you are burning capital inefficiently and should pause or restructure go-to-market investment.

The reason this metric is so widely used is that it is a single number that captures both growth and efficiency. Investors love it because it normalizes for company size — a $10M ARR company and a $100M ARR company can be compared on the same scale.

The data you need — and where it lives

To calculate the Magic Number in SQL, you need two clean data sources:

• Quarterly ARR — derived from a subscription events table or an MRR time-series. This is the same ARR view you should already have for board reporting.
• Quarterly S&M spend — from your general ledger or ERP, rolled up by department and tagged with a sales-and-marketing cost-center code.

The most common reason Magic Numbers are wrong is not the formula — it is the underlying data. S&M spend gets mixed with customer success, product marketing gets reclassified mid-year, and ARR is calculated as CARR by mistake. Before you write the SQL, validate the source tables with finance.

SQL data model for the Magic Number

I model this in two views: one that produces clean quarterly ARR, and one that produces clean quarterly S&M spend. A third view joins them and applies the formula.

-- views/quarterly_arr.sql
-- Rolling ARR at the end of each quarter
WITH monthly_arr AS (
  SELECT
    DATE_TRUNC('month', event_date) AS month,
    SUM(amount_delta) AS mrr
  FROM subscription_events
  WHERE event_type IN ('new_business', 'expansion', 'contraction', 'churn')
  GROUP BY 1
),
arr_by_month AS (
  SELECT
    month,
    SUM(mrr) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS arr
  FROM monthly_arr
)
SELECT
  DATE_TRUNC('quarter', month) AS quarter,
  MAX(arr) AS quarter_end_arr
FROM arr_by_month
GROUP BY 1
ORDER BY 1;

-- views/quarterly_sm_spend.sql
-- Fully-loaded S&M spend by quarter
SELECT
  DATE_TRUNC('quarter', transaction_date) AS quarter,
  SUM(amount) AS sm_spend
FROM general_ledger
WHERE cost_center IN ('SALES', 'MARKETING', 'SALES_OPS', 'SDR')
  AND transaction_date >= '2023-01-01'
GROUP BY 1
ORDER BY 1;

-- views/magic_number.sql
-- The metric itself
WITH arr AS (
  SELECT quarter, quarter_end_arr
  FROM quarterly_arr
),
sm AS (
  SELECT quarter, sm_spend
  FROM quarterly_sm_spend
)
SELECT
  arr.quarter,
  arr.quarter_end_arr,
  LAG(arr.quarter_end_arr) OVER (ORDER BY arr.quarter) AS prev_quarter_arr,
  sm.sm_spend,
  CASE
    WHEN sm.sm_spend > 0 THEN
      ROUND(
        ((arr.quarter_end_arr - LAG(arr.quarter_end_arr) OVER (ORDER BY arr.quarter)) * 4)
        / sm.sm_spend,
        2
      )
    ELSE NULL
  END AS magic_number
FROM arr
LEFT JOIN sm ON arr.quarter = sm.quarter
ORDER BY arr.quarter;

The LEFT JOIN is intentional. If a quarter has ARR but no S&M spend (for example, a pre-launch quarter), the Magic Number is NULL, not zero. A zero would imply infinite efficiency, which is misleading.

Common modeling mistakes

I have seen the same errors in Magic Number calculations across three companies. Here is what to watch for:

• Using CARR instead of ARR — CARR overstates growth because it includes contracted but not yet recognized revenue. The Magic Number should use actual ARR, not pipeline.
• Annualizing S&M spend — some teams multiply quarterly S&M by 4 to "match" the ARR numerator. This double-counts the annualization and deflates the ratio. The formula already annualizes the ARR change; S&M stays at quarterly value.
• Excluding headcount costs — if your S&M view only includes program spend (ads, events, tools) and omits payroll, you are not measuring fully-loaded efficiency. Include all GAAP-recognized S&M costs.
• Timing mismatches — the standard formula uses theprior quarter's S&M spend against the currentquarter's ARR growth. This assumes a one-quarter lag between spend and revenue impact. If your sales cycle is six months, adjust the lag in the SQL model.

Adjusting for sales-cycle length

The classic Magic Number assumes a ~90-day sales cycle, which is why it pairs current-quarter ARR growth with prior-quarter S&M. But enterprise SaaS with six-month cycles needs a different lag. The fix is a simple change in the window function:

-- For a 6-month sales cycle, compare current ARR to S&M from 2 quarters ago
CASE
  WHEN sm.sm_spend > 0 THEN
    ROUND(
      ((arr.quarter_end_arr - LAG(arr.quarter_end_arr, 2) OVER (ORDER BY arr.quarter)) * 4)
      / LAG(sm.sm_spend, 2) OVER (ORDER BY sm.quarter),
      2
    )
  ELSE NULL
END AS magic_number_6mo_cycle

Document the lag assumption in the view header. When you present the metric to the board, state the lag explicitly so nobody compares a 90-day-cycle Magic Number to a 180-day-cycle one.

Audit-readiness and data integrity

Like ARR and CARR, the Magic Number only survives audit if the underlying numbers are traceable. My rules:

1. Build from the same ARR view used in board packs — if the Magic Number uses a different ARR definition than the rest of your reporting, the metric is unexplainable.
2. Reconcile S&M to the GL — the sum of quarterly S&M in your SQL view should tie to the audited income statement. If it doesn't, find the variance before the board meeting.
3. Tag one-offs separately — a $2M event sponsorship or a one-time agency engagement should be flagged. Exclude them from the standard Magic Number and report a "normalized" and "reported" version side by side.
4. Version the lag assumption in Git — if you switch from a one-quarter to a two-quarter lag, the change should be a committed code change with a business justification, not a quiet spreadsheet edit.

Summary

The SaaS Magic Number is a powerful efficiency metric, but it is only as good as the data underneath it. Model quarterly ARR and S&M spend in separate SQL views, join them with a clear lag assumption, and reconcile both to their source systems. When the numbers are clean, the Magic Number tells a story investors understand in seconds — and auditors trust in hours.