This page documents the data sources available for reporting in Advanced Billing Business Intelligence. In the UI, these data sources are prefixed with chargify_bi_ to avoid naming collisions with third-party data.
As you begin exploring your data, it would be helpful to keep to following points in mind:
- The
keen.timestampis used for all time-related operations when analyzing data. In most cases, this field contains the timestamp for when the event or record was created. - We have standardized on storing money amounts in cents. You can use the settings tab to format query results containing money amounts.
- If your Advanced Billing site operates in multiple currencies, please be mindful to group or filter on
currencywhen analyzing fields containing money amounts. - Each field value has been marked as either static or up-to-date. Static data does not change; static fields contain values at the time of record creation. Up-to-date fields maintain current or live values.
Conversions
In Advanced Billing, a conversion occurs when a trialing or freemium subscription upgrades to a paying product. This data source provides the capability to analyze and segment your historic subscription conversions.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | static |
| address.country | The country portion of the payment-profile billing address. | static |
| address.state | The state portion of the payment-profile billing address. | static |
| address.zip | The zip portion of the payment-profile billing address. | static |
| customer.custom_fields | The customer’s custom fields. | up-to-date |
| customer.id | The ID of the customer. | static |
| customer.name | The customer’s name. | static |
| customer.organization | The customer’s company/organization name. | static |
| keen.timestamp | Timestamp indiciating when the conversion event occurred. | static |
| mrr | New MRR earned as a result of the conversion (new_business). | static |
| product.id | The ID of the subscriber’s product at the time of the event. | static |
| product.name | The name of the subscriber’s product at the time of the event. | static |
| subscription.activated_at | The timestamp for when the subscription was activated. | static |
| subscription.created_at | The timestamp for when the subscription was created. | static |
| subscription.custom_fields | The subscription’s current custom fields. | up-to-date |
| subscription.id | The ID of the subscription. | static |
| subscription.name | The subscription customer name. | static |
| subscription.renewal_interval | The subscriber’s renewal interval. Examples include ‘1_month’ and ‘10_days’. | static |
Dunning Retries
The Dunning data source provides you the capability to report on your complete dunning history. Each event in this data source represents an attempt to recover a subscription. Dunning retry events are categorized and enriched with Subscription data so that you can analyze your dunning history in ways interesting to you.
| Field | Description | Value |
| active_dunners_delta | This field is used to compute the total number of subscriber in dunning. | static |
| category | Categorizes the dunning retry event. Values in this field are one of: “entered_dunning”, “retry”, “recovered”, or “lost”. | static |
| currency | The currency code (ISO 4217 format) of the subscription. | static |
| customer.name | The name of the customer. | static |
| customer.organization | The customer’s company/organization name. | static |
| entered_dunning_at | The timestamp for when the subscription entered dunning. This timestamp is present on all dunning retry events. | static |
| keen.timestamp | The timestamp for when the dunning retry event occurred. | static |
| product.id | The ID of the subscription’s product at the time of the event. | static |
| resolution_duration | The amount of time spent attempting to recover the subscription. This value is expressed in seconds. | static |
| resolution_reason | The resolution reason. | static |
| revenue_at_risk_delta | The incremental amount of revenue at risk contributed by the subscription in context. Note that this value is in cents and can be positive or negative. | static |
| sales_rep.id | The ID of the subscriber’s assigned Sales Rep. | up-to-date |
| sales_rep.name | The name of the subscriber’s assigned Sales Rep. | up-to-date |
| subscription.activated_at | The timestamp for when the subscritpion was activated. | static |
| subscription.created_at | The timestamp for when the subscription was created. | static |
| subscription.custom_fields | The subscription’s current custom fields. | up-to-date |
| subscription.id | The ID of the subscription. | static |
Invoice Line Items
Metrics around your Invoice Line Items can be built using this data source. In addition to the attributes coming directly from invoices like Invoiced Amount, Due Date, and Line Items, you can also filter and categorize your data by Customer and Subscription Custom Fields and Sales Rep. You can also report on all the details around Unit Price and Tax Amounts.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | up-to-date |
| address.country | The country portion of the payment-profile billing address. | up-to-date |
| address.state | The state portion of the payment-profile billing address. | up-to-date |
| address.zip | The zip portion of the payment-profile billing address. | up-to-date |
| coupon_codes | A list of coupon codes applied to this invoice line item. | static |
| currency | The currency code (ISO 4217 format) associated with the invoice. | static |
| currency_symbol | The symbol associated with the currency. | static |
| customer.custom_fields | The customer’s current custom fields. | up-to-date |
| customer.id | The ID of the customer. | static |
| customer.name | The name of the customer. | up-to-date |
| customer.organization | The customer’s company/organization name. | up-to-date |
| discount_amount_in_cents | The total of any discounts for the line item. | static |
| invoice.due_date | The due date of the invoice. | static |
| invoice.issue_date | The date that the invoice was issued to the customer. | static |
| invoice.number | The number of the Invoice that this line item belongs to. | static |
| invoice.uid | The unique identifier of the Invoice that this line item belongs to. | static |
| item_description | The description for this line item. | static |
| item_id | The item’s unique id within Advanced Billing. | static |
| item_title | The line item’s name. | static |
| item_type | The line item’s associated prodcut catalog item type, for example, “product”, or “component”. | static |
| keen.timestamp | The timestamp for when the invoice was issued. | static |
| kind | The line item’s detailed kind, e.g. “quantity_based_component”. | static |
| net_terms | The invoice’s net terms. | static |
| payer.customer_id | The ID of the paying customer. | static |
| payer.name | The name of the paying customer. | static |
| period_range_end | The service period end date. | static |
| period_range_start | The service period start date. | static |
| price_point_id | The price point ID used to compute the total amount charged for the line item. | static |
| quantity | The billed quantity for the line item. | static |
| sales_rep.id | The ID of the subscriber’s assigned Sales Rep. | up-to-date |
| sales_rep.name | The name of the subscriber’s assigned Sales Rep. | up-to-date |
| subscription.custom_fields | The subscription’s current custom fields. | up-to-date |
| subscription.id | The ID of the subscription. | static |
| subtotal_amount_in_cents | The subtotal for the line item. | static |
| tax_amount_in_cents | The taxed amount (taxable - tax_exempt). | static |
| tax_exempt_amount_in_cents | The tax exempt amount. | static |
| taxable_amount_in_cents | The original taxable amount for the line item charge. | static |
| total_amount_in_cents | The total amount charged for the line item. | static |
| transaction_id | The unique transaction ID within Advanced Billing. | static |
| unit_price | The price per unit. | static |
Metered Usage
The Metered Usage data source will hold all of the data coming from your Metered Components. As soon as usage is uploaded to Advanced Billing, it will be available to report in via this data source by Subscription, Component details and Usage Quantities.
| Field | Description | Value |
| component.handle | The handle of the component. | static |
| component.id | The ID of the component for which the quantity was recorded. | static |
| component.name | The name of the component. | static |
| keen.timestamp | The timestamp for when the event occurred. | static |
| product.id | The ID of the product that the subscriber was subscribed to at the time of the event. | static |
| product.name | The name of the product. | static |
| subscription.id | The ID for the subscription that the recorded quantity belongs to. | static |
| subscription.name | The name of the subscription. | static |
| usage_quantity | The usage quantity recorded by this event. | static |
MRR Movements
In Advanced Billing, an MRR movement records an increase or decrease in a subscriber’s MRR value. The MRR movement amount is the net sum of all line-item expansion and contraction. This data source is also enriched with Subscription metadata such as status, sales rep, custom fields, and address.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | up-to-date |
| address.country | The country portion of the payment-profile billing address. | up-to-date |
| address.state | The state portion of the payment-profile billing address. | up-to-date |
| address.zip | The zip portion of the payment-profile billing address. | up-to-date |
| amount | The MRR growth amount (either positive or negative) at the time of the event. Please note that this value is stored in cents. | static |
| breakouts.plan.amount | The portion of the overall MRR movement amount that comes from recurring “plan” charges: Products, Qty-Based Components, and OnOff Components. | static |
| breakouts.plan.category | The MRR movement category pertaining to the plan amount of the MRR movement. | static |
| breakouts.usage.amount | The portion of the overall MRR movement amount that comes from usage-based charges: Metered and EventBased Components. | static |
| breakouts.usage.category | The MRR movement category pertaining to the usage amount of the MRR movement. | static |
| category | The type of MRR movement: new_business, expansion, contraction, reactivation, or churn. | static |
| churn_reason_code | The Churn Reason Code. | static |
| currency | The currency code (ISO 4217 format) for the MRR movement amount. Please note that MRR is stored in the Site’s configured currency. | static |
| customer.custom_fields | The customer’s current custom fields. | up-to-date |
| customer.email | The email address of the customer. | up-to-date |
| customer.id | The ID of the customer. | up-to-date |
| customer.organization | The customer’s company/organization name. | up-to-date |
| customer.reference | The unique identifier used within your own application for this customer. | up-to-date |
| customer.vat_number | The customer’s VAT number. | up-to-date |
| description | An English explanation of the MRR movement. For example, “Delores Quigley increased Widgets to 64 (+47).” | static |
| keen.timestamp | Timestamp indiciating when the MRR movement event occurred. | static |
| line_items.component_id | The Component ID for the line-item. For Product (or “baseline”) line items, this field will have a value of 0. | static |
| line_items.mrr | The total MRR amount contributing to the subscriber’s overall MRR value for the line item, at the time of the MRR event. | static |
| line_items.mrr_movements | The MRR movement amount(s) contributing to the overall amount for the given line item. |
static |
| line_items.name | The name of the line-item. | static |
| line_items.prev_quantity | The line-item quantity at the time of the subscriber’s previous MRR movement event. | static |
| line_items.product_id | The Product ID for the line-item. | static |
| line_items.quantity | The line-item quantity. | static |
| line_items.recurring | When true, the line item’s MRR value will contribute to the plan breakout. When false, the line item contributes to the usage breakout. | static |
| mrr | The subscriber’s MRR value at the time of the MRR event. | static |
| product.id | The ID of the subscriber’s product at the time of the event. | static |
| product.name | The name of the subscriber’s product at the time of the event. | static |
| sales_rep.id | The ID of the subscriber’s assigned Sales Rep | up-to-date |
| sales_rep.name | The name of the subscriber’s assigned Sales Rep | up-to-date |
| subscription.activated_at | The timestamp for when the subscription was activated. | static |
| subscription.created_at | The timestamp for when the subscription was created. | static |
| subscription.custom_fields | The subscriptions custom fields. | up-to-date |
| subscription.id | The ID of the subscription. | static |
| subscription.renewal_interval | The subscriber’s renewal interval at the time of the MRR event. For example, ‘1_month’, or ‘10_days’. | static |
| subscription.status | The current state of the subscription. Please see the documentation for Subscription States. | up-to-date |
SaaS Metrics
This data source contains monthly snapshots of key SaaS Metrics. These metrics cannot be filtered, segmented, or grouped within this data source. They are available as historical monthly calculations and up to today for the current month.
| Field | Description | Value |
| label | A description of the month represented, e.g. “June-2020”. | static |
| average_revenue_per_subscriber | Total MRR / Total Paid Subscriber Count | static |
| lifetime_value | Average Revenue Per Subscriber / Paid Subscriber Churn | static |
| mrr | Total MRR | static |
| arr | Total MRR * 12 | static |
| currency | The site’s configured currency | static |
| breakouts.plan.mrr | The portion of total MRR that comes from recurring “plan” charges: Products, Qty-Based Components, and OnOff Components. | static |
| breakouts.plan.arr | Plan MRR * 12 | static |
| breakouts.usage.mrr | The portion of total MRR that comes from usage-based charges: Metered and EventBased Components. | static |
| breakouts.usage.arr | Usage MRR * 12 | static |
| keen.timestamp | The timestamp for when the metrics were calculated. | static |
Subscriptions
The Subscriptions data source will allow you to create metrics based on all the data you manage on subscriptions, including attributes like Renewal Interval, Payment Collection Method and all of the data related to your customers like address data and Custom Fields. This data source also holds calculated Metrics for total MRR and ARR.
| Field | Description | Value |
| activated_at | The timestamp for when the subscription was activated. | static |
| address.city | The city portion of the payment-profile billing address. | up-to-date |
| address.country | The country portion of the payment-profile billing address. | up-to-date |
| address.state | The state portion of the payment-profile billing address. | up-to-date |
| address.zip | The zip portion of the payment-profile billing address. | up-to-date |
| created_at | The timestamp for when the subscription was created. | static |
| currency | The currency code (ISO 4217 format) of the subscription. | static |
| custom_fields | The subscription’s current custom fields. | up-to-date |
| customer.custom_fields | The customer’s current custom fields. | up-to-date |
| customer.email | The email address of the customer. | up-to-date |
| customer.full_name | The customer’s full name. | up-to-date |
| customer.id | The ID of the customer. | up-to-date |
| customer.organization | The customer’s company/organization name. | up-to-date |
| customer.reference | The unique identifier used within your own application for this customer. | up-to-date |
| customer.vat_number | The VAT number of the customer. | up-to-date |
| id | The ID of the subscription. | static |
| keen.timestamp | The timestamp for when the subscription was created. | static |
| next_assessment_at | Timestamp that indicates when capture of payment will be tried or retried. | up-to-date |
| payment_collection_method | The payment collection method for the subscription. Automatic or Invoice. | static |
| product.handle | The handle of the subscription’s current product. | up-to-date |
| product.id | The ID of the subscription’s current product. | up-to-date |
| product.price_point_handle | The handle of the subscription’s current product price point. | up-to-date |
| product.price_point_id | The ID of the subscription’s current product price point. | up-to-date |
| renewal_interval | The subscriber’s renewal interval. Examples include ‘1_month’ and ‘10_days’. | up-to-date |
| saas_metrics.arr | The subscription’s current ARR value (MRR * 12). | up-to-date |
| saas_metrics.breakouts.plan.arr | The portion of the subscription’s ARR value that comes from “plan” revenue: Products, Qty-Based Components, and OnOff Components. | up-to-date |
| saas_metrics.breakouts.plan.mrr | The portion of the subscription’s MRR value that comes from “plan” revenue: Products, Qty-Based Components, and OnOff Components. | up-to-date |
| saas_metrics.breakouts.usage.arr | The portion of the subscription’s ARR value that comes from usage revenue: Metered Components and EventBased Components. | up-to-date |
| saas_metrics.breakouts.usage.mrr | The portion of the subscription’s MRR value that comes from usage revenue: Metered Components and EventBased Components. | up-to-date |
| saas_metrics.mrr | The subscription’s current MRR value. | up-to-date |
| saas_metrics.potential_conversion_mrr | Displays the potential MRR that will be realized when the subscription converts from trialing to active. Please note that the potential MRR amount in captured at trial signup. The value in this field does not change upon trial conversion or trial cancellation. | static |
| sales_rep.id | The ID of the subscriber’s assigned Sales Rep. | up-to-date |
| sales_rep.name | The name of the subscriber’s assigned Sales Rep. | up-to-date |
| status | The current state of the subscription. Please see the documentation for Subscription States. | up-to-date |
| subscription_group.primary | Displays True when this subscription is the primary member of it’s subscription group. | up-to-date |
| subscription_group.primary_subscription_id | The ID of the subscription group’s primary subscription of which this subscription is a member. | up-to-date |
| subscription_group.uid | The UID of the subscription group of which this subscription is a member. It will be empty if the subscription does not belong to a group. | up-to-date |
Subscription Renewals
This data source captures subscription renewal events. Each time a subscription is processed, either at signup or regular renewal, a subscription renewal event is recorded to capture the details of the renewal. Use this data source to analyze your subscription renewal performance.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | static |
| address.country | The country portion of the payment-profile billing address. | static |
| address.state | The state portion of the payment-profile billing address. | static |
| address.zip | The zip portion of the payment-profile billing address. | static |
| customer.id | The ID of the customer. | static |
| customer.organization | The customer’s company/organization name. | static |
| keen.timestamp | The timestamp for when the renewal event occurred. | static |
| keen.uniqueness_token | static | |
| payment.amount_in_cents | The total payment amount associated with the renewal event. This amount in displayed in cents and relates to the subscription’s configured currency. | static |
| payment.gateway_used | The payment gateway used. | static |
| payment.success | Indicates whether or not the attempted payment associated with the event was successful or not. | static |
| payment.transaction_type | The type of payment associated with the renewal event. | static |
| product.handle | The handle of the subscription’s product at the time of the event. | static |
| product.id | The ID of the subscription’s product at the time of the event. | static |
| product.price_point_handle | The handle of the subscription’s product price point at the time of the event. | static |
| product.price_point_id | The ID of the subscription’s product price point at the time of the event. | static |
| renewal_type | Describes the renewal event type. One of “signup_success”, “signup_failure”, “renewal_success”, “renewal_failure” | static |
| subscription.activated_at | The timestamp for when the subscription was activated_at. | static |
| subscription.created_at | The timestamp for when the subscription was created. | static |
| subscription.currency | The currency code (ISO 4217 format) of the subscription. | static |
| subscription.current_period_ends_at | The timestamp for the subscription’s current billing period end. | static |
| subscription.current_period_started_at | The timestamp for the subscription’s current billing period start. | static |
| subscription.id | The ID of the subscription associated with this renewal event. | static |
| subscription.payment_collection_method | The subscription’s payment collection method at the time of the event. | static |
| subscription.renewal_interval | The subscriber’s renewal interval. Examples include ‘1_month’ and ‘10_days’. | static |
| subscription.status | The subscription status at the time of the event. | static |
Payments
Use this data source to analyze your payment history. This data source contains payment records.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | static |
| address.country | The country portion of the payment-profile billing address. | static |
| address.state | The state portion of the payment-profile billing address. | static |
| address.zip | The zip portion of the payment-profile billing address. | static |
| amount_in_cents | The amount in cents for the payment. | static |
| currency | The currency is which the payment occurred. | static |
| customer.custom_fields | The customer’s custom fields. | static |
| customer.email | The email address of the customer. | static |
| customer.id | The ID of the customer. | static |
| customer.organization | The customer’s company/organization name. | static |
| customer.vat_number | The customer’s VAT number. | static |
| id | The ID of the payment. | static |
| invoice.consolidation_level | The consolidation level of the associated invoice. | static |
| invoice.number | The number of the associated invoice. | static |
| invoice.uid | The unique identifier for the associated invoice. | static |
| keen.timestamp | The timestamp for when the payment was created. | static |
| memo | A note about the payment. | static |
| payment_type | The type of the payment associated with this transaction (credit_card, bank_account) | static |
| prepayment | Is true when this payment is a prepayment. | static |
| product.accounting_code | The product family accounting code (has no bearing in Advanced Billing, may be used within your app) | static |
| product.handle | The handle of the product purchased. | static |
| product.id | The ID of the product purchased. | static |
| product.price_point_handle | The subscription’s assigned price point handle when the payment occurred. | static |
| product.price_point_id | The subscription’s assigned price point ID when the payment occurred. | static |
| product.tax_code | The product tax code. | static |
| subscription.custom_fields | The subscription’s custom fields. | static |
| subscription.id | The ID of the subscription. | static |
| subscription.locale | The subscription’s locale. | static |
| subscription.status | The status of the subscription at the time of the payment. | static |
| success | Is true when this payment is successful. | static |
| test | Is true when payment was created in test mode. | static |
Refunds
Use this data source to analyze your refund history. This data source contains refund records.
| Field | Description | Value |
| address.city | The city portion of the payment-profile billing address. | static |
| address.country | The country portion of the payment-profile billing address. | static |
| address.state | The state portion of the payment-profile billing address. | static |
| address.zip | The zip portion of the payment-profile billing address. | static |
| amount_in_cents | The amount in cents for the refund. | static |
| currency | The currency is which the refund occurred. | static |
| customer.custom_fields | The customer’s custom fields. | static |
| customer.email | The email address of the customer. | static |
| customer.id | The ID of the customer. | static |
| customer.organization | The customer’s company/organization name. | static |
| customer.vat_number | The customer’s VAT number. | static |
| id | The ID of the refund. | static |
| invoice.consolidation_level | The consolidation level of the associated invoice. | static |
| invoice.number | The number of the associated invoice. | static |
| invoice.uid | The unique identifier for the associated invoice. | static |
| keen.timestamp | The timestamp for when the refund was created. | static |
| kind | The refund kind. | static |
| memo | A note about the refund. | static |
| origin_payment_id | The ID of the payment that this refund is associated with. | static |
| product.handle | The handle of the product purchased. | static |
| product.id | The ID of the product purchased. | static |
| product.price_point_handle | The subscription’s assigned price point handle when the refund occurred. | static |
| product.price_point_id | The subscription’s assigned price point ID when the refund occurred. | static |
| refund_type | The type of the refund associated with this transaction (credit_card, bank_account) | static |
| subscription.activated_at | The timestamp for when the subscription was activated. | static |
| subscription.created_at | The timestamp for when the subscription was created. | static |
| subscription.custom_fields | The subscription’s custom fields. | static |
| subscription.id | The ID of the subscription. | static |
| subscription.locale | The subscription’s locale. | static |
| subscription.status | The status of the subscription at the time of the refund. | static |
| success | Is true when this transaction is successful. | static |
| test | Is true when refund was created in test mode. | static |