Marketing analysis – PowerPoint project + 1 brief answer
Company: Cooper’s Hawk Winery & Restaurants/https://www.coopershawkwinery.com/
Thorough company research and analyze files included to finish the 2 parts below
This powerpoint project builds on what we have noticed about Cooper’s Hawk customers and market segments with the customer database, and customer perceptions based on
the survey results.
(Files included for references)
The powerpoint project consists of two parts.
1. In the first part you should evaluate Cooper’s Hawk’s presence in digital and social media environments. These include the company web site, Facebook page, Twitter
feeds, mobile activities, etc. Your evaluation should address the quality of the content in these and other digital environments. Note that the purpose is not to be
entirely exhaustive in detecting every piece of digital marketing content as much as to provide an assessment of their presence in digital media with respect to the
quality of the content, its relevance to CH wine club customers, and its integration into the overall brand and marketing efforts.
2. The second part should provide top three ideas and recommendations for how Cooper’s Hawk can improve its presence in digital and social media. The idea is to
provide strategically significant improvements that are relevant to customers, and consistent with the CH nationwide expansion strategy and other marketing
communications practices.
For 1 & 2 your submission should be a PPT file (no more than 10 slides).
After completing 1 & 2 for powerpoints, please give a brief answer for 3
3 – brief answers please
Cooper’s Hawk management believes there are significant opportunities to increase the financial performance of the restaurant and the wine club through marketing
communications directed at wine club members. This question builds on the references provided, after researching them please provide :
• Using the market segmentation analysis – references provide, select two or three target markets. Identify the opportunities to increased customer spending
among the target markets. Give brief reasons of why you select those 2 or 3 markets
Reference 1 is posed below,
Reference 2, 3 & 4 are attached
Reference 1:
reference 2
Segmentation: Wine Club Level & Restaurant Frequency
Cooper’s Hawk distinguishes itself as a restaurant that pairs upscale casual dining with a winery experience. Diners can choose to join the Cooper’s Hawk Wine Club
to learn more about wine, expose themselves to new types of wine, and enhance their relationship with the Cooper’s Hawk brand. Cooper’s Hawk leverage the wine club
service as a tool to give their customers more incentives to come back and dine in their restaurants. Therefore, we believe that the wine club level and their
frequency of restaurant visits are two influential variables.
Our data proves that wine club members tend to be more frequent visitors to the restaurants, and spend more on dining services. Furthermore, we found there is a weak
positive correlation (.319) between the members’ wine club level and their frequency of restaurant visits, which adds value to the interpretation of data.
We did a fixed basis segmentation on customers using wine club level and restaurant frequency with the timeframe of September 21, 2014 to September 20, 2016.
● Segment 1 HLHF: Wine club level =2 and frequency>21.66 Segment 2 HLLF: Wine club level =2 and frequency<=21.66
Segment 3 LLHF: Wine club level =1 and frequency>12.26
Segment 4 LLLF: Wine club level =1 and frequency<=12.26
We find segment 1 and 3 to be the most interesting segments. Segment 1, which is 12.8% of our total customers, is the smallest group yet has the second highest
spending. At 25.6% of our total sum, they are our premium customers who not only participate as the premium level 2 club members, but also visit and spend most heavily
in our restaurant. Segment 3, which has 25.2% of our total customers are the second largest group but actually has the highest total % of sum – 31.0%, which shows that
they spend and visit frequently. However, they kept themselves in our lower-end–or the level 1 wine club–which is our highest potential group; we wish to give them
more incentive to take the next step to upgrade to our premium level 2 wine club.
Segment Profiles: “Winers & Diners” and “Foodies”
Segment 1 “Winers & Diners”
This segment comprises of people who are at Wine Club Level 2, and who have a restuarant frequency of over 21.66 (they have visited the restaurant over 22 weeks in
the past two years).
Statistics observed for this segment are:
● 54.1% variety and 36.9% red club type
● Maximum spending in restaurant is on server (almost 37.8% of total revenue)
● The most popular time of year and venue(server) is Q4
● Average restaurant spending is $63
● Average of weeks they have been in wine club is 418
● Average of weeks ago their most recent visit at the restaurant is 3.4
What’s interesting about this segment is that they are very small in population, yet contribute to almost a quarter to of revenue. This segment actively drives
revenue from wine club as well as restaurant spending, hence is definitely very profitable.
Segment 3 “Foodies”
This segment comprises of people who are at Wine Club Level 1, and who have a restuarant frequency of over 12.26 (they have visited the restaurant over 12 weeks in
the past two years).
Statistics observed for this segment are:
● 50% variety and 34.8% red wine club type
● Maximum spending in restaurant is on server (almost 39.8% of total revenue)
● The most popular time of year and venue(server) is Q4
● Average restaurant spending is $67
● Average of weeks they have been in wine club is 409
● Average of weeks ago their most recent visit at the restaurant is 5.8
What we find particularly interesting about this segment is that while they are at wine club level 1, they contribute to almost 31% of revenue with 25% of total
customers. They might be people who are either more enthusiastic about food and eating out than wine, or ones who save on wine subscription and therefore feel
justified in spending more on food at the restaurant. Since their restaurant frequency is high, we believe they have the potential to be enticed into moving up a wine
club level, by special offers, promotions, or deals.
In addition to segment 1 and 3, we also noticed interesting trend on segment 2, (“Wine Enthusiasts”), comprising of customers who are at wine club level 2,
but low restaurant frequency. This could suggest that they might only be the wine enthusiasts who joined the wine club to pick up wines, but not really restaurant
enthusiasts. It is worth of conducting more research on them in the future to see if there is any insightful consumer insight.
Appendix:
Table 1 (Segmentation and total revenue)
Table 2 (Segmentation and wine club type)
Segmentation Frequenc y Percen
t
1.0
0 Vali d Red 202 36.9
Sweet 13 2.4
Variet y 296 54.1
White 36 6.6
Total 547 100.0
3.0
0 Vali d Red 374 34.8
Sweet 57 5.3
Variet y 537 50.0
White 106 9.9
Total 1074 100.0
Table 3 (Correlation)
Table 4 (Descriptive statistics)
Descriptive Statistics
Segmentation N Minimum Maximum Sum Mean
1.0
0 AverageRestSpendin g 547 19.42 457.13 34470.78 63.0179
WineclubRev 547 767.76 863.76 471228.72 861.4785
Amount_sum 547 473.76 13433.43 1252574.56 2289.8986
how many weeks they have been in wine club 547 270.00 572.00 228955.00 418.5649
how many times did they visited restaurants in the past two years 547 22 175 20085 36.72
how many weeks ago was their most recent visit at the restaurant 547 .00 39.00 1875.00 3.4278
TotalRevenue 547 1337.52 14297.19 1723803.28 3151.3771
Valid N (listwise) 547
3.0
0 AverageRestSpendin g 107
4 10.92 473.70 72885.08 67.8632
WineclubRev 107
4 407.76 455.76 486750.24 453.2125
Amount_sum 107
4 219.64 9332.00 1602984.93 1492.5372
how many weeks they have been in wine club 107
4 268.00 572.00 440055.00 409.7346
how many times did they visited 107
4 13 104 24165 22.50
restaurants in the past two years
how many weeks ago was their most recent visit at the restaurant 107
4 .00 40.00 6265.00 5.8333
TotalRevenue 107
4 675.40 9787.76 2089735.17 1945.7497
Valid N (listwise) 107
4
Table 5 (Segment 1 venue)
Table 6 (Segment 3 venue)
Table 7 (Segment 1 venue and quarter)
Table 8 (Segment 3 venue and quarter)
reference 3 – Cluster Analysis
Overview
We analyzed data file “Customer Level Merged_RFM” with a time frame of September 21, 2014 to September 20, 2016. The file has a total of 4,265 customers, and a total
of 65,124 transactions. We picked three variables to run the cluster analysis: “Average Restaurant Spending,” “Times visiting the restaurant,” and “Weeks ago of their
most recent visit at the restaurant.”
Reasons
1. Two Main Income Streams for the Company
Copper’s Hawk’s revenue comes from two main sources: wine club subscription fee (level 1 and level 2) and restaurant revenue. Occasion-based wine revenue is included
in the restaurant revenue. Restaurant revenue contributes a higher percentage to the company’s total revenue than the wine club revenue does. The wine club is a key
driver of restaurants traffic, and makes the company unique in the restaurant industry. We analyzed wine club level 1 and 2 customers respectively. The assumption here
is that customers’ consumption behaviors, specifically in the restaurant, are different based on which wine club level they are in. Therefore, the three variables we
picked are all about the consumption behavior in the restaurant (the average spending, frequency, and active level).
2. ANOVA Table and Correlations
In the original data, there are seven variables that we can pick from: wine club revenue, restaurant revenue, average restaurant spending, weeks they have been in
wine club, times visiting restaurants in the past two years, weeks ago of their most recent visit at the restaurant, and the total revenue. All said variables have
been proved to have significant differences between wine club members at level 1 and level 2 except the “weeks they have been in wine club (length of relationship).”
In our case, it is true that it does not matter whether or not there is a significant difference between the two wine club levels since we analyzed them separately.
However, we still consider this as a potential indicator that “length of relationship” might not be a good separator to segment our customers. Besides, we want to be
consistent about our initial assumption that different wine club levels customers have different consumption behaviors at restaurants. Specifically, in the future,
besides wine club level, we can use “length of relationship” as an additional layer to look at the clusters but not use it to run cluster analysis since we want to
pick variables that show behaviors in restaurants. Therefore, we first dropped the “length of relationship” from our consideration list. Next, we removed the “wine
club revenue” because it shows the similar direction of wine club level. For the correlations, we see that “restaurant revenue,” “frequency,” and “total revenue” are
highly correlated. We picked frequency to represent those variables. We consider the combination of “average restaurant spending” and “frequency” is stronger than the
combination of “average restaurant spending” and the “restaurant revenue.” Since wine club is considered as a tool to create a better performance of the overall
restaurant business, we believe that not only the monetary value but also the times that customers have visited are important.
Cluster Analysis for Wine Club Level 1 Customers
We created four clusters using the said three variables. All data has been logged and standardized. Level 1 has a total of 2,901 customers. Cluster 1 has 18.5% of
customers, cluster 2 has 32.3% customers, cluster 3 has 28.5% customers, and cluster 4 has 20.6% customers. In the chart below, the size of circles shows the numbers
of customers in the cluster. On one hand, cluster 2 and cluster 3 visit restaurants more frequently compared to the average. Those two clusters are also more active
than others. Between the two, cluster 3 has a higher average restaurant spending. On the other hand, cluster 1 has the bottom performance with low frequency to
restaurants, low active level, and the lowest average restaurant spending. Interestingly, although cluster 4 also has low frequency and low active level, they
surprisingly have a higher average restaurant spending. When we looked at the compared means, the outcome proved our observation as well. Cluster 3, the second largest
group, is the most profitable segment, which contributes the most to the total revenue (47.7%) while cluster 1 contributes the least (18.5%).
Cluster Analysis for Wine Club Level 2 Customers
Instead of creating four clusters, we created five clusters with the level 2 group since it segments customers in a more meaningful way. Similarly, all data has been
logged and standardized. Level 2 has a total of 1,364 customers. Cluster 1 has 21.1% of customers, cluster 2 has 29.3% customers, cluster 3 has 16.3% customers,
cluster 4 has 5% customers, and cluster 5 has 28.3% customers. As we can see in the chart below, cluster 5 visits restaurants the most and they are the most active
even though it has an average restaurant spending at an average level comparing to others. Cluster 1 performs averagely in terms of frequency and active level but has
the highest average restaurant spending. Although cluster 2 seems to do great in all three variables, it also contains the largest customers. Similarly, cluster 4
customers have the bottom performances, which they have the lowest frequency, lowest active level, and lowest average restaurant spending. However, again, cluster 4
has the smallest size of customers. Therefore, those two groups don’t give us many insights. We further analyzed the relationship between those five clusters and the
total revenue, and the output also provided similar result. Cluster 2 (29.3% customers) and cluster 5 (28.3% customers) only has a 1% difference in numbers of
customers, however, cluster 5 contributes 39.4% to the total revenue while cluster 2 only contributes 21.2%, which is an 18% difference. Cluster 5 is our most
profitable segments.
Appendix
Table 1 ANOVA table
Table 2 Correlations table
Table 3 Wine Club Level 1 Cluster Analysis
Table 4 Four Clusters and Total revenue for Wine Club Level 1
Table 5 Wine Club Level 2 Cluster Analysis
Table 6 Five Clusters and Total revenue for Wine Club Level 2
reference 5
All three regression models use a sample size of 4,265 and used the multiple regression enter method on SPSS. We modified 2 and 3 with logs to lessen impact of
outliers. The purpose of performing this regression is to test whether or not there is a relationship between the marketing approaches, email and promo, and the total
revenue.
1. Regression Model 1 – Total Revenue = f(Email, Promo)
Total revenue is the dependent variable and email and promo (1 is with promo and 0 is without promo) are the independent variables. From the ANOVA table, the F-
statistic is 1116.676 and the corresponding p-value is less than .05. This implies that the model is significant such that at least one of the predictors has
significant effect on the total revenue. The null hypothesis here is that the coefficient of determination is zero. Our result suggested that we can reject the null
hypothesis and conclude that the model accounts for significantly more variance in the criterion variable than would be by chance. The coefficient of determination is
0.344, which implies that the model can explain about 34.4% of total revenue variance.
The email variable is significant because the p-value is smaller than .05. However, the p-value for promo (.553) is greater than the common alpha level of 0.05,
which indicates that it is not statistically significant. The regression equation is: Total revenue = 276.924 + (17.746 * Promo) + (146.295 * Email). The intercept is
the expected mean value of total revenue, which is $276.924 with no email and no promotion. For every one email send out, we expect a 146.295 dollar increase in total
revenue, holding all other independent variables constant. The coefficient for promo is 17.746, which indicates that you can expect with promotion the total revenue
will increase by an average of $17.746. Lastly, since all the VIFs are less than 5, there are no collinearity issue for all the variables.
2. Regression Model 2 – Ln(TotalRevenue) = f(Email, Promo)
The log of the total revenue is the dependent variable and email and promo (1 is with promo and 0 is without promo) are the independent variables. From the ANOVA
table, the F-statistic is 1892.03 and the corresponding p-value is less than .05. This implies that the model is significant such that at least one of the predictors
has significant effect on the total revenue. The null hypothesis here is that the coefficient of determination is zero. Our result suggested that we can reject the
null hypothesis and conclude that the model accounts for significantly more variance in the criterion variable than would be by chance. The coefficient of
determination is 0.4703, which implies that the model can explain about 47% of total revenue variance. The email variable is significant because the p-value is
significant. However, the p-value for promo (0.932) is greater than the common alpha level of 0.05, which indicates that it is not statistically significant.
The regression equation is: Log total revenue’ = 6.406 + (0.001*Promo) + (0.088*Email). For email, since this is just an ordinary least squares regression, we can
easily interpret a regression coefficient, .088, as the expected change in log of total revenue with respect to a one-unit increase in email holding all other
variables at any fixed value, assuming that email enters the model only as a main effect. However, if we want to know what happens to the outcome variable total
revenue itself for an additional email, the natural way to do it is to interpret the exponentiated regression coefficients. First of all, let’s start with the
intercept, 6.4, is the unconditional expected mean of log of total revenue. Therefore the exponentiated value is exp(6.406) = 605.466. This is the geometric mean of
total revenue with no promotion and no email. OLS regression of the log transformed outcome variable is to estimate the expected geometric mean of the original
variable. For email, we can say that for an additional email send out, we expect to see about 9.1% of increase in the geometric mean of the total revenue, since exp
(.088) = 1.091. For promo, in terms of percent change, we can say that switching from no promo to promo, we expect to see about 0.1% increase in the geometric mean of
total revenue (exp (0.001)=1.001). Lastly, since all the VIFs are less than 5, there are no collinearity issue for all the variables.
3. Regression Model 3 – Ln(TotalRevenue) = f(log(Email), Promo)
Log (Total revenue) is the dependent variable and the independent variables are the log of email communications sent, and the promo dummy variable (1 is with promo
and 0 is without promo). Following the ANOVA test, we got an F-stat of 1454.514 and significance of corresponding p value<.05. Thus, we reject the null hypothesis H0:
that R²=0 and decide to use the model, concluding that at least one the predictor variables affect the total revenue function. R squared, or the coefficient of
determination is 0.406, which indicates that the model accounts for about 40.6% of total revenue variance. We ran an independent sample t test (assuming null
hypothesis H0: beta=0 where email /promo are unrelated to total revenue function), and saw that that email demonstrates significance (as p value of 0.00 is less that
5%) but for promo, the p value is greater than 5%, which is not significant.
The equation of the regression model is: Log total revenue’ = 6.1+ (0.007*Promo) + (0.541*log Email). The intercept, 6.1, is the unconditional expected mean of log
of total revenue. Therefore the exponentiated value is exp(6.1) =445.85, which is the geometric mean of total revenue. For promo variable that is not log transformed,
its exponentiated coefficient is the ratio of the geometric mean for the total revenue with promo to the geometric mean for total revenue without promo. For example,
in our sample, we can say that the expected percent increase in geometric mean from with promo to without promo group is about 0.7% holding other variables constant,
since exp(0.007) = 1.007. For every one log(email) sent out, we expect a 0.541 log dollar increase in total revenue, holding all other independent variables constant.
However, an easier way to interpret the effect of email is shown in the equation above, where we take two values of email, m1 and m2, and hold the other predictor
variables at any fixed value. (βemail is the coefficient of the log of email variable)
log(total revenue)(m2) – log(total revenue)(m1) = βemail*(log(m2) – log(m1))
It can be simplified to log(total revenue(m2)/total revenue(m1)) = βemail*(log(m2/m1)), leading to:
Total revenue(m2)/total revenue(m1) = (m2/m1)^βemail.
This tells us that as long as the ratio of the two email values, m2/m1 stays the same, the expected ratio of the outcome variable, total revenue, stays the same. For
example, we can say that for any 10% increase in email, the expected ratio of the two geometric means for total revenue will be 1.10^βemail = 1.10^0.541=1.0529. In
other words, we expect about 5% increase in total revenue when email increases by 10%. Lastly, since all the VIFs are less than 5, there are no collinearity issue for
all the variables.
4. Conclusion
The equation with the highest coefficient of determination is equation 2, with an R squared of .4703. Thus this equation best demonstrates the impact of the two
marketing approaches on the total revenue.
Appendix
1. Regression Model 1
2. Regression Model 2
3. Regression Model 3