Data Warehouse Applications by Industry

The following information is for the exclusive use of Sid Adelman & Associates and is not to be copied or shared without the expressed and written permission of Sid Adelman & Associates.

Many of the points expressed here are not truly applications but ways in which the DW (including data mining) is used by these industries.

• Consumer Goods
• Distribution
• Finance and Banking
• Finance – General
• Government and Education

– Federal Government
– State Government
– University

• Health Care
• Hospitality
• Insurance

– General
– Health Insurance
– Workers Compensation

• Manufacturing and Distribution

– General
– Appliance
– Automobile
– Clothing
– Computer
– Food
– Pharmaceutical
– Steel

• Marketing
• Multi-Industry (conglomerates)
• Cross Industry
• Retailers
• Services
• Sports
• Telephone
• Transportation
• Utilities

Consumer Goods

Consumer Goods 1

• Forecasting
• Inventory replenishment
• Effects of marketing campaigns, advertising, coupons and store displays

Consumer Goods 2

• Market research
• Sales and marketing analysis

Consumer Goods 3

• Sharing information with distribution sites, business partners and product managers

Packaged Goods

• Identify required product features

Distribution

• Supply chain analyses
• Understand pipeline issues

Finance/Banking

Bank 1 –

• Evaluating business concentration and risk exposure leading to modified credit policies and loan loss reserves
• Consumer asset data
• Spot market trends
• Government regulation reporting
• Marketing
• Mergers
  – Geographic overlap and saturation
  – Information for government regulation and approval

Bank 2 –

• To support management planning, marketing and financial decision making
• Ability to track and cut costs
• Manage resources more effectively
• Provide feedback to bankers regarding customer relationships and profitability

Bank 3 –

• Information on spending patterns on a segment of issuing members card base (co-brand)
• – Target marketing
  – Promotion performance
• Members use it to analyze performance by product, geography, interchange rates, volume by merchant, location, promotions, operational performance
• Cardholder spending by state and merchant classification to develop direct mail promotion with key merchants.
• Ability to select better marketing partners, build innovative and successful new product and build brand loyalty
• Co-branding – determining where their cards (Sierra Club) are being used and develop target marketing plans
• Combine with proprietary data to determine purchase patterns to develop marketing programs
• Agent alerts
• – Early warnings for changing spending patterns
  – Opportunity for special offer based on abnormal cardholder activity
  – When response rate to promotions hits target
• Can test promotional opportunities
• Their member banks or co-brands can add data from the DW to their own proprietary databases

Bank 4 –

• Mortgage Loan portfolio
• New loans in process (pipeline)
• Secondary marketing

Credit Cards 1

• Identify new customers
• Manage and control collections
• Develop new products
• Determine optimal marketing efforts for new products
• Manager customer service performance
• Identify potential risk of default

Brokerage, Commodity Trading

• Identify target investment services, products and promotions
• Risk management
• Quality of trader transactions

Financial Services

• Tracking investment managers

Personal Trust

• Tracking managers on performance relative to indexes
• Tracking managers on the types of stocks they purchase

Data Mining

• Product analysis
• Customer segmentation
• Customer profitability
• Target marketing campaigns
• Fraud detection (credit cards)

Finance – General

• Expense evaluation – trends
• Customer Information Systems
• Customer profitability
• Fee tolerance 

Government and Education

Federal Government 1

• Compliance research

State Government 1

• Accounting
• Payroll
• Procurement
• Human Resources

State Government 2

• Auditing and fraud investigation of social services

University 1

• Provide information to be used in a grant proposal

University 2

• Provide information on ethnicity statistics for a grant proposal
• University finances
• Human resources
• Student demographics
• Financial aid
• Room and course scheduling

University 3

• [Data would be captured on the characteristics/demographics of students to better understand retention and success among minorities.]

Government – Social Services

• Fraud detection
• Effectiveness of services
• Profiles of who is using what services

Government – Taxes

• Auditing tax records for patterns and anomalies

Government – Health Information

• Program policy effectiveness (Cost and outcomes)
• Provider care adequacy

Government – Police

• Patterns and trends of criminal activity
• Justice resource deployment
• Consolidation and integration of data from multiple agencies
• Effectiveness of programs and patterns of policing

Health Care

Health Care 1

• Financial
• Clinical
• Strategic and outcomes data
• Helps measure and track and analyze how well the hospital is providing services
• Reports on percentage of patients being fed intravenously
• Compares physicians to peer group on how long his patients occupy a bed, and the cost of surgery by physician
• Finance department gets statistics by patient type, revenue code and insurance carrier
• What percentage of hospital’s billings are from Medicare, Medicaid and each major insurance company
• Ability to be more proactive about research
• Can respond to managed care contracts more knowledgeably
• Ability to track costs and cut costs, manage resource more effectively, provide feedback to physicians regarding outcomes and the cost of realizing these outcomes.

Health Care 2

• Outcomes analysis
• Providing feedback to physicians on procedures and tests

Health Care 3

• Using data mining, detecting inappropriate tests
• Reports to doctors of testing trends and practices within their specialty
• Results of study given to doctors so they can refine their decisions for ordering tests

Health Care 4

• Evaluating service to determine if should be providing or outsourcing (ex. dialysis, organ transplantation)
• Nosocomial analysis
• Continuing education and certification of health care professionals

Health Care – General

• Pairing clinical to financial records to determine cost effectiveness of care
• Utilization review
• Contract management
• Data mining to identify data patterns that could predict future individual health problems
• Data mining to identify patients who will probably not respond well to specific procedures and operations
• Discover “best practices” to improve quality and reduce costs
• Analysis of care delivery
• Research (prescribing patterns, use of antibiotics)
• Physician performance
• Resource utilization
• Information for bids on managed care contracts
• Information to support audits and external reports
• Disease state support (cardiovascular, end-stage renal, etc.)

Pharmaceutical

• Outcomes analysis
• – Doctor access

Hospitality

Hotels, car rental, timeshares

• Cross-brand promotions – target customers with promotional offers tailored to their demographics and travel patterns
• Estimate response to promotions and products through demographic analysis
• Cross market

Insurance

Insurance 1 – Established subject areas (i.e. claims, marketing,)

• Incorporate both internal and external data (i.e. information on competitors and the insurance industry trends)
• Forecast and monitor changes in the industry thereby allowing better positioning in the marketplace
• Identify characteristics of profitable business
• Analyze information related to retention of business at renewal including patterns of customers who do not renew, determine reasons why, and resolve issues that will assist in retaining valued clients.

Insurance 2 –

• Accurate, consolidated view of customer portfolios

Insurance 3

• Analysis of profitability by customer, product, geography, and sales hierarchy
• Analysis of sales offices for profit and loss

Property and Liability Insurance

• Data mining review of claims by actuarial department

  Workers’ Compensation Insurance

• Recommend health insurance deductibles.
• Analysis of claims by the employer, cause of injury, body part injured and the percentage of employees who have suffered similar injuries.
• Fraud analysis using data mining

Health Insurance

• Provider evaluation
• – Physicians profiles
  – Cost
  – Length of hospital stay
  – Procedure evaluations
• Impact on subscriber services and cost
• Employee costs per employer
• Service usage

Health Insurer

• Fraud detection – Searching for claims where the service has not actually been provided. Looking for patterns that would suggest further inquiry into the claim.
• Abuse detection – Searching for patterns indicating that certain providers are performing unnecessary procedures, prescribing expensive medication where a less expensive drug would be as effective, performing unnecessary tests and keeping a patient in the hospital longer than necessary.

Blue Cross / Blue Shield

• Analysis of claims
• Providers analysis
• Reporting to groups, government agencies, trade associations
• Analysis of quality of care and costs
• Marketing managed-care contracts
• Actuarial
• Underwriting
• Financial analysis (actual expenses vs. planned expenses)
• Profitability of manager care arrangements
• Capitated contract performance
• Fraud analysis based on the provider’s health care specialty and the geography of the claim

Pharmaceutical Insurance

• Sales and marketing
• Provider profiling
• Government reporting
• Utilization
• Claims
• Actuarial
• Integrating pharmaceutical information with medical claims
• Cost analysis by patient demographics and geographical distribution
• Cost analysis by provider, provider specialties and treatment protocols
• Analysis by diagnosis/prescription
• Generic/brand name drug comparisons

Insurance Research

• Claims information (Zurich-American – RiskIntelligence)

Manufacturing and Distribution

Company M&D; 1

• Analyze emerging business trends
• Examine product bookings
• Determine product shipments, backlogs and cancellations
• Better manage product portfolio
• Sharper contract negotiations
• Better manufacture forecasting
• Earlier detection of warning conditions
• Ability to eliminate products from portfolio
• Allows product mangers to more quickly identify product lines that are not longer required or profitable
• Information about margins, product backlogs or historical sales data to critical decisions.
• Visibility to its customers – on an individual customer level, what products the company sells, where it sells the products and at what price point.
• Worldwide view of marketing developments
• Common global language

Company M&D; 2

• Marketing executives are better able to manage their product lines
• Better visibility into product and customer profit margins
• Information on customers, products, costs, invoices

Company M&D; 3

• Access to market demand data (orders and shipment data) by both finance and materials groups
• Legal department used DW to substantiate trademark claims in foreign markets

Company M&D; 4

• Sales analysis for product movement
• Sales decision support
• – are the company’s products not being stocked
  – market share
  – competitors market share
  – when our company increases its share of the market, it is coming from competitors or are we cannibalizing our own line

Company M&D; 5 (computer component manufacturer)

• Provide selected DW access to both customers and suppliers
• Extensive measurements of the quality of the product, quality of components supplied by their vendors
• Extensive feedback from customers on quality of the products

Company M&D; 6

• Sales history/sales trends
• Customer profitability

Company M&D; 7

• Plant capacity management
• Variances between standard and actual product costs
• Inventory turnover
• Human resources
• Core competency, skills and distribution of skills

Company M&D; 8

• Analysis of production patterns to improve inventory and pricing practices
• Demand forecasting to determine optimal inventory
• Analysis of product pricing to establish discounts and margins

Automobile Manufacturer

• Tracks assembly and warranty quality information by supplier
• Plans to use this information for product planning and design
• Supplier quality information will give the manufacturer a better position for negotiation with supplier
• Plans to provide suppliers with information on their products through the Internet. The goal is to give them enough information so they will improve the quality of their products. [The supplier could be provided with data on the cost of defective parts to the automobile manufacturer.]
• The data provides a quantitative measure of quality for both the manufacturer and the supplier

Appliance Manufacturer

• Customer service
• Suppliers’ quality
• Negotiation with suppliers

Clothing Manufacturer

• Analyze sales and product trends by location to understand customer buying patterns
• Analyze sell through, what was selling at retailers
• Analyze and understand cancellations, the reasons for the cancellations to identify steps to remedy manufacturing problems

Computer Manufacturer

• Market demand projections

Food Manufacturer

• Measure against competition
• Ability to project how a new product will do
• Ability to show grocery managers how product is selling at competitive stores, at stores within the same chain and against competitive products
• Using agents, monitors conditions that require attention including variances in prices and volumes in company’s and competitors’ products
• Analyses fixed costs, equipment utilization
• Analyses manufacturing costs and performance
• Analyses productivity
• Analyses inventory levels

Pharmaceutical Manufacturer

• Analysis of physicians (along with managed-care connections) and their prescribing patterns
• Target marketing
• Identify emerging prescribing trends

Pharmaceutical Manufacturer

• Generate reports for pre-clinical approval stage
• Measurement of toxicology parameters
• Research analysis
• Testing analysis

Steel Manufacturer

• Control (reduce) inventory
• Understand item-level detail by cost, revenue, profit, inventory, customer, location
• Analysis of production problems
• Reduce accounts receivable
• Understand profitability by customer
• Understand margins and profitability by manufacturing facility leading to decisions about where to manufacture each item
• Evaluate possibilities for renegotiating contracts with customers
• Identify opportunities for new products
• Identify opportunities for new locations
• Compare plans to actual performance
• Improve product and customer mix

Data Mining

• Quality analysis
• Profitability and problems with suppliers
• Profitability and problems with customers

Customer profitability
Product profitability analysis
Strategic partnering – Negotiating with suppliers, customers
Customer purchases to identify who should get new Material Safety Data Sheets

Marketing – General

• Comparing product lines
• Media research (Nielsen Media Research)

Retailers

Retailer 1

• Tracking an items contribution to margin for its category
• Tracking how promotions and one-time buys are doing including using trend analysis
• Buyers using it to analyze sales data
• Allowing slow selling lines and items to be dropped from the stores while giving their shelf space to more profitable ones

Retailer 2

• Better understand customers and their buying patterns
• – Drives promotions – by having access to all data, marketing staff can make more accurate and effective promotion decisions
  – Feeds targeted and mass mailings
  – Enable retailer to proactively develop a relationship with its best customers or individuals that should be buying more than they currently are.
• Marketing staff can ask “what if” marketing questions and get fast response
• Meets the objectives of increasing sales, increasing profit, increasing marketing analyst productivity and decreasing mail expense and promotion cycle time.
• Mail promotion effectiveness is easily and quickly quantified

Retailer 3

• Sales analysis
• Target marketing
• Cardholder base
• Evaluate technician training
• Evaluate maintenance tool and equipment inventory

Retailer 4

• Merchandising
• Inventory management
• Flow of goods management
• Relationship marketing
• Supplier integration

Retailer 5

• Marketing strategy
• Buying strategy
• Merchandizing strategy
• Sales tracking by item

Retailer 6

• Sales analysis
• Forecasting
• Inventory tracking
• Market basket analysis – e.g. Do products on sale generate other sales?
• Individual items contribution to profits
• Vendors (suppliers) have access to how their products are selling

Retailer 7

• Shelf space allocations
• Effectiveness of promotions, advertising
• Product analysis
  – restocking
  – profitability
  – inventory turns
  – price changes
• Category management (determining optimal product in each category and the optimal price for that product)
  Merchandising strategy – sales tracking
• – analysis by SKU by store
  – trend analysis
• Competitive analysis

Retailer 8

• Understanding complaints, claims and returns

Retailer 9

• Analyze customer contacts to determine preferences, attract and retain profitable customers
• Understand customer and predict behavior

Retailer 10

• Goal – improve gross margins
• Better merchandising
• Better buying decisions
• End-of-season vendor negotiations
• Inventory management
• Price management for markdowns and promotions
• Vendor analysis

Department Store

• Merchandising and buying decisions

Department Store

• Information to negotiate on end-of-season merchandise
• Inventory and price management for promotions and markdowns
• Vendor analysis

Automobile Convenience Store

• Better product mix by location and demographics
• Understanding price zones, profit, margins,
• Understanding the competition
• Understanding customers – images of the customers (who are they)
• – Results of surveys
  – Brand recognition
  – Customer demographics
  – Price sensitivity
• New locations
• Modeling/testing profitability of new items, different product mixes
• Effectiveness of advertising and promotions
• Understanding shrinkage by product, location,
• Understanding product tie-ins (coffee and doughnuts, hot dogs and soft drinks, gasoline and oil, smog check and tune-up) and trying out ways to exploit those tie-ins.
• Shelf allocation
• Use of ATM cards vs cash (are credit cards being considered?)
• Effectiveness of alternative business controls
• Statistical sampling when not all data is available
• – Suppliers/vendors
  – Price
  – Support/service
  – Delivery
  – Profitability
  – Quality
  – Alternative suppliers
  – Supplier negotiations
  – Joint promotions
  – Demographic data from suppliers

Fast Food

• Analysis of food costs
• Analysis of labor costs
• Analysis of sales data
• Service quality
• Customer information

Data Mining

• Advertising effectiveness
• Market basket analysis
• Profitability

Retailing — General

• Product profitability analysis
• Merchandise planning
• Analyze sales fluctuations
• Selling marketing data to suppliers
• Markdown management
• Identify markets to target for newspaper advertising inserts

Catalog

• Identify low profit products

Services

• Finance
• Revenue
• Purchase orders
• Human resources
• Customer profiles
• Materials management

Sports

• Winning player combinations
• Analyzing strategies, patterns of plays, defenses, players involved
• Player negotiation
• Internet statistical searches by fans

Telephone

Telephone 1

• Fixed asset analysis

Telephone 2

• Facility for sales reps to market to volume customers different long-distance packages based on their latest calling patterns.

Telephone 3

• Integrated customer and financial data
• Work request tracking

Telephone 4

• Customer information systems
• – Exploring product “churn”
  – Managing strategic accounts
  – Building customer loyalty
  – Market segmentation
  – Target marketing
• Product development
• Risk and fraud assessment

Telephony 5

• Customer analysis
• – Which customers are most likely to respond to an offer
  – Which customers are likely to accept new technology
  – Which customers are likely to respond to competitors’ offers
  – How much can we expect customers to spend on various products
  – Which prospects (non-customers) are likely to accept our offers

Telephony 6

• Identifying deadbeat customers and not marketing to these customers
• Identifying potentially profitable market segments

Telephony 7

• Fee tolerance
• Telemarketing
• Predictive modeling
• Merge lifestyle and demographic data to existing customer information

Data Mining

• Analyzing customers most likely to switch to another carrier
• Understanding customers
• Understanding customers’ desires and expectations in contrast to what they have ordered and what the company can provide

Transportation

Transportation 1

• Target marketing
• Understanding customer requirements

Railroad 1

• Customer satisfaction
• Train performance
• Derailment prevention
• Crew management

Railroad 2

• Fleet management, locomotive information
• Customer financial analysis

Railroad 3

• Rate analysis
• Profitability analysis
• Fleet maintenance analysis
• Identify tax-exempt purchases
• Understanding competitor’s cars on railroad’s line

Airline 1

• Customer service program – financial data

Airline 2

• Cargo volume analysis to understand source of revenue

Airline3

• Fraud analysis – issuing tickets on bad credit cards

Multi-industry (conglomerates)

• Planning and forecasting
• Make comparisons to plans

Cross Industry

• Legacy system retirement
• Reliability of sales forecasting
• Call centers – evaluating productivity and costs associated with varying responses to customer problems
• Control of property and income tax
• Financial
• – Budgets
  – Uncover problems in financial numbers before they are reported to upper management

Utilities

• Pricing
• Supply chain
• Asset management
• Human Resources – Medical benefits package evaluation
• Finance – Wholesale pricing models
• Accounts Receivable – maximizing collections for customers who missed a payment
• Accounts Receivable – analyzing processes to maximize collections for overdue accounts
• Fixed Assets
• Customer Information Systems

– Marketing
– Customer Satisfaction
– Financial
– Conservation

Marketing Queries

• Determining customer usage
• Understanding demographics and usage by demographics
• Tracking marketing programs
• Modeling a new program
• Cost justifying a new program
• Commercial customers – price sensitivity
• Data mining – looking for patterns that may be marketing opportunities
• Data mining – looking for patterns that predict delinquencies

Customer Satisfaction Queries

• Tracking customer comments and complaints
• – By demographics
  – By psychographics
• Tracking customer surveys
• Reporting to the Utility Commission and the Press
• Data mining

Financial Queries

• Customer profitability
• Evaluating relationship pricing
• Assessing alternative delivery channels
• Assess outsourcing possibilities
• Track profits across divisions

Conservation Queries

• Monitoring conservation programs by customer
• Identify pattern of customer (demographics) that could be candidate for conservation program
• Identifying customers who have (or have not) signed up for special programs
• Peak-period A/C disabled
• Old refrigerator surrender
• Florescent bulbs