Net annual recurring revenue

Overview

Net annual recurring revenue (Net ARR) represents the annualized value of monthly recurring revenue (MRR), calculated using a rolling average of realized revenue from posted journal entries. In Zenskar, this metric is available out of the box in the Invoices tab of the Billing and Collections dashboard as a chart created using the Visual Builder.

What Net ARR shows

Net ARR is used to track the growth of recurring revenue over time. It helps you answer questions like:

• Are we growing consistently month over month?
• Are expansions and new bookings contributing to sustained ARR growth?
• When did churn or contraction reduce our ARR?

This metric is especially useful for subscription or usage-based businesses that rely on predictable, recurring revenue streams.

How Net ARR is calculated

In Zenskar, Net ARR is computed as:

Net ARR = 12 * rolling average monthly revenue

This rolling average is calculated per customer and per product, smoothed over three months. The SQL shown below reflects this logic, and the chart groups values by the start of each month.

Data source and logic

The Net ARR chart uses the following dimensions and transformations:

1. Posting date (monthly group)

Revenue is grouped by:

toStartOfMonth(posting_date)

2. Monthly revenue per product per customer

Derived from journal_lines and related tables:

• Account filter: Only entries from the INCOME account category.
• Status filter: Only posted journal entries with no invoice and no soft deletion.
• Credit-debit difference is used to compute raw revenue.

3. Rolling average over three months

This smooths out one-off fluctuations by calculating a 3-month trailing average revenue for each combination of:

• customer_id
• revenue_contract_item_id
• product_name

4. Revenue movement logic

The logic includes detection of:

• New revenue from new customers
• Expansion revenue (positive deltas for existing customers)
• Contraction revenue (negative deltas for existing customers)

However, the final Net ARR calculation ignores this breakdown and simply uses the total rolling_revenue.

SQL behind Net ARR chart

This chart is built using Metabase Visual Builder, which generates the following SQL (formatted for clarity):

SELECT
  toStartOfMonth(source.posting_date) AS posting_date,
  12 * SUM(source.rolling_revenue) AS Net_ARR
FROM (
  -- Revenue entries per journal line
  WITH revenue_entries AS (
    SELECT
      posted_at AS posting_date,
      jl.customer_id,
      jl.revenue_contract_item_id,
      rci.name AS product_name,
      SUM(jl.credits - jl.debits) / 100 AS revenue
    FROM journal_lines jl
    JOIN accounts_revamped acr ON jl.account_id = acr.id
    JOIN journal_entries je ON je.id = jl.journal_entry_id
    JOIN revenue_contract_items rci ON jl.revenue_contract_item_id = rci.id
    WHERE
      acr.account_category = 'INCOME' AND
      jl.invoice_id IS NULL AND
      je.status_type = 'posted' AND
      je.deleted_at IS NULL
    GROUP BY posted_at, jl.customer_id, jl.revenue_contract_item_id, rci.name
  ),

  -- Compute rolling average revenue
  monthly_revenue AS (
    SELECT *,
      AVG(revenue) OVER (
        PARTITION BY customer_id, revenue_contract_item_id, product_name
        ORDER BY posting_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
      ) AS rolling_revenue
    FROM revenue_entries
  ),

  -- Detect previous revenue and new customers
  revenue_movement AS (
    SELECT mrr.*,
      ANY(rolling_revenue) OVER (
        PARTITION BY customer_id, revenue_contract_item_id, product_name
        ORDER BY posting_date
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
      ) AS prev_rolling_revenue,
      CASE
        WHEN customer_info.customer_id IS NOT NULL THEN 1 ELSE 0
      END AS is_new_customer
    FROM monthly_revenue mrr
    LEFT JOIN (
      SELECT MIN(posting_date) AS start_date, customer_id
      FROM monthly_revenue
      GROUP BY customer_id
    ) customer_info
    ON mrr.posting_date = customer_info.start_date AND mrr.customer_id = customer_info.customer_id
  ),

  -- Classify new, expansion, and contraction revenue (not used in chart)
  revenue_breakdown AS (
    SELECT *,
      CASE WHEN is_new_customer = 1 THEN rolling_revenue ELSE 0 END AS new_revenue,
      CASE WHEN is_new_customer != 1 AND prev_rolling_revenue < rolling_revenue THEN rolling_revenue - prev_rolling_revenue ELSE 0 END AS expansion_revenue,
      CASE WHEN is_new_customer != 1 AND prev_rolling_revenue > rolling_revenue THEN rolling_revenue - prev_rolling_revenue ELSE 0 END AS contraction_revenue
    FROM revenue_movement
  )

  SELECT * FROM revenue_breakdown
) AS source
GROUP BY toStartOfMonth(source.posting_date)
ORDER BY toStartOfMonth(source.posting_date) ASC

Example values

These values represent the output of the chart based on example data:

These figures are based on example data provided for illustration. Your actual chart will reflect live journal entries and contract data in your environment.

Summary

• Metric name: Net ARR
• Chart type: Line chart grouped by posting_date (monthly)
• Data source: Posted revenue entries from journal_lines, accounts_revamped, and revenue_contract_items
• Computation: Net ARR = 12 × SUM(rolling_revenue)
• Builder: Metabase Visual Builder with custom SQL

Let me know if you'd like me to package this into a downloadable format or link it to related metrics like DSO or AR aging.