The Clean ARR table

• By
• Chris Burgner

  Chris Burgner

  12+ years of experience, most recently as a Director of Finance at Intercom.

  

This will get you from the raw Base ARR table to a clean view of the primary object data. The query can get lengthy here, especially with the invoice-based method, as there are more edge cases to address in the data.

Every company will have unique nuances to contend with, but accounting for the below will get you ~95% of the way there.

Accounting for billing frequency (invoice or subscription)

As discussed earlier, you’ll want to derive the ARR amounts directly in the query by taking the product SKU multiplied by quantity. If you’re working with invoices, this will require an extra step of adjusting for invoice duration.

$$\frac{\text{product\_sku\_price}\times \text{prod\_quantity}}{\text{charge\_duration (in months)}}\times 12=\text{ARR}$$

Overlapping and backdated invoices (invoice specific)

Overlapping invoices can cause double counting as you typically only want to honor a single invoice on any given day for each subscription. Here’s how to account for the two edge cases discussed in the previous chapter, Defining Your ARR:

• Corrective invoices (same day): A quick window function can be used to honor the last invoice created on any given day. It’s a good idea to review a handful of examples to ensure this logic is correct and to understand better why corrective invoices are being issued.
• Backdated invoices: To avoid restating historicals, create a “start_date_clean” field that defaults to the created date when the created date > the start date on the invoice. As with corrective invoices, review a handful of these examples to ensure the ARR logic matches your current business logic.
• Amendments: The logic used to account for billing frequency can also be applied to calculating ARR values for amendments. The only additional piece needed is adjusting the “end date” of the original invoice (pre-amendment) to one day prior to the amended invoice’s issue date.

Removing non-recurring items (invoice or subscription)

Clean up the dataset for non-recurring products or services and test accounts.

• Non-recurring items: Identify any products that are one-time in nature and remove them from your calculations. In your ARR Build, you only want to focus on recurring items.
• Discounts: Remove recurring fixed dollar or percent-off discounts from the derived amounts. To enable discount reporting, it may be helpful to surface both gross and net amount fields in your calculations. This can be enlightening from a macro perspective but also useful in enforcing policies on a customer-by-customer basis.
• Test accounts: Lastly, always be sure to remove any test accounts from the data. Ideally you have a way to remove these with some kind of 100% off coupon, but keep a close eye out for any emails from your company’s domain or customer names matching your employees.

Ultimately, you want to aggregate the data into a single record per primary object id. Here’s an example of what the output should look like: