As part of the Data-Driven Decision Making course in university, we were divided into teams to play the Digital MediaPRO simulation game, aimed to recreate the environment of a marketing department for a soda brand. The goal is to maximise market share for the soda brand in three regions (Urban, Rural, Tourism) and amongst the three main demographics (Gen-X, Millennials, Gen-Z).
Below will contain screenshots of my in-depth explanation for each section of the assignment, and how I found a way to "math" the entire simulation, which lead to my group getting the highest final score the course has ever seen for the simulation game. The three main sections in Digital MediaPRO that involved my calculations were with the Content Powerhouse, Media Mix and Media Schedule.
Content Powerhouse (hereforth as CP)
The goal with CP is to gain insights on each demographic and improve our knowledge. The better our knowledge on each demographic, the better the impact of our messages on top of achieving the media objectives. At the start of each "year" of the simulation, we are allocated a budget, hereforth with the units of $million. The group decided that 20% of the total marketing budget should be allocated to research with CP. Then I started the calculations about how to distribute that total budget amongst each insight source.
The yellow-highlighted cells showcase the source of knowledge for each demographic. For example, 76% of our knowledge of Gen-Z comes from Social Listening. Our group wanted to make each insight source as equal as possible (around 33.3% for each). This is where the calculations come in. First I looked into the average percentage for each insight. The average for Creativity would be (10+22+59) = 30. Then I calculated how much from the total insights come from each individual source. As the image shows, Creativity produces 31% of the total insight for these demographics. With the goal of making each source equal, I had to find the inverse percentage of each source, to ensure those with a lower percentage gets a higher "score" and thus more budget allocated towards it. I then repeat the step about finding the percentage of each insight to the inverse total. Afterwards, I multiply that percentage with the overall CP budget. The example shows Creativity needing 35% of the budget, which adds to $2,380 million.
The next step is distributing that $2,380 million across the three demographics for Creativity. I repeated the inverse logic, allocating more resources towards the demographic that has the lowest amount for Creativity. With Gen-Z having a number of 10% for Creativity, the inverse of that is 90%. At last, I calculated the percentage of each demographic to the insight total, i.e. the percentage of Gen-Z + Creativity is (90 / (90+78+41) = 43%). I repeat this for the other eight combinations, and that is how we distributed the budget for each insight and each specific demographic.
Media Mix (hereforth as MM)
The goal with MM is to distribute the remaining budget into marketing in three different regions (Urban, Rural, Tourism) via five different media channels (Traditional, Outdoor, Digital Owned, Digital Paid and Digital Earned). The first step is to note all the media habits for each combination of region, media type, and demographic. From here I calculated the average media use (note that I wrote Gen-X are halved, we decided that Gen-X was not a strong priority so we reduced their influence in the data). Repeating that calculation for each media type per region we can learn that 25% of the Urban region consume Digital Owned media.
Moving onto another metric used to distribute the budget was calculating the return on marketing investment (ROMI) for each media type in detail. Each media type has three items (Traditional has Print, Radio, Television). All the data is imported into the Excel sheet and colour graded, green is the best in the region, red is worse. To illustrate, For Urban (first row), Mobile Apps have the highest return on investment, whilst Sponsored Events are not worth doing at all given a ROMI of 0.00. From there, I calculate the average ROMI for each media type and add them into its own table.
The next step is then to calculate the percentage of each media type within its region, like with the example: How much of Urban ROMI is from Traditional media. I repeat those steps for the other combinations. From there I look at the average between these two variables: the average media habit per media type, and the average ROMI per media type. This will give us the best indicator of which specific media type gives us the most users and ROMI.
As a team, we then decide on which regions to focus on. The image shows we focused on 35% Urban, 35% Rural and 30% Tourism. This leads to more calculations. First we find the budgets for each region, by multiplying the budget with these percentages. Then, as done before, I find the percentage of each media type to the total of the region (i.e. how much of Urban is from Traditional). This concludes with multiplying with the allocated budget for that region.
Now, I've already calculated how much of the budget will go to each region. The next step is figuring out how much should go to each specific media type. Referring back to the ROMI data for each media type, we calculated the percentage from within each media type (in other words, how much from Traditional media is Print). Within these numbers, I decided that types that have a ROMI less than 1.00, I will exclude from the calculations, since this suggests that we do not make money from investing in this and will always result in a loss. That is why, for the case of Public Transport in Urban, its value is 100%, since both Billboards and Sponsored Events have a ROMI less than 1 (0.91 and 0.00 respectively). I repeat these calculations for each combination and conclude with the table on the right, which we input into the Digital MediaPRO website.
Media Schedule (hereforth as MS)
MS is meant to explain when the company's marketing strategies should take place, and how to distribute the budget across a calendar year. There were four settings for this on the website:
Annual, equal distribution across the whole year,
Semesterly, the year is divided into two,
Quarterly, the year is divided into four, or
Monthly, it is possible to edit specifically per month.
There were three aspects that I considered when trying to "math" this section out. Of course, it was important we marketed our product the most when there is the highest number of viewers and consumers for these campaigns so I looked into:
The population of each region, how many people are in the region per month,
Events taking place in that region: events bring in visitors, which increases eyes on our campaigns, and
Sales from the previous year in that region. This is to analyse customer trends: when do they usually buy our product?
And for the last time, I looked into the percentage of each month to the total year, i.e. how much of the total sum of the population came from January. When referring to the image, we can see that the population of the Urban region (first row) remained relatively constant across the year. I repeat these calculations for events and historical sales to get an idea of when people are in this region the most/consuming our product the most. I conclude this with finding the average between these three factors, then using these percentages to add to the website. This step is repeated as well for the Rural and Tourism regions.
That concludes the in-depth explanation for my calculations in the Digital MediaPRO simulation game. I enjoyed finding a way to break down the logic of this game and optimise it in such a way that we got the highest score out of the entire history of the course, never mind within the class during that semester. I hope this has done enough to showcase my ability in using Microsoft Excel and data to make decisions!
Comments