CRM Data Engine Dictionary

Table Name: compass_total_treatments_monthly_summary_hcp__v

Column Name Description Example Type
__time The calculation date. Store when the record was generated based on the pre-aggregation 2023-07-30T00:00:00.000Z timestamp
transaction_year__v transaction year 2022 varchar
transaction_month__v transaction month 2 varchar
transaction_date__v transaction date (always 1st date of the year & month) 2022-02-01 date
territory_id__v CRM Territory ID 0MI7F000000PjYxWAK varchar
territory_name__v CRM Territory Name T101 varchar
account_id__v CRM Account Id 0017F00000pnNknQAE varchar
hcp_vid__v Veeva ID for rendering HCP 243190890074473000 varchar
npi__v Rendering Provider ID, i.e. prescription written, procedure performed, diagnosis made.  1699702993 varchar
product_name__v Product brand name Cholecap varchar
analytics_product_group_id__v CRM Analytics Product Group Id a107F00000751OqQAI varchar
company_product_group__v Company Product Flag TRUE varchar
market_id__v CRM Analytics Market Id a1D7F0000094C5AUAU varchar
value__v Summary of the monthly TRx 12 number
previous_value__v Summary of the previous month TRx 11 number
month_over_month_growth__v Growth ratio between value__v and previous_value__v 9.1 decimal

Table Name: compass_total_treatments_monthly_summary_product__v

Column Name Description Example Type
__time The calculation date. Store when the record was generated based on the pre-aggregation 2023-07-30T00:00:00.000Z timestamp
transaction_year__v transaction year 2022 varchar
transaction_month__v transaction month 2 varchar
transaction_date__v transaction date (always 1st date of the year & month) 2022-02-01 date
territory_id__v CRM Territory ID 0MI7F000000PjYxWAK varchar
territory_name__v CRM Territory Name T101 varchar
product_name__v Product brand name Cholecap varchar
analytics_product_group_id__v CRM Analytics Product Group Id a107F00000751OqQAI varchar
company_product_group__v Company Product Flag TRUE varchar
market_id__v CRM Analytics Market Id a1D7F0000094C5AUAU varchar
value__v Summary of the monthly TRx 12 number
previous_value__v Summary of the previous month TRx 11 number
month_over_month_growth__v Growth ratio between value__v and previous_value__v 9.1 decimal

Table Name: compass_new_patients_monthly_summary_hcp__v

Column Name Description Example Type
__time The calculation date. Store when the record was generated based on the pre-aggregation 2023-07-30T00:00:00.000Z timestamp
transaction_year__v transaction year 2022 varchar
transaction_month__v transaction month 2 varchar
transaction_date__v transaction date (always 1st date of the year & month) 2022-02-01 date
territory_id__v CRM Territory ID 0MI7F000000PjYxWAK varchar
territory_name__v CRM Territory Name T101 varchar
account_id__v CRM Account Id 0017F00000pnNknQAE varchar
hcp_vid__v Veeva ID for rendering HCP 243190890074473000 varchar
npi__v Rendering Provider ID, i.e. prescription written, procedure performed, diagnosis made.  1699702993 varchar
product_name__v Product brand name Cholecap varchar
analytics_product_group_id__v CRM Analytics Product Group Id a107F00000751OqQAI varchar
company_product_group__v Company Product Flag TRUE varchar
market_id__v CRM Analytics Market Id a1D7F0000094C5AUAU varchar
value__v Summary of the monthly NBRx 12 number
previous_value__v Summary of the previous month NBRx 11 number
month_over_month_growth__v Growth ratio between value__v and previous_value__v 9.1 decimal

Table Name: compass_new_patients_monthly_summary_product__v

Column Name Description Example Type
__time The calculation date. Store when the record was generated based on the pre-aggregation 2023-07-30T00:00:00.000Z timestamp
transaction_year__v transaction year 2022 varchar
transaction_month__v transaction month 2 varchar
transaction_date__v transaction date (always 1st date of the year & month) 2022-02-01 date
territory_id__v CRM Territory ID 0MI7F000000PjYxWAK varchar
territory_name__v CRM Territory Name T101 varchar
product_name__v Product brand name Cholecap varchar
analytics_product_group_id__v CRM Analytics Product Group Id a107F00000751OqQAI varchar
company_product_group__v Company Product Flag TRUE varchar
market_id__v CRM Analytics Market Id a1D7F0000094C5AUAU varchar
value__v Summary of the monthly NBRx 12 number
previous_value__v Summary of the previous month NBRx 11 number
month_over_month_growth__v Growth ratio between value__v and previous_value__v 9.1 decimal

Table Name: compass_diagnosed_patients__v

Column Name Description Example Type
__time The calculation date. Store when the record was generated based on the pre-aggregation 2023-07-30T00:00:00.000Z timestamp
transaction_year__v transaction year 2022 Int
transaction_month__v transaction month 2 Int
transaction_date__v transaction date (date of the diagnosis) 2022-02-01 date
ta1__v Primary, broadest, therapeutic area of the product. Therapeutic areas were created by Data Cloud to help facilitate research and analytics objectives. Diabetes varchar
ta2__v Secondary therapeutic area of the product. Therapeutic areas were created by Data Cloud to help facilitate research and analytics objectives. Insulin varchar
ta3__v Tertiary, most granular, therapeutic area of the product. Therapeutic areas were created by Data Cloud to help facilitate research and analytics objectives. Inhaler varchar
record_id__v record_id of the transaction. TX81f14a2f-dec5-4b76-ad55-90e549c49f varchar
diagnosis_column__v Set the column info the diagnosis code. If the diagnosis_code__v comes from diagnosis_code_1, then stamp 1. 1 2 varchar
diagnosis_code__v ICD-9/ICD-10 code for diagnosis E1110 varchar
diagnosis_code_desc__v Description for diagnosis E1110: Type 2 diabetes mellitus with ketoacidosis without coma varchar
diagnosis_market__v Market definition from CRM for the diagnosis_code 0MI7F000000PjYxWAK varchar
diagnosis_market_name__v Name of the market Diabetes TypeⅡ varchar
territory_id__v CRM Territory Id 0MI7F000000PjYxWAK varchar
territory_name__v CRM Territory Name T1101 varchar
hcp_vid__v Rendering HCP Veeva ID. It can be used to integrate with all Veeva systems, including CRM, Link, and OpenData. 243190890074473000 varchar
npi__v Rendering Provider NPI. Represents the HCP that wrote the prescription on Prescription Claims. Represents the HCP that performed the treatment and/or made the diagnoses on. 1699702993 varchar
account_id__v CRM Account Id 0017F00000pnNknQAE varchar
hcp_specialty_1__v Primary Specialty for the Rendering HCP, provided by Veeva OpenData Pulmonary Disease varchar
referring_hcp_vid__v Veeva ID for the referring HCP. Veeva ID is the unique ID created and maintained by Veeva to identify individual HCPs. It can be used to integrate with all Veeva systems, including OpenData, CRM, and Link. 243036094487069000 varchar
referring_hcp_npi__v NPI of the HCP who directed the patient for care to the HCP rendering the services being reported.The attribute is enriched using OpenData. 1386933992 varchar
referring_account_id__v CRM Account Id for referring HCP 0017F00000pnNknXVD varchar
product_name__v Product name given to the group of Product IDs, managed by Veeva Data Cloud. For branded products, product name represents the brand name given by the manufacturer. For generic products, product name is the generic name of the product.  Cholecap varchar
patient_gender__v Gender of the patient F M varchar
patient_zip3__v ZIP3 (first 3 digits of ZIP code) of the patient 344 varchar
patient_state__v State of patient based on patient’s ZIP3 FL varchar
new_flag__v Set "New" if the diagnosis market is new for the patient New varchar