Show one-time billing frequency revenue

[01705]

Hi

I would like to display the one-time revenue from a deal that i have.

Problem is that the one-time revenue is always showed two times, at the start of the billing period (e.g. 21.11.22 and 21.01.23 here). How can I make sure it is only display when it is actually billed: 21.11.22 in this example. I am using a calculated property I created based on this guide.

Please find the deal line items below:

And my current dashboard:

FYI: I added some more context in my second reply.

Thanks so much for your help.

[Berangere]

Hi @01705, I hope that you are well!
I am really glad that you asked the Community, thanks!
I’d like to ask clarifying questions:
1. Is this a custom report or a native default report?
2. If it is a custom report, which data sources did you select?
3. Which properties are you using? You mentioned the “one-time revenue” custom calculated property. Which date property are you using?
The more info, screenshots (without sensitive/confidential information), and details you can provide, the better the Community can assist.
I’d like to invite our top experts to this conversation: Hi @danmoyle, @Mike_Eastwood and @GrantCarlile do you have suggestions to help @01705, please?

If anybody else has anything to add and/or share, please feel free to join in the conversation

Thanks a lot and have a fantastic day!
Best,
Bérangère

[01705]

Hi @BérangèreL thanks so much for your comment.
Regarding your questions:

1. Is this a custom report or a native default report?
It is a custom report.
2. If it is a custom report, which data sources did you select?

I selected Deals and Line Items
3. Which properties are you using? You mentioned the “one-time revenue” custom calculated property. Which date property are you using?

I am using: Billing Start date for the date. Then Sum of one-time revenue that I calculated, here the alternative would be to use net price, but I cannot filter by billing frequency so if I have a recurring payment and a one-time payment both are showing up in the report and I only want one-time revenue.

I added some more screenshots of the report setup. The jump from 6k to 12k when using One-Time Revenue property is e.g. because after a 2 month trial, the annual recurring revenue is kicking in and somehow Hubspot adds the one-time line items just again but it is coming from the same deal and same line item.

Then on the other hand if I use only the net price the jump from 6999 to 7998 is also because of the same situation as just described. But then again actually the one-time revenue is not 6999 in the first place, but actually 6000.

Please let me know if you have further questions.

[Mike_Eastwood]

Hi @01705

If you remove the Line Item, from the Data Source, does it stop the double counting?

Curiously

Mike

[01705]

Hi @Mike_Eastwood

thanks for your comment.

If I remove the Line Item from the Data Source I loose the date property as I am using Billing start date for this which comes from the Line Item.

Any other ideas?

cc: @BérangèreL @danmoyle, and @GrantCarlile.

Thanks so much for your help.

Manuel

[Parvind]

This is from my SageGPT assistant - see it works

From the screenshots you’ve provided and the description of the problem, it appears that the custom report in HubSpot is double-counting one-time revenues because it aggregates them both from the deals and the line items. Here’s a step-by-step approach to resolve this issue:

Steps to Resolve Double Counting:

1. Deduplication Rule:
- Since the ‘Billing Start Date’ is coming from line items, you should ensure each line item is only counted once. If the ‘One-Time Revenue’ property is calculated from the line items, it should not aggregate from the deals again.

2. Correct Data Source:
- If the ‘Billing Start Date’ and ‘One-Time Revenue’ or ‘Net Price’ are both in line items, you do not need to select deals as a separate data source. This seems to be causing the duplication.

3. Filter for One-Time Payments:

- If you have both recurring and one-time line items, there should be a property that differentiates them. If not, consider creating one. Use this property to filter the report and include only one-time payments.

4. Use Single Property for Revenue:
- Consolidate your ‘One-Time Revenue’ into a single property within the line items. Make sure this property is not duplicated in the deal data source.

5. Custom Report Setup:
- When setting up the custom report, select ‘Line Items’ as the data source.
- For the date, use ‘Billing Start Date’ from the line items.
- For the revenue, use the ‘One-Time Revenue’ property or ‘Net Price’ filtered for one-time billing frequency from line items.

6. Report Filters and Settings:
- Adjust the filters to exclude any line items that do not represent the one-time revenue.
- Ensure the timeframe of the report matches the ‘Billing Start Date’ and not the creation date of the deal or line item.

7. Test the Report:
- After making adjustments, test the report with a known set of data where you have the expected one-time revenue outcomes.

Further Troubleshooting:

- HubSpot Custom Properties Logic Review:
- Double-check the logic of any custom properties or calculations to ensure they’re functioning as intended.

If removing the ‘Deals’ data source results in losing essential data properties, you may need to re-evaluate how the properties are set up within the CRM to ensure that the necessary data can be reported on without duplication. Remember, the goal is to have each one-time revenue counted once for its respective billing period.

[01705]

Any idea @Mike_Eastwood @BérangèreL @GrantCarlile @danmoyle :)?

[danmoyle]

Hi @01705 - I’ve been out of the office for an extended weekend. I’ll be catching up with Community messages throughout the week. Thanks!

[danmoyle]

@01705 if you want to report on the product (line item) and its one-time revenue, can you create a product report and not use the Deal object? I’ll experiment with my own portal to see if there’s something I can do to replicate your set-up and report on a one-time billing with the Deal, but that’s my first thought - just report on the product.