Databricks Report List
While the full data lakehouse functionality is being expanded, we have built some reports to give Goettl decision makers insights from the data work we have completed so far.
Using Databricks: This is not the fanciest system. In some cases, these reports are still in the format the data team uses to create them. If you click on a link and see something that looks like this:
you can ignore the top section entirely. That is the SQL code used to pull the data. In fact, you'd like, you can expand the bottom part to cover it entirely.
When you make changes to the filters/parameters, you need to click the APPLY CHANGES button. (Seen in the screenshot below on the far right.)
After running the report, you can export the result by clicking the arrow next to Raw Results
Prebuilt Reports
The name of the report is a link. Click it to open the report. You will be asked to sign in to Databricks with your Microsoft Account.
If you attempt to login and do not have access, please create a ticket at helpme.goettl.com using the top option, choose "PowerBI/report" from the "Application" drop down, and enter "Access Request" into the Short Description field.
| Report | Description |
| Average Hours Per Job | Calculates the average hours (clock hours not man hours) it takes to complete a job by job type. Also includes the current hours allocated to that job type in ServiceTitan. Designed to guide branches on resetting allocated hours. |
| Average Hours Per Job by tech | Same as Average Hours Per Job but includes the ability to separate by technician. |
| AverageTurnsPerDay | Calculated the average turns per tech per day. First calculates the turns per tech per day only for techs working on that day. Then creates an average for that day. |
| Call lookup | Looks up phone numbers and returns any call, customer, or job activity associated with that phone number. |
| Cancellations by branch | Cancel rates by branch |
| Cancellations by job type | Cancels by job type |
| Cancellations by Day of week | Cancels separated by day of the week |
| CancelledJobCount | List of filterable cancelled jobs |
| CancelledJobsByCategory | Cancelled job counts by cancel category (preventable, not preventalble, false, etc) |
| Cancels - Who canceled | Cancelled jobs by the role (CXR, Branch Dispatch, Branch, Other) of the person who cancelled. |
| Cancels by Appointment hour | Cancelled jobs by the hour of the appointment |
| Cancels by Campaign | Cancelled jobs sortable by the originiating campaign |
| Cancels by created cancelled dif | Cancels by the number of days between the appointment creation and the scheduled date of the appointment |
| Cancels by hour | Cancels by hour of the day (when they are canceled) |
| Cancels by type | Cancels by Cancel Type (No approval, Did not confirm, etc) |
| Cash Payments | List of jobs with a payment type of cash. Includes job date and collected date. |
| Returns count of type, count, and revenue for jobs by the group or type of job of the parent job. For example, will show the job count and total revenue of jobs created from Maintenance jobs. | |
| Company Requested CancelledJobCount | Count of cancellation requests where the type is Company Requested Reschedule |
| CompletedJobCount | Count of completed jobs |
| Compliance SoldHoursException | List of jobs where the sold hours on an invoice task does not match the item hours on the invoice task. |
Returns a list of appointment windows where a technician has more than one appointment scheduled to start at the exact same time. Includes the job class of the jobs and the job numbers. Will not include appointments that are overlapping but do not have the same start time. | |
| Discount Reporting | Details of discounts given on jobs with a count of the number of discounts on an invoice and a total discount %. |
| DispatchProUsage | (Beta) Attempts to determine then number of jobs scheduled by DispatchPro v. manually dispatched |
| DriveTimeRatio | Ratio of drive time to work time. Data is from ServiceTitan. An effort is made to exclude "bad" tech check-ins. |
| Estimate analysis | Per job listing of estimate counts. |
| Estimate count | Simple one number average of estimate counts per job |
| Exception | Appointment Workflow | Lists appointments where the check-in occurs an unreasonable amount of time before the appoinment. This report estimates possible deviations from the appointment workflow. |
| Exec LaborUt with Driving breakout. | Same as LaborUt but with addition vews on one page--designed for ease of use by leaders managing more than one branch |
| ExecView DispatchProUsage | Same as View DispatchProUsage with more views added on one page--designed for ease of use by leaders managing more than one branch |
| FieldOps | Callbacks | Number of callbacks by branch, trade, job type, technician et al. OJ in the field name indicates the field reflects the Original Job. CB in the field name indicates the field is reflective of the callback job |
| FieldOps | Callbacks% | Same as FieldOps | Callbacks but as a percentage of total jobs. |
| First Customer Job | Finds the first job for all customers and creates a total for all customers with the same first job type. Currently limited to customers what were created after 1/1/2022. |
| HoursOnZero$Invoices | Calculates the number of man hours spent on invoices with a $0 invoice total |
| Job Count by Day | Historical count of jobs per day. Includes average, high, and low temperatures for that day along with precipitation. Can filter by Callback. |
| Returns the count of jobs by day of week and create time rounded to the nearest half hour. | |
| LaborUt with Driving breakout. | Assumes 7 hours per technician per day is available to work and calculates the actual amount of time they are working compared to not working both inclusive and exclusive of drive time |
| LateAnyAppointment | Total number of appointments where we missed the window |
| LateAnyAppointment - % | Total number of appointments where we missed the window/total appointments during the selected period |
| Lifetime billing by job type | Lifetime customer value by job type of first job. |
| Lookup Job Number | Enter a job number and get an Invoice number or jobID. Enter a job ID get a job number. Note, if entering a job ID, The job number field must have a % in it and vice versa. |
| Membership | Count of active memberships by month |
| NonRevenueJobs | List of jobs in the non-revenue job category |
| PastBacklog | List of jobs that appear to be completed but are not marked as such |
| Post Dispatch Cancels | Cancels that occurred after a technician was dispatched |
| RanJobs | List of jobs ran |
| Refund Ratio | Ratio of refunds to jobs ran |
| Revenue to Discount Ratio | Ratio of revenue to discounts |
| Same Day Reschedule Count | Count of cancels where the date canceled and the date of the appoingment are equal. |
| SameDayCancels | List of cancels where the date canceled and the date of the appoingment are equal. |
| ServiceTitanUserEmailList | List of all Goettl ServiceTitan users |
| SoldHoursException | For dashboard. List of jobs where the sold hours on an invoice task does not match the item hours on the invoice task. |
| Turns and hours | Turns per day and productive hours per day per technician. |
| Turns and hours - Moving Average | Calculates a moving average of turns per day and productive hours per day per technician. |
| Utility | Branches | List of all branches |
| Utility | Cancel Type | List of all cancel types |
| Utility | Division | List of all divisions |
| Utility | Job Class | List of all job types |
| Utility | Job Group | List of all job groups |
| Utility | Subdivision | List of all subdivisions |
| Utility | Technicians | List of all technicians |
| Vendors | List of all vendors |
Related Articles
PowerBI Reports
Some of the reports and dashboards below require additional permissions to access. Access can be requested at helpme.goettl.com using the top option to create an app support ticket. Flash Report Branch and Technician Performance Daily Titan Reporting ...TechTurns
The tech turns report is an improvement on the ServiceTitan metric. The ServiceTitan metric assumes every tech works every day which creates incorrect data when calculating turns for more than one day. The Goettl data lakehouse report first ...Backlog - Past and Future
The backlog as a whole represents projected revenue based on scheduled jobs. In the Flash Report Backlog Tab, the backlog is broken down into two sections: future and past. Future Backlog The future backlog is just jobs that have been scheduled but ...PastBacklog
The past backlog is a list of jobs that have appointments in the past, have no appointments scheduled for the future, and are in a Scheduled, Hold, or In Progress status. It is essentially the detail for everything that is counted in the "Past ...SoldHoursException
This exception report contains invoice items where the Sold Hours on the invoice do not match the Pricebook Item hours. Preventing invoice items from appearing on this exception report is as simple as not changing hours on an invoice. Because Hours ...