Closed lost deals by owner
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+------------------+----------------+
| 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.
