ServiceTrade Data Warehouse Dataset Reference

Daniel Nolen
Daniel Nolen
  • Updated

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.

Datasets Available:

All data sets available for Core ServiceTrade and PartsLedger are easily accessible by clicking the In this article section to the right.

Within these datasets, 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

All tables include the following fields:

  • account_id - The internal ID of the ServiceTrade account [number]
  • account_name - The name of the ServiceTrade account [text]

Core ServiceTrade

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.

An Appointment_Services record will refresh any time a change is made to the service.

Fields:

appointment_id - The internal ID of the appointment [number]
appointment_status - The current status of the appointment [text]
appointment_window_end - The end of the scheduled window for this appointment [datetime]
appointment_window_start - The beginning of the scheduled window for this appointment [datetime]
asset - The name of the service's asset [text]
created - The date on which the service was created [datetime]
description - The description of the service [text]
duration_hours - The estimated duration of the service, in hours [number]
duration - The estimated duration of the service, in seconds [number]
id - The internal ID of the service [number]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
location_id - The internal id of the location associated with the service [number]
location - The name of the service's location [text]
office_assigned_to_job - - The name of the office assigned to the job [text]
office_id - The ID of the office associated with the appointment service
price - The estimated price of the service [number]
quote_type - The quote type of the associated service [text]
recurring_service_id - The internal ID of the recurring service to which this service belongs [number] (empty if this is a one-time service)
service_line_id - The internal ID of the service's service line [number]
service_line - The name of the service's service line [text]
status - The current status of the service [text]
updated - The date on which the service was most recently updated [datetime]
window_end - The end of the due window for this service [datetime]
window_start - The beginning of the due window for this service [datetime]

Appointment/Technicians

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

To analyze technician assignments 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 technician can be associated with more than one appointment, so it is valid for the same technician to appear multiple times across several different appointments on the same job.  Therefore, if you are analyzing technician assignments by job, you may see some technicians appear to be duplicated, and you may wish to construct your analysis so that it filters out those "duplicated" technicians that are associated with several appointments.

An Appointment_Technicians record will refresh any time a change is made to the appointment.

Fields:

appointment_created - The date on which the appointment was created [datetime]
appointment_id - The internal ID of the appointment [number]
appointment_status - The current status of the appointment [text]
appointment_updated - The date on which the appointment was most recently updated [datetime]
appointment_window_end - The end of the scheduled window for this appointment [datetime]
appointment_window_start - The beginning of the scheduled window for this appointment [datetime]
created - The date/time when the association between the technician and appointment was created [datetime]
customer - The name of the customer associated with the appointment [text]
duration - The estimated duration of the appointment in hours [number]
duration_hours - The duration of this appointment, in hours [number]
duration_hours - The estimated duration of the service, in hours [number]
id - The internal ID of the service [number]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
job_status - The current status of the overall job associated with the appointment [text]
job_type - The job type [text]
location_offices - A list of the offices associated with the location for this job [comma-separated text]
location_regions - A list of the regions containing the location for this job [comma-separated text]
location_state - The state/province of the location for this job [comma-separated text]
location - The name of the location for this job [text]
office_id - The ID of the office associated with the appointment technician [number]
price - The estimated price of the service [number]
released - A flag indicating whether this appointment has been released [boolean]
status - The current status of the service [text]
technician_id - The internal ID of the technician [number]
technician_name - The name of the technician [text]
technician_office - The name of office assigned to the technician [text]
updated - The date/time when the association between the technician and appointment was last updated [datetime]
window_end - The end of the due window for this service [datetime]
window_start - The beginning of the due window for this service [datetime]

Appointments

The 'appointments' dataset contains information about the individual appointments independent of how those appointments show up on, and are related to, job services and technicians. Each row represents a single appointment.

All Appointment records are refreshed every time the data warehouse data updates.

Fields:

created - The date on which the appointment was created [datetime]
customer - The name of the ServiceTrade company which is the customer for this associated appointment [text]
duration - The duration of the appointment in hours [number]
first_released - A flag indicating whether this is the first appointment released for the associated job [boolean]
id - Primary key / user id of the Appointments table [number]
invoice_status - The current status of the invoice associated with the appointment [text]
job_id - Job id of the job that the appointment is tied to [number]
job_type - The job type of the associated appointment [text]
location_id - Location id of the location the appointment takes places at [number]
name - The name associated with the attachment [text]
quote_type - The quote type of the associated appointment [text]
released - A flag indicating whether the appointment has been released or not [boolean]
status - The current status of the appointment [text]
updated - The date on which the appointment was most recently updated [datetime]
window_end - The date/time that the appointment ends on [datetime]
window_start - The date/time that the appointment starts at [datetime]

Asset Details

The ‘Asset Details’ dataset contains information about the individual asset data relating to the asset in the ‘Assets’ dataset.

All Asset Detail records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the asset detail associated with the assets table [number]
property_name - The name of the asset detail’s property [text]
property_value - The value of the asset detail’s property [text]

Assets

The ‘Assets’ dataset contains information about the individual assets. Each row represents a single asset.

All Asset records are refreshed any time a change is made to the asset.

Fields:

asset_type - The asset type [text]
created - The date on which the asset was created [datetime]
id - The internal ID of the asset [number]
location_id - The internal id of the location associated with the asset [number]
name - The name of the asset [text]
parent_id - The internal id of the parent asset [number]
status - The current status of the asset [text]
updated - The date on which the asset was most recently updated [datetime]

Attachments

The 'attachments' table contains information about the files attached to various entities in your ServiceTrade account. Each row represents a single attachment.

A record in the Attachments table will refresh when any change is made to the corresponding attachment.

Fields:

added_by - The name of the ServiceTrade user who added the attachment [text]
checksum - The checksum value of the attached file [text]
created - The date on which the attachment was created [datetime]
creator_id - The ID of the user who created the attachment [number]
deleted - A flag indicating whether the attachment has been deleted [boolean]
deleted_by - The name of the ServiceTrade user who deleted the attachment [text]
description - The description of the attachment [text]
entity_id - The ID of the entity to which the attachment is attached [number]
entity_type_id - The type of the entity for the associated attachment [number]
entity_type_name - The name of the type of the entity for the associated attachment [text]
file_name - The name of the attached file [text]
file_type - The type of the attached file [text]
id - The internal ID of the attachment [number]
meta - The metadata associated with the attachment [text]
purpose_id - The purpose for which the attachment is attached [number]
purpose_name - The name of the purpose for the associated attachment [text]
storage_type - The type of storage used for the attachment [number]
updated - The date on which the attachment was most recently updated [datetime]
viewable_by - The number of people who have view access to the attachment [number]

Budget Categories

The 'budget_categories' dataset contains information about high level budgets. Each row represents a single budget category.

All Budget Category records are refreshed any time a change is made to the budget.

Fields:

budget_id - The internal ID of the budget associated with the budget category [number]
cost - The unit cost of the budget category [number]
created - The date on which the budget category was created [datetime]
id - The internal ID of the budget category [number]
lib_item_type - The lib item type of the budget line item [number]
price - The unit price of the budget category [number]
quantity - The quantity of budget category [number]
service_request_id - The internal ID of the service request associated with the budget category [number]
updated - The date on which the budget category was most recently updated [datetime]

Budget Line Items

The 'budget_line_items' dataset contains information about granular level budgets. Each row represents a single budget category.

All Budget Line Item records are refreshed any time a change is made to the budget.

Fields:

budget_id - The internal ID of the budget associated with the budget line item [number]
cost - The unit cost of the budget line item [number]
created - The date on which the budget line item was created [datetime]
display_output - The display output of the budget line item [text]
id - The internal ID of the budget line item [number]
job_item_id - The database ID of the job item associated with the budget line item [number]
lib_item_id - The database ID of the lib item associated with the budget line item [number]
lib_item_type - The reference number of the budget line item [number]
price - The unit price of the budget line item [number]
quantity - The quantity of budget line item [number]
reference_number - The reference number of the budget line item [number]
service_line_id - The database id of the service line associated with the budget line item [number]
service_request_id - The internal ID of the service request associated with the budget line item [number]
total_price - The total price of the budget line items [number]
total - The total cost of the budget line items [number]
updated - The date on which the budget line item was most recently updated [datetime]

Budgets

The 'budgets' dataset contains information about the individual budgets. Each row represents a single budget.

All Budget records are refreshed any time a change is made to the budget.

Fields:

baseline_id - The ID of the budget's baseline [number]
created - The date on which the budget was created [datetime]
id - The internal ID of the budget [number]
job_id - The ID of the budget's job [number]
last_lock_user_id - The internal ID of the last ServiceTrade user to lock the budget [number]
last_lock -The date on which the budget was last locked [datetime]
owner_id - The ID of the budget owner [number]
parent_id - The internal ID of the parent budget [number]
status - The status of the budget [text]
total_price - The total price of the budget [number]
total - The total cost of the budget [number]
type - The budget type - Granular or High Level [text]
updated - The date on which the budget was most recently updated [datetime]

Change Order Categories

The 'change_order_categories' dataset contains information about high level change orders. Each row represents a single change order category.

All Change Order Category records are refreshed any time a change is made to the change order.

Fields:

action - The purpose of the change order Line Item(add/change) [text]
change_order_id - The Internal ID of the Change Order this Line Item belongs to [number]
cost - The total cost of the Change Order Line Item [number]
created - The date on which the change order category was created [datetime]
id - The internal ID of the change order category [number]
lib_item_type - The item type for the Change Order Line Item [number]
name - The name of the change order category [text]
notes - The notes written for the change order line item [text]
order_index - The index/order of the category relative to other categories [number]
price - The estimated revenue for the Change Order Line Item [number]
quantity - The quantity for the Change Order Line Item [number]
reference_number - A unique reference number for the change order category [text]
service_line_id - The internal ID of the service line associated with the change order category [number] service_request_id - The service request that this line item is associated with [number]
updated - The date on which the change order category was most recently updated [datetime]

Change Order Line Items

The 'change_order_line_items' dataset contains information about granular level change orders. Each row represents a single change order line item.

All Change Order Line Item records are refreshed any time a change is made to the change order.

Fields:

action - The purpose of the change order Line Item(add/change) [text]
change_order_id - The internal ID of the change order associated with the change order line item [number]
cost - The unit cost of the Change Order Line Item [number]
created - The date on which the Change Order Line Item was created [datetime]
display_output - The item description shown in the change order [text]
id - The internal ID of the change order line item [number]
job_item_id - The internal ID of the job item associated with the change order line item [number]
lib_item_id - The internal ID of the lib item associated with the change order line item [number]
lib_item_type - The item type for the Change Order Line Item [number]
name - The name of the change order line item [text]
notes - The notes written for the change order line item [text]
price - The unit price of the Change Order Line Item [number]
quantity - The quantity for the Change Order Line Item [number]
service_line_id - The internal ID of the service line associated with the change order line item [number]
service_request_id - The internal ID of the service request associated with the change order line item [number]
updated - The date on which the Change Order Line Item was last updated [datetime]

Change Orders

The 'change_orders' dataset contains information about the individual change order. Each row represents a single change order.

All Change Order records are refreshed any time a change is made to the change order.

Fields:

approved_count - The number of times the quote was approved by the customer for the associated change order [number]
approved - The date on which the quote was approved for the associated change order [datetime]
attachment_added_count - The number of times the quote had an attachment added for the associated change order [number]
baseline_budget_id- The internal ID of the original budget that is being changed [number]
budget_id- The internal ID of the new budget created from the change order [number]
budget_type - The type of budget the change order applies to (e.g. time and materials, fixed price, etc.) [text]
categories_sum_total_cost - The total cost of all change order categories associated with this change order [number]
categories_sum_total_price - The total price/estimated revenue of all change order categories associated with this change order [number]
created- The date on which the change order was created [datetime]
customer_id - The ID of the job's customer for the associated change order [number]
customer_name - The name of the ServiceTrade company which is the customer for this job for the associated change order [text] 
first_approved - The date on which the quote was first approved by the customer for the associated change order [datetime]
first_attachment_added - The date on which the quote first had an attachment added for the associated change order [datetime]
first_sent - The date on which the quote was first sent to the customer for approval for the associated change order [datetime]
id- The internal ID of the change order [number]
is_approved - A flag indicating whether the quote was approved by the customer for the associated change order [boolean]
is_attachment_added - A flag indicating whether the quote had an attachment added for the associated change order [boolean]
is_sent - A flag indicating whether the quote was sent to the customer for approval for the associated change order [boolean]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount for the associated change order [number]
job_all_appts_completed - A flag indicating whether all the job's appointments have been completed for the associated change order [boolean]
job_appt_first_end - The date and time on which the first appointment on the job is scheduled to end for the associated change order [datetime]
job_appt_first_start - The date and time on which the first appointment on the job is scheduled to start for the associated change order [datetime]
job_appt_last_end - The date and time on which the last appointment on the job is scheduled to end for the associated change order [datetime]
job_appt_last_start - The date and time on which the last appointment on the job is scheduled to start for the associated change order [datetime]
job_assigned_to_office - The name of the office assigned to the user who is assigned as the owner of this job for the associated change order [text]
job_assigned_to - The name of the ServiceTrade user who is assigned as the owner of this job for the associated change order [text]
job_completed_in_due_window - A flag indicating whether the job was marked as complete before the end of its due window for the associated change order [boolean]
job_completed_on - The date the job associated with this change order was completed, if applicable [datetime]
job_cost - The sum total of the costs of all job items on this job for the associated change order [number]
job_created - The date on which the job was created for the associated change order [datetime]
job_due_end - The date on which the due window for the job ends for the associated change order [datetime]
job_due_start - The date on which the due window for the job begins for the associated change order [datetime]
job_estimated_price (see the jobs dataset for job_estimated_price) for the associated change order [number]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_first_invoice - The date and time on which the job's first invoice was created for the associated change order [datetime]
job_id - The internal ID of the job associated with this change order [number]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job for the associated change order [number]
job_is_completed - A flag indicating whether the job has been marked as complete for the associated change order [boolean]
job_is_invoiced - A flag indicating whether the job has at least one invoice for the associated change order [boolean]
job_last_invoice - The date and time on which the job's last invoice was created for the associated change order.  If the job has only one invoice, this will be the same as job_first_invoice. [datetime]
job_number - The job number for the repair job associated with this quote [number]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on this job for the associated change order [text]
job_status - The current status of the job for the associated change order [text]
job_type - The job type for the associated change order [text]
job_updated - The date on which the job was most recently updated for the associated change order [datetime]
last_approved - The date on which the quote was most recently approved by the customer for the associated change order [datetime]
last_attachment_added - The date on which the quote most recently had an attachment added for the associated change order [datetime]
last_sent_by - The name of the ServiceTrade user who last sent the quote to customer for the associated change order [text]
last_sent - The date on which the quote was most recently sent to the customer for approval for the associated change order [datetime]
line_items_sum_total_cost - The total cost of all change order line items associated with this change order [number]
line_items_sum_total_price - The total price/estimated revenue of all change order line items associated with this change order [number]
location_id - The ID of the job's location for the associated change order (matches id field in the Locations dataset) [number]
location_latitude - The latitude of the job's location for the associated change order [number]
location_longitude - The longitude of the job's location for the associated change order [number]
location_offices - A list of the names of the offices assigned to the job's location for the associated change order [comma-separated text]
location_postal_code - The postal code of the state or territory of the job's location for the associated change order [text]
location_regions - A list of the names of the regions containing the job's location for the associated change order [comma-separated text]
location_state - The abbreviation of the state or territory of the job's location for the associated change order [text]
location - The name of the job's location for the associated change order [text]
notes-The notes written for the change order [text]
office_id - The ID of the office associated with the change order
owner_id- The internal id of the change order owner [number]
owner_name - The name of the user company that currently owns the change order for the associated change order [text] response_notes- The name and email address of the person who responded to the change order [text]
response_user_id- The internal ID of the change order response user [number]
sent_count - The number of times the quote was sent to the customer for approval for the associated change order [number]
service_lines - A list of the names of the service lines associated with this change order's services [comma-separated text]
status - The status of the change order [text]
terms_id - The internal ID of the terms associated with the change order [number]
total_cost - The total cost of the entire change order [number]
total_price - The total price/estimated revenue of the entire change order [number]
type - The change order type [text] (Internal or External)
updated- The date on which the change order was most recently updated [datetime]

Clock Events

The 'clock_events' data set contains information about clock-in/clock-out events performed in your ServiceTrade account.  Each row represents a clock-in event, and the associated clock-out event (if it exists).

A record in the Clock_Events table will refresh when any change is made to the corresponding clock event.

Fields:

activity - The activity represented by this clock-in/clock-out pair; one of: 'En Route', 'On Site', 'Job Prep' [text]
appointment_id - The ID of the appointment associated with this clock-in/clock-out pair [number]
clock_duration_minutes - The elapsed time between the clock-in and clock-out events, in minutes [number]
clock_event_key - A concatenated key of job_id-appointment_id-user_id for this clock-in/clock-out pair. This is not necessrily a unique key for this table, as appointments can be re-used by the same user for the same job [text]
clock_in_created - The date on which the clock-in event was created [datetime]
clock_in_event_time - The date of the clock-in event [datetime]
clock_in_id - The internal ID of the clock-in event [number]
clock_in_lat - The latitude of the clock-in event [number]
clock_in_lon - The longitude of the clock-in event [number]
clock_in_source - The source of the clock-in event; one of: 'Manual' (manually created in the ServiceTrade web UI) or 'Mobile' (created via the ServiceTrade technician mobile application) [text]
clock_in_updated - The date on which the clock-in event was updated [datetime]
clock_out_created - The date on which the clock-out event was created [datetime]
clock_out_event_time - The date of the clock-out event [datetime]
clock_out_id - The internal ID of the clock-out event [number]
clock_out_lat - The latitude of the clock-out event [number]
clock_out_lon - The longitude of the clock-out event [number]
clock_out_source - The source of the clock-out event; one of: 'Manual' (manually created in the ServiceTrade web UI) or 'Mobile' (created via the ServiceTrade technician mobile application) [text]
clock_out_updated - The date on which the clock-out event was updated [datetime]
clock_pair_id - A concatenated key of the clock_in_id and the clock_out_id. For clock events that are unpaired, the missing clock_id will display as "[unpaired]". This key serves as a unique primary key for the clock_events table [text]
customer - The name of the customer of the job associated with this clock-in/clock-out pair [text]
job_id - The ID of the job associated with this clock-in/clock-out pair [number]
job_number - The number of the job associated with this clock-in/clock-out pair [number]
job_office - The name of the office assigned to the job associated with the clock events. [text]
job_status - The status of the job associated with this clock-in/clock-out pair [number]
job_type - The type of job (e.g. service, installation, etc.) associated with the clock event [text]
location_offices - A list of the names of the offices assigned to the location of the job associated with this clock-in/clock-out pair [comma-separated text]
location_regions - A list of the names of the regions containing the location of the job associated with this clock-in/clock-out pair [comma-separated text]
location - The name of the location of the job associated with this clock-in/clock-out pair [text]
technician_name - The name of the technician user associated with this clock-in/clock-out pair [text]
technician_office - The name of the office assigned to the technician user associated with this clock-in/clock-out pair [text]
user_id - The ID of the technician user associated with this clock-in/clock-out pair [number]

Comments

The 'comments' dataset returns comments from the following entities: Job, Company, Invoice, Quote, Deficiency, Location, Account, Service Request, Comment, Contract.

A record in the Comment table will refresh when any change is made to the corresponding comment.

Fields:

author_id - The ID of the user that authored the comment [number]
author_name - The name of the user associated with this comment [text]
content - The comment message itself [text]
created - The date on which the comment was created [datetime]
entity_id - The ID of the parent entity that the comment was created on [number]
entity_type - The type of record, one of: Job, Company, Invoice, Quote, Deficien,cy Location, Account, Service ,Request Comment, Contract [text]
id - Primary key / user id of the Comments table [number]
is_billing_note - A flag indicating if the comment is related to billing [boolean]
is_public_note - A flag indicating if the comment should be visible to all users [boolean]
is_scheduling_note - A flag indicating if the comment is related to scheduling [boolean]
is_technician_note - A flag indicating if the comment is from or targeted at a technician [boolean]
updated - The date on which the comment was most recently updated [datetime]

Companies

The 'companies' dataset contains information about the companies associated with your ServiceTrade account.  Each row represents a single company.

All Company records are refreshed every time the data warehouse data updates.

Fields:

address - The company street address [text]
city - The company city [text]
created_by - The full name of the user who created the company [text]
created - The date on which the company was created [datetime]
id - The internal ID of the company [number]
is_customer - A flag indicating whether the company is a customer [boolean]
is_parts_vendor - A flag indicating whether the company is a parts vendor [boolean]
is_vendor - A flag indicating whether the company is a vendor [boolean]
name - The company name [text]
phone - The company phone number [text]
ref_number - The company's reference number [number]
state - The abbreviation of the company state or territory [text]
status - The company status [text]
updated - The date on which the company was most recently updated [datetime]
zipcode - The company zipcode [text]

 

Contacts

The 'contacts' dataset contains information about the contacts associated with your ServiceTrade account. Each row represents a given contact that is associated with a given company and/or location. Because contacts can be associated to multiple locations, any given contact_id might appear multiple times in the contacts table. Inactive contacts are filtered out of the data.

All Contact records are refreshed every time the data warehouse data updates.

Fields:

alternate_phone - The alternate phone number of the contact [text]
company_id - The internal ID of the company associated with the contact [number]
created_by - The full name of the user who created the contact [text]
created - The date on which the contact was created [datetime]
email - The email address of the contact [text]
first_name - The first name of the contact [text]
id - The internal ID of the contact [number]
last_name - The last name of the contact [text]
location_id - The internal ID of the location asssociated with the contact [number]
location_primary_contact- A flag indicating if this contact is the primary contact for the associated location [boolean]
mobile - The mobile phone number of the contact [text]
phone - The primary phone number of the contact [text]
status - The current status of the contact record, e.g. "Active" or "Inactive" [text]
type - The contact type [text]
updated - The date on which the contact was most recently updated [datetime]

Contract Items

The 'contract_items' dataset contains information about individual price rules and other items on service contracts created in your ServiceTrade account. 

All Contract_Item records are refreshed every time the data warehouse data updates.

Fields:

amount - The amount of the contract rule; for the rule type 'Markup', this is a percentage markup (for instance, 30.0 means 30% markup); for all other rule types, this is a dollar amount [number]
contract_id - The internal ID of the contract [number]
contract_name - The name of the contract [text]
created - The date on which the contract item was created [datetime]
id - The internal ID of the contract item [number]
item_code - The item code of the item associated with this contract item [text]
item_id - The internal ID of the item associated with this contract item [text]
item_name - The name of the contract item (usually derived from the associated item) [text]
name - The name of the item associated with the contract [text]
rule_type - The type of the contract item [text]
updated - The date on which the contract was most recently updated [datetime]

Contracts

The 'contracts' dataset contains information about service contracts created in your ServiceTrade account.  Single-use contracts generated from quotes (called 'quote' contracts in the ServiceTrade API) are NOT included in this dataset.

A record in the Contracts table will refresh when any change is made to the corresponding contract.

Fields:

actual_cost - The actual cost for all job items for all completed jobs associated with this contract [number]
actual_margin - The actual margin for this contract, calculated by comparing actual_pretax_revenue to actual_cost, for this contract, as a percentage (e.g. 80.0 = 80% actual margin) [number]
actual_pretax_revenue - The actual invoiced revenue (pre-sales tax) for all invoices associated with  this contract [number]
actual_revenue - The actual invoiced revenue (post-sales tax) for all invoices associated with this contract [number]
contract_type - The type of contract [text]
created - The date on which the contract was created [datetime]
created_by - The name of the user who created the contract record [text]
customer_id - The ID of the customer associated with this contract [number]
customer_name - The name of the customer associated with this contract [text]
customer_po - The customer purchase order number for this contract [text]
ends_on - The end date for the contract [datetime]
expected_margin - The expected margin for this contract, as a percentage (e.g. 80.0 = 80% expected margin) [number]
expected_revenue - The expected revenue for this contract [number]
id - The internal ID of the contract [number]
location_id - The internal ID of the location that the contract applies to [number]
name - The contract name [text]
parent_contract_id - The ID of this contract's parent contract [number]
parent_contract_name - The name of this contract's parent contract [text]
payment_terms - The payment terms for this contract [text]
review_on - The review date for the contract [datetime]
sales - The name of the ServiceTrade user who is assigned as the salesperson of the job [text]
starts_on - The start date for the contract [datetime]
status - The status of this contract [text]
updated - The date on which the contract was most recently updated [datetime]

Deficiencies

The 'deficiencies' data set contains information about deficiencies discovered in your ServiceTrade account.  All deficiencies, including 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 that 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.

A Deficiency record will update whenever any change is made to the Deficiency, its corresponding Job, Quotes tied to the Deficiency, or Quote_Items tied to the Deficiency.

Fields:

asset_id - The ID of the deficiency's asset [number]
asset - The name of the asset on which the deficiency was identified [text]
created - The date on which the deficiency was created [datetime]
creator_name - The name of the ServiceTrade user who originally created the deficiency [text]
creator_office - The name of the office assigned to the user who originally created the deficiency [text]
customer_id - The ID of the deficiency's customer [number]
customer - The name of the ServiceTrade customer company that owns the deficiency's location [text]
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]
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]
deficiency_owner - The name of the ServiceTrade user who is assigned as the owner of the deficiency [text]
deficiency_sales - The name of the ServiceTrade user who is assigned as the salesperson on the deficiency [text]
description - The description of the deficiency [text]
id - The internal ID of the deficiency [number]
invoice_status - The current status of the invoice associated with the deficiency [text]
job_completed - The date on which the repair job was marked as complete [datetime]
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]
job_created - The date on which the repair job was created [datetime]
job_first_appt - The date and time on which the first appointment on the repair job is scheduled to start [datetime]
job_id - The internal ID of the job the deficiency was discovered on (matches id field in the Jobs dataset) [number]
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_sales - The name of the ServiceTrade user who is assigned as the salesperson on the repair job [text]
job_service_id - The internal ID of the job's service to repair the deficiency (matches id field in the Appointment/Job Services dataset) [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 repair job [text]
job_type - The job type of the associated deficiency [text]
job_updated - The date on which the repair job was most recently updated [datetime]
last_reported_on - The most recent date on which the deficiency was last reported [datetime]
location_id - The ID of the deficiency's location (matches id field in the Locations dataset) [number]
location_offices - A list of the names of the offices assigned to the deficiency's location [comma-separated text]
location_postal_code - The postal code of the state or territory of the deficiency's location [text]
location_regions - A list of the names of the regions containing the deficiency's location [comma-separated text]
location_state - The abbreviation of the state or territory of the deficiency's location [text]
location - The name of the deficiency's location [text]
office_id - The ID of the office associated with the deficiency [number]
proposed_fix - The proposed fix for the deficiency [text]
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]
quote_approved - - A flag indicating if the quote associated with this deficiency has been approved [boolean]
quote_cost - The total cost of all the items of the quote [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_created - The date on which the quote was created [datetime]
quote_expires_on - The date on which the quote expires [datetime]
quote_first_sent - The date on which the quote was first sent to the customer for approval [datetime]
quote_first_viewed - The date on which the quote was first viewed by the customer [datetime]
quote_id - The internal ID of the quote (matches id field in the Quotes dataset) [number]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval [datetime]
quote_last_viewed - The date on which the quote was most recently viewed by the customer [datetime]
quote_margin - The difference between quote_price and quote_cost, as a percentage of quote_price [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_price - The total quoted amount of the quote [number]
quote_sales - The name of the ServiceTrade user who is assigned as the salesperson on the quote [text]
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_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_service_id - The internal ID of the quote's service to repair the deficiency [number]
quote_status - The quote's current status [text]
quote_type - The quote type of the associated deficiency [text]
quote_updated - The date on which the quote was most recently updated [datetime]
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]
repair_job_id - The internal ID of the job to repair the deficiency (matches id field in the Jobs dataset) [number]
reported_on_job_number - The job number of the job this deficiency was reported on/discovered on [number]
reported_on_job_type - The type of job this deficiency was reported on/discovered on [text]
reporter_name - The name of the ServiceTrade user who most recently reported the deficiency [text]
reporter_office - The name of the office assigned to the user who most recently reported the deficiency [text]
resolution - The deficiency's current resolution status [text]
service_line_id - The internal ID of the deficiency's service line [number]
service_line - The name of the deficiency's service line [text]
severity - The assessed severity of the deficiency (e.g. critical, high, medium, low) [text]
status - The current status of the deficiency [text]
updated - The date on which the deficiency was most recently updated [datetime]

Deficiency Reports

The 'deficiency_reports' dataset contains information about the deficiency reports in your ServiceTrade account.  Each row represents a single deficiency report.

All Deficiecy Report records are refreshed every time the data warehouse data updates.

Fields:

asset_id - The internal id of the asset associated with the deficiecy report [number]
created - The date on which the deficiency report was created [datetime]
creator_id - The ID of the user who created the deficiency report [number]
deficiency_id - The internal id of the deficiency associated with the deficiency report [number]
description - The description of the deficiency report [text]
id- The internal ID of the deficiency report [number]
job_id - The internal id of the job associated with the deficiency report [number]
proposed_fix - The proposed fix of the deficiency report [text]
reporter_id - The id of the ServiceTrade user who most recently reported the deficiency [number]
service_line_id - The internal ID of the deficiency report's service line [number]
severity - The severity of the deficiency [text]
source - The source of the deficiency report [text]
status - The status of the deficiency report [text]
updated - The date on which the deficiency report was most recently updated [datetime]

External IDs

The 'external_ids' dataset contains mappings of ServiceTrade records to their external IDs. In this dataset, each row represents a mapping of one ServiceTrade record to its external ID for one external system.

All External ID records are refreshed every time the data warehouse data updates.

Fields:

entity_id - The internal ID of the record [number]
entity_type - The type of record, one of: Company, Contact, Contract, Deficiency, Job, Item, Location, Quote, User, Warehouse [text]
system_description - The display name for the external system [text]
system_name - The code name for the external system [text]
value - The record's external ID [number]

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.   Items for invoices in all statuses except 'Void' are included in this dataset.

A record in the Invoice_Items table will refresh when any change is made to the corresponding invoice.

Fields:

created - The date on which the invoice item was created [datetime]
customer - The name of the ServiceTrade company which is the customer for this associated invoice item [text]
id - The internal ID of the invoice item [number]
invoice_id - The internal ID of the invoice (matches id field in the Invoices dataset) [number]
invoice_number - The invoice number [number]
invoice_recurrence_frequency - The frequency of the invoice recurrence. This field is NULL if the invoice item is non-recurring. [text]
invoice_recurrence_instance_id - The internal id of the recurring invoice instance. This field is NULL if the invoice item is non-recurring. [number]
invoice_recurrence_instance_window_end - The window end date of the recurring invoice instance. This field is NULL if the invoice item is non-recurring. [datetime]
invoice_recurrence_instance_window_start - The window start date of the recurring invoice instance. This field is NULL if the invoice item is non-recurring. [datetime]
invoice_status - The current status of the invoice associated with the invoice item [text]
is_recurring_invoice_item - A flag indicating whether this invoice item was generated by a recurring invoice [boolean]
item_code - The item code of the invoice item [text]
item_id - The internal ID of the item that is associated with the invoice item [id]
item_name - The name of the invoice item [text]
item_type - The item type of the invoice item [text]
job_id - The ID of the invoice item's associated job item's associated job; null if the invoice item has no associated job item [number]
job_item_id - The ID of the invoice item's associated job item; null if the invoice item has no associated job item [number]
job_item_total_cost - The total cost of the invoice item's associated job item (job item quantity multiplied by job item unit cost); null if the invoice item has no associated job item [number]
job_number - The number of the invoice item's associated job item's associated job; null if the invoice item has no associated job item [number]
job_type - The job type of the associated invoice item [text]
price - The unit price of the invoice item [number]
quantity - The quantity of the invoice item [number]
quote_type - The quote type of the associated invoice item [text]
recurrence_id - The internal ID of the invoice recurrence. This field is NULL if the invoice item is non-recurring. [number]
service_asset - The name of the asset for the service associated with this invoice item's associated job item [text]
service_description - The description of the service associated with this invoice item's associated job item [text]
service_id - The id of the service associated with this invoice item's associated job item [number]
service_line_id - The internal ID of the invoice item's service line [number]
service_line - The name of the invoice item's service line [text]
subtotal - The total price of the invoice item without tax (invoice item quantity multiplied by invoice item unit cost) [number]
tax_group_name - The local town/city for which the taxes will be calculated by [text]
tax_group_state - The state for which the taxes will be calculated by [text]
tax_rate - The tax rate percentage for this invoice item [number]
total - The total price of the invoice item with tax (invoice item quantity multiplied by invoice item unit cost, plus tax) [number]
updated - The date on which the invoice item was most recently updated [datetime]

Invoices

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

A record in the Invoices table will refresh when any change is made to the corresponding invoice.

Fields:

creator_id - The user_id of the user that created the invoice [number]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the invoiced job [text]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the invoiced job [text]
contract_id - The ID of the contract assigned to the invoice [text]
contract_name - The name of the contract assigned to the invoice [text]
created - The date on which the invoice was created [datetime]
creator_office - The name of the office assigned to the user created the invoice [text]
creator - The name of the ServiceTrade user who created the invoice [text]
customer_id - The ID of the invoice's customer [number]
customer - The name of the ServiceTrade company which is the customer for this invoice [text]
display_notes - The invoice notes [text]
id - The internal ID of the invoice [number]
invoice_first_sent - The date that this invoice was first sent to the customer [datetime]
invoice_last_sent - The date that this invoice was last sent to the customer [datetime]
due_date - The invoice due date [date]
invoice_last_submitted_by - The name of the user that submitted the invoice to an external accounting system [text]
invoice_last_submitted_user_id - The id of the user that submitted the invoice to an external accounting system [number]
invoice_last_submitted - The time at which the invoice was submitted to the accounting system [datetime]
invoice_submitted_accounting_system_id - The id of the accounting system that the invoice was submitted to [number]
invoice_number - The invoice number [text]
invoice_service_lines - A list of the service lines associated with the invoice's items [comma-separated text]
invoice_submitted_accounting_system_name - The name of the accounting system that the invoice was submitted to [text]
invoice_type - The invoice type [text]
invoicelink_first_viewed - The date that the Invoice Link for this invoice was first viewed [datetime] invoicelink_last_viewed - The date that the Invoice Link for this invoice was last viewed [datetime]
is_recurring_invoice - A flag indicating whether the invoice was generated from a recurring invoice [boolean]
job_completed_on - The date on which the job associated with this invoice was completed [datetime]
job_cost - The sum total of the costs of all job items on the invoiced job; null if the invoice has no associated job [number]
job_id - The internal ID of the invoiced job (matches id field in the Jobs dataset) [number]
job_number - The job number for the invoiced job [number]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on the invoiced job [text]
job_status - The current status of the invoiced job [text]
job_type - The invoiced job type [text]
location_id - The ID of invoice's location (matches id field in the Locations dataset) [number]
location_latitude - The latitude of the invoiced job's location [number]
location_longitude - The longitude of the invoiced job's location [number]
location_offices - A list of the names of the offices assigned to the invoiced job's location [comma-separated text]
location_postal_code - The postal code of the state or territory of the invoiced job's location [text]
location_regions - A list of the names of the regions containing the invoiced job's location [comma-separated text]
location_state - The abbreviation of the state or territory of the invoiced job's location [text]
location - The name of the invoice's location [text]
name - The invoice name as seen in several parts of the ST app. This will often include other data points in the name, such as Job Number and Company Name [text]
office_id - The ID of the office associated with the invoice [number]
office - The name of the office associated with the invoice [text]
owner_id - The user_id of the user that currently owns the invoice [number]
paid - A flag that indicates if the invoice has been paid [boolean]
partial - A flag indicating whether this invoice is the final invoice or not. If the flag is true, the invoice IS partial and thus NOT final. If the flag is false, the invoice is NOT partial and thus IS final [boolean]
payment_terms_id - The Payment Terms id of the set of payment terms that apply to this invoice (eg. Net 30, Net 15) [number]
purchase_order_number - The PO Number of the invoice [text]
quote_cost - The sum of all quote costs for all quotes tied to the job of the invoice in question [number]
quote_id - The internal ID of the quote associated with the invoice (matches id field in the Quotes dataset) [number]
quote_subtotal - The sum of all quote totals for all quotes tied to the job of the invoice in question [number]
quote_type - The quote type of the associated invoice [text]
sent - A flag indicating whether this invoice has been sent to the customer or not [boolean]
status - The current status of the invoice [text]
subtotal - The amount of the invoice, not including tax [number]
terms_id - The Terms id of the specific Terms and Conditions that apply to this invoice [number]
total - The total amount of the invoice, including tax [number]
total_paid_amount - The sum of all payment amounts made on the invoice thus far [number]
transaction_date - The invoice transaction date [datetime]
updated - The date on which the invoice was most recently updated [datetime]

Items

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

A record in the Items table will refresh when any change is made to the corresponding item.

Fields:

active - The current status of the item [number]
code - The internal code associated with this item [number]
cost - The unit cost of the item [number]
created - The date on which the item was created [datetime]
customer_id - The id of the customer that the item belongs to [number]
customer - The name of the customer that the item belongs to [text]
id - The internal ID of the item [number]
name - The name of the item [text]
service_line_id - The internal ID of the item's service line [number]
service_line - The name of the item's service line [text]
taxable- Whether this item is taxable [number]
type - The type of item [number]
updated - The date on which the item was most recently updated [datetime]

 

Job Contacts

The 'job_contacts' dataset contains information about the contacts associated with the jobs in your ServiceTrade account.  Each row represents a single contact associated with either the job's location or the job's customer company.

All Job Contact records are refreshed every time the data warehouse data updates.

Fields:

alternate_phone - The alternate phone number of the contact [text]
created_by - The full name of the user who created the contact associated with the job [text]
created - The date on which the contact was created [datetime]
email - The email address of the contact [text]
first_name - The first name of the contact [text]
id - The internal ID of the contact [number]
job_customer_id - The ID of the job's customer [number]
job_customer - The name of the ServiceTrade company which is the customer for this job [text]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
job_location - The name of the job's location [text]
job_number - The job number [number]
job_type - The job type [text]
last_name - The last name of the contact [text]
mobile - The mobile phone number of the contact [text]
phone - The primary phone number of the contact [text]
status - The current status of the contact's association with the job, e.g. "Primary", "Secondary", "Inactive" [text]
type - The contact type [text]
updated - The date on which the contact was most recently updated [datetime]

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.

All Job_Item records are refreshed every time the data warehouse data updates.

Fields:

cost - The unit cost of the job item [number]
created - The date on which the job item was created [datetime]
id - The internal ID of the job item [number]
item_code - The item code of the job item [text]
item_id - The internal ID of the item that is associated with the job item [id]
item_name - The name of the job item [text]
item_type - The item type of the job item [text]
job_id - The internal ID of the job (matches id field in the Jobs dataset) [number]
job_number - The job number [number]
job_status - The job's current status [text]
job_type - The job type [text]
quantity - The quantity of the job item [number]
service_asset - The name of the asset for the service associated with this job item [text]
service_description -The name of the service associated with this job item [text]
service_id - The id of the service associated with this job item [number]
service_line_id - The internal ID of the Job's service line [number]
service_line - The name of the job item's service line [text]
source_delivery_date - The delivery date of this job item (populated only for job items whose source type is 'Parts Vendor') [datetime]
source_details - Additional details about the source of this job item; populated with the purchase order number for job items whose source type if 'Parts Vendor', and empty for all other job items [text]
source_status - The source status of this job item; one of: 'Not Yet Ordered', 'Ordered', 'Received' (populated only for job items whose source type is 'Parts Vendor') [text]
source_type - The type of source for this job item; one of: 'Parts Vendor', 'Warehouse', 'Technician', 'Reference Number' [text]
source - The name of the source for this job item (technician name, warehouse name, or parts vendor name) [text]
total_cost - The total cost of the job item (job item quantity multiplied by job item unit cost) [number]
updated - The date on which the job item was most recently updated [datetime]
used_on - The date on which this job item was used [datetime]

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. A record in the Jobs table will refresh when any change is made to the corresponding service request.

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.

A Job record will update whenever any change is made to the Job, its Job_Items, Appointments tied to the Job, or Invoices tied to the Job.

Fields:

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]
all_appts_completed - A flag indicating whether all the appointments on the job are complete [boolean]
appt_count - The number of appointments on the job [number]
appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
appt_first_start - The date and time on which the first 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]
appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
appt_total_duration - The sum total of the durations of all appointments on the job, in hours [number]
assigned_to_office - The name of the office assigned to the user who is assigned as the owner of the job [text]
assigned_to - The name of the ServiceTrade user who is assigned as the owner of the job [text]
completed_appt_count - The number of completed appointments on the job [number]
completed_appt_first_end - The date and time on which the first completed appointment on the job was 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_last_end - The date and time on which the last 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_total_duration - The sum total of the durations of all completed appointments on the job, in hours [number]
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]
completed_on - The date on which the job was marked complete [datetime]
contract_id - The ID of the contract assigned to the job [text]
contract_name - The name of the contract assigned to the job [text]
created - The date on which the job was created [datetime]
created_by - The name of the ServiceTrade user who created the job [text]
customer_id - The ID of the job's customer [number]
customer - The name of the ServiceTrade company which is the customer for this job [text]
description - The job description [text]
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]
en_route_total_minutes - The sum total of the durations of all en route clock events for the job [number]
first_completed_by - The name of the ServiceTrade user who first completed the job [text]
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_completed - The elapsed time between when the job was created and when it was marked complete, in hours [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_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_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_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]
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]
id - The internal ID of the job [number]
invoice_status - The current status of the invoice associated with the job [text]
invoiced - A flag indicating if an invoice has been created for the job [boolean]
is_attachment_added - A flag indicating whether the job has at least one attachment added [boolean]
is_completed - A flag indicating whether the job has been marked as complete [boolean]
is_deficiency - A flag indicating whether the job is for a deficiency [boolean]
is_enroute_sent - A flag indicating whether the job has an appointment enroute sent [boolean]
is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
is_public_comment_added - A flag indicating whether the job has at least one public comment added [boolean]
is_review_requested - A flag indicating whether the job has a service review request [boolean]
is_servicelink_sent - A flag indicating whether a service link was sent to the customer [boolean]
is_servicelink_viewed - A flag indicating whether the job's service link has been viewed [boolean]
is_workack_created - A flag indicating whether the job has a workack created [boolean]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount [number]
job_canceled_count - The number of times the job was canceled [number]
job_completed_services_estimated_duration - The sum total of the estimated duration of all completed 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_cost - The sum total of the costs of all job items on 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]
job_due_end - The end of the due window for the job [datetime]
job_due_start - The beginning of the due window for the job [datetime]
job_estimated_duration - The sum total of the estimated duration of all services on the job [number]
job_estimated_price - The sum total of the estimated price of all services on the job [number]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_estimated_price [number]
job_first_appt_completed - The date on which the appointment associated with the job was first completed [datetime]
job_first_canceled_by - The name of the user who first canceled the job, if it has been canceled multiple times [text]
job_first_canceled - The date and time on which the job was first canceled [datetime]
job_first_completed - The date and time on which the job was first completed [datetime]
job_first_invoice - The date on which the job was first invoiced [datetime]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_invoice_count - The number of invoices for the job [number]
job_last_appt_completed - The date on which the appointment associated with the job was last completed [datetime]
job_last_canceled_by - The name of the user who last canceled the job [text]
job_last_canceled - The date and time on which the job was last canceled [datetime]
job_last_completed - The date and time on which the job was last completed [datetime]
job_last_invoice - The date on which the job was last invoiced [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_name - The name describing the purpose of the job [text]
job_number - The job number [number]
job_open_services_estimated_duration - The sum total of the estimated duration of all incomplete services on the job [number]
job_open_services_estimated_price - The sum total of the estimated price of all incomplete services on the job [number]
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]
job_prep_total_minutes - The sum total of the durations of all job preparation clock events for the job [number]
job_service_lines - A list of the names of the service lines associated with this job's services [comma-separated text]
job_technician_offices - The offices to which the technicians on the job belong [comma-separated text]
job_technicians - The technicians on the job [comma-separated text]
job_type - The job type [text]
last_completed_by - The name of the ServiceTrade user who last completed the job [text]
location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
location_latitude - The latitude of the job's location [number]
location_longitude - The longitude of the job's location [number]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location_postal_code - The postal code of the state or territory of the job's location [text]
location_regions - A list of the names of the regions containing the job's location [comma-separated text]
location_state - The abbreviation of the state or territory of the job's location [text]
location - The name of the job's location [text]
office_id - The ID of the office associated with the job [number]
office - The name of the office assigned to the job [text]
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]
on_site_total_minutes - The sum total of the durations of all on site clock events for the job [number]
percent_complete - The level at which the associated job is complete, as a percentage [number]
project_end_date - The end date of the project associated with the job [datetime]
project_id - The internal id of the project associated with the job [number]
project_start_date - The start date of the project associated with the job [datetime]
purchase_order_number - The customer purchase order number for this job [text]
review_requested - A flag indicating whether a service review request was sent to the customer [boolean]
sales - The name of the ServiceTrade user who is assigned as the salesperson of the job [text]
servicelink_first_sent - The date and time on which the job's service link was first 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_sent - The date and time on which the job's service link was last sent to the customer [datetime]
servicelink_last_viewed - The date and time on which the job's service link was last viewed the customer [datetime]
status - The job's current status [text]
technician_count - The number of technicians on the job [number]
terms_id - The ID of the Terms and Conditions policy assigned to the Job [number]
updated - The date on which the job was most recently updated [datetime]
vendor - For subcontracted jobs, the name of the ServiceTrade company which is the vendor performing service for this job [text]

Locations

The 'locations' data set contains information about locations in your ServiceTrade account.  All locations in all statuses (including inactive locations) are represented in this dataset.

A record in the Locations table will refresh when any change is made to the corresponding location.

Fields:

address - The location street address [text]
city - The location city [text]
created - The date on which the location was created [datetime]
created_by - The name of the ServiceTrade user who created the location [text]
customer_id - The ID of the ServiceTrade company which owns this location [number]
customer - The name of the ServiceTrade company which owns this location [text]
email - The location email address [text]
id - The internal ID of the location [number]
latitude - The location latitude [number]
location_is_office - A flag indicating whether the location is considered an office [boolean]
longitude - The location latitude [number]
name - The location name [text]
offices - A list of the names of the offices assigned to the location [comma-separated text]
phone - The location phone number [text]
postal_code - The location postal code [text]
regions - A list of the names of the regions containing the location [comma-separated text]
state - The abbreviation of the location state or territory [text]
status - The location status [text]
tax_group_code - A code to identify the tax group associated with the location [text]
tax_group_id - The internal ID of the tax group associated with the location [number]
tax_group_state- The state/province/region associated with the location's tax group [text]
taxable - A flag indicating whether items at this location are taxable or not [boolean]
updated - The date on which the location was most recently updated [datetime]

Marketing Impressions

The 'marketing_impressions' dataset contains information about marketing impressions -- actions you perform that your customer can experience -- that occur during service delivery.  In this dataset, each row represents a single action that created a customer marketing impression.

All Marketing Impression records are refreshed every time the data warehouse data updates.

Fields:

action_name - The name of the action that was performed [text]
action - The internal code name of the action that was performed [text]
attachment_content_url - Content URL of the attachment associated with the MIPS action/record [text]
attachment_description - Description of the attachment associated with the MIPS action/record [text]
attachment_file_extension - File extension (Eg. JPEG) of the attachment associated with the MIPS action/record [text]
attachment_file_name - File name of the attachment associated with the MIPS action/record [text]
attachment_file_type - File type (Eg. Image) of the attachment associated with the MIPS action/record [text]
attachment_id - Attachment Id of the attachment associated with the MIPS action/record [number]
attachment_purpose - Purpose of the attachment associated with the MIPS action/record [text]
created - The date on which the action was created [datetime]
customer_id - The ID of the customer of the associated record [number]
customer - The name of the ServiceTrade company which is the customer for the associated record [text]
id - The internal ID of the action [number]
location_id - The ID of the location of the associated record (matches id field in the Locations dataset) [number]
location_offices - A list of the names of the offices assigned to the location of the associated record [comma-separated text]
location - The name of the location of the associated record [text]
office_id - The ID of the office associated with the marketing impression
record_action_date - The date on which the action occurred [datetime]
record_assigned_to - The name of the user to which the action's associated record is assigned text]
record_created - The date on which the action was created [datetime]
record_id - The internal ID of the record with which the action was associated (for instance, a job ID or a quote ID) [number]
record_number - The number of the record with which the action was associated [text]
record_type - The type of record with which the action was associated [text]
subrecord_id - The internal ID of the subrecord with which the action was associated (for instance, an attachment ID or a clock event ID on a job record) [number]
updated - The date on which the action was most recently updated [datetime]
user_email - The e-mail address of the user who performed this action[text]
user_first_name - The first name of the user who performed this action [text]
user_id - The internal ID of the user who initiated the action [number]
user_is_admin - A flag to indicate whether the user who performed this action is an admin [boolean]
user_is_sales - A flag to indicate whether the user who performed this action is a sales person [boolean]
user_is_tech - A flag to indicate whether the user who performed this action is a technician [boolean]
user_last_name - The last name of the user who performed this action [text]
user_name - The full name of the user who performed this action [text]
user_status - The current status of the user (active, inactive, etc) who performed this action[text]
user_username - The username of the user who performed this action [text]

PartsManager Items

The 'partsledger_items' dataset contains information about partsmanager items in your PartsManager account.

A record in the PartsLedger_Items table will refresh when any change is made to the corresponding item.

Fields:

_id - The internal ID of the partsmanager item [number]
inactive - Flag stating where the partsmanager item is inactive [bool]
updated - The date on which the partsmanager item was most recently updated [datetime]
account_partsledger_uuid - The uuid of the partsmanager account [number]
cogs_account_id- The internal ID of the cogs account associated with the partsmanager item [number]
cogs_account_name - The name of the partsmanager item's receiving cogs account [text]
cogs_account_number - The number of the partsmanager item's receiving cogs account[text]
created - The date on which the partsmanager item was created [datetime]
default_purchase_price - The base price for the partsmanager item [number]
description- The description of the partsmanager item [text]
inventory_account_id- The internal ID of the inventory account associated with the partsmanager item [number]
inventory_account_name - The name of the partsmanager item's receiving inventory account [text]
inventory_account_number - The number of the partsmanager item's receiving inventory account[text]
name - The name of the partsmanager item [text]
number - The number of the partsmanager item [text]
sku - The SKU of the partsmanager item [text]

PartsManager Kits

The 'partsledger_kits' dataset contains information about kits in your PartsManager account.

A record in the PartsLedger_Kits table will refresh when any change is made to the corresponding kit.

Fields:

_id - The internal ID of the kit [number]
account_partsledger_uuid - The UUID of the PartsManager account associated with the kit [text]
created - The date on which the kit was created [datetime]
description - The description of the kit [text]
name - The name of the kit [text]
number - The number of the kit [number]
sku - The SKU of the kit [text]
updated - The date on which the kit was most recently updated [datetime]

PartsManager Purchase Order Line Items

The 'partsledger_purchase_order_items' dataset contains information about individual purchase order line items in your PartsManager account.

A record in the PartsLedger_Purchase_Order_Items table will refresh when any change is made to the corresponding PO Item.

Fields:

_id - The internal ID of the purchase order item [number]
amount - The total amount of the purchase order item [number]
created - The date on which the purchase order item was created [datetime]
description - The purchase order item description [text]
item_id - The internal ID of the purchase order item's associated item [number]
item_name - The name of the purchase order item's associated item [text]
item_number - The number of the purchase order item's associated item [text]
item_sku - The number of the purchase order item's associated SKU [text]
ledger_account_id - The internal ID of the purchase order item's GL account [number]
ledger_account_name - The name of the purchase order item's receiving GL account [text]
ledger_account_number - The number of the purchase order item's receiving GL account [text]
purchase_order_display_number - The purchase order item's parent purchase order number, as it is displayed in the PartsManager user interface [text]
purchase_order_external_id - The purchase order item's parent purchase order's external ID [text]
purchase_order_id - The internal ID of the purchase order item's parent purchase order [number]
purchase_order_number - The purchase order item's parent purchase order number [text]
quantity_received - The quantity received of the purchase order item [number]
quantity - The quantity ordered of the purchase order item [number]
sales_order_display_number - The number of the purchase order item's associated sales order, as it is displayed in the PartsManager user interface [number]
sales_order_external_id - The external ID of the purchase order item's associated sales order [text]
sales_order_external_reference - The external reference of the purchase order item's associated sales order [text]
sales_order_id - The internal ID of the purchase order item's associated sales order [number]
sales_order_number - The number of the purchase order item's associated sales order [number]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
tax_amount - The total tax amount of the purchase order item [number]
updated - The date on which the purchase order item was most recently updated [datetime]

PartsManager Purchase Orders

The 'partsledger_purchase_orders' dataset contains information about purchase orders in your PartsLedger account.

A record in the PartsLedger_Purchase_Orders table will refresh when any change is made to the corresponding PO.

Fields:

_id - The internal ID of the purchase order [number]
amount - The total amount of the purchase order [number]
approved_by_email - The email address of the user who approved the purchase order [text]
approved_by - The full name of the user who approved the purchase order [text]
approved_date - The date on which the purchase order was approved [datetime]
billing_address_city - The city the purchase order billing address [text]
billing_address_id - The internal ID of the purchase order billing address [number]
billing_address_line1 - The first line of the purchase order billing address [text]
billing_address_line2 - The second line of the purchase order billing address [text]
billing_address_name - The name of the purchase order billing address [text]
billing_address_postal_code - The postal code of the purchase order billing address [text]
billing_address_state - The state/province of the purchase order billing address [text]
created - The date on which the purchase order was created [datetime]
delivery_date - The date on which the purchase order was delivered [datetime]
description - The purchase order description [text]
display_number - The purchase order number, as it is displayed in the PartsManager user interface [text]
external_id - The purchase order's external ID [text]
issue_date - The date on which the purchase order was issued [datetime]
location_id - The internal ID of the location associated with the purchase order [number]
location_name - The name of the location associated with the purchase order [text]
notes - The purchase order notes [text]
number - The purchase order number [number]
receiving_account_id - The internal ID of the receiving GL account [number]
receiving_account_name - The name of the receiving GL account [text]
receiving_account_number - The number of the receiving GL account [text]
shipping_address_city - The city the purchase order shipping address [text]
shipping_address_id - The internal ID of the purchase order shipping address [number]
shipping_address_line1 - The first line of the purchase order shipping address [text]
shipping_address_line2 - The second line of the purchase order shipping address [text]
shipping_address_name - The name of the purchase order shipping address [text]
shipping_address_postal_code - The postal code of the purchase order shipping address [text]
shipping_address_state - The state/province of the purchase order shipping address [text]
status - The status of the purchase order [text]
updated - The date on which the purchase order was most recently updated [datetime]
vendor_id - The internal ID of the vendor associated with the purchase order [number]
vendor_name - The name of the vendor associated with the purchase order [text]
warehouse_id - The internal ID of the warehouse associated with the purchase order [number]
warehouse_name - The name of the warehouse associated with the purchase order [text]

PartsManager Sales Order Line Items

The 'partsledger_sales_order_items' dataset contains information about individual purchase order line items in your PartsLedger account.

A record in the PartsLedger_Sales_Order_Items table will refresh when any change is made to the corresponding SO Item.

Fields:

_id - The internal ID of the sales order item [number]
amount - The total amount of the sales order item [number]
created - The date on which the sales order item was created [datetime]
description - The sales order item description [text]
display_number - The sales order item number, as displayed in the PartsManager user interface [text]
external_id - The sales order item external ID [text]
item_id - The internal ID of the item associated with the sales order item [number]
item_name - The name of the item associated with the sales order item [text]
item_number - The number of the item associated with the sales order item [text]
item_sku - The SKU of the item associated with the sales order item [text]
number - The sales order item number [number]
purchase_order_display_number - The sales order item's associated purchase order number, as it is displayed in the PartsManager user interface [text]
purchase_order_external_id - The sales order item's associated purchase order's external ID [number]
purchase_order_id - The sales order item description [text]
purchase_order_item_id - The internal ID of the sales order item's associated purchase order item [number]
purchase_order_number - The sales order item's associated purchase order number [text]
quantity_fulfilled - The quantity fulfilled of the sales order item [number]
quantity - The quantity ordered of the sales order item [number]
sales_order_display_number - The number of the sales order item's parent sales order, as it is displayed in the PartsManager user interface [text]
sales_order_external_id - The external ID of the purchase order item's associated sales order [text]
sales_order_external_reference - The external reference of the purchase order item's associated sales order [text]
sales_order_id - The internal ID of the sales order item's parent sales order [number]
sales_order_number - The number of the sales order item's parent sales order [number]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
tax_amount - The total tax amount of the sales order item [number]
updated - The date on which the sales order item was most recently updated [datetime]

PartsManager Sales Orders

The 'partsledger_sales_orders' dataset contains information about sales orders in your PartsManager account.

A record in the PartsLedger_Sales_Orders table will refresh when any change is made to the corresponding SO.

Fields:

_id - The internal ID of the sales order [number]
amount - The total amount of the sales order [number]
approved_by - The full name of the user who approved the sales order [text]
approved_by_email - The email address of the user who approved the sales order [text]
approved_date - The date on which the sales order was approved [datetime]
complete_date - The date on which the sales order was completed [datetime]
created - The date on which the sales order was created [datetime]
delivery_date - The date on which the sales order was delivered [datetime]
display_number - The sales order number, as it is displayed in the PartsManager user interface [text]
external_id - The sales order's external ID [text]
external_reference - The sales order's external reference [text]
location_id - The internal ID of the location associated with the sales order [number]
location_name - The name of the location associated with the sales order [text]
notes - The sales order notes [text]
number - The sales order number [number]
order_date - The date on which the sales order was ordered [datetime]
servicetrade_job_number - The sales order's corresponding ServiceTrade job number [number]
status - The status of the sales order [text]
updated - The date on which the sales order was most recently updated [datetime]

PartsManager Shipment Receipt Line Items

The 'partsledger_shipment_receipt_items' dataset contains information about individual shipment receipt line items in your PartsManager account.

A record in the PartsLedger_Shipment_Receipt_Items table will refresh when any change is made to the corresponding Shipment Receipt Item.

Fields:

_id - The internal ID of the shipment receipt item [number]
amount - The total amount of the shipment receipt item [number]
purchase_order_display_number - The shipment receipt item's parent purchase order number, as it is displayed in the PartsManager user interface [text]
purchase_order_external_id
-
The shipment receipt item's parent purchase order's external ID [text]
purchase_order_id
-
The internal ID of the shipment receipt item's associated purchase order [number]
purchase_order_item_id
- The internal ID of the shipment receipt item's associated purchase order item [number]
purchase_order_number - The shipment receipt item's parent purchase order number [text]
created - The date on which the shipment receipt item was created [datetime]
item_id - The internal ID of the shipment receipt item's associated item [number]
item_name - The name of the shipment receipt item's associated item [text]
item_number - The number of the shipment receipt item's associated item [text]
item_sku - The number of the shipment receipt item's associated SKU [text]
purchase_order_item_quantity_received - The quantity received of the shipment receipt item's associated purchase order item [number]
purchase_order_item_quantity - The quantity ordered of the shipment receipt item's associated purchase order item [number]
sales_order_item_id - The internal ID of the shipment receipt item's associated sales order item [number]
sales_order_display_number - The number of the shipment receipt item's associated sales order, as it is displayed in the PartsManager user interface [text]
sales_order_external_id - The external ID of the shipment receipt item's associated sales order [text]
sales_order_external_reference - The external reference of the pshipment receipt item's associated sales order [text]
sales_order_id - The internal ID of the shipment receipt item's associated sales order [number]
sales_order_item_quantity_fulfilled - The quantity fulfilled of the shipment receipt item's associated sales order item [number]
sales_order_item_quantity - The quantity ordered of the shipment receipt item's associated sales order item [number]
sales_order_number - The number of the shipment receipt item's associated sales order [number]
servicetrade_job_number - The purchase order item's associated sales order's corresponding ServiceTrade job number [number]
shipment_receipt_id - The internal ID of the shipment receipt item's parent shipment receipt [number]
shipment_receipt_reference - The shipment receipt item's parent shipment receipt's reference number [text]
shipment_receipt_vendor_id - The internal ID of the vendor associated with the shipment receipt item [number]
shipment_receipt_vendor_name - The name of the vendor associated with the shipment receipt item [text]
status - The status of the shipment receipt item [text]
updated - The date on which the shipment receipt item was most recently updated [datetime]
warehouse_id - The internal ID of the shipment receipt item's associated warehouse [number]

PartsManager Shipment Receipts

The 'partsledger_shipment_receipts' dataset contains information about shipment receipts in your PartsManager account.

A record in the PartsLedger_Shipment_Receipts table will refresh when any change is made to the corresponding Shipment Receipt.

Fields:

_id - The internal ID of the shipment receipt [number]
created - The date on which the shipment receipt was created [datetime]
receipt_date - The shipment receipt date [datetime]
reference - The shipment receipt reference number [text]
updated - The date on which the shipment receipt was most recently updated [datetime]
vendor_id - The internal ID of the vendor associated with the shipment receipt [number]
vendor_name - The name of the vendor associated with the shipment receipt [text]

PartsManager Stock Adjustments

The 'partsledger_stock_adjustments' dataset contains information about stock adjustments in your PartsManager account.

A record in the PartsLedger_Stock_Adjustments table will refresh when any change is made to the corresponding Stock Adjustment.

Fields:

_id - The internal ID of the stock adjustment [number]
adjustment_ledger_account_id - The internal ID of the adjustment GL ledger account associated with the stock adjustment [number]
average_cost - The cost basis for the stock adjustment [number]
cost_basis - The cost basis for the stock adjustment [number]
cost_layers - The sets of cost information underlying the average cost, as a JSON object [text]
created - The date on which the stock adjustment was created [datetime]
description- The description of the stock adjustment [number]
item_id - The internal ID of the item whose stock was adjusted [number]
item_name - The name of the item whose stock was adjusted [text]
item_number - The number of the item whose stock was adjusted [text]
item_sku - The SKU of the item whose stock was adjusted [text]
journal_entry_type - The type of the journal entry associated with the stock adjustment [text]
journal_id - The internal ID of the journal entry associated with the stock adjustment [number]
journal_number - The number of the journal entry associated with the stock adjustment [text]
journal_reference - The reference of the journal entry associated with the stock adjustment [text]
journal_source_ledger - The source ledger of the journal entry associated with the stock adjustment [text]
journal_status - The status of the journal entry associated with the stock adjustment [text]
locked - Whether the stock adjustment is locked [boolean]
posted_date - The posted date of the stock adjustment [datetime]
price - The price for the stock adjustment [number]
strong
production_item_id - The internal ID of the production item associated with the stock adjustment [number]
purchase_order_item_id - The internal ID of the purchase order item associated with the stock adjustment [number]
quantity - The quantity of the stock adjustment [number]
quantity_available - The quantity available to be picked for the stock adjustment [number]
quantity_picked - The quantity picked for the stock adjustment [number]
sales_order_item_id - The internal ID of the sales order item associated with the stock adjustment [number]
shipment_receipt_item_id - The internal ID of the shipment receipt item associated with the stock adjustment [number]
transaction_date - The transaction date of the stock adjustment [datetime]
transfer_id - The internal ID of the stock transfer associated with the stock adjustment [number]
type - The type of the stock adjustment [text]
updated - The date on which the stock adjustment was most recently updated [datetime]
warehouse_address_city - The address city of the warehouse associated with the stock adjustment [text]
warehouse_address_line1 - The first line of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_line2 - The second line of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_name - The name of the address of the warehouse associated with the stock adjustment [text]
warehouse_address_postal_code - The address postal code of the warehouse associated with the stock adjustment [text] warehouse_address_state - The address state/province of the warehouse associated with the stock adjustment [text]
warehouse_description - The description of the warehouse associated with the stock adjustment [text]
warehouse_name - The name of the warehouse associated with the stock adjustment [text]
warehouse_id - The internal ID of the warehouse associated with the stock adjustment [text]

PartsManager Transactions

The 'partsledger_transactions' dataset contains information about GL transactions in your PartsManager account.  Each row in this table will represent either a credit line ('credit' value is nonzero, and 'debit' value is zero) or a debit line ('debit' value is nonzero, and 'credit' value is zero), never both a credit and a debit.

A record in the PartsLedger_Transactions table will refresh when any change is made to the corresponding Transaction.

Fields:

_id - The internal ID of the transaction [number]
cost_center_description - The description of the cost center associated with the transaction [text]
cost_center_name - The name of the cost center associated with the transaction [text]
cost_center_id - The internal ID of the cost center associated with the transaction [number]
created - The date on which the transaction was created [datetime]
credit - The credit amount of the transaction; if credit is nonzero, then debit will be zero [number]
custom_1_id - The internal ID of the first custom field associated with this transaction [number]
custom_2_id - The internal ID of the second custom field associated with this transaction [number]
custom_3_id - The internal ID of the third custom field associated with this transaction [number]
debit - The debit amount of the transaction; if debit is nonzero, then credit will be zero [number]
description - The description of the transaction [text]
journal_entry_type - The entry type of the journal entry associated with the transaction [text]
journal_id - The internal ID of the journal entry associated with the transaction [number]
journal_number - The number of the journal entry associated with the transaction [text]
journal_reference - The reference information for the journal entry associated with the transaction [text] journal_source_ledger - The name of the source ledger of the journal entry associated with the transaction [text]
journal_status - The status of the journal entry associated with the transaction [text]
ledger_account_id - The internal ID of the transaction's GL account [number]
ledger_account_name - The name of the transaction's GL account [text]
ledger_account_number - The number of the transaction's GL account [text]
location_address_city - The city of the address of the location associated with the transaction [text]
location_address_line1
- The first line of the address of the location associated with the transaction [text]
location_address_line2
- The second line of the address of the location associated with the transaction [text]
location_address_postal_code -
The postal code of the address of the location associated with the transaction [text]
location_address_state
- The state/province of the address of the location associated with the transaction [text]
location_entity_email -
The entity email address of the location associated with the transaction [text]
location_entity_phone -
The entity phone number of the location associated with the transaction [text] 
location_id - The internal ID of the location associated with the transaction [number]
location_name - The name of the location associated with the transaction [text]
location_entity_name - The entity name of the location associated with the transaction [text]
posted_date - The posted date of the transaction [datetime]
sales_order_id - The internal ID of the sales order associated with the transaction [number]
transaction_date - The date of the transaction [datetime]
updated - The date on which the transaction was most recently updated [datetime]
vendor_id - The internal ID of the vendor associated with the transaction [number]
vendor_name - The name of the vendor associated with the transaction [text]

PartsManager Transfers

The 'partsledger_transfers' dataset contains information about transfers in your PartsManager account.

A record in the PartsLedger_Transfers table will refresh when any change is made to the corresponding Transfer record.

Fields:

_id - The internal ID of the transfer [number]
created - The date on which the transfer was created [datetime]
date - The date of the transfer [datetime]
from_warehouse_address_city - The city of the address of the warehouse that the item was transferred from [text]
from_warehouse_address_line_1 - The first line of the address of the warehouse that the item was transferred from [text]
from_warehouse_address_line_2  - The second line of the address of the that the item was transferred from [text]
from_warehouse_address_postal_code - The postal code of the address of the that the item was transferred from [text]
from_warehouse_address_state - The state/province of the address of the that the item was transferred from [text]
from_warehouse_description - The description of the warehouse that the item was transferred from [text]
from_warehouse_id - The internal ID of the warehouse that the item was transferred from [number]
from_warehouse_name - The name of the warehouse that the item was transferred from [text]
item_id - The internal ID of the item that was transferred [number]
quantity - The quantity of the transfer [number]
status - The status of the transfer [text]
to_warehouse_address_city - The city of the address of the warehouse that the item was transferred to [text]
to_warehouse_address_line_1 - The first line of the address of the warehouse that the item was transferred to [text]
to_warehouse_address_line_2 - The second line of the address of the that the item was transferred to [text]
to_warehouse_address_postal_code - The postal code of the address of the that the item was transferred to [text]
to_warehouse_address_state - The state/province of the address of the that the item was transferred to [text]
to_warehouse_description - The description of the warehouse that the item was transferred to [text]
to_warehouse_id- The internal ID of the warehouse that the item was transferred to [number]
to_warehouse_name- The name of the warehouse that the item was transferred to [text]
updated - The date on which the transfer was most recently updated [datetime]

PartsManager Vendors

The 'partsledger_vendors' dataset contains information about vendors in your PartsMananger account.

A record in the PartsLedger_Vendors table will refresh when any change is made to the corresponding vendor.

Fields:

_id - The internal ID of the vendor [number]
acc_number - The account number associated with the vendor [text]
account_name - The name of the account associated with the vendor [text]
account_partsledger_uuid - The UUID of the PartsManager account associated with the vendor [text]
address_city - The city of the vendor's address [text]
address_country - The country of the vendor's address [text]
address_state - The state/province of the vendor's address [text]
address_zip - The postal code of the vendor's address [number]
address1 - The first line of the vendor's address [text]
address2 - The second line of the vendor's address [text]
created - The date on which the vendor was created [datetime]
currency - The currency associated with the vendor [text]
default_address_city - The city of the default address associated with the vendor [text]
default_address_line1 - The first line of the default address associated with the vendor [text]
default_address_line2 - The second line of the default address associated with the vendor [text]
default_address_name - The default name associated with the vendor's address [text]
default_address_postal_code - The postal code of the default address associated with the vendor [text]
default_address_state - The state/province of the default address associated with the vendor [text]
ein - The vendor's Employer Identification Number [text]
external_id - The vendor's external ID [text]
inactive - Flag stating whether the vendor is inactive [boolean]
is1099 - Flag stating whether a 1099 should be issued to the vendor [boolean]
name - The name of the vendor [text]
terms - The terms associated with the vendor [text]
updated - The date on which the vendor was most recently updated [datetime]

PartsManager Warehouses

The 'partsledger_warehouses' dataset contains information about warehouses in your PartsManager account.

A record in the PartsLedger_Warehouses table will refresh when any change is made to the corresponding Warehouse.

Fields:

_id - The internal ID of the warehouse[number]
account_partsledger_uuid - The uuid of the partsmanager account [number]
address_city - The address city of the warehouse [text]
address_line1 - The first line of the address of the warehouse [text]
address_line2 - The second line of the address of the warehouse [text]
address_name - The name of the address of the warehouse [text]
address_postal_code - The address postal code of the warehouse [text]
address_state - The address state/province of the warehouse [text]
created - The date on which the warehouse was created [datetime]
description - The description of the warehouse [text]
location_name - The name of the location of the warehouse [text]
name - The name of the warehouse [text]
parent_warehouse_id - The internal ID of the warehouse’s parent warehouse[text]
parent_warehouse_name - The name of the warehouse’s parent warehouse [text]
updated - The date on which the warehouse was most recently updated [datetime]

Payments

The 'payments' dataset contains information about individual payment records. Each row represents a single payment instance.

A record in the Payments table will refresh when any change is made to the corresponding payment.

Fields:

amount - The amount of the payment [number]
created - The date on which the payment record was created [datetime]
creator_id - The user_id of the user that created the payment [number]
customer_id - The ID of the customer associated with this payment [number]
customer - The name of the ServiceTrade company which is the customer for this associated payment [text]
id - The internal ID of the payment [number]
invoice_id - The internal ID of the invoice associated with the payment record [number]
invoice_status - The current status of the invoice associated with the payment [text]
job_id - The internal ID of the job associated with the payment (matches id field in the Jobs dataset) [number]
job_type - The job type of the associated payment [text]
payment_method - The payment method [text]
quote_type - The quote type of the associated payment [text]
reference_number - The payment's reference number [text]
source - The source of the payment [text]
status - The payment status [text]
transaction_date - The payment transaction date [datetime]
updated - The date on which the payment record was most recently updated [datetime]

Quote Items

The 'quote_items' dataset contains information about the individual quote items associated with quotes in your ServiceTrade account.  Each row represents a single quote item.

All Quote Items records are refreshed every time the data warehouse data updates.

Fields:

cost - The unit cost of the quote item [number]
created - The date on which the quote item was created [datetime]
id - The internal ID of the quote item [number]
item_code - The item code of the quote item [text]
item_name - The name of the quote item [text]
item_type - The item type of the quote item [text]
lib_item_id - The internal ID of the item that is associated with the invoice item [id]
post_tax_total - The total amount of the quote item after taxes are applied [number]
price - The unit price of the quote item [number]
quantity - The quantity of the quote item [number]
quote_created - The date on which the quote item's quote was created [datetime]
quote_id - The internal ID of the quote (matches id field in the Quotes dataset) [number]
quote_updated - The date on which the quote item's quote was most recently updated [datetime]
service_asset - The name of the asset for the service associated with this quote item [text]
service_id - The id of the service associated with this quote item [number]
service_description - The description of the service associated with this quote item [text]
service_line_id - The internal ID of the quote item's service line [number]
service_line - The name of the quote item's service line [text]
tax_rate - The tax rate percentage for this quote item [number]
taxable - Whether this quote item is taxable [number]
total_cost - The total cost of the quote item [number]
total_price - The total price of the quote item with tax (quote item quantity multiplied by quote item unit cost, plus tax) [number]
updated - The date on which the quote item was most recently updated [datetime]

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.

A Quote record will update whenever any change is made to the Quote, its Quote_Items, its related Job, or its related Services.

Fields:

contract_id - The ID of the contract assigned to the quote [text]
contract_name - The name of the contract assigned to the quote [text]
created_by - The name of the ServiceTrade user who created the quote [text]
created - The date on which the quote was created [datetime]
customer_id - The ID of the quote's customer [number]
customer - The name of the ServiceTrade company which is the customer for this quote [text]
expires_on - The date on which this quote expires [datetime]
id - The internal ID of the quote [number]
invoice_status - The current status of the invoice associated with the quote [text]
is_approved - A flag indicating whether the quote was approved by the customer [boolean]
is_attachment_added - A flag indicating whether the quote had an attachment added [boolean]
is_changes_requested - A flag indicating whether the quote had changes requested [boolean]
is_emailed - A flag indicating whether the quote has been emailed out [boolean]
is_sent - A flag indicating whether the quote was sent to the customer for approval [boolean]
is_viewed - A flag indicating whether the quote was viewed by the customer [boolean]
job_actual_margin - The difference between job_cost and job_invoice_amount, as a percentage of job_invoice_amount [number]
job_all_appts_completed - A flag indicating whether all the job's appointments have been completed [boolean]
job_appt_first_end - The date and time on which the first appointment on the job is scheduled to end [datetime]
job_appt_first_start - The date and time on which the first 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_appt_last_start - The date and time on which the last appointment on the job is scheduled to start [datetime]
job_assigned_to_office - The name of the office assigned to the user who is assigned as the owner of this job [text]
job_assigned_to - The name of the ServiceTrade user who is assigned as the owner of this job [text]
job_completed_in_due_window - A flag indicating whether the job was marked as complete before the end of its due window [boolean]
job_completed_on- The date on which the job associated with this quote was completed, if applicable [datetime]
job_cost - The sum total of the costs of all job items on this job [number]
job_created - The date on which the job was created [datetime]
job_due_end - The date on which the due window for the job ends [datetime]
job_due_start - The date on which the due window for the job begins [datetime]
job_expected_margin - The difference between job_cost and job_estimated_price, as a percentage of job_estimated_price (see the jobs dataset for job_estimated_price) [number]
job_first_invoice - The date and time on which the job's first invoice was created [datetime]
job_id - The internal ID of the job associated with the quote (matches id field in the Jobs dataset) [number]
job_invoice_amount - The sum total (not including sales tax) of all invoices associated with the job [number]
job_is_completed - A flag indicating whether the job has been marked as complete [boolean]
job_is_invoiced - A flag indicating whether the job has at least one invoice [boolean]
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_number - The job number for the repair job associated with this quote [number]
job_sales - The name of the ServiceTrade user who is assigned as the salesperson on this job [text]
job_status - The current status of the job [text]
job_type - The job type [text]
job_updated - The date on which the job was most recently updated [datetime]
location_id - The ID of the quote's location (matches id field in the Locations dataset) [number]
location_latitude - The latitude of the quote's location [number]
location_longitude - The longitude of the quote's location [number]
location_offices - A list of the names of the offices assigned to the quote's location [comma-separated text]
location_postal_code - The postal code of the state or territory of the quote's location [text]
location_regions - A list of the names of the regions containing the quote's location [comma-separated text]
location_state - The abbreviation of the state or territory of the quote's location [text]
location - The name of this quote's location [text]
name - The quote name [text]
office_id - The internal ID of the office associated with the quote [number]
owner - The name of the ServiceTrade user who owns the quote [text]
po_number - The PO number associated with the quote [number]
post_tax_total - The total amount of the quote after taxes are applied [number]
quote_approved_count - The number of times the quote was approved by the customer [number]
quote_approved - The date on which the quote was approved [datetime]
quote_assigned_to - The name of the ServiceTrade user who is assigned to the quote [text]
quote_attachment_added_count - The number of times the quote had an attachment added [number]
quote_canceled_count - The number of times the quote was canceled [number]
quote_changes_requested_count - The number of times the quote had changes requested [number]
quote_cost - The total cost of the items on the quote [number]
quote_emailed_count - The number of times the quote has been emailed out [number]
quote_first_approved - The date on which the quote was first approved by the customer [datetime]
quote_first_attachment_added - The date on which the quote first had an attachment added [datetime]
quote_first_canceled - The date and time on which the quote was first canceled [datetime]
quote_first_changes_requested - The date on which the quote first had changes requested [datetime]
quote_first_emailed - The date on which the quote was first emailed out [datetime]
quote_first_sent - The date on which the quote was first 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_approved - The date on which the quote was most recently approved by the customer [datetime]
quote_last_attachment_added - The date on which the quote most recently had an attachment added [datetime]
quote_last_canceled - The date and time on which the quote was last canceled [datetime]
quote_last_changes_requested - The date on which the quote most recently had changes requested [datetime]
quote_last_emailed - The date on which the quote was most recently emailed out [datetime]
quote_last_sent_by - The name of the ServiceTrade user who last sent the quote to customer [text]
quote_last_sent - The date on which the quote was most recently sent to the customer for approval [datetime]
quote_last_viewed - The date on which the quote was most recently viewed by the customer [datetime]
quote_margin - The difference between total and quote_cost, as a percentage of total [number]
quote_name - The name of the quote [text]
quote_number - The quote number [number]
quote_sales - The name of the ServiceTrade user who is assigned as the salesperson on the quote [text]
quote_sent_count - The number of times the quote was sent to the customer for approval [number]
quote_service_lines - A list of the names of the service lines associated with this quote's services [comma-separated text]
quote_type - The type of the job that is being quoted [text]
quote_viewed_count - The number of times the quote was viewed by the customer [number]
status - The quote's current status [text]
terms_id - The ID of the Terms and Conditions policy assigned to the Quote [number]
total - The total quoted amount of the quote [number]  Note that this value is BEFORE tax is applied.  This calculation may be changed in a future data warehouse update.
updated - The date on which the quote was most recently updated [datetime]

Recurring Invoices

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

Each row in this dataset represents a single instance of that recurring invoice.  For instance, an invoice 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.

A record in the Recurring_Invoices table will refresh when any change is made to the corresponding recurring invoice or to the Location of the recurring invoice.

Fields:

cost - The unit cost of the invoice item associated with the recurring invoice [number]
description - The invoice description [text]
frequency - The frequency on which the invoice recurs [text]
id - The internal ID of the recurring invoice [number]
interval - The interval on which the invoice recurs [number]
location_city - The name of the city of the invoiced job's location [text]
location_id - The ID of invoice's location (matches id field in the Locations dataset) [number]
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_state - The abbreviation of the state or territory of the invoiced job's location [text]
location_status - The status of the location of the invoiced job's location [text]
location - The name of the invoice's location [text]
office_id - The ID of the office associated with the recurring invoice
sequence_id - A generated ID for this instance of the recurring invoice [text]
service_line_id - The internal ID of the invoices's service line [number]
service_line - The name of the invoice's service line [text]
total - The total cost of the invoice items associated with the recurring invoice [number]
window_end - The end of the due window for this invoice [datetime]
window_start - The beginning of the due window for this invoice [datetime]

Recurring Services

The 'recurring_services' dataset includes information about recurring services in your ServiceTrade account.  In this dataset, recurring services are projected through (at least) 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.

A record in the Recurring_Services table will refresh when any change is made to the corresponding recurring service or to the Location of the recurring service.

Fields:

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

Roles

The 'roles' dataset contains information about the roles in your ServiceTrade account.  Each row represents a single role.

All Roles records are refreshed every time the data warehouse data updates.

Fields:

created - The date on which the role was created [datetime]
description - The description of the role [text]
id - The internal ID of the role [number]
name - The name of the role [text]
updated - The date on which the role was most recently updated [datetime]

Service Lines

The 'service_lines' dataset includes information about the various service lines that are available in Service Trade. Please note that this table contains the superset of all possible service lines. Based on your particular needs, some of may be turned off in the Service Trade core app, as they are not applicable.

A record in the Service_Lines table will refresh when any change is made to the corresponding service line.

Fields:

abbr - The abbreviation of the service line [text]
name - Service line name [text]
id - Primary key / service line id [number]
trade - The high-level trade that the service line is tied to (Fire Protection, Exhaust Cleaning, Security, etc) [text]

Services

The 'services' dataset includes information about services, both recurring and non-recurring, in your ServiceTrade account. Each row in this dataset represents a single instance of a service.

A record in the 'Services' table will refresh when any change is made to the corresponding service request.

Fields:

asset_id - The internal id of the asset associated with the service [number]
asset - The name of the service's asset [text]
closed_on - The date on which the service was closed [datetime]
contract_id - The ID of the contract assigned to the service [text]
created - The date on which this service was created [datetime]
customer - The name of the ServiceTrade company which is the customer for this associated service [text] deficiency_id - The internal id of the deficiency associated with the service [number]
description - The service description [text]
duration - The estimated duration of the service, in seconds [number]
id - The internal ID of the recurring service [number]
invoice_status - The current status of the invoice associated with the service [text]
job_id - The internal id of the job associated with the service [number]
job_type - The job type of the associated service [text]
location_id - The internal id of the location associated with the service [number]
location - The name of the service's location [text]
name - The name of the associated service [text]
preferred_start_time - The preferred start time for this service [datetime]
preferred_vendor_id - The internal id of the preferred vendor for the service [number]
price - The estimated price of the service [number]
quote_type - The quote type of the associated service [text]
recurrence_id - The internal ID of the recurring service, if there is one [number]
sequence_id - A generated ID for this instance of the service [text]
service_line_id - The internal ID of the service's service line [number]
service_line - The name of the service's service line [text]
status - The status of this service [text]
updated - The date on which this service was most recently updated [datetime]
window_end - The end of the due window for this service [datetime]
window_start - The beginning of the due window for this service [datetime]

Summary Info

The 'summary_info' dataset is designed as a storage tank for high-level KPIs and other metrics of value that exist at an account-wide level. At present, this table only holds the timestamp of the last time the data warehouse data was refreshed (in timezone UTC + 0). In the future, more KPIs and metrics will be added to this table as new rows with their own distinct value in the measurement_name field.

All Summary Info records are refreshed every time the data warehouse data updates.

Fields:

id - Primary key / row id of the summary_info table [number]
measurement_name - The name of the measurement [text]
measurement_value - The value of the measurement in question, stored as a string [text]

Tags

The 'tags' dataset contains mappings of ServiceTrade records to their tags. In this dataset, each row represents a mapping of one ServiceTrade record to one tag.

All Tag records are refreshed every time the data warehouse data updates.

Fields:

created - The date on which this tag was associated with this entity [datetime]
entity_id - The internal ID of the record [number]
entity_type - The type of record, one of: Location, Quote, Deficiency, Job, Invoice, Contract, Company [text]
tag - The tag [number]
updated - The date on which this tag was updated for this entity [datetime]

Tax Groups

The 'tax_groups' dataset contains information about the tax groups in your ServiceTrade account.  Each row represents a single tax group.

All Tax Groups records are refreshed every time the data warehouse data updates.

Fields:

code - The internal code associated with the tax group [number]
combined_rate - For tax groups that apply multiple tax rates (e.g. state and local taxes), this field specifies the combined/total tax rate percentage [number]
created - The date on which the tax group was created [datetime]
id - The internal ID of the tax group [number]
name - The name of the tax group [text]
state - The abbreviation of the tax group state or territory [text]
tax_rate - The tax rate percentage for the tax group [number]
updated - The date on which the tax group was most recently updated [datetime]

Technician Burden Rates

The 'tech_burden_rate' dataset contains information about the estimated hourly rates technicians charge for different tasks in your ServiceTrade account. A record in the 'tech_burden_rate' table is the combination of the user_id AND lib_item_id fields, representing a specific estimated hourly rate for a particular technician to perform a particular task.

All Technician Burden Rate records are refreshed every time the data warehouse data updates.

Fields:

cost - The estimated hourly rate amount [numeric]
created - The date on which the estimated rate was created [datetime]
id - The internal ID of the estimated hourly rate record [number]
lib_item_id - The internal ID of the task or item being estimated [number]
lib_item_name - The name of the task or item being estimated [text]
updated - The date on which the estimated rate was last updated [datetime]
user_id - The internal ID of the technician the estimate applies to [number]

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, "placeholder" techs and "helper" techs 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.

All Technician Productivity records are refreshed every time the data warehouse data updates.

Fields:

actual_revenue - The invoiced revenue allocated to this technician on this appointment (see distribution rules above) [number]
appointment_end - The date and time on which this appointment is scheduled to end [datetime]
appointment_id - The internal ID for the appointment [number]
appointment_start - The date and time on which this appointment is scheduled to start [datetime]
appointment_technician_id - A generated ID for the combination of this technician and this appointment [text]
created - The date on which this appointment was created [datetime]
customer_id - The ID of the job's customer [number]
customer - The name of the ServiceTrade company which is the customer for this job [text]
en_route_total_minutes - The sum total of the durations of all en route clock events for this technician on this appointment [number]
estimated_actual_revenue_difference - The difference between actual_revenue and estimated_revenue for this technician on this appointment [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_duration - The estimated duration for the services allocated to this technician on this appointment, in seconds (see distribution rules above) [number]
estimated_revenue - The estimated revenue allocated to this technician on this appointment (see distribution rules above) [number]
job_first_invoice - The earliest transaction date of the invoices for the job [datetime]
job_id - The internal ID for this appointment's job (matches id field in the Jobs dataset) [number]
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_number - The job number [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_type - The job type [text]
location_id - The ID of the job's location (matches id field in the Locations dataset) [number]
location_offices - A list of the names of the offices assigned to the job's location [comma-separated text]
location - The name of the job's location [text]
office_id - The ID of the office associated with the tech productivity
on_site_total_minutes - The sum total of the durations of all on site clock events for this technician on this appointment [number]
released - A flag indicating whether this appointment has been released [boolean]
status - The status of this appointment [text]
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]
updated - The date on which this appointment was most recently updated [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.

All Tech_Service_Line records are refreshed every time the data warehouse data updates.

Fields:

office_id - The ID of the office associated with the tech service line service_line_id - The internal ID of the assigned service line [number]
service_line - The name of the assigned service line [text]
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]

Terms

The 'terms' dataset contains information about the various Terms and Conditions (aka T&C's, aka terms) policies that exist in your ServiceTrade account. Each row represents a single T&C policy.

A record in the Terms table will refresh when any change is made to the corresponding T&C policy.

Fields:

created - The date on which the terms policy was created [datetime]
id - The internal ID of the terms policy [number]
name - The name of the terms policy [text]
updated - The date on which the terms policy was most recently updated [datetime]

User Roles

The 'user_roles' dataset contains information about the user roles in your ServiceTrade account.  Each row represents a single user role.

All User Roles records are refreshed every time the data warehouse data updates.

Fields:

id - The internal ID of the user role [number]
owner_id - The user_id of the user that currently owns the user role [number]
user_id - The internal ID of the user associated with the user role [number]
created - The date on which the user role was created [datetime]
role_description - The description of the user role [text]
role_name - The name of the user role [text]
updated - The date on which the user role was most recently updated [datetime]

Users

The 'users' dataset returns a wide array of information on various ServiceTrade app users.

All User records are refreshed every time the data warehouse data updates.

Fields:

company_id - Company id that the user belongs to [number]
created - The date/time that the user record was created [datetime]
created_by - The name of the ServiceTrade user who created the User [text]
details - User details [text]
email - E-mail address of the user [text]
first_name - User's first name [text]
id - Primary key / user id of the Users table [number]
is_admin - A flag to indicate whether the user is an admin [boolean]
is_sales - A flag to indicate whether the user is a sales person [boolean]
is_tech - A flag to indicate whether the user is a technician [boolean]
last_name - User's last name [text]
location_id - location id of the default location/office of the user [number]
manager_first_name - The first name of the ServiceTrade user who is assigned as the user's manager [text]
manager_id - The user_id of the ServiceTrade user who is assigned as the user's manager [number]
manager_last_name - The last name of the ServiceTrade user who is assigned as the user's manager [text]
office - The name of the office associated with the user [text]
office_id - The ID of the office associated with the user
phone - Phone number of the user [text]
status - Current status of the user (active, inactive, etc) [text]
timezone - The user's time zone [text]
updated - The date/time that the user record was last updated [datetime]
updated_by - The name of the ServiceTrade user who updated the User [text]
username - Username of the user [text]
users_name - The full name of the user constructed by concatenating the first and last name fields [text]

Was this article helpful?

/

Comments

0 comments

Please sign in to leave a comment.