Quartz 5

clg_sales_dashboard_blueprint

12 min read

📊 CLG HubSpot Sales Dashboard Implementation Blueprint

Modules 1A — 1E (Individual Coach Dashboard)

This blueprint provides the exact data architecture, custom property schemas, and step-by-step Custom Report Builder configurations required to build Modules 1A through 1E on the Conscious Leadership Group (CLG) HubSpot account (5702729) under Dashboard View ID 20696405.

🗺️ 1. Core Data Architecture & Custom Properties

To successfully report on individual coach activity, bookings, and commission-eligible product performance, configure the following custom properties inside Settings > Objects > Deals > Properties:

Property LabelInternal NameField TypeDescription / Usage
Lead Coachlead_5___sc_HubSpot UserThe coach who sourced/generated the lead (10% Lead commission).
Closing Coachclose_15___sc_HubSpot UserThe coach who closed the deal (10% Close commission).
Working CoachworkHubSpot UserThe coach actively delivering the coaching/service (45% Work commission).
Partner Coachpartner__sc_HubSpot UserStandard partner-level margin or oversight pool (Jim / Joyce).
Deal TypedealtypeDropdownOptions: New Business, Renewal, Expansion.

NOTE

Architectural Decision (Commissions on Hold): Deal-level calculated properties for commission amounts are placed ON HOLD. Since commissions are earned at the line-item level (and deals can contain multiple line items with different products and split attributions), CLG is deploying two specialized custom objects in Phase 3 to manage this mathematical complexity. This deferral does not block dashboard completion. All other metrics (deal count, deal amount, activities, pipeline health, and goals) can be fully implemented now using standard CRM objects.

🛠️ 2. HubSpot Custom Report Builder Rulebook (UI Logic)

When using HubSpot’s Custom Report Builder (Reporting > Reports > Create custom report > Custom Report Builder), you must adhere to several strict UI structural constraints:

---
config:
  theme: base
  look: neo
  fontFamily: '"Recursive Variable", sans-serif'
  themeVariables:
    fontFamily: '"Recursive Variable", sans-serif'
  layout: dagre
---
flowchart TB
    StartNode["Start Report Builder"]@{ shape: start }:::Aqua
    ChooseSources["Select Primary & Secondary Data Sources"]:::Rose
    ChooseVisualization["Choose Chart/Metric Visualization"]:::Rose
    CheckKPI{"Is Visualization a KPI Card?"}@{ shape: decision }:::Peach
    CheckMulti{"Has Multiple Values?"}@{ shape: decision }:::Peach
    DisableCompare["Compare By Bucket is DISABLED\n(Flat numbers only)"]:::Sky
    EnableCompare["Compare By Bucket is ENABLED\n(Shows percentage change)"]:::Aqua
    StandardBuckets["Configure X-Axis, Y-Axis, and\nBreak down by Buckets"]:::Rose
    EndNode["Save & Add to Dashboard"]@{ shape: stop }:::Ash

    StartNode --> ChooseSources
    ChooseSources --> ChooseVisualization
    ChooseVisualization --> CheckKPI
    CheckKPI -- Yes --> CheckMulti
    CheckKPI -- No --> StandardBuckets
    CheckMulti -- Yes --> DisableCompare
    CheckMulti -- No --> EnableCompare
    DisableCompare --> EndNode
    EnableCompare --> EndNode
    StandardBuckets --> EndNode

    classDef Rose stroke-width:1px, stroke-dasharray:none, stroke:#FF5978, fill:#FFDFE5, color:#8E2236
    classDef Ash stroke-width:1px, stroke-dasharray:none, stroke:#999999, fill:#EEEEEE, color:#000000
    classDef Sky stroke-width:1px, stroke-dasharray:none, stroke:#374D7C, fill:#E2EBFF, color:#374D7C
    classDef Aqua stroke-width:1px, stroke-dasharray:none, stroke:#46EDC8, fill:#DEFFF8, color:#378E7A
    classDef Peach stroke-width:1px, stroke-dasharray:none, stroke:#FBB35A, fill:#FFEFDB, color:#8F632D

🚨 Critical UI Constraints for KPI (Scorecard) Charts

  1. The Multi-Value Comparison Blocker: In HubSpot, a single KPI card with multiple fields in the Values bucket cannot use the Compare by bucket. If you drag more than one metric into Values, HubSpot automatically locks or removes the date comparison.
  2. The Comparison Solution: To display KPI metrics with comparison percentages (e.g. This Month vs. Last Month), you MUST build each metric as its own individual, single-value KPI report and arrange them adjacent to each other on the HubSpot dashboard.
  3. The Multi-Value Scorecard Layout: If you put multiple metrics into a single KPI widget (such as Quotes: Sent, Accepted, Declined), they will display side-by-side as a row of flat numbers with no temporal comparison.

📌 Module 1A: Sales Meeting & Outreach Activity

This module provides coaches with immediate visibility into their calendar productivity, top-of-funnel form captures, lifetime deal values, and outstanding quotes.

📊 Report 1A.1: Meetings Completed (This Month vs. Last Month)

  • Data Sources: Activities (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None (Ensure this is completely empty)
    • Values: Drag Count of Activities (aggregated as Count). Click pencil icon -> rename to "Meetings Completed"
    • Compare by: Drag Activity date. Click pencil icon -> set Filter period to This month and compare to Previous period.
  • Style Settings:
    • Display Format: Number (Integer)
    • Comparison Display: Show percentage change and absolute change side-by-side.
  • Report Filters (Sidebar):
    • Activity type is Meeting
    • AND Meeting outcome is Completed
  • Dashboard Filter Linkage: Link Activity assigned to to the dynamic viewer filter (Me).

📊 Report 1A.2: Form Submissions (This Month vs. Last Month)

  • Data Sources: Contacts (Primary) + Form submissions (Secondary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Count of Form submissions (aggregated as Count). Rename to "Forms Submitted".
    • Compare by: Drag Form submission date. Click pencil -> set Filter period to This month and compare to Previous period.
  • Style Settings:
    • Display Format: Number (Integer)
  • Report Filters:
    • Form submission date is This Month
  • Dashboard Filter Linkage: Link Contact owner to the dynamic viewer filter (Me).

📊 Report 1A.3: Lifetime Value of Company-Associated Deals

  • Data Sources: Companies (Primary) + Deals (Secondary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount (Deals). Rename to "Associated Deals LTV".
    • Compare by: None (Leave empty as this is a lifetime aggregate metric)
  • Style Settings:
    • Display Format: Currency (USD, rounded to nearest dollar)
  • Report Filters:
    • Deal stage is Closed Won
  • Dashboard Filter Linkage: Link Company owner (or Deal owner) to the dynamic viewer filter (Me).

📊 Report 1A.4: Quotes Lifecycle Metric Grid (Multi-Value flat scorecard)

  • Data Sources: Quotes (Primary) + Deals (Secondary)
  • Visualization: KPI (Scorecard / Multi-Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values:
      1. Drag Count of Quotes -> filter or rename to "Quotes Sent"
      2. Drag Count of Quotes -> rename to "Quotes Accepted" (where Quote Status = Approved)
      3. Drag Count of Quotes -> rename to "Quotes Declined" (where Quote Status = Declined)
      4. Drag Count of Quotes -> rename to "Quotes Waiting Signature" (where Quote Status = Pending Signature)
    • Compare by: None (Disabled by HubSpot due to multi-value configuration)
  • Style Settings:
    • Layout: Row of cards displaying the 4 status columns side-by-side in one dashboard widget.
  • Report Filters:
    • Create date is This Year YTD
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📌 Module 1B: Deals Booked & Closed

This module tracks hard closed-won revenue performance across monthly, quarterly, and annual time horizons.

📊 Report 1B.1: Closed Won Revenue (This Month vs. Last Month)

  • Data Sources: Deals (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount (Deals). Rename to "Revenue Closed Won".
    • Compare by: Drag Close date. Set Filter period to This month and compare to Previous period.
  • Style Settings:
    • Display Format: Currency (USD)
  • Report Filters:
    • Deal stage is Closed Won
  • Dashboard Filter Linkage: Link Closing Coach (close_15___sc_) to the dynamic viewer filter (Me).

📊 Report 1B.2: Closed Won Revenue (This Quarter)

  • Data Sources: Deals (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount. Rename to "Revenue Closed This Quarter".
    • Compare by: None
  • Style Settings:
    • Display Format: Currency (USD)
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Quarter
  • Dashboard Filter Linkage: Link Working Coach (work) to the dynamic viewer filter (Me).

📊 Report 1B.3: Closed Won Revenue (YTD)

  • Data Sources: Deals (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount. Rename to "Revenue Closed YTD".
    • Compare by: None
  • Style Settings:
    • Display Format: Currency (USD)
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Year YTD
  • Dashboard Filter Linkage: Link Working Coach (work) to the dynamic viewer filter (Me).

📊 Report 1B.4: Renewal Revenue Closed (YTD)

  • Data Sources: Deals (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount. Rename to "Renewal Revenue Closed YTD".
    • Compare by: None
  • Style Settings:
    • Display Format: Currency (USD)
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Year YTD
    • AND Deal Type (dealtype) is Renewal
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📊 Report 1B.5: Expansion Revenue Closed (YTD)

  • Data Sources: Deals (Primary)
  • Visualization: KPI (Scorecard / Single Value)
  • Fields & Configuration Buckets:
    • Group by: None
    • Values: Drag Sum of Amount. Rename to "Expansion Revenue Closed YTD".
    • Compare by: None
  • Style Settings:
    • Display Format: Currency (USD)
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Year YTD
    • AND Deal Type (dealtype) is Expansion
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📌 Module 1C: Deals Closed by Product Type & Weekly Scorecard

📊 Report 1C.1: Closed Deals by Product Type (Pivot Table)

  • Data Sources: Deals (Primary) + Line Items (Secondary)
  • Visualization: Pivot Table
  • Fields & Configuration Buckets:
    • Rows: Drag Product Name (Line Items dimension)
    • Columns: None (Keep empty for simple breakdown list)
    • Values:
      1. Drag Count of Deals (aggregated as Count). Rename header to "Deals Closed"
      2. Drag Sum of Line Item Net Price. Rename header to "Product Revenue"
  • Style Settings:
    • Layout: Condensed table grid, totals column enabled.
    • Sorting: Sort by Product Revenue descending.
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Year YTD
  • Dashboard Filter Linkage: Link Working Coach (work) to the dynamic viewer filter (Me).

📊 Report 1C.2: Weekly Cadence Scorecard (This Week vs. Last Week Pivot)

To construct a weekly scorecard tracking multiple distinct metrics, HubSpot’s Custom Report Builder requires a Pivot Table visualization using a date dimension on columns.

  • Data Sources: Deals + Activities + Contacts
  • Visualization: Pivot Table
  • Fields & Configuration Buckets:
    • Rows: Drag the custom metric fields:
      • Count of Contacts (Rename to "New Leads Added")
      • Count of Activities (where outcome is Completed. Rename to "Meetings Completed")
      • Count of Deals (where Stage entered Proposal. Rename to "Proposals Sent")
      • Count of Deals (where Stage entered Closed Won. Rename to "Deals Won")
      • Sum of Amount (Rename to "New Pipeline Revenue Added")
    • Columns: Drag Close date or Create date -> click pencil -> set Group by to Week -> set range to Last 2 Weeks.
    • Values: Drag the respective counts/sums corresponding to the rows.
  • Style Settings:
    • Layout: Clean grid with column boundaries, Grand Totals toggled Off.
  • Report Filters:
    • Create date is Last 2 Weeks
  • Dashboard Filter Linkage: Link Contact owner, Activity assigned to, and Deal owner to the dynamic viewer filter (Me).

📌 Module 1D: Pipeline Health (Stalled Deals & Conversion)

This module surfaces deals requiring urgent remediation and traces the step-by-step health of the sales pipeline.

📊 Report 1D.1: Open Deals with No Activity in 14 Days (Table)

  • Data Sources: Deals (Primary)
  • Visualization: Table
  • Fields & Configuration Buckets:
    • Columns / Values:
      1. Deal Name
      2. Amount
      3. Deal Stage
      4. Days Since Last Activity (Custom calculation)
  • Style Settings:
    • Sorting: Sort by Days Since Last Activity descending.
  • Report Filters:
    • Deal stage is none of Closed Won, Closed Lost
    • AND Days Since Last Activity is greater than 14
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📊 Report 1D.2: Open Deals with No Activity in 30 Days (Table)

  • Data Sources: Deals (Primary)
  • Visualization: Table
  • Fields & Configuration Buckets: Same as Report 1D.1
  • Report Filters:
    • Deal stage is none of Closed Won, Closed Lost
    • AND Days Since Last Activity is greater than 30
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📊 Report 1D.3: Stalled Forecasted Deals (Urgent 7+ Days No Movement)

  • Data Sources: Deals (Primary)
  • Visualization: Table
  • Fields & Configuration Buckets:
    • Columns / Values: Deal Name, Amount, Deal Stage, Days in Stage
  • Report Filters:
    • Deal stage is none of Closed Won, Closed Lost
    • AND Close date is This Month (Deals scheduled to close this month)
    • AND (Days Since Last Activity is greater than 7 OR Days in Stage is greater than 7)
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📊 Report 1D.4: Stage-by-Stage Conversion Funnel

  • Data Sources: Deals (Primary)
  • Visualization: Funnel (Pipeline Conversion)
  • Fields & Configuration Buckets:
    • X-Axis / Rows: Deal Stage
    • Y-Axis / Columns: Count of Deals
  • Style Settings:
    • Funnel Type: Cumulative conversion overlay.
    • Labels: Enable “Show stage-to-stage conversion rate” and “Show total conversion rate”.
  • Report Filters:
    • Pipeline is Coaching Sales Pipeline
    • AND Create date is This Year YTD
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

📌 Module 1E: Goal Progress

This module displays individual progress towards quota targets and deal valuation trends.

📊 Report 1E.1: Road to $150K Closed-Won Revenue YTD (Gauge)

  • Data Sources: Deals (Primary)
  • Visualization: Gauge
  • Fields & Configuration Buckets:
    • Y-Axis (Value): Sum of Amount
    • Target: Set a static custom target value of 150000
  • Style Settings:
    • Gauge Limits: Min: 0, Max: 200000
    • Label: "Revenue Closed YTD vs. $150K Goal"
  • Report Filters:
    • Deal stage is Closed Won
    • AND Close date is This Year YTD
  • Dashboard Filter Linkage: Link Working Coach (work) to the dynamic viewer filter (Me).

📊 Report 1E.2: Road to Annual Quota Goal (Dynamic Quota)

  • Data Sources: Native HubSpot Goals Engine
  • Visualization: Goal Progress Bar (Native Card)
  • Fields & Configuration Buckets:
    • Target: Automatically loads user quota from Settings > Reporting > Goals > Sales Quota
    • Metric: Closed Won Deal Revenue
  • Dashboard Filter Linkage: Automatic (Loads logged-in user’s personalized targets dynamically)

📊 Report 1E.3: Average Deal Size Trend over Time

  • Data Sources: Deals (Primary)
  • Visualization: Line Chart
  • Fields & Configuration Buckets:
    • X-Axis: Close date (Grouped by: Month, Range: This Year YTD)
    • Y-Axis: Average of Amount
    • Break down by: None
  • Style Settings:
    • Line Style: Smooth spline curves with node markers.
    • Title: X-Axis: "Month of Close", Y-Axis: "Average Deal Value ($)"
  • Report Filters:
    • Deal stage is Closed Won
  • Dashboard Filter Linkage: Link Deal owner to the dynamic viewer filter (Me).

👑 3. Pro-Tips for Jim & Joyce (Founder Multi-Role Attribution)

In HubSpot, Jim (James) and Joyce are partners/founders, meaning they receive commissions across all 5 categories (Lead, Work, Close, Network Builder, Partner), whereas other coaches only track their specific Work or Close deals.

The Solution: “Viewer-Based Visibility” via Dashboard Copying

HubSpot does not allow dynamic conditional card visibility on a single dashboard layout. To achieve this elegantly, build:

  1. Individual Coach Sales Dashboard (Standard):
    • Uses dashboard filters where Owner = Me.
    • Standard reports only query Closing Coach (close_15___sc_) and Working Coach (work) properties.
  2. Founder & Partner Revenue Dashboard (Jim & Joyce):
    • Copy the Standard Dashboard and add 5 additional aggregate metric cards:
      • Lead Qty / Amount: Sum of Deals where Lead Coach (lead_5___sc_) is Jim or Joyce.
      • Work Qty / Amount: Sum of Deals where Working Coach (work) is Jim or Joyce.
      • Close Qty / Amount: Sum of Deals where Closing Coach (close_15___sc_) is Jim or Joyce.
      • Network Builder Bonus: Sum of Deals where Lead Coach (lead_5___sc_) `Working Coach` (`work`) Closing Coach (close_15___sc_).
      • Partner Margin: Sum of Deals where Partner Coach (partner__sc_) is Jim or Joyce.
    • Restrict access to this dashboard to Jim and Joyce’s HubSpot Team only.

Graph View

Backlinks