HubSpot Relative Dates: Advanced Reporting with Calculated Properties

HubSpot's reporting tools are powerful, but sometimes you need to slice and dice data in ways that go beyond the standard options. One HubSpot Community member ran into this when trying to create reports based on relative months, specifically looking at leads enrolling several months into the future. The goal was to have a report that dynamically adjusted its timeframe each month, showing a consistent window of opportunity.

The Challenge: Relative Date Reporting

The original poster wanted to track sales leads enrolling four to five months into the future. For example, on January 1st, the report should display leads from May 1st to June 30th. Then, on February 1st, it should automatically shift to show leads from June 1st to July 31st, and so on. The standard HubSpot filters only offer relative tracking on a daily, monthly, quarterly, or yearly basis, which wasn't precise enough.

The initial idea involved using calculated properties to assign a number to each month and then use further calculations to filter the data. But is this the most efficient approach?

Solutions from the HubSpot Community

Several community members chimed in with helpful suggestions, offering different approaches to tackle this challenge.

1. Workflow Automation for Date Management

One respondent suggested using a workflow that runs on the first of each month. This workflow would update single-line text properties, such as "report_window_start" and "report_window_end," with the dates four to five months in the future. These properties could then be used as report date filters. This method provides a reliable way to manage date ranges without complex calculations.

2. Custom Report Builder with Relative Date Filters

Another option involves using the custom report builder with relative date filters set to "is after" a rolling date and "is before" another rolling date. While not as precise as exact month boundaries, this approach can provide a close approximation with minimal maintenance, especially for users on Professional plans or higher.

3. Calculated Properties with Date Adjustments

A more complex solution involves using calculated properties to adjust dates. One contributor shared a calculation that outputs the month and year, suitable for display purposes. For filtering, they suggested creating a calculated property that adds or subtracts five months (converted to milliseconds) from the date and then checks when that "fake" date property falls within the desired month.

Here's the code snippet provided:

if(month([properties.closedate])=1, concatenate("May ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=2, concatenate("Jun ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=3, concatenate("Jul ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=4, concatenate("Aug ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=5, concatenate("Sep ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=6, concatenate("Oct ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=7, concatenate("Nov ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=8, concatenate("Dec ", number_to_string(year([properties.closedate]))), 
if(month([properties.closedate])=9, concatenate("Jan ", number_to_string(year([properties.closedate])+1)), 
if(month([properties.closedate])=10, concatenate("Feb ", number_to_string(year([properties.closedate])+1)), 
if(month([properties.closedate])=11, concatenate("Mar ", number_to_string(year([properties.closedate])+1)), 
if(month([properties.closedate])=12, concatenate("Apr ", number_to_string(year([properties.closedate])+1)), 
"error"))))))))))))

This code snippet uses nested if statements to determine the month from a date property (closedate) and then concatenates the corresponding month name and year. Note that this is mainly for display and would need to be adapted for filtering purposes.

4. Simple Date Addition

A simpler approach: Create an “expected close date” calculated property that is the “lead/deal create date” + 150 days. Then for reporting, analyze expected close date by month.

Choosing the Right Approach

The best solution depends on your specific needs and HubSpot plan. If you need precise month boundaries and want a reliable, automated solution, the workflow approach is likely the best choice. If you're looking for a simpler, low-maintenance option, the custom report builder with relative date filters might suffice. For more complex scenarios, calculated properties offer flexibility, but require careful implementation and testing.

ESHOPMAN Team Comment

We think the workflow approach is the most robust and scalable solution for this problem. While calculated properties can be powerful, they can also become complex and difficult to maintain. Using a workflow to update date properties ensures accuracy and simplifies reporting. This is especially useful when you are selling merchandise through a storefront connected to HubSpot, because it enables you to accurately forecast and report on future sales trends.

Ultimately, understanding how to manipulate dates in HubSpot opens up a world of possibilities for creating custom reports and gaining deeper insights into your data. By leveraging workflows and calculated properties, you can tailor your reporting to meet your unique business needs and track the metrics that matter most.