Brainforge Knowledge

hedra-arr-findings-2026-02

10 min read

ARR Reporting: Methodology, Findings, and Verified Figures

Prepared by: Brainforge (Uttam Kumaran, Shriram Apte, Awaish Kumar) Prepared for: Sandra Nachförg-Buleandra, Michael (CEO), Hedra Date: 2026-02-18 Status: Delivered — models live in DEV_MARTS, pending handoff to Hex

Executive Summary

Hedra’s ARR reporting was overstated. The existing model showed approximately 7–8M based on roughly $600K in monthly cash collections. Brainforge investigated the discrepancy, identified the root causes, and rebuilt the ARR models from scratch based on Shri’s specification.

The new models produce figures that align with expectations. Current ARR as of January 2026 is 9.88M in June 2025. The decline is real and is primarily driven by churn and downsell outpacing new ARR. A significant portion of that churn is tied to failed Stripe payments — customers are lapsing because cards are not being charged successfully, not necessarily because they intend to cancel.

The three new tables are live in Snowflake (DEV_MARTS) and available in Hex. This document covers what was wrong, what we built, the decisions we made, and what Sandra needs to know to validate and extend the work.

The Problem: What Was Wrong and How We Found It

Starting point

When Sandra shared Hedra’s existing Hex dashboard, ARR was showing approximately 600K per month, which would imply ARR closer to $7–8M.

Brainforge ran verification queries directly against Snowflake (PROD_MARTS) on 2026-01-26 and confirmed the existing table was producing $11.25M. Hex and Snowflake agreed — the issue was in the underlying model, not a display error.

Root cause 1: Zero-amount invoices inflating ARR

The old model calculated ARR using a formula similar to (total paid invoice amount / paid invoice count) × 12. Stripe had 31.5 million zero-amount paid invoices out of 31.9 million total paid invoices in 2025. These zero-amount records were created by Stripe automatically for free-plan customers. Including them in the count suppressed the per-invoice average dramatically, and multiplying by 12 then produced a distorted annual figure.

Root cause 2: Annual plans double-annualized

For customers on annual plans, the old model was in effect multiplying the invoice amount by 12 again after Stripe had already issued the invoice at the full annual amount. This inflated ARR for annual subscribers.

Root cause 3: One-time invoices treated as recurring

Approximately 75,000 invoices in the dataset had no billing frequency (no billing_interval field). These included credit pack purchases and API credit purchases. The old model took their face value and included it in MRR as though it were a recurring monthly charge. A 1,000 of MRR.

Root cause 4: Credit filter too narrow

The existing filter excluded invoices where the product name contained “credit pack,” but it missed variants like “API Credits,” “API credits,” and similar labels. Approximately 400–500 additional invoices were leaking through the filter into ARR.

Root cause 5: No reactivation detection

The old model had no concept of customers who churned and then returned. Any returning customer was tagged as New ARR rather than Reactivation ARR, overstating organic new customer acquisition.

What We Built

Brainforge built three dbt models based on Shri’s specification. All three are materialized as tables in DEV_MARTS.REVENUE.

Table 1: fct_hedra__customer_monthly_mrr

One row per customer per month. This is the foundation for all ARR reporting.

  • MRR for each customer in each month, normalized to a monthly amount regardless of billing interval.
  • ARR = MRR × 12 per customer per month.
  • Covers only completed months (the current partial month is excluded).
  • Includes a customer-month spine: every customer who ever had MRR gets a row for every month in the range, with MRR = 0 for inactive months. This is what makes reactivation detection accurate.

Table 2: fct_hedra__arr_waterfall_monthly

One row per (period, customer, waterfall bucket). Supports the ARR waterfall chart.

Waterfall buckets:

  • Beginning ARR — total ARR at the start of the period (prior month ending), shown as a single aggregate row
  • New ARR — customers with ARR in this period and no ARR in any prior period
  • Reactivation ARR — customers with ARR in this period, no ARR in the immediately prior month, but ARR in some earlier month
  • Monthly Tier Upsell — customers whose MRR increased and moved to a new monthly (non-enterprise) plan tier
  • Enterprise Upsell — customers whose MRR increased and moved to an enterprise tier
  • Downsell — customers whose MRR decreased but is still greater than zero
  • Churn — customers with ARR in the prior month and zero ARR in this period; shown as a negative value

The sum of all buckets equals ending ARR.

Table 3: fct_hedra__arr_qa_reconciliation

One row per period. This is the QA and reporting table.

Columns: period_month, beginning_arr, new_arr, reactivation_arr, enterprise_upsell, monthly_tier_upsell, churn, downsell, total_arr, bucket_sum, bucket_gap, check_amount.

  • total_arr is the authoritative ending ARR figure for each month (direct sum from the customer monthly MRR table).
  • check_amount is beginning_arr (next period) - total_arr (current period). It should be 0 for all completed months. If it is not zero, beginning and ending ARR are not reconciling.
  • bucket_gap is total_arr - bucket_sum. Small non-zero values reflect rounding from the daily MRR allocation process and do not affect total_arr.

Decisions Made (and How to Override Them)

These are choices Brainforge made based on conversations with Sandra. Michael should review these and flag any he wants changed.

DecisionWhat we choseWhyHow to change it
Credit packsExcluded from ARRSandra confirmed these are one-time purchases, not subscriptionsRemove the billing_interval is null exclusion in fct_stripe__invoice_lines
Credit filter scopeExcludes any product name containing “credits” or “credit pack”Catches API Credits and similar variants discovered in QAAdjust the product name filter in fct_stripe__invoice_lines
ARR formulaMRR × 12Normalizes all billing intervals to monthly first, then annualizes; prevents double-annualization for annual plansChange to per-invoice ARR (requires rewriting fct_stripe__invoice_lines)
ProrationExcluded from MRRProrations are adjustments to existing charges, not new recurring revenueRemove the is_proration = true exclusion
Completed months onlyCurrent partial month excludedAvoids partial-month MRR distorting trendsRemove the < date_trunc('month', current_date()) filter
Invoice statusAll statuses included in daily MRRCaptures full billed ARR picture; failed payment analysis is additiveAdd where invoice_status = 'paid' in fct_hedra__daily_mrr_by_customer_product for a collections-only view

Open Items and Known Caveats

Reactivation ARR is now populated. The customer-month spine (and completed-months-only filter) ensures that prior period is the true calendar prior month, so customers who churned and then returned are correctly tagged as Reactivation ARR. The reconciliation table shows non-zero reactivation in recent months (e.g. roughly 58K in Nov–Dec 2025, $20K in Jan 2026).

Bucket gap is non-zero in some months. The gap between the sum of waterfall buckets and total_arr (e.g. ~$146K in Dec 2025) is a known artifact of daily MRR allocation and rounding. It does not affect total_arr, which is the direct sum from the customer monthly MRR table and remains the authoritative figure. No fix is required; the gap is expected.

Invoice status filter is not yet applied to the waterfall. The current models include invoices of all payment statuses (paid, open, uncollectible). This means ARR includes amounts Hedra has billed but not yet collected. A paid-only view will show a lower and more conservative ARR figure. We recommend building this view next.

Some no-frequency invoices may still need classification. During today’s QA, “ProWeb Monthly” appeared as an invoice without a billing_interval field, which would currently exclude it from MRR. This may be a Stripe data quality issue on Hedra’s side. We flagged it and are investigating.

How to Validate (Sandra’s Guide)

All three tables are in DEV_MARTS.REVENUE in Snowflake, available in Hex.

Start here — one row per month:

select period_month, beginning_arr, new_arr, reactivation_arr,
       enterprise_upsell, monthly_tier_upsell, churn, downsell,
       total_arr, check_amount
from DEV_MARTS.REVENUE.fct_hedra__arr_qa_reconciliation
order by period_month;

check_amount should be 0 for all completed months. Any non-zero value means beginning and ending ARR are not reconciling and should be investigated.

Drill into a specific month:

select customer_id, customer_name, waterfall_bucket, arr_amount
from DEV_MARTS.REVENUE.fct_hedra__arr_waterfall_monthly
where period_month = '2026-01-01'
  and waterfall_bucket != 'Beginning ARR'
order by waterfall_bucket, arr_amount desc;

Drill into a specific customer:

select period_month, mrr, arr, pack_tier, prior_period_mrr,
       has_mrr_prior_period, has_mrr_any_prior_period
from DEV_MARTS.REVENUE.fct_hedra__customer_monthly_mrr
where customer_id = '<customer_id>'
order by period_month;

Quick ARR number for any month: total_arr in fct_hedra__arr_qa_reconciliation is the authoritative figure. The check_amount = 0 property means you can trust that beginning ARR for month N+1 equals total_arr for month N.

Six-Month ARR Summary (August 2025 – January 2026)

From fct_hedra__arr_qa_reconciliation, run 2026-02-18:

MonthBeginning ARRNew ARREnterprise UpsellMonthly Tier UpsellChurnDownsellEnding ARR
Aug 2025$9,430,934$1,546,203$10,725$925,282-$1,822,581-$1,515,403$8,668,041
Sep 2025$8,668,041$705,937$42,215$766,637-$1,380,157-$1,282,610$7,609,864
Oct 2025$7,609,864$481,606$24,452$543,218-$1,452,960-$953,721$6,522,232
Nov 2025$6,522,232$496,919$64,152$440,207-$914,030-$884,084$5,835,965
Dec 2025$5,835,965$705,446$3,407$491,216-$738,258-$729,504$5,771,999
Jan 2026$5,771,999$494,655$86$360,296-$1,048,943-$694,678$4,932,411

Key observations:

  • ARR peaked at approximately $9.88M in June 2025 and has declined steadily since.
  • Churn has consistently exceeded new ARR in every month shown.
  • The gap between billed ARR and collected ARR (failed payments) is not yet visible in this table — that analysis is in progress.

1. Understand the failed payment rate. Shri’s analysis of the Stripe data indicates a high and growing share of invoices are failing. This is likely a material driver of the ARR decline. The immediate next step is a stacked bar chart of Stripe invoice amounts by payment status (paid, failed, uncollectible) by month, starting January 2025. This will show whether the ARR decline is driven by true cancellations or by payment infrastructure issues.

2. Separate billed ARR from collected ARR. Once the invoice status filter is applied, we can produce two waterfall views: one including all invoiced ARR and one including only paid/collected ARR. The delta between the two is the “expected but not collected” gap and likely represents a significant revenue recovery opportunity.

3. Diagnose the churn profile. The product analytics question: what behaviors do churning customers (both paid and unpaid) exhibit before they lapse? What is their time to first video created, credit consumption rate, and last active date relative to churn date? This analysis can identify whether churn is driven by product engagement gaps or payment issues, and informs both product roadmap and billing dunning strategy.

Appendix: MRR Calculation Detail

For recurring invoices (with billing_interval and billing_interval_count), MRR is the invoice net amount divided by the number of months in the billing period. The divisor is computed once (no double application of billing_interval_count):

MRR = (invoice net amount) / divisor

where divisor (months in the billing period) is:
  billing_interval = 'day'   →  billing_interval_count × 30.0
  billing_interval = 'week'   →  billing_interval_count × 4.33
  billing_interval = 'month'  →  billing_interval_count × 1.0
  billing_interval = 'year'   →  billing_interval_count × 12.0

Example: monthly plan with billing_interval_count = 3 (quarterly) gives divisor 3, so MRR = amount ÷ 3. Annual plan with count 1 gives divisor 12, so MRR = amount ÷ 12.

ARR at the customer-month level = MRR × 12.

Excluded from MRR:

  • Proration lines (is_proration = true)
  • Lines without billing_interval or billing_interval_count (treated as one-time)
  • Invoices where product name contains “credits” or “credit pack”
  • Invoices without a subscription_id where product name matches credit patterns

Graph View