HubSpot (legacy)

New contacts by month

1select
2    date_trunc(createdAt, month) as month
3  , count(*) as contacts
4from
5    {{raw.hubspot.contacts}}
6group by
7    month
8order by
9    month

+---------------------+----------+
|        month        | contacts |
+---------------------+----------+
| 2021-01-01 00:00:00 |   150    |
| 2021-02-01 00:00:00 |   200    |
| 2021-03-01 00:00:00 |   175    |
| 2021-04-01 00:00:00 |   225    |
| 2021-05-01 00:00:00 |   250    |
| 2021-06-01 00:00:00 |   300    |
| 2021-07-01 00:00:00 |   275    |
| 2021-08-01 00:00:00 |   350    |
| 2021-09-01 00:00:00 |   400    |
| 2021-10-01 00:00:00 |   375    |
| 2021-11-01 00:00:00 |   450    |
| 2021-12-01 00:00:00 |   500    |
+---------------------+----------+

This SQL model allows you to easily track the number of new contacts added to your HubSpot database each month. By using the date_trunc function, the SQL groups contacts by the month they were created, providing a clear picture of how your contact list is growing over time. This information can be used to identify trends in your lead generation efforts, and to make data-driven decisions about how to allocate your marketing resources. With the New Contacts by Month SQL template, you can gain valuable insights into the effectiveness of your marketing campaigns and make informed decisions about how to optimize your lead generation efforts.