Col Solare + assignment questions

Originally Written by Simha Mummalaneni, Adapted for Excel by Jonathan Zhang Col Solare: Free Samples in the B2B Wine Industry Col Solare is a winery based near Red Mountain, in eastern Washington’s Columbia Valley. The company is an equal partnership between two popular and well-respected wineries in Washington state and in Tuscany: Chateau Ste. Michelle and Marchesi Antinori, respectively. At the time of founding, Col Solare’s stated goal was to produce fine wines that unite illustrious Italian wine making know-how with the dense and luscious fruits that reflect Washington terroir. Col Solare’s first vintage was produced in 1995, and their wines have been highly rated by all of the four major publications: Wine Advocate, Wine Enthusiast, Wine & Spirits, and Wine Spectator. High-end wineries like Col Solare typically have significant marketing budgets, and the marketing employees are typically assigned to smaller groups that each focused on a specific issue. For instance, Col Solare has smaller sub-groups focused on branding, retail support, promotional activity, B2B sales, and design & packaging. The marketing groups at wineries have traditionally not been data-centric, but as in other industries, this was starting to change. Col Solare had recently created a small Marketing Analytics group and had given the group a broad mandate: their goal was to use data insights to improve marketing across the organization. Robert Parker had recently been promoted to the role of Chief Analytics Officer at Col Solare, and in that role, he managed the new Marketing Analytics group. This was Parker’s first job in the wine industry, but he had a wealth of relevant knowledge: he had spent the previous 17 years working in B2B marketing analytics, and he was also a wine enthusiast and collector. Parker had chosen to focus on B2B issues as his first project as Chief Analytics Officer for two reasons: (1) his background in B2B marketing analytics meant that he was already up-to-speed on the relevant issues, and (2) there was a perception within executives at Col Solare that the B2B side of the company had not been receiving enough attention over the past few years, which meant that he had a significant opportunity to improve things. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. Some elements of this case have been disguised or fictionalized for pedagogical reasons and/or to assure confidentiality.

C OL S OLARE The B2B Wine Industry Wineries make money both from selling wine to individual consumers and selling wine to businesses. The B2C side is relatively straightforward: most wine sales occur in retail stores (grocery stores, liquor stores, specialty wine shops; etc.), but there is also a small share of sales that are direct-to-consumer (monthly wine clubs, direct shipments from wineries; etc. Col Solare is best known for its Cabernet Sauvignon and Red Blend wines. Over the years, the company has also introduced other varietals including Malbec, Syrah, and Cabernet Franc, all under the Col Solare brand name. All of these Col Solare branded wines are typically sold in retail stores for about $85 per bottle. Separately, the winery also produces a cheaper Red Blend under its “Shining Hill” sister brand, and those wines are sold for $40. On the B2B side, wineries sell bulk orders of wine to institutional customers. Typically, these B2B customers are restaurants and bars, but wineries also sell their products to hotels, airlines, and other companies in the entertainment & hospitality industries. Col Solare sells to B2B customers through two channels: both directly and indirectly. Direct sales are those in which a restaurant/bar directly purchased from the winery, typically by speaking with a salesperson from Col Solare and placing an order. Indirect sales are those in which a restaurant/bar purchased from a distributor that sells Col Solare wines in addition to other wine and alcohol products from different manufacturers. Col Solare typically sells wine to B2B customers by the case, with each case containing 12 bottles. Roughly one-quarter of Col Solare’s B2B sales are direct, and the remaining three-quarters are indirect. Both of these channels are profitable for Col Solare, although direct sales are preferable for two reasons: (1) Col Solare earns a higher margin for each unit sold because there is no need to share money with a distributor, and (2) Col Solare can keep track of which products its direct customers are buying, which opens up possibilities for direct marketing tactics. B2B customers who buy directly from Col Solare are entered into a customer database, and all their purchases are logged. As a result, marketing managers and salespeople at Col Solare can contact these customers to inform them about new products and to make recommendations for new wines they might like. Another direct marketing tactic used by Col Solare is to send out free samples: the winery sends a restaurant/bar a free bottle of wine so that the owners and staff members can try it for themselves before deciding whether to purchase a case. Many restaurants and bars find it hard to keep up with details of new wine releases and to make decisions about which new wines might be worth trying out. As a result, wineries have difficulties when trying to properly inform customers about their newest releases. In recent years, Col Solare had found that sending free samples was a helpful way of mitigating this problem. Many of Col Solare’s B2B customers reported that they much more likely to buy a case of wine if they could first try the product and decide whether it was a good fit for their establishment. The main downside of sending out free samples is the cost associated with doing so. Col Solare is a high-end winery, and each of their bottles typically costs about $10 to produce. In addition, Col Solare also incurs a shipping cost of roughly $20 per bottle, or $40 per case. Wine bottles need to be packaged carefully to avoid breakage, and they are typically shipped overnight in order to reduce the possibility of being affected by adverse weather conditions during transit. 2

C OL S OLARE Exhibit 1: Dataset Description Contents of “ColSolare.csv,” which contains records for 5000 customers. Variable Name Variable Description customer_id Unique ID number for each customer customer_type Whether the customer’s establishment is a restaurant or bar state State where the customer is located (abbreviated) zip ZIP code for the customer (5 digits) zip3 First 3 digits of the customer’s ZIP code customer_sqft Size of the customer’s establishment (measured in square feet) first_purch Number of months since the customer’s first purchase from Col Solare last_purch Number of months since the customer’s most recent purchase from Col Solare cases Total number of wine cases that the customer has purchased directly from Col Solare dollars Total dollars that the customer has spent with Col Solare cab_franc Total number of Cabernet Franc cases that the customer has purchased from Col Solare cab_sauvignon Total number of Cabernet Sauvignon cases that the customer has purchased from Col Solare malbec Total number of Malbec cases that the customer has purchased from Col Solare merlot Total number of Merlot cases that the customer has purchased from Col Solare red_blend Total number of Red Blend cases that the customer has purchased from Col Solare syrah Total number of Syrah cases that the customer has purchased from Col Solare buyer Did the customer buy the 2016 Red Blend after receiving the free sample? (1 = yes, 0 = no) 4

C OL S OLARE Discussion Questions (part 1): Understanding Customers in the B2B Wine Industry Your Task (Can be completed using basic Excel functions, data analysis tookpak, and Excel pivot table) Put yourself in the shoes of Robert Parker, the new Chief Analytics Officer at Col Solare. Your immediate task is to analyze the data from the Col Solare test campaign, in an attempt to understand how Col Solare’s customers behave. The test campaign contains 5000 customers (restaurants and bars) which were chosen at random from the Col Solare customer database. Because these 5000 customers were chosen at random, we can assume that customer patterns we observe with the test campaign data should generally also hold true in the larger database as well. Your assignment is to answer the following 10 questions. 1. What percentage of Col Solare customers are restaurants? 2. Which four states account for the largest percentage of Col Solare’s customers? 3. What is the average total dollars spent, the average total number of cases purchased, and the average number of months since last purchase? 4. Create a new variable called avg_price that represents (for each customer) the average price-per-case for the wine that they have purchased. Calculate the minimum, the maximum, and the average of this new variable. 5. Which types of wine (Cabernet Franc, Malbec, Syrah; etc.) have the highest sales? Which have the lowest sales? 6. How many restaurants are in the data, and how many bars are in the data? What percentage of restaurants bought the 2016 Red Blend? What percentage of bars bought the 2016 Red Blend? 7. How many cases of wine have been purchased by restaurants and how many cases of wine have been purchased by bars? What is the average number of cases purchased for restaurants and what is the average number of cases purchased for bars? 8. Create a new variable that represents (for each customer) the gap in months between their first purchase and their most recent purchase. Calculate the minimum, the maximum, and the average of this new variable. 5

C OL S OLARE Col Solare (part 2): Targeting Free Samples in the B2B Wine Industry (The logistics regression part can be done using ChatGPT4’s Advanced Data Analysis) Upon taking over as the new Chief Analytics Officer at Col Solare, Robert Parker had decided to evaluate whether it made sense for the company to send free samples of their wine to restaurants and bars that were in their customer database. Many of Col Solare’s B2B customers had previously reported that these free samples were a very helpful way for them to discover new offerings, and that they had decided to buy wines in the past only after receiving and trying the free sample that Col Solare had sent them. Despite the positive feedback from customers, Parker was concerned because he knew that sending out these free samples was very expensive. In order to examine the profitability of this approach, Parker and his Marketing Analytics group had carried out a test campaign consisting of 5000 restaurants and bars that had been randomly chosen from the customer database. Each of these 5000 customers received a free bottle of Col Solare’s new 2016 Red Blend. One week later, they were contacted by a salesperson from Col Solare who asked if they wanted to purchase a case of the 2016 Red Blend. Parker’s suspicion was that sending free samples to all B2B customers was not the ideal approach — there were too many customers that were unlikely to be interested in any particular bottle of wine, so Col Solare was wasting money by sending them a free sample. Parker was eager to evaluate the effectiveness of more sophisticated approaches that can include details about the customer. In particular, Parker asked his team to use logistic regression (also known as a logit model) in order to understand which factors affected customer response, and to develop a targeting rule that can guide future decisions regarding which customers should get free samples. Part 2.1: Logistic Regression 11. Estimate a logistic regression using “buyer” as the dependent variable and the following as predictor variables: last_purch dollars customer_type customer_sqft cab_franc cab_sauvignon malbec merlot red_blend syrah 7 Technical Note : The cases variable is excluded from the set of predictor variables — including it will lead to perfect collinearity because cases (the total number of cases purchased) is equal to the sum of the number of cases purchases in each of the 6 wine categories. By including the number of purchases in each category, there is no need to include the total number of

C OL S OLARE Hint: to do this, first transform the “customer type” variable into a 0/1 dummy variable: The next step is to run the logistic regression and view the regression output: Finally, create a new variable that contains the predicted probability of purchase for each customer. Calculated : Please check excel - Logistic regression output tab. SUMMARY OUTPUT Regression Statistics Multiple R 0.411932537 R Square 0.169688415 Adjusted R Square 0.168024131 Standard Error 0.326440822 Observations 5000 ANOVA df SS MS F ignificance F Regression 10 108.6509 10.86509 101.9587731 4.8E-193 Residual 4989 531.6459 0.106564 Total 4999 640.2968 Coefficients tandard Erro t Stat P-value Lower 95%Upper 95% ower 95.0% Upper 95.0% Intercept 0.212108424 0.026448 8.019781 1.30935E-15 0.160258 0.263958 0.160258 0.263958438 customer_encode -0.093216415 0.010135 -9.19749 5.26716E-20 -0.11309 -0.07335 -0.11309 -0.0733474 customer_sqft 2.77017E-06 7.65E-06 0.362017 0.71735488 -1.2E-05 1.78E-05 -1.2E-05 1.77716E-05 last_purch -0.00777369 0.000573 -13.5554 3.9093E-41 -0.0089 -0.00665 -0.0089 -0.006649425 dollars 0.000139023 0.000114 1.224136 0.22095863 -8.4E-05 0.000362 -8.4E-05 0.000361668 cab_franc -0.110313507 0.082204 -1.34195 0.17967389 -0.27147 0.050842 -0.27147 0.050842476 cab_sauvignon -0.025155387 0.081979 -0.30685 0.758970158 -0.18587 0.13556 -0.18587 0.13556019 malbec -0.155664978 0.082028 -1.8977 0.057792831 -0.31648 0.005146 -0.31648 0.005146125 merlot -0.115615733 0.081961 -1.41061 0.15842144 -0.2763 0.045065 -0.2763 0.045064655 red_blend 0.037920659 0.081914 0.46293 0.64343481 -0.12267 0.198509 -0.12267 0.198509038 syrah -0.05951739 0.081975 -0.72604 0.467848871 -0.22023 0.101191 -0.22023 0.101190554 Note: Through correlation analysis, it is found that dollars is co-related to merlot and other varieties in various degrees, the highest being 0.71. But it is not omitted as the predictor variables were given as a part of instructions. Also computed % Probability – Check Predicted Probability tab in excel Example : customer_id customer_type customer_encode customer_sqft last_purch dollars cab_franc cab_sauvignon malbec merlot red_blend syrah buyer Logit Probability %Probability 1 restaurant 1 4538 35 3790 1 0 1 2 0 1 0 -0.170444782 0.457491665 45.75% 2 bar 0 3128 27 7215 1 1 1 3 2 2 0 0.027438302 0.506859145 50.69% 3 bar 0 2954 31 5757 2 1 2 1 2 0 0 -0.02932763 0.492668618 49.27% 4 restaurant 1 2731 29 6492 0 2 1 2 3 1 0 0.200715461 0.55001108 55.00% 5 restaurant 1 2939 25 7808 1 3 1 5 1 0 0 -0.075386104 0.481162394 48.12% 6 restaurant 1 3308 31 5108 0 0 1 4 0 2 0 -0.462381782 0.386420951 38.64% 7 bar 0 3347 27 6539 1 0 1 1 3 3 1 0.236916307 0.558953582 55.90% 12. Create and interpret the odds ratios for each of the predictor variables. Summarize and interpret the results (so that a marketing manager can understand them). Which variables are significant? Which seem to be “important”? 8

C OL S OLARE Computed in “Decile Distribution” tab. Sample below Logit Probability %Probability Decile 0.911610849 0.713329679 71.33% 10 0.840467801 0.698563731 69.86% 10 0.820277628 0.69429527 69.43% 10 0.819368209 0.694102212 69.41% 10 0.788579003 0.687526134 68.75% 10 0.774628629 0.684521307 68.45% 10 0.750940339 0.67938356 67.94% 10 0.74742455 0.678617263 67.86% 10 0.737007567 0.676341147 67.63% 10 0.736994397 0.676338264 67.63% 10 0.728039204 0.674374842 67.44% 10 0.727682535 0.674296515 67.43% 10 Note. Sorted, hence sample shows all 10 Excel formula used =PERCENTILE.INC($O$2:$O$5001,S19) =MATCH(O7,$T$18:$T$28,2) 14. Create a bar chart that plots response rate by decile (as just defined above). Hint: the “response rate” is not the same as the “predicted probability of purchase.” Instead, it is the percentage of customers in a given group (for example, a particular decile) that chose to buy a case of the 2016 Red Blend. It is the average of predicted probability of purchase for each decile. Response rate computation by Decile is shown below. Row Labels Sum of buyer Average of %Probability Count of Probability % Response Rate 1 4 0.481430724 500 0.80% 2 15 0.501545536 500 3.00% 3 28 0.511709433 500 5.60% 4 31 0.521259539 500 6.20% 5 48 0.530364255 500 9.60% 6 59 0.539130628 500 11.80% 7 76 0.547886775 500 15.20% 8 88 0.558127731 500 17.60% 9 143 0.573089646 500 28.60% 10 262 0.609305804 500 52.40% (blank) Grand Total 754 0.537385007 5000 Bar chart showing response rate 10

C OL S OLARE 0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 0 50 100 150 200 250 300 1 2 3 4 5 6 7 8 9 10 Response Rate vs Decile Buyers in the group Group Average Probability % Response Rate 15. Similarly, to visualize the same results differently, generate a Table showing the number of customers in each decile, the response rate in each decile, and the number of customers in each decile who chose to buy a case of the 2016 Red Blend ( Hint: the number of buyers = number of total customer in each decile x response rate of each decile ) Decile # Buyers in the group Group Average Probability % Response Rate 1 4 0.481430724 0.80% 2 15 0.501545536 3.00% 3 28 0.511709433 5.60% 4 31 0.521259539 6.20% 5 48 0.530364255 9.60% 6 59 0.539130628 11.80% 7 76 0.547886775 15.20% 8 88 0.558127731 17.60% 9 143 0.573089646 28.60% 10 262 0.609305804 0.524 16. For the 5000 customers in the dataset, estimate a logistic regression model where you predict response only based on the “merlot” variable. Why is the odds ratio for “merlot” different than in the logistic regression in Part 1? Please be specific and provide an intuitive explanation beyond simply stating the statistical problem. Merlot only model is computed in - Merlot Only Part 1: Simple Odds Calculation and Pivot Table In Part 1, where odds were calculated by simple division and pivot table, it's likely that no other variables were considered in the analysis. The odds ratio for "merlot" in this context would represent the change in odds of the response (buying wine) associated with a one-unit increase in "merlot," without considering other factors. Part 2: Logistic Regression Model with Merlot Only 11

C OL S OLARE Solare earned $720 in revenue. However, it also incurred a shipping cost of $40 and the cost of goods sold was $120. 17. How high does the customer’s predicted probability of response need to be in order for them to be a profitable target? 18. Assume that Col Solare ships free samples only to their target customers; i.e., those whose predicted probability of buying is greater than the breakeven rate. a. Of the remaining 20,000 customers in the database, how many buyers would you expect to get? b. What would be the expected response rate? c. What is the expected profit (in dollars)? d. What is the expected return on marketing expenditures? Part 2.4: Planning For Next Year The Marketing Analytics group at Col Solare was excited about their results and was trying to plan for the company’s next product launch: a Merlot (using 2017 grapes) that was scheduled to be released in Spring 2021. However, there was a disagreement between team members: some argued that the results from the current test campaign could also be used to decide which customers should get a free bottle of the 2017 Merlot, so Col Solare didn’t need to run another test. Meanwhile, others argued that the company would have to run a new test next year in order to figure out whom to target. 19. First, provide a yes/no answer to this question: does Col Solare need to run another test campaign next year, or do they not need to do so? Then, explain your reasoning and defend your answer. Part 2.5: Predicting Total Spending Another manager at Col Solare wants to use this dataset for a different purpose: to understand what factors can explain how much a customer spends over time. Specifically, she would like to understand how the overall spending for each customer (the “dollars” variable) can be explained by the following variables:  The number of months since the customer’s first purchase (the “first_purch” variable)  The number of Malbec cases they have purchased (the “malbec” variable)  The number of Merlot cases they have purchased (the “merlot” variable)  The number of Syrah cases they have purchased (the “syrah” variable) 13

C OL S OLARE 20. What kind of statistical method would be best for addressing this question, and why? Conduct the appropriate analysis, show the results, and explain how each of the above 4 variables affects total spending. Method : Here's how you can set up and interpret the multiple logistic regression model for new manager. Steps: 1. Regression Analysis:  Ensure that your dataset contains the necessary variables ("dollars," "first_purch," "malbec," "merlot," "syrah").  Use Excel toolpak to perform a multiple linear regression analysis. 2. Interpretation:  Examine the coefficients of the independent variables to understand their impact on the dependent variable ("dollars").  Check the p-values associated with each coefficient to assess their statistical significance.  Evaluate the overall fit of the model using metrics like R-squared. All calculations are in – “New Model Q 20” tab Regression output below SUMMARY OUTPUT Regression Statistics Multiple R 0.912595 R Square 0.83283 Adjusted R 0.832696 Standard E 806.5713 Observatio 5000 ANOVA df SS MS F ignificance F Regression 4 1.62E+10 4.05E+09 6221.179 0 Residual 4995 3.25E+09 650557.3 Total 4999 1.94E+10 Coefficients tandard Erro t Stat P-value Lower 95%Upper 95% ower 95.0% Upper 95.0% Intercept 113.2311 19.47433 5.814379 6.46E-09 75.05289 151.4094 75.05289 151.4094 first_purch 39.46761 0.850764 46.39079 0 37.79974 41.13548 37.79974 41.13548 malbec 675.8412 16.43267 41.12789 0 643.6259 708.0564 643.6259 708.0564 merlot 678.3266 12.33051 55.01204 0 654.1534 702.4999 654.1534 702.4999 syrah 701.6075 20.25239 34.64319 7.1E-236 661.9039 741.3111 661.9039 741.3111 14