value / 1000ĬASE WHEN 'key' = 'hs_lifecyclestage_marketingqualifiedlead_date' THEN 'day', 'createdate', 'hs_lifecyclestage_marketingqualifiedlead_date')ĮLSE 'Days 28+' END AS bins_days_diff, COUNT( distinct hubspot_contacts_id)ĬASE WHEN 'key' = 'createdate' THEN timestamp 'epoch' + p. 'day', 'createdate', 'hs_lifecyclestage_marketingqualifiedlead_date') = 0 The nitty gritty of nested queries is beyond the scope of this post, but you can learn more about them here. This is how we can see the distribution of the amount of time it takes for contacts to become marketing qualified leads. For each date_diff value (in days), the results will display a count of observations (rows) with distinct hubspot_contacts_id (contacts) from the nested table. This query (see Hubspot Lead Analytics in SQL, Beyond CRM Reporting to see how and why we came up with the nested part) will give us the difference between “createdate” and “hs_lifecyclestage_marketingqualifiedlead_date” and also aggregates the data by the difference between these dates. Put it all together and GROUP BY date_diff

This part of the query subtracts “createdate” from “hs_lifecyclestage_marketingqualifiedlead_date” and provides the difference in unit 'day'. We can use the column names “createdate” and “hs_lifecyclestage_marketingqualifiedlead_date” because the values are formatted as dates and contain the date part 'day'. SELECT DATEDIFF( 'day', “createdate”, “hs_lifecyclestage_marketingqualifiedlead_date”)

DATEDIFF to calculate the difference between createdate and MQL conversion date Return TypeĭATEDIFF returns an integer (BIGINT) data type. In other words, both date/time expressions must have an hour value if you are to put 'hour' as the datepart. Date/Timestampīoth date or time expressions must contain the specified datepart in the first argument. The difference between Decemand Januis 1 year, if ‘year’ is the datepart. If the datepart is ‘year’, it will only look at year and will ignore months and days. You can find a more comprehensive list of dateparts here.Īn important thing to note here is that DATEDIFF pays attention to datepart boundaries. The datepart is the part of the date or time expression that the function operates on (for example, year, month, day or hour). If the first value is more recent than the second (larger), the result will be negative. So if the first argument is prior to the second, you’ll get a positive value. The function subtracts the first date/time value from the second date/time value. DATEDIFF: All the things you need to know SyntaxĭATEDIFF ( datepart, ) ArgumentsĭATEDIFF takes 3 arguments: a datepart, and 2 date or time expressions. For this post we will use data from: Hubspot Lead Analytics in SQL, Beyond CRM Reporting and will use DATEDIFF to answer the question: How much time does it take for a “contact” to convert to a “marketing qualified lead” (MQL)? To take this one step further, we’ll ask, what is the distribution of the amount of time it takes for a “contact” to become a “MQL”? We’ll answer this question using a SQL query and use the resulting dataset to create a quick visualization that will clearly communicate our findings. DATEDIFF is a super useful function that returns the difference between date parts (or dateparts) of two date or time expressions.

Metabase histogram