Marketing Analytics Data - L2R Models
Rpt_lead_to_revenue
The Lead to Revenue report is the consolidation of the major lifecycle objects/marts: Person, Opportunity, Account, and both types of Bizible Touchpoints. It is a one-stop shop for nearly all Sales and Marketing metrics and data on the joined objects. It allows for quick analysis of funnel progression, cohorting of data points and metrics, and allows for a true funnel from lead creation to opportunity close to be created from one table.
Use Cases
- Funnel stage progression of a given record
- Count of records in a given funnel stage
- Average velocity between funnel stages
- Cohorting data around a given attribute
Key Fields
lead_to_revenue_id
- The surrogate key that identifies a unique row in this model.[kpi]_date_range_*
- KPI-specific date aggregationsbizible_mql_*
- Bizible TP data on the TP that occurs just prior to the MQL Date.bizible_most_recent_*
- Bizible TP data on the most recent TP on a person/opportunity
Key Metrics
- Count of records by funnel stage
- Count of Touchpoints between specific funnel stages
- Overall velocity through the funnel
- Funnel stage conversion rates
Data Lineage
- Data is sourced from SFDC
- The full lineage can be seen here
DBT Solution
The dbt solution generates a dimensional model from RAW source data. The exceptions are the following fields that are calculated based on business logic implemented within specific dbt models:
Field | Business Logic |
---|---|
lead_to_revenue_id | Defined in model |
[kpi]date_range* | Defined in model |
Rpt_l2r_campaign_interactions
The L2R Campaign Interactions report table uses a primary union between person/buyer and attribution/opportunity touchpoint and related mart fields to derive its base table. From there, various calculations on date/time fields and related dimensions are performed.
Use Cases
L2R is the base table of Campaign reporting at GitLab. It combines touchpoints allowing us to create a single view that shows the top of the funnel to Influenced SAO. It is used to track the conformance of email, content, and paid media efforts.
Key Fields
Person_order_type
- A derived Order Type for the person record, based on the is_first_order_person SFDC field
Touchpoint_type
- A denotation of whether the given touchpoint/record is a Person/Buyer or Opportunity/Attribution touchpoint.
Custom attribution fields (i.e. custom_sao)
- Calculations to sum up the custom model weights of an opportunity’s touchpoints based on other criteria in the model
Person_status_change
- A derived field to show the person record’s progression from one status to another.
Is_mdf_campaign
- A T/F flag that shows whether or not the campaign associated with the record will have MDF funding or not.
report_opportunity_*
- Derived fields to show the report-based attributes of the opportunity leveraging historical snapshot data where appropriate.
Is_sales_dev_owned_record
- A T/F flag to show whether or not the record is owned by a Sales Dev rep in SFDC.
*_date_range_*
- The pre-defined date aggregations of a given funnel-stage’s date for the given record.
Key Metrics
- Count of records/KPI totals by dimensions (campaign type, date, order type, etc.)
- Count of touchpoints at given stages or from specific campaigns
- Combined weighted Net ARR or Attributed touchpoints, by attribution model and dimension
Data Lineage
- Data is sourced from SFDC and L2R
- The full lineage can be seen here
Field | Business Logic |
---|---|
Custom attribution fields (i.e. custom_sao) | Defined in model |
person_status_change | Defined in model |
is_mdf_campaign | Defined in model |
report_opportunity_* | Defined in model |
is_sales_dev_owned_record | Defined in model |
Rpt_l2r_campaign_interactions_paid_account
Filtered version of rpt_l2r_campaign_interactions on current paying accounts only.
Use Cases
L2R is the base table of Customer-specific Campaign reporting at GitLab. It combines touchpoints allowing us to create a single view that shows the top of the funnel to Influenced SAO. It is used to track the conformance of email, content, and paid media efforts.
Key Fields
Person_order_type
- A derived Order Type for the person record, based on the is_first_order_person SFDC field
Touchpoint_type
- A denotation of whether the given touchpoint/record is a Person/Buyer or Opportunity/Attribution touchpoint.
Custom attribution fields (i.e. custom_sao)
- Calculations to sum up the custom model weights of an opportunity’s touchpoints based on other criteria in the model
Person_status_change
- A derived field to show the person record’s progression from one status to another.
Is_mdf_campaign
- A T/F flag that shows whether or not the campaign associated with the record will have MDF funding or not.
report_opportunity_*
- Derived fields to show the report-based attributes of the opportunity leveraging historical snapshot data where appropriate.
Is_sales_dev_owned_record
- A T/F flag to show whether or not the record is owned by a Sales Dev rep in SFDC.
*_date_range_*
- The pre-defined date aggregations of a given funnel-stage’s date for the given record.
Key Metrics
- Count of records/KPI totals by dimensions (campaign type, date, order type, etc.)
- Count of touchpoints at given stages or from specific campaigns
- Combined weighted Net ARR or Attributed touchpoints, by attribution model and dimension
Data Lineage
- Data is sourced from SFDC
- The full lineage can be seen here
DBT Solution
The dbt solution generates a dimensional model from RAW source data. The exceptions are the following fields that are calculated based on business logic implemented within specific dbt models:
Field | Business Logic |
---|---|
Custom attribution fields (i.e. custom_sao) | Defined in model |
person_status_change | Defined in model |
is_mdf_campaign | Defined in model |
report_opportunity_* | Defined in model |
is_sales_dev_owned_record | Defined in model |
Rpt_l2r_community_data
Tableau-specific table that joins Zuora, Opportunity, and Lead To Revenue data to hone in on the community-owned opportunities and their related data points.
Use Cases
- Calculating community-driven KPIs
- Split KPIs by Community project (SKU)
Key Fields
Community_data_type
- A grouping of SKUs (product_rate_plan_id) to determine which of the 3 Community projects this record belongs to.Number_of_seats
- The count of user seats on a given account/namespace.*_date_range_*
- KPI stage specific date aggregationsIs_last_segment_version
- T/F flag to indicate if the record in question is the most recent version of an invoice charge.Subscription_order
- Incrementing number of subscriptions per AccountIs_first_subscription_institution
- T/F flag to show whether or not the record in question is the first subscription for a given Account
Key Metrics
- Number_of_seats by dimension
- Count of subscriptions per account
- Conversion rate of subscriptions/opportunities over time by dimensions
Data Lineage
- Data is sourced from SFDC
- The full lineage can be seen here
DBT Solution
The dbt solution generates a dimensional model from RAW source data. The exceptions are the following fields that are calculated based on business logic implemented within specific dbt models:
Field | Business Logic |
---|---|
Community_data_type | Defined in model |
Number_of_seats | Defined in model |
Subscription_order | Defined in model |
Is_first_subscription_institution | Defined in model |
Rpt_l2r_prospects
Tableau-specific table that is a filtered view of rpt_lead_to_revenue, focused entirely on First Order Prospects.
Use Cases
- Funnel stage progression of a given record
- Count of records in a given funnel stage
- Average velocity between funnel stages
- Cohorting data around a given attribute
Key Fields
lead_to_revenue_id
- The surrogate key that identifies a unique row in this model.[kpi]_date_range_*
- KPI-specific date aggregationsbizible_mql_*
- Bizible TP data on the TP that occurs just prior to the MQL Date.bizible_most_recent_*
- Bizible TP data on the most recent TP on a person/opportunity
Key Metrics
- Count of records by funnel stage
- Count of Touchpoints between specific funnel stages
- Overall velocity through the funnel
- Funnel stage conversion rates
Data Lineage
- Data is sourced from SFDC
- The full lineage can be seen here
DBT Solution
The dbt solution generates a dimensional model from RAW source data. The exceptions are the following fields that are calculated based on business logic implemented within specific dbt models:
Field | Business Logic |
---|---|
lead_to_revenue_id |
Defined in model |
kpi_date_range_* |
Defined in model |
Wk_rpt_l2r_cohort_model
A Tableau-specific table, currently a WIP, that Cohorts the rpt_lead_to_revenue data, pre-calculating velocity and lag time between funnel stages. This is entirely focused around determining cohorted conversion rates and KPI counts.
Use Cases
- Calculating cohorted velocity, conversion rate, and KPI counts
Key Fields
lead_to_revenue_id
- The surrogate key that identifies a unique row in this model.[kpi]_date_range_*
- KPI-specific date aggregationsKpi_to_kpi_days
- Lag time (in days) between specific KPIs/Funnel stages. Also represents conversion velocity.
Key Metrics
- Count of records by funnel stage
- Count of Touchpoints between specific funnel stages
- Overall velocity through the funnel
- Funnel stage conversion rates
Data Lineage
- Data is sourced from SFDC
- The full lineage can be seen here
DBT Solution
The dbt solution generates a dimensional model from RAW source data. The exceptions are the following fields that are calculated based on business logic implemented within specific dbt models:
Field | Business Logic |
---|---|
lead_to_revenue_id | Defined in model |
[kpi]date_range* | Defined in model |
kpi_to_kpi_days | Defined in model |
ec253af0
)