The Challenge

Consulting firms must ensure that all their clients comply alongside industry regulations. This firm was tasked with assigning each of their customers an award status in order to receive industry recognition and funding. Originally, the client prepared Excel reports based on data in an SQL database that outlined the current award status of each of their customers. Then, the customer account owner assigned to each client manually reviewed these Excel reports to verify that each of their customers were awarded the correct status. Creating an automated solution that monitored the award status of the entire organization’s new and existing clients was crucial.

“Our client serviced hundreds of customers, and each customer needed to have the correct award status so that our client would receive the proper sponsored funding,” explains BlueGranite Staff Consultant, Elayne Jones. “The company relied on the manual oversight of Excel reports, which was very time-consuming as the entire process could take several days, and was also quite error-prone, requiring the company to follow up with employees who were late or made errors.”

The Solution

The challenge was determining how to formulate a solution that eliminated both the Excel report creation and the reliance on manual reviews of the data. “It was apparent that the current system was inefficient and redundant,” recalls Elayne. “Our client was determined to find a solution that cut out manual overhead. An automated solution brought the client closer to the truth in a more efficient and reliable manner.” After initial investigatory discussions, BlueGranite decided to implement the Modern Data Platform with Azure’s Logic Apps.

Logic Apps are a solution in the Azure cloud where users create and run automated workflows to complete business tasks. “Once we introduced Logic Apps to the organization, they were thrilled at the prospect of saving time and increasing the accuracy of their data. We were amazed at how receptive they were to shift towards an automated solution. The Logic Apps freed the company from the drudgery of creating Excel reports each month and managing employees’ reviews. Further, the company became ensured that the results were always on time and accurate.”

BlueGranite built two separate Logic Apps to satisfy the organizational needs for both new and existing customers.

New Clients App

For new clients, the goal was to ensure that the client received the correct award status within their first contractual month. The Logic App:

• Set up a Monthly Recurrence: We designed this app to run once a month at a specific hour.

• Ran an SQL Query: BlueGranite queried the SQL Database that contained the original award status data to obtain a list of client names, their award status, and their assigned employee name and email address. The query was filtered to pull only the clients that were missing an award status during their first contractual month.

• Sent a Microsoft Outlook Email: For each row in the result set of the query, the app sent an email to the employee(s) assigned to the client. The email asked the employee(s) to review the client's award status and modify it, if needed.

Existing Clients App

For existing clients, the goal was to ensure that the entire organization database maintained the proper award status each month. The Logic App:

• Set up a Monthly Recurrence: We designed this app to run once a month at a specific hour.

• Created a Variable: The simple Boolean variable will be used later in the flow to determine if the Logic App should send an email or not. The Logic App was designed to send emails only to employees assigned to clients who were missing the status or if their status amount had dropped below a threshold.

• Ran an SQL Query: The query pulled a list of all the organization’s clients, their award status amount for that month, and the month over month (MoM) percentage change from the previous month. The query also pulled the assigned employee name and email for each account.

• Performed an "If" Statement: For each row in the result set of the query, the app checked whether the MoM percentage change dropped below a certain threshold (-0.2) from the previous month.

If the MoM percentage change dropped below -0.2, the Boolean variable was set to True. Otherwise, the Boolean variable was set to False.

• Sent a Microsoft Outlook Email: If the Boolean variable was set to True, the app sent an email to the employee(s) assigned to the client. The email asked the employee(s) to review the client's award status and modify it, if needed. If the Boolean variable was set to False, the app did not take any further action.

The Results

BlueGranite helped this organization achieve its goals for monitoring its data in a more efficient and comprehensive manner. This Logic Apps solution adoption allowed the organization to cut down on manual business processes, thereby saving time, increasing accuracy of analysis, and maintaining their industry recognition. The organization no longer has to create redundant Excel reports each month, send the report to all their employees, and manage the quality and timeliness of the employees’ responses.

By implementing the Modern Data Platform tools, the organization automatically received the relevant data directly from the source, on the same day each month, while “cutting down on the noise” by only alerting on relevant missing information or information that fell out of an identified threshold. The reliability of an automated solution allowed the organization to meet industry deadlines in a timely manner each month, cut out manual overhead, and continue to foster an attitude of business optimization. Now, this company can spend their time making meaningful insights from the data and strengthening their relationships within the industry.

To learn more about how BlueGranite can help streamline your business processes, contact us today.