After submitting my Power BI report on LinkedIn as part of the Maven Super Bowl Challenge, I was messaged by a few people asking if I could put together an article or video blog on how I created it. So, here I am!!

Rather than jumping onto the snapshots of the Power BI platform and going through the process of putting the visuals together, I thought I would take a few steps back, and go through the plan and strategy I now go through before attempting these types of challenges, as without that you can often create a confused message. I pick up new lessons with every challenge, and find it an excellent form of self-development.

My Key Steps in Planning and Strategizing

For this particular challenge, I broke my process down into the following steps, but they are equally applicable to other work I do:

1. Read the Brief
2. Consider the End User
3. Summarise Scope and Objectives
4. Review the Raw Data
5. Consider available metrics
6. Develop a story or flow
7. Sketch your layout and structure
8. Execute, Review and Polish

Step 1 – Read the Brief

The challenge noted the following:

We’ve just added a brand new data set to the Data Playground, containing data from Super Bowl commercials for 10 popular brands this century

and

For this challenge, you’ll be assuming the role of Marketing Analyst at Maven Motors, an up-and-coming US car manufacturer looking to make a splash in the market. They have approved the budget to run a TV spot during the 2022 Super Bowl, but need you to analyze historical data to help guide the creative direction.

Your task is to recommend a data-driven strategy for the Maven Motors Super Bowl spot, and present it in the form of a single page report or dashboard.

They are asking you for two things, namely to “analyze the historical data to help guide the creative direction…” and to “….recommend a data-driven strategy for the Maven Motors Super Bowl spot.”

So, we need to keep these two points in the forefront of our mind as we progress.

Step 2 – Consider the End User

Perhaps the most important step and question to ask yourself – who am I creating this report for and how do I want them to feel when they look at it? This will dictate the style, format and presentation.

For example, the Harry Potter challenge or the Jordan vs LeBron challenge would encourage the use of colours, graphics and specialised visuals to engage the general viewer and add an element of fun and excitement.

However, as this challenge is aimed at the decision makers within a large corporation, the style and format should be very different. For this, if I imagined being in their shoes, I would like to see the following type of summary report or dashboard:

• Structured – easy to read and flows well
• Professional – the product should align with the standards and values of the company
• Related to the brief – no need for superfluous information that distracts or wastes time, something I don’t have a lot of!
• Comprehensive but to the point – only pick and present the key findings
• Able to invite a decision – use the data and recommendations to enable an easy decision to be made

Step 3 – Summarise Scope and Objectives

Following Project Management 101 techniques, I then documented my scope and objectives. This may seem like a duplication of the above, but is helpful as a reference to always come back to. For this reason, I usually write this out and have it sitting to the side to glance at as I work on the final solution:

In case you can’t read my writing:

Scope

• Provide a 1 Page Report
• Analyze Historical Data
• Provide Recommendations for a data-driven strategy

Objectives

• Enable board members, who are “time-poor” people, to have sufficiently succinct information in order to make a key decision on the direction of the marketing strategy
• Be clear, concise and professional
• Enable board members to be confident in your analysis (i.e. structure, methodology, analysis all appear credible, therefore we can put a measure of trust in the recommendations).

Step 4 – Review the Raw Data

It’s only now that I actually come to open the data files and look to see what is in there.

I found that, having performed the above steps, when I started looking at the data I was immediately considering whether this particular type of information or column was relevant to my brief or objectives.

I will try and briefly go through the my main thoughts when reviewing the data (below snapshot for 2000 and 2001), and how it related back to the scope, and what I would look at for more detailed analysis.

I would note at this point that this usually takes a few days, as I often like to let it ferment a little while I sip on a flat white or two.

Data Not Considered Relevant

Here’s point number one – NOT ALL DATA WILL BE RELEVANT.

Therefore, it was important to put aside any items that are not particularly useful in relation to the scope of works. Looking at the above table, these are highlighted in light blue.

Individual Brands

You may argue that the brand is important, but I would argue otherwise.

What’s the difference between Coke and Pepsi, or Bud and Bud Light in the context of this data? I don’t think this data set is going to tell you especially with the individual sample sizes.

What may give insight is the type of brand (food, cars, alcohol, etc. which I will address later). So for me, I took the decision to ignore individual brands.

Ad Links

The links themselves have no data analysis value for me here. Their only use may be to look at the actual ads, or provide a link in your dashboard or report. Therefore, I ignored these.

Estimated Cost

You may think- Wowwww! How can you ignore cost??? Well, I am not totally ignoring cost, but the total cost provides little insight, as there is the variable of ad length to consider.

The longer the ad, the higher the cost, right?

So, I checked if there is a correlation with a quick cost/time calculation. Guess what? The $ cost per second is constant for each year. Therefore I ignored the total cost, and focussed on the $ cost per second instead.

YouTube Likes

Again, you may wonder why I might be interested in YouTube views, but not likes? Well, this is my personal insight. I watch a lot of YouTube, and I enjoy a lot of what I watch, but I rarely feel the need to click “like” – I would be more likely to subscribe. I heard that YouTube itself does not place too much importance on “likes” these days too, they are more likely to track views, duration, click through, etc.

Additionally, the number of views is not underestimating the number of people who have engaged with the video (even if they only watched for a few seconds), but the number of likes will actually underestimate the number of people who actually like the video, as it is not compulsory to click the like button if you liked it! Again, I knocked that one off my analysis list.

TV Viewers

This may or may not be another surprise to some. If you take the time to look at the TV views for any given year, you will see they are pretty much constant – see below ( I recall there was one outlier). You can read into this that the money you pay to advertise during the Super Bowl is giving you the direct access to everyone that is watching.

As it is constant, the duration, brand, type, etc. are not directly impacting the number of viewers. I would only take this into account if you had a live feed of figures while the ads were being watched.

So, you guessed it, I kicked TV Viewers to the kerb!

Data Considered Relevant

OK, ok….. so I have pretty much now ignored a large part of the dataset. So what exactly am I actually interested in, and what information relevant to the scope do I think I can get out of it? Well, let’s see now….

Year

An easy one – we want to potentially see how our variables change over time, so we will definitely be keeping Year

Brand Category

With ten brands, its a little difficult to compare. However if we can reduce this to a smaller set of categories, it may be of benefit. In the back of my mind, I was thinking that as Maven Motors is a motor company, it might be useful to look at the performance of Kia, Hyundai and Toyota as a group, rather than as individual companies as it effectively provides a larger single sample size.

Following that, it made sense to group the others into Alcohol, Snacks and Drinks, Sports and Services, and see if there were any trends among those categories.

By the way, I created a YouTube video showing you how to create those categories.

Video Characteristics

It was difficult to glean any real insight into the 7 categories without more detailed analysis. Further, it is part of the brief to influence the “creative direction” of the advertisement. Therefore, it was a no-brainer to retain this data.

Again, I created YouTube video actually showing you how to “unpivot” these characteristics for more effective analysis.

Length

Further, I saw that there were varying lengths of ads, and there may be a potential trend over time which may allow me to impart some recommendation on what the current trend is – so that was kept in too.

YouTube Views

I retained this as one of my key data points. As Maven Motors were already gaining access to the TV network through having a large marketing budget, I believed that a true test of whether an ad was “creatively successful” (viral), was whether people then took the time to seek it out on YouTube to watch again, or felt the need to share it with friends, family and their social networks.

$/s

As discussed earlier, I saw that the $/s for advertisements was constant for each year, which makes sense. I kept and used this data point as I would be able to see the trend in cost over time and potentially give further assurance to the board on likely final expenditure (even though budgets were already approved).

Number of Ads per Brand per Year

One further data point I noticed was that some brands were having more than one ad per year. The below example shows Budweiser using 13 ads over 4 Super Bowl events. Again, this is something I thought would be interesting to look at further to see if there were additional recommendations I could make on the potential number of ads to run.

Step5 – Consider Available Metrics

Considering all of the above, I came up with the following list of data points or metrics I wished to examine and present and make data-driven recommendations on:

The number of ads in a campaign year – What was the average number of ads per year per brand category.

Ad Duration – what is considered a standard ad length, and what is the trend over time telling us we should do this year.

$/second – what is the historical trend in ad cost, and see if we can forecast the range for the coming year.

Average YouTube Views vs Ad Characteristics – analyze the each characteristic and see which is more likely to garner additional views.

Step 6 and 7 – Develop a Story or Flow and Sketch Out

The next steps were to capture the entire process above is a single structured sheet that would form my final report. My story needed to:

• Set the scene (provide context),
• Lay out my methodology,
• Establish the scope of the data analysed
• Present the key data and insights
• Give a summary recommendation

Again, I find it useful to sketch this out on a piece of paper (see below), along with even some potential visuals I have in my head.

I note that for this report I included a significant amount of text. This was on purpose as one of my key objectives was to enable the the board members to have confidence in my analysis. I felt that if I laid out the context, methodology and recommendations in clear concise language, that is then further backed up by data, this will provide that level of confidence required in such situations.

Step 8 – Execute, Review and Polish

Once the above it set, it is now a matter of prepping and cleaning any data (categorization, etc.), establishing the measures, then building the visuals into the structure I had envisaged.

One thing I like about Tableau is creating visuals on separate pages, and then constructing a final dashboard. I sometimes replicate that feel in Power BI, where I will trial and build individual visuals on separate tabs before bringing them in. Below are a few samples.

This gets the concepts down, so I know the type of data I want to show and in what general format.

I will experiment with how things are presented and how well the visual can communicate the point I want to make.

Once I am comfortable with the concept, I will look to “polish” it. What does this mean to me?

Polishing Steps

As I said, once I am happy with the story, flow, structure and type of visuals I am using, I will move on to “polish”, which for me usually involves the following:

• Selecting an appropriate colour theme/palette and applying consistent colours for headings, text and visuals. There are lots of websites that help generate themes and palettes.
• Apply consistent fonts and font sizes
• Remove superfluous information:
  • axis titles (if already obvious)
  • axis values (if using data labels)
  • repeated legends, which can be done with use of consistent colouring
  • gridlines, axis lines, etc. unless really needed
  • give concise visual titles
  • any other item that is not conveying
• Align titles, text boxes, visuals (the eagle eyed will note a few of my visuals are off centre!)
• Provide consistent spacings

Applying these steps allows me to move from the above visuals to the below final versions, which I believe met my goal of giving clean, concise and professional looking visuals.

If you have made it to the end of this, congratulations!! I apologise that my blogs are maybe not as short and to the point as my dashboard, but I hope this provided some insight into the process I took in developing my challenge submission, and maybe provided you with some tips and tricks for the next one.

Let me know what you think – would you change anything, or do you have a good alternative approach? I would be interested to hear.

Also, If you would like to see a video version of this and maybe some exploration of actually creating the above visuals, leave a comment below, or drop me a message on LinkedIn.