Skip to main content
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.
FieldData typeDescription
DayDATEThe specific calendar date. Useful for analyzing daily ARR trends.
WeekDATEThe specific week (set to the Monday of that week). Helps in aggregating ARR data by week for easier comparison of WoW trends.
MonthDATEThe specific month (set to the first day of that month). Allows for monthly trend analysis, a common timeframe for ARR reporting, and goal setting.
Week beginningBOOLEANIndicates whether a given date is the first day of a week (TRUE or FALSE). Useful for setting up reports or visualizations that depend on weekly summaries.
Week endingBOOLEANIndicates whether a given date is the end of a week (TRUE or FALSE). Allows for easy weekly data snapshots, making it easier to pull consistent weekly performance data.
Month beginningBOOLEANIndicates whether a given date is the beginning of a month (TRUE or FALSE). Provides a monthly “start” data point for monthly summaries and snapshots.
Month endingBOOLEANIndicates whether a given date is the end of a month (TRUE or FALSE). Provides a monthly “end” data point for monthly summaries and snapshots.
Fiscal periodVARCHAR(16777216)Specifies the fiscal period (as denoted by the first month in the quarter, or “Jan”, “Apr”, “Jul”, and “Oct”). Essential for aligning ARR reporting with fiscal quarters for financial reporting and budget analysis.
Month dayNUMBER(10,0)Indicates the day number within a month (1-31). Useful for tracking and analyzing mid-month trends, seasonality, and other patterns based on day-in-month positions.
Fiscal period dayNUMBER(10,0)The day number within a fiscal period. Allows for detailed trend analysis within a fiscal quarter and for making time-based projections on a quarterly basis.
Customer nameVARCHAR(256)The name of the customer account as it appears in Stripe. Allows for customer-specific analysis and reporting on individual ARR contributions.
Customer IDVARCHAR(256)A unique identifier for each customer as it appears in Stripe. Important for accurate aggregation and deduplication of customer records. Also enables accurate joins across tables.
CurrencyVARCHAR(256)The currency associated with each invoice value. If you need help creating multi-currency ARR reporting, reach out to us using the messenger in the bottom-right corner of your workspace Home.
Gross New ARRNUMBER(38,12)The ARR generated from a new customer within a given period.
Expansion ARRNUMBER(38,12)The ARR generated from an existing customer who upgraded or expanded their usage within a given period.
Contraction ARRNUMBER(38,12)The ARR lost from a customer who reduced their usage or downgraded within a given period.
Churn ARRNUMBER(38,12)The ARR lost due to a customer canceling their subscription within a given period.
Restart ARRNUMBER(38,12)The ARR from a customer who previously churned but who has returned within the restart cut-off window.
Net New ARRNUMBER(38,12)The net change in ARR for the period (Gross New + Expansion - Contraction - Churn + Restart) associated with a given customer.
Total ARRNUMBER(38,12)The cumulative ARR attributable to a given customer, up to and including a given period.
Gross New customersNUMBER(1,0)Flags whether a given customer is considered a new customer for a given period.
Expansion customersNUMBER(1,0)Flags whether a given customer is considered an expansion for a given period.
Contraction customersNUMBER(1,0)Flags whether a given customer is considered a contraction for a given period.
Churn customersNUMBER(1,0)Flags whether a given customer is considered a churn for a given period.
Restart customersNUMBER(1,0)Flags whether a given customer is considered a restart for a given period.
Net new customersNUMBER(1,0)Flags whether a given customer is considered a net new, gross new, churn, or restart customer for a given period.
Total customersNUMBER(1,0)Flags the ongoing active status of a given customer, allowing for historical tracking of customer activity on any given day.

​
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
Using your ARR Build views ARR Build Invoice Items