topic Re: Goals vs Revenue for Multi-Year Contracts in Dashboards & Reporting

Goals vs Revenue for Multi-Year Contracts

JCrowNM — Tue, 06 Feb 2024 01:23:53 GMT

We have contracts with multi-year deals. We have the amounts of those deals split out in different fields, e.g. FY24 Amount, FY25 Amount, etc.

I want to compare how much we have booked (closed won deals) vs the annual goal for the year. I can't use the Goal function within HubSpot, the line chart when adding the different FY's vs the yearly goal is more confusing than helpful because the goals need to have a date line comparison.

I was thinking of just adding fields for the FY Goals. But if I add the fields to the Deal object I would have to do something like create a fake deal where the revenue goal is entered but everything else is blank. Then I would have to use this fake deal to compare the revenue to the goal.

So the goal has to be for the our organization, but I am not sure the best way to accomplish this.

Any suggestions?

Re: Goals vs Revenue for Multi-Year Contracts

HFisher7 — Tue, 06 Feb 2024 16:22:45 GMT

Hey @JCrowNM - What HubSpot subscription are you working with to achieve the above?!

If you have access to an Enterprise subscription you could create a custom object for revenue goals and associate the relevant deals to each yearly revenue goal to see if it has been met? I've done something similar for margin goals, as detailed here: https://hubs.li/Q01NGdb10

Alternatively, if you have Ops Hub and access to datasets you can create a dataset to manage the goal vs revenue calculations. On that thought, I wonder if the formula fields BETA in reports will enable you to calculate a goal: https://knowledge.hubspot.com/reports/create-formula-fields-in-the-custom-report-builder

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Tue, 06 Feb 2024 16:44:36 GMT

I have Sales and Marketing Professional subscriptions. I will take a look at the formula fields.

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Tue, 06 Feb 2024 17:02:04 GMT

Hannah,

Thanks for the feedback. Originally I thought that you had to work with a field that was already created. But it looks like you can just add a value. I need to figure out how to get it to display on the report but it looks like it might work. I will keep you updated.

Re: Goals vs Revenue for Multi-Year Contracts

PamCotton — Tue, 06 Feb 2024 21:01:04 GMT

Hey @JCrowNM Happy Tuesday,

Let us know if you need any help. @HFisher7 thank you for the helpful information provided.

Kindly,

Pam

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Tue, 06 Feb 2024 22:26:32 GMT

So, I was able to create a static number for the goal in the formula field. The problem now is that the bar chart doesn't seem to want to display unless the field is aggregated. But if I aggregate by, say, sum, it creates a goal number that is far greater than the original number. I thought maybe I could have the goal number divided by like [Deal.Count] but that doesn't seem to be possible. I am also trying to figure out how to make that number formatted as currency.

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Wed, 07 Feb 2024 00:00:01 GMT

Yes. The formula field got me part of the way there, but still having an issue with the bar chart doesn't seem to want to display unless the field is aggregated. But if I aggregate by, say, sum, it creates a goal number that is far greater than the original number. I thought maybe I could have the goal number divided by like [Deal.Count] but that doesn't seem to be possible. I am also trying to figure out how to make that number formatted as currency.

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Wed, 07 Feb 2024 01:07:36 GMT

Ok. I figured out that I can aggregate by Average, which gives me the number I started out with. Now I just have to find a way to format the number as currency.

Re: Goals vs Revenue for Multi-Year Contracts

HFisher7 — Wed, 07 Feb 2024 08:49:39 GMT

Click on the little edit pencil to the right of the formula field, then select 'Currency' from within the 'Format' dropdown..

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Wed, 07 Feb 2024 17:43:15 GMT

Ugh. So simple, yet it very much elluded me. Thanks for all your help. This worked like a charm!

Re: Goals vs Revenue for Multi-Year Contracts

JCrowNM — Wed, 07 Feb 2024 17:47:45 GMT

Just wanted to provide a concise recipe of what I did in case others have a similar need.

1. Signed up for the formula fields beta 🙂

2. Create a new report.

3. Added a new formula field with a static number..

4. Added my FY24 Amount which adds all the values for deals with dollar amounts in FY24.

5. Added the formula field to the bar chart report with the Aggregate set to average and the format set to Currency.

Voila! Thanks again to @HFisher7 to getting me there.

Re: Goals vs Revenue for Multi-Year Contracts

HFisher7 — Fri, 23 Feb 2024 12:48:53 GMT

@JCrowNM - Do you have an image of the final report!? I'd be interested to see how you acheived it! DM if you'd rather