This view aggregates Stripe invoice data to display each customer's total ARR for each day they are active, along with their contributions to each component of ARR (Gross New, Expansion, Contraction, Churn, Restart, and Net New). Each row represents a customer's contributions to ARR on a given day.

This view also incorporates the impact of any invoice-level corrections or customer and domain-level exclusions made in your ARR Build.

How to use it

The ARR Build Daily by Customer view is the foundation of your ARR reporting, enabling you to build downstream reports like:

• Cohort analyses that measure net and gross retention rates
• ARR forecasts that project future ARR growth based on historical trends
• Pacing summaries that track progress against ARR goals

Example: Identify customers that contribute to each component of ARR

You can return a list of customers who contribute to each ARR component over a given period to build a deeper understanding of the drivers behind ARR growth and trends.

Below are some sample SQL queries to help you get started. The output of each will be a list of customers associated with each component of ARR for the specified month, which you can edit as needed.

Before running the queries, be sure to specify a month and date value.

Gross New Customers

select \*  
from ARR_BUILD_DAILY_BY_CUSTOMER  
where gross_new_arr \<> 0  
and month = date_trunc('month',current_date) -- current month  
-- and month = date_trunc('month',date '2024-01-01') -- specific month  
-- and month >= date '2024-10-01' -- more recent than a specific month

Expansion Customers

select \*  
from ARR_BUILD_DAILY_BY_CUSTOMER  
where expansion_arr \<> 0  
and month = date_trunc('month',current_date) -- current month  
-- and month = date_trunc('month',date '2024-01-01') -- specific Month  
-- and month >= date '2024-10-01' -- more recent than a specific month

Contraction Customers

select \*  
from ARR_BUILD_DAILY_BY_CUSTOMER  
where contraction_arr \<> 0  
and month = date_trunc('month',current_date) -- current month  
-- and month = date_trunc('month',date '2024-01-01') -- specific Month  
-- and month >= date '2024-10-01' -- more recent than a specific month

Churn Customers

select \*  
from ARR_BUILD_DAILY_BY_CUSTOMER  
where churn_arr \<> 0  
and month = date_trunc('month',current_date) -- current month  
-- and month = date_trunc('month',date '2024-01-01') -- specific Month  
-- and month >= date '2024-10-01' -- more recent than a specific month

Restart Customers

select \*  
from ARR_BUILD_DAILY_BY_CUSTOMER  
where restart_arr \<> 0  
and month = date_trunc('month',current_date) -- current month  
-- and month = date_trunc('month',date '2024-01-01') -- specific Month  
-- and month >= date '2024-10-01' -- more recent than a specific month

Example: Building ARR pacing summaries

Create pacing summaries to assess progress against monthly or quarterly ARR targets.

Before running the query, adjust the query to select the specific month, current_date, and reporting interval you are interested in.

-- Pull the monthly net_new total in terms of daily change  
with base as (  
  SELECT month  
    , month_day  
    , SUM(net_new_arr) AS net_new_arr  
  FROM arr_demo_daily_customer arr  
  where month >= date_trunc('month',current_date) - interval '6 months' -- in the last six months  
    and month \< date_trunc('month',current_date) -- but exclude this month's partial data  
  GROUP BY all  
  order by 1,2 asc  
),

-- aggregate those into a running sum and total month amount per month  
aggregate as (  
  select \*  
       , sum(net_new_arr) over (partition by month order by month_day asc) as running_sum  
       , sum(net_new_arr) over (partition by month) as month_total  
       , sum(net_new_arr) over (partition by month order by month_day asc) / sum(net_new_arr) over (partition by month) as percent_of_month_by_day  
  from base  
),

-- take the avg pacing per day for the last 6 historical months  
historical_pacing as (  
  select month_day  
       , avg(percent_of_month_by_day) as avg_percent_of_month_by_day  
  from aggregate  
  group by 1  
  order by 1 asc  
),

-- take the current month running total  
current_month_net_new as (  
  select distinct month  
       , month_day  
       , sum(net_new_arr) over (partition by month order by month_day asc) as net_new_arr  
  from ARR_BUILD_DAILY_BY_CUSTOMER  
  where month = date_trunc('month',current_date)  
)

-- calculate the end of month forecast based on current month attainment + historical pacing  
select a.month_day  
     , a.avg_percent_of_month_by_day  
     , b.net_new_arr as current_month_net_new_arr  
     , b.net_new_arr / a.avg_percent_of_month_by_day as end_of_month_forecast  
from historical_pacing a  
join current_month_net_new b on a.month_day = b.month_day  
order by 1 asc