Customized Reporting for Franchise Store Managers

We recently deployed a large-scale reporting project for a major client who had 1000’s of weekly, monthly and annual reports that were vital to taking data-driven decisions. A major challenge for the business was that they were creating and sharing important reports on an informal, ad hoc basis using emails. Adding to the complexity was the fact that it was a franchise business with a distributed decision-making network where hundreds of franchisees needed fast, easy and timely access to customized reports about their store.

As a result of the ad hoc system, store operators could not get the precise information they needed in a timely basis to make the right decisions for their unique situation. This necessitated an overhaul in how reports were created and distributed and importantly, a new system that gave more control to the end users.

In this blog, we show how we solved this important B2B problem. First, let’s get a first-hand feeling for what was going on by peeking into the lives of two people in the organization, an Account Manager and a Franchisee:

  • Account Manager – monitors the performance of the Franchises regularly and gives feedback to improve their performance.
  • Franchisee – the owner of the Franchise.

On a regular day, John an Account Manager walks through the market area and stops by one of the Franchises assigned to him. The conversation goes like this…….

John [AM]: Hey Sam, how are you?

Sam [F]: I am doing good, waiting for today’s business.

John: SAM, you need to increase the sales. Your sales are dipping from the past 3 months.

Sam: John, thanks for informing. Unfortunately, I don’t have any authenticated reports to refer or to check out my performance.

John: I understand that.  I will get your performance report update from the head office and will communicate the same to you.

Sam: I have a suggestion. Why don’t you share the reports in a File which we can refer to and then accordingly plan our improvisation areas?

John: Yeah, it is a good idea. I will talk about this with the business managers.

Sam: In addition to the above, it would also be more efficient if the reports are shared via e-mail. This will save you the time and effort to share it with each one of us, in-person every month.

John: Wow! That’s a great idea.

Does this situation seem familiar to you? As Sam and John are having the conversation, there are two problems which Sam pointed out:

  1. To create a performance report for the Franchise Owner.
  2. To e-mail the reports so that Sam does not have to visit every Franchise on an individual basis.

At Core Compete, we have delivered solutions for our clients using SAS Programming and VB Script programming. We developed a user-friendly excel tool which enables the business user to generate the reports by a single button click.

In reference to the above scenario, once the reports are validated, the account manager can share the reports to all the franchise owners with a single button click. Yes, a single button click and all the reports will be shared with respective franchise owners, thereby saving significant amount of manual time to compose e-mails.

Looks interesting, right! Let’s see how it is done:

Solution Design
franchise reporting solution design

Phase-1: Read the previous month data from raw datafiles and after processing using SAS append the data to the data lake

Phase-2: Read data from data lake and do data processing for reporting and then generate excel reports

Phase-3: Read excel reports using VB Script and then feed the data to PowerPoint Template objects and save the report as PowerPoint and PDF Reports.

Phase-4: E-mail reports after validating data

Process Flow

Here, we discuss what is handled in each stage of the process:

  • Data Lake
    • Manage historical data
    • Import updated data (Monthly)
    • Data cleaning
    • Quality Check
  • Reporting process
    • Apply business rules for the reports
    • Generate excel reports
  • Final Report
    • PDF and PowerPoint Reports are generated
    • Final Report
  • E-mail
    • Share PDF reports via E-mail

To conclude, we have created a solution which enables business users to generate 1000’s of reports and share them with the Franchise Owners via e-mail. The solution developed can be customized for any business line and can serve the large base of report consumers without incurring additional cost on business intelligence infrastructure. Automated e-mail process saves significant amount of time and effort for the business.

We can also implement similar solution using open-source technology such as Python and email the reports directory to the recipients.

You can also refer to the white paper which was published at SAS Global Forum 2019: Large Scale Reporting Solution using SAS® and Excel

About the Author

Balraj Pitlola, Data Science Analyst

Balraj is an analyst in the Core Compete Data Science practice. He focuses on designing, creating and implementing cloud analytics, big data and customized reporting solutions that deliver outstanding customer value.