Overview: Query Builder for HubSpot Marketing Smart Lists

The Query Builder for HubSpot Marketing Smart Lists was built to improve our ability to report on Smart List data in Databox. 

IN THIS ARTICLE

How to access the Query Builder for HubSpot Marketing Smart Lists

Navigate to Data Manager > 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 Contact Lists button. 

New Smart 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 Smart Lists in the HubSpot Account is performed in the middle of the night. 

    Query Builder is available in Plus and Business Accounts. Agency Basic Accounts have access to Query Builder in the Agency Account only.  Request a trial of Query Builder by following these steps  

How to use the Query Builder for HubSpot Marketing Smart Lists

A
  Custom Query Name: Enter a name for your Custom Metric. This Custom Metric Name will be available in the Designer after saving.
B
  Metric: Select a Contact List from your connected HubSpot Marketing Data Source
C
  Data Choice: There are two Data Choice options: Total Smart List Contacts and Net New Smart List Contacts. 
Total Smart List Contacts: This Data Choice will return the total number of Contacts in the Smart 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 Smart List will be displayed. 
Net New Smart List Contacts: This Data Choice will return the difference between the number of Contacts in the Smart List on the last day of the selected Date Range and the number of Contacts in the Smart 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 Smart List on the Last Day of the Date Range) - (Total # of Contacts in the Smart List on the First Day of the Date Range) = Net New Smart List Contacts
Selecting Net New Smart List Contacts as the Data Choice will reveal a checkbox to Skip contacts already in the smart list. By selecting this checkbox, the number of Contacts in the Smart List on the day the Smart List is created will be subtracted from the total # of Contacts in the Smart List. This makes it so a backfill of Contacts will not skew Smart List data in Databox.
F
    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 Smart List Create Date until the current day. 

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

Daily data tracking the number of Contacts in the Smart List is accessible from HubSpot's API. Specific Contact Properties associated with the Contacts in the Smart List are not accessible from HubSpot's API. Therefore, data is not accessible from before the Smart 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 Smart List that only have Contacts added to them, and setting a start date for when Contacts qualify for the Smart List. 

IN THIS SECTION

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

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

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

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

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

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

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

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

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

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

    850 (# of Contacts in the Smart List on May 31st [1000] - # of Contacts in the Smart List on the day the Smart List was created [150]) - 0 (# of Contacts in the Smart List on May 1st)= 850 (Net New Smart 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 Smart List (or Contacts who clicked the CTA) will not be skewed, because there weren't any old Contacts that were added to the Smart List during this time. However, the "Year to Date" Value for CTA Clicks will not be accurate until the following year, because the Smart List is not set to include Contacts who clicked the CTA throughout the full year. 

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

Let's say you create a Smart 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 Smart List so only Contacts who clicked on the CTA on May 1st and on are added to the Smart List. Because of this criteria, 25 Contacts are added to the Smart 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 Smart List Contacts as the Data Choice

On May 31st, there are 1000 Contacts in the Smart List. You will want not want to select the  Skip contacts already in the smart list checkbox, because all Contacts added to the Smart List were intentionally added. The number of Net New Smart 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 Smart List is not set to include Contacts who clicked the CTA throughout the full year. 

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

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

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

2
 To verify this Custom Metric in HubSpot, first log into your HubSpot portal and navigate to Contacts > Lists
3
 Find the Smart List you're pulling into Databox, and click More > View list performance. This will open the corresponding Smart List Performance data. 
4
 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. 
5
 When you hover over the graph, you can see the daily # of Contacts in the Smart List. This is what we're accessing and pulling into Databox from HubSpot's API. 

6
 To calculate Net New Smart List Members, simply subtract the # of Contacts in the Smart List on the 1st day of the Month from the # of Contacts in the Smart List on the last day of the month. 
For our example, we would do 4,824 - 4,787 = 37 Net New Smart 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 Smart List Contacts in HubSpot and Databox. 

How to add a Custom Smart List Metric to a Databoard

1
 Open the selected Databoard in the Databox Designer, or create a new Databoard.  
2
 Drag and drop a blank Datablock onto the Databoard from the Visualization Library. 
3
 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.   
4
 Select the Custom tab at the top of the Metric drop-down list. 
5
 Select the Custom Metric name to populate the Datablock.

Additional Information

  • No data is accessible from before the Contact List Creation Date
  • New Smart 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 Smart Lists in the HubSpot Account is performed in the middle of the night. 
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us