fct_lead_lifetime


Description

Representation of the lead conversion process of every customer (PROSPECT/MEMBER/FORMER_MEMBER), by the start and end date of the conversion/loss. Sourced by both Lead Management section and a fallback logic (see lead_source).

Columns

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

The primary key of this table. System column calculated by the source System.
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 person related to the lead process.
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 timestamptz 35 √ null

The local timestamp on which the lead process was started or for lead_source=FIRST_CONTRACT_FALLBACK: The local timestamp on which customer was added into the system.
lead_start_date date 13 √ null

The date on which the lead process was started or for lead_source=FIRST_CONTRACT_FALLBACK: The date on which customer was added into the system.
lead_finish_date_time timestamptz 35 √ null

The local timestamp on which the lead process was finished or for lead_source=FIRST_CONTRACT_FALLBACK: The first contract of the customer was created on.
lead_finish_date date 13 √ null

The date on which the lead process was finished or for lead_source=FIRST_CONTRACT_FALLBACK: The first contract of the customer was created on.
lead_source varchar 256 √ null

For getting the origin of a customer, see dim_customer.customer_origin.

How data of this entry is sourced. One of:

  • LEAD_MANAGEMENT: A lead was created in Lead Management section (either manually or via integrated systems).
  • FIRST_CONTRACT_FALLBACK: Customer was not handled via Lead Management section. Instead creation date of the customer itself and that of a customers contract are used.
lead_status varchar 256 √ 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.
source_campaign varchar 256 √ null

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

The reason picked if the lead was set to lead_status=LOSS or UNKNOWN if no specific reason is available.
last_updated timestamptz 35 √ null

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

Relationships

Close relationships within degrees of separation