Transcription
MASTER THESISReference System Architecture for Trade Promotion ManagementLeveraging Business Intelligence Technologies and Decision SupportSystemsAndra Bianca BalmusUNRESTRICTED
Management SummaryReference System Architecture for Trade Promotion ManagementJune, 2011Andra Bianca Balmus, s1016970Master of Science in Business Information TechnologyUniversity of TwenteSchool of Management and GovernanceEmail: andra.balmus@googlemail.comGraduation CommitteeFirst SupervisorDr. Maria-Eugenia IacobSecond SupervisorDr. Marten van SinderenProcter & Gamble Supervisors:Murk van Busschbach, P&G NetherlandsArnoud Campman, P&G BelgiumRemco Brouwer, P&G NetherlandsiiUNRESTRICTED
Management SummaryManagement SummaryOver the last couple of years, the attention of fast moving consumer goods (FMCG) companies hasshifted from direct consumer promotions and advertising to trade promotions established incollaboration with their channel members (retailers, distributors). Traditional trade promotionmanagement systems (TPMS), however, fail to provide the flexible and complex analytical functionalityrequired for accurate effectiveness tracing and optimization of trade promotional spending. Mostcommonly encountered issues range from unreliable, inaccurate and inconsistent data and low userfriendliness of the system to complicated, inflexible and time-consuming reporting, and low systemintegration.ObjectiveThis research provides exactly that; a reference system architecture, based on literature and practicalresearch, integrating the functionality of the TPM solution in place with BI tools and a recommendedarchitecture tailored to the current situation at P&G NL. By proposing such a reference architecture, weaim to enable a solution in which trade funds are effectively managed within the bounds of anintegrated architecture, Key Users achieve superior decision making as a result of consolidated externaland internal company data and all reporting is based on a Single Source of Truth.MethodologyTo achieve this goal, we focused first on defining a trade promotion management process and the KPIsfor promotional effectiveness, as recommended by external consulting firms, such as Accenture, Infosys,Synectics, SAP and Trade Promotion Management Associates. Secondly, we defined a reference systemarchitecture combining BI technologies and architecture principles with the TPM solution, following thesteps of the TOGAF ADM cycle. We especially focused on the elements having direct or complimentaryeffects on data reliability, accuracy and availability, user friendliness and performance of the end-userTPMS setup and better system integration. We employed a two stage approach for the validation of theproposed system architecture. We first conducted external interviews with 6 Information System (IS)experts and research consultants, with extensive knowledge and experience in Enterprise Architectures,followed by 13 internal P&G interviews, with application/IT/project managers inside the TPM space. Theresults of the validation had an average score of 4 (on a scale of 1 to 5), supporting our hypothesis thatsuch an architecture can improve the evaluation of trade promotion effectiveness.iiiUNRESTRICTED
Management SummaryKey FindingsThe research yielded a reference system architecture whose main points can be summarized asfollowing: Single Source of Truth: Build one Single Source of Truth – consolidate (cleanse, transform,aggregate) all data from transactional systems, external share and customer data in one enterprisedata warehouse (EDW). Based on it, build specialized Data Marts with aggregated and summarizeddata.Single Source for Data Traceability: Consolidate one central Metadata Repository – Build one centralmetadata repository, holding all information regarding ER diagrams of source & destinationsystems, rules incorporated in the ETL Tools, data characteristics.Real Time Reporting: Limit all reporting queries to the Data Cubes only, actualized on a “right” timebasis – All reporting are carried out on the Data Cubes, which extract their data from the EDW,actualized on a micro-batch ETL process.Single Source of Reference: Create community pages personalized per user groups(Marketing/Sales/Financial Department, Lead Team, Top Mgmt) – Build a personalized CommunityPage, where all reporting/BI output are shown according to the access rules, authorizations andneeds of the specific user groups (“role-based access control”)ivUNRESTRICTED
PrefacePrefaceFor me, January 2011 signified not only the beginning of a new calendar year, but also the start of anexciting internship in the Information & Decision Solutions function at Procter & Gamble, theNetherlands. Six months later, the results and experiences exceeded my expectations. I successfullycompleted the master assignment, met with intelligent and open-minded people, participated in avariety of company events and IT projects, and attended many meetings, lunches, and training sessions.The process entailed both knowledge enhancement and personal development. For the latter, thechallenges I encountered all along the way helped me overcome my weaknesses and integrate alloutcomes in this research. For the former, I went outside of my usual focus area and learned abouttrade promotion management, as well as about business intelligence and reporting capabilities. Themost interesting issues were faced when learning by doing, when given full responsibility for the tasksand when having impromptu discussions with individuals holding a strong view on the subject matter.Overall, this project would not have been successful without certain people, who supported, challengedand gave me sparks of inspiration throughout the entire period. First, I would like to thank Murk, mymain supervisor from P&G, who always had time for my questions and offered me perspectives I hadnot thought about myself. The discussions we had, the enthusiasm and the trust he constantly gave meprovided one of the incentives to succeed and believe in my work more. Second, I would like to thankArnoud and Remco, my further managers, who always provided me with challenging comments andmotivated me to set high goals in everything I do. Third, I would like to thank my supervisors from theUniversity of Twente, from whom I highly enjoyed the straight-forward feedback. Maria always helpedme stay focused and not lose sight of the main goals of the research, while aiming for the highest qualitypossible. Her support and conviction in this research was not constrained by office hours orconventional thinking. Marten further offered me out of the box thinking and his comments made mepause for some time before having an adequate answer. Fourth, I would like to thank my parents,without whom I could have never been here to start with; their love, immeasurable efforts and constantbelief and support are and will always be the main drivers of my success. Fifth, I would like to thank allthe people who made the time to participate in my research and help me finish a project which willhopefully provide insights for other interested people. Last but not least, I would like to thank my bestand closest friends, Nurcan, Eyla, Polina and Matei, for believing in me and going out of their ways inmaking me happy and strong whenever I needed it.I hope that you will enjoy reading this research and you will be able to take advantage of the mostimportant insights. If you have any questions or comments, please do not hesitate to contact me.Best regards,AndraRotterdam, 17th June, 2011vUNRESTRICTED
TABLE OF CONTENTSTABLE OF CONTENTSMANAGEMENT SUMMARY . IIIPREFACE . VTABLE OF CONTENTS. VITABLE OF FIGURES .VIITABLE OF TABLES .VIII1.INTRODUCTION . 11.1.1.2.1.3.1.4.1.5.1.6.1.7.2.CONTEXT .1PRACTICAL RESEARCH SETTING .2RESEARCH OBJECTIVE.2RESEARCH QUESTIONS .2RESEARCH METHODOLOGY .4RESEARCH APPROACH & DELIVERABLES .6THESIS STRUCTURE .6STATE OF THE ART IN TRADE PROMOTION MANAGEMENT SYSTEMS AREA . 82.1.TRADE PROMOTION MANAGEMENT .82.1.1.Main elements of the TPM process .102.1.2.Key Performance Indicators of Trade Promotions .152.1.3.Importance for Current Research .192.2.DECISION SUPPORT SYSTEMS .192.2.1.DSS for Marketing – Trade Promotion Management .212.2.2.Marketing Management Support System Adoption .222.2.3.TPM Implementation Risks .252.2.4.Importance for Current Research .262.3.BUSINESS INTELLIGENCE TECHNOLOGIES & TOOLS .262.3.1.Business Intelligence - Definition .272.3.2.Business Intelligence Users .282.3.3.BI technologies and architecture components .282.3.4.BI Trends .322.3.5.Importance for Current Research .323.CURRENT SITUATION AND PROBLEM DESCRIPTION AT P&G NL . 333.1.3.2.3.3.3.4.3.5.4.CURRENT TFM PROCESS .33CURRENT TFMS SETUP .33CURRENT BI REPORTING SCENARIO .33P&G PROBLEM DESCRIPTION .33APPLICATION OF LITERATURE REVIEW TO P&G NL .33SOLUTION DESIGN – REFERENCE SYSTEM ARCHITECTURE . 344.1.4.2.4.3.BUSINESS LAYER OF RSA .36APPLICATION LAYER OF RSA .38TECHNOLOGY LAYER OF RSA .39viUNRESTRICTED
Table of Figures4.4.5.CUSTOMIZED P&G REFERENCE SYSTEM ARCHITECTURE.43VALIDATION OF REFERENCE SYSTEM ARCHITECTURE . 445.1.EVALUATION OF THE RESEARCH PROCESS .445.1.1.Guideline 1: Design as an Artifact .445.1.2.Guideline 2: Problem Relevance .455.1.3.Guideline 3: Design Evaluation .455.1.4.Guideline 4: Research Contributions.455.1.5.Guideline 5: Research Rigor .455.1.6.Guideline 6: Design as a Search Process .465.1.7.Guideline 7: Communication of Research .465.2.EVALUATION OF THE RESEARCH PRODUCT.465.2.1.External Validation with EA Experts .475.2.2.Internal Validation inside P&G NL .546.MIGRATION STRATEGY FOR PROCTER & GAMBLE, THE NETHERLANDS . 626.1.6.2.6.3.6.4.7.EVALUATION OF ALTERNATIVE SOLUTIONS .62GAP ANALYSIS – OPTIMA ARCHITECTURE VS PROPOSED ARCHITECTURE (#3).62PROPOSED TFM SYSTEM ARCHITECTURE – BENEFITS .62MIGRATION STRATEGY FOR P&G, THE NETHERLANDS .62CONCLUSIONS . 637.1. ANSWERS TO THE RESEARCH QUESTIONS .637.1.1. State-of-the-art in TPM, TPMS and BI .637.1.2. Solution Design & Validation .647.2.LIMITATIONS .657.3.FURTHER RESEARCH .658.REFERENCES . 67APPENDIX A – ACRONYMS & ABBREVIATIONS. 72APPENDIX B – BUSINESS INTELLIGENCE DEFINITIONS SPECTRUM . 74APPENDIX C – VALIDATION INTERVIEW . 77APPENDIX D – RESULTS OF VALIDATION INTERVIEWS . 85APPENDIX E – SIMPLIFIED RECOMMENDED P&G ARCHITECTURE . 95Table of FiguresFigure 1 - Research Approach . 5Figure 2 - TPM Process as defined in (Accenture, 2008; Gilday, Orler, Zaks, Liu, & Singer, 2005) . 10Figure 3 - TPM Process as defined in (SAP, 2010) . 11Figure 4 - TPM Process as defined in Infosys (Gummaraju, Narayanan, & Anurag, 2006) . 11viiUNRESTRICTED
Table of TablesFigure 5 - TPM Process as defined in Trade Promotion Management Associates ("TPM Best Practices - APath to Foodservice Excellence," 2010) . 12Figure 6 - TPM Process as defined in Synectics Group Inc (SynecticsGroup, 2011) . 12Figure 7 - TPM Process as defined in MEI (MEI-ComputerTechnologyGroup, 2010a) . 13Figure 8 - TPM Process as defined in Clarkston Consulting (MEI-ComputerTechnologyGroup, 2010a) . 13Figure 9 - Reference TPM Process . 14Figure 10 - Structure of DSS (Vercellis, 2009) . 19Figure 11 - DSS Components (Power & Sharda, 2009) . 20Figure 12 - Logical Flow of Problem Solving Process (Vercellis, 2009) . 21Figure 13 - Phases of decision-making process . 21Figure 14 - IS Success Model (W. H. DeLone & McLean, 1992) . 23Figure 15 - MMSS Impact Model (Wierenga, et al., 2008) . 23Figure 16 - BI Conceptual Definition (Chee, et al., 2009; Shollo & Kautz, 2010) . 28Figure 17 - BI Architecture Components (Kimball & Ross, 2002) . 29Figure 18 - Comparison (Inmon, 2002) and (Kimball & Ross, 2002) . 30Figure 19 - CIO Council EA Layers . 34Figure 20 - ArchiMate Framework . 35Figure 21 - Business Layer of TPM RSA . 38Figure 22 - Application Layer of TPM RSA. 38Figure 23 - Technology Layer of TPM RSA . 41Figure 24 – Reference System Architecture for TPM. 42Figure 25 - Design Science Research Guidelines . 44Figure 26 - Design Evaluation Methods . 45Figure 27 - ISA Quality Attributes . 47Figure 28 - External Validation Results . 49Figure 29 - Internal Validation Results . 55Figure 30 - Improved Reference System Architecture . 60Table of TablesTable 1 - Traceability Matrix Chapters/Research Questions . 7Table 2 - TPM System Capabilities from MEI (Trey & van Delden, 2007) . 14Table 3 - KPIs for Trade Promotion . 16Table 4 - Risks of TPM Implementations in Practice . 26Table 5 - External Validation Respondents . 48Table 6 - Definitions of BI as a Process . 74Table 7 - Definitions of BI as Technologies . 74Table 8 - Definitions of BI as a Product . 74Table 9 - Definitions of BI as both Process & Technologies . 75Table 10 - Definitions of BI as both Process & Product . 75Table 11 - Definitions of BI as Product, Process & Technologies . 76viiiUNRESTRICTED
Introduction1. IntroductionThe current chapter provides an overview of the context and motivation behind this research, as well asthe adopted research method and approach. Furthermore, it outlines the subsequent overall structureof the thesis.1.1.ContextCompanies in the Fast Moving Consumer Goods (FMCG) industry spend an estimated 23% of sales onsales promotion and advertising, with 13% of it, equaling about 85billion on trade promotions only(Cui, Raju, & Zhang, 2008). Marketing academics distinguish sales promotions into trade promotions,representing agreements made with retailers, and consumer promotions, as incentives given directly toconsumers (Abraham & Lodish, 1987; Blattberg & Levin, 1987).As the main area of the current research, trade promotions are an integral part of marketing strategies,whereby manufacturers of products or services provide temporary price incentives to their mainintermediaries (such as dealers, distributors and retailers). In response, retailers perform actions whichlead to obtaining the benefits of the specific promotions – special displays, advertising in local shopmagazine, price discounts, free samples, etc. (Abraham & Lodish, 1987; Blattberg & Levin, 1987). Theseefforts are typically referred to as push efforts, since they provide retailers with incentives for offeringthe promoted brand on an agreed-upon price discount (Shimp, 2007). It has been found that during1997-2004, a significant part of an organization’s expenditures was devoted to sales promotions in theUS packaged goods manufacturers: approximately 75% was invested into promotions, and 25% intoadvertising. Moreover, in 2004, 59% of the budget was spent on trade promotions and only 16% onconsumer promotions (Heerde & Neslin, 2008). The motivation behind investing such a high percentageof a company’s net sales into promotions include maximizing the sales of products, introducing newproducts, reducing excess inventories, responding to competition or increasing market share (Abraham& Lodish, 1987; Blattberg & Levin, 1987; Shimp, 2007).First of all, most FMCG companies today have an inefficient process of regularly evaluating theeffectiveness of their past trade promotions, through the use of either in-house Excel tools orcommercial of-the-shelf (COTS) trade promotion management systems (TPMS) (TradeInsight, 2010). Thelatter category includes marketing decision support systems (DSS), which address the dynamic decisionmaking characteristics (such as changing consumer needs, seasonal demands, newproducts/competitors) of promotion planning by providing simulations of win-win promotion calendars(benefiting both the manufacturer and the retailer) and forecasts of profitability by categories (marketsegment, customer, product) (Cui, et al., 2008; Silva-Risso, Bucklin, & Morrison, 1999). While TPMSproviders have published whitepapers on the capabilities of trade promotion management systems theysupport, there is no scientific evidence that their systems reflect the field’s best practices and lead toeffective and optimal TPM processes. Thus, we argue that a reference TPM process inspired by the fieldpractice and based on literature is needed.1
IntroductionMoreover, research in the evaluation of trade promotion effectiveness further substantiates the needand importance of defining measurable criteria – key performance indicators (KPIs) – to be implementedin the TPMS. These KPIs could be used by account managers for measuring the effects of promotions,evaluating them and improving the future promotion productivity (Abraham & Lodish, 1987; Blattberg &Levin, 1987). Manufacturers have problems enforcing their promotions, mainly due to the lack ofappropriate decision support systems tackling the complexity of promotional planning and evaluation.Current trade promotion management systems, despite their in-built analytics capabilities, faceproblems due to a deficient back-end data and process integration (Cui, et al., 2008; Silva-Risso, et al.,1999). Therefore, we argue that there is high potential for long-term promotion effectiveness evaluationthrough the usage of business intelligence (BI) technologies and architecture principles (in the form ofprocess, application and infrastructure components). We believe that setting up the infrastructurelandscape required for efficient BI has the potential of solving the previously mentioned issues, as wellas leveraging the large amounts of unused organizational data. BI systems and their underlyinginfrastructure components improve the timeliness and quality of information needed for decisionmaking, by using various technologies, such as data gathering, data storage and data analysis to evaluatelarge volumes of transactional data and produce sophisticated reports (Negash & Gray, 2008).Therefore, BI systems fit with the identified deficient promotion evaluation, provided that they arecorrectly implemented and integrated with the organizational trade promotion management system.Consequently, the aim of this research is to define a Reference System Architecture integrating BusinessIntelligence Technologies and Trade Promotion Management Systems. To achieve this goal, we focusfirst on defining a trade promotion management process and the corresponding KPIs, inspired fromacademic literature and industrial experience. Secondly, we define a reference system architecturecombining BI technologies with TPMS.1.2.Practical Research SettingCONFIDENTIAL1.3.Research ObjectiveIn the previous sections, we explained the need for a theoretically sound trade promotion managementprocess and the opportunity identified by P&G NL in integrating business intelligence technologies withtrade promotion management systems. The main objective of the current research can thus betranslated into:“To define a Reference System Architecture integrating Business Intelligence Technologies and DecisionSupport Systems with the purpose of achieving efficient trade promotion management.”1.4.Research QuestionsBearing in mind the objective of the current research, the following central research question is elicited:“How can decision support systems and business intelligence technologies be integrated in a referencesystem architecture enabling efficient business reporting for trade promotion management?”2UNRESTRICTED
IntroductionIn order to answer the central research question, a decomposition of the main problem is applied, whichallows for the research and clarification of the several
Synectics, SAP and Trade Promotion Management Associates. Secondly, we defined a reference system architecture combining BI technologies and architecture principles with the TPM solution, following the steps of the TOGAF ADM cycle. We especially focused on the elements having direct or complimentary
