Case Studies
The Green Confidence Index (GCI) is a collaboration between Earthsense, Survey Sampling and GreenBiz. The GCI is an index score that tracks the changes in consumer attitudes and behaviors on the environment, and their perceptions on corporations and other institutions.
FSI automated the monthly process of exporting the survey response data from SPSS to SQL Server, and then generating more than a dozen Excel reports, each with over 50 data points and metrics.
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.
Doug Foster co-wrote a study (Affordability and the Funding Gap) for NeighborWorks America studying the changes in affordability over 127 market areas, and its impact on low and moderate income (LMI) families.
The study developed its own metrics to evaluate the housing boom’s impact specifically on LMI families. Doug applied his GIS and programming skills to automate a process of running the algorithm on the 127 market areas, ranking and mapping them.
As part of the Earthsense team, FSI is responsible for maintaining the data for a large, syndicated survey. Eco-Insights© is the largest U.S. syndicated database of consumer attitudes and behaviors toward the environment and sustainability.
Scripts are built in SPSS to export into a SQL Server 2008 database. All aspects of database maintenance, including QA/QC, appending and exporting to various platforms are handled by a combination of stored procedures and an Excel VBA application, custom built for the project.
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 teamed up with TNG Research to develop a customized posting system for The Tennis Channel, combining commercial traffic schedules with local market ratings data and advertiser guarantees to create a “report card” or each client’s audience delivery.
To develop the report card, FSI used a combination of SQL Server stored procedures and Excel VBA. The proprietary algorithm was automated to run against each advertiser in each of 57 markets based on each ratings data.
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.
FSI teamed with its partner Catenate Consulting as Experian’s “spatial analytical” team. During that relationship FSI/Catenate developed a methodology to use customer transaction files to create targeted mailing lists for Experian.
FSI automated the process to run the “hot spot” routine, which analyzed a million-plus record customer list and mapped it for over 250 stores.
The result was to increase return rates for direct mail campaigns for customers such as Williams Sonoma, Borders Books and Pottery Barn.
For years, the Village of Nyack had a six page website with minutes and agendas that were years old and not updated. Staff didn’t have the expertise to maintain the site themselves, but there was a need at a minimum to post documents.
The Village, like any local government, has demanding content management requirements, so FSI chose Drupal 6 to manage the groups, staff and documents.
The site is still under development, currently using a modified template while a custom design is underway, but in only a month the site has over a hundred documents that are being updated and maintained by staff.
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.