Automated Billing Process – Office 365 Licenses

Project Outline:

Think IT is a Cloud Service Provider (CSP) which means they resell cloud products like Office 365 licenses to their clients.  They paid for an application “developed in the USA” which was already expensive and had major limitations in its design.  The cost of the application also fluctuated with the exchange rates so the costs became unpredictable.  They approached us to design a better solution!

Major requirement of the project was Automating the billing of the licenses.  One problem with the current application is that it didn’t handle price fluctuations.  It also required login credentials for the API’s to access the clients license counts, which often were reset or expired which broke the integration and billing.  It also needed to be a set and forget billing system.

The Solution:

They already had Office 365, so ideal scenario was to develop a solution using the Office 365 toolset – namely Excel Online and Microsoft Flow for Automation.

Excel is great as their accounts team know it and they can customise and manipulate the data (if they needed) before syncing it to their billing system (ConnectWise).  They could access a csv license report in Excel from their supplier and it listed all license charges throughout the last month – it wasn’t live data like in the API’s but we decided that being Live wasn’t required as Think IT only invoice monthly so daily changes weren’t reflected in the billing.  The Excel File had a formula to handle the price increases and even mapped rules on items that don’t need to be synced or billed – which ended up a much tidier solution as the accounts team can intercept and manipulate oddities before they get billed.

We then used Microsoft Flow to extract all the billable items from the Excel file and push them into ConnectWise (via Ape-PI’s prebuilt Integration) at a click of a button.  We decided to make it a manual click rather than a scheduled sync so that the accounts team can control it.  Prior to the US application the billing process would take hours – the US application resolved that but cost US$600 per month and was increasing.  We reduced the cost and provided a much more flexible and controllable solution.

One limitation to this method is that with the original US application it would sync the clients usernames to the invoice so clients could see who they had licenses assigned and cancel any that weren’t required.  Our csv export didn’t have this data available so we moved into Phase 2.

Phase 2 – Develop a dashboard on their website to allow clients to view their Office 365 license count and users.  Read more about that phase here.

Toolset

Excel Online  via SharePoint – for storing and manipulating the billing data.

Microsoft Flow – for Automation

Ape-PI for the integration into ConnectWise

Related Posts

  • 36
    Project Outline: Phase 1 of the project was Automating the billing of the licenses, read more here. Phase 2 - Our client Think I.T. who are an Auckland and Waikato based IT services company required a dashboard on their website to allow clients to view their Office 365 license count and…
    Tags: office, data, licenses, microsoft, api's, toolset, solution, license, online, project