Working in Digital Client Services: Use Microsoft Power BI to Treat Yo’self
Yeah, I watch Parks and Rec. In fact, the time I saved implementing both a Power BI dashboard and data alerts gave me enough bandwidth to sail through all seven seasons.
This implementation guide will be perfect for anyone working in customer-facing client services at a large company and responsible for keeping track of service deliverables, territory health, and individual account KPIs. At the end of this tutorial you will know how to use Power BI to:
a) Eliminate the need for manual export of excel sheets and create visualizations and filters for daily analysis
b) Increase efficiencies in individual performance transparency, goal setting, and prioritization
c) Automatically alert yourself when crap happens (IMO this is one of Power BI’s most useful features and is discussed towards the end of the article)
What you’ll need:
- A lot of data, and a solid use case — rule of thumb: if it ain’t broke or the marginal effort to make it better isn’t worth the pay off… don’t fix it.
- Power BI and Microsoft Office Suite... duh
What might be helpful:
- Some basic understanding of coding concepts (functions, methods, dataframes, boolean operators, conditional logic)
- Some experience in statistical analysis (If you took AP stats or a similar course you’re good don’t sweat it.)
- Some business acumen — no need to get your MBA for this tutorial (Unless they started teaching implementation at Wharton)
First, stop and think about why your current way of working isn’t working.
In my case, I have a list of client accounts I service on a daily basis. As I record client data in my company’s CRM system(customer relationship management) my organization takes that data, makes calculations from it, adds to it, and spits it out in a spreadsheet format.
That spreadsheet is updated with new information overwriting previous data. Every. Day.
We have so many variables:
that it takes me hours per week to export the spreadsheet, filter it, visualize relevant data, and pull insights into a plan for my day/week/month.
Enter Power BI Dashboards:
An easy way to hook up to a Microsoft sharepoint site containing a spreadsheet with changing data. As the data is refreshed so too is your dashboard.
Disclaimer: For an auto-updated dashboard you’ll have to make sure your sharepoint file is formatted as a table.
To make this practice standard in every export I had to talk to our IT team over in India. They use Python and a Pandas dataframe. I had to send them this documentation on using the xlsxwriter module:
Making use of the
add_table()method to format the entire sheet as a table before exporting.
(max_row, max_col) = df.shape
worksheet.add_table(0, 0, max_row, max_col - 1)Step 1: Hook em’ up partner
Log into Power BI browser, hit “get data” in the bottom left corner,
then “Get” under “Files”, and “Sharepoint — Team Sites”. Paste in your site URL and click “Connect”:
To find your site URL you can create a new Team in Microsoft Teams and have your data exported there, or get added to one that holds the data in question.
Head to the files tab of your team (in Microsoft Teams), hit the “…” to open the team in sharepoint and copy the URL up to the folder name— mine looked like this after creating a new team:
https://COMPANYXYZ.sharepoint.com/sites/Arya
Hitting “Connect” brings up a few line items, one of which was:
Clicking into this folder,
then the next,
brought me to the excel sheet in question fittingly named:
Hit connect in the top right corner.
And “Import”. This ensures that you can create an auto-refreshed dashboard. (Previously mentioned, your sheet will need to be formatted as a table)
Step 2: Create your report, your visualizations, and pin them to a new dashboard
I’ll go through a few examples here, your individual use cases will likely be different depending on the variables your team collects, but hopefully my examples will give you a launching point.
After “import” click into “My workspace” in the left hand column and under the “All” tab you should find a line item corresponding to the dataset you just imported:
Hit the three vertical dots and in the dropdown click “create report”. You should get sent to a blank report where you can then build visuals that mean something to you.
Here are a few that are valuable to me: (all data is anonymized and randomized)
1. A treemap of those accounts with the longest service downtime
2. A count of accounts over a certain threshold of downtime and those with any downtime whatsoever (You’ll see why counting key indicators is important later)
Most importantly I filter my data by clicking into a visual and adjusting the “filters on this visual” tile.
You can click and drag metrics into the “add data fields here” tiles and drop down each metric for advanced or basic filtering
3. A list of accounts still needing a service kickoff paired with a gauge illustrating my progress towards completing all my kickoffs
Note: “client has completed their kickoff” and “client is eligible for a kickoff” are tracked as a binary variables with 0 meaning failure and 1 meaning success. I filter the table above to include only those accounts that are successfully (1) eligible for a kickoff and have failed (0) to receive their service kickoff.
Here, my progress gauge will tell me how many kickoffs I’ve completed, and how many I have left. I’ve set my gauge’s maximum value to be the sum of the variable “client is eligible for a kickoff”- the variable being binary (0, or 1) means summing this variable will give me the total number of accounts eligible for a kickoff.
The tracked value then becomes the sum of the variable “client has completed their kickoff”, again binary (0 or 1), which is why you see a progress marker equal to 3 in the center of the gauge corresponding to the 3 accounts that have successfully (1) completed their kickoff.
4. A count and progress gauge for use of a key service deliverable/product feature
Using the same logic I also created a table visualizing those accounts not using a key service deliverable/product feature (called “XYZ” in this example), a progress gauge, and a KPI card.
This is created from a binary variable in my dataset called “Service deliverable XYZ in use” (0 meaning not in use, 1 meaning it is in use). I can now track the number of accounts not using service deliverable XYZ and view my progress towards getting all my accounts to use a particular service deliverable/product feature.
5. A bar chart and table showing me my call history and those who have gone the longest without speaking to me:
In my linked spreadsheet I have a variable called “Last Call Date” formatted as a date variable. I use this variable to filter for the 7 accounts that have gone the longest without speaking to me. If you click into the header of any column in a table you create you can sort that table accordingly- hence that little:
Disclaimer: If you’re having trouble (like shown) with variable formatting from exports, especially with dates, please take a look at the below:
For exporting to a sharepoint site, while It isn’t possible to format any cells that already have a default format applied, it is possible to set the default date and datetime formats via the Pandas interface:
writer = pd.ExcelWriter("pandas_datetime.xlsx",
engine='xlsxwriter',
datetime_format='mmm d yyyy hh:mm:ss',
date_format='mmmm dd yyyy')If you’ve been paying attention you should have noticed a pushpin icon above every visual we’ve created:
Clicking that will allow you to pin any visual you have to a new or existing dashboard.
Opening up your dashboard will give you a full view of all your visuals. All your visualizations are updated in real-time as your connected file changes. You can view your dashboard at any time from the “My Workspace” tab.
Step 3: Alert yo’self
Arguably one of the greatest feature of all of this is the ability to #alertyoself when things change. This is only possible with your KPI tiles and gauges- hence the reason why we created so many.
In the dashboard with your pinned visuals the top right-hand corner of any KPI tile should have an ellipsis “…” .
Clicking that should bring up options for the tile, one of which is “Manage alerts”.
In the alert window that opens on the right-hand side of the screen you can click “Add alert rule,” edit the details of the alert like the title, and change the conditions for which you will be alerted. If you check the check box at the bottom of the window Power BI will also send you an email notifying you when changes happen.
You can always head back into your dashboard and change the alert settings, turn them on or off, or change the criteria for alerting.
Step 4. Never stop never stopping
The possibilities are great with these alerts. If you want even more customization you can use Microsoft Power Automate to change the email frequency of these alerts or run workflows as soon as alerts are triggered in Power BI.
I’ve used these triggers to send customizable messages to myself or my teammates via Microsoft Teams on a weekly basis.
With Microsoft’s suite of applications, integrations with your data become seamless, customizable, and more efficient with every use case.
Overall, using this instead of an excel spreadsheet, alerting myself, and having key insights ready at a glance and automatically update saves me ~5 hours/week. This gives me enough time to watch 10 epi — I mean, exercise… yeah I exercise every day now ;)
Cheers,
Arya
P.S. If you find anything I write about useful in any way please let me know:
arya.murthi@gmail.com
