Hubspot
Conversion from contacts to deals by owner
Converts contacts to deals by owner in HubSpot, displaying the owner's name, the number of contacts they have, the number of deals they have, and the conversion rate between the two. It uses left joins to connect the necessary tables and groups the results by owner.
1select
2 concat(owners.firstName, ' ', owners.lastName) as owner
3 , count(contacts.id) as contacts
4 , count(deals.id) as deals
5 , count(deals.id) / count(contacts.id) * 100 as conversion_rate
6from
7 {{raw.hubspot.contacts}} as contacts
8 left join {{raw.hubspot.contact_deal}} as contact_deal on contact_deal.contact_id = contacts.id
9 left join {{raw.hubspot.deals}} as deals on deals.id = contact_deal.deal_id
10 left join {{raw.hubspot.owners}} as owners on owners.id = contacts.properties_hubspot_owner_id
11group by
12 ownerExample of output from model:
+------------------+----------+-------+----------------+
| OWNER | CONTACTS | DEALS | CONVERSION_RATE |
+------------------+----------+-------+----------------+
| John Smith | 1000 | 200 | 20 |
| Jane Doe | 500 | 100 | 20 |
| Michael Johnson | 750 | 150 | 20 |
| Sarah Williams | 250 | 50 | 20 |
+------------------+----------+-------+----------------+This SQL template allows you to analyze the conversion rate of contacts to deals based on the owner of the contact. By integrating with HubSpot, this SQL pulls data from the contacts, contact_deal, deals, and owners tables to calculate the number of contacts and deals associated with each owner, as well as the conversion rate. The output of this SQL provides valuable insights into which owners are most successful at converting contacts to deals, and can help identify areas for improvement in your sales process. With this information, you can optimize your sales strategy and increase your overall conversion rate.