maiven gateway · online··
manifest · 2d ago
Sign in Back to catalog
Definition
Ephemeral join — chains proposals → opportunities → accounts so each proposal carries the deal's `segment` (off the account), `region` and `rfx_type` (off the opportunity), plus `account_id`. Exposes `total_price`, `total_cost`, `margin_pct` ((price−cost)/price·100), `cycle_time_days` (decision_date − submitted_date, Nullable while pending), `outcome`, and the derived `is_won = (outcome = 'won')`. Opportunity + account are LEFT-joined and the borrowed dims coalesce to `'unknown'`, so a proposal with a missing FK still lands (staging's relationships test flags the bad FK). Inlined into `mart_proposal_outcomes`.
18 of 18
Column
Description
Tests · PII
rfx_id
From `stg_gcs__rfx_proposals.rfx_id`. FK to the parent opportunity.
account_id
The opportunity's account. From `stg_gcs__rfx_opportunities.account_id` via LEFT JOIN — Nullable if the opportunity is missing.
segment
The account's sales segment. Chained from `stg_gcs__rfx_accounts.segment` (LEFT JOIN); coalesced to `'unknown'`.
region
The opportunity's region. From `stg_gcs__rfx_opportunities.region` (LEFT JOIN); coalesced to `'unknown'`.
rfx_type
The opportunity's type (RFP / RFQ / RFI). From `stg_gcs__rfx_opportunities.rfx_type` (LEFT JOIN); coalesced to `'unknown'`.
version
Proposal revision number 1..3, UInt8. From staging.
submitted_date
Date the proposal was submitted. From staging.
decision_date
Date the buyer decided. Nullable while pending. From staging.
outcome
`won` / `lost` / `pending` / `withdrawn`. From staging.
2 tests
total_price
Quoted price, Decimal(14,2). From staging.
total_cost
Estimated delivery cost, Decimal(14,2). From staging.
margin_pct
(price − cost) / price · 100, Decimal(6,2). From staging.
eval_score
Buyer evaluation score 0..100, Nullable(Decimal(5,2)) — null while pending. From staging.
competitor
Winning / competing vendor named in the decision. From staging.
lead_author_sme_id
From `stg_gcs__rfx_proposals.lead_author_sme_id`. FK to the lead-author SME (id only — no PII).
cycle_time_days
Derived `decision_date − submitted_date`, Nullable(UInt16) — null while pending. From staging.
is_won
Derived — `outcome = 'won'`. Convenience flag for win-rate rollups so all marts share one definition.