The date dimension
The infrastructure behind Funnel is built on an assumption that each row of data must be attributed to a date. This has worked well with marketing data so far, but it brings some troubles when working with CRM data in Funnel. This is because CRM data often contains multiple types of dates. To fulfill the date attribution requirement, each pulled HubSpot object is attributed with a date value that corresponds to when the object was created in HubSpot (“Create Date” in HubSpot).
This leads to some problems regarding data freshness and it affects how the “Data explorer” should be used when querying CRM-data.
When connecting a new data source in Funnel, the data source will immediately be scheduled to download a rolling interval of data with a given frequency. For marketing data it makes sense to only download the newest data, but with CRM data this is not always the case. An example of this is when pulling HubSpot Contacts data into Funnel. A HubSpot Contact with a “Create Date” from several years ago could have updated properties and be just as important as a contact that was created yesterday. With this is mind, and due to workload limitations, HubSpot connectors are set to pull all objects that have been created within the last 200 days every hour. HubSpot objects older than that will be pulled every 24 hour.
The “Date Picker” in the “Data Explorer” is only applied to the “Date” dimension (same as “Create Date” for HubSpot data). This leads to some difficulties, which are best illustrated with an example:
Assume a data set in Funnel where each Contact has a property called “Meeting Booked Date”. If a contact has signed up for a meeting, this property will have a date set as the value.
Let’s say we want to see how many meetings that were booked in January 2018, the naive solution would be to choose that month in the date picker (and to filter out contacts without “Meeting booked date”) and expect to get the actual number.
In this image, we have selected January 2018 and filtered out contacts that have not booked a meeting. It should be pointed out that this does not mean that 395 contacts have signed up for a meeting. This is because the “Date picker” filtered all contacts that have been created in January 2018 and a contact does not necessarily have a “Meeting booked date” in the vicinity of the “Create Date” (which could be seen in the image below).
In order to answer the question, two steps need to be made. Firstly, the “Date picker” settings have to be adjusted. To make sure that all contacts are included in the filtering later on, it is required that the interval set in the “Date picker” has an interval large enough to include all pulled contacts. The size of this interval varies depending on when the oldest contact was created, however you can always set the from date in the interval to be sometime before the oldest contact, and you're good to go. In this example the interval is from the 1st of January 2010, when the oldest contact was created sometime in 2015.
Secondly, a filter needs to be applied to the “Meeting booked date” dimension. This is because only contacts with “Meeting booked date” in January 2018 should be shown. (Side note about the filter; dates are written in the ISO 8601 standard, meaning yyyy-mm-dd. So to only get the dates in January 2018, they are matched with the string "2018-01", which means we allow for any day within that year and month.)
With these two steps applied, it could be concluded that 405 contacts booked a meeting in January 2018.