fct_lead_stage_history


Description

The chronological history of lead stages assigned to leads, including when each stage started and ended. One row per stage assignment. Sourced from the ERP table lead_phase_journal_entry.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
lead_stage_history_id int8 19 null
fct_lead_stage_history.previous_lead_stage_history_id fct_lead_stage_history_previous_lead_stage_history_id_fkey R

The primary key of this lead stage history entry.
customer_id int8 19 √ null
dim_customer.customer_id fct_lead_stage_history_customer_id_fkey R

The customer associated with the lead that occupied the stage.
organization_unit_id int8 19 √ null
dim_organization_unit.organization_unit_id fct_lead_stage_history_organization_unit_id_fkey R

The studio associated with the lead.
lead_stage_id int8 19 √ null
dim_lead_stage.lead_stage_id fct_lead_stage_history_lead_stage_id_fkey R

The lead stage represented by this history entry.
status varchar 65535 √ null

The lead status while this stage entry was active.
stage_start_date_time timestamptz 35 √ null

UTC timestamp when the lead entered this stage, as recorded by the ERP.
stage_end_date_time timestamptz 35 √ null

UTC timestamp when the lead entered its next stage, as recorded by the ERP. NULL indicates the lead is currently in this stage.
previous_lead_stage_history_id int8 19 √ null
fct_lead_stage_history.lead_stage_history_id fct_lead_stage_history_previous_lead_stage_history_id_fkey R

The preceding stage history entry for this lead, when available.
loss_reason_id int8 19 √ null
dim_loss_reason.loss_reason_id fct_lead_stage_history_loss_reason_id_fkey R

The loss reason associated with this stage, when available.
won_date_time timestamptz 35 √ null

UTC timestamp when the lead was marked as won, as recorded by the ERP. NULL if the lead was not won during this stage.
lost_date_time timestamptz 35 √ null

UTC timestamp when the lead was marked as lost, as recorded by the ERP. NULL if the lead was not lost during this stage.
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags shortly behind source system).
data_landing_time timestamptz 35 getdate()

System column. UTC timestamp assigned when a table batch is applied on Redshift. It represents warehouse landing order at table-batch granularity, not source event order. Usable to filter for incremental extractions.

Relationships

Close relationships within degrees of separation