This page documents every KPI on the Set to Close tab of the FSP Marketing Dashboard, a Rails app that reads HubSpot data synced to Postgres. Unlike most tabs, Set to Close does not compute its KPIs in SQL aggregates. Instead, the controller pulls one row per set appointment, classifies each row in Ruby, and ships a compact [row-of-indices] matrix plus a meta lookup dictionary to the frontend, which then rolls the rows up into every card, chart, and breakdown table. This page walks through that row-building approach and defines each derived metric.

See the shared Conventions page for column definitions, timezone rules, and the F2P filter parity used across the dashboard.

Primary source: app/controllers/api/v1/set_to_close_controller.rb:29 (calculate_metrics). Scopes: app/models/hubspot_deal.rb:21.

<aside> ⚠️

Timezone divergence. The Set to Close tab buckets on the raw hubspot_deals.appointment_date (both in the BETWEEN filter at set_to_close_controller.rb:39 and in the Ruby strftime("%Y-%m") at :82). It does not use the Central-time APPT_DATE_CT conversion (hubspot_deal.rb:3, ReportingTimeZone) that other tabs use. Evening-Central appointments near a month boundary can land in the next reporting month here. Same applies to closedate bucketing for closes at :76.

</aside>

Data flow & caching

The response shape (set_to_close_controller.rb:105):

{
  rows: [ [month_idx, region_idx, source_idx, content_idx, term_idx, interest_idx,
           is_set, is_close, is_storage_only, is_run, is_past], ... ],
  meta: { months:, regions:, sources:, contents:, terms:, interests: },
  cache_timestamp: Time.current
}

Each rows entry is one set appointment. The first six slots are integer indices into the corresponding meta arrays (dictionary compression — string values are sent once in meta, not repeated per row). The last five slots are 0/1 flags the frontend sums to build every KPI. Because the payload is index-encoded, all KPIs below are computed client-side by filtering/summing rows; the controller only emits the raw classified matrix.

• The two-pass build (why the query runs twice)

The base query

Every Set to Close row starts from this join (set_to_close_controller.rb:36):

sets_query = HubspotDeal
  .set_appointments_base
  .joins("INNER JOIN hubspot_contacts ON hubspot_deals.contact_id = hubspot_contacts.hs_object_id")
  .where("hubspot_deals.appointment_date BETWEEN ? AND ?", start_date, end_date)
  .where("hubspot_contacts.cac_lead_source IN (?)", allowed_sources)
  .select(
    "hubspot_deals.hs_object_id AS deal_id",
    "hubspot_deals.appointment_date",
    "hubspot_deals.closedate",
    "hubspot_deals.closed_by_proposal_tool",
    "hubspot_deals.run_status",
    "hubspot_deals.interested_in AS deal_interested_in",
    "hubspot_deals.market_region",
    "hubspot_contacts.cac_lead_source",
    "hubspot_contacts.utm_content",
    "hubspot_contacts.utm_term"
  )

set_appointments_base (hubspot_deal.rb:21) chains three scopes:

scope :new_business,   -> { where(dealtype: "newbusiness") }
scope :non_escalation, -> { where.not(deal_lead_source: "Escalation Add On") }
scope :warm_transfer_valid, -> {
  where("appointment_type != 'Warm Transfer' OR " \
        "(appointment_type = 'Warm Transfer' AND closed_by_proposal_tool = 'Yes')")
}
scope :set_appointments_base, -> { new_business.non_escalation.warm_transfer_valid }

So the effective SQL for the base set-appointment population is:

SELECT hubspot_deals.hs_object_id AS deal_id,
       hubspot_deals.appointment_date,
       hubspot_deals.closedate,
       hubspot_deals.closed_by_proposal_tool,
       hubspot_deals.run_status,
       hubspot_deals.interested_in AS deal_interested_in,
       hubspot_deals.market_region,
       hubspot_contacts.cac_lead_source,
       hubspot_contacts.utm_content,
       hubspot_contacts.utm_term
FROM hubspot_deals
INNER JOIN hubspot_contacts
  ON hubspot_deals.contact_id = hubspot_contacts.hs_object_id
WHERE hubspot_deals.dealtype = 'newbusiness'
  AND hubspot_deals.deal_lead_source IS DISTINCT FROM 'Escalation Add On'
  AND ( hubspot_deals.appointment_type != 'Warm Transfer'
        OR ( hubspot_deals.appointment_type = 'Warm Transfer'
             AND hubspot_deals.closed_by_proposal_tool = 'Yes') )
  AND hubspot_deals.appointment_date BETWEEN '2026-01-01' AND '<end_of_this_month>'
  AND hubspot_contacts.cac_lead_source IN ('Paid Click Advertising', 'Paid Social Advertising');

<aside> 🔑

Scope of this tab. Set to Close is a paid-media view: the cac_lead_source IN ('Paid Click Advertising','Paid Social Advertising') filter (allowed_sources, :130) restricts every KPI to paid-click and paid-social contacts. Organic/other sources are excluded entirely.

</aside>