Databases/Automation Case Studies
Phoenix Healthcare conducts an international tracking study (now in its sixth year) for a major pharmaceutical client. The study analyzes customer satisfaction of a drug treatment. After the analysis is complete, the deliverable is a 200 slide PowerPoint file for each of 7 countries, full of graphs and tables.
The analyst has the overwhelming job of populating the 300 graphs, 70 tables and hundreds of variables embedded in the text for each PowerPoint file. Not only does this take a huge amount of time but is prone to human error.
The challenge was to find a process that was flexible enough to handle the changing environment as graphs/tables were modified at the client’s request. The automation process was complicated, since of all the Microsoft Office applications, VBA is rarely used to automate PowerPoint.
FSI built an innovative system using Excel and PowerPoint to manage the automatic population of the thousands of variables in each of the seven reports. It was the best solution for this challenging situation.
Daily Makeover was using Excel spreadsheets to track Google Analytic data automatically mailed on a daily basis. As the company tracked multiple sites, this involved dozens of report files manually inputted daily.
FSI teamed with TNG Research to automate the process by creating a VBA application within the reporting environment already created. The end result was an application that could input unlimited reports at a time, and included quality control checks to ensure data integrity.
FSI’s background in consumer research proved critical when Mediamark needed to migrate their data from one panel to another. This was a tremendously difficult task, which involved the data structure transformation of dozens of large databases.
FSI’s familiarity with the data structure, along with the ability to automate the process, allowed the project to be successfully accomplished on a very tight schedule.
Here’s a common dilemma: Managers create “rogue” reports that give them metrics they need, and aren’t easily available by the corporate reporting system. They are usually Excel reports that start small, but, over time, grow so big that they’re challenging to maintain. If managers reach out to the IT department for help, they rarely find a warm response.
FSI found just this situation when it was asked to help some managers in two divisions of JP Morgan Chase. They created several detailed reports in Excel that were updated on a weekly or monthly basis. The reports had several data sources, including corporate data (Oracle) and several external data exports as text files.
Staff had to update these reports with a dozen metrics down to the bank branch level. IT had already conducted a formal analysis and said it would be much too expensive to duplicate the reports in the enterprise reporting system.
FSI kept the reports as is, which were quite complex and elegant, and used VBA to automate the update process. The managers had pull down menus or buttons to easily update or manipulate the data.
The time for each update was reduced from a full FTE day to fifteen minutes.