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.