Archive for March, 2010
Mediamark Research Inc. (MRI) owns perhaps the most comprehensive consumer panel survey. As it says on MRI’s website:
MRI has been the dominant voice in media and consumer research in the United States. No other organization – not even the Census Bureau – knows more about American consumers.
FSI initially was brought in to improve an existing project management tool for their interactive department. When FSI was hired, the tool was a simple MS Access file tracking all the Interactive division’s customer surveys. As the application became more complex, the system was upgraded to a SQL Server back end.
FSI has worked on various projects for MRI, including automating the migration from one panel software platform to another (see case study).
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.
The Earthsense website is a FSI/GORGES collaboration. The site is built on GORGES’ custom LAMP-based content management system.
The Earthsense site has a rich set of custom-built programming, including a prospecting component, where users can create a verified account and get access to reports and documents. Earthsense gets a valuable email address as a prospect.
The most advanced feature of the site is an on-the-fly PDF report generator that allows users to purchase “business indicator executive dashboard” reports. These reports are based on over 50 variables/metrics on 350 companies. The user can select a company and the report is dynamically generated.
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.
NeighborWorks America is a national not-for-profit created by Congress to provide financial support, technical assistance, and training for community-based revitalization efforts. It has been a client of FSI since 2001 as well as an employer for Doug Foster.
FSI and Doug Foster worked with the Campaign for Homeownership, working on various programs such as the housing rehab program, manufactured housing and green building. FSI provided GIS services, mapping HomeOwnership Centers and their markets.
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.
Richard Lampert has 30 years experience in housing market studies for local governments. He has worked with all communities and counties in the Hudson Valley area of New York.