Weld
Start free
HubSpot model

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.

See the SQLAll HubSpot models
Grain
See the notes
Built from
  1. 4
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from HubSpot
contact_dealcontactsdealsowners
Ships as
Weld and dbt SQL, open source on GitHub
core.hubspot.conversion_from_contacts_to_deals_by_owner
1-- core.hubspot.conversion_from_contacts_to_deals_by_owner
2-- Conversion from contacts to deals by owner. Converts contacts to deals by owner
3-- in HubSpot, displaying the owner's name, the number of contacts they have, the
4-- number of deals they have, and the conversion rate between the two. It uses left
5-- joins to connect the necessary tables and groups the results by owner.
6--
7-- Owner is the contact's owner. Each contact and each deal counts once per
8-- owner, however many associations link them; conversion_rate is deals per
9-- 100 contacts.
10--
11-- From the weld.app template library, corrected. Not reconciled against
12-- HubSpot; check the output against the source before relying on it.
13
14select
15concat(owners.firstName, ' ', owners.lastName) as owner
16, count(distinct contacts.id) as contacts
17, count(distinct deals.id) as deals
18, safe_divide(count(distinct deals.id), count(distinct contacts.id)) * 100 as conversion_rate
19from
20{{raw.hubspot.contacts}} as contacts
21left join {{raw.hubspot.contact_deal}} as contact_deal on contact_deal.contact_id = contacts.id
22left join {{raw.hubspot.deals}} as deals on deals.id = contact_deal.deal_id
23left join {{raw.hubspot.owners}} as owners on cast(owners.id as string) = cast(contacts.properties_hubspot_owner_id as string)
24group by
25owner

This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.hubspot.contact_deal
  • raw.hubspot.contacts
  • raw.hubspot.deals
  • raw.hubspot.owners

More HubSpot models

Put HubSpot in front
of your agents.

Connect HubSpot, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us