You've been asked to get data ready for a big modeling exercise, but what format do you need it in? What does clean data look like?

You've been asked to clean the data ready for a big model for a new client. But what does it mean to 'clean' data anyway?

Data Cleaning
Hey,
I know this is your first data cleaning task, so let me explain.
Our goal is to build a single file with all the data in tabular format.
That means one column per variable with one row per date.
Let me know how you get on!
Thanks,

Clean data refers to data that's in the right format for modeling. The format might differ depending on the model, but for marketing mix modeling in particular you need it in tabular format. This means one row per date (it could be daily, weekly, or monthly data) and one column per variable (for example one column per channel or campaign). There should be no gaps in the data (we need zeros if there are) and all of the data from different sources should be joined up together in one sheet. In order to get the data in the right format you might need to transpose and/or unstack the data, as well as cleaning outliers or missing values. The final result is called an ABT (Analytical Base Table), the dataset in its final form, ready for modeling.

Data Cleaning

Magazine

Data scientists spend 60% of their time on cleaning and organizing data.

You should do data cleaning as a separate task, in a separate file, and export the final dataset with the name 'clean' or '_abt' (Analytical Base Table). This lets you come back to the model in the future and use the exact same data as you did before, as well as letting others replicate your work.

Clean data, clear mind

First we need to collect our media spend, and get it all in one place, categorized by campaign in tabular format.

The first big job is to get all of your spend data in one place with revenue. To do this we need to unstack the data then categorize our marketing campaigns.

Data is 'stacked' when some of the values you want as columns are listed as values in a single column. So for example the source / medium column you export from Google Analytics needs unstacking to get one revenue column per channel. This is necessary because we might want to filter out specific values, categorize them into groups or just move to a tabular format for modeling. For marketing mix modeling you need one row per date and one column per variable, so unstacking is often a key part of cleaning the data. Take a look at how we unstacked the Facebook ads data and categorized the campaigns in the following Python notebook. Your challenge will be to do the same for Google.

Unstack Data

Categorize the campaigns into 'brand' (containing 'goolybib') and 'generic' (everything else). How many brand campaign entries were there?

Use the unstack data function on the categorized google data. How much did brand spend on 2021-03-24?

Some of the data we receive when building a model comes with the dates as columns and the variables as rows. This data needs to be transposed: that means swapping the rows for columns. Once it's in the right format we can join it up with the rest of the data we have in tabular format (dates = rows, variables = columns).
First we need to load the TV data from GSheets with the same method we used for Facebook and Google. The GSheet file is linked to below. Then we need to use the transpose_data function which will be provided.

Transpose Data

Did you manage to transpose the data with the function?

Another important concept is 'interpolation'. At present the TV data is presented weekly, but we need it daily to match up with the rest of the data we have. In order to do this we interpolate, which means filling in the gaps for the 6 days a week we don't have yet. The same method is used to zoom in on pictures (which makes them look blurry). We'll provide you with an interpolation function and the code to get it in the right format for interpolation, you just need to apply it. In order to use the function we need your date column to be in the format `YYYY-WW`.

Interpolate Data

Interpolate the transposed TV data and explain your method.

Now for a bigger challenge. Load the data from Google Analytics, unstack the data and filter for just the total revenue and date columns. You can use the methods we've covered so far (go back and review the code) and also feel free to Google for help if you get stuck.

Google Analytics

Please detail any issues you encountered that you had to work through to complete this challenge.

You'll see in the file above that we joined the data together for you into one file, based on the date column. Take a look at this code because we'll be using it in a later chapter.

Ok good progress
The media spend is the important part
Take a look next at what 3rd party data you can add

All your spend in one place

Market trends, and global events like COVID have an impact on sales, so how do we include them? The key is getting the data in a usable format.

It's straightforward to incorporate media spend, but how do we factor in external factors like market trends and the impact of COVID? Where do we even get that data? You ask a friend.

Yo!
For market trends, I usually just look at Google Search trends
Google also has a pretty good mobility dataset for COVID

One good proxy for market demand is how much the product is being searched for. Google doesn't provide actual search data but it does provide an index, which is all we need for our model. We just want to know when periods of interest were high or low, so we can tell whether our sales dips and spikes were due to something we did in marketing, or attributed to a wider shift in the market. Controlling for this will give us a better picture of how marketing is doing - without it you'd be giving too much credit to marketing in boom times and unfairly penalizing marketing in downturns. We'll provide you the data and code to deal with search trends data (which needs to be interpolated), but take a look at what it's doing, and the output.

Search Trends

Download the clean search trends data and make a line chart in Excel or Google Sheets. In what month does interest peak?

Large scale changes in human behavior will definitely impact your business as many have found with the COVID pandemic. Online businesses tended to do well, while traditional businesses did worse. Without controlling for these factors you may be under or over attributing performance to marketing. To include this data it's best to not simply use a 'is covid' dummy variable (1 or 0), instead you need more fine-grained control. When did people stay inside and when did they start to leave the house? This has changed daily based on lockdowns in different regions: luckily you can get this data from Google, who released this Maps data to help people understand how their community is reacting. Like search trends, this is an index, but that's fine for the purposes of our model. We'll provide the code to handle covid data for a region, you just need to adapt it and use it to get the data we need.

COVID Mobility

The files Google provides are quite large, so in our case we need to actually upload the file to the notebook, rather than loading it in from GSheets like we have in other exercises. To do that you can go to Files (left hand side menu) > Upload Icon. It should take about 2-3 minutes.

Get the data for 'New York' using the code and file provided.

Cool to see that COVID data
Didn't know that was available!
Finally we just need to add in fed holidays / sale periods

Importing 3rd party data

Holidays and other important dates need to be incorporated, and to do so we need dummy variables – 1s and 0s.

We're almost done, just one last task: incorporating holidays. But how do we handle dates? For that we need dummy variables.

The way we handle important dates in models is by creating dummy variables: columns of 1s and 0s to label when an event was occuring or not. This lets the model determine the impact of that variable on sales relative to the other channels and events that occured in that time period.
We'll provide you with the code for the exercise, you just need to modify and run it. First we look at the case of adding dummies for single dates, then the case of adding dummies for a range of dates, for example during sale periods.

Dummy Variables

Load the holidays gsheet and use the above function to generate dummies. Did you get this working?

Modify the code to get it working with date ranges in the sale period sheet above, rather than single dates. Explain what you did.

The final thing we need to do is merge all of our data together in one table, called the ABT or Analytical Base Table. This is the final cleaned version of the data, all joined together by the date column, which has been present in every data source. There are different ways to merge data, but typically you want to start with your revenue or conversion data as the first table and left join everything else to that. This is because if you don't have revenue or conversions for a time period, you won't be able to train your model on it anyway. Left join drops any values that are present in the right table (the one you're merging with revenue), so you'll never have a date that doesn't have revenue in your table. The other thing you want to make sure of is that you fill any N/A values with 0, which Pandas has a native function for called fillna(0). The code below shows you how to merge data with a left join. Do this for all the tables we've cleaned in this course so all of your data is in one place.

Merge Tables

The code for cleaning data will always be a little bit custom. Luckily Pandas and other Python libraries have a lot of common things covered, and there's a rich trove of information out there if you get stuck – just Google it (that's what even experienced developers do!). The benefit of doing this in code rather than manually in Excel or Google sheets is reproduceability and long term time saving: it might take longer to write the code the first time, but you can then reuse that code again and again, and it'll work in exactly the same way.

Amazing work on all of this
You've taken a big job off my plate
I'll make sure you're included in the modeling project so you get to do some of the fun stuff as your reward!

Making dummies for dates

Before you can build a model, you need your data in the right format. Data cleaning is 60% of the job: time to learn how to do it!

Is my data in the right format?

You've just landed a new client – a rapidly growing fast-casual hot dog restaurant chain in NYC. They want to measure the impact of their marketing campaigns, but weather plays a big role in driving sales. We need to account for that – but how?

The Original Dog Shed Restaurant, Tompkins Square Park – Mid Afternoon

The owner of the Dog Shed restaurant chain, Danielle Oscar, has invited you to her flagship restaurant to talk marketing. She wants to know if their new campaign is actually working, or if a recent heat wave deserves the credit. 

Thanks for coming
I like to take most of my meetings here, back where we started.
It reminds me of a time when I didn't have to have to take meetings.
Anyway, you're here, so you can help me with my problem.
We hired this expensive new marketing consultant who tells me what a great job he's doing... but I don't know.
Since I can remember we always sold more hot dogs when it gets hotter
People like to come out to the park, and a hot dog's got the advantage of being particularly mobile
I wanna know if I'm selling more dogs from the heat, or if this guy is actually doing as well as he says.

Back at the Vexnomics office – Late Afternoon

I heard it went well with Dani?
It sounds like a standard modeling project but we'll need weather data.
I found that NASA has pretty decent data that doesn't cost anything
Let me find the link
https://power.larc.nasa.gov/data-access-viewer/

We spend a lot of time thinking about what actions in marketing drove sales, but often external factors play a bigger role!
One great example is Weather Data – many product categories see temperature affecting sales. For example are you generating more sales on a hot day? Or does a cold day mean more people stay indoors and shop online for your product?
Without including this you might be giving too much credit to marketing and that means making mistakes when allocating budgets. Getting weather data can be a little messy – there are lots of options to download this data (few are free), and the methods can be a little antiquated and repetitive, or require manual cleaning or in some cases programming skill to pull data from an API.

Weather Data

Writer

It is best to read the weather forecast before praying for rain

Just caught up with Dani - she said she'll send the data tomorrow
While we wait can you make a start on gathering the weather data, just temperature and precipitation?
Then we can join it together and work on the model

Hoping for a heatwave

The client knows she sells more hot dogs in the summer, but what's driving that behavior, and by how much? Before we can figure this out, we need to clean the data and get it in one place.

You client sends across their data - it's your job to download relevant weather data and clean it, before adding it to the existing client data, ready for analysis. Let's get to work!

Your task is to download weather data from NASA, then clean the data before joining it to the existing client dataset ready. We need to download temperature and precipitation, which are labeled as 'Temperature at 2 Meters' and 'Precipitation Corrected' when you export the data from the POWER viewer. You need daily data for 2018, 2019, 2020, and up to June 2021, which you need to roll up into weekly data in the format 'YYYY-WW' using a pivot table. You also need to convert the temperature into Fahrenheit from Celsius.

If you want to download weather data for free, the best method we've found is as follows: visit the NASA Power Viewer (link below), choose a point on the map, select a start date and end date, select the CSV file format, then under '6. Select Parameters' choose 'Temperatures' and 'Humidity/Precipitation' then submit to download the CSV.

What's -10.02 in Celsius converted to Farenheit

Marketing Data
Hey all,
Here's the data you requested - let me know when you've done your thing.
Thanks


Join this data together so the Week columns line up. Note that the date range is shorter than your weather data. Make sure your date format is YYYY-WW. Share your file and explain your process.

Hey thanks for this
I also added daylight hours - important to know if people buy hot dogs because it's sunny, or because the day is longer.
I'll be in the office tomorrow we can build the model then

Leave it cleaner than you found it

Now we've got the data, let's figure out what drives hot dog demand. Seeing what's correlated with sales is key to understanding what drives it.

Now you have the data, we need to see if there are any correlations that stand out. The main thing we want to know is if there's a strong correlation between weather and hot dogs sold.

You may have heard the phrase "correlation isn't causation", and that's true! For example ice cream sales and shark attacks happen at the same time, but that doesn't mean one causes the other! They're both caused by a third variable, the increase in sunshine.
In addition, causation doesn't mean correlation either: take famous economist Milton Friedman's example of a thermostat. It turns on the heat when the outside temperature dips, and we know that the heater acts to increase inside temperature, but there would be no correlation between the activity of the heater and internal temperate, which is kept constant by the thermostat!
With all that in mind, doing exploratory data analysis is important for any business to understand what's driving sales – knowing what's correlated (and what isn't) is valuable information that may contain a few surprises. Unexpected information is the perfect place to look to form new hypothesis to test.

Variable Correlations

Web Comic, XKCD

Correlation doesn't imply causation, but it does waggle its eyebrows suggestively and gesture furtively while mouthing 'look over there'

In Excel and Google Sheets, you can find the correlation of two variables by using the CORREL function. Input the data you care most about first (sales), then the variable you think is correlated second (temperature), separated by a comma.
It can also be helpful to create a line chart to show visually if each variable is moving together. Put one variable on the left Y axis and one on the right Y axis to see them both on similar scales.
Finally, it can be useful to create a scatterplot chart and add a trendline with an equation, to get the R squared of the relationship, a measure of how much changes in one variable explains the other.

Create a line chart of temperature and hot dogs sold, with temperature on the right axis (sales on the left). Do they look correlated?

Correlation strength can be described as follows:
1.0 = Perfect Positive Correlation
0.7 = Strong Positive Correlation
0.5 = Moderate Positive Correlation
0.3 = Weak Positive Correlation
0 = No Correlation
-0.3 = Weak Negative Correlation
-0.5 = Moderate Negative Correlation
-0.7 = Strong Negative Correlation
-1.0 = Perfect Negative Correlation

Calculate the correlation between temperature and hot dog sales. How would you describe the strength of the correlation?

To see the correlations of all variables at once, you can create a correlation matrix. Create a grid with every variable name on the left as rows, and also on the top as column. Use the CORREL function to find the correlation betwen each pair in the grid. You actually only need to find half (on the diagonal) because you'd otherwise just be repeating yourself. More info in the article below.

Build a correlation matrix. What variable is most strongly correlated with sales?

Take a look at the weather variables correlation - which one has the strongest correlation with sales?

Create a scatterplot chart with store locations on the x axis and hot dogs sold on the y axis. Add a trendline with an equation to the chart. How much of the changes in hot dogs sold can be explained by the number of store locations?

Assuming the client is right about high temperature increasing sales, explain why temperature might be negatively correlated with sales.

Took a look at the data you sent
You're right, store locations is dominating any other effects
Try looking at the correltion since the 5th location launched?

Calculate the correlation between temperature and hot dog sales after the launch of the 5th store. Also calculate the correlation between TikTok impressions and sales. What can you say from this data?

Thanks so much for all the insight you've given me
It's amazing what you can see in the data - I'm gonna have to open another store!
I woulda axed that marketing guy for being slimy if it wasn't for your analyis
Looks like I'm stuck with him afterall
I thought there's no way the kids watching TikToks was selling dogs, but I guess we just learned a new trick

Correlation isn't causation but it helps

Gustav Gieger

Head of Performance

Dushyant Dixit

Consultant

Charlotte Cook

Sam Smirnov

Brand Strategist

William Winters

Data Scientist

Alexandra Anderson

Owner

Danielle Oscar

Does the heat sell hot dogs? Your new client at Vexnomics needs to know if it's the sun driving sales, or a new marketing campaign.

Do we sell more when it's sunny?

As the Head of Growth of GoolyBib, you just bet big on TV as your next growth channel... but how do you measure the impact?

You arrive at the GoolyBib office and take a look at your inbox. One email in particular jumps out at you – It's Gus, the CEO. You should probably check that message out first. What is it this time...

TV?
Hey,
Do we have evidence on TV moving the needle??
Need an answer ASAP: prepping investor deck.

As the Head of Growth at GoolyBib, a fast-growing DTC startup selling smart bibs, you started running a TV campaign 2 months ago. The wearable device for babies market is ultra-competitive, and potential customers need to be educated on your innovative new product: a smart bib that alerts you if your baby is choking on their food.
After some success with Affiliates and Facebook ads, you bet big on TV as the next major growth channel for GoolyBib. However leveling up from purely digital campaigns to TV gets you out of your comfort zone: you've always used last click attribution and haven't had time to explore other methods.
Now the CEO needs to see the numbers – this is critical for the future success of the company, and also your career! So let's get started...

B2C2B: Wearable Tech for Babies

Advertising Pioneer, 1800s

Half the money I spend on advertising is wasted. The trouble is I don't know which half.

Your friend Dushyant runs performance marketing for Ultra, a fast-growing male skincare brand. They spend a lot on TV so you're hoping he can shed some light on how to get the numbers your CEO needs to see.

Hey
Honestly I'd just start with a before / after analysis
If numbers are up compared to the same date range prior, I'd say that was probably due to your TV campaign!
We do surveys and a few other things, but it takes time
Just keep it simple for now

Email from the CEO – subject: TV?

You knew last-click attribution was far from perfect, but in this case it's useless: when there isn't a 'click', you're going to need another approach.

On your friend's recommendation you decided to run a before and after analysis. In preparation you've pulled revenue data from your analytics platform, with a wide enough date range to make your comparison.

Let's take a look at what our analytics is telling us. The CSV has data from Google Analytics telling us our revenue by channel.
Of course there is no 'TV' line item, because sales from TV will come through unattributed, or via other channels like an increase in brand term searches on Google.
Your task is to do some basic analysis of the data to understand how channels have changed before TV and after the campaign launched.

Before & After Analysis

How much revenue total did we generate from all sources?

The TV campaign began on the 23rd of August, 2020. How much did revenue change (in %) in the period after TV started running versus before it started running? Explain your method.

The TV campaign spent approximately $500k. Did it break even?

What 2 sources had the biggest percent change in revenue during the TV campaign?

Facebook Ads also saw an increase in revenue – what could explain this?

All models are wrong, some are more useful than others

The before and after method bought you some time, but now you need to learn how the professionals do it: Marketing Mix Modeling.

Unsatisfied with the simplicity of the before and after approach, you decide to dig deeper and find out how attribution is done by more sophisticated TV advertisers. You message your friend for more details.

Yeah sure, happy to help
The main thing we do is Marketing Mix Modeling
[https://en.wikipedia.org/wiki/Marketing_mix_modeling](https://en.wikipedia.org/wiki/Marketing_mix_modeling)
It's basically just a linear regression model... the trick is to figure out what should go into the model.
It's like a 1-2 week exercise, we do it in-house once a quarter

Marketing Mix Modeling was introduced in the 1980s to match spikes and dips in sales to actions taken in marketing (and external factors). It can tell you the incremental impact of each part of your marketing strategy, so you can make data-driven decisions on where to allocate budget. Previously used mostly by Fortune 500 advertisers spending millions on SuperBowl ads, MMM is now the only future proof way to measure marketing ROI across channels, thanks to Apple, GDPR/CCPA and adblockers limiting marketers' ability to track users.
As a probabilistic attribution method, it can only give you top down insights on what channels are working, not user-level data on who bought what like more deterministic methods such as last click. It's also a more manual and technical process, not something you can easily automate or pull off the shelf. To master MMM effectively, you're going to have to change the way you think about marketing attribution.

Marketing Mix Modeling (MMM)

Think with Google

Marketing mix modeling (MMM) is a time-tested method for measuring the impact of your marketing.

Finally after work you get back and open up your laptop... ready for more work. You never get the time to do the necessary research for your job in actual work hours, so you make it up at home. Nevermind getting ahead – it's the only way to keep up! Tonight's mission is to understand Marketing Mix Modeling you're researching. You find a useful looking blog post...

What marketing attribution questions can MMM answer?

Unlike user-level attribution which builds up from individual user behavior, marketing mix modeling works from the top down, associating events and actions with spikes and dips in sales. This allows you to still measure ROI and make strategic decisions even when theres no user level data, but you usually don't have enough data to tell how individual tactics, day-to-day optimizations or other smaller changes are working.

In the Econometric Model for Pie Sales, what variable had the biggest impact on sales (positive or negative), apart from the baseline?

Explain what the Adstock effect is, and what it's used for

Matching spikes and dips to events and actions

MMM is a black box, and you don't have time to look inside – maybe it's time to call in a favor and bring in some outside help.

Soon after you arrive at the office, you feel a tap on your shoulder. It's the CEO, inviting you to a 'quick' meeting on the TV performance. You share your opinion with Gus that MMM could be the way forward.

Sounds like the right path
Investors will want something substantial if we're going to score a Series A
An old friend of mine from college does this sort of thing
I'll get in touch
Leave it with me

Marketing Mix Modeling
Hi there,
Gus connected us as he said you needed a marketing mix model built to explain the impact of TV?
This is a pretty common exercise for me, so I'm confident I'll be able to help!
Let me pull together a list of the data you'll need and we can go from there.
Best,

Data scientists spend 60% of their time on cleaning and organizing data. Collecting data sets comes second at 19% of their time, meaning data scientists spend around 80% of their time on preparing and managing data for analysis.

Data collection and cleaning are not only the most time consuming jobs, but also the tasks that Data Scientists, Statisticians and Econometricians enjoy the least! With Marketing Mix Modeling projects, at least some of this burden falls on the client (that means you!).
A good vendor will usually send you a data checklist – an example of which you'll find below. This goes through each of the 4 P's of the Marketing Mix (Product, Price, Place, Promotion) and lists all the usual suspects in terms of data sources and activities that are likely to show up as significant in your model.
MMM is a holistic exercise so you'll need to provide a lot of data on almost everything you can think of that might have driven sales, in order to give your vendor the best chance of building an accurate model. As a vendor will be unfamiliar with your business and what data you have accessible, you'll need to give guidance on what data is available, what variables you think are likely to drive sales, as well as help them get the data in the right format.

Data Request Checklist

A friend of the CEO – hiring a consultant

You didn't realize the modeling process would be so all-encompassing... you need a lot of data and you want to get it into the right format, so you don't waste the consultants' time.

Luckily you didn't have to do the hard work to pull the data – a friendly member of the analyst team did that for you. But you do have to get it into the right format for modeling...

Take a look at the Google Analytics data in the Data Room folder, labelled 'GoolyBib-trafficsources-all-traffic'. Right now it's in 'stacked' or 'long' format, and we need to 'unstack' it for modeling. Each Source/Medium should be a new column like in the image above ('unstacked' or 'wide' format). How would you accomplish this (without just copy/pasting)?

Unstacking Long Format Data

You should try to 'unstack' the Google Analytics data in the Data Room (GoolyBib-trafficsources-all-traffic), converting it from long format to wide (each Source/Medium has its's own column. How do you accomplish that?

Data Requests
Hey,
Just checking if you had the TV data in daily format instead of weekly for the model?
If not we're going to have to transpose it.
Best,

Now we need to look at the TV data in the Data Room folder, labelled 'GoolyBib-TV-Expenditure-2020'. The problem with this data, is that it's weekly data when our marketing mix modeling vendor has requested daily!
Luckily there's a technique called 'Interpolation' which let's us fill in the gaps to turn weekly into daily data. Each day of the week for the date range should divide the weekly number we have and divide it by 7. We also need the data to be 'transposed', meaning one date per row, rather than per column.

Interpolating and Transposing Data

Now try to Transpose and Interpolate the TV data in the Data Room (GoolyBib-TV-Expenditure-2020), turning it from weekly to daily and making sure there's one date per row (rather than horizontally across columns). List the steps you took to do this.

RE: Data Requests
Thanks so much!
It looks like everything I need for my model is in here.
I appreciate you putting it into the right format - the transposing saved me some time!
Look out for my finished model next week
Best,

Getting ready: data collection & cleaning

This isn't like the attribution methods you're used to – how do you make decisions and take actions based on the model?

Finished Model
Hi there,
As promised, attached are the slides for the presentation.
Top level summary:
- The model could explain 75% of the movements in revenue, and was only out by 5% on a daily average across the period
- TV returned $0.40 cents for every dollar invested, but this doesn't capture long term effects on the brand (likely to be substantial)
- Affiliates doesn't look to be driving incremental revenue. You should investigate their behavior further
- Facebook looks to be driving a high return at $4.20 ROAS, but we should validate this with an incrementality test
- There are improvements to be made with the model, for example the numbers for Facebook are likely inflated
- According to this model, with $10,000 spent on Facebook and $5,000 on TV per day, we'd average $57k revenue
Please let me know if you have any questions – let's hop on a call to discuss.
Best,

Even if this isn't the first Marketing Mix Model you've seen, there's a lot going on here to interpret. First and foremost is the accuracy – our model could explain 75% of the movements in revenue (an R squared of 0.75) and was 5% out on any given day (a mean absolute percentage error of 5%). This is within reasonable levels for decision-making, so it's worth digging into the model further.
Next we look at the coefficients and see if they make sense. Facebook Ads drove the majority of revenue due to its high return of $4.20... but this seems too high. Maybe in the model it's stealing credit from elsewhere. Affiliates is driving no significant revenue, which may very well be true: as far as you could tell they were mostly targeting existing customers who would have purchased anyway. TV is less good news, as we're losing money at 40 cents on the dollar.
Now the statistics – if we look at the models spreadsheet we can see a number of additional information above and below the coefficients: Omnibus, Skew, Kurtosis, Durbin-Watson, Jarque-Bera and Cond No. None of these likely mean anything to you, so that's what we'll investigate next. Read the following article for more info.

Interpreting the Model

After reading that article, you see that the model failed the Omnibus, Jarque-Bera and Cond. No statistical tests. You message the vendor about this to get their explanation.

Hey, thanks for messaging to follow up
You're right on the money – the model still has some flaws.
Specifically this means that the errors aren't evenly distributed, and the variables may not be independent.
It's rare for a model done in a short time on limited data to pass all tests, though in this case we're not in a bad position
The variable giving us trouble is Facebook... the numbers have been pretty constant but then rose at the same time as TV.
That's the reason one of my recommendations was to pump Facebook up in step changes to get a clearer readout.
Happy to help you spec this test out!

RE: Finished Model
Thanks Charlie,
Appreciate the quick turnaround.
I think we have enough for now, but Facebook def too high. I'm fine with TV numbers: this isn't capturing long term branding impact.
We'll discuss and circle back.
Thanks,

You've made it all the way to the end of the simulation. You learned about Marketing Mix Modeling and how it can help you tell the impact of TV.
This free course is focused on a Marketing Manager's role in working with a vendor for Marketing Mix Modeling. It's meant to give you a taste of what the deliverables look like and what to expect in terms of process.
If you want to keep learning and go more in depth, we'll soon have a library of content to train you or your team in Marketing Mix Modeling and Marketing Attribution, available for paying customers.

Congratulations!

We've got the numbers, but can we trust them?

What's the impact of TV ads?

You're a newly promoted data scientist with an important task: find what variables are important to include in your model.

As a newly promoted data scientist at Vexnomics, you're about to attend a meeting with your boss to find out your priorities.

Hey I bet you're looking forward to getting your hands dirty with some more advanced modeling?
I do actually have something for you
Dog shed have come back to ask for a more in-depth modeling project, to build on the work we've done before
They have a big list of variables they think are significant
We need to include the most important variables, but we don't have much data to work with so we're risking overfitting
So the job is to whittle that list down to produce a model that's a little more practical
Are you up to the task?

Ideally we'd be able to include as many variables in our model as possible, because so many things may impact our sales. However in practice, the data we have for modeling is always limited: we never have enough of it! Therefore we run into the curse of dimensionality – every new datapoint we add to our model becomes a new dimension on the chart. If we add too many variables each one could just memorize each observation and perfectly predict our data back to us. This is called overfitting, and it's a problem because it would lead to a model that doesn't do very well predicting data it hasn't seen yet. It would give you good accuracy scores, but wouldn't generalize enough to be useful in future forecasts. In addition the model would suffer from multi-colinearity, where multiple variables would be correlated with each other, which makes it harder for the model to separate out the impact of any one variable.
The solution is to always keep the model as simple as possible, and throw out any variables that don't show significant impact. But how do you know what variables are important? The act of doing this, called 'feature selection' is both a science and an art. Feature selection is one of the fundamental problems in data science. It's tedious to do manually in Excel but also can't be fully automated, so a good analyst needs to understand the different techniques available and when to use them

The Curse of Dimensionality

Theoretical physicist

Everything should be made as simple as possible, but no simpler.

Hey as promised here's the cleaned data for Dog Shed
We have 146 observations here so as a rule of thumb we need to limit to 14 variables at a maximum, but ideally aim for as few as possible
Try to build the model manually first to get a sense of what's important in the data, and then run a few feature selection algos before deciding on the final list
Let me know how you get on

As a general rule of thumb you should have a minimum of 7-10 observations in the data for every variable you include in your model. This isn't a hard and fast rule but can help you avoid trouble with overfitting. This means if you had a year's worth of weekly data, 52 observations, you'd want to stick to 5 variables maximum. You might find more variables than that are significant, and even that model accuracy improves with 6 variables, but the model will be simpler and more generalizable if you limit it to the top five most significant variables.

Avoiding the curse of dimensionality

Before you attempt to automate it, it's important you step through it manually first. Work through building a custom model by hand.

Time to dig into your feature selection task. First let's build a model with everything in it and see what variables look important!

A kitchen sink model comes from the phrase 'everything but the kitchen sink'. It refers to including all variables in a model to see which ones are statistically significant. It can be a good starting point, from which you can work through and drop some of the least significant variables. Create a model with the LINEST function, then calculate the P Values in the same way as the following template does. The P Values will tell you which variables were significant, which you can then drop and create a new model. In dropping variables you want to see how it impacts the coefficients of the model, as well as the p values and the overall accuracy. For example dropping a variable might decrease R Squared, but the coefficients for the variables that are left over might make more sense. Particularly if two variables are correlated, removing one might make the other more significant.

Kitchen Sink Model

Taking a look at the template and how it works, then create a 'kitchen sink' model with every variable: which variables were statistically significant? (p value less than 0.05)

What was the least statistically significant variable in the model? (apart from the constant)

Copy your work to a new tab, and create a new model but remove the least significant variable: how did the R squared of the model change?

In another new tab, copy the template to make a new model, but this time remove all of the variables that were insignificant in your last model. What variables are left?

Hand crafted artisanal models

Explore Filters, Wrappers and Embedded methods to find the best way to automate feature selection for your model.

Now that you've had a few rounds of manual selection, you see the benefit of automating some of the work. This is where it makes sense to break out into coding...

There are three main types of automated feature selection: Filter methods, Wrapper methods and Embedded. Filter methods are the cheapest computationally, as they rely on simple calculations and rules, and don't take the whole model or other variables into account. Wrapper methods rebuild models trying multiple combinations of variables until the right mix is found that maximizes accuracy or removes insignificant variables. Embedded methods are part of the regression algorithms themselves, and often use feature importance to determine what variables are selected.

Feature Selection

Google Colab is a hosted version of Jupyter Notebooks, a way of running code cell by cell that's popular with data scientists. Using a notebook helps you work through the model step by step while showing your work. You can share Google Colab Notebooks just like any Google doc, and you don't need to set up any coding environment on your computer. If you're unfamiliar with colab or python code we recommend taking some time to learn how it works before continuing this simulator, which assumes some Python coding knowledge.

If a variable doesn't have much variance (it doesn't change much) then it's not likely to be that informative in the model. This is because regression works best when it can map spikes and dips to events and actions, in order to estimate what variables were most responsible for performance. One simple filter method is to just remove variables that have low variance.
Another filter method is Univariate analysis: regressing each variable individually against sales gives you an idea on how much variance it can explain on its own. This isn't a perfect method because it only takes one variable into account at a time (without accounting for interactions between variables) but it is computationally quite simple and easier to interpret than multi-dimensional analysis.
Read the first two sections of the Scikit Learn documentation (link below) on 'Removing features with low variance' and 'Univariate feature selection' then use these examples to perform variance and univariate feature selection on your data.

Filter Methods

Make sure you drop the 'Week' and 'Sales' variables from the dataframe before doing the variance selection. Use the same threshold they use in Scikit Learn. Rather than using fit_transform, try using fit, and then get_support() on the sel variable to get a list of booleans you can use to filter the dataframe. For example: `sel.fit(X)` and then `X.loc[:,sel.get_support()].columns`. This method can also be applied to univariate selection to get the selected columns.

What variables did the variance threshold select?

What variables did the univariate selection choose?

Can you forsee any downsides or issues with using these filter methods?

One benefit of code is that it's trivial to run many different models with any combination of variables and see what works best. In Excel or GSheets this would take a long time, but in a Colab notebook you just need to create a loop and define the logic you want to use to select what model works best.
One common approach is backwards feature elimination (BFE), where you start with a 'kitching sink' model (i.e. all variables), then work your way backwards, dropping the least significant variable until all variables are statistically significant and/or you're below the threshold of the number of variables you want to select.
Another wrapper method feature selection algorithm you could use is progressive feature enhancement (PFE). This works in the opposite direction: start with no variables and iterate through each option until you choose whatever one increases the accuracy the most. Keep adding variables until you hit your quota or the variable is insigificant.
In this exercise we won't be asking you to code these solutions, instead you should run our implementations and see how they work. We'll ask you how you might modify the logic to improve accuracy, so be sure to play around with the code if you understand roughly what's happening. Feel free to make changes to see how it changes the outcomes of what variables are selected.

Wrapper Methods

Automated feature selection methods

How do you know what factors are most responsible for your performance? Selecting features for your models is both an art and a science, but it's tedious work until you learn to automate it!

Free Courses

Premium Courses