Presenter : Vaibhav Dhawan

1
Smart Strategies for Business Intelligence Design
and Implementation July 31, 2010
Presenter Vaibhav Dhawan Country Director
2
Agenda

• An Introduction to Lunexa

• Business Processes and Technology Solutions

• Related Lunexa Case Studies

• Best Practices Methodology

• Technical Overview

3
Complementary, End-to-End Advisory
Implementation Services

• Lunexa is a consulting firm focused on providing
  advisory and implementation services to help
  clients unlock opportunities from their data
  assets.
• Corporations at the leading edge of business
  intelligence development choose to work with
  Lunexa because we offer unique, end-to-end
  expertise in all aspects of the data warehouse
  technology stack
• Business intelligence, reporting and analysis
• Database design and development
• Enterprise data integration
• Lunexas offerings emphasize the importance of
  advisory services that complement implementation
  efforts for each project
• Architecture planning and design
• Benchmarking
• Best practice methodology
• Business process analysis
• Development and deployment strategy
• End-to-end impact analysis
• Project audits
• Tuning and optimization
• Vendor collaboration

4
Lunexa Consultants Customer Experience
Lunexa consultants experience with a wide array
of applications will allow clients to get a head
start on planning and development efforts. Rather
than waste time to re-define the generic aspects
of these applications, customers can focus on the
requirements for their own unique business models.
5

• An Introduction to Lunexa

• Business Processes and Technology Solutions

• Related Lunexa Case Studies

• Best Practices Methodology

• Technical Overview

6
Business Processes and Technology Solutions

• Business Process analysis should initiate the
  design and development of any technology solution
• Analytical applications require the definition of
  a business decision architecture
• Operational applications must be designed with
  use cases and activity diagrams
• IT Strategy necessitates the modeling of broader
  business processes, both internal and external
  (involving customer and partner interactions), to
  determine the role that technology can play
  within the business processes

7
Business Processes and Technology
Solutions Analytical Applications and Business
Decision Architecture

• Breakdown the different steps of a business
  process

• What decisions need to be made at
• each step of the business process?

• What information is needed to make the decision?
• What questions need to be answered to make the
  decision?

• Business / marketing Intelligence constructs
• Reporting and analysis components

8
Business Processes and Technology
Solutions Operational Applications, Use Cases
and Activity Diagrams
Different Levels of Detail for Business Process
Definition
The high-level business process is broken down to
use cases for different steps
More detailed activity diagrams are then created
for each step of a use case
9
Business Processes and Technology Solutions IT
Strategy and Broader Business Processes

• With the online division of a leading retail
  bank, Lunexa consultants worked with business
  process maps describing customers and prospects
  multi-channel interactions with the bank in order
  to define and implement KPIs and interactive
  dashboards that enabled the end-to-end
  measurement of the business processes.

Collect, Analyze Deconstruct Metric Components
Identify KPIs
GDWG Reviews, Certifies Publishes Key Metrics
Deploy, Publish Train
Verify / Validate
Develop Test
Standardize Certify
10

• An Introduction to Lunexa

• Business Processes and Technology Solutions

• Related Lunexa Case Studies

• Best Practices Methodology

• Technical Overview

11
Proposal Review Related Lunexa Case Studies

• Leading Credit Card Company
• Highlights
• Gathered business requirements and defined the
  end-to-end detailed design for the campaign
  management platform including
• Centralized customer database with 50 million
  cardholders
• Direct marketing engine
• Integrated workflow using Aprimo
• Post-campaign analytics
• Creative and branding approval web interface
• This is the first time the Credit Card Company
  has taken on such campaign management and loyalty
  marketing initiatives. These activities were
  outsourced in the past.
• Facilitating and managing the workflow across
  numerous organizations banks and merchants
  presented a unique challenge.
• Solution Type Campaign management platform
• Data Sources Credit card transactions,
  cardholder data and campaign responses
• Related Technologies Aprimo and MicroStrategy
• Lunexa Activities Business requirements
  gathering, tool evaluation, detailed design

12
Proposal Review Related Lunexa Case Studies

• Leading Retail Bank
• Highlights
• Analyzed existing marketing automation and
  business intelligence technologies in use to
  determine gaps/overlaps and create a master
  inventory of business metrics.
• Using the clients business process maps as a
  foundation, interviewed the sales marketing
  teams to consolidate 2,400 metrics into 30 KPIs
  with detailed business requirements.
• Established an ongoing approach and process to
  identify, validate and publish new KPIs and
  maintain existing ones.
• Designed a metrics repository database to support
  the development of interactive performance
  dashboards on the initial set of KPIs.
• Implemented the clients first interactive
  performance dashboard to present executive
  management and product managers with a single
  integrated view across all 100 banking and
  financial products.
• Solution Type Business intelligence and online
  performance management
• Data Sources Account and customer information,
  web traffic, inbound and outbound campaigns,
  sales activity
• Related Technologies E.Piphany, Mediaplex,
  Omniture, Unica and Visual Sciences
• Lunexa Activities Business requirements
  gathering, metric consolidation strategy,
  performance dashboard design and development

13
Proposal Review Related Lunexa Case Studies

• Leading Online Retailer
• Highlights
• Gathered business requirements for business
  intelligence from the CEO and heads of Marketing,
  Merchandising and Product Management.
• Detailed end-to-end design for data integration,
  reporting and analytics.
• Process for identifying unique customers from
  named and anonymous purchases across multiple
  sites hosted by the Retailer.
• Customer segmentation is the key focus of the
  business intelligence design.
• Currently implementing the enterprise data
  warehouse with web analytics, e-commerce and
  customer demographic data.
• This will allow the Retailer to attribute
  purchase decisions to specific marketing
  activities.
• Solution Type Business intelligence and
  enterprise data warehouse
• Data Sources Web analytics, e-commerce
  transactions and customer demographics
• Related Technologies Great Plains, Omniture and
  YesMail
• Lunexa Activities Business requirements
  gathering, detailed design and development

14

• An Introduction to Lunexa

• Business Processes and Technology Solutions

• Related Lunexa Case Studies

• Best Practices Methodology

• Technical Overview

15
Lessons Learned and Best Practices

• Business process requirements should drive
  technology solutions and not the other way
  around technology should aid process
  improvements.
• Functional requirements must be assembled in an
  architecturally sound manner.
• Enterprise Marketing Management (EMM) comprises
  various components of marketing automation and
  intelligence, and is slow to be offered by the
  vendor community as an integrated solution.
• The Gartner Quadrant identifies no leaders in
  this category
• Components include web analytics, campaign
  management, marketing resource management, lead
  management, event-driven marketing, predictive
  modeling and more.
• The marketing staff can get burdened with
  operational and manual activities and not focus
  enough on strategic activities.
• Reduce time-to-market by segmenting your
  customers iteratively and regularly, and not just
  when the next campaigns targeting criteria are
  solidified.

16
Lessons Learned Best Practices

• The two most complex activities are
• Business process integration adoption of new
  technologies and roles
• Data integration single view of the customer,
  sales attribution and response identification
• The holistic view of the customer must include a
  detailed understanding of customer touches -
  marketing deliverables can result from different
  departments and reduce the effectiveness of the
  combined message . You may have many campaign
  management initiatives but you are targeting
  individual customers.
• KPIs need to be identified that reflect the
  effectiveness of overall marketing strategies
  there is a tendency to look at operational
  metrics at a very granular level, on a per
  campaign basis, that can be influenced by too
  many external factors.
• Best-of-Breed vs. Single (Integrated) Vendor
• Vendor alternatives
• Breadth and depth of requirements today and in
  the future
• Internal skill set
• Segmentation requires an intuitive interface and
  workflow as well as sophisticated analytics.
  Picking the right technical solution for each can
  be a challenge.

17

• An Introduction to Lunexa

• Business Processes and Technology Solutions

• Related Lunexa Case Studies

• Best Practices Methodology

• Technical Overview

18
BI/DW Design Issues
What Customers Want! The end user needs reporting
capabilities with acceptable performance that
delivers results as per their business
requirements.

• Major factors that can directly influence the
  success of a BI/DW design and implementation
• ETL performance
• Disparate legacy systems
• Source system impact
• Data volume growth
• .
• Report query performance
• Complex queries
• Database optimization
• Data Model
• Data Quality
• Multiple data sources
• Business rules
• Error-free ETL

19
Best Practices Methodology

• System Requirement Study  Gap analysis of the
  existing processes in order to provide concrete
  recommendations and set expectations on what can
  and cannot be met given the constraints
• Impact Analysis Understand the clients business
  requirements and the potential ripple effect on
  the data model
• Integrated Requirements Gathering To understand
  clients business growth model and to optimize
  the long term reporting requirements, often
  beyond initial request

20
Real Time Case study RFM Customer Segmentation
for Retail

• Business Requirement Ability to look at unique
  customers from inception through to present time
  selected by Recency, Frequency, Monetary value
• Recency Elapsed time since last order
• Frequency Lifetime number of orders
• Monetary Value Lifetime order value
• Level(s) Store, Product Category, Region,
  Customer Demographics
• Date Range Current snapshot of lifetime customer
  segmentation values

21
Report Requirement

• Mockup

22
Technical Challenges

• Design Approach
• Primary Fulfill reporting functionality of
  providing customer segmentation at multiple
  levels with acceptable levels of database query
  performance
• Secondary Basic level of flexibility in changing
  segmentation buckets
• Tradeoff Lifetime calculation limits reporting
  flexibility

23
The Solution Step 1
Create lookup tables for each RFM segment that
will allow between joins
Segmentation Attribute Order Frequency

• The Order Frequency lookup table categorizes the
  number of orders made by a customer into data
  buckets.
• NA (No Orders)
• 1 Order
• 2 Orders
• 3 Orders

24
The Solution Step 1
Create lookup tables for each RFM segment that
will allow between joins
Segmentation Attribute Order Recency

• The Order Recency lookup table categorizes into
  buckets the time elapsed since the last order
  made by a Customer.  The buckets are defined to
  be
• NA (No Orders)
• NTF (New To File, First lifetime Order this
  Month)
• 1-3 Months
• 4-6 Months
• 7-9 Months
• 10-12 Months
• 13-24 Months
• 25 Months

25
The Solution Step 1
Create lookup tables for each RFM segment that
will allow between joins
Segmentation Attribute Order Value

• Lookup table listing Pre-definded buckets based
  on the Order value in dollars. The buckets are
  defined to be
• 1 10
• 11 - 20
• 21 - 30
• 31 - 40
• 41 - 50
• 51 - 60
• 61 - 70
• 71 - 80
• 81 - 90
• 91 - 100
• 101

26
The Solution Step 2

• Summary level tables for each segmentation level
  (Region, Store, Product Category, Customer)
• Each table includes the data required for
  segmentation, like lifetime order value and order
  count
• Nightly ETL loads recalculate these metrics for
  each customer who made an order that day and
  updates the summary level tables

27
The Solution Step 3

• Views on top of the summary tables that do
  between joins up to your segmentation tables thus
  ensuring report performance
• Single pass query
• Covers entire history of transactions
• Low query time
• select CASE WHEN a11.cust_num_orders 1 THEN 1
  WHEN a11.cust_num_orders 2 THEN 2 WHEN
  a11.cust_num_orders gt 2 THEN 3 ELSE 0 END
  custacct_num_orders,
• max(CASE WHEN a11.cust_num_orders 1 THEN '1
  Time' WHEN a11.cust_num_orders 2 THEN '2 Times'
  WHEN a11.cust_num_orders gt 2 THEN '3 Times' ELSE
  'No Orders' END) order_freq_desc,
• a11.cust_orderrec_id cust_orderrec_id,
• max(a11.custd_orderrec_desc) cust_orderrec_desc,
  
• a13.cust_net_orderlbl_id cust_orderval_id,
• max(a13.cust_net_orderlbl_desc)
  cust_net_orderlbl_desc,
• count(distinct a11.customer_id) WJXBFS1,
• sum(a11.cust_net_sales) WJXBFS2
• from vl_cust_orderrec_seg a11
• join vl_cust_net_orderval_seg a12

28
Deliverable Results
Final RFM Report(Across All Stores)
29
Actual Dashboard, Export to Excel
30
(No Transcript)