Snowflake Reader Models

agg_sales_activities_valid_table

Aggregate model of sales activities and Time in the Selling Zone(TSZ) by day and by community. The TSZ is including the time from all leasing counselors.

Column	Type	Description
date	None	date in year-month-day(yyyy-mm-dd) format according to community entry date.
community_id	None	distinct id for each community.
community_name	None	community name for each community.
community_is_skilled	None	boolean field to included or exclude communities marked as skilled nursing facilities.
community_is_active	None	boolean field to included or exclude communities marked active.
Appointment	None	Count of distinct activity.
Appointment_TSZ	None	Sum total TSZ.
Assessment	None	Count of distinct activity.
Assessment_TSZ	None	Sum total TSZ.
Call_In	None	Count of distinct activity.
Call_In_TSZ	None	Sum total TSZ.
Call_Out_Total	None	Count of distinct activity.
Call_Out_Total_TSZ	None	Sum total TSZ.
Call_Out_Without_Message	None	Count of distinct activity.
Call_Out_Without_Message_TSZ	None	Sum total TSZ.
Call_Out_With_Message	None	Count of distinct activity.
Call_Out_With_Message_TSZ	None	Sum total TSZ.
Creative_Follow_Up	None	Count of distinct activity.
Creative_Follow_Up_TSZ	None	Sum total TSZ.
Email_In	None	Count of distinct activity.
Email_In_TSZ	None	Sum total TSZ.
Email_Out	None	Count of distinct activity.
Email_Out_TSZ	None	Sum total TSZ.
Home_Visit_Total	None	Count of distinct activity.
Home_Visit_Total_TSZ	None	Sum total TSZ.
Initial_Home_Visit	None	Count of distinct activity.
Initial_Home_Visit_TSZ	None	Sum total TSZ.
Return_Home_Visit	None	Count of distinct activity.
Return_Home_Visit_TSZ	None	Sum total TSZ.
Mail_In	None	Count of distinct activity.
Mail_In_TSZ	None	Sum total TSZ.
Mail_Out	None	Count of distinct activity.
Mail_Out_TSZ	None	Sum total TSZ.
Planning_Session	None	Count of distinct activity.
Planning_Session_TSZ	None	Sum total TSZ.
Text_Total	None	Count of distinct activity.
Text_Total_TSZ	None	Sum total TSZ.
Tour_Total	None	Count of distinct activity.
Tour_Total_TSZ	None	Sum total TSZ.
Initial_Tour	None	Count of distinct activity.
Initial_Tour_TSZ	None	Sum total TSZ.
Return_Tour	None	Count of distinct activity.
Return_Tour_TSZ	None	Sum total TSZ.
Video_Message	None	Count of distinct activity.
Video_Message_TSZ	None	Sum total TSZ.
Virtual_Tour	None	Count of distinct activity.
Virtual_Tour_TSZ	None	Sum total TSZ.
Walk_In	None	Count of distinct activity.
Walk_In_TSZ	None	Sum total TSZ.

dtl_action_table

When a leasing counselor sets a next action then all the details of that information will be here. May have to join to dtl_sherpa_user_table to get leasing counselor name.

Column	Type	Description
action_id	None	ID of the corresponding action entry.
prospect_id	None	The prospect ID. This will NOT be a unique value as the prospect will show up for each community they have been shared with.
sales_note	None	This id will correspond with a sales_note_id so you can tie the action to the sales note entry.
selected_event_id	None	This id will correspond with the event name.
community_prospect_id	None	ID of the corresponding community prospect record.
community_referral_contact_id	None	ID of the corresponding community outreach/referral id.
description	None	Note associated with next task.
event_type	None	This is the name of the task set, such as 'Call Out', 'Planning Session', 'Tour', etc..
due_date	None	The date when the task is set to be accomplished.
start_date_time	None	Date and time of when task started.
start_datetime_utc	None	Date and time of when task started in UTC.
start_datetime_local_calculated	None	Date and time of when task started in community timezone.
end_date_time	None	Date and time of when task ended.
end_datetime_utc	None	Date and time of when task ended in UTC.
end_datetime_local_calculated	None	Date and time of when task ended in community timezone.
end_time	None	Time of when task ended.
start_time	None	Time of when task started.
end_date	None	Date of when task ended.
complete	None	Boolean if task has been completed or not.
complete_date	None	Date when task was completed.
completed_at_utc	None	Date when task was completed in UTC timezone.
completed_at_local_calculated	None	Date when task was completed in community timezone.
canceled	None	Boolean if task was cancelled or not.
cancel_date	None	Date when task was cancelled.
cancel_date_utc	None	Date when task was cancelled in UTC timezone.
cancel_date_local_calculated	None	Date when task was cancelled in community timezone.
category	None	This is currently all null and will be removed in the future.
recurring	None	Boolean if the task is a recurring task.
all_day	None	Boolean if task is an all day task.
title	None	Name of the type of task/event.
action_source_system	None	This is all null and will be removed in the future.
deleted_date	None	Date when task was deleted in the CRM.
created_date	None	Date when task was created in the CRM.
updated_date	None	Date when task was updated in the CRM.
sales_note_source_system	None	This is all null and will be removed in the future.
priority	None	This is a boolean field where you can mark if the task is a priority or not.
orig_sherpa_id	None	This is all null and will be removed in the future.
outreach_contact_id	None	ID that is associated with an outreach contact.
created_by_id	None	ID that is associated with the User that created the record.
updated_by_id	None	ID that is associated with the User that updated the record.
action_type	None	ID that is associated with the action/event type selected.
automated_task_id	None	This is all null and will be removed in the future.
discr	None	This is the event type name.
completing_sales_note	None	This is the sales_note_id where the leasing counselor completed the task.
is_synced_to_vendor_calendar	None	Boolean field to let you know if a task has been synced to a vendor calendar.
created_in_community_id	None	This is the community_id of which community the task was created in.
data_synced_date	None	This is the date and time when the row of data was synced with the Snowflake database.

dtl_care_type_table

dtl_community_prospect_clinical_table

Community prospect details for all skilled communities. Each row represents a unique prospect and community combination.

Column	Type	Description
community_prospect_id	None	ID of the corresponding community prospect record.
community_id	None	Community id associated with the community prospect record.
community_name	None	Community name.
community_onboard_date	None	The date the community was onboarded with Sherpa CRM.
community_is_skilled	None	A flag indicating whether or not the associated community is skilled nursing.
prospect_id	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
prospect_name	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
is_overdue_activity	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
no_next_steps	None	Indicates whether or not there is an open next step for this community prospect.
last_completed_activity_date	None	The date of the last completed sales activity for this community prospect.
next_activity_date	None	The date of the next scheduled sales activity for this community prospect.
lost_lead_date	None	The date this community prospect was marked as a lost lead, if applicable.
is_top_ten	None	Flag to indicate if this community prospect has been placed in the top ten.
is_from_on_deck	None	Flag to indicate if this community prospect came from an On Deck status.
is_read	None	Flag to indicate if this community prospect profile has been viewed.
created_date	None	The actual date the community prospect record was created.
updated_date	None	The date the community prospect record was last updated.
active_date	None	The date that the community prospect was given a valid status.
community_prospect_clinical_info_id	None	The clinical info record id. May be used to find further information about this community prospect.
community_prospect_status_id	None	The current status id of the community prospect.
community_prospect_status_name	None	The current status name of the community prospect.
community_prospect_status_display_name	None	The current status display name of the community prospect.
lost_lead_reason	None	The reason id a community prospect was given the lost lead status, if applicable.
stage_of_change_id	None	The current stage of change id for the community prospect.
initial_leasing_counselor_id	None	The id of the initial leasing counselor.
created_by_id	None	The id of the user who created the community prospect.
updated_by_id	None	The id of the last user who updated the community prospect.
lost_lead_snf	None	If a clinical community prospect was lost to another SNF, this is the ID of the outreach company they went to.
data_synced_date	None	The date that the community prospect was last update by the data pipeline.
turnaround_time	None	The amount of time in seconds that elapsed between a community prospect creation and the first action.
payer_type_category_id	None	The category id of the payer type.
payer_type_provider_category_name	None	The category name of the payer type.
payer_type_provider_id	None	Payer type provider id.
payer_type_provider_name	None	Payer type provider name.
denial_reason_id	None	Denial reason id.
denial_reason_name	None	Denial reason name.
denial_reason_display_name	None	Denial reason display name.
status_pending_reason_id	None	Status pending reason id.
status_pending_name	None	Status pending reason name.
diagnosis_names	None	Concatenated list of diagnosis names.

dtl_community_prospect_inquiry_source_table

Model for inquiry sources at the community prospect level with additional inquiry source details and outreach comapny name.

Column	Type	Description
community_prospects_source_id	None	ID of the community that the initial inquiry source is related to.
inquiry_source_id	None	ID of the associated inquiry source.
inquiry_source_parent_category_id	None	ID of the unique category that an inquiry source belongs to.
inquiry_source_name	None	The the name of the inquiry source for each prospect id.
inquiry_source_parent_category_name	None	Name of the category that the inquiry source belongs to.
inquiry_date	None	The date that the inquir took place.
origin_community_id	None	The community id that the prospect transferred from.
origin_community_name	None	The name of the community that the prospect transferred from.
community_id	None	THe id of the current community.
outreach_contact_id	None	unique id per outreach contact.
outreach_company_name	None	The name of the outreach company that the contact belongs to.
is_physician	None	Boolean for checking if the outreach contact is a Physician or not.
community_prospect_source_created_date	None	The date that the row record was created.
community_prospect_source_updated_date	None	The date that the source record was last updated.
community_prospect_id	None	ID for prospects within a community.
created_by_id	None	unique ID of individual that created the record.
updated_by_id	None	unique ID of individual that updated the record
data_synced_date	None	The most recent date-time stamp that the table was updated.
prospect_id	None	Unique ID for the prospect at the company level.
prospect_inquiry_source_row	None	The flag to determine if this was the initial inquiry source by prospect id.
community_prospect_inquiry_source_row	None	Flag to determine if this was the initial inquiry source by the community prospect id, this should always equal 1.

dtl_community_prospect_table

Community prospect details for all non-skilled communities. Each row represents a unique prospect and community combination.

Column	Type	Description
community_prospect_id	None	ID of the corresponding community prospect record.
community_id	None	Community id associated with the community prospect record.
community_name	None	Community name.
community_onboard_date	None	The date the community was onboarded with Sherpa CRM.
community_is_skilled	None	A flag indicating whether or not the associated community is skilled nursing.
prospect_id	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
prospect_name	None	The prospects names. Concatenation of all person names associated with this prospect.
is_overdue_activity	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
no_next_steps	None	Indicates whether or not there is an open next step for this community prospect.
last_completed_activity_date	None	The date of the last completed sales activity for this community prospect.
next_activity_date	None	The date of the next scheduled sales activity for this community prospect.
lost_lead_date	None	The date this community prospect was marked as a lost lead, if applicable.
is_top_ten	None	Flag to indicate if this community prospect has been placed in the top ten.
is_from_on_deck	None	Flag to indicate if this community prospect came from an On Deck status.
is_read	None	Flag to indicate if this community prospect profile has been viewed.
created_date	None	The actual date the community prospect record was created.
updated_date	None	The date the community prospect record was last updated.
active_date	None	The date that the community prospect was given a valid status.
community_prospect_status_id	None	The current status id of the community prospect.
community_prospect_status_name	None	The current status name of the community prospect.
community_prospect_status_display_name	None	The current status display name of the community prospect.
lost_lead_reason	None	The reason id a community prospect was given the lost lead status, if applicable.
stage_of_change_id	None	The current stage of change id for the community prospect.
initial_leasing_counselor_id	None	The id of the initial leasing counselor.
created_by_id	None	The id of the user who created the community prospect.
updated_by_id	None	The id of the last user who updated the community prospect.
photo_id	None	The id of the prospect's main photo. If column is not null then there is a photo associated with the prospect.
other_photo_id	None	If there is other photos associated with the prospect profile then it will have an ID in the column.
residence_photo_id	None	If there is a prospect residence photo on the prospect profile then it will have an ID in the column.
data_synced_date	None	The date that the community prospect was last update by the data pipeline.

dtl_community_table

Community details for all active communities.

Column	Type	Description
community_id	None	Unique ID of the community.
community_name	None	Community name.
company_id	None	The ID of the parent company.
company_name	None	Parent company name.
phone_number	None	Community phone number.
address_line_one	None	Line one of the community address.
address_line_two	None	Line two of the community address.
city	None	City of the community address.
state_name	None	State abbreviation of the community address.
postal_code	None	Zip or postal code of the community address.
country_name	None	Country abbreviation of the community address.
is_active	None	Flag indicating the status of the community.
is_skilled	None	Flag indicating if the community is designated as a skilled nursing facility.
time_zone	None	Timezone of the community in "America/Chicago" format.
onboard_date	None	The date the community was activated in Sherpa.
primary_contact_name	None	Name of the person designated as the primary contact.
primary_contact_phone	None	Phone number of the person designated as the primary contact.
primary_contact_email	None	Email address of the person designated as the primary contact.
secondary_contact_name	None	Name of the person designated as the primary contact.
secondary_contact_phone	None	Phone number of the person designated as the primary contact.
secondary_contact_email	None	Email address of the person designated as the primary contact.
owner_name	None	Name of the person designated as the owner.
executive_director_name	None	Name of the person designated as the executive director.
regional_director_name	None	Name of the person designated as the regional director.
sales_director_name	None	Name of the person designated as the sales director.
created_by_id	None	ID of the Sherpa user who created this community.
created_date	None	Date that the community was created in the database.
deleted_date	None	Date that the community was marked deleted in the database.
updated_by_id	None	ID of the user who last made changes to this community.
updated_date	None	The date on which the community record was last updated.
data_synced_date	None	The date on which the community record was last synced from the source database.

dtl_custom_fields_outreach_contact_table

Custom fields for outreach contact information.

Column	Type	Description
custom_fields_id	None	The unique id of of the field row.
outreach_contact_id	None	The outreach contact id for each custom field.
field_name	None	The custom field name from the values table.
value	None	Any response that is considered valid.
is_active	None	Is this custom field currently set to an active status.
custom_fields_created_date	None	The day that the custom fields were created.
custom_fields_created_by_id	None	The unique user id of the person or process that created the custom fields.
custom_fields_updated_date	None	The day that the custom fields were updated.
custom_fields_updated_by_id	None	The unique user id of the person or process that updated the custom fields.
custom_values_created_date	None	The day that the custom values were updated.
custom_values_created_by_id	None	The unique user id of the person or process that updated the custom fields.
custom_values_updated_date	None	The day that the custom fields were updated.
custom_values_updated_by_id	None	The unique user id of the person or process that updated the custom fields.

dtl_custom_fields_prospect_table

Custom fields at the prospect id level.

Column	Type	Description
custom_fields_id	None	The unique id of of the field row.
prospect_id	None	The prospect ID. This will not be a unique value as the prospect will show up for each community they have been shared with.
field_name	None	The custom field name from the values tables.
value	None	Any response that is considered valid.
is_active	None	Is this custom field currently set to an active status.
custom_fields_created_date	None	The day that the custom fields were created.
custom_fields_created_by_id	None	The unique user id of the person or process that created the custom fields.
custom_fields_updated_date	None	The day that the custom fields were updated.
custom_fields_updated_by_id	None	The unique user id of the person or process that updated the custom fields.
custom_values_created_date	None	The day that the custom values were updated.
custom_values_created_by_id	None	The unique user id of the person or process that updated the custom fields.
custom_values_updated_date	None	The day that the custom fields were updated.
custom_values_updated_by_id	None	The unique user id of the person or process that updated the custom fields.

dtl_deposit_level_table

Details about customer defined deposit levels

Column	Type	Description
deposit_level_id	None	Unique ID for the deposit level.
deposit_level_name	None	Deposit level name.
deposit_description	None	Deposit level description.
is_occupies_unit	None	Is the deposit level configured to require a unit.
is_requires_date	None	Is the deposit level configured to require a date.
sortorder	None	Sorting preference for the deposit level.
is_concurrent	None	Is the deposit level configured to be concurrent.
created_date	None	Timestamp the deposit level was created.
created_by_id	None	Id of the user that created the deposit level.
updated_date	None	Id of the user that last updated the deposit level.
updated_by_id	None	Id of the user that last updated the deposit level.
archive_date	None	Id of the user that archived the deposit level.
archived_by_id	None	Id of the user that archived the deposit level.

dtl_deposit_table

Table showing all deposits down to the person level with all attributes of a deposit.

Column	Type	Description
wait_list_entry_id	None	Unique ID for the wait list entry.
community_id	None	The unique ID for the community. Can connect to dtl_community_table to get community details.
deposit_date	None	The date a deposit took place.
deposit_amount	None	The deposit amount.
deposit_level_name	None	The deposit type (reservation, etc..)
deposit_description	None	Description of the deposit input by whomever entered the deposit.
is_concurrent	None	deposit happens in conjunction with another deposit.
is_occupies_unit	None	Is moving in soon, holding unit for prospect.
deposit_note	None	Sales note associated with the outcome of a deposit.
priority	None	Rank that leasing counselors assign to know which order to work deposits in.
projected_move_in_date	None	Date prospect is projected to move into a unit.
unit_id	None	Distinct id given to each unit at the company level. Can connect to dtl_unit_table to get unit details.
progressed_date	None	If a later deposit was made then a progressed date will be given to a deposit.
progressed_by_id	None	User_id associated to a later deposit that has been made.
progressed_wait_list_entry_id	None	Next wait_list_entry_id for the same prospect that progressed a deposit.
completed_sale_id	None	Sale_id associated with a completed deposit. Connect to dtl_sale_table to get sale details.
completed_date	None	Date deposit was completed at a prospect level.
completed_by_id	None	User_id who entered the completed deposit. Can connect to dtl_sherpa_user_table to get user info.
cancelled_date	None	Date deposit was cancelled.
cancelled_by_id	None	User_id who entered the cancelled the deposit. Can connect to dtl_sherpa_user_table to get user info.
created_date	None	Date the deposit was entered into the CRM.
created_by_id	None	User_id who entered the deposit into the CRM. Can connect to dtl_sherpa_user_table to get user info.
updated_date	None	Date the deposit was updated in the CRM.
updated_by_id	None	User_id who updated the deposit into the CRM. Can connect to dtl_sherpa_user_table to get user info.
deleted_date	None	Date the deposit was deleted in the CRM.
deleted_by_id	None	User_id who deleted the deposit in the CRM. Can connect to dtl_sherpa_user_table to get user info.
outcome_type	None	Discription of the deposit outcome.
cancelled_outcome_type	None	Outcome description of a cancelled deposit.
is_active_waitlist	None	Boolean for if someone is on the active wait list.
prospect_id	None	Distinct id given to a prospect at the company level. Can connect to dtl_prospect_table for details.
community_prospect_id	None	Distinct id given to a prospect at the community level. Can connect to dtl_community_prospect_table for details.
first_person_id	None	Distinct id given to first person (min person id) at the community level. Can connect to the dtl_person_table for details.
second_person_id	None	Distinct id given to second person (max person id) at the community level. Can connect to the dtl_person_table for details.

dtl_occupancy_daily_by_unit_table

This is a detail table for occupancy at the unit level, by community, by day. This table can be used to find unit occupancy levels adn unit care types at a point in time. This table also shows move-ins, move-outs and trial stay ins/outs at the unit level. This table does not transfers in the move-in/out metrics. This table also checks that the unit is not off census for unit_count and unit_occupied_percentage.

Column	Type	Description
date	None	The date for the units details.
community_id	None	The unique identifier for the community within the company.
community_name	None	The name of the community within the company.
unit_id	None	The unique identifier of the unit itself. This is unique at the company level.
unit_name	None	The name that has been assigned to the unit within the community.
move_in	None	The number of prospects that moved_in to the unit. This can be a 0.5 for a unit can hold more than one prospect.
move_out	None	The number of prosepects that move_out of the unit. Thuis can be a0.5 for a unt that can hold more than one prospect.
trial_stay_in	None	The number of prospects that moved in to the unit for a trial stay. This can be a 0.5 for a unit can hold more than one prospect.
trial_stay_out	None	The number of prosepects that moved out of the unit after a trial stay. This can be a0.5 for a unt that can hold more than one prospect.
unit_care_type_name	None	The corresponding care type asspciated to teh id assigned to the unit. (i.e. Assisted Living, Independent Living, etc...)
care_type_category_name	None	The care type name assigned to the parent care type category.
unit_style_name	None	The description of the unit as set by the community.
units_occupied	None	The total amount of the unit the is occupied. If the unit can hold two prospects this can be 0.5.
unit_count	None	The total number of units that are attributed by unit_id. Archived Units will not show up. Deleted Units will not show up after their deleted_date.
unit_occupied_percentage	None	The porcentage occupancy that the unit is at on the corresponding day.

dtl_occupancy_event_moves_transfers_table

All move-in/move-out and transfer-in/tranfer-outs events combined.

Column	Type	Description
occupancy_event_id	None	The unique id for each event that has taken place.
occupancy_event_end_id	None	The occupancy event id of the move-out/tranfer-out record corresponding with previous event, if exists.
person_id	None	The person level on which the event took place
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
community_name	None	The name for the community within a company.
event_start_type	None	The initial event type that took place. This should always be a move-in.
event_start_date	None	The date that the initial event took place.
event_end_date	None	The day that follow-up event occured.
event_end_type	None	The event that the followed a previous transfer or move-in.
occupancy_move_out_reason	None	Reason given for why the prospect moved out.
length_of_stay	None	How many days between the start and end event. If there is no end event yet the number represents the number of full days between the start event and the last full day. If the start event is in the future the record will show a zero.
unit_id	None	The ID of the unit that the event occurred in.
unit_name	None	The name of the unit that the event occurred in.
unit_care_type_id	None	The ID of the care type unit assigned to that unit.
unit_care_type_name	None	The corresponding care type associated to the id assigned to the unit. (i.e. Assisted Living, Indepenent Living, etc..)
unit_care_type_category_id	None	The ID that unit care type falls under.
unit_care_type_category_name	None	The name associated with the ID in the unit care type id field.

dtl_occupancy_event_trial_stays_table

All trial-stay events combined. Does NOT include moves or transfers.

Column	Type	Description
trial_stay_id	None	The unique id for each event that has happended.
person_id	None	The person level on which the event took place
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
community_name	None	The name for the community within a company.
event_type	None	The occupancy event that took place, this should always be trial-stays.
event_start_date	None	The date that the trial-stay started.
event_end_date	None	The day that the trial-stay ended.
length_of_stay	None	How many days between when the trial-stay began and trial-stay ended. If the trial-stay has not happened yet the number represents the number of full days between the trial-stay beginning and the last full day. If the trial-stay is in the future the record will show a zero.
unit_id	None	The ID of the unit that the event occurred in.
unit_name	None	The name of the unit that the event occurred in.
unit_care_type_id	None	The ID of the care type unit assigned to that unit.
unit_care_type_name	None	The corresponding care type associated to the id assigned to the unit. (i.e. Assisted Living, Indepenent Living, etc..)
unit_care_type_category_id	None	The ID that unit care type falls under.
unit_care_type_category_name	None	The name associated with the ID in the unit care type id field.

dtl_outreach_activities_valid_table

Detail table containing all outreach activities by day, community, prospect id and community prospect id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_name	None	The name of the community.
is_skilled	None	Flag for determining if a community is a Skilled Nursing Facility or not. Will always be a true or false flag.
is_active	None	Flag for determining if a community is active or now. Will always be a true or false flag.
community_outreach_contact_id	None	The ID for outreach contact at the community level.
outreach_contact_id	None	The ID for each outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the sales action that took place.
toz	None	The total number of seconds logged per activity.

dtl_outreach_address_table

Table with outreach address attributes.

Column	Type	Description
address_id	None	Unique ID for the outreach address.
outreach_contact_address_line_one	None	Line one of the address.
outreach_contact_address_line_two	None	Line two of the address.
outreach_contact_city	None	City of the address.
outreach_contact_state	None	State of the address.
outreach_contact_postal_code	None	Postal code of the address.
outreach_contact_country_name	None	Country name of the address.

dtl_outreach_company_table

Table with outreach company attributes.

Column	Type	Description
outreach_company_id	None	Unique ID for the outreach company.
address_id	None	The ID for the outreach contact address. Connect to dtl_outreach_address_table to get sale details.
outreach_company_name	None	The name of the outreach company.
outreach_company_phone_number	None	The phone number of the outreach company.
outreach_company_fax_number	None	The fax number of the outreach company.
outreach_company_website	None	The website for the outreach company.
outreach_company_is_active	None	Boolean indicator indicating whether or not outreach company is active.
outreach_company_category_name	None	The outreach company category.

dtl_outreach_contact_table

Table with outreach contact attributes.

Column	Type	Description
outreach_contact_id	None	Unique ID for the outreach contact.
outreach_company_id	None	The ID for the outreach company. Connect to dtl_outreach_company_table to get sale details.
address_id	None	The ID for the outreach contact address. Connect to dtl_outreach_address_table to get sale details.
outreach_contact_first_name	None	Outreach contact first name.
outreach_contact_last_name	None	Outreach contact last name.
outreach_contact_title	None	Outreach contact title.
outreach_contact_stage	None	The stage the outreach contact is in (e.g Developing, New, Established).
outreach_contact_best_way_to_reach	None	The best way to reach the outreach contact.
outreach_contact_last_contact_date	None	The last date the outreach contact was contacted.
outreach_contact_next_contact_date	None	The next date the outreach contact is scheduled to be contacted.
is_outreach_contact_do_not_contact	None	Binary indicator indicating whether or not outreach contact can be contact (0=Yes, 1=No).
outreach_contact_gender	None	Outreach contact gender.
outreach_contact_salutation	None	Outreach contact salutation.
outreach_contact_initial_leasing_counselor_id	None	The ID for the outreach contact's initial leasing counselor.

dtl_person_table

Table with person attributes. A person is unique to a prospect at the company level.

Column	Type	Description
person_id	None	Unique ID per person per prospect_id.
prospect_id	None	Unique ID per prospect at the company level.
first_name	None	Person first name.
last_name	None	Person last name.
gender	None	The gender of the person.
age	None	The age of the person at time of entry of the person.
birth_day	None	The day of birth.
birth_month	None	The month of birth.
birth_year	None	The year of birth.
birthday_us_format_date	None	Birthday in m/d/yyyy format.
birthday_full_date	None	The birthday in yyyy-mm-dd format.
deleted_date	None	The date the person_id/row was soft deleted.
created_date	None	The date the person/row of data was created.
updated_date	None	The date the person/row of data was updated.
salutation	None	Salutation (Mr., Mrs., etc..)
created_by_id	None	Sherpa_user_id that created the row of data.
updated_by_id	None	Sherpa_user_id that updated the row of data.
person_rank	None	Rank for displaying persons per prospect_id in the correct creation order.
data_synced_date	None	Date the data was synced from MySQL to Snowflake.

dtl_sale_table

Table with sale outcome attributes.

Column	Type	Description
sale_id	None	Unique ID for the sale.
sales_note_id	None	The unique ID for the sales note.
first_person_id	None	Distinct id given to first person (min person id) at the community level. Can connect to the dtl_person_table for details.
second_person_id	None	Distinct id given to second person (max person id) at the community level. Can connect to the dtl_person_table for details.
sale_unit_id	None	Distinct id given to each unit at the company level. Can connect to dtl_unit_table to get unit details.
sale_due_date	None	The date that the payment for the sale was due.
sale_paid_date	None	The date that the payment for the sale was made.
sale_amount	None	The amount the sale is for.
sale_cancelled_date	None	The date that the sale was cancelled if applicable.

dtl_sales_activities_valid_table

Detail table containing all sales activities by day, community, prospect id and community prospect id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
is_skilled	None	Flag for determining if a community is a Skilled Nursing Facility or not. Will always be a true or false flag.
is_active	None	Flag for determining if a community is active or now. Will always be a true or false flag.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Unique ID of the sales note sales action type level.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
duration	None	The total number of seconds logged per activity.

dtl_sales_note_table

Sales note detail table. NOTE: This table may contain sales notes that have been deleted. To determine if a sales note has been deleted, please refer to the fct_sales_note_deleted_table.

Column	Type	Description
sales_note_id	None	Unique ID for the sales note.
description	None	The contents of the sales note.
description_markdown	None	The contents of the sales note in markdown format.
entry_datetime_utc	None	Datetime the user entered for the sales activity in UTC.
entry_datetime_local_calculated	None	Datetime the user entered for the sales activity in the local timezone of the community.
created_date	None	UTC timestamp of the datetime the sales note was created.
created_by_id	None	ID of the user who created the sales note. Note that this is possibly different from the leasing counselors attributed to the activity.
updated_date	None	UTC timestamp of the datetime the sales note was last updated.
updated_by_id	None	ID of the user who last updated the sales note.
lost_lead_reason_comment	None	If lost lead, additional info on why the lead was lost.
denial_reason_comment	None	If denial, additional info on why the lead was denied.
status_pending_reason	None	If status is pending, the reason the lead is pending.
status_pending_reason_comment	None	If status is pending, additional info on why the lead is pending.
next_steps	None	The next steps strategy for the lead.
next_steps_markdown	None	The next steps strategy for the lead in markdown formal.

dtl_sherpa_user_table

This table has the details for all active leasing counselors. If a user is falg as being a leasing counselor at multiple communities they will have multiple records.

Column	Type	Description
sherpa_user_id	None	Unique ID tied to a user within the database.
enabled	None	The user is enabled within the company.
last_login	None	The most recent date that the user has logged into the system.
first_name	None	The users first name.
last_name	None	The users last name.
full_name	None	The users full name.
email	None	The users email address.
job_title	None
hire_date	None	The date that the user was hired within the company.
linkedIn	None	The users LinkedIn profile URL.
facebook	None	The users Facebook profile URL.

dtl_unit_table

This table will hold all details on a unit at a single point in time (current unit details).

Column	Type	Description
unit_id	None	Unique ID tied the individual unit.
community_id	None	ID for each community within the company.
community_name	None	The name of the community that the unit belongs to. This can be blank if the community has been deleted.
floor_name	None	The floor that the unit is on.
unit_style_id	None	The ID that correlates to the type of unit this is.
unit_style_name	None	The full name of the style of unit.
unit_care_type_id	None	The id that associates the unit to its care type.
unit_care_type_name	None	The care type assigned to the unit.
unit_care_type_category_id	None	The id that associates unit care type to the parent care type category.
unit_care_type_category_name	None	The care type name assigned to the parent care type category.
unit_name	None	The name that the unit has been assigned.
market	None	The market value of the unit.
cost	None	The cost of the unit.
is_off_census	None
additional_info	None	Additional information regarding the unit.
notes	None	Notes related to the unit, i.e. if it is a model, combo, etc...
square_feet	None	How many square feet make up the room.
number_of_baths	None	How many baths a unit has.
has_washer_and_dryer	None	Boolean data point flagging if the unit has a washer and dryer.
renovation_date	None	If the unit has been renovated, on what day did that happen.
has_patio_or_balcony	None	Boolean data point flagging if the unit has a patioor balcony.
has_premium_view	None	Does the unit have a premium view?
is_pets_allowed	None	Are pets allowed in this unit.
is_display	None	Is the unit a display unit?
second_occ_cost	None
level_of_care_cost	None	The cost for the level of care.
incentive_cost	None	The costs of the incentives for the unit.
other_cost	None	Other cost associated with the unit.
created_date	None	The date that the record was made.
updated_date	None	If the record has been updated, this is the day that update happened.
unit_floor_id	None	The floor id that the unit is on.
unit_type_id	None	The id that related to the type that the unit is assigned to.
floor_plan_id	None	Id associated to the floor plan of the unit.
created_by_id	None	The user id of the person or process that created the record.
updated_by_id	None	The user id of the person or process that updated the record.
deleted_date	None
archived_date	None	The date that the unit was archived.
archived_by_id	None	The user id of the person or process that arvhived the record.
unit_type_name	None	Denotes what type of unit this is, i.e. single, shared, etc...
max_occupants	None	The total number of occupants that the unit can hold.

fct_active_leasing_counselor_table

This table shows all users that are enabled or have a minimum activity date tied to a sales note action.

Column	Type	Description
sherpa_user_id	None	Unique ID tied to a user within the database.
community_id	None	The unique ID for each community within a company.
user_is_enabled	None	The user is currently enabled within the company.
min_activity_date	None	The first recorded date that the user has a sales note date recorded at the community.
max_activity_date	None	The most recent recorded date that the user has a sales note date recorded at the community.

fct_community_activity_table

An offboard date does not exist in Sherpa at the community level. The Max activity date from Sales Note will determine offboard date.

Column	Type	Description
community_id	None	Distinct ID associated to each community in a company.
community_onboard_date	None	Date community onboarded with Sherpa. If a community is not currently active, this can be NULL.
community_is_active	None	Boolean for if a community is active or not.
min_activity_date	None	first activity associated with a sales note creation.
max_activity_date	None	Last activity associated with a sales note update date. It shows when a community was last active in Sherpa.

fct_community_prospect_care_types_table

fct_deposits_users_table

Fact table showing all users that have created a deposit record and the corresponding wait list entry id to be joined on.

Column	Type	Description
wait_list_entry_id	None	Unique ID for the wait list entry.
created_by_id	None	The unique ID for the person that created the wait list entry.

fct_inactive_leasing_counselor_table

This table shows all users that are to a sales note action that do not have a record in stg_leasing_counselor.

Column	Type	Description
sherpa_user_id	None	Unique ID tied to a user within the database.
community_id	None	The unique ID for each community within a company.
user_is_enabled	None	Indicates whether the user is currently enabled within the company.
min_activity_date	None	The first recorded date that the user has a sales note date recorded at the community.
max_activity_date	None	The most recent recorded date that the user has a sales note date recorded at the community.

fct_inquiry_sources_community_prospects_initial_table

Fact table showing the initial inquiry date for all community prospects.

Column	Type	Description
community_prospects_source_id	None	ID of the community prospect source record.
inquiry_source_id	None	ID of the associated inquiry source.
origin_community_id	None	The community that a prospect was shared from.
outreach_contact_id	None	ID of the outreach contact who refferred this prospect.
is_physician	None	Boolean for checking if the outreach contact is a Physician or not.
community_id	None	Unique id for all communities within a company.
community_prospect_source_created_date	None	The date that the row record was created.
community_prospect_source_updated_date	None	The date that the source record was last updated.
community_prospect_id	None	ID for prospects within a community.
source_category_id	None	category_source_id
created_by_id	None	unique ID of individual that created the record.
updated_by_id	None	unique ID of individual that updated the record
data_synced_date	None	The most recent date-time stamp that the table was updated.
prospect_id	None	Unique ID for the prospect at the company level.
prospect_inquiry_source_row	None	The flag to determine if this was the initial inquiry source by prospect id.
community_prospect_inquiry_source_row	None	Flag to determine if this was the initial inquiry source by the community prospect id, this should always equal 1.

fct_inquiry_sources_community_prospects_reinquiry_table

This table can be used to find where a reinquiry took place at the prospect and community prospect level.

Column	Type	Description
community_prospects_source_id	None	ID of the community prospect source record.
origin_community_id	None	The community that the initial inquiry took place.
community_id	None	ID of the community in which the reinquiry took place.
community_prospect_id	None	ID of the community prospect associated with this reinquiry.
prospect_id	None	ID of the prospect associated with this reinquiry.
inquiry_source_id	None	ID used to identify the source of the reinquiry
inquiry_date	None	The date that the reinquiry took place.
created_by_id	None	The id of the person that created the record.
updated_by_id	None	The id of the person that updated the record.
community_prospect_inquiry_source_row	None	Auto-incremented row number for this reinquiry based on creation date.

fct_leads_new_table

Fact table showing all new leads.

Column	Type	Description
community_prospects_source_id	None	ID of the community that the initial inquiry source is related to.
inquiry_source_id	None	ID of the associated inquiry source.
origin_community_id	None	The community that a prospect was shared from.
outreach_contact_id	None	ID of the outreach contact who refferred this prospect.
is_physician	None	Boolean for checking if the outreach contact is a Physician or not.
community_id	None	The unique identifier for the community within a company.
community_prospect_source_created_date	None	The date that the row record was created.
community_prospect_source_updated_date	None	The date that the source record was last updated.
community_prospect_id	None	ID for prospects within a community.
source_category_id	None	category_source_id
created_by_id	None	unique ID of individual that created the record.
updated_by_id	None	unique ID of individual that updated the record
data_synced_date	None	The most recent date-time stamp that the table was updated.
prospect_id	None	Unique ID for the prospect at the company level.
prospect_inquiry_source_row	None	The flag to determine if this was the initial inquiry source by prospect id.
community_prospect_inquiry_source_row	None	Flag to determine if this was the initial inquiry source by the community prospect id, this should always equal 1.
active_date	None

fct_leads_qualified_table

Fact table showing all qualified leads.

Column	Type	Description
community_id	None	Unique ID of a community within a company.
community_prospect_id	None	Unique ID of a prospect at the community level.
prospect_id	None	Unique ID of a prospect at the company level.
active_date	None	Timestamp that a prospect moved into active status.

fct_leads_worked_table

Use this model to calculate worked leads for both clinical and non-clinical communities. This table should be joined to when trying to understand when a qualified lead was worked with a valid sales note. Note that when using this table, the only unique value is the sales_note_id. All other columns may have multiple entries. When searching for worked leads in a timeframe, you will likely want to use a CTE to get distinct community_prospect_ids and then joining to other resources.

Column	Type	Description
community_id	None	Unique ID of a community within a company.
community_prospect_id	None	Unique ID of a prospect at the community level.
prospect_id	None	Unique ID of a prospect at the company level.
active_date	None	Timestamp that a prospect moved into active status.
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
sales_note_id	None	The unique ID for the sales note.

fct_occupancy_event_move_ins_table

All move-ins that have not been deleted or cancelled. Does NOT include transfers.

Column	Type	Description
occupancy_event_id	None	The unique id for each event that has happended.
person_id	None	The person level on which the event took place
occupant_group_id	None	The unique id for the occupant group this person is associated with.
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
event_date	None	The date that the move-in took place.
event_type	None	The occupancy event that took place. This should always be move-in.
unit_id	None	The ID of the unit that the move-in occurred in.
move_in_created_date	None	Date that Sherpa user created a move-in record.

fct_occupancy_event_move_outs_table

All move-outs that have not been deleted or cancelled. Does NOT include transfers.

Column	Type	Description
occupancy_event_id	None	The unique id for each event that has happended.
parent_occupancy_event_id	None	The occupancy event id of the corresponding move-in record for this move-out.
person_id	None	The person level on which the event took place
occupant_group_id	None	The unique id for the occupant group this person is associated with.
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
event_date	None	The date that the move-out took place.
event_type	None	The occupancy event that took place. This should always be a move-out.
unit_id	None	The ID of the unit that the move-out occurred in.
move_out_created_date	None	Date that Sherpa user created a move-out record.

fct_occupancy_event_transfer_ins_table

All transfer-ins that have not been deleted or cancelled. Does NOT include moves.

Column	Type	Description
occupancy_event_id	None	The unique id for each event that has happended.
person_id	None	The person level on which the event took place
occupant_group_id	None	The unique id for the occupant group this person is associated with.
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
event_date	None	The date that the transfer-in took place.
event_type	None	The occupancy event that took place. This should always be transfer-in.
unit_id	None	The ID of the unit that the transfer-in occurred in.
transfer_in_created_date	None	Date that Sherpa user created a transfer-in record.

fct_occupancy_event_transfer_outs_table

All transfer-outs that have not been deleted or cancelled. Does NOT include moves.

Column	Type	Description
occupancy_event_id	None	The unique id for each event that has happended.
person_id	None	The person level on which the event took place
occupant_group_id	None	The unique id for the occupant group this person is associated with.
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
event_date	None	The date that the transfer-out took place.
event_type	None	The occupancy event that took place. This should always be transfer-out.
unit_id	None	The ID of the unit that the transfer-out occurred in.
transfer_out_created_date	None	Date that Sherpa user created a transfer-out record.

fct_occupancy_event_trial_stays_table

All trial-stays that have not been deleted or cancelled. Does NOT include moves or transfers.

Column	Type	Description
trial_stay_id	None	The unique id for each event that has happended.
person_id	None	The person level on which the event took place
prospect_id	None	Unique ID at the prospect level.
community_prospect_id	None	Unique ID of a community prospect id.
community_id	None	Unique ID for the community within a company.
event_start_date	None	The date that the trial-stay began.
event_end_date	None	The date that the trial-stay ended.
unit_id	None	The ID of the unit that the trial-stay occurred in.

fct_outcomes_advance_table

Fact table showing all advance outcomes that happened within a certain timeframe.

Column	Type	Description
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
community_id	None	The unique ID for each community within a company.
sales_note_id	None	The ID for the sales note.
sales_note_date	None	The date that the sales note happened.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
advance	None	Shows that this outcome was an advance.
advance_reason_comment	None
advance_reason_id	None
advance_reason_name	None	Corresponding name that is tied to the advance reason. This has exmaples such as Trust-Breakthrough, Scheduled Tour, Other, etc...
advance_description	None	This will always contain a value of prospect or referral.

fct_outcomes_deposits_people_table

Unique person list that are tied to a deposit.

Column	Type	Description
wait_list_entry_id	None	Unique Id for when a person is placed on the wait list.
person_id	None	Identifier for each person within the dataset.
community_prospect_id	None	Id for the prospect at the community level.
community_id	None	Unique ID for the community within a company.

fct_outcomes_deposits_table

Table showing all deposits that happened within a community broken down by the wait list entry id.

Column	Type	Description
deposit_date	None	The day that the deposit was made.
wait_list_entry_id	None	The unique id for when a prospect is placed on a wait list.
community_id	None	Unique ID for the community within a company.
deposit_amount	None	The amount deposited.
deposit_level_id	None	Unique ID for each deposit.
outcome_id	None	Unique id for the outcomes that are in the table.

fct_outcomes_sales_table

This table shows all paid sales down to the community prospect level. In order to aggregate to a number that represents the same thing as the sales option under outcomes in the UI, you will need to aggregate at the distinct sales note id level and make sure that the sale cancelled field is null. The date range for aggregation must be based on the sale paid date field.

Column	Type	Description
entry_datetime_local_calculated	None	The time that the record was recorded in local time.
entry_datetime_utc	None	The time that the record was recorded in UTC time.
community_id	None	Unique ID for the community within a company.
sales_note_id	None	Identifier for sales note that has been recorded.
sales_note_date	None	The date that the sales note was recorded.
community_prospect_id	None	Identifier for a prospect at the community level.
prospect_id	None	Identifier for a prospect.
sale_paid_date	None	The date that the payment for the sale was made.
sale_cancelled_date	None	The date that the sale was cancelled if applicable.

fct_outreach_activities_appointment_table

Fact table showing all appointment outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_attend_event_table

Fact table showing all attend event outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_call_in_table

Fact table showing all call in outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_call_out_table

Fact table showing all call out outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	Dd associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_creative_follow_up_table

Fact table showing all creative follow up outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_drop_by_visit_table

Fact table showing all drop by visit outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_email_in_table

Fact table showing all email in outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_email_out_table

Fact table showing all email out outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_group_presentation_table

Fact table showing all group presentation outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_mail_out_table

Fact table showing all mail out outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_on_site_tour_table

Fact table showing all on site tour outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_planning_rcs_table

Fact table showing all planning-rcs outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_text_in_out_table

Fact table showing all text in/out outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_valid_by_user_table

Fact table showing all valid outreach activities by sherpa user.

Column	Type	Description
sales_note_id	None	The ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
sales_action_type_name	None	sales action type name
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
community_id	None	The unique ID for each community within a company.
sales_note_sales_action_type_user_id	None	user id of who selected the sales action type
additional_action_bool	None	flag for if an additional action was selected
duration	None	Total number seconds logged per activity.
advance	None	Details what the advance outcome was.

fct_outreach_activities_valid_table

Fact table showing all valid outreach activities for prospects.

Column	Type	Description
sales_note_id	None	The ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
sales_action_type_name	None	sales action type name
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
community_id	None	The unique ID for each community within a company.
outreach_contact_id	None	The ID for the outreach contact.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
community_outreach_action_type_rank	None	rank of sales notes by sales action type name and after a Community Prospect's active_date
additional_action_bool	None	flag for if an additional action was selected
duration	None	Total number seconds logged per activity.

fct_outreach_activities_video_message_table

Fact table showing all video message outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_activities_virtual_tour_table

Fact table showing all virtual tour outreach activities down to community outreach contact id.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.
outreach_contact_id	None	The ID for the outreach contact.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
outreach_action_type_name	None	The name of the action that took place.
outreach_action_type_name_detail	None	The additional level of detail for the outreach action that took place.
community_outreach_action_type_rank	None	Rank of sales notes by outreach action type name and ordered by the local entry datetime.
duration	None	Total number seconds logged per activity.

fct_outreach_sales_notes_valid_table

Fact table showing all valid sales notes for outreach activites.

Column	Type	Description
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
sales_note_id	None	The unique ID for the sales note.
community_id	None	The unique ID for each community within a company.
outreach_contact_id	None	The ID for the outreach contact.
community_outreach_contact_id	None	The ID for the outreach contact at the community level.

fct_sales_activities_appointment_table

Fact table showing all appointment sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.

fct_sales_activities_assessment_table

Fact table showing all assessment sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Unique ID of the sales note sales action type level.
sales_action_type_name	None	The name of the action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_call_in_table

Fact table showing all call in sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Unique ID of the sales note sales action type level.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_call_out_table

Fact table showing all call out sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_creative_follow_up_table

Fact table showing all creative follow up sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_email_in_table

Fact table showing all email in sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_note_sales_action_type_id	None	Unique ID of the sales note sales action type level.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_email_out_table

Fact table showing all email out sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.

fct_sales_activities_event_table

Fact table showing all home visit sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_home_visit_table

Fact table showing all home visit sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_mail_in_table

Fact table showing all mail in sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_mail_out_table

Fact table showing all mail out sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_pcs_table

Fact table showing all pcs sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_text_in_out_table

Fact table showing all text in/out sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_tour_table

Fact table showing all tour sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_valid_by_user_table

Fact table showing all valid sales activities for prospects.

Column	Type	Description
sales_note_id	None	The ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
sales_action_type_name	None	sales action type name
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_sales_action_type_user_id	None	The ID for each user that is associated with a sales note sales action type.
community_prospect_action_type_rank	None	rank of sales notes by sales action type name and after a Community Prospect's active_date
additional_action_bool	None	flag for if an additional action was selected
duration	None	Total number seconds logged per activity.

fct_sales_activities_valid_table

Fact table showing all valid sales activities for prospects.

Column	Type	Description
sales_note_id	None	The ID for the sales note.
sales_note_sales_action_type_id	None	Id associated with the sales note action.
sales_action_type_id	None	id associated with the sales_action_type_id
sales_action_type_name	None	sales action type name
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
community_prospect_action_type_rank	None	rank of sales notes by sales action type name and after a Community Prospect's active_date
additional_action_bool	None	flag for if an additional action was selected
duration	None	Total number seconds logged per activity.

fct_sales_activities_video_message_table

Fact table showing all video message sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_virtual_tour_table

Fact table showing all virtual sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_activities_walk_in_table

Fact table showing all walk in sales activities down to community prospect id level.

Column	Type	Description
date	None	The local time that the event took place.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.
sales_note_id	None	The unique ID for the sales note.
sales_action_type_name	None	The name of the action that took place.
sales_action_type_name_detail	None	The additional level of detail for the sales action that took place.
community_prospect_action_type_rank	None	Rank built by the community prospect id and action type name to differentiate between initial and return events where applicable.
duration	None	Total number seconds logged per activity.

fct_sales_note_deleted_table

Fact table showing both hard-deleted and soft-deleted sales notes.

Column	Type	Description
sales_note_id	None	The id of the deleted sales note.
deleted_date	None	The timestamp that the sales note was deleted. Note that in the case of a hard-deletion this timestamp is approximate.
data_synced_date	None	The timestamp that the sales note was synced to the data warehouse.

fct_sales_note_invalid_table

Fact table showing all invalid sales notes for prospects.

Column	Type	Description
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
sales_note_id	None	The unique ID for the sales note.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.

fct_sales_note_user_table

Fact table showing all users that have created a sales note. Note that this does not equate to a sales acivity. For example, a user may create a sales note and attribute the activity to another leasing counselor.

Column	Type	Description
sales_note_id	None	The id of the sales note the user created.
created_by_id	None	The user id of the sales note creator.

fct_sales_notes_valid_table

Fact table showing all valid sales notes for prospects.

Column	Type	Description
entry_datetime_utc	None	The UTC datetime stamp that the user entered.
entry_datetime_local_calculated	None	The local datetime stamp that the user entered.
sales_note_id	None	The unique ID for the sales note.
community_id	None	The unique ID for each community within a company.
community_prospect_id	None	The ID for each prospect within a community within a company.
prospect_id	None	The ID for each prospect within a company.

hst_stage_by_community_prospect_table

This model is meant to show the progression that a community prospect goes through from a stage standpoint. Each record shows a stage that the prospect entered, the date that the prospect exited that stage, what the stage was, how many FULL days the prospect was in that stage, and if a sales activity happened while the prospect was in a certain stage, how many seconds were spent during that time. For a record to appear in this view/table the stage_id cannot have a NULL value upon entry.

Column	Type	Description
community_prospect_id	None	Unique ID for the prospect at the community level.
community_id	None	Unique ID for the community that the prospect belonged to.
stage_rank	None	This is a rank function of the order in which a prospect moved through stages. The rank looks at each community_prospect_id and then ranks them by the stage_start_date data point.
stage_start_date	None	This is the date that the prospect entered the stage of that the record row represents. This date and time are based in UTC.
stage_end_date	None	This is the date that the prespect was moved into the next stage in their timeline. If this stage is their current one this date will always be the CURRENT_DATE() that the data was refreshed.
stage_id	None	The ID that is assigned to each stage within the database.
stage_name	None	The name assigned to the stage_id (Assess, Thinking, Planning, Action, etc...)
days_in_stage	None	The total number of full days that a prospect was in the row records stage. If the stage is the most recent record the differenct is based on the stage_start_date and the CURRENT_DATE() that the data was refreshed. If the prospect was moved from one stage to another within the same day, this will show a zero value.
tsz_in_seconds	None	The total amount of time in seconds that were recorded on valid sales activities between the stage_start_date and the end_stage_date (down to the time stamp as well.) This is matched on when the sales activity was recorded in UTC time to align with the other dates being in UTC as well. When no valid sales activity has been recorded between the two dates, then the TSZ will show a zero value.

hst_status_by_community_prospect_table

This model is meant to show the progression that a community prospect goes through from a status standpoint. Each record shows a stage that the prospect entered, the date that the prospect exited that stage, what the stage was, how many FULL days the prospect was in that stage, and if a sales activity happened while the prospect was in a certain stage, how many seconds were spent during that time. For a record to appear in this view/table the stage_id cannot have a NULL value upon entry.

Column	Type	Description
community_prospect_id	None	Unique ID for the prospect at the community level.
community_id	None	Unique ID for the community that the prospect belonged to.
status_rank	None	This is a rank function of the order in which a prospect moved through stuses. The rank looks at each community_prospect_id and then ranks them by the status_start_date data point.
status_start_date	None	This is the date that the prospect entered the status that the record row represents. This date and time are based in UTC.
status_end_date	None	This is the date that the prespect was moved into the next status in their timeline. If this status is their current one this date will always be the CURRENT_DATE() that the data was refreshed.
status_id	None	The ID that is assigned to each status within the database.
status_name	None	The name assigned to the status_id (Active, Futured, Depositor, Resident, Delete, etc...)
display_name	None	The name that is displayed throughout sherpa for reporting for this status (i.e. Delete would be Lost Lead.)
days_in_status	None	The total number of full days that a prospect was in the row records status. If the status is the most recent record the difference is based on the status_start_date and the CURRENT_DATE() that the data was refreshed. If the prospect was moved from one status to another within the same day, this will show a zero value.
tsz_in_seconds	None	The total amount of time in seconds that were recorded on valid sales activities between the status_start_date and the status_end_date (down to the time stamp as well.) This is matched on when the sales activity was recorded in UTC time to align with the other dates being in UTC as well. When no valid sales activity has been recorded between the two dates, then the TSZ will show a zero value.

hst_unit_table

Unit history by day. Remove all data entries that had $0 for market rates.

Column	Type	Description
unit_date_key	None	unique id of date and unit id. Each key is unique to one row.
date	None	date by day
unit_id	None	distinct id associated with a unit
community_id	None	distinct id associated with a community
unit_style_id	None	distinct id associated with a unit style
unit_care_type_id	None	distinct care type id associated to a care type
photo_id	None	id associated to a unit photo
unit_name	None	Designator for each unit given by community admin
occupied	None	designator for if the unit is occupied
is_off_census	None	boolean for if a unit is off census
market	None	Market rate for the unit
cost	None	Cost of the unit
additional_info	None	any other attributes about the unit that need to be described
notes	None	Any notes assigned to unit
square_feet	None	square feet of the unit
number_of_baths	None	number of baths in a unit
number_of_beds	None	number of beds in a unit
has_washer_and_dryer	None	designator to describe if unit has a washer or dryer
renovation_date	None	date the unit was renovated
has_patio_or_balcony	None	designator to describe if unit has a patio or balcony
has_premium_view	None	designator to describe if unit has a good view
is_pets_allowed	None	boolean to say whether you can have a pet or not at the unit
is_display	None	boolean flag that tells if the unit is on display or not
batch_id	None	number associated with the JIRA ticket system
second_occ_cost	None	cost for second occupant
level_of_care_cost	None	cost of level of care at unit
incentive_cost	None	discount on the unit
other_cost	None	other cost associated to the unit
is_deleted	None	boolean for if unit is deleted
created_date	None	date unit record was created
updated_date	None	date unit record was updated
unit_type_id	None	id associated with a unit type
floor_plan_id	None	id associated with a floor plan
created_by_id	None	id associated with a Sherpa user. The user created the unit record
updated_by_id	None	id associated with a Sherpa user. The user updated the unit record.
resident_count	None	The number of individuals occupying the unit on the associated date.

marketing_attribution

These are the details of the Sherpaverse Marketing Attribution report.

Column	Type	Description
community_prospect_id	None	distinct id per prospect at a community
community_id	None	ID of the community
is_valid	None	boolean flag, is sales note valid. Created after prospect was active.
lost_lead_reason	None	reason marked by leasing counselor on why a prospect was given a lost lead status
lost_lead_reason_name	None	the descriptive name for why a prospect was given a lost lead status
prospect_id	None	distinct id per prospect at the company level
sales_note_date	None	date the sales note was created
is_from_on_deck	None	boolean flag, did the prospect come from on-deck.
active_date	None	date the community prospect became active
movein_date	None	date the community propsect moved in
created_date	None	date the prospect was created
inquiry_date	None	date the prospect inquiried
community_prospect_status	None	current status of the prospect
sales_action_type_id	None	id associated with an action type. To be used with a lookup table.
outreach_company_name	None	name associated to an outreach company
outreach_company_category_name	None	name associated to an outreach category
lost_lead_date	None	date the prospect status changed to lost lead
min_inquiry_source_flag	None	flag to determine if an inquiry source is the intial inquiry source
community_name	None	Name of the community
is_active	None	Boolean if community is active
is_skilled	None	Boolean if skilled nursing
onboard_date	None	Date the community onboarded with Sherpa

rpt_team_productivity_sales_activities

rpt_team_productivity_sales_activities_detail

rpt_team_productivity_sales_outreach_activities

rpt_team_productivity_sales_outreach_activities_detail

sales_analysis