International Theme Park Chain-Marketing Campaign
StatSlice Helps an International Theme Park Chain Improve Marketing Campaign Analysis
The Background – A Strong Need for Consolidated Marketing Data Warehouse as a Path to Better Analysis and Marketing Campaigns
This large national theme park chain retained StatSlice to implement an enterprise-wide data warehouse. After initial success following the delivery of a Business Intelligence (BI) roadmap to the office of the CIO, the company wanted to improve their analysis of customer trends as they related to specific marketing campaigns. The existing reporting structure used by the marketing department had been outsourced off-site to another vendor which made it difficult for the development team to keep up with new enhancements.
The company wanted to consolidate the marketing data with the rest of the data warehouse, reflecting an improved data model for better analysis, lower development costs, and tighter integration with new business processes.
The company’s marketing director managed the initiative and led the coordination efforts with multiple departments that would benefit from the data provided by this project. With several million customers frequenting many theme parks across multiple states and countries, every type of ticket sold was stored and maintained in its own application, thus its own database. These included:
- Season Passes
- Season Pass Vouchers
- Daily Tickets
- Complimentary Tickets
- Consignment Tickets
- E-commerce Sales
Unable to analyze the various ticket sales over time to determine daily, weekly, monthly and annual key metrics performance, the customer wanted a data model that would make this analysis very simple. The data warehouse would need to integrate data from all the parks and 6 different applications into the same database schema. Several complex business logic steps would be required to assist in this type of data analysis. The main challenges and requirements that drove this project included:
- Limited access to the data in the vendor-led outsourced system
- Ongoing costs for the outsourced system were too high
- The outsourced system was not integrated with the production environment and other available business data sources
- Client desire to migrate to a standard Microsoft platform
- Desired data included customer data from multiple source systems
- Cleansing and standardization of all customer names and addresses with as much consolidation as possible
- Centralized need to aggregate all email campaign lists
The Director of Marketing managed the initiative and led the interfacing efforts with a number of other departments that would benefit from the data provided by this project.
The StatSlice Approach
In order to deliver the solution, StatSlice applied an agile development methodology. This approach put the team onsite to execute a piece-by-piece implementation. The process was married with the BI roadmap and the project was divided into two-week duration work sprints. The project involved several complex design components one of which was the data quality consolidation process. This process involved four steps:
- Consolidate—combine the information from all sources
- Profile—filter out any records that could not be used or did not belong in the analysis based on business requirements
- Cleanse—contact data cleansing was to go beyond addresses and extend to email, phone numbers, and names
- Identify—match the records against themselves to determine which ones are new and which ones should be used to update existing data and make sure any records being updated are done with the most complete and latest data from each batch
Data processing was performed in SSIS while a custom SSIS component was used to perform table merges. StatSlice created C# scripts for custom business logic and used the Microsoft BI Stack for everything else (SSMS, SSIS, SSAS, SSRS).
The Results – Lowered Costs, Consolidated Marketing Data for Analysis, and Better Email Campaigns
The final product improved data quality, query performance, and gave the users access to the data they needed. In addition, business units that were not part of this initiative could eventually connect to the data structures and integrate with other subject areas. Other results included:
- Better accessibility – full access to their data through a relational database or an OLAP cube –can now publish data more easily via dashboards, Excel pivot tables, or SSRS reports
- Ease of analysis – ability to breakdown all of ticket sales in a single query to simplify the creation of reports and dashboards – and an automated Excel report that goes out every morning
- Lower development costs – with an in-house database under the control of their IT department, new data sources could be integrated more easily, smaller changes could be done almost immediately, and outside vendor contracts were terminated to save substantial annual maintenance fees
- Consolidated marketing data – data was combined from six different sources through a custom job scheduler to efficiently bring in and merge the data into the correct order
This organization wanted to improve their analysis of customer trends and buying habits, especially as they related to specific marketing campaigns. The current environment involve multiple applications with multiple databases and an outsourced data warehouse. Proper data consolidation was needed to get the desired outcomes.
Using an agile development methodology, StatSlice quickly put together an action plan built on a corporate BI roadmap. Using Microsoft as their BI platform, they were able to build, consolidate and identify all the needed data and built systems to provide answers.
A very happy client—who could now have better access to key management information. Analysis was easier, faster, and with improved performance. This data is now accessible by other departments who also needed the same information.
Theme Park and Entertainment
© 2013 StatSlice Systems. All rights reserved. This case study is for informational purposes only. StatSlice makes no warranties, express or implied, in this document.
To download the formatted PDF of this case study, click below:
You can subscribe to our RSS feed.