topic Re: Need to build a Monthly Flight Revenue Report in Dashboards & Reporting

Need to build a Monthly Flight Revenue Report

VMerrill — Thu, 20 Feb 2025 21:57:38 GMT

We have a client that works in digital media. So when they sell, they don't see revenue the moment deal is closed, the gain revenue as the flight is completed.

So for example, there can be a deal with Product A. Product A will run from 01/01/2025 to 01/01/2026. We have those properties set as Flight Start Date and Flight End Date on the Line Item level.

The monthly rate for each product is also set. So we have the Flight Start Date, the Flight End Date, and then how much of the revenue they should be receiving each month.

We somehow then need a way to divide that rate by the number of months in the month so we can get a daily rate.

And then on top of that, need to pull a report that shows how much revenue they are receiving each day, month, year, etc. I think the big thing was missing is HubSPot doesn't have the function of "this line item's flight date is going on Today".

Anyone have any ideas? Or is there a third party reporting app we should be using?

Re: Need to build a Monthly Flight Revenue Report

DianaGomez — Fri, 21 Feb 2025 11:15:31 GMT

Hi @VMerrill,

Thanks for reaching out to the Community!

I was reviewing this topic and found that several users in the community have had the same question. They've been able to resolve it by following different methods shared by some of our experts. I’m sharing some of those conversations here so you can check if any align with what you're looking for, or if any steps can help you get started on creating your report.

Monthly Recurring Revenue - reporting

Revenue report

Combined Revenue Report

Report for Company Revenue vs Goal

I’m also inviting those experts to join this conversation in case they have any insights to add for your specific use case. 🙂

Hi @danmoyle, @MPalmer6, @crispyGTM, @FarioConsulting, @Shadab_Khan - do you have any insight for @VMerrill to build out a report or dashboard to see a Monthly Flight Revenue Report?

Thanks!

Diana

Re: Need to build a Monthly Flight Revenue Report

danmoyle — Mon, 24 Feb 2025 16:51:43 GMT

Thanks @DianaGomez - and hello @VMerrill! In addition to the links from Diana, here's what I'm thinking (full disclosure, I havent done this, but I think it makes sense).

In order to build a monthly flight revenue report as you described, you'll need to create a system that calculates daily rates and tracks revenue over time. It sounds like you've got a lot of that in place. Here's what else I think you could do to solve this.

I think you have the monthly rate for each product (you calculated the number of days in each month of the flight period and divided the monthly rate by the number of days in the month - that should tell you the daily rate). But if that's not how I understand what you've described you've already done, then you can use a daily rate calculator to simplify this process. The formula is: Daily Rate = Monthly Rate ÷ Number of Days in the Month

Next, creating the revenue report. To generate a report showing revenue for each day, month, and year:

Set up a pivot table report in HubSpot with columns for Date, Product, Daily Rate, and Revenue.
For each product, list all dates from the Flight Start Date to the Flight End Date.
Apply the daily rate to each date.
Sum the daily rates to get monthly and yearly totals.

Now, the next step of tracking current flights. Either in a spreadsheet (gross - but might be a necessary step if a custom report doesn't do this) or in a custom report pivot table, you want to identify which line items are active on a given date:

Create a column for "Active" (Yes/No).
Use a formula to check if the current date falls between the Flight Start Date and Flight End Date.
Filter the report to show only active flights.

So here's what I'm thinking I'd try if it were my client. I'd try HubSpot's custom reporting with pivot tables, or one of these other options.

Custom Integration

Develop a custom integration that pulls data from HubSpot and processes it using the logic described above.

Third-Party Reporting Tools

Look into business intelligence tools that can connect to HubSpot and perform complex calculations, such as Tableau or Power BI.

Spreadsheet Solution

Export HubSpot data regularly and use Excel or Google Sheets with formulas and pivot tables to create the required reports.

Custom Development

If you have development resources, create a custom application that interfaces with HubSpot's API to pull data and generate the reports.

I don't know that it's an easy button solution, but hopefully this gives you something to think about. In the meantime, I'd definitely look for this as a feature idea or submit it in the ideas section.

Re: Need to build a Monthly Flight Revenue Report

VMerrill — Mon, 24 Feb 2025 18:33:41 GMT

So question, right now the data points are housed on the line item level, and we can't create calculated fields on the line item. So we were thinking we create a custom Product object to be created when a line item is set, so we can have the calculations be done there.

Would you recommend this or is there a better place to house that Daily Rate?

Re: Need to build a Monthly Flight Revenue Report

danmoyle — Tue, 25 Feb 2025 13:37:22 GMT

That's an interesting point, @VMerrill. Good call out on the calculation in Product Properties not being supported.

I think yes, a custom object could be a way around that. And maybe creating sync properties between the standard HubSpot Product object and your Custom Product object could help keep those two in sync - I'd imagine there's functionality built into the standard object you may want to take advantage of.

I agree on trying that custom object route.

Re: Need to build a Monthly Flight Revenue Report

frankjferris — Wed, 23 Apr 2025 21:08:33 GMT

Hey @VMerrill ,

For this custom/complex business logic, I'd highly recommend working in a spreadsheet instead of HubSpot directly. Nothing beats the flexibility and control you get with spreadsheet formulas.

You can use a 3rd party tool to import your HubSpot data to Google Sheets or Excel (and even back to HubSpot!). I love Coefficient's extension but there are a few other options you could consider. Then it becomes as simple as importing your Flight Start Date and End Date and then writing a formula such as =Days(StartDate,EndDate) or =NetWorkDays(StartDate,EndDate,[holidays]) to get number of days between 2 dates (networkdays will exclude weekends and holidays, if desired).