Zendesk and Xero Harmonized In PowerBI: The Model
When the BI Company needs BI..
Years ago, we decided to change our entire business model. We realized that we had to increase the speed of delivery and the quality of the service offered to our partners all through a more intimate delivery approach..
Behold our saving grace - The Subscription Service Model. The phrase has buzzed in my head for the last 5 years. Every day of my working life since our business changed has been about ‘frequent value delivery’. A focus on our partners’ needs rather than chasing sales, partnering with teams rather than contracting services. You get the idea.
However, it’s not all peachy. For example:
How do I know that my consultants are contributing equally towards our minimum float amount?
How do I know I’m using my teams efficiently?
Which contracts are experiencing churn?
What contracts are taking too many resources?
How do I know that my Actual Billings match work completed?
I can’t personally attend to all those questions and maintain my own set of projects. At this point in our business life we knew what made a successful deployment, we knew what the signs of uncooperative consultants were, but on the first of the month I still couldn’t tell my team at the opening scrum what to look out for.
Our instinct was to lean on the greats: Base camp, Pipedrive, Salesforce, Microsoft Dynamics; and, if we’re honest with ourselves, committing to one of those may have answered all our questions and soothed our worries. Perhaps we would have seen the red flags we missed. Maybe the contracts we lost would have stuck around. We may never know..
The fact still remained, we needed something- something to look at in our management scrums. We’re an analytics company dammit. We were already recording all this data in our ticketing system and Financial system, so the next obvious answer was to leverage those to answer our questions. Of course- getting them to talk to PowerBI was another problem..
Why Xero and ZenDesk?
It’s all predefined:
We didn’t need extra software to record all our data points because it was already recorded in our existing software - Xero & Zendesk.
It’s quick:
We can get refresh times of sub 5 minutes. It’s quick, easy ,and once it’s all defined- flows so, so well.
It’s modern:
I haven’t seen any indication of Xero or ZenDesk slowing down any time soon. Their software will be around for a while and the features available are more than sufficient for our needs.
Phase 0:
So we know we need to get data out of ZD and Xero. Why can’t we pipe it straight into PowerBI?
Well. There is not yet a connector from PowerBI to Xero- so we need a way around this. Thus, Phase 0 exposes itself.
Getting Started:
To get data out of our of Xero, we decided to use an Azure Data Factory. You can do this in in the Azure Portal. You’ll need a SQL Server to store the data as well. For our implementation we decided for a fully Azure implementation.
Designing the Xero -> Database movement:
Visit https://adf.azure.com to design the data flow.
My Pipeline consists of three data copies from a ‘Dataset’ to a ‘Datasink’. When you create a new data copy you’ll be walked through the configuration properties. ( Img 1 )
My Datasets use Xero (Preview) connectors. ( Img 2 )
You’ll need to configure them with your Xero Developer account information. ( Img 3 )
At that point you can choose the table you want to gather data from and finally import the schema on the next tab. ( Img 4 & 5 )
Back in the Data copy we can now explicitaly request data from the Minimal.Recurring Invoices table in the Data Source Tab. ( Img 6 )
You’ll follow a similar procedure with the Datasink: Choose a new Datasink connector- define the outstanding information ( connection string, schema, table design, etc ) and finally record any pre-execution tasks. Personally I chose to drop and load a new dataset. ( Img 7 & 8 )
Remember to define a regular time period for refreshes under ‘Triggers’. ( Img 9 )
Configuring ZenDesk and connecting via PowerBI:
The additional information I needed was how much is a contract worth, is it recurring and who is assigned to it. The contract value and recurring flag was a configured at the Organizational level. Then we distributed the contract value across all the consultants that have tickets relating to that org.
To Connect to ZD from PowerBI, we had to use the ‘in preview’ ZenDesk connector. When it asks you for the Zendesk URL, be sure to input it with the following format: https://CompanyInstance.zendesk.com/hc/en-us ( Img 3 )
Sewing it all together:
Finally we can import the Azure SQL tables and create relationships on the correct tables.
Of course this depends on the names in Xero being the same as in ZenDesk, but in my opinion, they should be anyway. Why would we have different names in our billing system vs our ticketing system.
Now I can calculate contribution margin per consultant, and as a company.
Now I know which of my contractors is doing the most work.
Now I can see the billings of a contract vs the number of tickets.
Now I have analytics.
Our Latest Blogs
About FreshBI
Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.