TD: Finance Annual Recurring Revenue (ARR)

---

The goal of this page:

• Help you understand how to navigate through the Snapshot ARR Dashboards.
• Help you understand the data models used to create the Snapshot ARR dashboards.
• Help you undersand the manual true up additions to ARR calculations
• And overall help everyone contribute!

Snapshot ARR

ARR in the Zuora data changes on a daily basis as subscriptions are renewed and amended. Therefore, it is necessary to create a snapshot of the data each day so we can have a historical record of ARR in the data warehouse to use for reporting and analysis. The three methodologies we use to Snapshot and report ARR are described below.

Method One:

We use dbt snapshots which implement type-2 Slowly Changing Dimensions over mutable source tables. These Slowly Changing Dimensions (or SCDs) identify how a row in a table changes over time. In Method One, we snapshot the mart_arr table which is the mart used to generate our live ARR metrics. This method produces an exact replica of mart_arr each day which is then modeled using date spine techniques. In this method, we do not use the snapshotted raw data and instead snapshot the mart table. We found this has benefits of producing a simple model of the snapshotted mart_arr table that always stays up to date with the columns in the mart_arr without having to do full refreshes. This is a great fit for the use case of needing an exact three ring binder copy of mart_arr that keeps up with the columns and does not need to be fully refreshed. Even if the model is fully refreshed, it would still return the same results all of the time.

Method Two:

We use the Snowflake Clone feature to create a zero-copy clone of mart_arr. Method Two satisfies the same use cases as Method One where an exact three ring binder copy of mart_arr is needed each day. This method serves as a backup to Method One by using a methodology that is independent of dbt. These cloned tables will not be the primary tables queried or used for reporting, but will provide redundancy and a fail safe to the dbt snapshots in case there is a performance issue with them on a particular day.

Method Three:

Method Three uses the more traditional bottoms-up snapshotting approach that is detailed in this dbt blog post. We use dbt snapshots which implement type-2 Slowly Changing Dimensions over mutable source tables to snapshot the raw source data. We then use date spinning modeling techniques to build a snapshotted mart_arr table from the bottom-up. This model uses a Snapshotted ARR Fact and live CRM Account and Product Details dimensions. This allows us to answer questions about how ARR looks with the current state of the Sales CRM Account Hierarchy and Product Details attributes such as Product Tier and Product Delivery. This method allows for full refreshes and while the total ARR amount on a given day will not change during a full refresh, the slicing of that ARR will change and update according to what the live Sales CRM Account Hierarchy and Product Details dimensions report.

Release Train Cadence:

1. Coming Soon

Maintenance Schedule:

1. As needed, Scheduled Maintenance will be performed every two weeks on Friday, from 9am to 11am EST.

True Up Subscriptions

Manual true ups come from our Revenue Accounting team through Zuora Revenue. We combined these true ups with our other charge records to adjust our overall ARR number.

Key Data Models

• zuora_revenue_revenue_contract_lines_source: Flags subscriptions with manual true ups using custom field (ATR16)
• zuora_revenue_manual_journal_entry_source: Source of true up amount based on flags in revenue contract lines
• prep_charge: Combines non-manual charges with manual true up charges to generate ARR models
• rpt_dropped_manual_true_up_charges: Compares snapshotted charges with current charges to alert if manual true ups are dropped

Monitoring Dropped True Ups

Flow:

1. Alert goes to emails assigned in alert (Revenue Accounting team & GTM Data team)
2. Data team creates a triage issue
3. Data team tags Revenue Accounting DRI, asking to update the dropped subscription(s) in the issue
4. Check after the next data warehouse refresh that the data has flowed in the ARR models and the alert is showing 0 dropped true ups

Monitoring dashboard can be found at Dropped True Up Subscriptions

Quick Links

Getting Started

To get started we want to make sure you understand:

• What KPIs/PIs are supported using this dashboard
• Key terms that will explain how we account for the metrics
• The data source behind the dashboard
• To explore further, you can create visual and analysis yourself in Sisense. A great way to start is using the Sisense Discovery tool. Want to get started in Sisense head here.
• To go even deeper, you can explore data in snowflake. The benefit of exploring in Snowflake is you can join to additional information (i.e. other data sources). Additional information on exploring in Snowflake can be found here.

Key Terms, Metrics, KPIs/PIs, and Key Field and Business Logic

Key Terms

Dimensions:

• Coming soon

Key Metrics, KPIs, and PIs

Facts:

• Coming Soon

Key Fields and Business Logic

Coming Soon

Understanding the Data Sources and Data Models

The ARR Dashboards and Data models use the data models as seen in the ARR ERD

Data Lineage

* Data is sourced from Salesforce.com and Zuora * The dbt solution generates a dimensional model from Mart Snapshot source data. The documentation and SQL for mart_arr_snapshot_model , and the complete data lineages can be found at dbt mart_arr_snapshot_model lineage chart * The dbt solution generates a dimensional model from RAW snapshot source data. The documentation and SQL for mart_arr_snapshot_bottom_up can be found here , and the complete data lineages can be found at dbt mart_arr_snapshot_bottom_up lineage chart

Example Queries

Coming Soon

Coming Soon

Additional Resources

Trusted Data Solution

ARR models use the arr, arr_snapshots, mrr, zuora, billing_account, and crm_account tags for Trusted Data tests and their results. This can be seen most easily using the Trusted Data Dashboard

See overview at Trusted Data Framework

dbt guide examples for details and examples on implementing further tests

EDM Enterprise Dimensional Model Validations

The [(WIP) Enterprise Dimensional Model Validation Dashboard](https://app.periscopedata.com/app/gitlab/760445/WIP:-Enterprise-Dimensional-Model-Validation-Dashboard) reports on latest Enterprise Dimensional model test and runs.

RAW Source Data Pipeline validations

[Data Pipeline Health Validations](https://app.periscopedata.com/app/gitlab/715938/Data-Pipeline-Health-Dashboard)

Data Security Classification

Coming Soon

ORANGE

• Description: Customer and Personal data at the row or record level.

• Objects:

YELLOW

• Description: Denomas Financial data, which includes aggregations or totals.

• Objects:

Solution Ownership

* Source System Owner: * Salesforce: `@jbrennan1` * Zuora: `@andrew_murray` * Source System Subject Matter Expert: * Salesforce: `@jbrennan1` * Zuora: `@andrew_murray` * Data Team Subject Matter Expert: `@paul_armstrong` `@jeanpeguero` `@jjstark` `@iweeks` `@michellecooper`