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.
- Grain
- See the notes
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from HubSpot
- dealsowners
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.hubspot.closed_lost_deals_by_owner
2-- Closed lost deals by owner. Retrieves the number of closed lost deals by owner
3-- from HubSpot and orders them by the highest number of deals. It uses a left join
4-- to match the owner ID from the deals table to the owners table and concatenates
5-- the first and last name of the owner for readability.
6--
7-- From the weld.app template library. Not reconciled against HubSpot;
8-- check the output against the source before relying on it.
9
10select
11concat(owners.firstName, ' ', owners.lastName) as deal_owner,
12count(*) as number_of_deals
13from
14{{raw.hubspot.deals}} as deals
15left join {{raw.hubspot.owners}} as owners on cast(owners.id as int) = deals.properties_hubspot_owner_id
16where
17properties_dealstage = 'closedlost'
18group by
19deal_owner
20order by
21number_of_deals descThis 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().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.hubspot.deals
- raw.hubspot.owners
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.