Using Formula Fields in HubSpot Custom Reports to group contacts into buckets based on how many Marketing emails they’ve opened.

This is an example of using Formula Fields (currently a Public Beta) in Custom Reports

Here’s the video walkthrough:

There’s other ways to do this (eg using calculated properties, or Operations Hub Enterprise functions) – this example doesn’t use either of those (ie no need for Operations Hub for this particular example).

The key is to use the mod operator (ie %) in the formula field – here’s the formula from this particular example:

([CONTACT.hs_email_open] – ([CONTACT.hs_email_open] % 10))/10

Notes:

– Limitation: the buckets are all the same size (ie ranges of 10 in the example), they can’t be mixed sizes eg 1-9, 10-49, 50-250, etc

– Limitation: the label on the report axis is just 0, 1, 2, 3, etc – would be nice if they could be tailored

– The mod operator can be used with any number (doesn’t have to be 10), just replace it in both places in the formula