Mastering Relative Date Reporting in HubSpot: Advanced Techniques for Future-Proof Insights
HubSpot's reporting tools are incredibly powerful, offering a wide range of options for analyzing your data. However, sometimes you need to go beyond the standard features to slice and dice information in specific ways. This is especially true when dealing with relative dates and trying to project future trends based on current data.
The Challenge: Reporting on Future Sales Leads
Imagine you want to track sales leads that are likely to enroll several months into the future. For example, on January 1st, you want a report that shows leads expected to convert between May 1st and June 30th. Then, on February 1st, the report should automatically update to show leads from June 1st to July 31st, and so on. This kind of dynamic, month-relative reporting isn't directly supported by HubSpot's standard filters, which primarily offer relative tracking on a daily, monthly, quarterly, or yearly basis. One HubSpot Community member encountered this very challenge and sought a solution.
The initial idea was to use calculated properties to assign a numerical value to each month and then use further calculations to filter the data. While this approach is feasible, it can become complex and difficult to maintain. So, what are the alternative, more efficient methods?
Solutions from the HubSpot Community: Thinking Outside the Box
The HubSpot Community is a treasure trove of innovative solutions, and this challenge was no exception. Several members offered helpful suggestions, providing different approaches to tackle the problem of relative date reporting.
1. Workflow Automation: A Simple and Reliable Approach
One community member suggested using a workflow that triggers 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 can then be used as report date filters. This method offers a reliable way to manage date ranges without the need for complex calculations within calculated properties.
How to implement this solution:
- Create two single-line text properties: "Report Window Start" and "Report Window End."
- Create a workflow that triggers on the 1st of each month.
- In the workflow, use the "Set property value" action to update "Report Window Start" and "Report Window End" with the appropriate dates using date math. For example, you can use the
addMonths()function (if available in your HubSpot instance, or a similar function depending on your workflow setup) to add the required number of months to the current date. - Use these properties as filters in your reports.
2. Leveraging the Custom Report Builder
Another option involves using the custom report builder with relative date filters. You can set one filter to "is after" a rolling date and another filter to "is before" another rolling date. While this approach might not be as precise as defining exact month boundaries, it can provide a close approximation and is often sufficient for many use cases.
Benefits of using the Custom Report Builder:
- No need for complex calculated properties.
- Easy to set up and maintain.
- Provides a visual interface for creating reports.
3. Calculated Properties with Date Functions
While initially deemed complex, calculated properties can still play a role. You can create a calculated property that outputs the month and year based on a date property (e.g., close date). This can be useful for display purposes in your reports. For filtering, you can create another calculated property that adds or subtracts a specific number of months (converted to milliseconds) from the date and then checks if that "fake" date falls within the current or next month.
Example of a Calculated Property (for display purposes):
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"))))))))))))
Note: This code snippet is provided as an example and may require adjustments based on your specific HubSpot setup and property names.
Choosing the Right Approach
The best approach for relative date reporting in HubSpot depends on your specific needs and technical expertise. If you prefer a simple and reliable solution, workflow automation is a great option. If you need more flexibility and control, calculated properties might be a better choice. And if you're looking for a quick and easy way to get a close approximation, the custom report builder is a solid option.
By understanding these different techniques, you can unlock the full potential of HubSpot's reporting tools and gain valuable insights into your sales pipeline and future performance. Don't be afraid to experiment and find the solution that works best for you!
For those also running storefronts, remember that integrating your e-commerce platform with HubSpot can unlock even more powerful reporting capabilities. For example, understanding how your shopify email automations contribute to lead generation and future sales can provide invaluable insights.