Revenue

Modeling your company's revenue

Overview

Revenue data represents the financial transactions and subscription metrics that track your business's financial performance. This data is essential for understanding customer lifetime value, monthly recurring revenue (MRR), annual recurring revenue (ARR), churn analysis, and overall financial health.

Roadway AI's revenue analytics supports ingesting Stripe data from Airbyte, Fivetran, and Custom. This guide outlines the required tables and setup for integrating Stripe revenue data with our revenue analytics models.

Data Source Options

Finer details about each integration option can be found below. As a note, for customers who have existing definitions (and modeling) for revenue metrics (such as MRR, New Customer ARR, etc.), we recommend using the Custom Revenue Models option.

Airbyte Stripe Connector

Use this method if you're already using Airbyte for data integration. We officially support the Airbyte Stripe source connector for extracting Stripe payment and subscription data into your data warehouse to then be consumed in Roadway.

Airbyte Stripe Streams

To use Roadway AI's revenue analytics with Airbyte, you must configure your Airbyte Stripe connector to sync the following tables to your data warehouse:

Table Name	Description	Required
stripe_customers	Customer information and metadata	Required
stripe_subscriptions	Subscription lifecycle and billing details	Required
stripe_subscription_items	Individual items within subscriptions	Required
stripe_charges	Payment transactions and charge details	Required
stripe_invoices	Invoice records and billing information	Required
stripe_invoice_line_items	Detailed line items for each invoice	Required
stripe_products	Product catalog and definitions	Required
stripe_prices	Pricing information for products	Required
stripe_plans	Legacy subscription plans (if using older Stripe setups)	Required
stripe_balance_transactions	Financial transaction records	Required
stripe_payment_intents	Payment intent tracking	Optional
stripe_refunds	Refund transaction details	Optional
stripe_coupons	Discount and coupon information	Required
stripe_checkout_sessions	Checkout session data	Conditional Required (see below)
stripe_events	Stripe webhook events	Required

Key Notes:

• ⚠️ stripe_checkout_sessions is conditionally required if you bill via Stripe Payment Links

• The sync mode for these tables should include a deduplication method. We recommend Incremental - Append + Deduped

• Airbyte allows for table prefixing. We recommend prefixing with stripe_

Fivetran Stripe Connector

Use this method if you're using Fivetran as your data integration platform. We also support the Fivetran Stripe source connector for extracting Stripe payment and subscription data into your data warehouse to then be consumed in Roadway.

Fivetran Stripe Tables

To use Roadway AI's revenue analytics with Fivetran, you must configure your Fivetran Stripe connector to sync the following tables to your data warehouse:

Table Name	Description	Required
customer	Customer information and metadata	Required
subscription	Subscription lifecycle and billing details	Required
subscription_item	Individual items within subscriptions	Required
charge	Payment transactions and charge details	Required
invoice	Invoice records and billing information	Required
invoice_line_item	Detailed line items for each invoice	Required
product	Product catalog and definitions	Required
price	Pricing information for products	Required
plan	Legacy subscription plans (if using older Stripe setups)	Required
balance_transaction	Financial transaction records	Required
payment_intent	Payment intent tracking	Optional
refund	Refund transaction details	Optional
coupon	Discount and coupon information	Required
checkout_session	Checkout session data	Conditionally Required (see below)
event	Stripe webhook events	Required

Key Notes:

• ⚠️ checkout_session is conditionally required if you bill via Stripe Payment Links

• Fivetran automatically handles incremental syncing and deduplication

• Tables are synced using Fivetran's naming convention (singular form without stripe_ prefix)

• We can also support Fivetran's Stripe Data Model

Custom Revenue Models

Use this method for maximum flexibility or when integrating with existing data infrastructure. This custom connector option allows you to implement your own Stripe data extraction and define your own revenue metrics while maintaining compatibility with our revenue analytics models.

Custom Revenue Analytics Tables

dim__customers

Customer dimension table containing customer metadata and segmentation information.

Column Name	Data Type	Description	Required
customer_id	varchar	Unique customer identifier (primary key)	Required
user_id	varchar	Internal user identifier	Required
first_payment_date	date	Date of customer's first successful payment	Required
created_date	date	Customer account creation date	Required
currency	varchar	Customer's primary currency	Required
<optional_custom_dimension_1>	varchar	Custom customer segmentation field	Optional
...	...	...	...

Primary Key: customer_id

Table Constraints:

• customer_id must be unique across all records

• Supports custom customer dimension columns for business-specific segmentation

• Enables flexible customer analysis and cohort tracking

DDL Example

create table dim__customers (
    customer_id varchar(255) primary key,
    user_id varchar(255),
    first_payment_date date not null,
    created_date date not null,
    currency varchar(3) not null,
    custom_dimension_1 varchar(255),
    custom_dimension_2 varchar(255),
    custom_dimension_3 varchar(255)
);

fact__customer_arr_snapshot

Daily snapshot of customer Annual Recurring Revenue (ARR) and subscription metrics.

Column Name	Data Type	Description	Required
customer_id	varchar	Customer identifier	✅
subscription_id	varchar	Subscription identifier	✅
date_day	date	Date of the snapshot	✅
implied_arr	float	Annualized recurring revenue based on current subscription	✅
paid_arr	float	ARR based on actual payments received	✅
new_arr	float	New ARR from new subscriptions on this date	✅
churn_arr	float	ARR lost from canceled subscriptions	✅
expansion_arr	float	ARR gained from subscription upgrades/expansions	✅
contraction_arr	float	ARR lost from subscription downgrades/contractions	✅
reactivation_arr	float	ARR gained from reactivated subscriptions	✅
num_seats	integer	Number of seats/licenses in subscription	⚠️
plan_type	varchar	Subscription plan identifier	✅
billing_period	varchar	Billing frequency (monthly, annual, etc.)	✅
subscription_status	varchar	Current subscription status	✅

Primary Key: (customer_id, subscription_id, date_day)

Table Constraints:

• Must contain continuous daily records from first payment date to current date (or churn date)

• ARR calculations are automatically computed based on subscription pricing and billing cycles

• Enables comprehensive revenue trend analysis and customer lifecycle tracking

• Supports multi-subscription customers with separate records per subscription

DDL Example

create table fact__customer_arr_snapshot (
    customer_id varchar(255) not null,
    subscription_id varchar(255) not null,
    date_day date not null,
    implied_arr float not null,
    paid_arr float not null,
    new_arr float not null,
    churn_arr float not null,
    expansion_arr float not null,
    contraction_arr float not null,
    reactivation_arr float not null,
    num_seats integer,
    plan_type varchar(255) not null,
    billing_period varchar(50) not null,
    subscription_status varchar(50) not null,
    primary key (customer_id, subscription_id, date_day),
    foreign key (customer_id) references dim__customers(customer_id)
);

Data Validation

• Unique customer_id: Each customer should appear only once in the customers dimension table

• Non-null customer_id: Every customer record must have a unique identifier

• Non-null subscription_id: Every ARR snapshot record must have a subscription identifier

• Non-null date_day: Every ARR snapshot record must have a date

• Valid timestamps: All date fields should be valid dates

• Non-negative ARR values: All ARR metrics should be >= 0

• Valid currency codes: Customer currency should be valid 3-character ISO codes

• Continuous date series: ARR snapshots should contain continuous daily records from first payment to current date (or churn date)

Data Integrity Expectations

• Customer consistency: customer_id values should be consistent across all tables

• Subscription lifecycle: ARR snapshots should accurately reflect subscription state changes

• Revenue calculations: ARR calculations should be based on actual subscription pricing and billing cycles

• Multi-subscription support: Customers with multiple subscriptions should have separate ARR records per subscription

• Data freshness: Revenue data should be updated regularly to reflect current subscription states