fct_lead_lifetime


Description

One row per customer, tracking their journey from first contact to becoming a member (or being marked as lost). Every customer has a row — including prospects who never signed a contract.

Data comes from two sources (see lead_source): - LEAD_MANAGEMENT: The customer was actively managed in the Lead Management section. Start and end dates come from the lead record. - FIRST_CONTRACT_FALLBACK: The customer was not managed in Lead Management. The customer creation date is used as the start and the first contract date as the end. A customer with no contract will have lead_status = PENDING indefinitely.

Use this table to answer: how many leads converted this month, how long did conversion take, which campaigns drove the most wins, and which leads are currently pending.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
lead_lifetime_id int8 19 null

Primary key of this table. Equals customer_id — one row per customer.
organization_unit_id int8 19 √ null
dim_organization_unit.organization_unit_id fct_lead_lifetime_organization_unit_id_fkey R

The gym (organization_unit) where the lead was initiated at (locally or online).
customer_id int8 19 √ null
dim_customer.customer_id fct_lead_lifetime_customer_id_fkey R

The customer this lead journey belongs to. Every customer has exactly one row.
campaign_id int8 19 √ null
dim_campaign.campaign_id fct_lead_lifetime_campaign_id_fkey R

The campaign the lead was associated with or NULL.
lead_start_date_time timestamp 29,6 √ null

Local timestamp in studio timezone when the lead process started. For FIRST_CONTRACT_FALLBACK rows this is when the customer record was created.
lead_start_date date 13 √ null

Local date in studio timezone when the lead process started. For FIRST_CONTRACT_FALLBACK rows this is the customer creation date.
lead_finish_date_time timestamp 29,6 √ null

Local timestamp in studio timezone when the lead process ended (win or loss). For FIRST_CONTRACT_FALLBACK rows this is when the first contract was created. NULL for leads still in progress.
lead_finish_date date 13 √ null

Local date in studio timezone when the lead process ended. For FIRST_CONTRACT_FALLBACK rows this is the first contract creation date. NULL for leads still in progress.
lead_source varchar 65535 √ null

How this row’s dates are sourced. One of: * LEAD_MANAGEMENT: The customer was actively managed in Lead Management. Dates come from the lead record. * FIRST_CONTRACT_FALLBACK: The customer was not managed in Lead Management. Dates are derived from the customer creation and first contract records.
lead_status varchar 65535 √ null

The final status of the lead process. One of: * PENDING: Lead process is still ongoing (no final status yet). * WIN: Lead process was successful, customer signed a contract. * LOSS: Lead process was not successful, only applicable to LEAD_MANAGEMENT sourced leads.
lead_classification varchar 65535 √ null

The current classification assigned to the lead, such as HOT or COLD. NULL when the lead has not been classified or for fallback rows.
source_campaign varchar 65535 √ null

DEPRECATED! Use campaign_id. The name of a campaign the lead was associated with. Or NONE.
loss_reason varchar 65535 √ null

The reason picked if the lead was set to lead_status=LOSS or UNKNOWN if no specific reason is available.
lead_stage_id int8 19 √ null
dim_lead_stage.lead_stage_id fct_lead_lifetime_lead_stage_id_fkey R

The current stage the lead is in, as configured in the studio’s lead pipeline. NULL for FIRST_CONTRACT_FALLBACK rows, which are not managed on a pipeline board.
first_seen_date timestamptz 35 √ null

UTC timestamp when a staff member first opened/viewed this lead. NULL if never viewed or not applicable.
progress_started_date timestamptz 35 √ null

UTC timestamp when the first meaningful action was taken on this lead (e.g. moved on the pipeline board, contacted). NULL if no action has been taken yet.
last_contact_date timestamptz 35 √ null

UTC timestamp of the most recent contact attempt with this lead. NULL if never contacted.
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