Use Analytics to Identify Your Best and Worst Customers

Harness Customer Profitability Analysis to Boost Profits

David Haertzen

Customer Profitability Analysis (CPA) is an analytic approach that determines the profitability of individual  customers or segments of customers by identifying revenue and cost patterns associated with those customers.  This includes identifying the most profitable customers (angels) as well as unprofitable customers (devils).

Dr. Robert Kaplan of the Harvard Business School has conducted research that has contributed greatly to CPA.  He wrote “.. The most profitable 20% of customers generate between 150 – 300% of total profits.  The middle 70% of customers about break even, and the least profitable customers lose 50 – 200% of total profits, leaving the company with its 100% of total profits.  Often some of the largest customers turnout to be the most unprofitable.”

Best Buy Case Study

Best Buy was able to build profiles of best and worst customers.   Patterns of customer behavior were identified by an analysis of company databases.  It’s research showed:

  • 20% of customers are angels and result in bulk of profit
  • 20% of customers are devils and reduce profits by 20%
  • 60% of customers are breakeven
  • Profitable and unprofitable customers show patterns of behavior.

Based on the research results Best Buy was able to make changes to improve its profitabilty.  The company attracts the most profitable customers by promotions, stocking desired products and providing best service.  In addition, the company avoids unprofitable customers by dropping them from promotion lists, stopping loss-leader promotions and charging fees for restocking.

How to Analyze Customer Profitability

Determining the profitability of individual customers is a challenge.  Traditional accounting does not identify the profitability of individual customers, so Dr. Kaplan recommends the use of Time Drive Activity Based Costing (TDABC).  This approach identifies the true Cost To Serve (CTS) individual customers by adding the service interactions with each customer such as: customer service telephone calls and product returns.  This analysis should consider revenues, expenses, opportunites and risks:

Revenues Revenues generated may include: 

  • Initial sale of goods and services
  • Follow on sale of goods and services
  • Service plans
  • Fees
Expenses Expenses incurred may include: 

  • Cost of goods sold
  • Discounts and coupons
  • Customer service calls
  • Returns
  • Warranty repairs
  • Mailing invoices and statements
  • Collecting overdue receivables
Opportunites Opportunities generated may include: 

  • Customer recommendations and testimonials
  • Feedback to improve products
Risks Risks incurred may include: 

  • Customer complaints harm reputation
  • Company focuses on unprofitable customers

Detailed, time-phased data that has been integrated at the individual customer level is required.
Time-phasing is needed to determine behavior sequence.
For example, a customer may return an item and then buy back the same item at a lower cost
when it has been restocked.

 

Profiles of Best and Worst Customers

After determining the profitability of individual customers, customer segmentation analysis can be used to determine behavior patterns of best and worst customers.  The segementation may look like this:

Best Customers Worst Customers
  • Orders standard products
  • Orders standard handling
  • Orders via web or ecommerce
  • Makes short service calls
  • Almost never returns goods
  • Orders large volume
  • Pays on time
  • Praises company on social media
  • Orders custom products
  • Orders via call centers
  • Often makes lengthy service calls
  • Frequently returns goods
  • Orders special handling
  • Orders small volume
  • Requires low cost – price match
  • Pays late – requires collection
  • Complains on social media
  • Buys only on sale or with coupons

You may find that some of the largest volume customers are the least profitable due to the low prices that they negotiate and the costly service that they require.

 References

Analyzing Customers, Best Buy Decides Not All Are Welcome : Wall Street Journal, 2004

Time-Driven Activity Based Costing : Dr. Robert Kaplan – Harvard Business School

Motivation-Need Theories and Consumer Behavior : W. Fred Van Raaij

The Analytical Puzzle – Profitable Data Warehousing, Business Intelligence and Analytics : David Haertzen

Text Mining Use Case

David Haertzen – October 2019

Text mining methods are techniques that can turn unstructured data like emails, tweets and recordings into actionable insights.  The knowledge gained can be used to both identify opportunities and serve customers as well as management risks such as cybercrimes.  Examples of text mining use cases that capitalize on opportunities include:

  • Customer Experience: Obtain knowledge about customers through diverse sources such as emails, surveys and calls to provide automated response and to identify opportunities and issues.
  • Contextual Advertising: Target advertising to specific customers based on analysis of text
  • Business Intelligence: Answer specific business questions through scan and analysis of thousands of documents.
  • Knowledge Management: Gain value from huge amounts of information in areas like product research and clinical patient data.
  • Content Enrichment: Add value to content by organizing, summarizing and tagging.
  • Social Media Analysis: Scan large data volumes to gather opinions, sentiments and intentions relating to organization reputation, brands and offerings.

Examples of text mining use cases that address risks and losses include:

  • Cybercrime Detection: detect malicious threats such as ransomeware and identity theft using machine learning to identify likely malware. Machine learning identifies trends and improved its predictions formed through experience.
  • Fraud Detection: Identify potential fraudulent activity such as insurance claim fraud through analysis of unstructured data.
  • Risk Management: Scan thousands of documents to find patterns that identifying risks to be addressed.
  • Spam Filtering: Reduce the volume of spam through better filtering tuned through machine learning.

How can we take advantage of these use case?  One way, is to use the Text Frequency – Inverse Data Frequency (TF-IDF) method to quantity the strength of words that make up documents – based on the relative frequency of words. The flow of this process is illustrated in the following diagram.

There are five major steps to this process:

  1. Gather Text: Read in the body of text (corpus) from sources such as:  emails, reports, tweets, comments and notes which may be stored as separate files or as fields in a database.
  2. Preprocess Text: Produce a streamlined version of the text by removing punctuation, shifting to lower case, removing stop words and location words, resolving to word stems (stemming). Using tokenization methods such as “bag of words” render words into streams of numbers.
  3. Apply TF-IDF Algorithm: Calculate the strength of words using  the TD-IDF calculation. Text Frequency (TF) for each word in a document = specific word count divided by total words in document count.  Inverse Document Frequency (IDF) = log e(total number of documents / total documents containing the word. Finally, TD-IDF = TF * IDF.
  4. Output Structured Data File: Generate one flat file record for each input document. Each record will contain a document identifier plus a field for each word of interest. See the example structured flat file below.
  5. Apply Data Science Algorithms: The generated flat file is in a format where data can be better understood or outcomes predicted using data science algorithms such as: regression, decision tree, clustering or neural network.

In conclusion, text mining methods are available that can be used to capitalize on opportunites, reduce losses and manage risks.  The TF-IDF method is one of many approaches to successful data mining and is a good example of the overall approach.  Typically multaple documents are scanned, pre-processed and then analyzed using an algoritm like TF-IDF, Keyword Association Network (KAN) or Support Vector Machines (SVM).  Libraries of algorithms such as Python Scikit-learn support text processing via machine learning.  I encourage you to learn more about text processing and its applications.

Model Your Customer – Part 1

David Haertzen, September 2019

Would you like to better understand your customers?   Customer knowledge can both help to improve revenues, reduce losses  and avoid risks.   A customer is broadly described as a party (person or organization)  who is of interest to the enterprise.   A customer model that identifies characteristics of your customers  is a great way to achieve a level of understanding and achieve  company goals.  This article is the first one in a series about customer modeling.

There are two broad categories of customer models:   data models and analytical models.  Data Models are representations, usually through graphical means, of facts,  statistics, or items of  information. Data models may be used to:  understand data, communicate the structure of data and design data structures.   In contrast, Analytical Models are representations of reality coupled with
algorithms that produce results such as:  classifications, predictions, optimizations or recommendations.

Customer Data Model

This information can be organized into a high level data model for  better understanding and easier management. This diagram shows  the Shrewd Data high level Customer Data Model.

Examples of data for each topic in the customer data model include:

  • Customer Identifiers: Account numbers, tax id numbers, drivers license numbers
  • Demographics: Birthdate, age, gender, marital status, education level
  • Measures: Networth, income, revenue, credit score, Lifetime Customer Value (LCV)
  • Behaviors: Social media, driving record, address changes
  • Locations: Geo location (country, state, city, zip), district, telephone
  • Psychographic: Opinions, values, sentiments, preferences, risk tolerance
  • Transactions: Purchases, returns, payments, deposits, withdrawals
  • Interactions: Service calls, web visits, abandoned cart, response to offers
  • Products: Current products owned, products under consideration

These topics are the “tip of the iceberg”.  A full 360 degree view of customer will be comprised of multiple data stores with multiple data sources.

Customer Analytical Models

Analytical models apply algorithms to data to: enable better understanding, make predictions or recommend decisions.  Examples of customer analytical models include:

  • Acquisition Model: A model that predicts the probability that a prospect will buy the company’s products or services.
  • Cross-sell Model: A model that predicts the probability that an existing customer will buy additional products or services of a different type than currently bought. Goods are at the same level.
  • Up-sell Model: A model that predicts the probability that an existing customer will buy an upgraded product or service.
  • Attrition Model: A model that predicts the probability that an existing customer will stop purchasing the company’s products or services. This also known as a churn model.
  • Value Model: A model that predicts a numeric value such as customer lifetime value (CLV) or value resulting from the sale of a specific product to a customer.
  • Tone-Of_Voice Model: A model that identifies the most effective message for each targeted customer.
  • Risk Model: A model that predicts potential negative activities by customers such as: fraud, loan defaults, or excess service costs.
  • Customer Segmentation Model: A model that assigns customers to groups with similar characteristics.
  • Recommendation Engine: A model that provides advice on a near real-time basis – such as advice about offers that should be made to a customer or additional products to show to a customer.
  • Look-alike Model: A model where the target-marketed group (e.g. for a marketing campaign, product offering etc.) is an expanded list of parties whose profiles look like the selected party.

Customer Model Conclusion

In conclusion, customer models are powerful tools that have the potential to impact an organization’s bottom line.  You can learn more about this topic by studying additional articles and white papers provided on the Shrewd Data and other websites.

Recruiting the BIA Sponsor – Management for Profitable Analytics – Part 2

thumb_project_schedule_wpclipart_600x435In this article you will better understand the role of the Business Intelligence and Analytics (BIA) sponsor  and you will be ready with a five step approach to recruiting a person to fill that role.  The role of the sponsor is critical to the success of the BIA project or initiative.   This individual is a senior management person who takes overall responsibility for the effort.

Seek out a BIA sponsor who has a large stake in the project outcome as well as authority over the resources needed for the project.   Look for someone with enough authority throughout the organization to manage competing priorities.   It will help if there is organization wide recognition that put business intelligence and analytics as high priority and worthy of sponsorship.

The BIA sponsor fills a number of roles including:

  • Definer of the BIA vision
  • Owner of the business case
  • Harvester of benefits
  • Overseer of the project and chair of the BIA steering committee
  • Ambassador of the BIA effort to upper management.

BIA champions complement the work of the project sponsor.   Look for people who will promote data warehousing efforts across the organization.   They make sure that the project is aligned with enterprise goals and help sell the project to the rest of the organization.

The scope of a BIA effort is also highly dependent on the level of authority of the project sponsor.   If the sponsor is the CEO or CFO, then the scope can be enterprise wide. If the sponsor is a business unit head,   then the scope is likely the business unit. If the sponsor is a department head, then the scope is likely limited to a single department.

Conversely, an Enterprise BIA or Data Warehouse project requires a higher level executive sponsor with more authority  and resources than is required for a Departmental Data Mart project.

Five Steps to Recruiting the BIA Project Sponsor

Recruiting the BIA project sponsor is too important to leave to chance. Follow this step by step approach to recruiting your BIA project sponsor and champions:

1. Define the Requirements

      Start by identifying the characteristics that are needed in the BIA sponsor.    Consider the scope of the project. What you need the sponsor to do? Who needs to be influenced?   What needs to be signed off> What personality type needed?  Do you need to motivate an enterprise wide team or a departmental unit?   Put the requirements and their weighted priority in a spreadsheet.

2. Identifiy the Best Candidates

      Build a list of candidates for the role and narrow it down to a short list based on the identified requirements.   This means scoring each of the candidates based on the prioritized requirements.     Remove any candidate from the list who score poorly on the most critical requirements.

3. Analyze Candiate Capacity

      Perform an analysis of the short listed candidates to make sure that they have the time available to support the BIA effort.   It is critical that the selected person on the list is devote enough time to your project and not be a sponsor in name only.

4. Plan your Marketing Approach

      Selling the BIA effort to the BIA sponsor and other critical stakeholders requires  building a short “elevator pitch” which summarizes the benefits of the BIA program in 10 to 20 seconds. You will use the elevator pitch to gain initial interest so that you can provide greater detail and “close the sale”.   Components of the elevator pitch may include:
      • Problem identification
      • Proposed solution
      • Value proposition
      • Competition reference – what competitors are doing
      • Team identification
      • Resource needs

The BIA sponsor is more likely to respond to a value proposition that addresses organization pain points and strategies rather than technical features.  Put in business benefit terms and then package that into the “elevator speech”.

Technical Feature

Business Results

Integrated customer data

Effective use of marketing dollars

Improved customer experience

Dashboards

Visibility of enterprise performance

Data warehouse cubes

Fast Results

Advanced hardware platform

Capacity to enable business growth

Cloud BI

Faster time to market and lower cost of ownership

5. Present the Pitch

          The presentation of the elevator speech to the candidate sponsor definitely should be done in person.  First, rehearse – be ready for a smooth presentation followed by questions from the prospective sponsor.  Second, if you do not have a direct connection with the sponsor, find a connection with a mutual contact who can introduce you to the sponsor. This is better than trying to corner by the water cooler or literally by the elevator. You should ask the sponsor to meet for an executive briefing rather than immediately asking the sponsor to be a sponsor.
          The executive briefing will enable you to better understand the candidate sponsor and build toward the close when the candidate sponsor is asked to commit to the BIA program.

 

Partnering with the BIA Sponsor

You have gained a better understanding of the BIA sponsor role  and you cam use the five step approach to recruiting a person to fill that role.   Recruting the BIA sponsor is just the beginning. Now it is time to work with the BIA sponsor to launch and implement the BIA effort. Be sure to keep him or her in the loop:

  • Provide regular status reports
  • Request help in dealing with roadblocks, especially those requiring cooperating across the organization
  • Align BIA effort with organization goals

 

Management for Profitable Analytics – Part 1

thumb_project_schedule_wpclipart_600x435In this blog tutorial series, you will learn about the management of successful business intelligence and analytics projects. Topics include:

  • Defining Scope and Objectives
  • Finding the Right Sponsor
  • Producing the Project Roadmap and Plans
  • Organizing the Team
  • Executing the Plan
  • Finishing the Project
  • Avoiding Major Data Warehouse Mistakes.

Defining Scope and Objectives

Scope specifies the boundaries of the project. It tells what is in and what is out. The scope definition started in the business case will be expanded, if needed, when the project is underway. This effort includes:

  • Overview of the project (Mission, Scope, Goals, Objectives, Benefits)
  • Scope plan
  • Scope definition
  • Alternative development.

Defining the correct scope and setting realistic objectives are critical to any project’s success, and a data warehouse project is no exception. Scope defines project boundaries including:

  • Business requirements addressed
  • Anticipated/planned users
  • Subject Areas such as inventory transactions or customer service interactions
  • Project success criteria, including quantified planned benefits.

Defining an overly large project scope and letting scope grow in an uncontrolled fashion (scope creep) are certain to cause project failure. Remember you cannot please everyone:

I cannot give you a formula for success,

but I can give you a formula for failure: try to please everybody.

Herbert Bayard Swope

Enterprise vs. Departmental Focus

The choice of Enterprise Data Warehouse vs. Departmental Data Mart is critical to the success of data warehousing projects. This choice is a major component of project scope. Examples of factors that arise with each focus, based on my experience, are shown in Table 1.

Table 1: Enterprise vs. Departmental Focus

Factor Enterprise Focus Department / Functional Focus
Organizational Scope Enterprise Wide Business Unit or Business Process Focused
Time to Build Multi-year phased effort Single Year effort
Sponsorship Required Executive Sponsor Management Sponsor
Complexity High Medium
Typical Cost Often a multimillion dollar effort Often less than $1 million effort

The project may require both an Enterprise Data Warehouse and one or more Data Marts. The future Technical Architecture blog article will explain more about this choice.

Contemporary Data Architecture Patterns

Data Warehousing Architecture Patterns

The choice of where and how to store the data for the analytical use is a critical architectural question. Part of the issue is the Contemporary Data Architecture (CDA) pattern, which is explained in Table 1. CDA patterns include:

  • Independent Data Marts
  • Coordinated Data Marts
  • Centralized Data Warehouse
  • Hub and Spoke
  • Federated / Logical Data Warehouse
  • Big Data / Data Lake.

Table 1 DW Architecture Patterns

Pattern Description
Independent Data Marts Multiple databases containing analytic data are created and maintained by different organizational units. The databases tend to be inconsistent with each other, having different dimensions, measures and semantics. There is “no single version of the truth” and it is a challenge to perform cross data mart analysis. These weaknesses make the independent data mart the least desirable of the architecture patterns.
Coordinated Data Marts Data is harmonized across multiple analytic databases or areas of databases. Each database meets a specific need such as customer segmentation or product analysis. There are shared dimensions and semantics between the databases. For example, customer and product dimensions are consistent across the databases. Consistent dimensions and semantics support a logical enterprise view.
Centralized Data Warehouse An Enterprise Data Warehouse (EDW) contains atomic data, summarized data and dimensional data. This is provided through a single database. Logical data marts are built from this database to support specific business requirements. Analysis between subject areas is facilitated by data being stored in a single database.
Hub and Spoke The hub and spoke architecture is similar to the centralized data warehouse architecture except that there are physical data marts instead of logical data marts. A cost and complexity element is added due the need to copy data from the central data warehouse to the distributed data marts.
Federated / Logical Data Warehouse The federated/logical data warehouse makes data that is distributed across multiple databases look to users like a single data source.  Data may be stored in traditional relational databases as well as Hadoop or NoSQL formats. Data Virtualization software is used to create a logical view of the data and to optimize query of data across databases.

Common keys such as customer and product identifiers are used to tie the data together. This approach reduces the expense and time needed for data movement. The approach may have a high overhead and slow response time because queries between data sources in multiple locations can be inefficient.

Big Data / Data Lake The big data/data lake architecture stores data in the Hadoop environment which is an economical and flexible way to store large volumes of data including unstructured data.

Some organizations are shifting away from traditional relational databases and moving to big data environments.

Data Model Patterns for Data Warehousing

A data model is a graphical view of data created for analysis and design purposes. While architecture does not include designing data warehouse databases in detail, it does include defining principles and patterns for modeling specialized parts of the data warehouse system.

Areas that require specialized patterns are:

  • Staging / landing area – looks like source systems
  • Atomic Data Warehouse – uses normalized ERD (Entity Relationship Diagram)
  • Data mart – uses dimensional modeling – the star schema, the snowflake schema or the cube.

In addition to these specialized patterns, the architecture should include other pattern descriptions for:

  • Naming of tables and columns
  • Assignment of keys
  • Indexing
  • Relational Integrity (RI)
  • Audit history.

Infogoal Blog Kick Off

David Haertzen – May 2019

The uses of data and analytics to produce effective outcomes are the focus of the Infogoal Blog.  The blog will extend the book, The Analytical Puzzle: Profitable Data Warehousing, Business Intelligence and Analytics, as well as our body of data and analytics work including: white papers, articles, courseware, presentations websites and software products.

The planned data and analytics topics will include:

◾Big Data Architecture – “The Data Lake”
◾Predictive Analytics
◾Data Mining Methodology
◾Customer Modeling
◾Data Warehousing and Data Marts
◾Conceptual and Logical Data Modeling
◾Data Modeling for Data Warehousing
◾Metadata Fundamentals

The blog will serve members of the data and analytic community who want to:  choose frameworks, approaches, products and services that help them to succeed; be recognized as knowledgeable experts in the field; avoid expensive mistakes; and save time by following effective practices.  We will not provide a sales pitch on products.  Instead we will provide solid information from proven experts.
Another area we can help is in establishing a successful data and analytics program which may include: determining the objectives of the effort; assessing the maturity of the capability; developing strategies and roadmaps; making the business case; creating architectures; and building a BIA Center of Excellence. In support of these activities we will describe fast track approach that produce an effective outcome; save time and mitigate risk when tailored to your organization’s specific needs.