Presenter : Vaibhav Dhawan Country Director

1. Smart Strategies for Business Intelligence Design and Implementation January 22, 2011 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 • Lunexa’s 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 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

13. An Introduction to Lunexa • Business Processes and Technology Solutions • Related Lunexa Case Studies • Best Practices Methodology • Technical Overview

14. Lessons Learned and Best Practices • Business process requirements should drive technology solutions and not the other way around; technology should aid process improvements. • Staff can get burdened with operational and manual activities and not focus enough on strategic activities. • Functional requirements must be assembled in an architecturally sound manner. • Best-of-Breed vs. Single (Integrated) Vendor • Vendor alternatives • Breadth and depth of requirements – today and in the future • Internal skill set • 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. • Reduce time-to-market by segmenting your customers iteratively and regularly, and not just when the next campaign’s targeting criteria are solidified.

15. An Introduction to Lunexa • Business Processes and Technology Solutions • Related Lunexa Case Studies • Best Practices Methodology • Technical Overview

16. 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 • Non-standardized business terminology

17. 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

18. Report Requirement • Mockup

19. 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

20. 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

21. 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

22. 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+

23. 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 Order Region Order Date Customer tl_cust_orderrec Customer Max(Order Date) Region Sum(Order Value) tl_cust_orderval Order Value Count(Order) Product tl_cust_orderfreq Store

24. 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 > 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 > 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 • on (a11.customer_id = a12.customer_id) • join tl_cust_net_orderval a13 • on (a12.net_orderval_id = a13.cust_net_orderval_id) • group by CASE WHEN a11.cust_num_orders = 1 THEN 1 WHEN a11.cust_num_orders = 2 THEN 2 WHEN a11.cust_num_orders > 2 THEN 3 ELSE 0 END, • a11.cust_orderrec_id, • a13.cust_net_orderlbl_id

25. Deliverable Results • Final RFM Report(Across All Stores)

26. Actual Dashboard, Export to Excel