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 (40% TMM). You are expected to demonstrate a critical understanding as to how these narratives can be actioned to feedback into Bluebird’s wider digital marketing strategy. The maximum word limit for the project is 3,000. This assessment is due in week twenty-four of study. This assessment extends upon the case study used in assessments one and two in that you will “test” the effectiveness of the devised marketing strategy for Bluebird using marketing analytics.
Please read the case study below. The report is to be submitted to blackboard in MS-Word format by Friday 30th April 2021, along with the completed Excel File. Two separate submission areas have been set up for this.
As the Digital Marketing Manager, you presented a Digital Marketing Strategy to the board (Assessment One) and proposed a Digital Marketing Campaign (Assessment Two). Based on your Digital Marketing Strategy and Digital Marketing Campaign, Bluebird successfully undertook the following:
Launched their new product line of customizable footwear
Updated their website
Updated their social media platforms
Incorporated an Ecommerce system to handle online sales
Launched 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 recommendations to the company. The Cases extend upon weekly topics covered within term two of the module. You will need to work through the exercises in the Excel Workbook “Asssessment3-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 “Assessment3-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 30th of April 2021 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 30th of April 2021 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
Calculate the minimum value for the three variables
Calculate the maximum value for the three variables
Calculate the median value for the three variables
Use the minimum value for “Product” to identify the start point for segment 1
Use the maximum value for “Product” to identify the start point for segment 2
Use the median value for “Product” to identify the start point for segment 3
Table: Using “SUMXMY2” Function
Calculate the sum of squares for each start point for each respondent using SUMXMY2.
Calculate the minimum value across all start points for each respondent
Compare the minimum value for each respondent to their start values and assign each participant to the appropriate segment
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.
Calculate the mean average for the significant variables across all segments.
Table: Distance from revised mean
Calculate the sum of squares for all segments for each respondent using SUMXMY2.
Calculate the minimum value for each respondent across the three segments.
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
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
Calculate the Sales Forecast for the Fiscal Year
Calculate the Revenue/Sale for the Fiscal Year
Calculate the percentage of sales for each segment for the Fiscal Year
Input Table: Orders
Calculate the total number of orders for the Fiscal Year
Calculate the number of orders for each segment
Results Table: Segment 1
Calculate the orders for segment 1 for each type of campaign.
Calculate the required responses for segment 1 for each type of campaign.
Calculate the budget required to reach segment 1 prospects for each type of campaign.
Calculate the expected sales for segment 1 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 1
Results Table: Segment 2
Calculate the orders for segment 2 for each type of campaign.
Calculate the required responses for segment 2 for each type of campaign.
Calculate the budget required to reach segment 2 prospects for each type of campaign.
Calculate the expected sales for segment 2 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 2
Results Table: Segment 3
Calculate the orders for segment 3 for each type of campaign.
Calculate the required responses for segment 3 for each type of campaign.
Calculate the budget required to reach segment 3 prospects for each type of campaign.
Calculate the expected sales for segment 3 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
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:
Total orders for each segment
Total number of responses required for each segment
Total budget required to reach prospects for each segment
Total budget required to reach prospects across all three segments
Total expected sales for each segment
Total expected sales across all three segments
Total sales effectiveness for each segment
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
Calculate the Brand Equity Index for Bluebird
Calculate the Contribution Margin for Bluebird
Calculate the Customer Lifetime Value for Bluebird
Calculate the Profit for Bluebird
Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Bluebird
Calculate the Revenue (Year on Year Growth) for years 1 -2 for Bluebird
Calculate the Return on Marketing Investment Bluebird
Table: Performance Metrics / Boot N’ Shoe Co
Calculate the Brand Equity Index for Boot N’ Shoe
Calculate the Contribution Margin for Boot N’ Shoe
Calculate the Customer Lifetime Value for Boot N’ Shoe
Calculate the Profit for Boot N’ Shoe
Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Boot N’ Shoe
Calculate the Revenue (Year on Year Growth) for years 1 -2 for Boot N’ Shoe
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
Calculate the unit cost
Calculate the mark-up price
Table: Target Return Pricing Calculations
Calculate the unit cost
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.
