1-800 CONTACTS, the leading contact lens provider in the world, sells to consumers across the United States through its Web site and toll-free call center. Company management was frustrated with its limited ability to directly access important data and its need to go through IT staff members in order to get timely, reproducible reports. To provide better, faster results, the company created a new data warehouse using Microsoft® SQL Server™ 2005 and SQL Server 2005 Integration Services, which is used to define business rules for data that help create consistent, clear business intelligence. The company is also using Microsoft Office SharePoint® Portal Server 2003 to create new intranet sites where users can easily find predefined reports for data analysis. The new technology is helping 1-800 CONTACTS glean better business intelligence to help guide the growing company.
1-800 CONTACTS is the largest retailer of contact lenses on the planet. The company maintains an inventory of more than 20 million contact lenses, and on an average day sells about 150,000 replacement contact lenses—more sales than 2,500 typical retail optical stores combined. With its large inventory, the company promotes itself as being able to provide customers with more selection at better prices than retail optical outlets.
||With the SQL Server–based technology, we can now see results very fast, and sometimes in real
time. It helps us be more adaptive to changes in the industry.
The Draper, Utah–based company employs 600 people, including more than 300 customer service representatives who are available seven days a week to help customers with orders through its toll-free telephone line and 1800contacts.com Web site.
The publicly traded company reported net sales of U.S.$204.4 million for fiscal year 2004, an increase of about 13 percent over the previous year. It predicts net sales of at least U.S.$220 million for fiscal 2005.
Since the company launch in 1995, the 1-800 CONTACTS IT team, which includes about 30 professionals, has created all the applications used in the business, from order entry and order processing to inventory tracking. Legacy code is still in production, although the company’s systems are constantly updated and improved.
By 2003, however, the organization’s size and continued growth demanded a more sophisticated approach to using information from phone and Web-based orders so that business analysts and senior management could better understand buying patterns, plan promotions, and conduct other business activities.
The company deployed Microsoft® SQL Server™ 2000 several years ago and benefited from features such as SQL Server 2000 Reporting Services for analyzing data from operational systems. The information, however, frequently contained inconsistent or redundant data stemming from the disparate systems. Moreover, producing reports entailed enough complexity to require the assistance of at least two or three IT staff members, making it difficult and time-consuming for end users, including business analysts and executives, to quickly access data in order to make informed business decisions.
“We had predefined reports, but there was very little self-service in the organization in terms of getting reports,” says Jim Hill, Data Warehouse Manager for 1-800 CONTACTS. “No one outside of the IT department had direct access to the data. That put a strain on our IT department because IT employees had to be available to run reports for internal users. It was very difficult to develop any ad hoc analysis in a timely fashion and was an inefficient use of company resources.”
1-800 CONTACTS decided to build a new data warehouse to consolidate operational information in one location, and to provide a rich set of business intelligence tools that users could easily access on a corporate intranet site. To build the new data warehouse, 1-800 CONTACTS became an early adopter of the beta version of Microsoft SQL Server 2005. In particular, the company used SQL Server 2005 Integration Services (SSIS), a new feature of SQL Server 2005 that enables organizations to create enterprise-level extraction, transformation, and loading (ETL) applications. These applications are used to integrate and analyze data from heterogeneous information sources such as the operational systems used by 1-800 CONTACTS.
1-800 CONTACTS uses SQL Server 2005 Analysis Services to understand revenue information through an Orders Activity cube. Most of the company’s occasional users can now access the SQL Server Analysis Services cube instead of the relational database.
In addition, the company is using SQL Server 2005 Reporting Services, primarily through a broad set of predefined reports that are accessible to users through an internal Web site. These reports give senior managers a fast, high-level intra-day view of the company’s performance. Senior managers can view charts comparing the current day’s, week’s or quarter’s revenue numbers with the same period from the previous year. Other reports show progress towards daily, weekly, and quarterly revenue goals
1-800 CONTACTS chose the Microsoft Windows Server™ 2003 Enterprise Edition operating system to run this business-critical application on SQL Server 2005 because a reliable and scalable environment was vital to success. Both SQL Server and the Windows Server operating system are part of Microsoft Windows Server System™ integrated server software and offer an extensible platform that 1-800 CONTACTS can leverage for future needs.
||With SQL Server Integration Services… our business users now have reports with consistent sets of data…and that clarity helps produce clearer answers to user queries.
Data Warehouse Manager
The company worked with Microsoft and the Kimball Group, a consultancy that specializes in dimensional data warehouse design. Together, they analyzed business procedures and the company’s systems to maximize the efficiency and usability of the new data warehouse. The data warehouse, built in a star schema, is expected to grow to about 300 gigabytes in size.
The SQL Server 2005 technology was initially deployed in late 2004, and a production version of the data warehouse went live in April 2005. The design and implementation process included standardization of terms for different business activities and the integration of well-defined business logic into ETL processes based on SSIS.
The company also deployed Microsoft Office SharePoint® Portal Server 2003 to create new intranet sites that provide access to the new business intelligence tools. One scorecard was developed specifically for call center employees to provide them with real-time information on their key performance metrics, which include their productivity, closing ratio, average order size, and a quality score that reflects customer service.
The sales scorecards are dynamically refreshed every 15 minutes, providing the most current information possible to call center agents. The employees receive bonuses tied directly to these scores and are now able to monitor their performance throughout the day.
1-800 CONTACTS is also taking advantage of out-of-the box Web Components of SQL Server 2005, which are a set of controls that help users perform additional data analysis within the Microsoft Office System applications such as the Microsoft Office Excel 2003 spreadsheet software. For example, users can launch Office Web Components by clicking on a link within one of the predefined scorecards, which, in turn, accesses an OLAP [online analytical processing] cube.
A user can select the data components he wants, and export them quickly to Excel for charting or graphing information and creating pivot tables. It is an additional tool that gives end users quick access to data that was hard to get in the past and new ways for analyzing that information to understand the business.
Being able to rapidly access information on key performance indicators, helps company employees continuously improve their performance. The ETL process using SQL Server Integration Services helped standardize and unify information from the company’s different operational systems to eliminate redundant and potentially conflicting data.
SQL Server 2005 also provides a powerful environment for new tools that can be used in the future to perform highly detailed analysis on different aspects of the company’s business – helping the company maintain its leadership in the marketplace.
Call Center Incentives Increase Revenues
The new SQL Server 2005 data warehouse gives call center managers and agents greater visibility into intra-day performance. As a result, the call center has become more productive and is achieving higher revenues.
Since implementing the new call center scorecard, 1-800 CONTACTS has experienced an improvement of $40,000-$50,000 in sales per month. Hill explains, “Once the agents had visibility and could see that their performance tied into their incentive payout, their overall quality has improved across the board. Every metric being displayed has improved.”
Faster Access to Business Information
Hill says the new technologies in SQL Server 2005 have helped the company reach its goal of providing employees with speedier access to important data without having to rely on the IT department for reports. "With the SQL Server–based technology, we can now see results very fast, and sometimes in real time,” notes Hill. “It helps us be more adaptive to changes in the industry.”
“This is one of our biggest objectives—moving responsibility for accessing data back to the business users so that IT is no longer a bottleneck for information,” Hill says. He says that a good example occurred when several business analysts wanted to review the average number of contact lens boxes that were shipped per order. In the past, this kind of information would have taken several days or more to get from the IT department.
“With the new data warehouse and business intelligence tools, they pulled their own data and were able to go back every week for two-and-a-half years, then provide the results directly to the finance team,” Hill says. “It took less than an hour.” Because users can pull their own data, the IT department has seen the number of requests for reports decrease drastically, saving nearly 1,000 hours of staff time per year.
Deeper Analysis of Data
Creating a new data warehouse includes establishing business rules to govern data that originates in separate operational systems. The Integration Services feature of SQL Server 2005 enables this process with powerful tools that helped 1-800 CONTACTS define business logic rules and then align them with the business analysis tasks to be performed by end users.
“With SQL Server Integration Services… our business users now have reports with consistent sets of data…and that clarity helps produce clearer answers to user queries.” says Hill. The dashboard metrics exactly match the data that analysts pull from the database. Previously, analysts may have used different systems to retrieve data which led to conflicting reports because the business rules for each system were unique. As a result, staff time was spent investigating and reconciling data between the various systems. With the new SQL Server 2005 data warehouse, these issues have disappeared. This time savings reduces rework and allows company analysts to dive deeper into the data.
Powerful Environment for New Business Intelligence Tools
SQL Server 2005 provides 1-800 CONTACTS with a powerful environment for designing new business intelligence tools in the future.
In the future, 1-800 CONTACTS hopes to use their new data analysis capabilities to improve customer segmentation by monitoring ordering and usage patterns. This information will be used to create more targeted marketing efforts, which will reduce the amount of direct mail and e-mail the company produces. In addition, the company is planning to replicate the call center dashboard for its prescription verification agents.
Microsoft Windows Server System
Microsoft Windows Server System is a line of integrated and manageable server software designed to reduce the complexity and cost of IT. Windows Server System enables you to spend less time and budget on managing your systems so that you can focus your resources on other priorities for you and your business.
For more information about Windows Server System, go to:
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-
© 2005 Microsoft Corporation. All rights reserved. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, Excel, SharePoint, the Windows logo, Windows Server, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other products are the trademarks of their respective owners. Document published October 2005