Tables

This model represents the services associated with purchase order lines.

Information about the hostname(s) used for each company’s Lookout URL(s).

Information about each company.

Tickets that exist within Lookout. Tickets are used to enhance the workflow of a company by tracking responses and procedures to certain events.

Group Visits are a type of event.

Events can have one or more occurrences, each with multiple attending members. (E.g. An event can occur every Tuesday at 10 AM.)

This model combines data from events, event occurrences, and attendee records to offer insights into event performance, attendance, and scheduling patterns.

Use this model to: - Track overall event performance and attendance - Analyze event scheduling patterns - Monitor attendance rates and trends across events - Understand capacity utilization for events

The communities a helper is expected to work in. These can be multiple per helper.

A purchase order is created for third-party providers to order goods or services. Once a purchase order is generated for a third party, a PDF is sent to the relevant party and the order is placed. The associated fulfilment of this order is given in the fct_purchase_order_items model.

Information about a member’s attributes, their profile, and whether they have a help plan. This includes everything in dim_profiles for the member. A member can appear multiple times if associated with multiple memberships. Note also that some memberships can have more than one member. The distinction between a member and a membership is that a member is a role within a membership that is receiving the care.

A Survey in Lookout is a structured set of questions designed to gather information from respondents.

They are used to assess aspects such as health, mobility, and mental state, and can be accessed through the Lookout App.

Surveys can be customised to fit the specific needs of the organisation and are an integral part of the feedback loop within Lookout.

For a broader understanding of Surveys, please refer to the Lookout Manuals’ Survey Management article, which goes into detail about how Surveys work.

The responses to these Surveys help organisations understand the needs and experiences of their Members, allowing for better service delivery and support.

Purpose

This model is designed to answer the following questions:

1. What surveys are available for a company?
2. What are the details of a specific survey? (E.g.: survey_name, created_at, etc.)
3. How many questions are in a survey? (total_questions)
4. How often does the Survey recur? (recurrence_limit, recurrence_interval, recurrence_frequency)
5. How many Survey Requests have been sent out? (total_survey_requests)
6. How many Survey Requests have been completed? (total_survey_request_respondents)

Recurrence

Surveys can be set with recurring schedules, allowing them to be sent out at regular intervals. This setup includes:

• Recurrence Limit (recurrent_limit): The total number of times the survey is set to recur.
• Recurrence Interval (recurrence_interval): Defines the interval time period of recurrence.
• Recurrence Frequency (recurrence_frequency): Could be days, weeks, months, or years.

Questions

For a list of Survey Questions, see fct_survey_questions.

Survey Questions Responses

For a list of responses to Survey Questions, see fct_survey_question_responses.

The responses to these surveys help organisations understand the needs and experiences of their Members, allowing for better service delivery and support.

Services that have been carried out at visits. Multiple services can be carried out in a given visit.

Matching Preferences allow Lookout to best match customers with helpers that have suitable skills and experience. When members create a new match request, they pick the preferences most important to them. The more specific their match request is, the better Lookout can surface ideal candidates from the Marketplace.

Matching Preferences are made up of a Matching Preferences category. Within that category, category preferences are then added in.

These are service ids related to the new support at home supported services.

Transactions for HCP Membership accounts. This includes invoices for visits, fees, DHA subsidies and third-party orders. Transactions are only tracked if the membership has an open finance account (see dim_memberships).

Line items from invoices or credit notes from helpers to funders or other helpers.

Incidents are a ticket add-on. There are 4 steps to an incident. 1. An incident ticket is created. 2. The incident is then categorised. 3. The incident is actioned. 4. Summary of the incident. The fct_incidents model combines the base incident with the categorisation. Incidents do not include archived tickets.

Tickets can be assigned to users through the ticket system, which is reflected here as an association with their profile.

Leave periods for members from their home care package, most commonly for a hospital stay, transition care, or for a respite care period.

Contracts between helpers and memberships. Join to fct_contact_prices for products and agreed prices.

This table joins helper verification renewals with tickets.

A purchase order item entry is an individual service associated with the purchase order.

Defines tax rates (applicable to products, client transport rates, provider travel rates, etc.).

Cohorts are adhoc tags that help manage groups of members. You are able to assign multiple cohorts to a member.

Different roles that user profiles have with respect to their associated membership.

Leave periods for helpers from their work. These can be for annual leave, personal leave, and so on with reasons defined per-company.

Memberships, their funding details, and any other closely linked attributes. A membership has multiple individuals attached to it, these are members, funder and authorised representative. This contains all types of memberships and their funding plans within Lookout. It is normal for certain columns to be empty if your organisation doesn`t have any memberships within a given funding scheme.

Surveys are structured with a series of questions that the respondent answers.

Every Survey Question has a Question Type, which is one of:

1. multiple_choice - The respondent chooses one or more options from a list of choices.
2. text - The respondent writes a response in a text field.
3. scale-based questions - The respondent chooses an option from a minimum/maximum value range.
4. number - The respondent enters a number with an optional minimum/maximum value range.
5. date - The respondent chooses a date.

Survey Questions Model

fct_survey_questions is designed to answer the following questions:

1. What questions are in a survey?
2. What are the details of a specific question? (E.g.: question_name, question_type, question_instructions, etc.)
3. What is the minimum and maximum scale of the question, (if a scale-based question)? (question_scale_minimum, question_scale_maximum, etc.)
4. What are the options available for the question? (question_option_labels)

Information about membership plans including pricing, payment frequency, and management settings.

This model contains helpers, also known as care workers, as well as any third party providers that are employed or contracted. Helpers sign contracts with memberships, with information such as products and services offered and their pricing arrangements. This enables them to visit a specific membership. Helpers can have default prices that they wish to charge for products, which show up as defaults when setting up contracts, but can be overriden by the contract prices. A helper is a part of multiple communities.

Actions taken in response to incidents.

The budget aims to provide a structured plan for the income and expenditures of care delivery. There can be multiple budgets per membership, however only one is valid at a given time. There are a few elements associated with a budget. Income: There can be a lot of income sources for a budget. They can be, but are not limited to government subsidies, any unspent funds, income tested fees or any additional supplements or income. Expenses: These are care management expenses relating to the budget. E.g. Packagement Management Fee. Service planning: This represents the agreed spend by service between the client and the service provider.

Information about user profiles. These are all users within Lookout. They can be helpers, members, authorised representives, staffers, funders, etc. To see the specific role within a membership, join it to fct_membership_roles.

Invoices or credit notes from helpers to funders or other helpers. For example, it can be from a corporate helper to a funder (e.g. monthly fees, etc.), a care worker to funder (e.g. full visit cost), a corporate helper to a care worker (e.g. platform fees, claiming back companies portion), or a TPP Helper to a funder.

This table contains aggregated metrics, by month, relating to memberships.

Observations refer to events identified by Lookout that necessitate a response. Various system entities can raise observations. An observation with a state of ‘investigated’ implies that the staffer has: - Assigned relevant categories - Added actions to address the observation - Assigned a risk level from among ‘low’, ‘medium’, or ‘high’

Further details on how an observation is initiated are available in the source field documentation.

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.

These are the rules that are associated with recurring visits. The rules include the start and end time of a visit, the products being offered, and the associated members and helpers.

Products are offered by Care Workers on a visit. Companies have different ways of using this, which translate to different naming or descriptive conventions. Most are related closely to the services that helpers provide (e.g. Domestic Assistance, Nursing, etc.) along with any additional time considerations (e.g. 12 Hour Overnight Block). Products are directly related to how the Care Workers are paid, so some companies are more specific than others. They’re usually linked to the service a helper provides, but for flexibility don’t have to be. They can be linked to codes for finance and claiming.

Event Occurrences represent individual scheduled instances of an event.

Each event can have multiple event occurrences.

Attendees are members who have signed up for specific events at scheduled times. These events could be group activities, workshops, meetings, or any scheduled sessions offered by the company.

The fct_event_attendees model provides detailed information about members who have registered to attend events organised by the company. It combines data from attendee records, event schedules, and event details to offer a comprehensive view of event participation.

This model includes details such as the event ID, event title, member ID, attendance status, and timestamps related to their registration and cancellation.

Use this model to analyse event participation, track attendance trends, and understand member engagement.

Services a helper is able to provide. These can be multiple per helper.

Verifications helpers currently hold or have held (state is either expired or verified) and the type of evidence provided.

Communities are defined by the company and are a way to group memberships and helpers by region.

A linkage between products and their contracts.

Multiple todos can be added to each ticket through the ticket template. This is a series of steps that need to be completed after the ticket is created. For example, a step might be: Schedule a follow-up with the Member.

A Survey Question Response represents an individual answer provided by a respondent to a survey question in Lookout.

This model aggregates responses along with respondent roles and completion status, enabling analysis on survey results, gaining insights into the experiences and needs of their Members.

For more information on the Surveys Model, see fct_surveys.

Purpose

This model is designed to answer the following questions:

1. What responses have been provided to survey questions?
2. Who responded to the surveys, and what are their roles? (respondent_role)
3. When were the surveys completed? (survey_completed_at)
4. Were any exceptional options selected in the responses? (option_exceptional)

Respondent Roles (respondent_role)

Respondents can have various roles within the system:

• Member
• AuthRep
• BillingRecipient
• EmergencyContact
• Sharer
• Staffer

See the Glossary of Terms in the Lookout Manual for more information about roles.

Exceptional Responses

Some multiple-choice options can be marked as exceptional. The option_exceptional field indicates if the selected option was set as exceptional. This can help in identifying responses that may require further attention.

Limitation: The option_exceptional field does not surface exceptional responses to other question types at this time.

Survey Completion

The survey_completed_at field indicates when the survey was completed by the respondent. This can be used to track if the survey was completed, as to not report on incomplete surveys.

Text and Date Responses

Please note, text and date responses are not captured in fct_survey_responses, as they may contain Personal Identifiable Information (PII), and are therefore excluded. To access text and date responses, please use the Data Export Tool via the Lookout Admin App.

Related Models

• For details about Surveys, see fct_surveys.
• For details about Survey Questions, see fct_survey_questions.

Information related to who visits have been updated by and when they’ve been updated. Updates relate to check ins, check outs, cancellations and aquits

Meta information about the latest data refresh for Lookout Analytics.

This table is a utility table that contains helpeful generated dates and their corresponding day attributes.