Weld logo

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.
2    concat(owners.firstName, ' ', owners.lastName) as deal_owner
3  , count(*) as number_of_deals
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
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 100+ more apps and databases available.

Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync your data in minutes with Weld. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
© 2024 Weld. All rights reserved.