Overview: Query Builder for HubSpot Marketing (Active Lists)

How to access the Query Builder for HubSpot Marketing (Active Lists)

Navigate to Metrics > Query Builder to access the Query Builder for HubSpot Marketing. Click the green + Create Custom Metric button and select your connected HubSpot Marketing Account from the Data Source drop-down list. Select the Active Lists button.

New Active Lists that were created in your HubSpot Account will not be recognized in Databox until the next day after a larger data sync to identify new Active Lists in the HubSpot Account is performed in the middle of the night.

these steps

How to use the Query Builder for HubSpot Marketing (Active Lists)

Metric: Select an Active List from your connected HubSpot Marketing Data Source

Data Choice: There are two Data Choice options: Total Active List Contacts and Net New Active List Contacts.

Total Active List Contacts: This Data Choice will return the total number of Contacts in the Active List at the end of the selected Date Range. If it is an open Date Range, like "Year to Date," the current total number of Contacts in the Active List will be displayed.

Net New Active List Contacts: This Data Choice will return the difference between the number of Contacts in the Active List on the last day of the selected Date Range and the number of Contacts in the Active List on the first day of the selected Date Range. The exact calculation performed on the backend is as follows:

(Total # of Contacts in the Active List on the Last Day of the Date Range) - (Total # of Contacts in the Active List on the First Day of the Date Range) = Net New Active List Contacts

Selecting Net New Active List Contacts as the Data Choice will reveal a checkbox to Skip contacts already in the active list. By selecting this checkbox, the number of Contacts in the Active List on the day the Active List is created will be subtracted from the total # of Contacts in the Active List. This makes it so a backfill of Contacts will not skew Active List data in Databox.

Custom Query Name: Enter a name for your Custom Metric. This Custom Metric Name will be available in the Designer after saving.

Data Preview: After clicking Run query, a Data Preview of the Custom Metric will be displayed. Daily Metric values are displayed in this section from the Active List Create Date until the current day.

Best Practices when setting up your Active Lists to track Net New Active List Contacts

Daily data tracking the number of Contacts in the Active List is accessible from HubSpot's API. Specific Contact Properties associated with the Contacts in the Active List are not accessible from HubSpot's API. Therefore, data is not accessible from before the Active List's Create Date, even if a Contact would have qualified for the List at an earlier date.

Because of this, you may want to consider creating Active List that only have Contacts added to them, and setting a start date for when Contacts qualify for the Active List.

IN THIS SECTION

How to create Active Lists to track New Contacts that meet specified Active List Filters

Net New Active List Contacts are calculated by performing the following backend calculation:

(# of Contacts in the Active List on the Last Day of the Date Range) - (# of Contacts in the Active List on the First Day of the Date Range) = Net New Active List Contacts in the Date Range

Because of this, if you want to track New Contacts that are added to the Active List rather than Net New Contacts that are added or removed from the Active List, you'll want to ensure that your Active List Filters do not remove Contacts from the Active List. This will make it so Contacts only enter the Active List and Net New Active List Contact Values are not skewed.

How to create Active Lists with an open Start Date to qualify for the Active List [Example]

Let's say you create an Active List to track CTA Clicks to a specific CTA on May 7th and 150 Contacts were added to the Active List. HubSpot is only sending Databox the number of Contacts in the Active List, not any specific Contact Properties associated with the Contacts in the Active List. So, all 150 Contacts that entered the Active List on May 7th are considered New Active List Contacts (or CTA Clicks) on May 7th.

In Databox, you create a Custom Metric on May 8th and select Net New Active List Contacts as the Data Choice.

On May 31st, there are 1000 Contacts in the Active List.

If the Skip contacts already in the active list checkbox is not selected, the following equation will be performed on the backend for Month to Date:

1000 (# of Contacts in the Active List on May 31st) - 0 (# of Contacts in the Active List on May 1st)= 1000 (Net New Active List Contacts in May)
If the Skip contacts already in the active list checkbox is selected, the following equation will be performed on the backend for Month to Date:

850 (# of Contacts in the Active List on May 31st [1000] - # of Contacts in the Active List on the day the Active List was created [150]) - 0 (# of Contacts in the Active List on May 1st)= 850 (Net New Active List Contacts in May)

Since this is a rolling list, no changes need to be made moving forward. In June, the number of Net New Contacts in the Active List (or Contacts who clicked the CTA) will not be skewed, because there weren't any old Contacts that were added to the Active List during this time. However, the "Year to Date" Value for CTA Clicks will not be accurate until the following year, because the Active List is not set to include Contacts who clicked the CTA throughout the full year.

How to create Active Lists with an set Start Date to qualify for the Active List [Example]

Let's say you create an Active List to track CTA Clicks to a specific CTA on May 7th and you are primarily interested in Monthly reporting. Therefore, you add a condition to the Active List so only Contacts who clicked on the CTA on May 1st and on are added to the Active List. Because of this criteria, 25 Contacts are added to the Active List on May 7th to account for all CTA Clicks in May.

In Databox, you create a Custom Metric on May 8th and select Net New Active List Contacts as the Data Choice.

On May 31st, there are 1000 Contacts in the Active List. You will not want to select the Skip contacts already in the active list checkbox, because all Contacts added to the Active List were intentionally added. The number of Net New Active List Contacts will be accurately calculated in Databox for May and all future Date Ranges. However, the "Year to Date" Value for CTA Clicks will not be accurate until the following year, because the Active List is not set to include Contacts who clicked the CTA throughout the full year.

How to verify a Custom Active List Metric in HubSpot vs Databox [Example]

Let's walk through an example to verify Net New Active List Members for an Active List that is tracking Contacts who sign up for our AM Demo. We're interested in viewing data for This Month.

Within Databox, the Custom Metric is set up as follows:

To verify this Custom Metric in HubSpot, first log into your HubSpot portal and navigate to Contacts > Lists.

Find the Active List you're pulling into Databox, and click More > View list performance. This will open the corresponding Active List Performance data.

You'll now see a graph called List size over time. First, check the selected Date Range to ensure it matches what you're viewing in Databox.

When you hover over the graph, you can see the daily # of Contacts in the Active List. This is what we're accessing and pulling into Databox from HubSpot's API.

To calculate Net New Active List Members, simply subtract the # of Contacts in the Active List on the 1st day of the Month from the # of Contacts in the Active List on the last day of the month.

For our example, we would do 4,824 - 4,787 = 37 Net New Active List Members for Last Month. This matches what we see within Databox for This Month, so we were successfully be able to verify the number of Net New Active List Contacts in HubSpot and Databox.

How to add a Custom Active List Metric to a Databoard

Open the selected Databoard in the Databox Designer, or create a new Databoard.

Drag and drop a blank Datablock onto the Databoard from the Visualization Library.

Select the appropriate HubSpot Marketing Data Source from the righthand Datablock Settings panel. This must be the same HubSpot Marketing Account that was selected when creating the Custom Metric.

Select the Custom tab at the top of the Metric drop-down list.

Select the Custom Metric name to populate the Datablock.

Additional Information

No data is accessible from before the Contact List Creation Date
New Active Lists that were created in your HubSpot Account will not be recognized in Databox until the next day after a larger data sync to identify new Active Lists in the HubSpot Account is performed in the middle of the night.