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.

Field	Data type	Description
Day	DATE	The specific calendar date. Useful for analyzing daily ARR trends.
Week	DATE	The specific week (set to the Monday of that week). Helps in aggregating ARR data by week for easier comparison of WoW trends.
Month	DATE	The 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 beginning	BOOLEAN	Indicates 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 ending	BOOLEAN	Indicates 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 beginning	BOOLEAN	Indicates 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 ending	BOOLEAN	Indicates 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 period	VARCHAR(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 day	NUMBER(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 day	NUMBER(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 name	VARCHAR(256)	The name of the customer account as it appears in Stripe. Allows for customer-specific analysis and reporting on individual ARR contributions.
Customer ID	VARCHAR(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.
Currency	VARCHAR(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 ARR	NUMBER(38,12)	The ARR generated from a new customer within a given period.
Expansion ARR	NUMBER(38,12)	The ARR generated from an existing customer who upgraded or expanded their usage within a given period.
Contraction ARR	NUMBER(38,12)	The ARR lost from a customer who reduced their usage or downgraded within a given period.
Churn ARR	NUMBER(38,12)	The ARR lost due to a customer canceling their subscription within a given period.
Restart ARR	NUMBER(38,12)	The ARR from a customer who previously churned but who has returned within the restart cut-off window.
Net New ARR	NUMBER(38,12)	The net change in ARR for the period (Gross New + Expansion - Contraction - Churn + Restart) associated with a given customer.
Total ARR	NUMBER(38,12)	The cumulative ARR attributable to a given customer, up to and including a given period.
Gross New customers	NUMBER(1,0)	Flags whether a given customer is considered a new customer for a given period.
Expansion customers	NUMBER(1,0)	Flags whether a given customer is considered an expansion for a given period.
Contraction customers	NUMBER(1,0)	Flags whether a given customer is considered a contraction for a given period.
Churn customers	NUMBER(1,0)	Flags whether a given customer is considered a churn for a given period.
Restart customers	NUMBER(1,0)	Flags whether a given customer is considered a restart for a given period.
Net new customers	NUMBER(1,0)	Flags whether a given customer is considered a net new, gross new, churn, or restart customer for a given period.
Total customers	NUMBER(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

Getting Started

Connect and transform data

Explore data

Analyze data

Report on data

ARR Build Daily by Customer

How to use it

Example: Identify customers that contribute to each component of ARR

Gross New Customers

Expansion Customers

Contraction Customers

Churn Customers

Restart Customers

Example: Building ARR pacing summaries

​How to use it

​Example: Identify customers that contribute to each component of ARR

​Gross New Customers

​Expansion Customers

​Contraction Customers

​Churn Customers

​Restart Customers

​Example: Building ARR pacing summaries

How to use it

Example: Identify customers that contribute to each component of ARR

Gross New Customers

Expansion Customers

Contraction Customers

Churn Customers

Restart Customers

Example: Building ARR pacing summaries