Looking to get more Agreement information out of your ConnectWise environment? Awesome! BrightGauge now has 2 datasets to help you start diving into that.
There are two datasets to help you pull in this information. Both datasets are built to only look at parent agreements (with child data included, but not listed separately). They are also only to be used to look at each agreement for a client. If a client has more than one agreement, summing or averaging the data will cause things to look off
Agreements is a dataset that is built to look at the overall life of the agreement. It pulls in data from day 1 to the current (or last) day of the agreement. It helps to give you an overall sense of how the agreement is performing and/or if you're loosing too much money on it.
- Agreement Data Monthly
The goal of this dataset is to give you an idea of how an agreement is doing each time a monthly agreement is invoiced. If you have agreements that are not setup to invoice monthly in ConnectWise, then the data in this dataset will be off.
One of the things that we get questions about are the metrics contained within the datasets. We're now listing them out to help you get a better understanding, however if you still need help, let us know and we'd be glad to explain them more. The fields in here are designed to match how ConnectWise calculates the hours when looking at your Agreement Financial Dashboard.
- Revenue - This is based on what you invoice the agreement for. Depending on the dataset, this is either for that month or for the lifetime of the agreement. Since this is pulled from the invoice, it will include the revenue associated with additions.
- Agreement Hours - This is all of the hours associated with the agreement. Depending on the dataset, this is either for that month or for the lifetime of the agreement. Time entries must be associated with the agreement (or the child agreement) to count here. Hours simply logged against the company do not count. This is also all hours, not just those marked as billable (recording all hours is important for later calculations).
- Labor Cost - This field is a sum of the hours logged against the agreement multiplied by the hourly cost set for each hour. The hourly cost field is set at the time entry level by ConnectWise and respects whatever hierarchy you have setup.
- Addition Cost - This pulls from the agreement addons. It sums up the cost of each addition active during the period (life of the agreement or the month billed). It is based on the cost of the item used to create the addon.
- Total Cost - This field adds the labor and addition costs for the period.
- Margin - This field calculates the margin on the agreement. It takes the revenue and subtracts the labor and addition costs.
- Margin Percentage - This is an expansion of the margin calculation, but then divides everything by the total revenue to get a better sense of the % of profit
- Effective Hourly Rate - This metric is to calculate what the hourly rate is for the agreement based on the revenue compared to the number of hours actually spent. Take this number and compare it to what you would normally bill at to see the profitability of your agreement.
- No Addition EHR - This metric looks to calculate the Effective Hourly Rate, but remove the cost of additions. The cost of additions can be high and including them in your revenue for this calculation can throw off the metrics.
If the datasets are not working (they show in red in your dataset page) then feel free to drop our support desk a line for more assistance. If you want to try and troubleshoot yourself, try testing the dataset. Depending on the error returned, there's a few things going on:
- Execute Error: This error shows itself as: The EXECUTE permission was denied on the object 'udf_EncrDecr' What this means is that we're using a function to help decrypt cost information for techs so that we can accurately measure margin. This requires an elevated, Execute, permission which may have not been setup initially. No worries, it's an easy add. Here's help documentation to get that working. Once you've given the user that permission, resave the ConnectWise datasource which will initiate a re-saving of all of the datasets. If you're still having an issue after re-saving the datasource (give it a few minutes), then reach out to our support desk.
- Concat Error: This error shows itself as: 'concat' is not a recognized built-in function name What this means is that a function of SQL Server we're using to combine two field names is not available in your version of SQL server. It started with SQL Server 2012, but no worries we can make an adaption on the back end. Reach out to our data team and let them know you're having a concat error here.
- Datefromparts error: This error shows itself as: 'datefromparts' is not a recognized built-in function name What this means is that a function of SQL Server we're using to create a date is not available in your version of SQL server. It started with SQL Server 2012, but no worries we can make an adaption on the back end. Reach out to our data team and let them know you're having a concat error here.
- Invalid object error: This error shows itself as: Invalid object name 'dbo.v_rpt_InvoiceAgreementAdditions'. What this means is that a part of the data we need to make these datasets work is not in your system. To get these datasets working, you'll need to upgrade your ConnectWise setup.
- Timeout error: This error can show in multiple ways, but the word timeout will appear in some form. There's many reasons for that, so just have a shout out to our data team and we'll be glad to help.