ASGN Goal Seek and Social Media UPDATED Spring24

docx

School

Brigham Young University *

*We aren’t endorsed by this school

Course

310

Subject

Marketing

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by CoachHorsePerson1062

Updated 1/2/2024 ASGN: Goal Seek and Social Media Analytics Part 1: TV Advertising and Goal Seek Definitions CPM – Cost Per Mille: This is the cost per thousand impressions. Mille is Latin for “a thousand”. If you see (000) such as VIEWERS (000) this represents thousands. Engagement: In Social Media Marketing (SMM), Engagement is a way to measure how your audience interacts with your content on social media. This is any interaction including Likes, Comments, Follows, Shares, Click-throughs, and saving/favoriting something. NPS – Net Promoter Score: This is the number of customers that are “Promotors” minus those that are “Detractors”. This is considered an indicator of customer loyalty. We use a relatively simplistic model of positive – negative sentiment. Sentiment Analysis: This analysis looks at the sentiment people have for things. This is used a lot in (SMM) Social Media Marketing to find out if customers are talking about the brand in a Positive, Negative, or neutral tone using NLP (Natural Language Processing). Social Media Marketing – SMM: This is marketing specifically on social media and social networks. Scenario 1 Walt Disney Resorts & Parks (WDRP) has a new advertisement for broadcast TV and social media. As a new marketing intern at WDRP, it is your job to identify the most popular TV shows and social media sites on which to place the ad. You’ve also been asked to understand the historical performance of various shows and genres, as well as perform some basic calculations to determine the cost implications of different placement strategies. Using the data on the "Prime Broadcast TV” tab of the provided data spreadsheet, answer the following questions: Questions Data Orientation For the first two questions, use the “Prime Broadcast TV” tab of the spreadsheet (1) For the following weeks what single TV show should WDRP have advertised on to reach the most viewers? a. 7/30/2018: << AMERICA'S GOT TALENT-TUE>> b. 2/4/2019: << GRAMMY AWARDS>> c. 2/1/2021: << Super Bowl LV>> d. 9/12/2022: << NBC SUNDAY NIGHT FOOTBALL - TPA @ DAL>> e. 7/24/2023: << FIFA WOMEN'S WORLD CUP - USA v NL >>

Updated 1/2/2024 (2) WDRP may be able to negotiate a discount to advertise on ABC stations (which are also owned by Disney). Filter the Prime Broadcast TV list for just ABC shows from the above time frame 9/14/2020 – 7/24/2023. (This should result in just 8 rows ) and list the three ABC shows with the most viewers. << MONDAY NIGHT FOOTBALL - DEN @ SEA, DANCING WITH THE STARS, PRIMETIME EMMY AWARDS>> Goal Seek (3) Prepare to run a goal seek by following these directions:  Remove any Week filters so you have all programming listed from 1/22/2018 – 7/24/2023.  Make sure only ABC shows are selected.  Copy the 19 rows of ABC shows and paste them into the “Goal Seek” worksheet starting in cell B4.  Use an appropriate function to calculate the “Cost to Advertise” for each show in cells H4:H22 using the given “Cost to advertise per Thousand Viewers ”  Use an appropriate function to calculate the “Total Cost to Advertise (no discount)” for all nineteen shows in cell H24. (Hint: first calculate the “Cost to Advertise” for each show based on “Cost to advertise per Thousand Viewers ”)  Use an appropriate function to calculate the “Total Cost to Advertise (with discount)” in cell H26 (Hint: Your formula should reference the discount given in cell J7)  Given yearly advertising budget that averages $140,000 per week , and you need ads to run for 9 weeks, calculate the total budget for the ad buy in cell J11 .  Up to this point you should have put a formula in cells H4:H22, cell H24, cell H26, cell J11.  Take a screenshot of the table F3:H26 and paste it into A below. This lets us see what your total Cost to Advertise is Without a discount and with a discount, but before the goal seek is run. (4) Now that you have calculated the total budget in cell J11, you can use Goal Seek to calculate how big of a discount is needed for WDRP to afford to advertise on all 19 of the top 10 shows for 9 weeks of advertising. (5) NOTE: Take a screenshot of the goal seek pop-up window after you’ve entered your inputs ( but before running the goal seek ), and paste it into part B.

Updated 1/2/2024 (6) Run the goal seek and write the result (the required discount) in part C. Part B should have a screenshot of the Goal Seek Dialogue Box, filled out: a. Screenshot: F3:H26 before Goal seek: << b. >> c. Screenshot: Goal seek dialogue box before it is run: << >> d. Discount needed: <<40.53%>>

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Updated 1/2/2024 Part 2: Social Media Advertising Scenario 2 For these next three questions, use the “SVOD” tab of the spreadsheet Next, assume that WDRP has been approached by several leading streaming services to consider advertising before a stream starts. There are generally three types of streams shown on these services: Acquired shows from networks, Movies, or Original programming we produce. Because WDRP wants to be associated with positively perceived content, you have been asked to look at public sentiment for each of the most popular streaming programs to try and see which ones give us the most potential engagement and positive sentiment. We will use a “ Net Promoter Score ” (NPS) as our metric. NPS is a good KPI for measuring the strength of positivity for something. First you will need to calculate the Positive Sentiment Score then the Negative Sentiment Score, and finally, the Net Promoter Score (NPS). Positive/Negative Sentiment scores You create the positive and negative scores based on a percentage. To create the Positive Sentiment Score, you take the Positive Mentions value (From Column I) and divide it by the sum of the Positive, Neutral, and Negative Mention values (From Columns I to K). (Note that this is different from “Total Engagement” so don’t use that). Do this for the negative sentiment as well, but use Negative Mentions instead of Positive (Column K instead of Column I). Finally, be aware, these scores are on a per-row basis. You do not select any other values than those on the row you are working with. Net Promoter Score To get the Net Promoter Score (NPS), start with positive sentiment percentage and subtract the negative sentiment percentage from it. Next, multiply that number by 100. The final number is your NPS. As an example, if you get a .06 difference, the NPS score is 6. (Multiplying a percentage by 100 turns it into a number value). Questions 1. Insert a PivotChart using the table on SVOD as the data source. Change the chart type to Clustered Columns chart. Add the Original, Acquired, Movie field to the “Legend”. Weekly Rank to the “Axis”, and the Average of NPS to the “values”. (*Note: a Pivot chart and Pivot Table use slightly different labeling A Pivot Chart uses Legend instead of Column and Axis instead of Rows). You will have a screenshot and written answer for A. Only a screenshot for B, and a written answer only for C. We need to see your chart, and your analysis of that chart. 2. Then, answer: A. Screenshot & analyze what features or differences do you notice for each type of show? <<Insert Screenshot>> <<Type in Analysis>> B. Alter the PivotChart so you can answer which program type we should advertise on to maximize our Net Promoter Score and insert a screenshot. (Remove Weekly Rank from the Axis).

Updated 1/2/2024 <<Insert Screenshot>> C. Complete a short analysis of which program type would you recommend (Original, Acquired, or Movie) and why using the screenshots from A and B. <<Type in Analysis>> 3. In the SVOD tab, filter the table to just the top 15 programs by NPS , and report how many Originals, Movies, and Acquired shows there are. 4. How many shows of each type are in the top 15 of NPS? a. Acquired: <<Number>> b. Movies: <<Number>> c. Original: <<Number>> d. Insert Screenshot of the top 15 programs: <<Insert Screenshot>>

ASGN Goal Seek and Social Media UPDATED Spring24

Related Documents