Building a marketing mix model is not just about finding the right parameters, but also the hyperparameters used to transform your data to account for adstocks and saturation.

You've been looking for an excuse to learn how to use evolutionary algorithms for building models automatically, and now this might be your chance. You hear on the grapevine that a colleague is struggling with their model.

Yes you heard correctly
We're struggling with the GoolyBib model for Italy
I'm not satisfied with the model as it stands – the error is over 10%!
This could be a good use case for automated hyperparamter optimization
Doing it manually is getting us nowhere fast!
I'd appreciate your help

Every model has parameters: these are the variables used to make predictions when the model is built. For example your model might say that Facebook Ads drives a cost per purchase of $5. How the model arrives at that conclusion is dependent on Hyperparameters: parameters that control how the model learns the right answer. To take our Facebook Ads example, our cost per purchase might get worse at higher spend levels due to saturation, or there might be a carryover effect where spend today affects performance tomorrow. 
These transformations of the data are key to building an accurate model, but it's hard to know ahead of time what values are correct. The manual way of figuring this out is to choose a value for a parameter, for example an adstock level of 20% (20% of your spend from today has an impact tomorrow), and then move that value up or down to see how it impacts model accuracy. If you increase it to 30% and accuracy of the model goes up, you know that's a better fit. The problem is that each parameter affects the behavior of every other parameter in the model, so it can be hard to find the right value.
So there have emerged several strategies for dealing with this task of Hyperparameter optimization. The brute force approach is a grid search: try every possible value until you find the right one. This isn't always possible however, because there may be more potential combinations than there are stars in the universe! Random search lets you set a limit of how long to look for the right values, randomly guessing within a budget. The method we're using today is an evolutionary approach, which learns in a more efficient way than random guessing. It deploys a population of potential solutions, then kills off the worst ones (with the highest error) and replaces them by mutating the best  performers in the batch. With hundreds or thousands of iterations we can efficiently arrive at the best estimate for even a very complex set of parameters.

Hyperparameter Optimization

GoolyBib Italy model
Hi,
Thanks for offering to help: please see model attached. 

We have been choosing the values for theta (adstocks) and beta (saturation) manually and this is the best combination we've got to so far.

Best,

Statistician and Management Consultant

To optimize the whole, we must sub-optimize the parts.

Marketing campaigns tend not to have a linear relationship with sales. As you spend more money on a channel, the efficiency decreases because you have to outcompete a greater number of other advertisers for ad inventory. In order to model a non-linear variable in a linear model, we must transform the data. The Power saturation curve we're using simply takes the advertising spend to the power of beta. But what is beta? It's a Greek letter, because by convention mathematicians use them to identify parameters in a formula. 
We don't know what the correct value of beta is – it's our job to estimate it. The way this is done is by trying a lot of beta values from ~0 (completely saturated) to 1 (linear) and see which value best improves the model. If we see the accuracy increase from 0.3 to 0.4, but then decline again at 0.5, we know the true value could be around 0.4.
We do a similar job for adstocks, to estimate the percentage of advertising impact that's felt the next day after spend. For example if we choose 0.2 then 20% is felt the day after, 20% of that the next day (4%), and so on. The process is the same: calculate the accuracy of the model at each level of adstock, and see what it does to accuracy. Of course this can get complicated to do across multiple variables, as if you change one it changes the whole model, and the odds of ending up with the best possible combination are low.

Theta and Beta

For models with up to 5 or 6 parameters, it can be feasible to adjust the model manually and find a good enough solution. However this task quickly becomes impossible with more parameters because the number of potential combinations increases exponentially.

Finding numbers for Greek letters

Rather than manually choosing the value for each parameter, let's get Nevergrad to do it for us. It uses an evolutionary algorithm to build hundreds or thousands of models to find the right parameters for us.

After digging through the Nevergrad documentation and using some marketing mix modeling code you had from another project, you have something ready to run.

Nevergrad is a Python package created by the Meta (Facebook) team, for gradient-free optimization using techniques. It works using differential evolution and particle swarm optimization, which optimizes a problem by iteratively trying to improve a candidate solution with regard to a given measure of quality. 
This process is called evolutionary, because it is based on the ideas behind evolution in nature. The simplest evolution strategy operates on a population of size two: the current point (parent) and the result of its mutation. Only if the mutant's fitness is at least as good as the parent one, it becomes the parent of the next generation. Otherwise the mutant is disregarded.
Such methods are useful because they make few or no assumptions about the problem being optimized and can search very large spaces of candidate solutions. Because it does not use gradients like other optimization methods, it can be used on optimization problems that are not even continuous, are noisy, change over time, etc. This is perfect for machine learning where the actual model might be a black box, where changes in one part of the model can give non-linear responses in another part of the model. The downside of evolutionary algorithms is that they are computationally intensive, so take a while to run, and do not guarantee an optimal solution is ever found. 

Nevergrad Optimization

Hi, I'm going to walk you through how Nevergrad works
I'm gonna take the file here, and just going to make a copy, so we have our own copy in drive
First you want to install Nevergrad
So if you just click into that cell and press shift + enter, or you can click play here
That's just downloading the library and installing in on the machine Google is giving you for colab.
Now we just need to load in all of the libraries that we're going to use
Just shift + enter, or click the play button.
And these are some functions we're going to use as well
We need to authenticate Google drive so we can import the data
So if we just run that cell, then it'll give you a link to click through, and you just want to authorize, then copy and paste
Hit enter
Now if we run this cell
Its going to pull in the data from this Google doc, and it's going to display it here as well
So we know that we've got the right data, its the number of days, then facebook spend, radio spend, tv spend and then revenue
Now let's plot the data so we see how the data is trending over time
So we see here it's a plot of revenue, and then we see all the facebook, radio and tv spend underneath
Now in order to run Nevergrad we need to do a little bit of setup
First we need to get all the spend columns, so the media variables are if 'spend' is in the column, and then we just need to create the X and Y variables
Y is revenue, that's what we're trying to predict
Then X is what we're trying to predict it with
This is an adstock formula, it helps us take into account that there will be some lagged effect of advertising
And Nevergrad is going to help us find the right adstock values
It's going to try lots of theta values here
And it needs that function in order to transform the data in each run
Same for saturation, this is diminishing returns its the same thing, its just a power transformation, with just one variable
You can have more variables, if you do something more complex, like a weibull adstock or a hill saturation curve.
Now we need one big function to model everything together
So it needs to take in the facebook spend theta, facebook spend beta, and then same thing for each other channel
And then it just pulls it together, and then creates the adstocks, and the saturation, so it does adstock first, and saturation after that, which is best practice
Finally it creates a new data frame, basically an excel spreadsheet
then creates the X and Y values, and splits them into X train, X test, and X train X test
So this is a train test split that lets us check the accuracy, with data we haven't seen before, in the model
This prevents overfitting of the data
Finally we have a linear regression model
Although you could replace this with whatever model you want to use, if you want to use Ridge regression or something different, and then it fits the data, and gives us predictions, we then calculate the accuracy metrics
So normalized root mean squared error, is just an accuracy metric that gives a percentage, that penalizes really high percentage values, more than mape 
The mape is the mean absolute percentage value, and that's just a general percentage you were wrong each day
We also have the R squared as well
Then we kind of wrap this function and get all of the results, but only return the mape value
This function is what nevergrad is going to use to optimize to
So here we're optimizing to the mape
But we could swap this out for nrmse or r squared if we wanted to
Now we just need to make these scalar values
Don't worry too much about this, but we're essentially just setting the bounds of what the parameters could be, and then we're going to run the optimizer here
The optimizer, this is actually Nevergrad doing its thing
We're passing in the instrumentation into the parameters, and then we're giving it a budget, and we're saying this is how many times we want you to run a trial
And the trial is basically the algorithm choosing what kind of parameters to set or to try next and then run that trial to see if those parameters worked
Then we just want to log the error values, so we can do a chart afterwards of how quickly it approached the lowest mape, 
so that's what we're doing here
we're just registering the callback
every time it tells us the answer for that trial
ok and we're going to set a timer, so we just see how long it takes to run
this is the optimizer function, this is where its actually minimizing the error
this is like where all of the work is actually happening
so when you run all of this
oh I have an error
main function is not defined
if you ever get this, its because you probably ran things out of order
so I'm just going to go back and run this one here
then run this one again
there you go, you can see it took 1.8 seconds to do those 100 trials
and these are the values that it came back with for facebook beta and facebook spend theta
So these were the parameters for the adstocks and saturations, that got us the best accuracy out of all of the models they tried
You can also kind of push those parameters into the function that you created in order to get the MAPE
So it's 0.076 so it's 7.6%
Then you can see if you plot
So just run that so you can see if it works
It's going to be a little bit different each time, because its an evolutionary algorithm, so there's some random elements in there and it wont' always be the exact values
Just going to run this
We can see here that we started off with a very high mape, it's 18%, and then even spiked up to 20%
Then over time it kind of evolved towards the better values
It got a lot lower, to 0.08 and then stayed around there for most of the 100 trials
Then if we just run the build model function, its going to return us the mape value and the nrmse, r squared value, the model and also the result
So we just run that and its going to give us those values
We can also plot the accuracy
So if we plot the figure, and we take the revenue and the prediction from the result, which is the data frame, then we are showing that
We're seeing here the revenue is in purple, vs the red prediction
That's how you run nevergrad in order to find the right parameter values




Use Nevergrad

What are the benefits of using Nevergrad vs other optimization libraries?

Describe in your own words the steps taken in the Google Colab notebook to use the Nevergrad library

That's great to hear
8% error is much better than we had on our manual model
Looking forward to seeing if we can improve it further by dialling up the number of models

This approach works if you're running up 2,000 models, but it takes a while and hits an error if you set the budget too high. To reach 10,000 models we have to parallelize the operation, meaning apply multiple 'workers' to solving the calculation, rather than calculating each iteration one at a time.

Better than random guessing

We need to make some modifications if we want to scale up the number of models we're building. It will help to parallelize the operation to allow multiple calculations to happen at once.

We have a model that works – we're already getting better accuracy. Let's modify our code so we can run a much higher number of models.

Nevergrad offers parallelization right out of the box, you just need one other library called 'futures' to make it work. Futures lets you create a ThreadPool which manages the multiple workers you create to do the calculations. These workers can carry out tasks asynchronously, as if they were their own mini computers, and then pass back the information to the main function. We run this within a 'with' statement to ensure they're cleaned up properly and shut down after everything finishes executing. 
You can have as many workers as you have cores available for processing on your computer, which you can find with the os library `os.cpu_count()`, though it makes sense to leave 1 core for anything else your computer needs to calculate. For demonstration purposes we're going to just use 2 cores, which is the minimum needed to calculate 10,000 models. We set this in the optimizer on the first line with `num_workers=2`. The following file has the updated script we'll use.

Parallelizing Nevergrad

Copy the code from the last model to show the output of the parallelized code. What is the MAPE for the next model? Was it significantly better?

The parameter for TV spend beta changed from 0.45 to 0.10 in the new model. How would we interpret that?

What does the chart showing mape over time tell you? What does it look like Nevergrad was doing? Is this behavior what we'd expect?

Thanks for sending the results across - very interesting
I thought dialling up the models would get us a better result
However seeing that it jumped around a lot and tried lots of options gives me confidence that we really did end up with the best allocation
Thanks again for your work on this
We can reuse the script with modifications for every new model we need to optimize!

Scaling to 10,000 models

It's tedious to try different diminishing returns manually to find the best one. Facebook's Nevergrad package can try thousands of models for us!

Let's try 10,000 models

You just graduated with an Econ degree, and landed your first job: marketing for a product recommendation tool for E-Commerce stores. You’ve gone through orientation so now the real work begins.

Upsert is a product recommendation engine that let's you automatically insert product recommendations as natural links in your blog content. They just hired you in your first role out of college! They're B2B, targeting primarily ecommerce website owners who are on the Shopify or Woocommerce platforms, with which they have integrations. It's free to promote your own products using Upsert, but you can make money 'Upserting' the products of other relevant, non-competitive brands. Their main marketing channel outside of word of mouth and referral is PPC - ads on Google search. Learning how this channel works is the main thing that attracted you to the job. You started last week, which was mainly just orientation: you're eager to please and ready for your first task. 

Welcome to Upsert

You finished orientation last week, so now you're in early, eagerly awaiting your first task. You get an incoming Zoom call from the Head of Marketing...

Hey
I hope you're settling in here?
I was wondering...
Would you like to take a look at our PPC account?
It's due a round of bid optimization - it hasn't been done in weeks.
Let me know if you get stuck!

Referring to ads on search engines such as Google and Bing, or the business model they pioneered, where ads are bought on a self-service basis, using an auction model where advertisers set a 'bid': how much they are willing to pay per click on their ad.

PPC (Pay Per Click)

Bid optimization is one of the most fundamental skills you can learn as a marketer. To figure out what bid to set for your campaigns, you need to have a solid grasp on how to calculate the key performance metrics: CPM, CTR, CPC, CPA and more. The key metric you need to calculate is RPC: Revenue per Click. This is the amount of revenue you expect to make per click for that campaign. Each campaign, adgroup and keyword performs differently, so will have different expected values and therefore different bids.
Once you can figure out how much a click is worth for you, and pivot that calculation by channel, device or keyword, you can rapidly improve the performance of your campaigns. Knowing how to calculate the value of a PPC campaign is a transferrable skill: not every channel requires you to set bids, but if you can calculate the value of a click from any channel, and compare it across channels, you can make much better budgeting decisions to improve your ROI.

Bid Optimization

Even if it's called "Pay Per Click", the real goal is to pay the right amount per Action. So you should always set your 'bid', the amount you're willing to pay for a click, based on how many actions people will take after clicking, and what those actions are worth. 
Take the number of conversions you received on that campaign historically, and multiply that by the average value of those conversions. The formula is: (Average Conversion Value x Number of Conversions) / Number of Clicks = Expected Revenue per Click

Founder, IdeaLab

Pay per click was just the beginning. The real evolution is pay per action.

Can you optimize the PPC account?

Once we've got the data, it'll be time to calculate our metrics. First we need to calculate the main marketing metrics: CPC, CTR, CPA, and others.

We're eager to please as this is the first task we've been trusted with. Once we get the data we'll calculate the KPIs, Key Performance Indicators, that our boss would expect: CPC, CTR, CPA and others. In the meantime, let's learn about Pivot Tables so we're ready.

KPIs are the key metrics that marketers use to measure performance. They include metrics like CPC (Cost per Click), CTR (Click-through Rate) and CPA (Cost per Acquisition.

KPIs (Key Performance Indicators)

The main weapon in every data-driven marketer's arsenal is the mighty Pivot Table! It's the workhorse of marketing analytics, because it allows you to quickly summarize the data, run calculations and segment it any way you see fit. The best thing about it: it's drag and drop! Anybody can learn to use Pivot Tables, even if you aren't technical or don't know how to code. 
Excel was initially used in Marketing to manage media plans, but really came into its own when Search Engine Marketing rose to prominence. Because Google allows marketing to be bought self-service rather than by negotiation over the phone, Marketing became more analytical and being able to calculate marketing KPIs in Pivot Tables became an essential skill.

Marketing Analysis

Here's how to create a Pivot Table
If we click into the data, anywhere we want.
Press CTRL + A, or Command + A if you're on a Mac
And then you want to go into Insert > Pivot Table
And you can choose whether you want it in a new sheet, or an existing sheet.
So we've got it in a new sheet, and what you need to do to create a Pivot Table is just drag in the Rows, Values or Columns
So if we want to see performance by keyword, we drag in the keyword column here
and then for the values, we want spend
You might think you want spend in the columns, but that's actually something slightly different
If you put something in the columns, like device, then it actually just splits the spend by device up here as well
So that's how these work and this is all drag and drop, so don't be intimidated, it's pretty easy
If you instead wanted to put device over here, now we have a breakdown by device and by keyword, or you can put the keyword in the columns at the top here


Pivot Tables

PPC Data
Hey,
Here's the data I promised from the PPC account. You should take this and calculate the main KPIs like CTR and Conversion rate, then work out what we should be paying per click for each keyword.
Let me know if you get stuck!
Best,

Create a Pivot Table with Adgroup as the Rows, and Spend as the Values. What Adgroup got the least clicks?

Ok let's learn how to do Calculated Fields in Pivot Tables
We're looking at some click data, and some leads data, for a campaign that we ran, and it's broken down by device
So if we look at the underlying data
If we wanted to calculate conversion rate, we do have that calculated here
and we might intuitively think "ok let's just bring conversion rate into the pivot table, and then we've got the conversion rate... but this is actually wrong!
If we try and calculate the conversion rate, which is the leads divided by clicks, and then let's just turn this into a percentage, you can see that the true conversion rate is not 48%
So let's try and average it - this is summing all the conversion rates, lets try and average it
And you can see it's still wrong! You shouldn't average an average, because it's going to give different weights to the final value
So how do you do it?
Well the real way you do it, is with the calculated fields function
So you have to let the pivot table do the pivot table do the calculation for you
And that's the only way to get the true, weighted average
So if we go to calculated field, and then we're just going to take the leads, and then divide by the clicks, then hit enter
And now we have this calculated field, and if we select this and click percentage, we have the true conversion rate here
We can just call this conversion rate as well
Cool so that's how calculated fields work
And if you wanted to then change how you're summarizing the data
So if we take off device and we add adgroup instead, we can see it's still calculating conversion rate correctly.
And that's the power of calculated fields!



Calculated Fields

Why is it incorrect to take the average of a metric like conversion rate?

Pivot the data by Keyword instead of Adgroup, and calculate CTR (Clickthrough rate), which is Clicks/Impressions. What was the CTR of the keyword 'product recommendations'?

Calculating the full range of KPIs

We've learned how to calculate KPIs in Pivot Tables in Excel, but how do we know what we're willing to pay per click? We've got one more metric to calculate...

You've finished the KPI calculations warmup, not for the main task: calculating how much we should be paying per click. You ask a mentor for help...

Oh fun, bid optimization! I did this in my first job too :-)
Sure I'm happy to help
There are way more complicated ways of figuring this out, but you just need something simple
Take the historical leads for a keyword, multiply them by how much a lead is worth on average
That's the effective revenue
Divide that by the number of clicks, and that's what you're willing to pay
Assuming you're running marketing to a breakeven ROAS

Take the revenue or value generated from advertising, and divide it by how much you spent: that's your ROAS. It's similar to ROI, but is easier to calculate in scenarios where you don't know your profit margin. It's common in performance marketing to run your campaigns to break-even ROAS (100%) to maximize volume, on the assumption that organic / unpaid channels will contribute profit.

ROAS (Return on Ad Spend)

Assuming Leads are worth $45 each, use a calculate field to calculate ROAS. Do the campaigns break-even in aggregate?

Calculate the suggested bid for each keyword - what keyword are we willing to pay the most for per click?

It's often more useful to calculate the relative change needed in bid, the bid adjustment, rather than simply displaying the suggested bid. This is because it helps you identify mistakes (i.e. if an adjustment is too aggressive) and people usually find it easier to set percentage changes in the Google ads platform. To calculate bid adjustment, take the suggested bid, divide it by the Cost per Click and subtract 1, then format as a percentage.

Calculate the bid adjustments (%) for each keyword. What does the bid adjustment and other metrics tell you about the worst performing keyword?

RE: RE: PPC Data
This is amazing! I didn't expect you to get all this done on your first attempt. 
You don't have posting privileges yet in the account, so I'll get someone to check and action this for you, but keep this up and you'll be running the account in no time!
Thanks again,

What are we willing to bid?

It’s your first job in marketing and the first task is optimizing the PPC campaigns. You’ve got the report, but how do you calculate what to pay per click for each keyword?

What am I willing to pay per click?

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?

It can be hard to come up with something new staring at a blank canvas. Whether it’s a social media post, blog or email, start by explicitly copying something that already exists and is proven to work. Then you can modify it to make it your own.

Hey I was hoping you could help me out with something while you're between projects?
We have a Twitter but we haven't posted anything in a while.
I don't suppose you could write up some tweets about data science?
Write two to start with and send them to me to check before you post

When you hear the word 'meme' you probably think of internet memes, but that's actually a hijacking of the word. It was coined by british evolutionary biologist Richard Dawkins, and it means the information version of a gene. Unlike genes which reproduce via DNA, memes reproduce by being shared from person to person.
Every creative work builds on what came before it. Listen to the White Stripes and hear Lenny Kravitz. Listen to Kravitz and hear Led Zeppelin. Listen to Zeppelin and hear The Blues. Steve Jobs stole the Graphical User Interface from Xerox. George Lucas lifted the scrolling opening credits from Flash Gordon. Iron Man was Marvel's response to the success of DC Comic's Bat Man, which in turn was heavily inspired by Zorro. 
If everything is a remix and nothing is original, maybe the way we create is all wrong. What if instead of staring at a blank canvas and trying to come up with an original thought, we started by explicitly copying instead? Rather than starting from scratch, we have a solid foundation to build on. We can “stand on the shoulders of giants” and focus our energy on improving and adapting memes that are already proven.

Meme Jacking

Okay, this is meme jacking. So you need to write some social media posts and you have a bank page ahead of you right here. Really hard to write a a hundred blank page. You needs a little bit of inspiration to get you going. So me and Jackie is basically taking something that already has. A lot of proven worth.
It's something that's already quite popular. So is there any filtered, like, you know, that people are, I've already found it useful in the past? And you're using that as a starting point. And then I kind of building on that. Kind of extracting the content and then making it original kind of innovating on it.
So you know, you could go into specific quotes. You know, there's a lot of popular quotes in here. If you think about it this is you know, these memes double validated, first of all someone thought it was good enough to write in a book. And then second of all someone thought it was going to have to quote from that book.
You can even say triple validated because, you know, even after it was quoted, then there was, you know, 161,000 likes. So the chances are, if you start with this and kind of start the concept behind it and then make it your own, make it. Then you're already starting with something that's always guaranteed to work.
So we're going to talk about data science specifically because you know, we are looking for that topic to write about that's what you know, if we're just doing kind of a general account we can just choose more broader quotes, but we want specifically stuff to do data science. Cool. So you get a lot of code sometimes they're big you know, sometimes smaller you typically want to just kind of search through to find something that jumps out at you.
So here we go. Sherlock Holmes, it's a capital mistake to theorize before one has data. So obviously that's you know, a pretty popular quote. Like he wouldn't want to just copy that, you know, it shouldn't just take that and say. I think it's stage to theorize before. You have data, you know, that's, that's like pretty close to plagiarism.
So you wouldn't want to do that, but you know, we're not here to copy something. Exactly. We're heads, you remix it. We want to make it our own. So let me think about what you know, what, from my experience what scenarios I've kind of been in where fear of. Before I had data and kind of make it unique to me.
And I think that that's really key to making it you know, not plagiarism but you're kind of using it as inspiration. So it's saying it's a mistake to theorize before you have data. So let me see. Here we go. So there was a, I remember there was a scenario when I was running my agency. I had you know, I was talking to one of the team members.
They had the ones to run retargeting ads for the client. And and you, they have the stereo already, that retalk team would work really well. Because the product was a really considered purchase. And you know, they kind of had all these theories as to why it would work but they didn't have any data.
So I asked them to look at Google analytics and we saw that the website was only getting, I think it was 3000 visits a month. It doesn't have to be precise or accurate, but it's close enough to illustrate the point. So let's say 3000 visits per month. And if you do the calculations with retargeting, you're going to have you know, probably 1%.
So 0.01 of the people click on your ad. Even if you reach all of them, let's just assume that we will. So 0.0 is your printer or one times 3000. So we're gonna get 30 clicks and then. Let's say it was like 0.03 conversion rate. So we're going to get less than 1 0 9 per month. So it's not really worth the time for us to set this up yet.
Right. So three targeting and the problem was I didn't have the data before we came up with the theories. So now let me just write this out in social media. I would client account the analyst, the account
pitching retired,
but he had all these theories.
one problem. When we looked at,
I only have 3000 visits, three K visits and.
That's the one.
Cool. Okay. So that's what we have for our post. Let me just see what the word count is. Okay. So it's, you know, it's less than a tweet, 280 we account for a tweet, so that's, that's good. That will fit. And we could kind of edit this down a little bit further now that we have a nugget of information.
But hopefully that gives you an illustration of how you think about this type of thing. And obviously it helps if you have experienced or if you can talk to someone. I who has experience you know, you could actually kind of turn this into a question that you ask an expert if you don't have that expertise yourself.
But you can see what we did here. We went from having a blank page you know, just five minutes ago to now having a pretty compelling social media. Post. And you know, if we know that this is probably gonna succeed because this is a very famous show, like Holmes quote you know, it's something that he said all the time.
It's a mistake to theorize before we have data. It's something that, that data analysts say quite often they include that in presentations and we've captured the spirit of. That, you know, in fact, we've, maybe we'll even have someone quote this back to us. We'll see. But but yeah, we, we were building this tweet on, on the solid foundation and we know that this is a meme or this is something that analysts deal with and data scientists deal with.
So we, we, we, we were pretty confident that this is gonna, you know, do good numbers but it's completely original, right? Like we didn't rip this off or, you know, we didn't just do a basic. We actually kind of related it to ourselves and I think that's the key. Hey, hopefully this was.

What would be a good example of meme-jacking for the following quote?
"Predictions have an expiry date. Action is needed before predictions expire."

Meme Jack this quote:
"The government are very keen on amassing statistics. They collect them, add them, raise them to the nth power, take the cube root and prepare wonderful diagrams. But you must never forget that every one of these figures comes in the first instance from the village watchman, who just puts down what he damn pleases."

Thanks for these, they're good to go!
Can you do 20 more?

Starting with a blank page

Your boss comes to you with a favor: can you start posting something to their twitter account? This isn’t part of your job, so you need to do more with less: what’s the quickest way to get good content ideas?

What can we post on social media?

You're planning to list your rental property on AirBnB, but first need to categorize the existing listings to see what 'meme's are common or rare.

I just heard back from the client and she has one condition before she'll sign off on the project
She wants someone from our team to actually go out to Europe for 6 months to make sure we understand how different the market is there
Travel will be paid, but you'd have to cover rent unfortunately the budget doesn't stretch that far.
What do you say? Do you want to go for it? If I didn't have kids I'd be jumping at the opportunity!

You're excited to go to Europe for 6 months, but you can't afford to pay double rent. So you decide to list your apartment on AirBnB. You ask a friend for advice on how to promote your listing to make sure you get enough bookings.

Oh I've got a good trick for this
It's a technique we use at Whipple for exploring the landscape for a brand (brandscape?)
We use it to figure out what attributes to talk about
Ironically the post I read on it is about AirBnB listings, which is why I thought of it!
It's called 'meme mapping' as in the original meaning, not internet memes
Memes as in genes, but for cultural evolution not biological evolution
Really fascinating topic...
Any way let me know how you get on

To stay consistent with consumer expectations, you first have to understand what memes are associated with your chosen product category. Here we're using the original definition of the word meme, coined by evolutionary biologist Richard Dawkins in 1976. "An element of a culture or system of behaviour passed from one individual to another by imitation or other non-genetic means." Internet memes are examples of memes, but then so are the different words, phases and design elements we use in our marketing.
Meme mapping is a method to systematically identify recurring patterns in competitive products. Once you have built your meme map, you can strategically decide what memes to incorporate, and where to differentiate your brand. The insights you gain from meme mapping are a great source of new hypotheses for conversion rate testing, or optimizing your ad campaigns with creative testing.
To start you must build a swipefile of competitive products, for example screenshots of their websites, marketplace listings or ad campaigns. Then you tag the listings with common themes until you feel like you've captured the important concepts. Finally you can do analysis of the memes you identified by seeing which ones occur most or least often, and incorporating performance data to spot opportunities or memes to avoid.

Meme Mapping

Hey, let's learn meme mapping  so we have an apartment in New York that we want to around and we want to know what listings tend to include so we can either copy it or decide to differentiate from it. So you can see, I've just done a search here on Airbnb for different listings. You actually get different results if you zoom in.
So I'll let you kind of decide what. View as most relevant to the apartment that you're renting. But you can see here that we have the listings in the side and straight away we can start to see some trends. So all the listings have beds in the picture. You know, some have different views of Manhattan.
It'd be interesting to see. You know, these get more or less bookings because you know, in some respects you can't see the apartment straightaway. You know, in other respects it kind of makes you interested in the skyline. So, you know, maybe there's some intentional kind of choices. You could also see, you know, segment these into higher alert price listings, or listings that have a set number of reviews.
Okay. So how do you do meme mapping? So I'm just going to bring up notion. This is the tool that I use, but you can really use any of these modern. Yeah, database kind of backed platform. So there's coder and air table. Some of the one I would be considered. Perfect. So first you want to make a table this would be a database view.
So here, I'm just going to make you in line, which is clinical. This is the listings. And then you basically just want to come in and copy the name. So click into the listing, copy the name.
You want to take a picture of the main image and just going to add this as a file a media. Okay. And then just let me select this and just save it to my computer. Okay. And then
add that picture there.
And then you just go onto the next slide. And keep going with it. So you know, this whole, we have the text and the image again and you just kind of keep doing that. You can, you can actually record multiple things about the listing. Maybe you want to go in and record these things here or record the description as well.
Because you can look at patterns in any of these you know, it's probably important to include the price and the number of reviews. So you can kind of add. As new fields to reviews and this case, it's a a number
Okay. So this one has 138 reviews, and those can be really useful for us to see, you know as a, kind of a proxy to bookings if, if something has a lot of reviews and it's probably pretty popular and we might want to copy some of the memes that they're using. Okay. So once you've got a few tagged and it's a pretty arduous process, you could either use web scraping to do this or do it manually.
Like I just did. Or you could pay an intern or virtual assistant to do it. But I actually, especially with something that's really important, I like to look at the listings myself just because it gives you a real sense. You kind of pick up through osmosis, what trends are popular and that really helps you do the analysis afterwards.
So even if you do pay someone or, you know, kind of. This process is probably worth just doing a few yourself just to pick up on the trends. Okay. So I'm just going to copy that in here. And this one has
138 reviews.
Okay. So once you've got the different listings, and I would say you probably need maybe about 20 you know, 10 to 20 to start noticing patterns. Then you want to start adding the tax. So with this one you know, for the image, that's a living room living room tag and then this one is a bed bedroom.
And you want to just keep going through like that. You could also tag the text as well. So this one says beautiful. So we can also tag that with beatable.
And then you've got tags for the text as well as as well as for the images. This one is sun drenched. So if you go through and you notice that there are a lot of. A lot of these with the mentioned sun drain. So I mentioned the sun or you know, others that mentioned beautiful and they have higher reviews then that could be a really good sign.
That that's a word you want to mention in your listing as well. Okay. Hopefully this is helpful and good luck out there. Me mapping.

What would make a good source of competitor assets for meme mapping?

What memes stand out from the title of this listing?
"East Village, private room with free breakfast"

Why might it make sense to use a meme in your listing that rarely appears?

Meme mapping airbnb listings

You've been given an assignment abroad and plan to rent out your apartment over the summer. What should you include in your listing?

What attributes should we talk about?

Your company is looking for new opportunities to cut wasted ad spend. If you knew what words were working in your ads, you could make changes to improve efficiency.

You've probably heard we're on an efficiency drive this week

The CEO has asked us to think creatively about areas we don't normally look at

I was thinking that although we do analysis on an individual ad basis

We don't really look at creative performance across ads based on what words we use in the ads

Did you want to take a look at that and see what we can find?

If you can do an NGram analysis of the ad account, we should be able to spot some opportunities to improve

Thanks in advance!

British advertising tycoon

If it doesn’t sell, it isn’t creative.

The standard way to do creative analysis is to look at individual ads. But the same ads or similar ones can exist across multiple campaigns and ad groups, so you'll be missing broader patterns by looking at one ad in isolation. NGrams are a natural language processing technique for breaking text into 1, 2, or 3+ word phrases. 
Words can have a lot of meanings, so you want to look at them next to other words as well as on their own; maybe ads containing “3 star hotel” perform poorly, but “4 star hotel” gets clicks. You wouldn't see that if you just looked at the word "star". Looking at short phrases within ads gives more insight into creative performance while still being broad enough to work across the whole account.
The way to do analysis by ngram is to tag each ad with its ngrams, then add up the clicks, impressions, cost, conversions, etc of each ad containing the NGrams — then you can calculate the click-through rate, cost per click, conversion rate, CPA or ROI to see the impact of that NGram across ads.
If the performance is good, you might want to make new ads around the NGram; if the performance is bad, you might want to rewrite your ads to exclude that NGram.

NGram Analysis

Look at the code above. How would you print the 1-grams, 2-grams and 3-grams for the given text?

Hey, I'm going to show you how to use a for-loop to run this function for each of the one gram two gram and three ground keywords. So first of all, I created four loops are going to say for ground. And then you to say range one to four because their range you know, start at one and the no good to toot and they get to three of the, no stop before four.
And then this is. All right. And then we just want to run what we're going to run for each loop. So, first we want to say print F string, which is just a way to insert code into a string. Sylvia say you know, this is going to be ground, actually, let's make this easier. And then dash
kind of code and then we want to create the actual and grounds. So you can see, we have the function here. And then this is the end. So that means it's going to start with N equals one and then an equals two equals three. And then just to add another space. So the, so it's just a bit more readable and let's run that and see if it works.
yeah. To have the one grams it's really like facts, power, pretty awesome. The two grounds, which are the combinations of two and phrases and the three grams, which a combinations of three word phrases.

For Loop

Take the above script which creates NGrams for Headline 1, and run it for Headline 2 and Headline 3. Walk us through your process.

It's important that you clean the data first before running NGrams analysis. You should remove punctuation and stop words (commonly used words that won't add anything to the analysis).

It's a T course. So first we're gonna run the first cell which is importing with libraries. And this is just a shift in enter. It might take a little bit of time while it connects to the virtual computer that it runs this code on for you. Second, gonna click to authenticate. And you just need to click the link there and then choose an account.
And then, wow, that's going to give you a code to paste in here, and this is how you connected your drive. And we're going to use that later. So just going to skip some of this stuff, this is the initial and grounds function. One of the things you do need to do is actually write. If I find this function here, so actually just run that area.
Are you going to just make sure that's all working? You can see that it's working. Okay, cool. Now we're going to import the data and then again, I'm just putting shift and to you know, you can put your own data in here as long as it's open for sharing, where you have access with the account that you just authenticated.
And what this does is it just pulls in the data from the spreadsheet and creates it into a data frame, which is the, you know, Excel for Python coding, essentially. Course. So this just cleans the data. No, we have you know, all kind of different casing like capital letters here. You know, we have a lot of stock words.
You know, we have words that you know, punctuation, et cetera, that we don't necessarily need. So but we also want to clean out the dollar signs here just so it makes us a number. And so we can sum up the spend at the end of. Cool. So that's what that could does, and don't worry too much about it.
If you don't understand it. Processing the in grams is pretty straightforward. You know, here, we're just using this and grounds function. The way we're applying it is with a Lambda function. This is just a way of, you know, taking the column. And this is a new column that we're creating code know column.
Then brackets kind of and the grams, you can see a headline. One, one grams had like one, two grams and you know, to assign the value to that column, we're just applying a function to the original column, head Lambda just means run this function in. It's basically a way of creating you know, creating a function or running a function without giving it a name.
And text is just a variable for that function. So the value of this cell is gonna pass through as text. So actually in this case, it's the headline one. So AI powered product recommender would be the text and get angry. And so we run on. And they would do this simple timelessly for every cell.
So this is a really efficient way of doing it. And then had, is just sharing, you know, the, the results of that. So we can see you know, we have the Ingrams here. Okay. So we want to process that for every text. So, and we already have these defined here and ad copy columns above. So that was up here.
Or is it yeah, here we go. Ad copy columns headline one headline, two description. So you know, you can modify that for your own data. But we want to loop through the ad copy columns and then four and in the range, a one to four. So this is, you know, an equals one equals two equals three, and then it stops because it doesn't get to four.
Then we're going to create these columns. So. You can see, we don't just have the headline one, one grounds, two grounds, three grounds was I have headline two and description. Cool. And then this is how we get all the data together at one place. What this is doing is we're just taking it to the columns, have just metrics of impressions and clicks.
We're looking at click through rate. I was there. I didn't have that many conversions in this dataset and this is an right to us. So it goes through each row individually. It gives you the index, which is 1, 2, 3 here. And then the row as is, is actual data for that row. And we're going to get the metric values hit.
And then for each column in all names you can see here. We created all names up here. Every time that we created one of these in grams columns and we added or names to that list. So this is going to get all of the and grounds columns and then for gram in row column. So for the column you know, that we're iterating through at that time in all names, and then we're going to get, we're going to create new.
So, what does all this do? It basically brings all the data together so that you know, for each and Graham you're kind of duplicating the impressions and clicks and kind of adding a new rogue. So what we're going to be able to do at the end is kind of, you know, merge all this stuff together and then see the average performance for each and Graham.
And the way we do that, like right now we have AI powered, but actually AI powered might have appeared multiple places because it would appear for each individual ad that had it. So we just need to do a group buy and we're grouping by Anne Graham and then somebody. And then we're adding the CTR at the end.
This is the clicks divided by the impressions multiplied by a hundred. And then we're sorting the values by impressions decent. And then just getting the first 20 results. So this gives us very clearly here, the performance by and Graham. And we can see that, you know, some of the in grams, like product have lower click-through rates, post 63 and then some have higher click through rates like the keyword stores or acuity content.
I think it's really important to also kind of look at the data and see you know, Ads are actually running because that will give you a bit more context. So if we click in here, it's just a control click. We can see the underlying data here, so we can see that, you know, there's certain ad groups with good for SEO or very few users.
And then we have like the QL automatically showing up here. AI powered is here as you can just kind of look at that data. I understand the account a little bit more before. You know, some of the recommendations at the end and then finally, what this does is just you know, for you a benefit if you were running this analysis on your own data this just saves the file to CSV and then downloads it locally for you as well.
Hope that was useful.

NGrams Analysis

What recommendation would you give after looking at this analysis?

Thanks so much for your analysis
The insight into how 'content' is better than 'posts' is a real killer
We haven't been applying these keywords consistently because we weren't sure what worked best
I'll share this with the PPC team and no doubt they'll come back with more questions
Thanks again!

What words are working?

You want to identify wasted ad spend by checking which words work best in your ads. Looking at your ad account, it’s hard to see patterns of what words are working. How do you extract out the keywords in the ads to get a better picture of performance?

What words work best in my ads?

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.

Correlation isn't causation but it helps

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.

Courses - It's time to grow👇

Learn the skills to be a Data-Driven Marketer