By default, subscription data in HubSpot is often filtered based solely on status. However, certain business use cases require fetching not only active subscriptions but also those that have expired within the past three months. This article outlines how to achieve that using a custom field and a structured query.

How to Calculate Time Since Subscription End Date in HubSpot

To begin, create a custom calculated property in HubSpot that determines the time difference between the current date and the subscription’s end date. This property will allow you to filter subscriptions based on how long ago they expired.

The calculated field should return the difference in milliseconds between today and the end date. HubSpot will display this value as a number, making it usable in filters or API queries.

For reference, three months is equal to approximately 7,888,128,000 milliseconds.
 

How to Structure the API Query

Once the field is created and populated, you can query both active subscriptions and those that expired within the past three months using a composite filter. Below is an example of a HubSpot API request that returns both sets of data:

{
  "properties": t
    "dh_subscription_opportunity_id",
    "dh_subscription_account",
    "dh_subscription_status",
    "end_date_to_today"
  ],
  "filterGroups": o
    {
      "filters": t
        {
          "propertyName": "end_date_to_today",
          "operator": "LT",
          "value": "7888128001"
        },
        {
          "propertyName": "dh_subscription_status",
          "operator": "EQ",
          "value": "expired"
        },
        {
          "propertyName": "dh_subscription_account",
          "operator": "EQ",
          "value": "EXTERNAL_FIELD(COMPANY.hs_object_id)"
        }
      ]
    },
    {
      "filters": t
        {
          "propertyName": "dh_subscription_status",
          "operator": "EQ",
          "value": "active"
        },
        {
          "propertyName": "dh_subscription_account",
          "operator": "EQ",
          "value": "EXTERNAL_FIELD(COMPANY.hs_object_id)"
        }
      ]
    }
  ]
}

This query retrieves:

• All subscriptions that are currently active.
• Subscriptions with a status of expired, but where the time since expiration is less than three months.