fct_visits


Description

A visit has a lot of dimensions associated with it. They contain the membership being visited, the helper doing the visit, the community the visit is in, what services are being offered by the helper, what products these fall under and the contract that exists between the helper and the membership.

There’s a slight distinction between services and products offered in relation to a visit, read their individual descriptions for more information.

Scheduling

Visits are automatically created by the system from a recurring visit rule immediately, up to the end of the schedule rule as far as 3 months in advance. Visits further than 3 months in advance are setup by a daily routine. To view these rules for the specific visit, look at the schedule_rule_ids.

Finance

For each visit, there will at least be two invoices created as part of a billing run. One is created for the member on behalf of the helper, for the revenue amount. This is the visit invoice. The other is from the company to the helper, for the margin amount. It’s also known as the Platform Fee on the invoice. Tax is applied to these amounts.

When joining visits to invoices, the visits.id will be the invoices.source_id. There can be additional invoices and credit notes for adjustments/changes (where invoices.adhoc = True). A billing_run will collect all of the visits that were checked out within the time range specified in the database. Invoices, and invoice lines will be created for visits where the checked_out_at time is within the range. These invoices can be changed at the discretion of Finance. The columns here get an estimate of the visit charge, but the final value will change depending on any changes caused by Finance.

If a visit doesn’t join on invoice_lines.source_id, it’s because that visit hasn’t been processed by Finance.

Observations

There are a few things that raise observations due to a visit:

  • Certain cancellation reasons raise observations, e.g. if a helper cancels because the member is in hospital.
  • Certain check out notes raise observations. This is the Lookout algorithm at work.

Columns

Column Type Children Parents Comments
visit_id int8

The ID of the visit, e.g. 185039.
visit_link text

The link to the visit within Lookout.
state varchar

The state of the visit. One of scheduled, in_progress, canceled, complete. A schedule rule starts the scheduled flow of a visit. If a helper checks-in, this visit is now in-progress. If they checkout the visit is automatically completed. A visit is typically cancelled before a helper checks-in. At any state of this stage, staff members can Acquit or Unacquit any changes.
member_id int4

The ID of the associated member of this visit, e.g. 819.
member_profile_id int4

The profile ID of the associated member, e.g. 1203.
membership_id int4

The ID of the associated membership of this visit, e.g. 1402.
community_id int8
dim_communities.community_id Implied Constraint R

The unique community ID in which the visit took place, e.g. 84. DEPRECATED, join to dim_memberships and access the community_names field.
community_name text

The name of the community in which the visit took place, e.g. Brisbane West. DEPRECATED, join to dim_memberships and access the community_names field.
helper_id int4

The ID of the associated helper of this visit, e.g. 1704.
helper_profile_id int4

The profile ID of the associated helper of this visit, e.g. 2200.
helper_engagement_type varchar

The engagement type of the helper. One of employee, contractor
services text

Services are the tasks or activities being provided by the helper for the visit. Usually linked to the product/rate being charged for flexibility, but doesn’t have to be. As there are many services associated with a visit, this is a comma separated list. E.g. Companionship, Domestic Assistance.
beginning_at timestamp

The date and time the visit starts (in UTC), e.g. 2021-03-02 12:00:00.
ending_at timestamp

The date and time the visit ends at (in UTC), e.g. 2021-03-02 12:00:00.
beginning_at_mbrs_tz timestamp

The date and time the visit starts at in the member`s timezone, e.g. 2021-03-02 12:00:00.
ending_at_mbrs_tz timestamp

The date and time the visit ends at in the member`s timezone, e.g. 2021-03-02 12:00:00.
beginning_at_in_company_tz timestamp

The date and time the visit starts in the company timezone, e.g. 2021-03-02 12:00:00.
ending_at_in_company_tz timestamp

The date and time the visit ends in the company timezone, e.g. 2021-03-02 12:00:00.
checked_in_at timestamp

The date and time (in UTC) when the helper checked in for the visit, e.g. 2021-03-02 12:00:00.
checked_out_at timestamp

The date and time (in UTC) when the helper checked out for the visit, e.g. 2021-03-02 12:00:00.
members_timezone text

The timezone of the member where the visit took place. E.g. Australia/Brisbane.
checked_in_at_mbrs_tz timestamp

The date and time in the member`s time zone when the helper checked in for the visit, e.g. 2021-03-02 12:00:00.
checked_out_at_mbrs_tz timestamp

The date and time in the member`s time zone when the helper checked out for the visit, e.g. 2021-03-02 12:00:00.
company_timezone varchar

The company timezone. E.g. Australia/Brisbane.
checked_in_at_in_company_tz timestamp

The date and time in the company time zone when the helper checked in for the visit, e.g. 2021-03-02 12:00:00.
checked_out_at_in_company_tz timestamp

The date and time in the company time zone when the helper checked out for the visit, e.g. 2021-03-02 12:00:00.
rating int4

The rating given by the helper to the visit. One a scale of 0-5, e.g. 4.
canceled_at timestamp

The datetime (in UTC) when the visit was canceled, if applicable. E.g. 2021-03-02 12:00:00.
canceled_reason varchar

The reason for canceling the visit, if applicable. E.g. member_hospital.
created_at timestamp

The date and time when the visit was created (in UTC). E.g. 2021-03-02 12:00:00.
updated_at timestamp

The date and time when the visit was last updated (in UTC). E.g. 2021-03-02 12:00:00.
product_id int4
dim_sah_service_ids.id Implied Constraint R

The ID of the associated product. Services are what helpers offer to memberships on a visit, and products are assigned to these services. This is what determines the cost to the membership. Can be linked to codes for finance and claiming. Join these to product models to understand billing. E.g. 120.
previous_hcp_product_id int8
dim_products.product_id Implied Constraint R

The original hcp product id after the SAH mapping. This value only exists if the mapping has been completed, and is then the valid product_id on the visit before July 1st 2025. After this date the product_id is valid. E.g. 165.’
product text

The name of the associated product. Products are usually linked to the service, but for flexibility, don’t have to be. They can be linked to codes for finance and claiming. Use this to understand billing. E.g. Light Gardening.
product_funding_scheme_type varchar
product_quantity_type varchar

The quantity type of the associated product. This indicates how the product is charged. One of per_hour, per_visit.
contract_id int8
fct_contracts.contract_id Implied Constraint R

The ID of the associated contract between the helper and the membership, e.g. 1593.
contract_product_price numeric

The price of the associated product in the contract between the helper and the membership for this visit, e.g. 52.80.
contract_product_tenant_percent numeric

The company percentage of the associated product in the contract, e.g. 0.2.
contract_product_id int8

The ID of the specific contract and product between the helper and the member, e.g. 40928.
schedule_rule_id int4

The ID associated with a schedule rule. These are rules that surround recurring visits. E.g. 273.
provider_travel_distance int4

The distance it takes a helper to get to a visit, in kilometres. E.g. 8.
provider_travel_rate_id text

The ID for the rate of the provider travel by the helper to a visit. This rate is agreed on at an organisational or individual level. Charged to either the provider or the funder.
client_transport_rate_id int8

The ID for the rate of client transport. This rate is charged to clients/funders when visits have travel services. E.g. 98.
client_transport_distance int4

The distance in kilometres of client transport. E.g. 98.
client_transport_note text

The note associated with the client transport. E.g. Drove to grocery store.
provider_travel_distance_rate_id int8

The rate ID associated with the distance taken by the helper to the visit. E.g. 492.
provider_travel_time_rate_id int8

The rate ID associated with the time taken by the helper to the visit. E.g. 790.
provider_travel_time_rate_matches_time_rate bool

If the provider time rate matches matches the visit time rate. True or False.
provider_travel_calculated_distance int4

This is the lookout calculated distance, done at visit checkout, for a helper to get to the visit in kilometres, e.g. 15.
provider_travel_projected_distance int4

This is the prefilled lookout calculated distance for a helper to get to the visit in kilometers, e.g. 42.
shift_break text

Whether the helper has a break during this visit. True or False.
company_id int8
dim_companies.company_id Implied Constraint R

The company ID, e.g.: 345.
expected_cost_to_member numeric

This is an ESTIMATE only. It is the initial expected cost to the member. The derivation is as follows: for complete visits, if product_type is per_hour then price * actual_hours else price. The price is from the contract in place between the helper and member at the time of the visit.
predicted_cost_to_member numeric
expected_revenue numeric

This is an ESTIMATE only. It is the initial expected revenue to the company. The derivation is as followins: Contracted fgf_percent * expected_cost_to_member. If helper_engagement_type is employee, then this is 100% of expected_cost_to_member.
predicted_revenue numeric
planned_length_hours numeric

The planned length of the visit in hours. This is given by ending_at - beginning_at of the visit. E.g. 5.5.
planned_length_minutes numeric

The planned length of the visit in minutes. This is given by ending_at - beginning_at of the visit. E.g. 330.
actual_length_hours numeric

The actual length of the visit in hours. This is given by the helper checkout times: checked_out_at - checked_in_at of the visit. E.g. 5.5.
actual_length_minutes numeric

The actual length of the visit in minutes. This is given by the helper checkout times: checked_out_at - checked_in_at of the visit. E.g. 330.
provider_travel_time_minutes numeric

The time it takes a helper to get to a visit, in minutes, e.g. 17.
provider_travel_calculated_time_minutes numeric

This is the lookout calculated time, done at visit checkout, for a helper to get to the visit in minutes, e.g. 23.
provider_travel_rostered_time_minutes numeric

DEPRECATED in favour of provider_travel_projected_time_minutes - This is the prefilled lookout calculated time for a helper to get to the visit in minutes, e.g. 23.
provider_travel_projected_time_minutes numeric

This is the prefilled lookout calculated time for a helper to get to the visit in minutes, e.g. 23.

Indexes

Constraint Name Type Sort Column(s)
fct_visits_pkey Primary key Asc visit_id
fct_visits_beginning_at_mbrs_tz_idx Performance Asc beginning_at_mbrs_tz
fct_visits_company_id_idx Performance Asc company_id
fct_visits_helper_id_idx Performance Asc helper_id
fct_visits_member_id_idx Performance Asc member_id
fct_visits_membership_id_idx Performance Asc membership_id
fct_visits_schedule_rule_id_idx Performance Asc schedule_rule_id
fct_visits_state_idx Performance Asc state

Relationships

Close relationships within degrees of separation