# CRMs
## 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:
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](https://app.gitbook.com/o/Nu4l1RKbmRqUw9cj7Aqp/s/QMBFYr0uPV4dZtHhcYRX/~/changes/26/required-ids-for-attribution/add-anonymous-ids-to-crm-contacts). |
| created\_date | date | UTC date that the contact was created |
| \\_reached\_date | date | UTC date that the Lead reached the I-th lifecylce stage |
| ... | ... | ... |
| \ | See the notes on [Custom User Dimensions](/data-requirements/warehouse-requirements/users.md). | Any custom contact/lead-segmentation dimension, such as \`pipeline\_name\`, \`opportunity\_type\`, \`country\`, etc. |
| ... | ... | ... |
#### Additional Constraints
* `contact_id`is the primary key
* Where possible, Roadway uses `visitor_id`to join to relevant attribution. See [Add Anonymous IDs to CRM Contacts](https://app.gitbook.com/o/Nu4l1RKbmRqUw9cj7Aqp/s/QMBFYr0uPV4dZtHhcYRX/~/changes/26/required-ids-for-attribution/add-anonymous-ids-to-crm-contacts) for more details.
* The columns denoted as `_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 |
| \\_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 |
| \ | See the notes on [Custom User Dimensions](/data-requirements/warehouse-requirements/users.md). | Any custom opportunity-segmentation dimension, such as \`pipeline\_name\`, \`opportunity\_type\`, \`country\`, etc. |
| ... | ... | ... |
#### Additional Constraints
* `opportunity_id`is the primary key
* The columns denoted as `_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).
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.roadwayai.com/data-requirements/warehouse-requirements/crms.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.