Weld logo
hubspot logo
Hubspot

Closed lost deals by owner

Retrieves the number of closed lost deals by owner from HubSpot and orders them by the highest number of deals. It uses a left join to match the owner ID from the deals table to the owners table and concatenates the first and last name of the owner for readability.
1select
2    concat(owners.firstName, ' ', owners.lastName) as deal_owner
3  , count(*) as number_of_deals
4from
5    {{raw.hubspot.deals}} as deals
6    left join {{raw.hubspot.owners}} as owners on cast(owners.id as int) = deals.properties_hubspot_owner_id
7where
8    properties_dealstage = 'closedlost'
9group by
10    deal_owner
11order by
12    number_of_deals desc
Example of output from model:
+------------------+----------------+
| deal_owner       | number_of_deals|
+------------------+----------------+
| John Smith       | 10             |
| Jane Doe         | 8              |
| Michael Johnson  | 5              |
| Sarah Williams   | 3              |
| David Lee        | 2              |
+------------------+----------------+

This SQL model allows you to quickly and easily see which sales representatives are losing the most deals, and how many deals they are losing. By joining the HubSpot deals and owners tables, this SQL template creates a list of all closed lost deals, grouped by the sales representative who was responsible for the deal. This information can be used to identify areas where sales representatives may need additional training or support, or to identify patterns in the types of deals that are being lost. The output of this SQL template is a table that shows the name of the sales representative and the number of closed lost deals they have had, sorted in descending order by the number of deals. This SQL template is a valuable tool for any business that wants to improve their sales performance and increase their revenue.

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.