CRMs

Tracking your sales-led growth funnel

Intro

In order to track the progression of prospects through your sales funnels, Roadway needs access to tables that track Leads/Contacts, Opportunities/Deals, and Pipeline ARR. With these tables, it is possible to attribute prospects to their causal growth levers.

Roadway treats the progression of prospects through Marketing and Sales pipelines as shown in the following diagram:

Lead Lifecycle Stages and Opportunity/Deal Stages

In order to drive analysis of these pipelines, Roadway requires the following tables:

• dim__contacts

• dim__opportunities(dim__deals)

• fact__pipeline_arr_snapshot

Schema

dim__contacts

This table tracks the evolution of your Leads as they progress through the stages of your sales process. The schema is as follows:

Column Name	Type	Description
contact_id	varchar	Identifier of the contact, from Salesforce or Hubspot
visitor_id	varchar (nullable)	The anonymous visitor id associated with the form fill (or other process) that led to contact/lead creation. See Add Anonymous IDs to CRM Contacts.
created_date	date	UTC date that the contact was created
<lead_lifecyclestage_i>_reached_date	date	UTC date that the Lead reached the I-th lifecylce stage
...	...	...
<custom_lead/contact_dimension_1>	See the notes on Custom User Dimensions.	Any custom contact/lead-segmentation dimension, such as `pipeline_name`, `opportunity_type`, `country`, etc.
...	...	...

Additional Constraints

• contact_idis the primary key

• Where possible, Roadway uses visitor_idto join to relevant attribution. See Add Anonymous IDs to CRM Contacts for more details.

• The columns denoted as <lead_lifecyclestage_i>_reached_date are custom columns that map to your specific Lead Lifecycle Stages (akin to the diagram above).

dim__opportunities (dim__deals)

This table tracks the evolution of your Leads after they graduate to Opportunities (Salesforce) or Deals (Hubspot). The schema is as follows:

Column Name	Type	Description
opportunity_id (deal_id)	varchar	Unique identifier of the opportunity (SFDC) or deal (Hubspot)
contact_id	varchar	Identifier for the owning (or oldest) contact associated with the opportunity or deal. This contact will be used to drive attribution for this opportunity.
created_date	date	UTC date that the opportunity or deal was created
<opportunity_stage_i>_reached_date	date	UTC date that the opportunity reached the I-th stage of the opportunity pipeline.
...	...	...
closed_date	date (nullable)	UTC date that the opportunity or deal was closed, null if not yet closed
is_closed_won	boolean	Whether the deal was closed with the opportunity converting to a customer
<custom_opportunity_dimension_1>	See the notes on Custom User Dimensions.	Any custom opportunity-segmentation dimension, such as `pipeline_name`, `opportunity_type`, `country`, etc.
...	...	...

Additional Constraints

• opportunity_idis the primary key

• The columns denoted as <opportunity_stage_i>_reached_date are custom columns that map to your specific Opportunity (Deal) Stages (akin to the diagram above).

fact__pipeline_arr_snapshot

Very similar to `fact__customer_arr_snapshot`, this table tracks the evolution of Pipeline ARR attributed to your sales processes. The schema is as follows:

Column Name	Type	Description
opportunity_id (deal_id)	varchar	Unique identifier of the opportunity (SFDC) or deal (Hubspot)
date_day	date	An "as_of-style" date
pipeline_arr	float	The annualized recurring revenue value as implied by opportunity (deal) revenue, multiplied by the probability of the opportunity (deal) closing, for the given row

Additional Constraints

• The primary key of this table is (opportunity_id, date_day).

• It is expected that from the date of an opportunity's creation to the date of its closure (inclusive), there exists a row for each (opportunity, date).