Marketing analysis – PowerPoint project + 1 brief answer

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

find the cost of your paper