Hubspot
New contacts by month
Retrieves the number of new contacts added to HubSpot each month and groups them by month. It uses the date_trunc function to extract the month from the createdAt field and orders the results 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 monthExample of output from model:
+---------------------+----------+
| 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.
