Exclude records with certain deal stages from a company report

[TFarris]

I'm trying to build a report that shows my pipeline of companies with a lifecycle of SQL and Opp but I want to exclude those that have a closed won & closed lost deal associated with them.  The goal is to see what the remaining potential is in the pipeline. 

I'm using the new report builder and when I use the filter Deal Stage is none of closed won, closed lost I'm still getting company records that have closed won or closed lost deals associated with them. 

Using the free CRM

[karstenkoehler · Accepted solution]

Hi @TFarris,

By default, HubSpot will always apply the principle "at least one associated record meets the criteria". Could you confirm that the companies that are still showing up have only one associated deal and that this deal is closed won or closed lost? Even if there is just one other deal that is neither closed won nor closed lost, the report would show the associated company.

For the overall goal of your report, I think it would make sense to include these records – if a company has multiple deals associated and some of them are not closed yet, they would signal potential (if these deal records are up to date).

If however you want to exclude all companies that have a closed won or closed lost deal associated, I believe this can only be done via an exclusion list. You would create an active company list, set filters to include all companies that are associated with at least one deal in the Deal stage "Closed won" or "Closed lost". You would then exclude this list from your report.

(If you can confirm that these are companies with just one deal, that deal is closed and the company is still showing up despite the filter for only open deals, that would be a bug and I'd recommend reaching out to HubSpot technical support.)

Hope this helps!

[webdew]

Hi @TFarris ,

If you are using the filters 'deal stage is none of closed won or closed lost', then it shouldn't include the the deals with these stages. 
Can you please share a screen shot what all filters you have applied in your report?

Regards.

[karstenkoehler · Accepted solution]

Hi @TFarris,

By default, HubSpot will always apply the principle "at least one associated record meets the criteria". Could you confirm that the companies that are still showing up have only one associated deal and that this deal is closed won or closed lost? Even if there is just one other deal that is neither closed won nor closed lost, the report would show the associated company.

For the overall goal of your report, I think it would make sense to include these records – if a company has multiple deals associated and some of them are not closed yet, they would signal potential (if these deal records are up to date).

If however you want to exclude all companies that have a closed won or closed lost deal associated, I believe this can only be done via an exclusion list. You would create an active company list, set filters to include all companies that are associated with at least one deal in the Deal stage "Closed won" or "Closed lost". You would then exclude this list from your report.

(If you can confirm that these are companies with just one deal, that deal is closed and the company is still showing up despite the filter for only open deals, that would be a bug and I'd recommend reaching out to HubSpot technical support.)

Hope this helps!

[TFarris · Accepted solution]

Thank you Karsten, this worked! 

The companies did have only 1 associated deal. We are only a few months in using HubSpot so our data set is very small (79 deals). This gives me the intel I need for the future when we will hopefully have more open deals on previously closed lost deals.

I'll reach out to Support as well.  Thank you, you saved me from a big manual effort!

[karstenkoehler]

Happy to help, @TFarris! It looks like you marked your own post as a solution – you can help the community by also accepting my answer as a solution. Have a great day!