9 min read

Priceline optimizes retail store operations with Microsoft Power Platform

By Sameer Bhangar

November 26, 2019

Categories
Power Apps

“The speed at which I could build out these on-screen components helped me realize the appeal of low-code platforms. Power Apps has a lot to offer even to the most experienced developers among us.” – James Matson, IT Retail Innovation Lead, Australian Pharmaceutical Industries

In this blog post we learn about James Matson’s perspective as a full stack professional developer embracing low-code development with Microsoft Power Platform. James is an IT Retail Innovation Lead at Australian Pharmaceutical Industries (API), a publicly listed company that is about to celebrate its 110th birthday as one of Australia’s leading pharmaceutical distributors and fastest growing health and beauty retailers. With presence across Australia, the API network boasts more than 480 Priceline and Priceline Pharmacy stores, 970 Soul Pattinson stores, Pharmacist Advice Club and over 50 Clear Skincare clinics. API is best known for their network of Priceline and Priceline Pharmacy stores.

James built a solution for Business Leaders (Area Managers), to gain insights on the health of their retail stores, and in doing so was convinced that “Power Apps had a lot to offer even to the most experienced developers.” The Power Platform connector infrastructure made it possible for him to easily integrate with Azure functions, Azure Table Storage, Bing Maps and existing web APIs.

BUSINESS SCENARIO

Priceline Pharmacy is a much-loved health and beauty brand in the Australian market with the retail store teams providing an exceptional customer experience. Each team aims to deliver the right range of products at the right time, be well presented, and provide a warm and knowledgeable customer service experience.

Each month, the Business Leaders visit each of the 480+ Priceline stores, and primarily use the solution that James developed to access and view several retail and customer service metrics during key promotional events. These include how the store is tracking in regard to sales and basket size, and key best sellers.

BEFORE POWER PLATFORM

Before investing in the Power Platform, the process of providing business leaders and other key retail employees with the kind of information required to make informed decisions varied across different stores. Useful data could be gathered in one of the following ways:

By running reports physically within the store
By utilizing central reports provided by Business Intelligence platforms, with a time delay
By manually gathering data from various systems and bringing it together manually and distributed via email.

Each Pharmacy Business Leader would run reports at the support office to gather key retail metrics, interpret and format that information for inclusion in their own analysis, and then visit the store to get locally generated reports. They would also perform a review of the store from a visual merchandise and staff engagement perspective and record those results in a separate system, from which they’d export the results to include in a final email report.

The result of this process was hours of gathering information, and hours of putting that data together into an email report for the business. Each report ended up having a different look and feel compared to reports from stores in other parts of the country. During periods of peak promotional activity, the only way to ascertain how particular stores were performing, was to wait at least one business day for BI reports to run overnight and produce data related to the promotional activity in each store. By the time this happened, the opportunity to make decisions that might positively affect the outcome of the promotion had already passed. The challenges with the existing process were numerous:

Manually transposing data from one place to another increases the chance of mistakes
With multiple systems providing data, the process of bringing these together was manual and time consuming
The resulting assessments were often based on old data and lacked consistency

Let’s hear directly from James Matson on his journey into low-code development with Microsoft Power Platform, after which we’ll look at some of the details of his solution.

LOW-CODE FOR THE FULL STACK DEVELOPER – by James Matson

“A few months ago, Priceline Pharmacy held an ‘Ideas Jam’ day, where we brought together different vendors (including Microsoft) to demonstrate some of the new technology that were making a mark on the retail space and generate ideas as to how we might use that technology to solve real world problems. During that Ideas Jam, I was introduced to Viren Joseph, an AI architect based at the Microsoft Technology Center in Sydney. Viren helped me to develop some really cool prototypes using the Azure bot framework and other Microsoft / Azure technologies. During the course of working together, I mentioned to Viren that our business leaders were engaged in this lengthy and manual process of evaluating store performance both out in the field and at head office, and that I thought this was an area ripe for disruption.

As a .NET developer with years of development experience under my belt, I tossed ideas back and forth with Viren as to how to build a solution that would provide a great interactive dashboard for our staff that they could use either at their desk, or out in the field. I had toyed with various full stack solutions, the kind that would see me building out a database, a presentation layer, business logic and so on. I could already see the end result forming and knew that there was a good two to three months of development work in standing up anything that could deliver on the requirements.

I explained this to Viren and wanted to get his take on what components might be best to use to get the outcome I was after. That’s when he mentioned Power Apps. Before then, I’d only ever taken a cursory glance at Power Apps. I knew it was a ‘low-code’ platform for the rapid delivery of business applications, and it was that low-code moniker that stopped me from exploring any further.
To a full stack developer such as myself, the term ‘low-code’ came with a lot of assumptions, namely any solution build on top of ‘low code’ would be:

Too simplistic to address the requirements
Difficult to extend beyond whatever is offered ‘out of the box’
Easy to learn must mean – ‘limited in functionality’

Simply put, I had always veered away from anything low-code because of all of the above reasons, but most importantly the inability to answer one simple question. “Why?” Why would I use a low-code solution when I have the experience and skills to develop something from the ground up?
Viren gently pushed me towards at least looking at Power Apps before moving on, so I did, and from that day onwards I haven’t looked back.

From only a week or two of prototyping it became apparent that any preconceived notions I had of low-code were about to be blown away. At first, I approached the utilization of Power Apps as simply a presentation layer, and even in that I wasn’t disappointed. Power Apps allowed me to very quickly stand up a visually pleasing and compelling front end, complete with dashboard style metrics tiles and values that provided the user with the ability to see ‘at a glance’ how the store in question is tracking in terms of retail metrics and store visit information. The speed at which I could build out these on-screen components helped me realise the appeal of low-code platforms, but as I began to experiment further I started to understand that Power Apps still has so much to offer a full stack developer such as myself.

If you’re not a professional developer, you can without a doubt build compelling apps. If you happen to be a pro dev however, you can leverage your experience and knowledge around application architecture to extend Power Apps far beyond its basic use cases. If you’ve build complex application integrations elsewhere, you can leverage those within Power Apps via hundreds of pre-built connectors and integrations, and – if you really need the freedom to integrate any kind of data – you can utilize generic connectors like the HTTP Custom Connector to bring into the platform information from just about any service or API you happen to have built, even if it’s not within the Microsoft ecosystem.

What’s more, is that these integrations might take even a pro developer several days or even weeks to complete, but with the Power Platform, the same functionality can be achieved with a few mouse clicks and some simple code placed in your app. Because of the seamless integration with your Office 365 environment, concepts like security and authentication are built into the platform, freeing the pro developer up to concentrate on the things that make the app unique – the business logic itself. The same can be said about multi-platform use. While a pro developer can achieve compatibility across multiple platforms, Power Apps dispenses with even the question itself, ensuring that whatever is built can operate across Mobile, Desktop, iOS and Android.

Through the journey of creating the ‘Heart In Hand’ app, it was clear to see that Power Apps had a lot to offer even the most experienced developers among us.”

POWER PLATFORM SOLUTION – HEART IN HAND APP

The genesis of this app was a conversation between James and the General Manager of Retail Operations. James asked her – “if you wanted to know three things, what would they be?” Her response was: Sales, Average Basket Size and Transactions per hour.
Through experimentation with Power Apps and Power Automate, the “Heart in Hand” app was born. From the outset, the requirements were simple but critical:

Deliver an app that allows Business Leaders to access store information both in the office and on the road.
Bring together information from multiple systems about the performance of a retail outlet into a single view.
Provide a platform that is pleasing to look at and easy to navigate.
Provide a solution which enables hourly reporting by store during key promotional events

Below is a high-level diagram that describes how the “Heart In Hand” App operates.

Microsoft Power Apps
A Power Apps canvas app is used by Business Leaders during key promotional events to visualize how a store is performing in near real-time. The app can drive friendly competition between stores as business leaders check the performance of all the stores in their region.

Every new Business Leader has access to the canvas app, which can be used as a key tool for reviewing data during key promotional events.
The users can be on the road, at head office, or in the store and obtain an up-to-the minute view of how sales are tracking in their stores and their region.
The app users can use a form within the app to provide feedback on the app or post a new idea. Others can vote on ideas. Functionality is added to the app based on the ideas with the most votes/likes.

Users can also create their own custom groups of store locations to report on a subset of stores without seeing other information. The custom groups are stored in Azure Table Storage, each one consisting of an array of store locations, which are broken up into individual stores.

HTTP Custom Connector
The Custom Connector forms the basis of the data retrieval and display for the “Heart In Hand” app. The connector is backed onto a Web API which retrieves up-to-date sales data and other retail metrics which in turn have been sourced from each individual store.

Microsoft Power Automate
Microsoft Power Automate provides the app with the ability to execute more complex activities than Power Apps provides out of the box. The activities include things like storing and retrieving data from Azure Table Storage, and verifying the user’s access to different parts of the app. This data then populates a variety of fields within the app.

Flow used to retrieve stored data from Azure Table Storage

Another flow is used to obtain data from an external auditing platform – iAuditor – to display information about store visits into the app. The same flow uses Azure Functions to manipulate the raw data into HTML which can be displayed on one or more screens.

Flow used to retrieve data from external auditing platform – iAuditor

Azure Table Storage
Azure Table Storage is used to store useful telemetry information about app usage such as when the user signed in, what screens they visited and more.

Sample telemetry data on app events stored in Azure Table Storage

A combination of Azure Table Storage, Microsoft Power Automate, and Power Apps is used for users to suggest new ideas for functionality in the app. They can submit their ideas via a Power Apps screen, and the data is stored in Azure Table Storage via Microsoft Power Automate. The Flow used can also track whether the user has ‘liked’ other peoples’ suggestions and ensure they can only like an idea once.

Ancillary services
The Heart In Hand app also uses a number of different connections from the vast library of available connectors, including Bing Maps, which is used to show the geographical location of the retail store. The address obtained from the HTTP custom connector is passed into the Bing Maps connector to obtain the location of the store.

IMPACT AND BENEFITS

The benefits from the Power Platform solution can be summarized as follows:

Near real-time data is now available in the hands of key Retail Operations employees
An easy to use user interface for navigating through critical retail KPIs
Mobility across all common devices
Provides an easy to enhance platform where users can shape the development of the app
A marked increase in the ability of Business Leaders to understand the impact of promotional activities

SOLUTION ARCHITECTURE

A Power Apps canvas app connected to web APIs via a custom connector is used to view insights on desktop and mobile devices. Several flows are used to write data to Azure Table Storage, manage user ‘likes’ on app suggestions, convert raw data into HTML, get location information from Bing Maps, and more.