MIS762 Data Warehousing, T2 2014
Assignment 02: OLAP for Customer Intelligence
Marks: 80
Pass Mark: 60
Assignment Type: Individual
Due Date: 11:59PM Sunday 25st September 2014
The assignment introduces you to customer analytics and the decision-support role of data warehousing. It contains an element of self-study to better understand this field followed by the application of OLAP and cube creation for query, reporting and analysis. Module 2 focused on the use of a data warehouse for cube building, OLAP queries and reporting. Your ability to correctly apply these learning outcomes to a business scenario will be assessed.
Background
Demographic analysis (or demographic segmentation), also known as population analysis is frequently used in customer analytics to better understand the current and potential client base. Demographic analysis focuses on factual circumstances of the customer, such as age, gender, income, education, ethnicity etc. Demographics can be segmented into several markets to help an organization target its consumers more accurately. With this type of segmentation, an organization can categorize the needs of consumers and attempt to develop targeted business strategies.
Psychographic analysis (or lifestyle analysis) is another technique in customer analytics where instead of factual information the focus is on interests, activities and opinions of the current and target customers. Psychographics attempts to classify customer sentiments towards products, based on general customer values and beliefs, what products are needed, why they are needed and how they choose what they purchase.
Roy Morgan Values Segments - devised by Michele Levine of Roy Morgan Research and Colin Benjamin of The Horizons Network, are presented as “an innovative system of market segmentation that goes beyond demographics and psychographics to explore the values, mindsets and attitudes that motivate consumer behaviour”. Roy Morgan Research is a Melbourne based market research and consultancy firm. The value segments are publicly accessible from their website - http://www.roymorgan.com/products/values-segments.
Business Case
The CIO of Valeur, a large national supermarket/retail store chain has recently introduced a loyalty card program and keen to gain a more comprehensive understanding of the organisation’s clientele through traditional transactional data as well as the newer and effective demographics and psychographics analysis techniques.
A close friend, the CEO of Vigour has highly recommended you as an accomplished business analyst/consultant for data warehousing and OLAP projects based on the outstanding work completed on the dimensional modelling and design phase of a data warehouse at Vigour.
The CIO of Valeur, impressed by the commendation, has approached you with his interests in customer analytics. The customer relationship management (CRM) team working under his supervision have already put together a summarised data warehouse containing some transaction data and customer demographics. Although the data is accurate and complete, the CIO believes the structure is not. He feels there’s more information that can be captured by the warehouse. He’s very impressed with the power of demographics and psychographics and speaks highly of Roy Morgan value segments as they are local and directly applicable to his client base unlike similar American initiatives.
He has two main business problems in mind. Firstly, improving the current dimensional model/ warehouse to capture all relevant information that applies to demographics, psychographics analysis and Roy Morgan value segments. Secondly, using current data in the warehouse to better understand the customer base by identifying customer segments and potential sales and marketing opportunities. You have agreed to take on this responsibility and deliver a complete report documenting your recommendations to improve the dimensional model and your findings from the current warehouse.
What you are required to do
1. Know the value segments
Read, review and understand Roy Morgan value segments. The website (link given above) is the primary reference but you may use other resources (please cite all resources). Feel free to extend or expand the segments if you feel they lack coverage. Based on your readings and understanding, produce a short review of each segment.
2. Improve the dimensional model
Study the given dimensional model to determine appropriate and relevant attributes for each segment and what’s lacking. Familiarise yourself with demographics and psychographics analysis. Use this knowledge to identify potential improvements to the dimensional model. This can be in the form of new attributes, new dimensions, new measures or the use of dimension design techniques. Present your recommendations with supporting evidence and the new dimensional model.
3. Create cubes and OLAP queries
The data warehouse (ValeurDW) is accessible from the Azure server. Use SSDT to create a multidimensional analysis project. Design and run OLAP queries to determine the actuals. The actuals include customer segments (which segments are present in the client base, which customers belong to each segment, which customers are hybrids or outliers) and buying behaviours and patterns. You may decide the number of queries to run and you are free to use any query interface - MS Excel, SQL reporting, MDX or even the Cube browser.
4. Recommendations for new markets
Taking into account the natural progression of a typical customer from the current segment to the next segment and the potential opportunities exposed in their buying behaviour, recommend selling techniques for each segment, hybrid or outlier. Selling techniques can be simple as cross-sell, up-sell and down-sell or a notch-up sophisticated. Again, you may refer additional resources.
Deliverables
A professionally written report (complete with executive summary, table of contents, references etc.) on the above activities undertaken for Valeur. The report should be compiled as a series of topics of the above mentioned ‘to do’ list.
• The executive summary (max. one page) should provide a complete overview from objectives to outcomes of the activities undertaken.
• The report should be compiled in Microsoft Word, font size 11.
• The report should not exceed 20 pages (including diagrams) + 2 pages (TOC and references). Diagrams, tables and any other visualisation has to be in the main body of the report.
• You are free to make any assumptions with justification. Include these in the report, preferably where applicable.
• Make realistic assumptions on any information (schema or business requirements) that may be missing in the above description.