Weld logo
hubspot logo
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    owner
Example 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.

Ready to start modeling your own hubspot data?

Get started building your data warehouse with hubspot and connect all your apps and databases.

hubspot logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.