Sunday, September 29, 2019
Questions for the London Jets Case Download the file LondonJets. xls, available on the course website. I. Perform RFM Analysis: (recency, frequency, monetary (total sales) analysis) as indicated on page 3 of the case; steps for RFM are given on page 8 of the case (I have provided one approach on page 3 of this document). Use pivot table with Frequency and Monetary value for rows and Recency for column. Data field should be Count of CustID. Important Note Ã¢â¬â The format for this table is shown on the next page; use this format for your RFM table. Please provide a brief interpretation for the RFM analysis Ã¢â¬â assume you are the consultant to London Jets; how would you explain what the numbers in the pivot table mean (keep the discussion brief; each and every entry need not be explained). II. Additional Analysis: Do two additional sets of analysis beyond RFM. As an example, you may want to analyze attendance patterns for Fan Club Members versus non-members to see if there is any merit in increasing the size of this club (this would count as one analysis). There are several different variables in the database, and as such there are lots of ways in which the data can be analyzed. Think about the issue London Jets is facing Ã¢â¬â how to increase attendance Ã¢â¬â and let this objective drive your analysis. For each additional analysis undertaken by your team, provide a rationale as to why should London Jets consider the customer segment you are analyzing. Please also be sure to briefly explain the outcome of your analysis (what did you learn for your analysis, and how London Jets can benefit from this analysis). What to submit? Please submit one hard copy of the report per team. List the last names of team members on the cover page in alphabetical order. Prepare your report in power point such that it could be presented to London JetsÃ¢â¬â¢ management (although your team will not be asked to present the report). Your report should end with a Conclusions page where you draw conclusions from all the analyses you have done, and recommended actions London Jets can take to increase attendance to its games based on your work. Please make sure that the report is contained within a maximum of 12 pages (slides) including the title and conclusions pages. Team work models I have seen two models of team work. The first and more commonly used model is where the team divides the workload and each member is responsible for completing their share of work. These disparate pieces are put together as a report. The obvious advantage of this approach is that the work is divided evenly among members. The drawback of this approach is that sloppy work by one or more team members adversely impacts the score of those who put in good work. Additionally, if someone in the team does not take the trouble to integrate the disparate pieces, the end result can be a report that appears disjoint (i. . made up of pieces that do not flow well together). The second, less common approach is where each member provides input on every aspect (question) of the case. Compared to the approach described above, the second approach certainly requires more time from each member. The advantage however is that often the best ideas (i. e. responses to case questions) make it into the report, resulting in a higher quality of sub mitted work. Important Note Ã¢â¬â This note applies for all cases done in this class. I cannot entertain questions such as Ã¢â¬Å"Professor, please take a look at what we have so far and let us know if we are on the right trackÃ¢â¬ . Teams are expected to work on each case independently. Taking necessary decisions in situations where there is no clear consensus is a part of team learning process. [pic] Learn more about RFM here: http://searchcrm. techtarget. com/sDefinition/0,,sid11_gci751219,00. html Steps for creating RFM table for London Jets Step 0 Ã¢â¬â Start by creating a column with 3000 entries such that the first 600 entries are 5s, followed by 600 4s, 600 3s, 600 2s and 600 1s. With copy and paste, this takesÃ less thanÃ 2 minutes and I can gladly show you how. Here are the remaining steps for assigning RFM codes to the London Jets. Step 1 Ã¢â¬â Sort the entire data on recency of attendance so that customers that attended the games most recently will be on the top of the list. Add a new column to the data labeled R, where you copy and paste the column created in step 0. Step 2 Ã¢â¬â Sort the entire data, including recency codes assigned in step 1, on the basis of frequency of attendance such that customers with the highest frequency of attendance are at the top of the list. Add a new column to the data labeled F, where you copy and paste the column created in step 0. Step 3 Ã¢â¬â Sort the entire data, including recency codes assigned in step 1 and frequency codes assigned in step 2, on the basis ofÃ monetary spending such that customers spending the most are at the top of the list. Add a new column to the data labeled M, where you assign first 1000 hundred customers aÃ M code of 5, the next 1000 a code of 4, and the last 1000 a code of 3. You are done assigning codes at the end of step 3, and are ready to create the RFM table.