Invoicing Frequency and revenue by month by company

[JenP102787]

Hi!

I'm new here and would appreciate some pointers! 

I'm trying to setup a report where I can use invoicing frequency, line items, billing frequency to calculate how much is generate month on month.

For ex.

Billing frequency is Quarterly

Company gets billed in March. Total Annual Recurring Rev is 10000. So I would expect to see 2500 for March. I want to run this report monthly and generate the billing amount for that month by company and line items. Any suggestions or help would be much appreciated! 

Many thanks,

[Jnix284 · Accepted solution]

Hi @JenP102787 ,

To figure out how to build this report, we have to unpack this a little bit.

Do you have a custom property for billing frequency? and also a corresponding property to identify which month the invoicing starts (to indicate it starts in March and would be every 3 months)?

For the Total Annual Recurring Revenue - you can create a calculated property based on the billing frequency to calculate the $2500 that should be billed and show in your reports.

With these data points, you could then build a report that associates the $2500 with the corresponding months.

I'm not 100% on how to incorporate the line items, but I think this is a good starting point to isolate some of the data to get the initial reports outlined.

[jolle · Accepted solution]

Hey @JenP102787, thanks for sharing this use case! I'm not sure how you're currently storing revenue and billing info in HubSpot (or if you use a separate platform for billing and invoicing), but I have some thoughts based on what you provided.

First, if you have different billing frequencies for different companies (i.e., monthly, quarterly, annually), you'll probably need a separate report for each billing frequency. If we used the quarterly billing company you provided, a report showing monthly revenue wouldn't be that helpful since only 4 months out of the year would show revenue billed.

If you're layering in line items as well, there are a couple things to note:

1. You should tie in the line item amount to the associated deal amount if that hasn't been done already. Standardizing revenue in a single property (or as close to that as you can get) is really important if you want to use native HubSpot reporting
2. Looking at this HubSpot Knowledge Base article on line items, you can set the recurring billing frequency for each line item/product. That may get you a good bit of the way there depending on how you're currently using line items 

I feel like using these native HubSpot tools would give you the best shot at building out month-specific revenue forecasts (I know I was able to build something like that for recurring revenue).

If you're looking to take more of a manual approach (that most likely will not be tied to particular months), you could try out calculation properties. Here's what you would need:

1. A custom number property like "Number of Payments Per Year" that would be set based on the billing frequency (so "4" for quarterly, "12" for monthly, etc.)
2. Complete billable revenue info stored in one place (ideally "Deal amount")
3. A calculation property that divides the "Deal Amount" by "Number of Payments Per Year"

For each deal, that would give you a payment amount per payment term that you could pass along to the associated company record.

This may not be cloes at all to what you're looking for depending on your use case, but I wanted to share in case it's helpful!

Hopefully this gives you a sense for where to start! Happy to share some additional thoughts if there's anything else you can tell us about your use case and current setup. Thanks!

[JenP102787]

Thanks Jolle! I think you share some really good ideas. I will try to build them out on some of the suggested. I am trying to layer the line items too. But at the moment, try to work out what happens to one-off and different cases if was in the same deal. 

This acts as an interim process while I work out a invoice integration tool! So much fun... 

Thank again. Will let you know I have another question. 🙂

[Jnix284 · Accepted solution]

Hi @JenP102787 ,

To figure out how to build this report, we have to unpack this a little bit.

Do you have a custom property for billing frequency? and also a corresponding property to identify which month the invoicing starts (to indicate it starts in March and would be every 3 months)?

For the Total Annual Recurring Revenue - you can create a calculated property based on the billing frequency to calculate the $2500 that should be billed and show in your reports.

With these data points, you could then build a report that associates the $2500 with the corresponding months.

I'm not 100% on how to incorporate the line items, but I think this is a good starting point to isolate some of the data to get the initial reports outlined.

[JenP102787]

Thanks for this information.  I will try to build something based on the information shared here. I have billing frequency as a custom property (Annually, Quarter, Monthly etc..) along with invoice start date.  I think the calculated property could work. I shall try and see what happens. Thanks for the ideas. 🙂