This page documents two related Funnel-tab features of the FSP Marketing Dashboard (Rails app):
See also the shared Funnel Conventions page for the funnel view model (Total / By Region / By Source, cross-filters, by_month / totals / eom bucket shape).
<aside> ⚠️
Amounts flow in raw dollars. Revenue = income accounts, Credit − Debit. Costs = Debit − Credit. The dash never re-scales them.
</aside>
flowchart TD
NS["NetSuite (SuiteQL): income 40%/44%, labor, COGS"] --> LOADER["fsp-bi-hub loader<br>netsuite_monthly_revenue_costs_by_region_loader.py"]
LOADER --> TBL["fsp-sql table<br>netsuite_monthly_revenue_costs_by_region<br>(1 row per posting_month × region)"]
TBL --> MODEL["NetsuiteRevenueCost (FspSqlBase)"]
MODEL --> CALC["Funnel::RevenueCostsCalculator.inject!"]
CALC --> PAYLOAD["Funnel payload<br>group[:by_month]/[:totals]/[:eom] += rc_* keys"]
PAYLOAD --> UI["FunnelTable.jsx (Revenue & Costs + % of Revenue rows)"]
CSV["Forecaster fills CSV template"] --> IMPORT["POST /api/v1/forecasts<br>Funnel::ForecastImport"]
IMPORT --> FTBL["primary DB<br>forecasts table"]
FTBL --> INJ["Funnel::ForecastInjector.inject!"]
INJ --> PAYLOAD2["group[:forecast] = { 'YYYY-MM' => { fc_leads, fc_spend } }"]
PAYLOAD2 --> UI2["FunnelTable.jsx Forecast column"]
Two distinct data planes:
NetsuiteRevenueCost.AccessGrant) — they never join in SQL to the fsp-sql funnel queries; the funnel reads them in Ruby (app/models/forecast.rb:6-10).Both are folded into the already-computed funnel payload in Ruby, keyed by the funnel's by_month / totals / eom structure, then all the derived columns (Sales, Call Center, Contribution Margin, every % of Revenue, Cost per Lead) are computed client-side in constants.js.
| Row (UI label) | Bucket key | Shown for | Source / formula |
|---|---|---|---|
| Revenue ($) | rc_revenue | overview, solar, generators | NetSuite income. Overview = rev_solar_storage + rev_generator; Solar = rev_solar_storage; Generators = rev_generator |
| Lead Spend ($) | rc_lead_spend | overview | Derived = the funnel's own spend bucket (marketing_ad_spend) |
| Sales ($) | rc_sales | overview | Derived client-side = rc_revenue × 0.09 |
| Call Center ($) | rc_call_center | overview | Derived client-side = rc_revenue × 0.03 |
| Installers ($) | rc_installers | overview | NetSuite dept-1 labor |
| Field ($) | rc_field | overview | NetSuite dept-2 labor (field_labor) |
| Operations ($) | rc_operations | overview | NetSuite dept 3/8/NULL labor |
| Material & Eng ($) | rc_material_eng | overview | NetSuite COGS (materials) |
| Est. Contribution Margin ($) | rc_contribution_margin | overview | Derived = Revenue − Installers − Field − Operations − Lead Spend − Material&Eng − Sales − Call Center |
| • (% of Rev) | rc_*_pct | overview | Derived = each line ÷ rc_revenue |
| New Leads (Forecast col) | fc_leads | overview | Sum of Forecast.leads across in-scope cells |
| Lead Spend (Forecast col) | fc_spend | overview | Sum of Forecast.leads × cost_per_lead |
| Cost per Lead (Forecast col) | — | overview | Derived = fc_spend ÷ fc_leads |
netsuite_monthly_revenue_costs_by_region lives on fsp-sql, materialized by the fsp-bi-hub loader postgres_loaders/netsuite_monthly_revenue_costs_by_region_loader.py. One row per (posting_month, region). It reproduces the three F2P Overview feeders EXACTLY (same accounts / classes / periods / department + item maps): f2p_2026/netsuite_revenue.py, netsuite_labor.py, netsuite_materials.py.
Schema (fsp-bi-hub/db/schemas.py:2993):