Access to the ServiceTrade Data Warehouse is available only to ServiceTrade Enterprise customers.  For more information about ServiceTrade Enterprise, contact sales@servicetrade.com.

The ServiceTrade Data Warehouse contains several data sets, each of which has a number of fields.  Those datasets can be used to build visualizations with Amazon QuickSight and other BI tools.  This reference describes those datasets and the individual fields within each.

The following types of fields are supported:

  • text - A text value
  • comma-separated text - A list of text values, separated by commas
  • number - An integer or decimal number
  • datetime - A date and time
  • boolean - A true/false flag; 1 indicates true/yes, 0 indicates false/no

Jobs

The 'jobs' dataset contains information about the jobs in your ServiceTrade account.  Jobs in all statuses, including canceled jobs, are included in this dataset.

This dataset includes information about appointments and services on each job.  Appointments whose status is 'Canceled' are not included.  Services whose status is 'Canceled' or 'Void', or which are associated with canceled appointments, are not included.  An appointment's duration is the difference between its scheduled start and end times; for instance, an appointment which is scheduled to start at 10:00am and end at 12:30pm has a duration of 2.5 hours.

This dataset also includes information about clock events on each job.  A clock event's duration is the time elapsed between a given clock in and clock out for a given technician, appointment and activity type.

Fields:

id - The internal ID of the job [number]
job_number - The job number [number]
job_type - The job type [text]
status - The job's current status [text]
created - The date on which the job was created [datetime]
updated - The date on which the job was most recently updated [datetime]
completed_on - The date on which the job was marked complete [datetime]
customer - The name of the ServiceTrade company which is the customer for this job [text]
location - The name of the job's location [text]
location_state - The abbreviation of the state or territory of the job's location [text]
location_postal_code - The postal code of the state or territory of the job's location [text]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location_regions - A list of the names of the regions containing the job's location [comma-separated text]
location_latitude - The latitude of the job's location [number]
location_longitude - The longitude of the job's location [number]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the job [text]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the job [text]
job_estimated_price - The sum total of the estimated price of all services on the job [number]
job_estimated_duration - The sum total of the estimated duration of all services on the job [number]
job_cost - The sum total of the costs of all job items on the job [number]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_expected_margin - The difference between job_cost and job_invoice_amount [number]
job_actual_margin - The difference between job_cost and job_invoice_amount [number]
job_open_services_estimated_price - The sum total of the estimated price of all incomplete services on the job [number]
job_open_services_estimated_duration - The sum total of the estimated duration of all incomplete services on the job [number]
job_completed_services_estimated_price - The sum total of the estimated price of all completed services on the job [number]
job_completed_services_estimated_duration - The sum total of the estimated duration of all completed services on the job [number]
job_due_start - The beginning of the due window for the job [datetime]
job_due_end - The end of the due window for the job [datetime]
job_service_lines - A list of the names of the service lines associated with this job's services [comma-separated text]
appt_first_start - The date and time on which the first appointment on the job is scheduled to start [datetime]
appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
appt_last_end - The date and time on which the last appointment on the job is scheduled to end [datetime]
completed_appt_first_start - The date and time on which the first completed appointment on the job was scheduled to start [datetime]
completed_appt_first_end - The date and time on which the first completed appointment on the job was scheduled to end [datetime]
completed_appt_last_start - The date and time on which the last completed appointment on the job was scheduled to start [datetime]
completed_appt_last_end - The date and time on which the last completed appointment on the job was scheduled to end [datetime]
appt_count - The number of appointments on the job [number]
completed_appt_count - The number of completed appointments on the job [number]
technician_count - The number of technicians on the job [number]
is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
is_completed - A flag indicating whether the job has been marked as complete [boolean]
all_appts_completed - A flag indicating whether all the appointments on the job are complete [boolean]
all_appts_completed_in_due_window - A flag indicating whether all the appointments on the job were completed within the job due window; that is, completed_appt_first_start is later than job_due_start, and completed_appt_last_end is earlier than job_due_end [boolean]
completed_in_due_window - A flag indicating whether the job was marked complete before the end of the job due window; that is, completed_on is earlier than job_due_end [boolean]
appt_total_duration - The sum total of the durations of all appointments on the job, in hours [number]
completed_appt_total_duration - The sum total of the durations of all completed appointments on the job, in hours [number]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_last_invoice - The latest transaction date of the invoices for the job; if the job has only one invoice, this will be the same as job_first_invoice [datetime]
job_invoice_count - The number of invoices for the job [number]
job_deficiency_count - The number of deficiencies reported on this job [number]
job_deficiency_quote_count - The number of quotes created to repair deficiencies reported on this job [number]
hours_created_to_first_appt - The elapsed time between when the job was created and when its first appointment was scheduled to start, in hours [number]
hours_first_appt_to_completed - The elapsed time between when the job's first appointment was scheduled to start and when the job was marked complete, in hours [number]
hours_created_to_completed - The elapsed time between when the job was created and when it was marked complete, in hours [number]
hours_completed_to_invoiced - The elapsed time between when the job was completed and the earliest transaction date of the invoices on the job, in hours [number]
hours_created_to_invoiced - The elapsed time between when the job was created and the earliest transaction date of the invoices on the job, in hours [number]
hours_created_to_on_site_first_clock_in - The elapsed time between when the job was created and the earliest on site clock in, in hours [number]
hours_on_site_last_clock_out_to_first_invoice - The elapsed time between the last on site clock out and the transaction date of the first invoice, in hours [number]
hours_on_site_last_clock_out_to_last_invoice - The elapsed time between the last on site clock out and the transaction date of the last invoice, in hours [number]
hours_on_site_first_clock_in_to_on_site_last_clock_out - The elapsed time between the first on site clock in and last on site clock out, in hours [number]
en_route_total_minutes - The sum total of the durations of all en route clock events for the job [number]
on_site_total_minutes - The sum total of the durations of all on site clock events for the job [number]
job_prep_total_minutes - The sum total of the durations of all job preparation clock events for the job [number]
on_site_first_clock_in - The date and time of the first on site clock in for the job [datetime]
on_site_first_clock_out - The date and time of the first on site clock out for the job [datetime]
on_site_last_clock_in - The date and time of the last on site clock in for the job [datetime]
on_site_last_clock_out - The date and time of the last on site clock out for the job [datetime]
job_prep_first_clock_in - The date and time of the first job preparation clock in for the job [datetime]
job_prep_first_clock_out - The date and time of the first job preparation clock out for the job [datetime]
job_prep_last_clock_in - The date and time of the last job preparation clock in for the job [datetime]
job_prep_last_clock_out - The date and time of the last job preparation clock out for the job [datetime]
en_route_first_clock_in - The date and time of the first en route clock in for the job [datetime]
en_route_first_clock_out - The date and time of the first en route clock out for the job [datetime]
en_route_last_clock_in - The date and time of the last en route clock in for the job [datetime]
en_route_last_clock_out - The date and time of the last en route clock out for the job [datetime]
servicelink_first_sent - The date and time on which the job's service link was first sent to the customer [datetime]
servicelink_last_sent - The date and time on which the job's service link was last sent to the customer [datetime]
servicelink_first_viewed - The date and time on which the job's service link was first viewed the customer [datetime]
servicelink_last_viewed - The date and time on which the job's service link was last viewed the customer [datetime]
review_requested - A flag indicating whether a service review request was sent to the customer [boolean]

Job Items

The 'job_items' dataset contains information about the individual job items associated with jobs in your ServiceTrade account.  Each row represents a single job item.

Fields:

id - The internal ID of the job item [number]
job_number - The job number [number]
job_id - The internal ID of the job [number]
item_name - The name of the job item [text]
item_code - The item code of the job item [text]
item_code - The item type of the job item [text]
service_line - The name of the job item's service line [text]
quantity - The quantity of the job item [number]
cost - The unit cost of the job item [number]
total_cost - The total cost of the job item (job item quantity multiplied by job item unit cost) [number]
used_on - The date on which this job item was used [datetime]
created - The date on which the job item was created [datetime]
updated - The date on which the job item was most recently updated [datetime]

Appointment/Job Services

The 'appointment_services' dataset contains information about the individual services associated with appointments (and, by extension, jobs) in your ServiceTrade account.  Each row represents a single service associated with a single appointment.

To analyze services by job instead of by individual appointment, construct your analysis to group by job number or job ID instead of by appointment date or appointment ID.

IMPORTANT:  A single service can be associated with more than one appointment, so it is valid for the same service to appear multiple times across several different appointments on the same job.  Therefore, if you are analyzing services by job, you may see some services appear to be duplicated, and you may wish to construct your analysis so that it filters out those "duplicated" services that are associated with several appointments.

Fields:

id - The internal ID of the service [number]
appointment_id - The internal ID of the appointment [number]
job_id - The internal ID of the job [number]
job_number - The job number [number]
service_line
- The name of the service's service line [text]
asset - The name of the service's asset [text]
location - The name of the service's location [text]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
status - The current status of the service [text]
price - The estimated price of the service [number]
duration - The estimated duration of the service, in seconds [number]
duration_hours - The estimated duration of the service, in hours [number]
service_line - The name of the service's service line [text]
appointment_window_start - The beginning of the scheduled window for this appointment [datetime]
appointment_window_end - The end of the scheduled window for this appointment [datetime]
appointment_status - The current status of the appointment [text]
created - The date on which the service was created [datetime]
updated - The date on which the service was most recently updated [datetime]

Deficiencies

The 'deficiencies' data set contains information about deficiencies discovered in your ServiceTrade account.  All deficiencies other than those with a status of 'Invalid' are represented in this dataset.

This data set includes information about the quote that proposes a fix to the deficiency (if there is one).   Only quotes which have at least one service to repair the deficiency are included.  Quotes with statuses of 'Canceled' and 'Rejected', and quotes whose deficiency repair services have statuses of 'Canceled' or 'Void' are not included.  If there are no quotes to repair the deficiency, all the fields beginning with quote_ will be empty.

This dataset also includes information about the job to perform the repair work described in the quote (if there is one).   Only jobs which have at least one service to repair the deficiency are included.  Jobs with a status of 'Canceled', and jobs whose deficiency repair services have statuses of 'Canceled' or 'Void', are not included.   If there are no jobs to repair the deficiency, all the fields beginning with jobs_ will be empty.

Fields:

id - The internal ID of the deficiency [number]
status - The current status of the deficiency [text]
description - The description of the deficiency [text]
resolution - The deficiency's current resolution status [text]
proposed_fix - The proposed fix for the deficiency [text]
created - The date on which the deficiency was created [datetime]
updated
- The date on which the deficiency was most recently updated [datetime]
service_line - The name of the deficiency's service line [text]
asset
- The name of the asset on which the deficiency was identified [text]
location
- The name of the deficiency's location [text]
location_state
- The abbreviation of the state or territory of the deficiency's location [text]
location_postal_code
- The postal code of the state or territory of the deficiency's location [text]
location_offices
- A list of the names of the offices assigned to the deficiency's location [comma-separated text]
location_regions
- A list of the names of the regions containing the deficiency's location [comma-separated text]
customer
- The name of the ServiceTrade customer company that owns the defiency's location [text]
reporter_office
- The name of the office assigned to the user who most recently reported the deficiency [text]
reporter_name
- The name of the user who most recently reported the deficiency [text]
quote_id
- The internal ID of the quote [number]
quote_number - The quote number [number]
quote_owner - The name of the ServiceTrade user who is assigned as the owner of the quote [text]
quote_status - The quote's current status [text]
quote_expires_on - The date on which the quote expires [datetime]
quote_created - The date on which the quote was created [datetime]
quote_updated - The date on which the quote was most recently updated [datetime]
quote_service_id - The internal ID of the quote's service to repair the deficiency [number]
quote_price - The total quoted amount of the quote [number]
quote_first_sent
- The date on which the quote was first sent to the customer for approval [datetime]
quote_last_sent
- The date on which the quote was most recently sent to the customer for approval [datetime]
quote_first_viewed
- The date on which the quote was first viewed by the customer [datetime]
quote_last_viewed
- The date on which the quote was most recently viewed by the customer [datetime]
quote_approved
- The date on which the quote was approved [datetime]
job_id
- The internal ID of the job to repair the deficiency [number]
job_service_id
- The internal ID of the job's service to repair the deficiency [number]
job_service_status - The current status of the service on the job that repairs the deficiency [text]
job_status - The current status of the job [text]
job_created - The date on which the job was created [datetime]
job_updated - The date on which the job was most recently updated [datetime]
job_first_appt - The date and time on which the first appointment on the repair job is scheduled to start [datetime]
job_last_appt - The date and time on which the last appointment on the repair job is scheduled to start [datetime]  If there is only one appointment on the repair job, this will be the same as job_first_appt. [datetime]
job_completed - The date on which the repair job was marked as complete [datetime]
deficiency_discovered_to_quote_created_days - The number of days from when the deficiency was created, to when the quote to repair it was created [number]
quote_created_to_sent_days - The number of days from when the quote was created, to when it was first sent to the customer for approval [number]
quote_sent_to_viewed_days - The number of days from when the quote was first sent to the customer for approval, to when the quote was first viewed by the customer [number]
quote_viewed_to_approved_days - The number of days from when the quote was first viewed by the customer, to when the quote was approved by the customer [number]
quote_sent_to_approved_days - The number of days from when the quote was sent to the customer, to when the quote was approved by the customer [number]
quote_approved_to_job_created_days - The number of days from when the quote was approved, to when the repair job for that quote was created [number]
job_created_to_job_completed_days  - The number of days from when the repair job was created, to when the repair job was marked as complete [number]
deficiency_discovered_to_job_completed_days - The number of days from when the deficiency was discovered, to when the repair job was marked as complete.  This measures the length of the entire 'cradle-to-grave' lifecycle of a repaired deficiency. [number]

Quotes

The 'quotes' data set contains information about quotes created in your ServiceTrade account.  All quotes in all statuses (including canceled and rejected quotes) are represented in this dataset.

This dataset also includes information about the job to perform the repair work described in the quote (if there is one). Jobs whose quoted services have statuses of 'Canceled' or 'Void' are not included.   If there are multiple jobs associated with the same quote, the information from the most recently updated job is used.  If there are no jobs to perform the work described in the quote, all the fields beginning with jobs_ will be empty.

This dataset also includes information about the invoices associated with the repair job.  Invoices whose status is 'Void' are not included.  If there are no invoices associated with the repair job, the 'job_invoice_amount', 'job_first_invoice', and 'job_last_invoice' fields will be empty.

Fields:

id - The internal ID of the quote [number]
quote_number - The quote number [number]
name - The quote name [text]
status - The quote's current status [text]
created - The date on which the quote was created [datetime]
updated - The date on which the quote was most recently updated [datetime]
customer - The name of the ServiceTrade company which is the customer for this quote [text]
location - The name of this quote's location [text]
location_state - The abbreviation of the state or territory of the quote's location [text]
location_postal_code - The postal code of the state or territory of the quote's location [text]
location_offices - A list of the names of the offices assigned to the quote's location [comma-separated text]
location_regions - A list of the names of the regions containing the quote's location [comma-separated text]
location_latitude - The latitude of the quote's location [number]
location_longitude - The longitude of the quote's location [number]
owner - The name of the ServiceTrade user who is assigned as the owner of this quote [text]
expires_on - The date on which this quote expires [datetime]
quote_first_sent - The date on which the quote was first sent to the customer for approval [datetime]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval [datetime]
quote_first_viewed - The date on which the quote was first viewed by the customer [datetime]
quote_last_viewed - The date on which the quote was most recently viewed by the customer [datetime]
quote_service_lines - A list of the names of the service lines associated with this quote's services [comma-separated text]
total - The total quoted amount of the quote [number]
job_number - The job number for the repair job associated with this quote [number]
job_type - The job type [text]
job_status - The current status of the job [text]
job_created - The date on which the job was created [datetime]
job_updated - The date on which the job was most recently updated [datetime]
job_assigned_to - The name of the ServiceTrade user who is assigned as the owner of this job [text]
job_assigned_to_office - The name of the office assigned to the user who is assigned as the owner of this job [text]
job_cost - The sum total of the costs of all job items on this job [number]
job_appt_first_start - The date and time on which the first appointment on the job is scheduled to start [datetime]
job_appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
job_appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
job_appt_last_end - The date and time on which the last appointment on the job is scheduled to end [datetime]
job_due_start - The date on which the due window for the job begins [datetime]
job_due_end - The date on which the due window for the job ends [datetime]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_first_invoice - The date and time on which the job's first invoice was created [datetime]
job_last_invoice - The date and time on which the job's last invoice was created.  If the job has only one invoice, this will be the same as job_first_invoice. [datetime]
job_expected_margin - The difference between job_cost and total [number]
job_actual_margin - The difference between job_cost and job_invoice_amount [number]
job_is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
job_is_completed - A flag indicating whether the job has been marked as complete [boolean]
job_all_appts_completed - A flag indicating whether all the job's appointments have been completed [boolean]
job_completed_in_due_window - A flag indicating whether the job was marked as complete before the end of its due window [boolean]

Invoices

The 'invoices' dataset contains information about invoices created for jobs in your ServiceTrade account.  Invoices for jobs in all statuses except 'Void' are included in this dataset.

Fields:

id - The internal ID of the invoice [number]
invoice_number - The invoice number [text]
status - The current status of the invoice [text]
total - The total amount of the invoice, including tax [number]
subtotal - The amount of the invoice, not including tax [number]
invoice_type - The invoice type [text]
created - The date on which the invoice was created [datetime]
updated - The date on which the invoice was most recently updated [datetime]
transaction_date - The invoice transaction date [datetime]
invoice_service_lines - A list of the service lines associated with the invoice's items [comma-separated text]
customer - The name of the ServiceTrade company which is the customer for this invoice [text]
location - The name of the invoiced job's location [text]
location_state - The abbreviation of the state or territory of the invoiced job's location [text]
location_postal_code - The postal code of the state or territory of the invoiced job's location [text]
location_offices - A list of the names of the offices assigned to the invoiced job's location [comma-separated text]
location_regions - A list of the names of the regions containing the invoiced job's location [comma-separated text]
location_latitude - The latitude of the invoiced job's location [number]
location_longitude - The longitude of the invoiced job's location [number]
job_id - The internal ID of the invoiced job [number]
job_number - The job number for the invoiced job [number]
job_type - The invoiced job type [text]
job_status - The current status of the invoiced job [text]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the invoiced job [text]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the invoiced job [text]
job_completed_on - The date mon which the invoiced job was marked complete [datetime]

Invoice Items

The 'invoice_items' dataset contains information about the individual invoice items associated with invoices in your ServiceTrade account.  Each row represents a single invoice item.

Fields:

id - The internal ID of the invoice item [number]
invoice_number - The invoice number [number]
invoice_id - The internal ID of the invoice [number]
item_name - The name of the invoice item [text]
item_code - The item code of the invoice item [text]
item_code - The item type of the invoice item [text]
service_line - The name of the invoice item's service line [text]
quantity - The quantity of the invoice item [number]
price - The unit price of the invoice item [number]
subtotal - The total price of the invoice item without tax (invoice item quantity multiplied by invoice item unit cost) [number]
total - The total price of the invoice item with tax (invoice item quantity multiplied by invoice item unit cost, plus tax) [number]
tax_rate - The tax rate percentage for this invoice item [number]
created - The date on which the invoice item was created [datetime]
updated - The date on which the invoice item was most recently updated [datetime]

Recurring Services

The 'recurring_services' dataset includes information about recurring services in your ServiceTrade account.  In this dataset, recurring services are projected through the end of the year following the current year.  Only recurring services are included in this dataset.  One-time (single instance) services are not included.

Each row in this dataset represents a single instance of that recurring service.  For instance, a service that occurs quarterly (every 3 months) would have up to 8 rows representing it in this dataset: up to 4 rows for the current year, and 4 rows for the next year.

Fields:

id - The internal ID of the recurring service [number]
sequence_id - A generated ID for this instance of the recurring service [text]
description - The service description [text]
location - The name of the services's location [text]
location_state - The abbreviation of the state or territory of the job's location [text]
location_postal_code - The postal code of the state or territory of the job's location [text]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location_regions - A list of the names of the regions containing the job's location [comma-separated text]
asset - The name of the service's asset [text]
window_start - The beginning of the due window for this service [datetime]
window_end - The end of the due window for this service [datetime]
interval - The interval on which the service recurs [number]
frequency - The frequency on which the service recurs [text]
price - The estimated price of the service [number]
duration - The estimated duration of the service, in seconds [number]
duration_hours - The estimated duration of the service, in hours [number]
service_line - The name of the service's service line [text]
description - The service description [text]
preferred_techs - A list of preferred technicians for the service [comma-separated text]

Technician Productivity

The 'tech_productivity' dataset contains information about the expected and actual daily revenue for your technicians.  In this dataset, each row represents a single technician on a single appointment.

Because each job can have multiple invoices, each job can have more than appointment, and each appointment can have more than one technician and/or service, both expected and actual revenue may be distributed among multiple technician/appointment combinations.

When calculating the distribution of actual revenue, the pre-tax totals of all non-void invoices on a given job are summed.  Then, that revenue is distributed evenly across each technician/appointment combination.  For example, in the following scenario:

  • Appointment 1:  Technician A and Technician B
  • Appointment 2:  Technician C
  • Appointment 3:  Technician A and Technician C

Technician A and Technician C will each receive 40% of the job's actual revenue, and Technician B will receive the remaining 20%.

When calculation the distribution of expected revenue, the estimated revenue for each service on a given appointment is summed. Then, that revenue is distributed evenly across all technicians assigned to that appointment.  This calculation is done independently for each appointment on a given job.  For example, in the following scenario:

  • Appointment 1:  Service W and Service X / Technician A and Technician B
  • Appointment 2:  Service Y / Technician C
  • Appointment 3:  Service Z / Technician A, Technician C, and Technician D

Technician A would receive 50% of the expected revenue from Service W, 50% from Service X, and 33% from Service Z.  Technician B would receive 50% of the expected revenue from Service W and Service X.  Technician C would receive all of the expected revenue from Service Y and 33% of the revenue from Service Z.  Technician D would receive 33% of the revenue from Service Z.

Estimated service durations are distributed in like manner: the estimated durations of all services for each appointment are summed, then the resulting total duration is distributed evenly across all technicians assigned to that appointment.

IMPORTANT:  Services whose status is 'Void' or 'Canceled', and appointments whose status is 'Canceled', are ignored in this dataset, and are not used to calculate the expected and actual revenue distributions.

In addition, to prevent helper/apprentice technicians from being allocated a portion of expected and actual revenue, technicians whose office name contains the word 'helper' are ignored in this dataset only.  For instance, if an appointment was assigned to two technicians, one of which was a helper, 100% of the revenue and estimated duration for that appointment would be distributed to the other (non-helper) technician.

Fields:

appointment_id - The internal ID for the appointment [number]
technician_id - The internal ID for the technician [number]
appointment_technician_id - A generated ID for the combination of this technician and this appointment [text]
job_id - The internal ID for this appointment's job [number]
job_number - The job number [number]
job_type - The job type [text]
customer - The name of the ServiceTrade company which is the customer for this job [text]
location - The name of the job's location [text]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
technician_name - The full name of the technician [text]
technician_office - The name of the office assigned to the technician [text]
released - A flag indicating whether this appointment has been released [boolean]
status - The status of this appointment [text]
appointment_start - The date and time on which this appointment is scheduled to start [datetime]
appointment_end - The date and time on which this appointment is scheduled to end [datetime]
created - The date on which this appointment was created [datetime]
updated - The date on which this appointment was most recently updated [datetime]
actual_revenue - The invoiced revenue allocated to this technician on this appointment (see distribution rules above) [number]
estimated_revenue - The estimated revenue allocated to this technician on this appointment (see distribution rules above) [number]
estimated_duration - The estimated duration for the services allocated to this technician on this appointment, in seconds (see distribution rules above) [number]
estimated_duration_hours - The estimated duration for the services allocated to this technician on this appointment, in hours (see distribution rules above) [number]
estimated_actual_revenue_difference - The difference between actual_revenue and estimated_revenue for this technician on this appointment [number]
en_route_total_minutes - The sum total of the durations of all en route clock events for this technician on this appointment [number]
on_site_total_minutes - The sum total of the durations of all on site clock events for this technician on this appointment [number]
job_prep_total_minutes - The sum total of the durations of all job preparation clock events for this technician on this appointment [number]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_last_invoice - The latest transaction date of the invoices for the job; if the job has only one invoice, this will be the same as job_first_invoice [datetime]

Technician Service Lines

The 'tech_service_lines' dataset contains information about your technicians and the service line capabilities that are assigned to them.  In this dataset, each row represents a single service line assignment to a technician.

Fields:

technician_id - The internal ID for the technician [number]
technician_name - The full name of the technician [text]
technician_office - The name of the office assigned to the technician [text]
service_line - The name of the assigned service line [text]

Did this answer your question?