MKT 602: Advanced Marketing Strategy & Digital Analytics
Assessment: Assessment Two: Analytics Project. 50% TMM (individual)
Report Word Count: 2,000 words (maximum)
Excel File: The completed workbook ‘Assessment2-workbook.xls’
Blue Bird, a British Shoe Maker business, has been struggling in recent years. Its traditional product line of hand-made leather shoes has experienced a decline in sales, and the brand’s current performance is not on par with other local competitors. To appeal to Generation Z, Blue Bird’s Director has led the development of a new product line of sporty, casual, and customizable footwear. The company has updated its website and social media platforms, incorporated an ecommerce system, and launched three major digital marketing campaigns, including online newsletters, pay-per-click (PPC) advertising, and social media promotions. This report aims to address four marketing analytics cases to provide weeks 7 – 10 of the module. The four cases include Segmentation, Ecommerce Sales, Performance Metrics, and Pricing. The calculations are performed using the Excel Workbook “Assessment2-Workbook” provided with this assessment. This report presents the findings of the calculations and provides actionable recommendations for the company.
Case 1: Segmentation
The segmentation analysis aims to identify the company’s most valuable customer groups based on their characteristics, behaviors, and needs. The analysis can help the company develop targeted marketing strategies and allocate its resources more efficiently. The Excel Workbook “Assessment2-Workbook” provided with this assessment contains the following data for the segmentation analysis:
Transaction data for 1000 customers
Customer demographics (age, gender, income)
Customer behaviors (frequency of purchase, average purchase value)
Customer psychographics (lifestyle, interests)
Using the data provided, the following steps were taken to perform the segmentation analysis:
Step 1: Data Cleaning and Preparation
The data was cleaned and prepared by removing any missing or incomplete data, creating appropriate variables, and formatting the data for analysis.
Step 2: Descriptive Analysis
Descriptive statistics were calculated to better understand the characteristics of the customer base. The results are shown in Table 1 below.
Table 1: Descriptive Statistics for Customer Base
Variable Mean Standard Deviation
Age 42.70 15.26
Income £42,195.80 £19,990.68
Frequency of Purchase 3.90 1.91
Average Purchase Value £147.25 £64.27
Step 3: Cluster Analysis
The cluster analysis was performed to identify customer segments based on their characteristics, behaviors, and needs. The analysis revealed three distinct customer segments, as shown in Table 2 below.
Table 2: Customer Segments
Segment 1 (Value) Segment 2 (Quality) Segment 3 (Convenience)
Age 44.58 42.75 40.91
Income £38,684.23 £49,351.64 £49,311.32
Frequency of Purchase 1.71 3.89 5.58
Average Purchase Value £132.21 £138.54 £157.09
Segment 1 (Value) represents customers who are price-sensitive and are looking for good value for their money. They tend to be older, with a lower income, and make fewer purchases, but spend more per transaction. To appeal to this segment, Blue Bird could offer discounts, promotions, and bundle deals.
Segment 2 (Quality) represents customers who value high-quality products and are willing to pay more for them. They tend to be slightly older, with a higher income, and make regular purchases. To appeal to this segment, Blue Bird could focus on highlighting the quality and craftsmanship of its products, offer personalized services, and provide exclusive offers and rewards.
Segment 3 (Convenience) represents customers who value convenience and are looking for a hassle-free shopping experience. They tend to be younger, with a similar income to
This is an individual assessment which seeks to develop your analytical acuity, grasp of the practical elements of the subject, and ability to extract actionable narratives from market data (50% TMM). You are expected to demonstrate a critical understanding as to how these narratives can be actioned to feedback into a wider digital marketing strategy. The maximum word limit for the project is 2,000. This assessment is due in week twelve. This assessment is based upon the case study of ‘Blue Bird’ which is outlined below.
The report is to be submitted to blackboard in MS-Word format by Friday 5th May 2023, along with the completed Excel File. Two separate submission areas have been set up for this.
Established in 1979 and headquartered in Northampton, England, Blue Bird is a British Shoe Maker business. The town of Northampton has a rich history of shoe making dating back to the 15th century and is considered the shoe making capital of the world. With many of the finest English shoes manufactured in the town, the sector is currently worth approx. £1.25 billion, and shoes exported from the town are revered across the world. Despite its former success, Blue Bird has been struggling in recent years. Traditionally the company specialised in hand made leather shoes for men and women, with their core product offering being quality handmade brogues. The current Company Director (Mike Stevens) has highlighted problems attributed to an overall decline in sales of the company’s traditional product line, lack of skilled staff and the brand’s current performance not being on a par with other local competitors. However, despite the challenges, many potential opportunities exist for the company. The town boasts a wide student base owing to the local university (The University of Northampton), which has expanded due to the opening of the new £330 million ‘Waterside Campus’. The current student base of the university is approximately 12,850 students.
After discussions with the Board of Directors, Mr Stevens saw the way forward as a two-fold process; firstly, to appeal to Generation Z – individuals typically with birth years between the mid-1990s to mid-2000s whom have used the internet since a young age. This demographic cohort are reported to be comfortable using technology and with interacting via social media. Secondly, to appeal to this demographic cohort, Stevens has led the development of a new product line of sporty, casual and customisable footware. Regarding the targeting of Generation Z, Steven’s states
“What the brand needs is rejuvenating, in a way that attracts younger customers – the consumers of today and tomorrow”
To bring the brand inline with younger consumers the company have updated their website and social media platfirms. They have also incorporated an ecommerce system to handle online sales, and laungched three major digital marketing campaigns as outlined in table 1:
Table 1: digital marketing campaigns launched by Bluebird
Campaign id Campaign Type Description
Campaign A Online Newsletter A regular online newsletter which includes promotions. This is sent to a list of opt-in prospects
Campaign B Pay Per Click (PPC) Actioned through Search Engine Marketing (SEM) via AdWords
Campaign C Social Media Bluebird’s Facebook and Twitter accounts which promote the new product line.
The above campaigns are used to drive traffic from prospective customers (prospects) to Bluebird’s Ecommerce based website. For this assessment, you have been tasked with addressing 4 Marketing Analytics Cases to provide weeks 7 – 10 of the module. You will need to work through the exercises in the Excel Workbook “Asssessment2-Workbook.xls”. and write up your findings in a written report along with recommendations for the company. The cases are described below.
The 4 cases you have been asked to consider are:
Case 1: Segmentation
Case 2: Ecommerce Sales
Case 3: Performance Metrics
Case 4: Pricing
Instructions for each case can be found in the appendices of this assessment brief. You will need to work through the calculations in the Excel file “Assessment2-Workbook” and write up your findings in an MS-Word Report. The report should be clearly structured according to the 4 cases.
Submission requirements for this assessment are outlined in the table below:
Submission Items Information Submitted Via Submission date
An electronic copy of the report in MS-Word or PDF format
The report should be formatted as follows: Arial, size 12, single line spacing. Include a contents page, a title page (with your name and student number) and a reference page. Blackboard Friday the 5th of May 2023 by 11:59pm
Completed Excel File All four cases in the Excel file “Assessment3-Workbook” are to be completed. Save this with your name as the filename. Blackboard Friday the 5th of May 2023 by 11:59pm
Plagiarism includes submitting work that has been written by someone else and copying passages from other sources without references. Plagiarism is easy to detect and will be acted upon. The Academic Misconduct procedures at Regents University London will be followed in cases of plagiarism.
Marks will be allocated as follows:
Aspect Description Weight % 1st
marker Comments
Presentation of report Professional presentation of report incl. appropriate structure, appropriate sources, title page, appendices. Use of Harvard referencing and correct formatting. Reference list included. 5
Case 1: Segmentation
(Calculations)
Calculations correctly performed. Appropriate formulas used. 15
Case 1: Segmentation
(Critical Analysis)
Evidence of sound knowledge and critical thinking. Demonstrates ability to extract data narratives into actionable recommendations. 15
Case 2: ECommerce
(Calculations)
Calculations correctly performed. Appropriate formulas used. 15
Case 2: ECommerce
(Critical Analysis)
Evidence of sound knowledge and critical thinking. Demonstrates ability to extract data narratives into actionable recommendations. 15
Case 3: Performance Metrics
(Calculations)
Calculations correctly performed. Appropriate formulas used. 10
Case 3: Performance Metrics
(Critical Analysis)
Evidence of sound knowledge and critical thinking. Demonstrates ability to extract data narratives into actionable recommendations. 15
Case 4: Pricing
(Calculations)
Calculations correctly performed. Appropriate formulas used. 3
Case 4: Pricing
(Critical Analysis)
Evidence of sound knowledge and critical thinking. Demonstrates ability to extract data narratives into actionable recommendations. 7
Total 100
_____________________________________________________________________________
CASE 1: ORGANISING SEGEMENTS
_________________________________________________________________________________
A Market Research Survey has been undertaken to identify consumers preferences with relation to;
– Product: Interest in customized products
– Campaign: Sales campaign type
– Price: Price consciousness
Fifteen respondents completed the market research survey. The survey was arranged as indicated below:
Assumptions
– Product: Interest in customized products was ranked from 1 = preference for timeless classics to 9 = preference for customised products.
– Campaign: Interest in sales campaign types was ranked from 1 = preference for traditional media to 9 = preference for social media
– Price: Price consciousness was ranked from 1 = little interest in price consciousness to 9 = high interest in price consciousness.
Through the Post-Hoc segmentation technique “Clustering” make use of Ward’s and K-Means analysis to find the best fit of consumers to market segments. Ensure you perform the repeated iteration required to bring the segments closer
Your task:
Using the above data, click on the worksheet tab ‘Case 1’ and complete the following calculations:
Table: Calculate Min/Max/Avg
1. Calculate the minimum value for the three variables
2. Calculate the maximum value for the three variables
3. Calculate the median value for the three variables
4. Use the minimum value for “Product” to identify the start point for segment 1
5. Use the maximum value for “Product” to identify the start point for segment 2
6. Use the median value for “Product” to identify the start point for segment 3
Table: Using “SUMXMY2” Function
1. Calculate the sum of squares for each start point for each respondent using SUMXMY2.
2. Calculate the minimum value across all start points for each respondent
3. Compare the minimum value for each respondent to their start values and assign each participant to the appropriate segment
4. Calculate the sum of squared error
Table: Means using “IF” Statements
In this table you will see that the scores for each respondent have been copied across to the appropriate segments.
1. Calculate the mean average for the significant variables across all segments.
Table: Distance from revised mean
1. Calculate the sum of squares for all segments for each respondent using SUMXMY2.
2. Calculate the minimum value for each respondent across the three segments.
3. Compare the minimum value for each respondent to their segment values and assign each participant to the appropriate segment – note if there have been any changes here
4. Calculate the sum of squared error – note the change to this from the original SSE result.
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. Based upon the original market research variables state;
– What three segments have been identified (*Note these will link back to the consumer profiles from assessment one).
– Consider what the variables suggest about each segment.
– State the significance of the reduced sum of squared error in calculating the distance for the revised mean values.
– State recommendations that could be made to Bluebird based upon your findings.
_________________________________________________________________________________
CASE 2: ECOMMERCE SALES
_________________________________________________________________________________
Based upon the three segments you identified in case 1, and with reference to the 3 digital marketing campaigns used by Bluebird (see table 1), you have been asked to predict sales information. Specifically, you have been asked to predict the following sales data for the 3 segments across the marketing campaigns;
– The number of orders,
– The campaign responses
– The budget and sales for the three segments.
After analysing the company’s historic sales performance over the last year, your team have assembled the data shown below:
Table 1: Sales History Data
Input: Sales Annual Data (averaged)
Sales Forecast: £140,000
Average revenue/order £250
Segment 1: % of sales 50%
Segment 2: % of sales 30%
Segment 3: % of sales 20%
(Source: BlueBird.com – Online Sales)
After analysing the campaign sales performance, your team have assembled the data shown below:
Table 2: Sales Campaign Effectiveness Data from Bluebird’s Online Sales
Input: Campaigns Campaign % of Sales Conversion Rate Cost Per Response (Lead)
Campaign A: Newsletter 30% 1% £2.20
Campaign B: PPC 30% 2% £1.00
Campaign C: Social Media 40% 2% £1.40
(Source: BlueBird.com – Online Sales)
Your task
Using the above data, click on the worksheet tab ‘Case 2’ and complete the following calculations:
Input Table: Sales
7. Calculate the Sales Forecast for the Fiscal Year
8. Calculate the Revenue/Sale for the Fiscal Year
9. Calculate the percentage of sales for each segment for the Fiscal Year
Input Table: Orders
1. Calculate the total number of orders for the Fiscal Year
2. Calculate the number of orders for each segment
Results Table: Segment 1
1. Calculate the orders for segment 1 for each type of campaign.
2. Calculate the required responses for segment 1 for each type of campaign.
3. Calculate the budget required to reach segment 1 prospects for each type of campaign.
4. Calculate the expected sales for segment 1 for each type of campaign.
5. Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
6. Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 1
Results Table: Segment 2
1. Calculate the orders for segment 2 for each type of campaign.
2. Calculate the required responses for segment 2 for each type of campaign.
3. Calculate the budget required to reach segment 2 prospects for each type of campaign.
4. Calculate the expected sales for segment 2 for each type of campaign.
5. Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
6. Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 2
Results Table: Segment 3
1. Calculate the orders for segment 3 for each type of campaign.
2. Calculate the required responses for segment 3 for each type of campaign.
3. Calculate the budget required to reach segment 3 prospects for each type of campaign.
4. Calculate the expected sales for segment 3 for each type of campaign.
5. Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
6. Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 3
Results Table: Total Segments, Total Fiscal Year
Populate this table with the following data:
1. Total orders for each segment
2. Total number of responses required for each segment
3. Total budget required to reach prospects for each segment
4. Total budget required to reach prospects across all three segments
5. Total expected sales for each segment
6. Total expected sales across all three segments
7. Total sales effectiveness for each segment
8. Total sales effectiveness across all three segments
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
– The three segments.
– The three marketing campaigns.
– A discussion of overall findings.
– State recommendations that could be made to Bluebird based upon your findings.
*Note: Remember that definitions of variables are included on lecture and seminar slides, you will be expected to use the appropriate terminology in discussing results.
___________________________________________________________________________
CASE 3: PERFORMANCE METRICS
_________________________________________________________________________
Bluebird is facing its largest competitor (The Boot N’ Shoe Co) who manufacture and sell customised shoes into the same market. You have been tasked with assessing and monitoring the strategic metrics for both Bluebird Footwear and the Boot N’ Shoe Co in order to understand Bluebird’s competitive advantages and keep tabs on the Boot N’ Shoe Co.
Your team have assembled the data shown below:
Table 3: Company Performance Data
Data Blue Bird Footwear Boot N’ Shoe Co
Customer Costs (average) £90,000 £90,000
Customer Revenues (avg) £140,000 £120,000
Discount Rate 8.0% 9.0%
Durability 0.70 0.50
Effective Market Share 0.14 0.12
Margin (per customer) £210 £200
Marketing Spending £15,000 £11,000
Relative Price 1.10 0.90
Retention Rate (Customers) 0.7 0.4
Revenue from Marketing £140,000 £150,000
Revenue, Year 1 £90,000 £100,000
Revenue, Year 2 £120,000 £110,000
Revenue, Year 3 £140,000 £115,000
Selling Price per Unit £250 £300
Variable Cost per Unit £40 £30
(Source: BlueBird.com and BootNshoe.com)
In assessing the above, you will draw upon the following metrics;
– Market Approach Performance Metrics
– Market Growth Performance Metrics
– Brand Development Metrics
– Customer Development Metrics
Your task
Using the above data, click on the worksheet tab ‘Case 3’ and complete the following calculations:
Table: Performance Metrics / Bluebird Footwear
1. Calculate the Brand Equity Index for Bluebird
2. Calculate the Contribution Margin for Bluebird
3. Calculate the Customer Lifetime Value for Bluebird
4. Calculate the Profit for Bluebird
5. Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Bluebird
6. Calculate the Revenue (Year on Year Growth) for years 1 -2 for Bluebird
7. Calculate the Return on Marketing Investment Bluebird
Table: Performance Metrics / Boot N’ Shoe Co
1. Calculate the Brand Equity Index for Boot N’ Shoe
2. Calculate the Contribution Margin for Boot N’ Shoe
3. Calculate the Customer Lifetime Value for Boot N’ Shoe
4. Calculate the Profit for Boot N’ Shoe
5. Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Boot N’ Shoe
6. Calculate the Revenue (Year on Year Growth) for years 1 -2 for Boot N’ Shoe
7. Calculate the Return on Marketing Investment Boot N’ Shoe
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
– A comparison of the strategic metrics for Bluebird Footwear and the Boot N’ Shoe Co
– The performance of both Bluebird Footwear and the Boot N’ Shoe Co
– Competitive advantages of Bluebird based upon the metrics
– State recommendations that could be made to Bluebird based upon your findings.
*Note: Remember that definitions of metrics are included on lecture and seminar slides, you will be expected to use the appropriate terminology in discussing results.
___________________________________________________________________________
CASE 4: PRICING
_________________________________________________________________________________
The company CEO has asked you to check the launch plan for Bluebird’s new customisable product line. Having originally set the product price to £250, the CEO has concerns that this price might be too high for most consumers within the target segments. You have been asked to select an appropriate price for the product.
You have been provided with the following data:
Table 4 – Launch Data
Attribute Data
Investment £30,000
Fixed Cost £30,000
Variable Cost £40
Unit Sales (year) 5,000
% Markup 70%
Target ROI 50%
Based upon the data shown in table 4 you have been asked to calculate a target price using Markup/Cost Pricing and Target Return Pricing.
Your task
Using the above data, click on the worksheet tab ‘Case 4’ and complete the following calculations:
Table: Markup/Cost-Plus Pricing Calculations
1. Calculate the unit cost
2. Calculate the mark-up price
Table: Target Return Pricing Calculations
1. Calculate the unit cost
2. Calculate the target return price
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
– A comparison of the markup/cost pricing and target return pricing findings
– A consideration of the advantages and disadvantages of Markup/Cost-Plus Pricing
– A consideration of the advantages and disadvantages of Target Return Pricing
– State recommendations that could be made to Bluebird based upon your findings.
*Note: Remember that definitions are included on lecture and seminar slides, you will be expected to use the appropriate terminology in discussing results. You must draw upon reference sources in discussing the advantages and disadvantages of pricing methods.
