Description of Key Tables, Terminology and Common KPIs With Examples

Overview

This article is intended for those that have received access to a replica/read-only version of the database and are attempting to connect a BI tool of their choice to access their own data. This article will give an introduction to some Terminology, common KPIs and which tables are used in calculating those KPIs. This document assumes the reader has an understanding of basic database design and of Primary Keys/Foreign Keys one-to-one, one-to-many, many-to-many relationships and how to aggregate data within a Postgresql database. This article will attempt to link the tables with common concepts.

Terminology Introduction

Company

Summary

This is the place where your company/franchise name and contact information is located. For the purpose of most reports, you'll probably reference the company name only from this table. However, if you ever need to convert a UTC timestamp to the timezone of the company the timezone is also found on this table. On this table, are a number of columns, most of them govern business logic that happens in the application. For the Scoreboard Report, it's also where the number of Vans Owned, Population is stored.

Tables

• company

Departments

Summary

There are several areas in the application that separate data by department. For example, its common to see revenue reported/broken out by department. The following tables are associated with departments

Tables

• department
  • List for the whole organization
• company_department
  • Companies can opt in if they want to use these departments or not
  • Goals by department are defined here
• route_company_department
  • Help ensure bookings in certain departments only go on the correct routes
• product_company_department
  • 98% of services can only be associated with one department; however, exceptions were made for the service fee(s) or the system promo code discount line items*
    • * This is planned to be changed at some point in the future and every line item will just go to one department.
• zone_company_department
  • Can define zone minimums by department
• user_account_company_department
  • Can define what routes a user sees by default when viewing the Schedule screen

Products and Services

Summary

The following tables are used to define a company's list of available services

Tables

• product
• product_code
  • 5 = Special promo code line item
    • There can only be one non-taxable/taxable version of these per company
  • 7 = Tip
    • When looking at an invoice and you don't want the tip, look for this code
• product_company_department
• required_product
  • If this is linked to a service, this means it's a "forced service" like the Water Treatment/Disposal Fee

Clients

Summary

A client is a collection of contacts and addresses and booking/invoice history. For commercial clients we store the company name and commercial type on the client record. 

Tables

• client
• client_contact
• client_contact_email
• client_contact_phone
• contact_phone_type
• client_service_address
• address
  • Note postal_code 
• zone_postal_code
  • Note can join from address to this table on the postal code to determine which zone in which an address resides
• zone
• client_tag
• tag
• client_comment
• comment

Bookings/Work/Invoices

Summary

A booking in its initial creation represents the services a customer originally requested at the time they scheduled the service. When the booking is opened up from the technician's view, conceptually, work has started on the booking. When the technician interacts with the line items they are modifying the final invoice that will be sent to the customer. When the technician accepts payment, the payment(s) is linked with the final invoice and closed out.

Tables

Original Booking

• client
  • company_id
• booking
  • client_id
  • service_contact_id
  • billing_contact_id
  • service_address_id
  • billing_address_id
  • source_item_id
  • source_value_id
• client_contact
• client_contact_email
• client_contact_phone
• address
• zone_postal_code
• zone
• booking_status_type
  • Note 1 = Upcoming, 3 = In progress, 5 = Completed
  • Note 2 = Cancelled, 4 = Lost Sale *
    • * More details around Lost Sales later
• booking_product
  • include_in_revenue
• product
  • include_in_revenue
  • include_in_upsell
• promo_code
• source_item
• source_value
• booking_tag
• tag

Final Invoice

• client
  • created_dt is a timestamp in UTC
• booking
  • created_dt is a timestamp in UTC
• booking_appointment
• work
• work_user
  • leader
    • communicates who was the primary technician
• user_account
• work_product
  • include_in_revenue
  • include_in_upsell
• product
  • include_in_revenue
  • include_in_upsell
• promo_code
• invoice
• payment
• payment_type
• payment_work_product
  • When refunds are issued, links a refund payment with one or more line items

Calls

Summary

Calls can be thought of events that occur by CSRs around creating or updating bookings in the system. There are different types of calls: Booked, Cancelled, Lost Sale, Reschedule, etc. The call tables store information about what happened at a moment in time for some booking. When a user presses the new call but doesn't actually take any action such as creating a booking or creating a lost sale, these call records default to "Undetermined"

 

Tables

• call
  • call_date/call_time
    • The combination of these two values is assumed to be in the timezone of the company. These are NOT in UTC time
• call_type
  • 1 = Booked
  • 4 = Lost Sale
  • 7 = Cancelled
  • 26 = Rescheduled
• call_product
  • For Booked, Lost Sale, Cancellations this is a snapshot of what the services looked like at the time this event happened, even if updates to the booking occur later
• booking

Online Reservation Requests/Bookings

Summary

When a user interacts with the online scheduler they are not created as clients or bookings in the system, but are stored in a separate location as they are not "clients" per se, but potential clients. When a reservation is converted into a real upcoming appointment, the reservation is linked with the booking record.

Online Commercial contact requests are stored separately from online reservation requests that are stored from customers.

Tables

Residential Requests

•  reservation
  • created_dt/created_time/visit_dt/visit_time
    • The combination of these values is assumed to be in the timezone of the company
  • created_dt_utc
    • This is a timestamp in UTC
• reservation_product
• product
• promo_code
• reservation_mode_type
• reservation_mode_step

Commercial Requests

• commercial_contact

Routes/Route Assignments

Summary

When appointments are scheduled, they at some point are placed on a route with a time. One or more users are assigned as a primary/secondary technician on some day. When an invoice on that route is closed, the primary technician of that route is assigned as the primary technician, or leader, on that invoice for future reporting.

Tables

• booking_appointment
• route_assignment
• route
• user_account

Routes/Route Closures/Route Closure Blocks

Summary

The following tables are used when Full Day Closures or Closure Blocks are created. One item of note, is when users select an arrival window from the online reservation screen or a CSR from the booking screen, a route closure block is created with the type "Pending Booking". A scheduled task runs and will remove any "Pending Bookings" that are older than 5 minutes.

Tables

• route
• route_closure
• route_closure_block

Reviews/NPS Feedback

Summary

A review that has been successfully imported from Listen 360 or from Podium will be stored in the review table. The score, comments will be available, and are linked up with the appointment/client in this system that completed the review

Tables

• review
• work
• booking
• client_contact
• client

Key Metrics Snapshot

Summary

This is a denormalized collection of various kpis by date and by department for all companies in the system.

Tables

• key_metrics_snapshot

Groups

Summary

If you want to run reports by group see the following tables

Tables

• organization_reporting_group
• organization_reporting_group_company
• company

Common KPIs With Examples

Total Revenue/Upsell/Adjustments/Unpaid Invoices

If I want to see the total revenue for some month my query would look something like this. In this example we also included ways you can get insight into upsell, adjustments or unpaid invoices easily.

-- Clients/Appointment Dates with Revenue for all departments
select
    zerorez.fn_client_contact_get_full_name(cc) as client_name, -- helper to display client's first/last name
    b.appointment_dt,
    zerorez.fn_format_address_as_text(a) as address,
    ba.booked_amount,
    wu.upsell,
    wa.adjustments,
    wr.revenue,
    t.subtotal,
    t.tax,
    t.total,
    -- if total_payments < total its an upaid invoice
    t.total_payments
from zerorez.client c
join zerorez.booking b on c.id = b.client_id
join zerorez.address a on b.service_address_id = a.id
join zerorez.client_contact cc on b.service_contact_id = cc.id
join zerorez.work w on b.id = w.booking_id
-- helper methods for common invoice metrics
left join zerorez.fn_booked_amount_inline(b.id, null) ba on ba.booking_id = b.id
left join zerorez.fn_work_revenue_inline(w.id, null) wr on wr.work_id = w.id
left join zerorez.fn_work_upsell_inline(w.id, null, b.id) as wu on wu.work_id = w.id
left join zerorez.fn_work_adjustments_inline(w.id, null) as wa on wa.work_id = w.id
left join zerorez.fn_invoice_amount_due_with_total_paid_work(w.id) t on t.work_id = w.id
where c.company_id = 575
and b.non_revenue_type_id is null
and b.appointment_dt BETWEEN '2022-01-01' AND '2022-01-31'
and b.booking_status_type_id = 5
order by 2, 1;

-- Get Company Department Ids for some company
select * from zerorez.fn_company_departments(575)

-- Clients/Appointment Dates with Revenue By Department
select
    zerorez.fn_client_contact_get_full_name(cc) as client_name, -- helper to display client's first/last name
    b.appointment_dt,
    sum(wr.revenue) as total_revenue
from zerorez.client c
join zerorez.booking b on c.id = b.client_id
join zerorez.client_contact cc on b.service_contact_id = cc.id
join zerorez.work w on b.id = w.booking_id
    -- helper method to just get the revenue, reference the work ID or pass in an array with a company department id
left join zerorez.fn_work_revenue_inline(w.id, array[92]) wr on wr.work_id = w.id
where c.company_id = 575
and b.non_revenue_type_id is null
and b.appointment_dt BETWEEN '2022-01-01' AND '2022-01-31'
and b.booking_status_type_id = 5
group by 1, 2
order by 2, 1;

Reviews

Here is the query that is used to get reviews by technician. This also illustrates a way that if you are seeing t_Data in your output, you may need to run a method to get the translation for that value. If you don't want to join all the way to the company table to get the language_locale_id you may consider just putting a 1 in for the English language locale.

-- Reviews By Technician
select
    zerorez.fn_user_account_get_full_name(ua) as primary_technician,
    zerorez.fn_client_contact_get_full_name(cc) as client_name,
    coalesce(t.translation_value, t.translation_key) as booking_type,
    b.appointment_dt,
    wr.revenue,
    r.completed_at,
    r.recommendation_likelihood,
    r.comments
from zerorez.review r
join zerorez.work w on r.work_id = w.id
join zerorez.work_user wu on w.id = wu.work_id and wu.leader
join zerorez.user_account ua on wu.user_account_id = ua.id
join zerorez.booking b on w.booking_id = b.id
join zerorez.client_contact cc on b.service_contact_id = cc.id
join zerorez.client c on b.client_id = c.id
join zerorez.company co on c.company_id = co.id
left join zerorez.non_revenue_type nrt on b.non_revenue_type_id = nrt.id
join zerorez.fn_get_translation(CASE WHEN b.non_revenue_type_id is null THEN 't_revenue' ELSE nrt.non_revenue_type END, co.language_locale_id) t on true
left join zerorez.fn_work_revenue(w.id, null) as wr on wr.work_id = w.id
where r.completed_at BETWEEN :startDate AND :endDate
and c.company_id = :companyId
order by 1, r.completed_at, b.appointment_dt, 2;

Calls

If you want the total number of Booked Calls or other metrics as defined in the Call Source report the query is a little more involved and you can reach out to engineering for specific examples of how that can be queried.

Permissions

Summary

You will have permission to select data on most tables with a few exceptions. We have removed the ability for your connection to access any api keys to external systems/credit card gateways and the ability to see encrypted versions of user passwords. We have removed the ability to see transaction specific identifiers to Credit Card Gateways. If you are trying to access data from either the organization, company, user_account, or payment tables and are having permission denied issues, please reach out to support.

Also, a policy has been created that you will only be able to see company and client data for the companies you have permission to see. If you try to view client contact, phone, email, invoices for clients in some other franchise they will not be returned.

If you feel that you are not able to see some information you can normally see through the main application, please reach out to support and we can review the policies and row level security and make adjustments if needed.

Examples

-- Permission examples

-- The following examples fail with permission denied errors
select * from zerorez.company;
select * from zerorez.user_account;
select * from zerorez.payment;

-- payments for some invoice on some client
select
    p.*
from zerorez.client c
     join zerorez.booking b on c.id = b.client_id
     join zerorez.work w on b.id = w.booking_id
     join zerorez.invoice i on w.id = i.work_id
     join zerorez.payment p on i.id = p.invoice_id
     join zerorez.payment_type pt on p.payment_type_id = pt.id
where c.company_id = 575
  and c.id = -- some client id here
  and b.id = -- some booking id here
;

-- Works
select id,name from zerorez.company; -- Note only returns companies you have access to
select id,first_name,last_name from zerorez.user_account;

-- payments for some invoice on some client
select
    pt.payment_type,
    p.amount,
    p.payment_type_id,
    p.check_number,
    p.cc_last_name,
    p.cc_last_four
from zerorez.client c
     join zerorez.booking b on c.id = b.client_id
     join zerorez.work w on b.id = w.booking_id
     join zerorez.invoice i on w.id = i.work_id
     join zerorez.payment p on i.id = p.invoice_id
     join zerorez.payment_type pt on p.payment_type_id = pt.id
where c.company_id = 575
  and c.id = -- some client id here
  and b.id = -- some booking id here
;

Long Running Queries

Since this is running on an AWS managed replica database, any updates that happen on the primary, transactional database are being replicated/copied to the replica database(s). It's not uncommon that if you get a long running query (30+ seconds) and the main transactional database needs to make an update to a row that is included in your query, AWS will kill your long running query so it can replicate the data and you may see an error similar to the following:

"Cancelling request due to conflict recovery"

If you consistently run into this error when trying to get data, consider narrowing the scope of your search.