How To Report On Closed-Lost Deal Insights In HubSpot

Understand where in your pipeline you're losing deals and why.

Closed lost deals are a huge opportunity most teams are missing 😩

When a deal is lost do you:

1. Consistently note the reason in your CRM?
2. Identify which stage the deal was lost from?
3. Retro the deal journey to identify how to improve the experience for future deals?
4. Engage lost deal contacts with a relevant nurture sequence?

Today, we’re covering how to improve your visibility into how and where deals are lost.

---

---

Step-by-step reporting for closed-lost deals

Step 1: Add required closed lost deal properties

Using required properties is a great way to capture insights about deal lost reasons when a deal is moved to lost in the sales pipeline.

To improve the quality of our data we’ll make 2 modifications to the default open text HubSpot property for closed lost reasons.

1. Edit the name of the HubSpot open text property to “Closed Lost Reason Details”

2. Create a new single select deal property “Closed Lost Reason” with the following options (or your preferred options).
→ Budget
→ Chose Competitor
→ Missing Features
→ No Decision
→ Timing
→ Unresponsive

3. Make both of these properties required in the conditional property settings for the Closed Lost stage of the sales pipeline.

Step 2: Create a “Last Stage Before Lost” property

Our goal with this property is to keep track of the stage a deal was in before it was moved to closed lost so we can see if there are patterns and bottlenecks in our sales pipeline. There are 2 approaches we can use to update this property:

1. Automated Workflow

Using a workflow we can automatically populate a “Stage Before Deal Closed Lost” whenever the stage of a deal is changed except when it is moved to closed lost.

Here’s how it works:

1. Create a deal-based workflow
⚡️ Trigger Criteria: Property value changed
→ Deal Stage is known
→ AND "Deal Stage is None of “Closed Lost” AND Pipeline is “Sales Pipeline”

2. Set a property value
→ Property: Stage Before Deal Closed Lost
→ Value: Deal Stage

2. Calculated Property

This method is more complex to implement but is updated in the same way any other property would be without requiring an additional workflow.

Here’s how it works:

1. Create a new calculated deal property called “Deal Before Closed Lost” in the Deal activity property group.

2. Select the “Calculation” field type and choose “Custom Equation”

3. Copy + paste the formula below into your property editor and update the “Date Exited” properties used if you have added more than the default HubSpot stages to your sales pipeline.

if(
    (([properties.dealstage] == "closedlost") && ([properties.pipeline] == "default")),
    if(
        is_known([properties.hs_v2_date_exited_appointmentscheduled]) && 
        (
            ([properties.hs_v2_date_exited_appointmentscheduled] >= if(is_known([properties.hs_v2_date_exited_qualifiedtobuy]), [properties.hs_v2_date_exited_qualifiedtobuy], [properties.hs_v2_date_exited_appointmentscheduled])) &&
            ([properties.hs_v2_date_exited_appointmentscheduled] >= if(is_known([properties.hs_v2_date_exited_presentationscheduled]), [properties.hs_v2_date_exited_presentationscheduled], [properties.hs_v2_date_exited_appointmentscheduled])) &&
            ([properties.hs_v2_date_exited_appointmentscheduled] >= if(is_known([properties.hs_v2_date_exited_decisionmakerboughtin]), [properties.hs_v2_date_exited_decisionmakerboughtin], [properties.hs_v2_date_exited_appointmentscheduled])) &&
            ([properties.hs_v2_date_exited_appointmentscheduled] >= if(is_known([properties.hs_v2_date_exited_contractsent]), [properties.hs_v2_date_exited_contractsent], [properties.hs_v2_date_exited_appointmentscheduled]))
        ),
        "Appointment Scheduled",
        if(
            is_known([properties.hs_v2_date_exited_qualifiedtobuy]) && 
            (
                ([properties.hs_v2_date_exited_qualifiedtobuy] >= if(is_known([properties.hs_v2_date_exited_presentationscheduled]), [properties.hs_v2_date_exited_presentationscheduled], [properties.hs_v2_date_exited_qualifiedtobuy])) &&
                ([properties.hs_v2_date_exited_qualifiedtobuy] >= if(is_known([properties.hs_v2_date_exited_decisionmakerboughtin]), [properties.hs_v2_date_exited_decisionmakerboughtin], [properties.hs_v2_date_exited_qualifiedtobuy])) &&
                ([properties.hs_v2_date_exited_qualifiedtobuy] >= if(is_known([properties.hs_v2_date_exited_contractsent]), [properties.hs_v2_date_exited_contractsent], [properties.hs_v2_date_exited_qualifiedtobuy]))
            ),
            "Qualified to Buy",
            if(
                is_known([properties.hs_v2_date_exited_presentationscheduled]) && 
                (
                    ([properties.hs_v2_date_exited_presentationscheduled] >= if(is_known([properties.hs_v2_date_exited_decisionmakerboughtin]), [properties.hs_v2_date_exited_decisionmakerboughtin], [properties.hs_v2_date_exited_presentationscheduled])) &&
                    ([properties.hs_v2_date_exited_presentationscheduled] >= if(is_known([properties.hs_v2_date_exited_contractsent]), [properties.hs_v2_date_exited_contractsent], [properties.hs_v2_date_exited_presentationscheduled]))
                ),
                "Presentation Scheduled",
                if(
                    is_known([properties.hs_v2_date_exited_decisionmakerboughtin]) && 
                    ([properties.hs_v2_date_exited_decisionmakerboughtin] >= if(is_known([properties.hs_v2_date_exited_contractsent]), [properties.hs_v2_date_exited_contractsent], [properties.hs_v2_date_exited_decisionmakerboughtin])),
                    "Decision Maker Bought In",
                    if(
                        is_known([properties.hs_v2_date_exited_contractsent]),
                        "Contract Sent",
                        ""
                    )
                )
            )
        )
    ),
    ""
)

4. Also update the deal stage names inside “” in the formula to match your stage names.

5. Test your formula by adding dates to the formula test section of the property builder, click test and make sure the sample output matches the stage which you added the latest date to.

Knowing which stage a deal was last in before being closed lost can help identify pipeline bottlenecks as well as being invaluable when combined with closed lost reasons to find common patterns in how reps are managing (or mismanaging) deals.

Step 3: Create closed lost reporting

Report #1 - Deals by Closed Lost Reason

📀 Data sources of "Deals"
📊 Use the “Vertical Bar” chart type:
→ X-Axis: Closed Lost Reason
→ Y-Axis: Count of Deals
→ Breakdown by: Deal Owner
→ Filter: Deal stage is any of Closed Lost (Sales Pipeline) 

Report #2 - Monthly Closed Lost Deals by Reason

📀 Data sources of "Deals"
📊 Use the “Vertical Bar” chart type:
→ X Axis: Close Date (Monthly)
→ Y Axis: Count of Deals
→ Breakdown by: Closed Lost Reason
→ Filter: Deal stage is any of Closed Lost (Sales Pipeline) 

Report #3 - Lost Deals By Last Stage

📀 Data sources of "Deals"
📊 Use the “Vertical Bar” chart type:
→ X-Axis:  Last Stage Before Close
→ Y-Axis: Count of Deals
→ Breakdown by: Deal Owner
→ Filter: Deal stage is any of Closed Lost (Sales Pipeline) 

Report #4 - Monthly Lost Deals By Last Stage

📀 Data sources of "Deals"
📊 Use the “Vertical Bar” chart type:
→ X-Axis:  Close Date (Monthly)
→ Y-Axis: Count of Deals
→ Breakdown by: Last Stage Before Close
→ Filter: Deal stage is any of Closed Lost (Sales Pipeline) 

With a better understanding of how and why deals are being lost, you’ll more easily identify opportunities for rep coaching, process improvement, and importantly have a solid starting point for running effective closed-lost reengagement campaigns.

Option 2: Deploy pre-built closed-lost properties and reports (~3 minutes)

To save you time implementing everything described above from scratch I’ve packaged the custom properties and reports up in a ready-to-deploy module using Supered.