# 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.](#custom-revenue-models)

### Airbyte Stripe Connector

Use this method if you're already using Airbyte for data integration. We officially support the [Airbyte Stripe source connector](https://docs.airbyte.com/integrations/sources/stripe) 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](https://stripe.com/docs/payment-links)
* The [sync mode](https://docs.airbyte.com/understanding-airbyte/connections#sync-modes) 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](https://fivetran.com/docs/connectors/applications/stripe) 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](https://stripe.com/docs/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](https://fivetran.com/docs/transformations/data-models/stripe-data-model/stripe-transform-model?utm_source=erd)

### 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

<details>

<summary><strong>DDL Example</strong></summary>

```sql
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)
);
```

</details>

**`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

<details>

<summary><strong>DDL Example</strong></summary>

```sql
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)
);
```

</details>

#### 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