Phone Call Business Intelligence with Power BI and FoneBox

, ,

Microsoft Power BI lets you build dashboards and interactive reports from your existing data. It can connect to pretty much any data source and also comes with a bunch of built in connectors to services like Google Analytics, Facebook, Quickbooks Online etc.

We’ve been working on a range of Power BI connectors for our accommodation provider clients, providing them with updating business intelligence dashboards to monitor their Xero Accounting, NewBook booking data, Office 365 activity, MailChimp, Facebook, and now their phone calls.

Since phone numbers are unique, this data can be matched against existing customer data in Power BI, so our clients can find out which customers are calling, how often, and for how long.

This is a sample dashboard I put together this morning using the phone data we’re importing for a Queensland tourist park. The data comes from FoneDynamics, an online call tracking and analytics service based in Australia. Power BI Dashboard Phone Calls

Clicking on dashboard elements opens the interactive Power BI reports. From here, you can drill down into the data and see how the elements relate to one another. In this example, we’ve selected ‘No’ under the Repeat callers chart. This gives us some pretty detailed information about the first time callers into the tourist park. This could include how long they spent on the phone, how many first time callers went unanswered, how long it took to answer these calls etc.Phone Call Business Intelligence Dashboard

Since we’re using Power BI, we can open and interact with these reports on any device. Here’s the same dashboard running on an iPhone, where you can open the charts and set alerts on important data. For example, get notified when the missed calls for the month exceeds a certain number.FoneBox Power BI Dashboard

The tech stuff

For this solution, we had to pull the data from a collection of CSV files stored on an FTP site. Power BI will connect to CSV files out of the box, though not via FTP and it won’t join multiple CSVs into a single dataset.CSVs For Import Into Power BI

I wrote a console app last night to connect to the FTP site, pick up each CSV, collect the phone records from it and upload them into an Azure Storage Table.Extract Phone Data for Business Intelligence On PowerBI

Here’s the data in an Azure Storage Table.

Power BI Phone Data On Azure Storage

Once the data for the previous day’s phone records is uploaded into Azure Table Storage, I set up an Azure Web Job to run the process once a day.Azure Web Jobs To Power BI

Now that we have a regular, up to date source of phone data, we can connect to it using Power BI Desktop app. We can use this app to create our reports before publishing them to Power BI Online.Connect To Azure Table Storage Via PowerBI

If you’d like to learn more about setting up Business Intelligence dashboards for your data, send an email with what you have in mind and we might be able to make it happen.

/0 Comments/by

Migrating from Google Drive to OneDrive for Business and SharePoint Online

, , , ,

When switching from Google Apps/Google for Work to Office 365, you’ll usually want to migrate your Google Drive files as well as your mail.

There are a few online tools that will do this for free, or at a cost, with varying degrees of functionality. I came across this handy article that goes into more detail on these methods.

The method that stuck out to me was the new SharePoint Online Migration API from Microsoft. A free powershell driven process. Microsoft released an IT User Guide on the steps required when it was in preview. This is the document I used, and it can be downloaded here.

I used a Microsoft Azure virtual machine to do the initial download of the Google Drive Directory – about 150 GB of data. It downloaded incredibly fast on the Azure VMs connection and completed in a couple of hours. I just used the Google Drive sync tool for this, though you can also use Google’s Takeout tool if you need to convert your Google Docs/Sheets/Slides to their Microsoft Office equivalents.

Once downloaded, I installed the updated SharePoint Online Management Shell, and followed the instructions in the provided Word documents above.

The next step was to create an Azure storage account, create the folders for the migration packages (a bunch of XML manifests outlining what needs to be migrated) and start the upload of the data.

I got an error during one of the first powershell commands that read ‘New-SPOMigrationPackage : The server could not be contacted‘. Following the instructions of this blog post, I changed the initial command to this and added the -NoAdLookup switch to resolve it.

Next we run the Set-SPOMigrationPackageAzureSource cmdlet to upload the data from the Azure Server to the Azure Storage account.

Once the data and the migration package is uploaded, the migration can be kicked off via PowerShell. Now the data is being moved from Azure to OneDrive for Business/SharePoint online. You can check the status of the Migration using the Microsoft Azure Storage Explorer, or just keep an eye on the library that you’re migrating to.

Storage Explorer Migration Queue

 

The coolest thing about this method is that it avoids the upload throttling of the ‘Open with Explorer’ method, and the syncing issues of the OneDrive for Business sync client. Best of all, it preserves the date modified metadata of the original files.

File metadata is preserved

 

/0 Comments/by