Push ServiceTitan reports into Geckoboard datasets
On macOS and Linux you'll need to open a terminal and run chmod u+x path/to/file (replacing path/to/file with the actual path to your downloaded app) in order to make the app executable.
- Create a service titan app in the developer console here
- Then add the new app to your account under Settings > integrations here and grant it access to reports.
- Copy the values for later
From the developer console the application key field in the image below (ak1...) maps to the servicetitan app_id in the config.
From the integrations page under the settings section those fields map nicely to the config.
Open up a terminal (on linux/max) or a command prompt (on windows), and run your script.
./servicetitan-to-dataset config --generate
This will generate an example config and create a file called config.yml by default. Open this file and replace the servicetitan values with your specific values.
Hopefully this is obvious, but this is where your Geckoboard API key goes. You can find yours here.
Now we need to list all the reports and categories in your account. This is required so that we can store what category and report we want to push to Geckoboard.
Run the following command
./servicetitan-to-dataset reports list
If the servicetitan config values was setup correctly you should see something similar to the below, depending on the number categories you have you might see more output.
2022/10/14 16:10:27 Fetching categories...
2022/10/14 16:10:41 Fetching reports for category Accounting...
2022/10/14 16:10:41 Fetching reports for category Other...
+-----------+----------------------+----------------------+-------------------------+
| REPORT ID | CATEGORY ID | CATEGORY NAME | REPORT NAME |
+-----------+----------------------+----------------------+-------------------------+
| 1234 | accounting | Accounting | Revenue by employee |
| | | | |
+-----------+----------------------+----------------------+-------------------------+
| 2345 | performance-reports | Performance Reports | Sales by technician |
| | | | |
+-----------+----------------------+----------------------+-------------------------+
Now you have a category ID and report ID (lets take the second example above). Now we need to query the report fields and parameters
./servicetitan-to-dataset reports parameters --report 2345 --category performance-reports
That should output something like the following;
Report id: 2345
Report name: Sales by technician
Report fields:
+--------------------------+------------------------+--------+
| FIELD NAME | LABEL | TYPE |
+--------------------------+------------------------+--------+
| Technician Name | TechnicianName | String |
+--------------------------+------------------------+--------+
| Completed Jobs | CompletedJobs | Number |
+--------------------------+------------------------+--------+
| Total revenue | TotalRevenue | Number |
+--------------------------+------------------------+--------+
Report parameters:
+-----------------+------------------------------+-----------+--------+-----------+
| PARAMTER NAME | LABEL | DATA TYPE | ARRAY? | REQUIRED? |
+-----------------+------------------------------+-----------+--------+-----------+
| From | From | Date | FALSE | TRUE |
+-----------------+------------------------------+-----------+--------+-----------+
| To | To | Date | FALSE | TRUE |
+-----------------+------------------------------+-----------+--------+-----------+
| JobTypes | Job Type | String | FALSE | FALSE |
+-----------------+------------------------------+-----------+--------+-----------+
| IncludeInactive | Include Inactive Technicians | Boolean | FALSE | FALSE |
+-----------------+------------------------------+-----------+--------+-----------+
With all the information now about the report and the fields and parameters we can add an entry to the config.
When pushing data to Geckoboard at least one required field is expected. This could be a ID field or a date creation field, or a name field that is always present.
In our example the "Technician Name" will always be present so we add that to the config.
In our report example as well we have 2 required parameters (you might have none in which case you can omit the parameters section) However if you have required parameters then they must be supplied in the config.
Here we use NOW and NOW-1 a special keyword for date parameters - that are dynamic based on the current datetime.
entries:
- report:
id: 2345
category_id: performance-reports
parameters:
- name: From
value: "NOW-1"
- name: To
value: "NOW"
dataset:
required_fields:
- Technician NameBy default the default name is automatically implied from the report name and converted to ensure it is safe.
You may provide a dataset name in the config as such if you want to use the same report with different params for instance
entries:
- report:
...
dataset:
name: "My custom dataset name"
required_fields:
...Please note that in some cases the dataset name you input will be converted based on the rules of what a valid dataset name is but all letters and numbers are valid values
By default when pushing data to Geckoboard - we query just the first page of data and always replace the dataset contents with the latest report data returned from ServiceTitan.
In some very rare cases - it maybe preferred to have the dataset append data, however for this to work you must have a required and unique column or multiple columns..
For instance if you report contains a Date, Name and Number fields, the required fields would be Date and Name as these are the unique constraints. By doing this we can build up data overtime if the query only returns data for "today"
To support appending data - you need to specify the dataset type as "append"
dataset:
name: ...
type: append
required_fields:
- Date
- NameIf you're report requires a date parameter, you can hardcode a specific date such as 2022-10-19 (today) however you would need update the configuration every day.
To support a dynamic date value - you can use one of the following - the report param type must be type of Date
| Keyword | Description |
|---|---|
| NOW | Replaces with today date |
| NOW-1 | Replaces with yesterday date |
| NOW+1 | Replaces with tomorrow date (should never require this) |
| CURRENT_MONTH_DAY1 | Replaces with the 1st of the current month to allow reporting for the current month progress |
Please note that by default it uses the time from your machine to determine "TODAY", if this is being run on a server running in UTC time - you can specify the time_location at the top of the config and specify a time location local to you. Refer to the time_location section of the readme for more info
parameters:
- name: From
value: "NOW-1"
- name: To
value: "NOW" parameters:
- name: From
value: "CURRENT_MONTH_DAY1"
- name: To
value: "NOW"Once started, it can query ServiceTitan periodically and push the results to Geckoboard. Use this field to specify the time, in seconds, between refreshes.
Unfortunately due to some limitations with the new reports (beta) endpoint, ServiceTitan only allow query 1 report every 5 minutes. This means that if you have;
- 2 entries - then they will update every 10 minutes + the refresh time.
- 10 entries - then they will update every 50 minutes + the refresh time.
If you do not wish for it to run on a schedule, omit this option from your config and it will run only once after it has completed all entries.
refresh_time: 60If you wish, you can provide any of the options under servicetitan and geckoboard as environment variables - to prevent storing secrets in the config.
This is possible using the following syntax "{{YOUR_CUSTOM_ENV}}". Make sure to keep the quotes in there! For example:
geckoboard:
api_key: "{{GB_APIKEY}}"By default when using magic date keywords - it uses the current time of the machine that the binary is run on. However in some cases, the binary might be run on a server running in UTC, for cases like this your might want to change what "TODAY" means when the time locations differs from UTC greatly.
The time location should be one of the values under the TZ Database name column here
time_location: America/New_York
geckoboard:
...
servicetitan:
...
entries:
...