UPSERT OFFICES – NEAR THE END OF THE QUARTER

It’s time to set next quarter’s goals, but how do we account for COVID? Performance was down and everyone missed their targets, but if we set the goal too low as the economy recovers we’ll go bankrupt for all the bonus payouts! How do we model what’s realistic?

We’re setting quarterly goals by the end of the week and we need your submission
Leadership won’t approve anything they deem ‘unrealistic’
That’s based on their view that we’re exiting the pandemic and shouldn’t see further lockdowns
Though I have an agreement that bonus schedules will be revised accordingly if we do miss target because of COVID again
Of course the last few quarters have been unnaturally down thanks to the pandemic
What I want to see from you is what numbers would we have hit if we control for COVID?
If you can run a regression analysis and get back to me end of day that’d be great
You can get the Google Mobility data here:

The workhorse of analytics, Linear Regression, is a tool every marketer should have in their toolbox. It’s unreasonably effective for its simplicity and ease of use. Despite what you might fear, you don’t need to know a lot of advanced statistics to make use of it. Excel and GSheets have you covered with the LINEST function, which can correlate multiple variables and give you an estimate of their relative impact on performance.

This is done using the ordinary least squares method, which is relatively easy to visualize with two variables. If you had height and weight, and plotted them each on one axes, you’d see they were correlated and could draw a line of best fit between them. LINEST is doing this but can handle multiple variables for you, providing coefficients that can be plugged into an equation to estimate what performance would have been controlling for each significant variable.

One common use of linear regression is to estimate the impact of one aggregated dataset on another. For example if we wanted to know the impact of COVID on the number of sales leads we captured, there’s no way to track the impact of COVID directly. Using Linear Regression, we can associate the spikes and dips of COVID with spikes and dips in leads captured, to draw a picture of the relationship between the two.

Linear Regression

Hey, I'm going to help you calculate the impact of COVID. So what we're looking at here is Google COVID data. This is from Google maps. You can get this from COVID mobility. On google.com. I downloaded the global CSV, but it's a huge file though. So what I recommend is you just open that in a text editor rather than opening it in Excel or Google sheets, and then just delete all the lines that you don't need.
So you're just left with the one country that you do in this case. I've got United States. Cause otherwise it's like. Millions of lines it's ever gigabyte data. So a crash, a Google sheets, if you try to put it cool. So I'm just showing you a few of the different indexes. So one thing you can see here is parks.
This is the percentage change from baseline. So what it's saying is there around July then, you know, people like 50% more going to parks than they were at the start of COVID which is February 25th. The the other ones here, which I think will be more useful because parks is obviously very seasonal with this.
The peak is always in July. The the more seas, the, you know, the, the less seasonal more useful variables are in this case transit stations are, we can see that there's a huge drop-off when there was the initial outbreak of COVID and it never really recovered. People are not going to try the stations as much as they used to.
But the there's a real problem with that one because it's very spiky. So you can see. That is a very clear, you know, day, a week trend on the other weekends. Then there's a dip but there's a lot of transit for commuters. So that's gonna mean. And muddy the water with our analysis. We're going to kind of correlate to the day of, we tried to, rather than the fact that it's COVID so we could switch this out, but actually we're going to use in this case of retail recreation percentage.
So not only does it show the, you know, correct dip at COVID, it also has a few severe spikes when there was more of a panic. Which I think is going to be helpful. And there's a way less pronounced day of week trend. So there is something still, but, you know, it's, it's much smoother, so better to use that out of the box, I believe.
Cool. So the other data that we have is Facebook ads states. Which is just your spend conversions, et cetera. And then Google ads, data, which is the same thing. And I've just combined it together into the sheet here. So by date, we have the number of conversions from Facebook and Google ads.
The, amount that we spent on both of those platforms and then also the. You know, the, the retail mobility trends. So you can see that hit that's going up and down as we go. Okay. Cool. The way that you do linear regression, which is what we're going to use is you type in lit X, which is the function.
And then you want to select the thing. You're trying to predict a, turn this, getting a press control, shift down where the conversions. And then that's gonna suck the fourth row. I said, it's just a shortcut. And then we're going to now select the Y variable. So these are the things that we think will predict him versions.
In this case, we have a pretty simple model, which is two things I'm gonna put one and one here. This is just to add an intercept, which is the practice, and also to give them a boost of list when you hit enter on Back, I believe you have a press control shift into I think it's an array formula.
But you should be able to get this unintelligible result. So don't worry about NAS, anything like this. I'm just going to label these for you. So this is the intercept coefficient. This is what would happen if there was no COVID and so we'd get on average 2.6 leads. And then these variables actually flipped.
They go, they go backwards. So this last one here is actually the first one we chose to cost. And then this retail so how do we interpret this? This is saying that for every 1% increase in retail traffic we got 0.01. More leads. So retail traffic's come acting as a proxy. It was the retail traffic stop causing the you know, the increase of leads, but it's a good correlation with it.
And then this is basically, you know, the same thing, you know, for every one $1 we spend on ads we're getting the 0.02 leads. So that's actually just the inverse and CPA. If we're to get the CPA, we just do one divided by that number. And that Sonya it's on average, the CPA was 37 with $38.
Cool. So how do we work out the total impact of COVID to pull this cost of COVID. And the cost of COVID will be how many leads we lost. So first we need to select retail and press four to make that an absolute reference, or you can just put dollar signs in front of it. And that's let's just drag it down without changing the reference.
And then we're going to do it and multiply it by called D, which is the COVID retail figures. So to attract this down
This is pretty low. Say maybe it just makes sense to scroll down to the bottom. K.
So that is a, the column that tells us how many leads. We gained a loss based on retail traffic. And you can see here at first there was more retail traffic as we did better that we normally would expect. And then we started to see an impact of leads in some of the peak periods where losing half a lead, maybe even a full lead every day.
So a huge issue. So if we sum up this column costly privileged here, that is going to be leads. So control, shift down. That's what I just said there. That just lets you it's like the whole. Really quickly. So here we go, we lost 134 leads. How many leads did we get total? That's just this again, control, shift down this column.
We got 3,744 leads. So leads without COVID. So like, if COVID didn't happen, how many leads. That would be the number of leads. We actually got class this, a minus figure here. So we actually would have gotten oh, sorry, there were road. There'll be the number of leads minus this month.
Sorry. So we'd got more leads without COVID there's 300 and sorry, 3,878 leads. Okay, cool. So that's if COVID didn't happen, this is how many leads with. And therefore we want to work out what our CPA would be otherwise so for the CPA we just need to get total costs.
Okay. So you're going to slack that so that's a total cough.
And then we also want to get just the leads that are I guess attributed to the advertising spend there's I guess two ways we do it either. CPA or, or just a CPA for advertising. So let's just do blended because that's what the business really cares about. And, and you know, it depends if we're sending them because of budget, but I blend the first calculated CPR that's believed or sorry, I'll, I'll call it CPA.
Cause it's what you call it. So the blended CPA. That divided by leads of 1596. And COVID is the cost of by pivot. So in this scenario we would have been. Know 3% better off basically a 3.5% better off. So a relatively small impact to the overall business because this is a, you know, B2B business is not, can be a huge one.
But that tells you a lot because you know, obviously sales people like to complain and they might say, you know, marketing teams like to complain. Everyone likes to have a good excuse to blame something on external factors. But this, at least this model is telling us. Actually COVID was like a relatively small impact on the blended CPA.
So when we're setting goals we should set them based on that, you know, that expectation of whether there's going to be COVID or not. And, and, you know, that gives us kind of a 3%. We've got real for this business, but for yours, it might be 20%. It depends on what industry. But now that you know how to do this you can build a more complex model.
This model right here is not really, really accurate because rather explaining 24% of the variance. This is the are two figure. So that is basically saying that there's a lot of other factors to also, you know, we should incorporate and we can do some more investigation and build maybe a full VOC team mix model if if we're so inclined but this should give you at least a little bit of a steer out of whether COVID was a really big thing for your business or not.
And then you can quantify exactly how much and, and they use that for year four forecasts. Hey, hopefully that was useful.

What was the full cost of COVID in terms of lost leads?

How many leads would we get per day if there was no COVID and no ad spend?

What was the R2 value of the regression i.e. how much of the data could our model explain?

Was COVID a significant impact on this team’s performance?

How could we improve our confidence in this model?

How do I control for COVID?

External variables often affect your performance more than anything you did in marketing. Yet most marketing reports are missing the impact of macro factors like pandemics, the economy, and seasonality. Failing to control for significant variables means missing your targets in downturns and overcompensating your team in periods of high demand.

Vexpower Learning Certificate of Completion

Congratulations relieved-AI-48 💪