We're spending about $3,000 a week on brand terms in our Google account
The CEO is on an efficiency drive and wants us to turn this off
I do wonder if these clicks are incremental: if someone searches for our brand won't they just click on the organic result anyway?
Just to cover all the bases can you do some analysis to estimate the impact?

Should you bid on your brand terms? When someone searches for the name of your company they already know who you are. They had to learn your name somewhere, so it's highly unlikely that the Google ad they click on after searching your name deserves 100% of the credit. If you can turn off ads for brand terms you can usually save thousands of dollars to redirect into generic product terms or other demand-generating channels.
That said, there are some advantages to brand bidding. For example if a competitor is bidding on your brand term they might steal some of your best customers. So bidding on brand can defend your terms against competitors. In addition when running brand ads, you have more control over the message and destination. You can change the copy of your brand ad in an instant and A/B test different messages, which you can't do with organic search. Determining whether you should bid on brand terms or not requires a deprivation test, to find out how many of your sales are incremental, and how many would happen anyway.

Brand Bidding

All right. We've turned off our brand ads. How do we interpret the results? So you can see the ads are running for quite a while. And then around here we turned them off. We went from 56% share, 14% share, turned it off halfway through that day. So the share quickly dropped to zero as you know, all the kind of clicks from the campaign.
So to get registered in analytics and you know, there were no more. Euro codes trading around. So specifically paid traffic is from analytics. Anything that came through with a brand new campaign, a UTM a URL parameter, and then organic traffic is anything to the homepage from a organic SEO.
And if you add them up together, that's total brand traffic. Cool. So how do we interpret this analysis? So let's look at let's, let's actually kind of rank this or show this visually in a chart so you can see what effect is happening. So I just selected it and then I went to insert chart.
If I get rid of total traffic we want to share this as a stacked area. And that's going to kind of add the two together. So you know, this red line here is the, some of the blue and the red. So, so you can see total traffic was like, you know, pretty spiky. There was a dip here, right when we turned things off.
But then there have been a few other spikes. So actually it doesn't look like, you know, the total traffic is down that much organic traffic actually. Pretty considerably compared to where it was before. So it looks like not all of the clicks from brand were incremental, which is exactly what we expected to see but how much?
So first we need to choose two different periods. We're going to work out the, to work out the traffic before the traffic after, and there's going to be averages. And then we're going to find the difference between. The other thing we want to be able to do is we'll get paid before. So how much paid traffic there was before?
And then paid incremental. So how much of that was actually incremental? And then that, that should give us a good idea of what we want to you know, w what we want to decide about whether it's attendance back on. Okay, cool. So traffic before let's choose two equal periods, we're going to start on the Monday here at the beginning.
We're going to go Monday to Sunday, and then Monday to Sunday, again, we're going to take two weeks and that kind of balances out any you know, in any ethnic kind of fluctuations in the data Yeah, we don't have like say one weekend in this period and then no weekends in the period after because then if there's an effect on the weekend, you know, traffic's higher then it's going to bias the results of a test.
We also don't want to go straight from this Monday because you can see, we have at least one. Where the Brenda ads was still on and also you know, you can take, it takes some time for behavior to filter through the system. Just want to give it some breathing room and kind of you know, look at the data after the results of switching it off settled.
So we're going to skip this week and we're just going to go Monday to Sunday. Two, twice his or to Sunday and then Sunday again, and this can be a test period. Cool. So let's look at the average average traffic to that period per day was 11,000. I'm just going to have filled that out as a number of thousand 4 95.
And what was the traffic after. And that's this red period here.
Okay. So that's 9,961, as soon as the difference she wanted to take the 1,061 divided by the previous number and then minus one. And then we just kind of format that as a percentage, as a minus 13%. So we actually lost 13% of arch. Okay. So so that's interesting, right? It's not you know, not a huge drop and it might actually be worth it if that's although it was incremental, so let's work out how much you know, specific traffic we dropped, like how much traffic we would've got.
If if we had kept paying. So so if we had the average of the paid ads before and say how much traffic. Before on an average day for paid ads. So that was 6,000. I'm just gonna kind of copy this formatting here. So 6,166, that was how much traffic we we would normally get on an average day.
And then we want to figure out basically what is the difference between a before and after? So are we going to take 11,405 minus 9,961? So we we, we lost 1500 clicks per day, so that was the part that was incremental. So so pay. In this case, if we were driving 6,000 paid clicks that, and only 1500 of them, it was incremental.
Then the incrementality for that channel. About 25%. And that means, you know, if we were you know, previously spending you know, 10 cents a click then in reality, our true you know, cost per click was actually four times higher than that. You know, so 40 cents a click, we can figure out whether that's worth it to us or not.


What would be the best control and test periods to choose to determine the before and after effect of a change?

Upsert spends $3,000 per week on brand ads, what is their effective incremental cost per click?

What would be a good argument for keeping brand terms running even if it was ROI-negative?

When people search for your brand term, should you run an ad? Performance looks great but are those conversions all incremental? Let’s run a deprivation test and find out.

Should we turn off brand ads

Your boss complained about their ads matching to 'vegan hotdog', so you need to take a look at the search terms we're matching to and add negative keywords where relevant.

Ok I get it
Google's matching isn't the greatest
But surely it's obvious we shouldn't be bidding on 'vegan hotdogs'!?
I'm not sure why you didn't set this up as a negative to begin with
What other bad keywords are we matching?
Let's get this rectified
Can you do a search query report?

Search query reports are among the highest value things you can look at when running a PPC account. Google is notoriously unreliable when it comes to matching to relevant keywords: what does it matter to them if your ad spend is being wasted? This is something you need to monitor religiously yourself to make sure you can drive performance.
Google and other search engines usually provide some visibility into what you matched to. You can go through these matches and then find the ones that aren't relevant. For example there are some keywords that are completely irrelevant ("vegan hotdogs" if you sell beef hotdogs). There are other negatives you want to add to divert spend to the right place. For example if you're matching to a competitors name in your generics account, you want to add a negative at the campaign level in generics to divert spend to the right place.

Negative Keywords

Hey, let's learn how to do a negative keywords report. Okay. So this is the search terms report that you typically get from Google ads. You can export that from the platform and it gives you you know, what you match to what, what search terms you matched to what ad group that was in that the match occurred.
And then some performance data as well. For this account, we don't have conversions. It's just clicks. Cool. So what do we need to do? We need to go through all the chemo. And we need to see which ones are relevant to to the client. So some things will be relevant. Some things won't be you can see we've got some, you know, competitor terms in here you know, bacon wrapped, hot dogs you know, if they don't sell bacon, wrapped hot dogs.
And this is a good example of something we want to decrease as a negative. And if you add a negative or you can add it. Of three different stages. So one is the account level and that's something that would be irrelevant to the client in general. Like it's something they don't sell. It could be the campaign level, like you know, maybe we don't want it to match to the hotel campaign because it's a competitor's name and it should match to the competitor.
Campaign instead or it could be at the ad group level. So maybe it is, you know, in the generics campaign, it should be in that, but it shouldn't match to the ad group. Hotdog in particular that's, what's called a cross matching negative and that's basically just like redirecting spend to the right place and making it more efficient that way.
Because then you're going to be able to show a, you know, be able to show an ad that's more relevant to what the purchase, you know, person's search. Cool. So when I'm doing this, I tend to make two more columns and one is the negative or negative keyword, I should say. And then the other is the negative level and the negative level is going to be one of three things.
Sorry, I'm just going to if we. Kind of click there and then control down. That gets us to the very bottom of the field and then we can press control, shift up and then hold shift and then press down. Okay. So what I'm doing there is kind of navigating around a much quicker. I can select the whole thing much easier.
Okay. So. We're going to go to data validation. And then we're going to say, we want a list of items and we're going to say, is this an account level, negative, a campaign level, negative, or an ad group level negative. And they're gonna hit. And then that just gives us this dropdown. So we can say if there isn't a negative that we're adding what level should we add it?
We can also add some additional formatting here if you want to. But I'm not going to bother for this tutorial. Okay. The second thing we need to do is let's select everything. So if I click in here and I press control a that's like the whole data sheet and then I want to go to data and I want to create a filter.
And the reason that we're going to do this is because we want to sort this by impressions. We're going to go short so the keyword with the most impressions is at the top. You can also do, you know, the most cost. But I, I, you know, almost clicks I prefer most impressions because it gives you a good idea of keywords where you're getting a lot of impressions, but it's not actually relevant.
So a really good example here is hot dog cut for sale. So this is something that we don't want to bet against because when selling hot dogs, we're not selling hot dog cuts. So in this case, we're going to add it's actually going to say for sale, and this is a phrase match negative. I almost always just use phrase match negatives because then it's a much cleaner you can kind of get rid of any time we match to for sale.
Because You know, if the keyword contains for sale now we're not going to match to it. So yeah, if someone searches hot dogs for sale, we're going to lose out on that. But I don't think that's a particularly big keyword, so I think we should be safe here. And that is an account level negative.
Cause it's something we never want to match to. Another example could be you know, if we look at so like Feltman's hot dogs maybe that's a competitor Feltman. So we shouldn't be matching to the generic campaign. We should be matching that to the competitor's campaign. So, you know, we can add that keyword in later, don't worry about adding keywords yet.
Let's just add this, you know, Feltman's you can just do Feltman in this case because then it will match to any kind of variation. And then this is just a campaign logo level negative. Because that's something that's going to matter. You know, it shouldn't be matching to the generics campaign at all.
There's a competitors campaign. And then let me find an example of something that, you know, should match to the generics campaign, but not specifically to a hot dog. So let's say if we look at like, Be Franks. Maybe we want to specifically call out the fact that we use and our hot dogs.
And in this case we want to kind of create a new ad group called beef. So if we if we, but that is beef as a negative and then put that at the Abbott group level then we know that. Beef Franks is you know, is something that is in the generics campaign, but it shouldn't be matching to this broader hot dog term.
Okay. So that's the three different examples, you know, account level negatives. So these are things that are completely irrelevant. Another one is campaign level negatives, which is something that's relevant, but not specifically for this campaign. Needs to be in the competitor's campaign instead. And then this is an ad group level negative, cause it's still relevant to this campaign, but just should be in this separate ad group where we can specifically call out in the ad that it's a beef hot dog.
Okay. So the other thing we need to do is add up how much spend we've saved. So the way that you do that is if we say, you know, equals some F. You can see the sum if formula is the range criteria on the summer range. So the range that we're matching to this is gonna be. And then you're just going to select that and risk control shift down.
That's going to select the whole thing. It can compress F for to make that an absolute reference. So it's not going to change if you drag that around. And then criterion we want it to match the QA. Count. And then the sum range. If if that matches account, I, then we want to some the same row over here for costs.
So again, if we just press control shift down, oh, it didn't work for some reason. You know, the other actually the other way to do this, if you just copy this. Range and then you paste it and then instead of E it's raw, it's called I so just change that. Okay. And we can see that we have one keyword that we spend 125 bucks on.

So that makes sense. So that's how you sum up that if we add another. You know, account level of negative here, we have your Turkey. If we don't sell Turkey hotdogs. So add an account level negative here, and we can see that now this is updated and it includes the spend for both. So you could do the same thing again, if you want to add up the campaign and ad group level negatives, and that will tell you how much spend was redirected versus how much spend was wasted entirely.
If it's an account level negative, then it's wasted. Okay. Hopefully that was useful. You know, it's a really tedious piece of work but you know, but it's probably the biggest thing you can do on a week to week basis to improve the performance of your Google ads campaigns.

Add negatives for all irrelevant matches at the account level, assuming Dogshed sells halal beef hot dogs. How much money have you saved?

Add negatives for any Campaign or Ad Group level mismatches. What amount of spend will be redirected?

What should you be careful about when adding phrase match negatives (i.e. keyword contains "for sale")?

Can you do a search query report?

Your boss Googled "vegan hotdogs" and saw one of your ads appear. Just one problem... our hotdogs ain't vegan. What other irrelevant terms are you bidding on? 

What other bad keywords are we matching?

GoolyBib Offices – The start of the year

The analytics team is complaining again – they’re constantly finding misspellings and non-standard naming conventions in the tracking parameters you’re using. Your boss calls a meeting with one topic on the agenda: standardisation.

I understand it has been busy, and people make mistakes
But just the other week I got a report where the numbers were all wrong because someone forgot to add “FB” into the filter in analytics
We missed out on 20% of the actual Facebook traffic
This is just one of a number of incidents, and the analytics team are complaining analysis is taking too long because of it
I need you to make a concerted effort to standardise your work
Let’s create a template spreadsheet, a URL builder, where we can ensure everything is standard
Then if we use that religiously going forward, there’s no way errors can creep back in
Get this done by the end of the week and send it my way

In analytics it really is a case of garbage in, garbage out. Most analytics platforms record the source of a session once, and that data is impossible to change once recorded. What that means is that if you ever make a mistake in the spelling of a tracking parameter, or use a non-standard name, your analytics team will live with that mistake forever. For example “facebook”, “face book”, and “FB” are all treated as different sources, so every time you want to report on the performance of Facebook ads, you have to remember to filter for all three of these names.

The best teams adopt a strict naming policy in order to protect against this. By standardizing the names you use and recording them in a spreadsheet, you minimize the chances of a mistake, but also make it easier for analysts to later unearth what names were used and what they mean, making their analysis much easier. When enforcing a naming protocol in a spreadsheet, you can even make the task of constructing the URL easier, because you can use data validation and dropdown menus to make it so you only pick valid values.

The way the spreadsheet works is two fold. One you make a lookup sheet with all valid values for source, medium and campaign. Second you use the CONCATENATE formula to glue everything together into a valid URL at the end, for the ops team to copy and paste into their campaigns. The system I’ve found most useful for adding granularity to the codes used is an audience, creative and message ID system. Each utm_campaign is a combination of these IDs, for example a1-c1-m1 refers to audience 1, creative 1, message 1. This maximises flexibility while also keeping the URLs relatively short and uncomplicated.

URL Parameters

Hey, I'm going to teach you how to build a URL builder. So this is a way to keep all of your tracking consistent and make sure you're not making any mistakes or kind of reusing. Or tracking codes when you're adding the UTM parameters to your URLs. So for example, who running ads on Facebook you want to put the base URL in here and then choose the source, which is Facebook choose a medium.
In this case, I call it CPM because that's how it's bought a CBC would be good. Google ads, for example or, you know, whatever platform you're buying on clicks. You could also have like CPA, which is you know, where you're paying just per conversion. And then this part here is really key.
That's the the audience ID, the creative ID and the messaging ID. So that is a system that I developed when I was working. My agency and we use this across, you know, 200 clients and found those the simplest way to do it. But it still have a lot of information. So the way it works is when you want to run a new audience.
So maybe it's like. Likes and interest audience on Facebook for people who only, like, let's say it's a behavioral audience, like, you know, half one child, you can target them on Facebook. Then you just add another ID. So each ID response to a specific. Audience and then you can choose from existing creative or existing messaging.
This is the ad copy, creative as the image or video. So this is a way to keep track of, you know, what audience you're running against, but also what creates. Image and then what message. And when you're reporting at the end you can look up these ideas and kind of see what they mean and which, you know, which messages are working well with, which creatives or, you know, which creatives are working well on each audience, et cetera.

Cool. So so that's how, that's what we're trying to build. And it works by two things. One is data validation, which is how we get this kind of drop down. And then the second is concatenate. At which is how we construct the URL at the end. So if you can see ad say we're running another sale and this one I'm really announce on MailChimp email.
So that's a source MailChimp, medium email. And then let's see what audiences we have. So newsletter audiences, a six. So we may say this is a six. And then we have cell banner. When you use that as the email assess, see it's a C5. And then in terms of messaging so this is like winter holidays.
Let's say let's call this like a, it's like 13 and this will be like new year's three out of that. And this is M 13.
I mean, he did the typing in or choosing the dropdown. Cool. So you can see it's constructed this year, our just because we've kind of copied down. And and it's added, you know, UTM source cause MailChimp, you tend to be to me cause email and UTM campaign equals a six C5 and three. So that's what we're trying to build and that's the concatenation.
I said, let's do that. And I'm just going to show you roughly how to do it. I'm going to leave you as an exercise to do the full thing. But let's just copy you know, some, let's just copy the base you're out here. And then we want here it's like medium and then This is critical. The, like this actually started moving this ID and I'll let you guys do the creative a message.
But I'll also show you the concatenation as well. So source we're gonna pull, this is a data validation. So we need to pull it from somewhere. So if I make another If I make another sheet this is going to be a reference sheet. And we're going to say here or here, or a medium surface sources, Facebook and Google.
And really let's just say it's just CPC. And then we have a audience audience and let's just say we, we have just one audience And then this would be I've been there. We talked to you can go as granular or as broad with your ideas as you want really depends on your needs. I would say only create ideas for things that you're testing that you care about. You know, otherwise you can have like hundreds of these ideas and you kind of get lost.
So start simple and then kind of branch out. Cool. So in order to do the validation, I want to kind of choose the cells that we're going to add the validation to and go to data and then data validation. And then we're going to click this to select a data range. And this was the source. So we're going to just select these.
But we actually want it to work like the other one where as we add more sources, they pop into list. So here, which you can delete that three and then have the range all the way down to a thousand. Hey, and then we're going to save and there we go. Now we have the drop-downs and we can choose, you know Google and if we put like take off from there, it's going to give us a warning to say invalid, it's not in the range.
So that helps us avoid making mistakes. If we type for each book, you know, that's not the treated the same analitics as Facebook when they're spaced. So that would be invalid as well. There we go. Okay. So that's how you do the data validation you know, for medium we can do the same thing. So.
That is data, data validation, and then we're choosing the range, which is this. And then I think that. This is going to go all the way down to a thousand of TG updated. If you, if you need more once you've done that, you gotta just copy and paste the cells. And then for data validation. Oh yeah, we're going to meet the audience here.
We just need the audience ID. So let's just do the validation for that as well. There to validation. Choose from the rain. And then we're just liking the audience IDs. And the labels on this side is really just to look up. So if we're doing a report in the future, we could do a V look up against a one to pull in that that's like a retargeting audience.
But just using the idea. Always keeps the Tim campaigns within limits. It keeps it short, so it's easy to copy and paste and use. All right. So how do we construct the URL? So this is the tracking URL, and obviously we're going to, you know, in the exercise, you're going to need to add a creative.
And a message into here as well, but let me show you how this works. So it's called concatenate. And the way it works is you can just concatenate as many cells as you want and just separate them by commas. So if you just click that and then comma, and then. Then there's going to concatenate the two things together.
And you can see that that kind of makes a nonsensical URL it's dot com Facebook. So what we need in between is the, the strength which is the UTM source equals and then comment, and then you can see it to UTM source equals Facebook to the first. The first parameter needs to have a question mark in front of it, but there can only be one question mark in the URL.
So make sure you don't mess that up. If your base URL has a question mark, then you actually want to kind of use an emphasis on there. But let's join on the next thing. So in this case the next thing we were using our percent is UTM medium equals, and then she's a medium. Come a medium. And then last one is UTM campaign equals.
Oh. And I missing the emphasis on that. And once you get the tray, like content's working, then you never really have to think about it again, which is really part of the benefit. Cool. So that is how that works and you can see there's nothing in those promises right now. Cause you haven't selected. So in this case what was the medium CPC?
It looks like the data validation didn't work here says she to be 1000 Arivale we didn't be to choose a range properly. So let's just choose that to be, to, to be.
And if we go back now, we should have, yeah, here we go. So CPC and then companies that one down as a CPC as well. Cool. And then we can use audience one and then put is to just copied and paste that as well. So you can see, you know, when we update it here, audience, one changes to audience one as that's working.
If we added another audience, which is I don't know let's say interest then then it would show up as an option now, and we can see when we change it, if there's take three. So the rest of the exercise is you need to add another one here, which is creative ID and also the. And message ID for the copy.
And you want to do the data validation as we did before. But then with concatenation you also want to separate them. So so, so like for UTM campaign, we're actually joining the defense. Ideas together with a dash. So that's what you all going to kind of have in between the see the dash here.
And if I go to this other URL, you can see how it's doing it. So you, Tim company equals eight, two dash C2 dash and two. Cool. Okay. There's you know, all you can do with this obviously you can get a lot more complicated. You can do include some validation to make sure it's a valid URL. You know, whatever it is you want, but this is the basics and this is the system that worked for me across hundreds of clients.
So it's pretty foolproof.

URL Builder

Why is it important to standardise your naming conventions?

How many question marks can be in a valid URL?

How do you get a dropdown menu in GSheets?

Let's keep our UTM tracking consistent

If you look at analytics and see “facebook”, “face book”, and “FB” in the utm_source, that company is not going to make it. Every analysis just got 10x harder because you have to account for all the misspellings in every filter. Garbage in, garbage out.

Your agency is now managing ads for a client, and they were attracted in the sales pitch to the potential to automate routine account management tasks. The owner of the agency gets in touch to get you to put their first automation live.

Hey, I just got off the phone with the client, and they’re asking when we start rolling out automation on their account
The obvious thing for me is pausing poor performing ads
They have a bunch of ads in Facebook that have low CTR, and if we can write a script we run periodically to pause them, they’ll save money
Can you get set up on the Facebook API and give that a try?

Understanding how to make API calls is one of the fundamental building blocks of technical marketing. APIs are how computers talk to each other, so if you can write your own code to make an API call, you can automate many routine, boring tasks without having to log in to your ad account and do it manually. 
There are multiple methods for making API calls, and each platform is different. However they all have the same components. You need to be able to authenticate to the API, so it knows it’s you calling, which is done often with an account ID and access token (analogous to your email and password for logging in manually). There are also API endpoints, or the URLs you call with your script to do certain things or read certain data (similar to URLs on the website, which know to return certain pages). Finally there’s the parameters you pass alongside your request, essentially additional information on filters, limits, and other options that are listed in the API’s documentation.
Making calls to API in Python is usually done with the requests library, which lets you read data as well as push activities to almost any API. There are usually SDKs available for popular platforms, libraries that make API requests easier, but in practice it’s usually more stable to make your own API requests because you’ll be surprised how often the official SDKs aren’t up to date, or do something different from what you expected. Some platforms like Google Ads make automation easy, with Ad Scripts letting you write code directly in the platform. Facebook provides the Open Graph Explorer, which simplifies getting access tokens and making API calls to see what results you get. Others only give you documentation, and for some you have to go through a lengthy approvals process.
Within Facebook specifically you can do most things you would normally do in the ads manager interface. There’s a reporting API which lets you pull data and use it or display it any way you wish, rather than relying on the default reports you see in the interface. Facebook also allows access to push changes to your account, for example increasing budgets, changing targeting, or turning off poor performing ads. It takes more work up front to automate a task, but once you have the script written, it can take mere seconds to run it whenever that task needs to be done again, saving you time for more valuable strategic work on the account, and driving significant performance gains.

Facebook API

In this video, I'm going to explain to you how you can get data from the Facebook ads API. So how you can access your ad account programmatically via the API.
And what we'll go through in this tutorial is we'll get all of our active assets. And then we will applause all the ads that are performing badly or under a certain threshold.
So to get started, we need to create a Facebook application, Facebook app. Um, it's super easily done in less than a minute.
So let's go over here. Of course you need an active Facebook account. And then once you click on this link, you can choose the app type.
I'm going to choose business and you can give it a name, my back's power download app, and you can choose to add this to a business manager account or just keep it separate.
So we then need to duplicate this, enter the password here, and then we're done. As soon as we have the app created, we need to activate the marketing API.
So we can go over here and click on set up and that's it. The next thing that we need to do is head over to the graph API Explorer and get an access token and where there's access token.
We will then be able to make our API calls. It's basically our username and password to access Facebook programmatically. Cool.
So you need to find the right app over here. So I'm going to choose my VAX power demo app and then click on generate access token, and then a new window will pop up that you can't see right now when you just kind of go through the process of, um, giving the app access.
So we can see now that we have an access token over here, um, but we don't have the right permissions yet.
So the permissions we want is ads management. Um, ads read would be enough if we were just reading out information, but we want to be able to make changes.
So we need to add this and then click generate access talking again, and the window should pop up again as it does for me.
But you can see this, you go through a process of approving the app to be able to access the account.
Now that we have an access token in this graph, API Explorer, we can also make, uh, API calls, but in our case to be able to actually build some tools script that allows us to do something, we should just copy the access to a confirm here.
Cool. Um, so I'm just going to pace the token here. So now that we have an access token, um, let's take a look at the actual requests that we're here.
So we need to import two libraries, the requests library, which allows us to make any API call and the Jason library, uh, which allows us to, uh, format the response that we get from Facebook and make it usable in Python.
And then we also have the pretty print library that just makes it a little bit nicer to print the output from Facebook.
So in the first step, we're getting all active assets and here's what the request looks like. We have the best part of the request, which is grass dot, facebook.com.
We have a version number that may be a little bit different when you see this. And we have an account ADA, uh, in our API requests, they always start with act underscore, and then we have the actual account ID, and then we're requesting the ad sets and point.
Um, so we're getting all assets for that account. And then with query parameters, we can now define a few more options.
So in this case, we say we would like to get the ID and the name back of each ad set.
And we only want those that are active and we're setting a limit here of 5,000 per default. The limit is set to 25.
So if you had more than 25 active assets, you wouldn't get all of that. Um, there is also another way to handle this with something called pagination, but we'll skip that for the sake of this tutorial.
So, um, as you can see here, this is our main URL. So once it's formatted, um, we have the account ID swapped in here, and then we need to add one more pro uh, Euro parameter called access token, where we add the access token that we had stored in this variable at the top.
So let's make an API call, uh, that's run this piece of code, as you can see. I quickly update this to 14 because 13 was already out of date.
So let's run this and see what we get. So we can see the responses successful. We get a 200 back and then we can see a number of different ad sets, uh, ideas, and then names as well.
And I already know that a bunch of these answers are just demo assets from my account. So they're kind of, they never actually ran.
There is no ads in there, or maybe they never got any delivery, but we do have a few answers here at the bottom that I know actually gathered some delivery, um, over the past years.
So, uh, at the next step, what we need to do is we want to find out which ads we should pause.
And for that we will loop through each asset in our list of assets and we will make an API call getting the ad insights.
So insights on ad level. So let me just swap out this to 14 so we can see that our request here, um, again, cause not the ad account ID, but in this case, the asset ID, um, we're formatting or adding the accident to here for the, and we're calling the insights and point.
And then we can look at some of the parameters that we're using here. So we, the level that we define as ad, because we want ad level insights, data, the data preset is set to maximum.
You could add change this to last 30 days or last seventies, and then we can define the fields over here.
So in this case, we're looking for the additive ad name, spend impressions, CTR, and then in lane link, click CTR.
Um, so in our case, um, we will look at invent link clicks, CTR. These are basically clicks that happen within the ad that lead outbound or that the, for example, to a canvas or instant experience of using something like that.
And then the actions field will give us all, uh, external site or app actions and we're limited and limiting it to 50 because there can only be 50 ads inside one ad set.
So this doesn't need to be any larger. And what we'll do then is we'll see, um, get me those add insights, um, log, load them again.
So this Jason, that loads loads the data, and then we need to have a logic that defines whether we should pause an ad set.
So in this case, what we'll do is we'll see if the spend is larger than 10. Uh, and if CTR is less than 0.5 and this case that in that neg click CTR, then we will add this, uh, adds a deep, um, to our ads to pause list.
And as you can see here, we'll also print out some information saying, Hey, this is the ad and it has low link, click CTR, et cetera.
And let's take a look at what this looks like. So as we can see below, we are getting a lot of data here.
So for each ad that had any impressions, we're getting some data back and see that we have some spend impressions, something called CTR, and then also the inland link click CTR.
And then every now and then one of these messages pops up that says, Hey, there's ad called my new name.
I actually had an inner lik link click CTR of less than 0.5. It in fact was just 0.4, 6%. And you can see that a bunch of these show up here.
Um, so we have four of the country correctly that are below the threshold. So lastly, um, we now have four ads in our ads to pause a list or a rate.
And so we will loop over each attitude and this, uh, in this list and we'll make a request to Facebook.
And in this case, the request we're making is recalling the ad ID and we're setting the status to applause. And then when making the call, um, tool to make a change in this case, we're making a pulse request as opposed to get requests before.
Um, we are now also adding this fields parameter and adding the status so that Facebook responds with the actual, um, status that it's set at the app tool.
So let's run this and see what it looks like. Okay, we're getting an Arab because we call it a deprecated version.
Let's try again. And there we go, so it gets it now see that four ads were paused successfully and our tutorial therefore concludes.

API Calls

What type of permission should we get to make changes to our ad account?

What do we need to pass alongside our request to authenticate and connect to the API?

Instead of link click through rate, what other metrics could we use to determine if an ad should be paused?

Why do we use link click through rate as opposed to just click through rate? Feel free to consult Facebook’s API documentation.

Pause all the worst performing ads

The biggest mistake I see with junior marketers is taking pride in doing a great job on mundane tasks that should have been automated. Make the computer do it and clear some headspace for more important things. Mastering APIs is how you scale to $20k+ per day.

GOOLYBIB OFFICES – MARKETING PLANNING MEETING

You’ve been up until this point simply running Facebook ads for GoolyBib, a wearable technology seller of baby bibs that alert you when your child may be choking. Now things are going well, you're being trusted with a more strategic role. The most important piece to start with when creating a marketing strategy is a good audience segmentation, so you have a map of the market and can understand who to go after.

You’ve really stepped up in the past few months
However we’re still thinking tactical, and we need to be more strategic if we’re going to win the market
I want you to draw up a marketing plan
First things first, let’s figure out audience segmentation
Who should we be going after?

Marketing strategy is about the application of limited resources to where they’re most effective. In order to know where the best opportunities lie you need to understand audience segmentation. With a map of the market showing each customer segment at a high level, you can put some numbers around what segments are biggest, and make educated guesses about what areas are underserved or saturated.
Often it’s not possible to get complete data about a market and how it’s segmented. First it’s difficult to determine what the right way to break down a market is. Ideally the dimensions chosen should translate into actual targeting options or the resulting segmentation won’t be actionable. Segments should be mutually exclusive and collectively exhaustive. Meaning that customers can be in one segment only, and adding up all the segments describes the total market.
Once you have your segmentation typically you would look to industry reports, survey results, or figures cited in the media to fill in the relative sizes of each segment. This can be a challenging exercise as you don’t always have complete information and must attempt to pin down one or two segments before deriving the relative size of other segments by piecing together different statistics. 
The goal of this exercise is not to get completely accurate information, but to get a good enough understanding of the relative size of each segment. Once you understand the wider picture you can usually reason about what segments might be good to target for your unique brand position.

Audience Segmentation

Hey, let me run you through an audience segmentation for this company, goodly Bev. They sell bibs online. So the interest in the online market be kind of click through and Hey, you'll open up this report that they've purchased.
And the goal really is to get the information contained in this report onto the page. So we have a segmentation and we'll understand what are the different sizes of these categories in the market?
So this is a technique I learned from the mini MBA in marketing, by my CARECEN. Although this example is my own and the the way to read this chart is to basically kind of think about these different categories.
These are all mutually exclusive, but collectively exhaustive, meaning like if you're in the mother's category, you can't be in the father's category or vice versa.
You know, if you're pre winning, you can't be post winning. And if you're a, can you parent, you can't be a parent with toddlers.
So so obviously the lines are fuzzy. Like their education is, we don't really care that much. We're just looking at a top level, big numbers.
I want to know, is this a $1.5 million opportunity to target this demographic? Or is it like, you know, 250 K like 4% of the market?
So that's what you want to understand. And when you understand this and you have a map of the different segments, then then you can really think about which ones to go after.
Okay. So I've started with a blank sheet here. I came up with these segmentations based on what Facebook allows you to target at least at the top level.
So you can target new parents zero to 12 months. And parents were told us one to two years. That's really important that the personas that you choose are actually realistic.
You know, you can target mothers versus fathers on Facebook. That's going to make it much more actionable once you've decided as some things you won't be able to target.
So you don't know necessarily like pre winning meaning, you know, other babies are eating solid food yet. But you can also segment based on creative, right?
So if you show babies eating solid food with the I'm wearing the bib then that's like one way when you talk to new parents to attract more you know, parents who are post swimming.
So that's how we kind of do the segmentation. There's different ways you can break down the market, but this is just the way that works best for me.
And this is the way that fits into this market analysis that we've purchased. And this is typical you'd buy like some market analysis, or maybe you do some analysis of yourself, like, you know, survey a bunch of people in the industry and see what patterns come out, like, what are the different groupings?
So now let's walk through a few examples of how to take the information here. So we know that their overall market is $6.5 million.
So on our worksheet, which got the red line on there are, let's just add that number. So that's 6.5 million.  And let's make that in light, of course, 6.5 million, and we want to see how this breaks down.
So are there any kind of breakdowns that we can see him? So this is just talk about both roads shift to e-commerce.
So one thing would be, I guess, like, you know, doing, doing know you know, how much, how much the mafia is disposable.
So here we go, disposable. So this is a disposable versus non-disposable that says all, almost all disposable products were bought after the child turned one, which within a checking that they come in to 26% market share.
Okay, cool. So we know roughly that this is 26% across these two here. So this make that like this, and then let's just color this in.
So this is like so you don't mess around too much with formatting.  You know, don't have to worry about making it too fancy, straight away.
So that's 26%. Okay, so that's useful. Okay. Let's kind of zero in on this fathers constitute 31% of the overall baby big baby market.
So let's figure this out. So fathers, they 31%. All right. And then what else do we have? So 61% of them are mothers and 29% of fathers are loyal to a specific brand of baby bib.
Okay. So that's interesting. So Brandeis, he doesn't develop until after the, child's doing typical age of one. Okay. So and it says here basically in a brand loyalist would not consider switching.
So that's an interesting data point for when we decide which segments to target. Is there a 61% of mothers and 29% of fathers segment?
So let's do, it's talking about this section here because it doesn't we don't get brand loyalty until afterwards. And then and then like disposable brands or not.
I think I said in the report, they're not to do with the brands that we're working with. They're, they're separate companies that make them so here, it's going to say it's like 60, so it's going to be 60% or 51% of the mothers in this category.
And this one is it says 29% of this, so 29% of that category. So that would be so that's just the reverse, right?
So that's 1% and it's arrest as well. So this would be so this could be 29. Yeah. Okay, cool. So we're starting to get some understanding.
So that's like all of the category, this is the market. And then this is all the market. So we know this.
So this would be six, 9% would be mothers. And so we can actually put some numbers in here now, too.
We can say so off 6.9 million let's see, let's say 600, 900, 6,900. It's most 1.69. So that's a four million seven hundred thousand two hundred sixty one thousand. So let's say 4.6 million, 4.7 million.
And then, sorry, and then this one to 6,500 times 0.31. So this about 2 million. Cool. Let's just check the other number.
Oh, sorry. 4.5 million. Okay, cool. So we understand this. So we know mother's first, his father's, that's interesting. All right. Now let's see what else we have.
So let's see if we can find anything about, we need to search document control F weaning. So winning typically becomes at six months, only 4% put foot 6% of the market buys a big advance of weaning.
Cool. So that means that if we go back here And it's women that said, sir, this is going to be like 4.6%.
And we know that 4.6%. So 0.04, six times the whole market is a 300 K. Cool. So so we know this number now, right?
So we said, I said it wasn't fathers that bought pre winning 4.6. So know it's a 300 K of this market.
We also know this number as well. So we know that 26% of overall market was disposable. And let me see.
So this is a 0.2, six times 6,500 so this out 1.7 million, okay. It says 1.7 million in this bucket. And we don't know necessarily these ones yet, or this one post weaning.
So, but we're, you know, we're starting to get there and we know that this bucket is an, a, This is a weird ours and 0%.
So it's really just a case of continuing to dig into this report and finding ways that we can ask to this out.
So what we need is like how much is going to you know, post winning how big is this market? So we would, if we knew, for example, how big the parents would tell those market was compared to the new parents market, we'll be able to figure that out.
And then we just need you know, we know what the disposable is, so if we could figure out how many was non-disposable, we, we know that actually as it's, if it's 26% non-disposable then you know, we, we, in order to figure out what the disposable we could just take the inverse of that.
Right. So so this would be 74% would be, be in this bucket. Right. And once we, once we figure this out, then we can figure these out as well.
Right. So,   So this is so if at 1.7 was 26% of the overall market. Okay. So we know hopefully 7 million was 26% and we just need to know how many are brand loyal.
Okay. Oh, here we go. This is something else we can figure out. So 60, 40 let's. So I like to jump around and You know, figure out the thing.
I kind of pin different things down when I find them to, if you're not having, if you're can not finding a number of maybe try and find another number, and then you'll hope Luxembourg, it's kind of like Sudoku, right.
As a, here's actually one, that's a little bit easiest to figure out disposable pigs bibs are patched by mothers and fathers in a ratio of 60, 40.
So we can at least figure this out, right. So this is 60 40, and we know we'll put seven proponents six as, so it's mothers and fathers 60 40.
So mothers are 60. So this is a 1 million 1 million here. And so that that's mean the father is 1.7 times four, so six 80.
Cool. And then now that we know the numbers, we can actually work out the overall, so a 68, the, you know, the final percentage.
So this is about 10% of the market. And then this one, 1 million is 15% of the market. Okay, cool. So we're kind of drilling in here and you can see the predecessor, a few things you need to find on your own.
I'll leave it as an X associated. What is the share that is you know, parents versus parents who have toddlers versus new parents.
So what is that relative share? If you can find that out, then you can fill in these two buckets. And then also what is the split for parents who have told us, what is the split between disposable and then and disposable versus the independent brand loyal.
And if you can figure that out, then, you know, how much revenue is in this category, and if you know that, you know, these percentages of the category, so you can figure that out as well.
Okay. So you know, once you've figured this out, I want you to choose a segment and it's useful to come up with an understanding of why you can cheat use my segment but I still want that that explanation you know I think you can figure out from here why you might want to tell you digital dads or maybe have a justification for something else, and the reason why you might want to target.
So I look at some of the additional information here about technology and about interesting, you know, what what's interesting about our brand versus the other brands.
Cool. Okay. I hope this was helpful and good luck.

What is the first step in planning a marketing strategy?

How much in sales are parents of newborns (age 0-1) worth in the bib market?

How many sales come from fathers of toddlers who are brand loyal?

What 2 segments adding up to $1-2m would you target as the marketing lead at GoolyBib, and why? GIve an answer based on the data provided in the market report.

Who should we target?

In digital marketing we often pretend like the potential audience we’re targeting is infinite. The algorithm will provide. In reality good marketing strategy starts with proper audience segmentation, so you can decide what part of the market you want to go after.

VEXNOMICS OFFICES – THE BIG CLIENT COMPLAINT

One of your biggest clients just complained about overspending in their account: your company is on the hook for thousands of euros. Your boss has asked for you to program spending alerts with the Facebook API into Slack so this never happens again.

You’ve heard about the problem with Goolybib’s overspend, I take it?
As the technical member of the team, I was hoping you could spin up a solution
Could you connect to the Facebook API and then post into Slack an alert if we’re spending too much?
Take a look and see if that’s possible.
Thanks in advance for the help!

You can only solve problems you know about. It’s impossible to log into every single account for every single platform you use, across every single client, and be on top of everything while still having time left over to do your actual work. However if you aren’t actively monitoring the situation you can be left with unhappy clients, expensive refunds and a demoralised team. The solution is to automate alerts, so that you’re notified when something important happens and can direct your attention to solving it. The simplest and most useful alert is to monitor spending and notify you via slack if you’re over spending. Once you understand how to do this, you can do any kind of alert, and plug into any messaging or email system with the same principles.

Slack Alerts

Hi and welcome to this tutorial. I am going to show you how you can get data from Facebook and send Slack alerts. For example, if one of your assets is spending too much money or you just want to get notified if something is performing extremely well or may be spending over budget. So the example that I'm going to use is this we can look at the tutorial over here. This is hosted in collapse, so you can just copy this script and replace all of this with 
your own data. So let's first head over to Facebook Graph API Explorer to generate an access token. And of course, for for that to work, we need a Facebook app. So if you don't have a Facebook app, then check out the linked course in the script above. On pausing worst performing ads and that will teach you 
how to create a Facebook app. So will either it click here on generate access token or we can just copy this token over here, which is what I'm going 
to do. And there is a possibility to extend your token. So normally it will expire after 60 Minutes. That is the default to keep your app and your account safe. But if you know what you're doing, you can go to the debugger tool and paste your token in here. And when you click on DEBUG now, you'll see that this expires in about an hour. But it is possible to extend the access token. I'm not going to do it now because I don't want to share this exact token with the world. But if you want to use your access token and don't want to refresh it all the time, then you can do that. Just make sure to keep it safe. Cool. So we have an access token. We don't need anything else. I'm just going to replace the access token over here and then we need an account ID. So let's head over to our account and just copy it from the URL up here. Now that we have our access token and the company did, what we need to do is just make sure that we actually supply these variables to the script further down below. So I'm just going to hit run here. This doesn't output anything and just make sure that we're using the correct elements. And the next step in the process here is to then go create a slack up and a webhook URL that we can then use to send the message. What I'm going to do now is just show you what the data looks like on Facebook when we get it programmatically. And so what you can see here is that we'll be getting asset insights. So insights on asset level. You can see this here. You can also do the same by supplying campaign account or ad, and you can choose a timeframe I have chosen last year. You may choose 
today, tomorrow, etc.. Sorry, today. Yesterday. 
Tomorrow doesn't work of course. And the fields that we're accessing are just the absolutely the asset name and the spend. And we want to get basically all the assets we have. So we set a limit of 5000 so we don't have to deal with pagination. And once we get the data, we'll actually make it a little bit more readable for our Python script. And then we'll look through each item in the insights response and we'll just filter out all of the ones that actually spent more than 500. So let's take a look at what that looks like. Okay. Fantastic. So it looks like we have it looks like we have something going on here. We printed out at the outset and there is a spend of more than 79 spent of €79. But we're comparing our spend with a number. And this right now still is a string. So let's convert this to a float. And float means here that it's a number with decimals. So do this again. Okay. Fantastic. So it looks like we looked through each item here and we can see that we have this offset 79, 78, 41. So we would expect only this one for this one asset to show up. We only have one asset, but 1200 euros. All the others were below 500. And if you do a quick check on in Facebook ads manager. This is correct. Right. So this is exactly the same data that we can see here. We could, again, look at a different time frame, but this doesn't make sense to me right now. So let's clear this output. We don't really care about it. And what we'll do in the next step is we in the next step of the script is we will iterate through each insight. In this case, it will only be one who will create a nice message that says, Hey, this accident with the name X and Y has already spent more than €500 today. It doesn't really make sense. I'm going to put last year. Maybe we'll just fix the grammar as well. Spent more than €5 last year. So it's not going to be exactly insightful right now, but you can adapt it to the timeframe, the level and and the metrics that make more sense to you. Cool. But this gives you an idea of how we can create a dynamic message and then send data to a weapon. So now we need to actually get a web hook URL. So let's go back to the top and follow the process here. So what we need to do is create a Slack app and with that generate an incoming webhook so we can hop over to the official Slack API page 
and just follow the process. So there are four steps that we need to follow along, so we need to create a Slack app if we don't already have one. So let's go here and create one. I'm just going to create one from scratch. 
I call it my amazing Facebook alerting. And I'm going to choose a workspace. So I'm going to choose the kitchen community space. Maybe I'm just going to choose the kitchen space and I'm going to 
create the app. Great. So you can see now that we have a new app, it doesn't have a great logo yet. This is our actual app that we use at Kitchen now. So I'm going to go to incoming Web hooks over here. And now I need to switch it on. So I'm going to switch it on. And this is the second step. And the third step is now that we need to add a new app to the workspace. So what you can do is you can define a channel where you want to receive these messages. So I'm going to choose this one called Simon's Notifications. So this is the channel that I usually use and I'm going to click Allow. And what I have now is a way for me to programmatically access this channel. So let's copy this URL from over here and go back to script. So now we need to replace this. And this is important to not share with others. So I'm going to invalidate this in a second. But there is some data in here that you want to keep secretive, because otherwise right now, anybody could be pushing data to the scrapbook. So if you have this book, you're. Now, if I didn't invalidated, you could actually send me messages, anything that you like. So that doesn't really make a whole lot of sense to me. So I'm going to press play one more time. And now this has been run. So then I step really is just to loop through our access. Again, this should just be one and send a message. And then let's take a look at what it looks like. So the only thing that we need to do now is actually press rock. And it looks like Slack replied with okay. So that apparently the message came through. So let's take a look at what that looks like. So what we can see is that, yes, actually out of this channel just now and we have the amazing Facebook learning app that told me this app sat with this. I'd actually spent more than €15 last year. And so this is already pretty cool. We can now make it a lot better. We can, for example, add a link to the app such over here. We can actually run this maybe on today's data or yesterday's data to have something a little bit more relevant. So if you followed this tutorial from top to bottom, you should now have a Facebook app, a Slack app. You should be able to send custom alerts and notifications based on your Facebook ads performance.

Why is it important to not share your webhook URL and keep it confidential?

Why do we need to convert the spend to a float and not a string?

What part of the code loops through each adset’s data that we pull from Facebook?

Can we get spending alerts in slack?

Anyone managing 5 or more Facebook ad accounts knows how easy it is to miss crucial performance changes. If you are not constantly monitoring your accounts, you may miss an opportunity to scale well-performing creatives or pause adsets that are overspending. Instead of manually checking in Ads Manager, set up Slack Alerts to get notified when your attention is required.

Fizzy Love Drinks Co. 4 weeks before quarterly sales meeting

You are in the commercial team of a soft drinks company and you are seeing falling sales for one of your leading products with one of your key customers online. A new competitor has recently entered the market, running an expensive influencer campaign which seems to be eating into your market share. Your boss has seen the sales falling too and sends you the following email:

I’ve seen the recent sales figures for Diet Fizzy Love online. 
We need to do something about this! 
If our competitor starts getting real traction with sales, our search position will be threatened and so will our listing. 
We’ve got £20K in the budget for online activations. 
Can you come up with a quick plan on what we should do? 
I’d like to see a proposal by the end of the week. 

You don’t really know much about digital retail media. So where do you start?

Digital Retail Media is digital advertising’s third big wave and the next big thing in CPG eCommerce. For Bricks and Clicks retailers, Digital Retail Media is THE crucial source of revenue for the online P&L. For brands, Digital Retail Media means shopper understanding has never been so good! (potentially…)

Online grocery is no longer just a place to go and buy things, it is where shoppers find ideas, become aware of new products and compare brands! Because it’s a new, rapidly changing area, many commercial and marketing teams are struggling to make the most of the opportunity. Deploying it correctly can be an incredibly effective way to engage and influence shoppers but executed badly is a surefire way to waste promotional budgets. 

Retail Media is advertising within a retailer’s e-commerce website or apps – typically sold by retailers to brands who wish to boost visibility on the ‘digital shelf’ and promote their brands closer to the point of sale. Think of Retail Media as any communication and marketing techniques used on consumers' purchase journeys online that can be packaged and sold to third parties such as brands to drive sales. Another way to think about Retail Media has been coined by Fidji Simo, the new CEO of Instacart: “There’s an advertising product inside people’s e-commerce shopping cart...the holy grail in advertising.” Retail Media is a virtual cousin to end-of-aisle or in-store, point-of-sale promotions in a bricks-and-mortar store with a more compelling proposition than a typical ‘end-of-aisle’ promotion: as first-party data allows them to identify consumers who make a purchase after seeing an advertisement on their e-commerce site. 

By following the 4 principles in this lesson we think you’ll add an extra layer of rigour to your planning process and make the most of your Retailer Digital Media budgets. 

Principle 1: Have a clear objective in mind
Principle 2: Think ‘Path to Purchase’
Principle 3: Be selective with investment mechanics
Principle 4: Secure retailer commitment

Digital Retail Media

Hello there in this lesson, mark and I are gonna talk about the importance of digital retail media and how you can make the most of your budget.

First off, what is digital retail media effectively? It's digital advertising within a retail's eCommerce website within their apps and their digital ecosystem, whether it's social media or on their newsletters, wherever consumers come into contact with a brand, when you can advertise that's digital retail media.

It's one of the most effective ways for brands to engage in influence shoppers in a retailer's online ecosystem. And it's also an inherently brand safe environment. Relying on long established trust. Retailer domains. Some people say it'll have a similar effect to the advent of search marketing back in the day, but what's sure is in a fast changing marketing environment, brands are likely to invest heavily in ad tech, which is more data driven and easily trackable, right to the point of conversion.

If the first 10 years of digital media were around search, the last 10 years have mostly been around social media and we can see retail media. Growing fast. If you look at the global ad spend in 2021, you can see dominated by TV, Google, and meta, and there's a big drop off at that point. you have a level across Google display, Amazon newspapers and out of home.

Amazon made $31 billion last year in ad revenue. Basically the same as YouTube or the entire global newspaper ad industry Retail media can mean big money for the retailer. So they're all now developing their own digital media solutions.

eCommerce profitability is a challenge for many bricks and clicks retailers. Retail media offers them a new source of revenue. So done badly. This can mean an erosion and margin for suppliers and FMCG brands, but done well.

This can be a win-win for both brands and. especially since apple changed the privacy settings on iOS brands are increasingly asking how to diversify them channels and place more emphasis on owned media that doesn't rely on third party data, retail media is a great way to a discovery and conversion.

There are lots of issues for product discovery, especially on mobile customers tend to shop very quickly for groceries. They rely on favorite shopping list and repeat purchases. There's a lack of sensory cues that you get in store that screen size reduces the chances of discovery.

So each retailer will develop its own flavor of retail media, and that means retail. Media's gonna become a key platform for brands to get new products in front of consumers. Is it real advertising or is it just souped up shopper marketing in a digital world?

Well, not exactly as this data from insider intelligence shows on the right online grocery retail is no longer just where people complete their journey. It's also where people find ideas, become aware of new products and compare and contrast brands. Given the importance of digital retail media. Our data shows that over 90% of CPG people rate themselves poorly in this area. So where should Lindsay start? The clock's ticking for applying to her boss. Mark's gonna take you through four clear principles to consider, to maximize your digital retail media investment.

Thanks, Tim. So, what would it cover now? The actual principle is you need to think about deploying when I'm organizing your retail media And there are four. The first is, is always have a clear objective in mind. Then make sure you think path to purchase. Thirdly be selective with which investment can actually deploy and fourth, the always make sure that you secure retailer commitment to what you're trying to do

Traditional marketing campaigns focused on objectives such as ROAS or. Non-art served. And this is really an attribution measure, which looks at the correlation between the shoppers in an advert and a action. They may take at some point in the future. We don't think is necessarily the best things to, um, to focus on for digital retail media. He would suggest these areas. So always talking retailers language when you're engaged in the retailer or the customer, it's about their shoppers. It's not about your consumers. We actually have a clear shopper target in mind and to do this, we recommend folks. I'm one of the shopping malls, which are more shoppers penetration by more often, which is frequency.
Spending more money, which is way to purchase. And then thirdly and importantly, make sure that, you know, what day you., have access to, so you can establish a baseline and demonstrate improvement over time.
Some talk That's clear objectives are to really understand what your target shoppers are buying online and when they're Think about how I can improve the shopper experience on the retailer website. And that's all about overcoming barriers, reducing friction. It can help us find out what events are coming
If it's a particular campaign you're running for a particular season and you could run a particular promotion to target those But very clear on what the investment supporting is it for new product launch or product? Out in the market. Maybe your objective is smart. Smart stands for specific measurable, achievable, relevant, and time
If it's all about sales and you don't want to think about conversion and nothing else. Um, and engagement awareness isn't as relevant as just driving sales.
The second principle is, think about path to purchase in the campaign., the evaluation, different ways of looking at path to purchase, but broadly speaking, they always go through these three areas starts up by generating awareness. Then encouraging consideration of a product or product group. And finally converting to purchase. And what you'll see is a many different mechanics that you can deploy.
To do those specific actions on the path to purchase. This means, realistically that you use different formats at different times, so that could responsive products, brands, display, or video. They're paying in different parts of retail website or app, which could be the homepage. Can't be page. Product detail, page checkout, or even secondary It can be quite a good way to, to drive, consideration of a product, which isn't the normal product listings.
But thinking about which part of the path to purchase you targeting and which retailer, mechanic you wants to deploy, you can really bring together very target campaign. That's going to deliver those shop project is that we talked about a moment ago. This is of course, making sure that you choose right investment mechanic. The many different ways to look at If we think about linking back to our shop objectives, then a good way to consider which mechanic to deploy, convince terms of which of these shop measures you're trying to deploy. So in this example here, you might want to use a skew bundle. If your objective is where you can show it at purchase.
I'm on, which is homepage banner. If you are about driving penetration. It's really important that you bring together the investment mechanic and the right part and the path to purchase to target your shop is most effectively.
That means that you might need to consider different success metrics. So again, depending on your funnel stage on the mechanic that you're deploying, there's a number of different things you can consider. It could be more traditional market metrics like CPM, which has got the meal or click through rate CTR, which is really a measure of how effectively particularly banana has been
As you move further down the funnel and it can become more about conversion to purchase or how much it's costing you to actually secure a new shopper, which could be cost per acquisition. It's really important that you mentioned together the right path to purchase stage with the different success measure.
The tool that you're trying to deploy. So talk to it's how you do that. It's really, really, this can make choices. You don't need, it must do everything that you want unless you've got a bottomless pot of money. So it's really important that you are selective and you do things in deliver your specific objectives. Your campaign. Always think about the success measures. Make sure you have a plan. Don't just demonstrate your tactics.
When you engage with the retailer, which we'll talk more about in a moment, always talk about the value of what you're doing, they do need your money. Make sure they don't forget It's really important to get into an alignment before you launch campaign. Everyone is really clear on what you're trying to achieve.
Wait forever for the perfect scenario. So sometimes it is good just to get on with it and to embed them. And it's think we're thinking about benefits. It's not about ROI. It could be other things as well, like Ottawa. We just done so return on relationship, which is really about to make the retailers clear on why you're spending the money with them.
The final thing is to make sure he put all this together in a retailer business plan or JBP on EDBP, which is online business And it's important that this could really look something like this. So you've got revenue target, which you both signed up to. You've got the amount of money are you going to spend to deliver that revenue target? You've got KPIs that you both agreed on, and then you got to send me the activation. You're
Underpinning. Oh, with what y'all going to do as a brand. Yeah, expect the retailer to do as well. And these can't really work in order to make sure that you're getting what you want money pays for and both living benefits If the retailer.
So in period chance to get in that commitment, make sure that you're doing put the investment into a business plan. It's really important to get the retailer to agree, to share data. Make sure you plan your approach as well, depending on how good the relationship is with your retailer or how sophisticated they are, don't try and do stuff, which is too sophisticated for the relationship stage you're at.
Definitely clear test and learn. That room before you can walk still, despite everything we said, it's really important to make sure that the digital shelf work first before you try and do anything else. I'm actually talking about what the retailer wants to know. You want. And as a guideline, trying to invest 5% of the sales target into retail media on that website.
So just to recap, then these are the four things which should be a command focusing on to deliver those reads, telling me to campaigns. It's about having a clear objective in mind, thinking about path to purchase. Been selective with the money you put in and always making sure that you get some retailer.
Commitment to what you're trying If you feel it comes from with these areas, then you can move on to some, some next principles or things. I think in Omni channel. Really trying to link the investment you make in direct to sales, doing some advanced targeting and doing some dynamic creative work, which can be a really good way of improving both yours and the retailers learning what works and what doesn't work.
Now we're going to see what you've learned by applying these principles to support Lindsey, make the right response to her boss.

Which of the following objectives would make most sense to our retail partners?

Which of the following items are mandatory to deliver an effective Digital Retail Media campaign?

Which of the following would be a good choice for your retailer investment?

Which of the following are mandatory to incorporate into your online business plan? Tick all that apply.

Digital Retail Media is becoming increasingly important as online shopping becomes the new must win battleground for retailers and brands. Many brands still have a traditional (offline) broadcast mentality to marketing investment and lack capabilities to make the most of the Digital Retail Media opportunity.

How to Plan a Retail Digital Media Campaign

Conversions were down week on week and your boss is looking for some extra explanation to go with your weekly report. We know that the drop in conversion rate was consequential, but how many conversions did you lose because of it?

I understand that conversions are down
I understand that it’s likely because of the drop in conversion rate
What I don’t understand is how impactful that was
So for example, how many conversions did we lose thanks to lower conversion rates?
What did the other factors contribute? Can we work that out?

It’s important to know how your performance is trending, because you need to take steps to correct poor performance, as well as being quick to double down on good performance. Most marketers will identify conversions have fallen, then look deeper into what metrics may be causing it. If CTR (Clickthrough Rate) has fallen, it might be an issue with our ads. If CVR (Conversion Rate) has fallen, maybe it’s the quality of the traffic. We could also look at search impression share and see if maybe we’re being outcompeted for inventory. Looking at these metrics as a way to explain what’s going on, and then taking action to correct performance issues, is a key part of every marketer's role.
However it’s not enough to just eyeball the metrics. Yes CTR is down by 50% or Impression Share is up by 10%, but how much individually did those metrics contribute to the overall conversion number we hit? Without knowing how impactful each change was you can’t properly prioritise the right actions to improve them. In order to figure this out, you need to decompose the contributions of each metric and display it in a waterfall chart. The chart shows initial conversions on one side, positive and negative contributions by metric in the middle, and finally the resulting conversion numbers. It can be thought of as a kind of marketing funnel visualisation, because each metric contributes to the final figure, and the sum of their contributions totals to zero. The maths to do this is a little complicated, because it requires stepwise calculations, so we recommend using WeDiscover’s free template.

Waterfall Charts

hello so in this video i'm going to be
taking you through the newest tool that
we have been putting together which is a
performance decomposition tool um in
this video i'll take you through kind of
how to set up for your cells and
actually how to interact with the tool
to hopefully get the most out of it
before getting into some of the details
i think it's just useful to give an
overarching view on what decomposition
is in case some of you are not familiar
with it so effectively decomposition
charts are a way to see the drivers of
changes between two periods so for
example in the context of google ads if
we wanted to understand why conversions
dropped week on week or why conversions
dropped in the last 14 days compared to
the previous period before that we can
use decomposition to really easily see
exactly what those what has driven those
changes in conversion volume
there's obviously all the other kind of
metrics we can look at and when we get
into using the tool i'll go through
those in a little bit more detail but as
an overarching point they're just really
useful charts for getting a quick view
on why
something might have changed
so in terms of how we set this up what
we first need to do is navigate to our
we discover public github repository and
if we then navigate into performance
decomposition in google ads you'll find
a script called google ads export what
you'll effectively need to do with this
is copy it
and then navigate to your google ads
account so if you've never implemented a
script before what you need to do is go
to tools and settings
scripts
and this will take you to this interface
where you just need to add a script
new script
and it should bring up this blank
template where you're able to actually
paste the script that you've just copied
from our github repository
so this sometimes takes a couple of
seconds to load so while we just wait
for this to load um
shouldn't be too long now here we are
what i need to do is copy this script in
and then if i scroll to the top of the
script you'll notice there are a few
general configurations that you might
want to put together so this is where we
now need to go to the templated google
sheet and create a copy
so if we go to this template
file
make a copy
you can name this kind of whatever you
want so let's just say
test
and then you can save it to the specific
folder you want to so i would recommend
saving to your drive your company folder
whatever makes sense to you
so i just hit cancel there
i have a demonstration one here
so if i just
copy this url at the top
navigate back into my google ads account
and then paste that url just as is from
the bar into the spreadsheet url you
should then be good to go
if you do want to kind of specifically
use certain accounts within for example
an mcc or you want to exclude certain
accounts from being able to be accessed
in this decomposition you can do that
here by adding the ids in a list similar
to one shown here
so once that's all done what you'll need
to do is kind of preview the script
that'll probably then ask you to
authorize it so if i just hit authorize
i'll need to authorize kind of the
script to
be able to access it fairly standard
practice if you've ever used google
scripts before and then the next time i
run this or preview this i'm able to
actually export the data
so for the sake of this example
let me just put the script name as test
decomposition
and hit save
so now i've done that if i just go back
to my scripts
you should see test decomposition is one
of these scripts we can now use
and i'm able to then schedule this on a
daily basis a weekly basis if i want to
and what this is effectively doing is
exporting the data from google ads into
this google sheet which enables you to
then interact with it and create
specific performance decomposition
charts so for example if you want to be
able to look at this on a daily basis
you might want to schedule the data to
get exported on a daily basis or if you
only care about on a weekly basis or
even an ad hoc basis you might want to
do a slightly different schedule or just
run it once
so once that's all set up you just then
need to navigate to your new copy of
your google sheet so in this instance
i've already got one set up that has all
the data um in the background and what
you'll notice is a variety of different
kind of buttons and settings here
generally the way that we do tools from
a we discover perspective is we try to
have drop downs where you're able to
interact with the data
so i'll basically just talk through this
this interface as a whole and then we'll
go through playing around with some of
the options and see what we can do with
it
so at the top here we've got two sets of
controls
you're able to kind of select your
account um you're able to add filters
you're able to select the specific
decomposition metrics you want to look
at
and then on the right hand side we've
got a period section so this is where we
might want to be able to say okay i want
to look at
last week or the last seven days worth
of data versus the period before that
what you'll notice is it'll kind of run
what that actually looks like from a
date perspective and tell you if you
have data available for that period so
if you are flagged as not having data
that's where you might need to go back
into google ads and rerun the script
this next section is then just something
that you can use
to look at metrics in that period so if
i was just interested in comparing for
example impressions i could click
impressions and because i've got no
entity selection at the moment this
shows nothing but if i was to just go
through very quickly
and just
select a couple of options
what you should notice in a second
actually i just need to hit show all
here is it shows me the difference in
impressions between the period that i've
selected so this is more for you to be
able to quickly look at the change on
change over a selected period if you
would like to but it is worth noting
that this has no direct impact on the
decomposition charts this is more for
your interest so if you kind of don't
want this or you don't need this feel
free to kind of hide these rows and then
they won't be showing at the bottom here
is this pre this area that automatically
populates based on the settings you
select so obviously we haven't gone
through and adjusted everything and run
it yet so this is all showing us null
but when we actually go to run run this
you'll see that this populates there's a
decomposition chart
cool
so
let's actually just go and run this so
what i've done here is i've selected my
account
i've said i want to look at all
campaigns
i've selected conversions of
decomposition metric and then the
conversion action of just conversions
and i'll explain what these mean in a
little in
a second but let's first run this
script and what you'll see happens in
the background is you have this
processing and finished and that then
populates at the bottom a decomposition
chart
so what we're seeing here is for a
simple view of conversions of
the last seven days versus the
previous seven days before that
we're getting a decomposition chart
which just shows
what is driving those changes in
convergence so what we're saying here is
each of these kind of areas is isolating
how that area is affected
the
overall conversion so for example if we
were to isolate just impression share or
the change in impression share we're
saying based on our change in impression
share we would have expected or seen an
uplift of 62 conversions
what we can see very easily here is okay
our impression show improved
but because of our conversion rate
dropping more significantly our overall
change in conversions dropped so you see
this initial which is the very first
week of the
the
previous period and then the subtotal
which is the last seven days
cool so we can then play around with
adjusting this and and changing things
and equally i can add in some different
metrics here so i can say okay what with
the change in clicks what do conversion
rate look like and we can see here
clearly that that change in conversion
rate was was significant and again it's
shown here as well
and we can also play around with days so
let's say i want to see last week versus
the period before that this should
automatically update these dates
and then um
it also automatically updates the
decomposition at the bottom so you can
see this kind of changes depending on
the the filters that you obviously have
if you wanted to compare
a period to a slightly different
previous period you can choose previous
period minus one or previous period
minus two so in this context if
last week is our period previous period
is obviously the week before that and
then period minus one is the week before
that so you're able to play around with
the exact dates you're looking at but if
i was just to change this you'll see
that these dates change
and you can see what the comparison is
versus the initial
so we're saying the initial here is the
15th to the 21st of august and then the
comparison is last week so not 28th 29th
of august the 4th of september
cool
so that's how you kind of play around
with dates
you're also able to do custom dates
and all of these different defaults
in terms of the entity obviously in my
instance i've only got a single account
but let's say i want to only look at
campaigns that contain brands so in this
example i have a very simple campaign
naming convention where i've just got
brand contained within it if there is
just brand what you'll see here is this
has changed the way the decomposition
looks at the bottom that overall volume
has dropped because obviously we're not
looking all campaigns
and the way that the decomposition looks
is different this also updates
impressions clicks conversion rates etc
so you get to see this all flow through
so if i just navigate back to show all
the only other kind of options you have
are the decomposition metric and the
conversion action if we start with the
decomposition metric what this is
effectively showing is the different
ways that we can manipulate the data to
look at different things depending on
what we want
so for example if
for your specific instance cost per
acquisition is most important
if i just select that and then
[Music]
rerun it
what you should notice is that this then
changes the way that the decomposition
is actually put together
so the reason we've done it like this is
because decomposition is basically
breaking down all of the steps that
happen between one week and then the
next week so for example
if we had 130 conversions what were all
of the different things that could
contribute to changing those conversions
in that period of time that has caused
this new figure so that's how we're
effectively looking at when we look at
decomposition so depending on what
metric we select for decomposition we
might want we we might need to consider
different things so for example
conversions doesn't necessarily need to
consider cost on its own but if we have
cpa we do need to consider the change in
cost because that obviously has a direct
impact on what the cpa will be
there are obviously a couple of other
metrics so potentially if you're a
client that focuses
more specifically on revenue or roast
then maybe you want to select those
conversion action is then just to give
you a little bit more flexibility in the
specific conversion you're looking at so
in google ads it's quite common that you
would have kind of a primary conversion
but you might also have some secondary
conversions that you're also interested
in let's say in this instance you have
sales as your primary conversion but you
actually want to see quote form as your
secondary conversion so you might want
to run a decomposition looking at that
quote form so if i just rerun this
it should change some of these figures
if it didn't automatically do so
i think it automatically did it so
you're able to actually flexibly play
around with what exactly you're looking
at from a conversion perspective if that
is something that is important
cool
so the only other things that worth
noting at the if i just move myself
at the very bottom there is an option
for options so this sheet is hidden
because we think that um a lot of these
defaults
are
important in all instances but if there
was a specific situation where you said
actually
we are not a revenue based business we
don't
necessarily want to or from a google ads
perspective we're not a revenue based
business we don't really care about
seeing conversion value and rows in that
drop down you are able to remove them
here if you would like to but we
probably wouldn't recommend doing that
just because it's nice to have that
flexibility and also in some of the
these kind of instances this is getting
auto populated based on the data so just
be careful with playing playing around
with options but if you do really want
to
get rid of some of these hard coded
figures then you can do
but yeah just just be cautious i guess
when doing something like that
great so that's kind of how you play
around with decomposition and that's how
you use the the tool we've put together
i think the main takeaways are
just make sure that you copy a template
of the script otherwise everyone will be
able to kind of see the data if you're
populating it into the template
and also
make sure that you've got the schedule
set up as you want in google ads because
if you want this data to always be kind
of populated and always be usable then
you might want to have that schedule in
place so that it's always working
um please let us know we'll reach out
via this kind of email at the bottom
here if you do have any feedback or
concerns and we'll be more than happy to
try and help you out
great i hope that video was useful
and thanks taking time to watch it

What is the advantage of using a Waterfall Chart?

What are the different metrics that together determine conversions?

How many conversions did we lose by having a lower conversion rate (CVR)?

Which of our metrics contributed positively to conversions?

What’s the process for using this template?

How much did a metric move the needle? It’s easy to say which metrics went up or down week on week, but how much did they actually contribute to conversions? To figure that out you need a waterfall chart.

What's driving performance?

GoolyBib Offices – Website Planning Meeting

Your team has a number of test ideas you want to run, but you don’t have enough time, traffic, or resources to run them all. You need some framework or way of designing the experiment so as to get results quickly, without missing a winning combination.

Have you heard of orthogonal arrays?
It’s something I learned about in my MBA. From this guy called Taguchi. It’s a way of designing experiments so that you can get results quickly, but without leaving a potential winner on the table.
They work by allowing you to test a variety of different hypotheses at the same time. That way, you can find out which ones are most likely to be true quickly, and then focus on those.
Take a look at what you can find online, and see if you can figure out how to apply it.


In marketing we typically have more variables than we have time or money to test. We can decrease the scope of our testing program, but that may mean missing out on potential winning variations. Orthogonal arrays offer a solution to design tests in a way that you cover all of the major pairings of tests, but without having to run every single combination exhaustively. Orthogonal arrays were first developed by the statistician R. A. Fisher in the early 1900s. Fisher's work was motivated by the need for efficient designs for sampling from discrete distributions. He showed that certain types of matrices, now known as orthogonal arrays, could be used to construct efficient designs. Orthogonal arrays have since been used in a wide variety of applications, including statistical design and analysis, computer science, cryptography, and communication theory. More recently they were popularized by the work of Genichi Taguchi in the Ford Motor Company with Bob Moesta, the co-creator of the “jobs to be done” framework.
An orthogonal array is a matrix that allows you to map out all of your variables, and options for each variable, and then pick the least number of combinations to test. For example, if you were testing three variables, with two options for each variable, you would use an orthogonal array with three columns (for the three variables), and six rows (two options for each variable). If you were testing four variables, with three options for each variable, you would use an orthogonal array with four columns (for the four variables), and 12 rows (three options for each variable). And so on. In decreasing the number of experiments that need to be performed, this test design technique gets you to better results quicker, but without the risk of missing a potential winner. If a single value for a variable is performing badly, that’ll show up in the data wherever that value is present. If two values are interacting badly with each other that’ll also show up in that unique pairing (each pair only appears once). If more than two variables are interacting you’ll still see what region this issue shows up, and that gives you an idea for where to drill down for further testing.

Test Design

Hey, let's walk through how to use orthogonal arrays to decrease the number of tests you need to run without potentially missing any winners.
So these are the experiments we wanted to run. We have our hero text we have our description, and we have our call to action.
This is for our website. We have three different options for each. We don't know which combination of options is gonna work, so maybe next generation smart babe works really well with, you know, why is everything else in your house smart?
And that, and, and that combined with buy now works well. But, you know, we wanna test all these combinations and we, you know, don't want to potentially miss out on a winner.
So to test all these combinations, we'd have, you know, three different options. It's a power of three different variables, so that's 27 different variations.
So that would be quite a big test if we ran that all side by side. And most of those variations switch fail, right?
So we don't wanna run too many experiments. Were waste traffic take a lot longer to get results. And maybe the test would even be insignificant because, you know, we don't have enough traffic to test that.
So how do we cut this number of variations down? Well there's an article here which you can take a look at, which is linked on experiment design.
Kind of walks you through how to do this with a different example. There's also a link here to this lookup table.
So that's what I'm gonna show you is this lookup table here. So what, these are orthogonal arrays. And basically they're just a combination of pairs.
So you can see like, you know, we were three to the power three. This is three to the power forwards, the closest one that we have.
So just gonna copy and paste that across so can make that into a new grid. And then actually before you paste you wanna maybe just copy, click on that, and then go format as plain text so we don't lose the zeros.
There we go. So now that's paced across and we have all the different zeros. So the reason you can see it's pairs is you can see you know, we have a combination of zero and zero, zero and one, zero and two, one and zero, one and one, one and 2, 2, 0, 2, 1, 2, 2.
So like every potential pair, but it's not like every single combination. Like if you had three to the, you know, three to the power of four that would be 81 combinations, right?
So that's what this square is coming in down to the, just the most important nine combinations. And you know, we, we don't need three to the power four.
We need three to the power three. So we can actually just chop off the one on the end. So the way I kind of get this array out here is if I, if you go you know, this is 1, 2, 3, 4 and then I'm just gonna use the mid formula.
So we'll take a string and we're starting at one in this case, and we're just extracting a length of one.
And there's a couple things we need to do. We need to lock this with the dollar sign so that it, when you drag it across, it doesn't look for a different string.
And then we need to lock this with the dollar sign, just the number one.  So that we always should look at the top column here for this lookup.
Cool. So it's doing autofill here. So you can see it's extracted. Just the first thing here. 0, 0, 0, 1, 1, 1, 2, 2, 2. Cool. And if we drag this across, then it should split everything out for you.
Cool. So that seems to have worked. Now like we said, we don't need the actual last values here because we just have three variables.
And then all we need to do really is, you know, copy and then control or command shift v just to get the values here.
And now we have the specific values for the grid. Cool. So now we just need to replace our names to map our experiments.
So we have option A, B, and C for the hero text description is cta. So this is the hier text, this is the description, and then this, the cta.
Okay? And now we have all these like ones and zeros. So how do we, how do we replace those? So we're just gonna create a new grid here.
I'm just gonna say replace. And then if it's that or it's actually, maybe it's substitute be better. Yeah, so text to search, search for actually not this be easier with the V look up.
So it's just gonna v look up this. So yeah, there's usually multiple different methods you can, you can use to do replacements and stuff.
But I'm just gonna use this one. I think this will be easier. So index is one. So what am I doing here?
I'm taking this I'm gonna actually move this across, say like, I'll put my array here. Don't worry. I'll fill that in in a second.
I'm gonna press F four to put dollar signs in front of each of that. Cool. So so what this is, oh, this should be two.
So what this is saying is take this value of b2, look it up in this box over here, and then give me the second value or the second column in that box when it matches.
So right now there's nothing in that box but we're gonna put 0 1, 2 and then A, B, C and that means when there's a zero, we're gonna replace it with a, when there's one replace with B, when there's a two replace with C.
Cool. So we should be able to just drag that down and then drag that across. And there we go. So now we have A for zero and then C for two, B for one.
So we have that. So now we know which options we need. And you can just, you know, draw the over that.
If, if you want or in our new array maybe we just, you know, this is our array here. And then this is gonna variation, And This is 1, 2, 3.
Then just track that down. Cool. So now with nine tests, so nine test variations. Now we can test all these different variables and and, and we're gonna, you know, not gonna test every combination every, you know, of all 27.
But we're gonna get a good idea of what region shows the most promise. So for example, let's say we've run this experiment and let's say in the results you know, this is 0.3.
So this the 30% or yeah, 30% conversion. Really, this is 20%. Say it's all 30% on average. And then this one has 10%,  On average.
So you'd be able to see that you know, this looks like a really bad variation, right? This value of c is bringing down the average.
So that's something that you might wanna look at, right? Because that is like an issue, right? So, you know, whenever anything would match with value, see for the hear text it performed badly.
So you can look back and say, Okay, this doesn't, maybe an mention anti choking device in the copy is, is what's performing badly.
So that would be like a, a single variable anomaly, right? The second variable anomaly or, you know, pair-wise anomaly two mode anomaly, whatever you wanna call it, would be if you know, one of these pairs ends up performing badly or, or really well whichever, whichever way it goes.
So say for example maybe this a and B performed, you know, 50%, right? So that's a really good sign, right?
We have this one that, you know, performed really well. And if you look, you know, this is, this is the only combination where it's, you know, this is the only combination where the, that pair appeared, right?
Because you know, it's only pairs, right? So we have, we only have b value for hero text here. The only around three tests for hero text.
And this was the only one where that was paired with a now it's also the only one that was paired with C.
So one limitation here is we don't know if it's the B and if, you know, if it's the hero text and the description interacting here, or if it's the hero text and the CTA interacting or all three.
So that's one limitation of this result. But now we have an area that we can drill down. We know that this combination of you know, of bac seems to be the most fruitful.
And this is like the best performance. We know that we can probably rule out value C for the here text in the future.
And we know that we need to drill down into this. So what we could do is now that we know that it's you know, this, this combination is working really well we can start to kind of run all the, all the other potential combinations here as well.
So we can try B and A with A, B, and C, and we can try, you know the hear text B with you know, the three different descriptions and then C for the cta.
And so we can drill down and just kinda hold those constant and figure out what actually, was it the combination?
Was it the combination of being the hear text description B or is it like the CTA in hero text or whatever, what have you.
But you know, we don't have to worry about any of these tests. We don't have to worry about any of these tests.
So we've ruled out quite a few of the 27 different tests that we would normally have to run. And we have a variation that is performing better already with only running nine experiments.
So this is really helpful technique. You know, it's it's just one of many kind of test design, test test design and like prioritization techniques you can use.
But I found it really, really helpful, especially when there are lots of different variables that all might interact in different ways.
Okay, Hopefully that was helpful.


If you had 5 different variables you wanted to test, with 4 different options each, how many variations would that be?

How many tests could you run if you were using an orthogonal array for 5 variables and 4 options each?

If you have 3 options for 2 variables, you have 9 potential variations. Why can’t orthogonal array testing decrease that number?

What’s the benefit of using orthogonal array testing?

Can you think of any downsides to orthogonal testing?

You always have more ideas than you could test. You can brainstorm and come up with 9 good ideas in an hour, but that could be 3 ^ 3 = 27 different variations, and you're lucky to have the traffic to test maybe half that. It's frustrating because you don't want to leave potential winners on the table.

We've got too many test ideas

You’re preparing for the end of quarter reporting, and part of your job is to provide a list of the leads by channel. Unfortunately the output you got from the development team is all joined together.

One thing you can handle for me
Please take this report from the dev team and untangle it
Right now the source, medium, and campaign are all joined by pipes
But we need a pivot table just showing leads by medium and source
Thanks!


In Excel and Google Sheets the LEFT, MID, and RIGHT functions are useful for a wide range of operations where you need to extract text from a cell. The LEFT function is useful for grabbing all the text to the left of a specified number of characters in. The MID function extracts the text starting at a specified number of characters in, of a specified length. The RIGHT function starts from the right and extracts everything that’s a specified number of characters to the left of that. There is also the LEN function, which tells you the length of the string in a cell. Combine these with the FIND function, which lets you identify how many characters into a text a specific string or character is, and you can flexibly and automatically extract almost any text from a cell. For example if your marketing source is delimited by pipes (|), you can combine these formulas to extract out the source, medium, and campaign into separate cells. These formulas are more accessible and understandable than using REGEX, but more flexible and useful than Text to Columns, so they should have a place in any data-driven marketers’ toolbox.

Text Extraction

00:01 Hey, okay, let's learn how to do a report by channel. And this is a good example of the type of internal messy data that you might get from a development team.
00:13 This is actually based on some real data that I had. Basically we were recording the source medium and campaign for each you know, campaign that came through you know, both organic and also the paid campaigns.
00:28 And we were storing that when someone submitted a lead. So this is some internal data. You might have it set up in a different way.
00:35 It really depends. But I found a lot of companies have these custom setups where it's like you're not dealing with Google Analytics or something else that has an easy ui, and therefore you get the report in a format that you need to edit in order to get the real data.
00:49 Cool. So what we're trying to do is get a report that looks like this. We wanna split into the medium and the source and also the campaign.
00:57 But for our report, we want to be able to show, okay, you know the total you know, total CPC leads was 4,207, and the majority of that was Google, for example.
01:11 So this is a pivot table and we'll, we'll kind of walk through how to do that. But the first step in order to do that is to split out the source medium and campaign right now.
01:22 So there's a couple of different ways you could do this, right? Actually one way is, you know, if you copy all this, so I just did a control shift down.
01:32 And then control C to copy is command shift down if you're on a Mac the control shift v if you paste it there, and then if you do you know data and then it's like split text to columns and then you can say custom you can, they're actually separated by pipe then you could do it that way, right?
01:54 So that actually works pretty well but it's not very flexible. So you have to you know, have to use the pipe, right?
02:01 Like you have to use a com has to be a very consistent format. But the way I'm gonna show you which doesn't use the pipe, sorry, doesn't use text columns, it uses left mid and right.
02:15 It's much more flexible. If you want to get really flexible you can use red X, but I find red really hurts my head.
02:22 And you know, most people who don't use it every day have to look it up every time they use it because it can get really complicated.
02:29 So usually I recommend, you know, just doing something simple with left mid and right. Cool. Okay. So I'm gonna show you how this works and then once you know how to do it, then you'll, you know, be able to do it anytime you want.
02:41 Okay? So first thing I wanna do is I wanna find the first pipe, right? And so we use a find formula and find formula just asks for you asks, where do you wanna search?
02:55 What do you want? Oh, sorry, what do you wanna search for? Where do you wanna search? And then where do You wanna, wanna start?
03:00 So we wanna search for the pipe, which is gonna be in that cell. And then we'll just start the first character.
03:12 Cool. So you can see I just entered and then you can autofill or you can just double click, you know, on the, on the square and it'll drag down.
03:19 But you can see that this tells you how many characters along the first pipe is, right? So you know, if if I deleted three characters there, then that would go down by three characters, right?
03:29 So this is the first step because you want this to be programmatic, right? When we, you see the left formula in a second, actually, how many characters you wanna go in.
03:39 And because this isn't a consistent number of characters in, you know, these two are seven characters in, but this one's 17 characters in.
03:46 So you need something that works automatically, therefore you need the fine formula. So that'll become clear in a second. But first, let's do the second pipe, right?
03:56 And to do this, we're still gonna use the fine formula. You know, we're gonna search for the pipe and we're gonna search in that cell.
04:03 We're gonna just do one difference, right? We're gonna say, we're gonna start on the character that has found here. And not just that character, but just play plus one.
04:17 So we're gonna start the next character over and here we go. So now if we drag that down, you can see here there is actually a pipe straight after, cuz there's nothing between.
04:26 So this starts on Character eight whereas this one you know, the first pipe is at character seven, and the second pipe is at character 15.
04:34 Cool. Now we've found the two pipes. Now we can have fun with our formulas. First one is left, right and we're gonna use that to get the source, which is the first thing here.
04:46 So left you just need string and number of characters. So this one's the easiest. That's the string. And then the number of characters is seven.
04:57 And that's what you found here. So there we go. We can see we pulled out the source. It does bring the pipe along with it, so that's pretty easy to fix.
05:06 We just minus one, and then you can double click. Or if that doesn't work, press control sheet. This is it.
05:13 Control C, control shift down, and control V. And there we go. Cool. Now we have the source. So that was pretty easy.
05:24 Now what do we do for the mid, right? So mid is similar to the left, but instead you give it the string and then you give it where you wanna start.
05:35 And in this case, we're gonna start at the first pipe, cuz we want everything after the first pipe. And then you give it an length, right?
05:44 So how many characters do you want? And to do that, we just need to take where the second pipe is and subtract it from where the first pipe is, and let's see what result we get.
05:56 Cool. Okay, we got It. It, so it's actually pulled out, you know, here, we, we started at seven and then we came across and we, you know, took out, you know, 15 minus seven.
06:07 So you know, there's eight characters. We took out that organic in the middle. You can see we're still missing the pipe.
06:13 And so we still got this pipe in here, so we can actually get rid of that. We can do minus one.
06:19 Oh, nope. Okay, so we're gonna start at, sorry plus one and just see if that works. Okay, yeah. So now we've got this, you know, got rid of this pipe, but now we've grabbed this pipe instead.
06:36 So here we want also subtract one. And, you know, rather than like memorizing this in your head, honestly, I find it's just much easier to just like do it and then add or subtract one or whatever it's bit, once you see what you get.
06:53 Cool. Okay. So all I did was, you know, control shift and then down to select the whole thing and then control V.
07:00 So now the function is across everything. Cool. Okay. Now final one, and this is the most interesting, I think is campaign.
07:10 And we're gonna use the right function. Now what the right asks is just the number of characters you want from the right.
07:19 So it's gonna get the string, and then we need to know how many characters we're gonna go from the right.
07:24 Well, this is eight characters. From the left is when the second pipe hits. And we want basically to know how many characters are there in between that and the end.
07:35 And to find the end, we just need to do the length, right? L n and then, and then we just need to subtract from el this location of the second pipe, right?
07:48 So we're basically you know, if there was 10 characters long string and the second pipe was two characters from the end then we're basically taking you know, we're taking like eight from 10, or we'll get to the two, right?
08:02 Here we go. So there we go. That has worked. Now we have all of the ETM campaigns. Cool. Okay. So now that we've done that now we just need to do a pivot table, which should be straightforward.
08:19 You just click anywhere and press control. Shift a, oh, sorry, just control a and then or command A, if you're on a Mac, and then insert and then pivot table.
08:29 And you can put that on a new sheet. And all you need to drag in here are the rows. You want to have your medium, because a medium will have different sources.
08:40 So that's the parent. And then you wanna have your source, you can untick the totals from medium. And then the values are just gonna be those leads.
08:51 And we have the sum of leads. Cool. Last thing we should probably Do is sort by some of leads and then sort descending.
08:59 And then same thing for in the source sort. Descending, there we go. So now we have all of our leads at the top, you know, so our biggest medium is blank.
09:11 And then second biggest is cbc. Third biggest is referral, right? And we can see, oh, here are the biggest referrals, right?
09:19 Indie Hackers is our biggest referral. Or facebook.com. And facebook.com. Cool. So this is how you do a pivot table is also how you do the right formula, the mid formula and the left formula.
09:34 And you could do this really for anything. So you know, for example, you know, now this has separated by dashes.
09:41 So you could do basically the, you know, the same thing here. And I, I, you know, I can put all these things together you know, but you know, you can actually put them in the same function, right?
09:52 So here I'm just putting sorry, just putting that here. Text to search, all right. And then closing it off. Yeah.
10:04 So there you go. Now I found everything like to the left of the T, right? Or you can like minus on.
10:10 Yeah. So there you go. And then you everything else. So if it, if it's a value, if it doesn't have it, then it'll be an error.
10:19 So anyway, now you know how to do it. You could really pass it in any which way you want. Obviously text columns would be quicker if everything is nicely formatted.
10:27 But if you have a mix of different formatting, everything you know, or, or like you're trying to pull out information from you know, from ads or whatever you want to use this for then you'll find a bunch of situations so that this will help you.
10:42 Okay? Hopefully that was useful.

Which function can be used to grab a string of specified length a number of characters in from the left?

When might it make sense to use Text to Columns?

What 2 functions do we use in conjunction with the RIGHT formula in order to calculate the number of characters from the right?

Name another task that combination of functions might be used for?

Almost nobody is as good at Excel as they pretend to be in job interviews. One thing I see is people manually finding/replacing row by row to compile simple channel reports.

Compile a report by channel

Ultra’s Offices – Annual Planning Meeting

You’re talking to your boss about setting marketing budgets for next year, and he says that they use the Advertising to Sales ratio to set it.

So what we do, it’s quite simple
Is that we calculate the CAGR
The Compound Annual Growth Rate
Then we figure out what revenue will be next year based on that growth rate
Then we just set marketing budget as a percentage of revenue
Usually we use last year’s percentage of revenue as a benchmark
Can you figure out those calculations for me for the next meeting?

The Advertising to Sales ratio is a measure of the proportion of a company's sales revenue that is spent on advertising. It is calculated by dividing the amount spent on advertising by the company's total sales revenue.

For example, if a company has total sales revenue of $1,000,000 and spends $100,000 on advertising, its Advertising to Sales ratio would be calculated as follows:

Advertising to Sales ratio = $100,000 / $1,000,000 = 0.10 or 10%

In this example, the company's Advertising to Sales ratio is 10%, which means that 10% of its sales revenue is being spent on advertising.

The Advertising to Sales ratio is a useful measure for companies, as it helps them understand how much they are spending on advertising relative to their sales revenue. It can also be useful for investors, as it can provide insight into a company's marketing strategy and how it is investing in advertising to drive growth. It’s also helpful when attribution is hard to measure, for example if the company is doing a lot of brand advertising, or if user-level tracking data is unavailable or unreliable.

The majority of companies use Advertising-to-Sales ratios to set marketing budgets because it’s a relatively easy and simple method which can be fully controlled by finance, requiring no domain expertise in marketing. 

It allows companies to allocate a consistent portion of their sales revenue to advertising, which can help ensure that they are investing enough in marketing to drive growth. It also ensures companies avoid over- or under-spending on advertising relative to their competitors, as well as allowing straightforward comparisons between the advertising budgets of different companies, which can be useful for investors and analysts.

An alternative might be setting marketing strategy first, building a tactical plan based on that strategy, and using probabilistic modeling such as Econometrics (Marketing Mix Modeling) to estimate the potential future impact of each marketing channel. Based on the tradeoffs between volume and efficiency, marketers can choose the point in which they maximise the effectiveness of their marketing campaigns and advise finance on where that level is. 

This strategy-first technique however requires advanced attribution and marketing domain expertise, and is open to bias given marketers are expected to justify their own costs using a technique finance might not fully understand. This is why the uncomplicated Advertising-to-Sales technique dominates marketing budget setting, with an estimated 90% or more companies using it, according to marketing professor Mark Ritson.

To use Advertising to Sales determine what marketing budget to set for next year, you first need to estimate what next year’s sales will be. To do this it’s common to use the CAGR, or the Compound Annual Growth Rate of the company, and extrapolate to apply the Advertising-to-Sales ratio to next year’s estimated Sales, assuming growth continues at the same pace.

CAGR is a measure of the average annual growth rate of an investment over a specified period of time. It is regularly used to measure the growth of investments such as stocks, mutual funds, and index funds, as well as company revenue.

CAGR is calculated by dividing the value of an investment at the end of the period by its value at the beginning of the period, and then taking the nth root of the result, where n is the number of years in the period. The resulting value is then subtracted from 1 to give the CAGR as a percentage.

For example, if the value of an investment starts at $1,000,000 and grows to $1,500,000 over the course of 3 years, the CAGR would be calculated as follows:

CAGR = (1,500,000 / 1,000,000)^(1 / 3) - 1 = 0.14 or 14%

In this example, the CAGR of the investment over 3 years would be 14%. Put another way, the value of the investment grew by an average of 14% per year, every year for 3 years.

CAGR is a useful measure because it helps investors compare the growth of different investments over time, regardless of their starting and ending values. It is often used in conjunction with other measures such as return on investment (ROI) to evaluate the performance of an investment.

As is common with startups, if you haven't completed a full year and need to estimate your CAGR run rate, you can use the following formula:

CAGR run rate = (current value / beginning value)^(1 / (number of months / 12)) - 1

Here's an example to illustrate how this formula works:

Let's say you have an investment that starts at $1,000,000 and grows to $1,100,000 over the course of 9 months, with the growth occurring evenly each month. To calculate the CAGR run rate of this investment, you would first need to divide the current value by the beginning value, which in this case would be $1,100,000 / $1,000,000 = 1.1.

Next, you would need to divide the number of months, which in this case is 9, by 12 months in a year, so the next step would be 9 / 12 = 0.75. Divide 1 by this number to get 1 / 0.75 = 1.33.

Finally, you would need to raise the result of the first step (1.1) to the power of the result of the second step (1.33), and then subtract 1 from the result. This would give you the following:

CAGR run rate = (1.1)^(1.33) - 1 = 0.14 or 14%.

Therefore, in this example, the CAGR run rate of the investment over 9 months would be 14%.

If the company has grown for several years and a partial year, the formula remains the same, other than adding the years to the partial year calculation. For example if you grew for 1 year plus 9 months, and arrived at $1,250,000, then you would add 1 year to the partial year of 9 months, which is 1 + 9 / 12 = 1.75, which you would divide into 1 as 1 / 1.75 = 0.57, and the rest of the formula is the same, for example you would divide 1,250,000 by 1,000,000, which equals 1.25, then take that to the power of 0.57, then subtract 1.

CAGR run rate = (1.25)^(0.57) - 1 = 0.14 or 14%.

Once you have the CAGR for your company, just take this year’s Sales and apply that growth rate, to get next year’s Sales. From there you can apply the Advertising-to-Sales Ratio to that figure, to determine next year’s marketing budget.

So for example if our CAGR was 14%, and last year’s Sales were $1,000,000, we would expect to hit 1,000,000 * (1 + 0.14) = $1,400,000 in Sales by the end of this year. That means we expect to reach 1,400,000 * (1 + 0.14) = $1,596,000 in Sales by the end of next year. If we look at the Advertising-to-Sales Ratios for our industry and find they are 10%, we would multiply 0.10 * 1,596,000 = $159,600 which we would allocate as the marketing budget for next year. Note that no marketing budget is set in stone: budgets are liable to be cut part way through the year if you aren’t hitting your growth targets, or can be negotiated upwards if you can convince finance that you have an opportunity for outsized returns.

The downsides of using Advertising-to-Sales Ratios to set marketing budgets is that it enables poor marketing practices, due to a lack of accountability for performance. If advertising budgets are always tied to total Sales, marketing budgets automatically increase even if it wasn’t marketing that was responsible for driving sales up. It also doesn’t take into account competitive moves in the market, for example if you use an outdated or category level Advertising-to-Sales benchmark, and your primary competitor doubles their budget this year without warning, you could be left with a much lower share of voice in the market. The recommendation is to use a combination of Advertising-to-Sales Ratios as well as Share of Voice benchmarking and bottoms up Tactical budget setting to triangulate a final budget.

Advertising-to-Sales Ratios

00:01 All right, let's learn how to set your marketing budget by advertising to sales ratio. Okay, so what we have here are, are sales, and this is cumulative.
00:10 So you know, end of 2020, we got to, you know, 2,700,000 sales. And then it's just been growing by, you know, a small amount per month.
00:21 By the end of December, we had 3.3 million in sales. So the formula for kaga or compound annual growth rate is here.
00:33 And we're just gonna kind of break down these steps and kind of go through it. The goal at the end is really to predict, you know, how many sales we're gonna get in 2023, which we've done here, and then and then work out the advertising budget that we should set based on that.
00:47 So I'm just, you know, I'll walk you through step by step, just so you know, you understand the calculations. Cool.
00:55 So whenever I'm doing a formula what I try and do is I just kind of write out the different parts of the formula in the spreadsheet, makes it easier to understand.
01:04 So, current value beginning value, these are two variables we need. And then number of months as well. So there's also the current divided by beginning which we're gonna put here.
01:16 And then this part of the formula, which is one divided by the number of months divided by 12. And then the kgo, we're gonna bring all that together by making this value to the power of this value minus one.
01:29 Cool. So if we start with the current value, that's what we have right now.  And it's 3.9 million and the beginning value, so where we started out was 7 million fill.
01:48 So just to divide these we just go G eight divided by G nine and that's 1.42. So the number of months I'm gonna use account a formula.
02:05 Count A is good for you said, counting all the different values or how many values there are. And then, so 21 months here no, it's the number of months from the starting values.
02:18 So you don't include this first one here. And then this function works out at, you know sorry, one divided by the number of months, which is there divided by 12 0.57.
02:35 And then we have everything we need to work out the compound in your growth. So gonna use the power function here which is the base to power of an exponent in this case.
02:47 So this, you know, upwards tick here is how you denote power normally. So it's basically saying this value to the power of that value minus one.
02:56 So this value is the current divided by beginning value. That's, that, that's What what we worked out here. And then the next part is this exponent, and then we just a minus one.
03:07 And there we go. So we are growing at a compound annual growth rate of 22%. What that means is with you know, with, with the you know, number of months that we have basically like per year we're, we're growing on average 22%.
03:28 And the reason why this is useful is just because then you can compare different time periods, you know, you can compare different investments.
03:35 So if you have multiple companies that you're invested in or multiple brands in your portfolio or if you're just comparing stats with friends or, or col know other people in other companies contemporaries, then you know, then you can, you can work out your CAGR and say like, yeah, we're growing 22% a year, and that's yeah, that's good or bad depending on the, the size of the investment and the industry that you're in.
03:59 Cool. So now that we have the kga we can work out a few other things and, and then we're gonna get to the appetizing to sales ratio.
04:07 So 2021 sales that was this here. So we ended on 3.3 million by the end of December 22 and two sales, we have to figure out, because we haven't finished 2022 yet, we, you know, only partially through the year which is why we had to kind of use these number of months here.
04:33 So how do we figure that out? Well, it's just basically the starting value from 2021, and then you multiply that by in brackets one plus the kago.
04:47 So if we grew 22% year on year, then we're gonna end up at 4 million. And that doesn't look, a 4.1 million doesn't look like we're far off that, so I think that's probably realistic, right?
04:58 So just sense check that when you're, when you're calculating it. Okay? And that's not enough though because we're, you know, we're in September, we're working on an annual plan for next year.
05:07 So you know, we already have a marketing budget for this year, and that's playing out. What we need to figure out is what's the sales gonna be next year, because we want a percentage of that budget.
05:17 Basically the, the theory here is if you always set a percentage of your annual budget or a percentage of your annual revenue and devote that to marketing, then you're gonna kind of keep up with the growth that's needed.
05:31 Cool. So in order to do that, we'll just multiply the estimate for 2022. So that's what we're gonna end up by the end of the year.
05:38 And then we multiply that by one plus the kgo, and that means we're gonna hit 5 million by the end of 2023.
05:47 Now, obviously lots of assumptions here. This is just very simplistic. But actually this is, you know, what 90% of companies do.
05:55 You know, you, you wouldn't believe it, but but yeah, like this is usually what finance does when they set a budget for you and pass it along to the marketing team.
06:05 There's so there's the advertising to sales ratio. We have to figure out. Now, we could use this year's budget for marketing but I think that's a little bit problematic.
06:15 So you know, say for example you know, we had set like, you know, a million dollars for budget this year.
06:25 Well, we're not through in the year. So you know, and we set the budget last year, so maybe we set it to optimistically, in which case maybe, you know, a million dollars divided by the actual growth we got.
06:37 Like, if we got less growth than expected, then the advertising to sales ratio would be massive, right? Like, if we expected to get to 5 million, we only got to, you know, 4 million then you know, if we set a million dollar budget instead of being, you know, 20%, it's gonna be 25% of the the revenue figure.
06:57 So that's really bad because it means that essentially you know, the worse you do this year, the more budget you're gonna get next year as a percentage of revenue.
07:06 And if you do really, really well, you're gonna be rewarded by slashing the budgets. And that's the opposite of really what we want.
07:13 I mean, you could argue that like, if growth is worse than expected, it's gonna cost more to grow faster. But I'm generally of the belief that you know, funding should follow growth.
07:23 It shouldn't, it shouldn't force growth. So you know, if you know, you're not growing as fast as you expected then I would say, you know, cut the budget and work on your product until you do get the growth back.
07:37 Or maybe the market's not the right time to invest, you know, that budget could be used to invest in a different business, right?
07:43 There's no use making this business work or this brand work. If there are better opportunity costs, better KRS out there to have.
07:51 But yeah, philosophical point. Okay, so so what I would say is what we're gonna do to avoid that mess of missed expectations is to look at the last year in which we had an actual, you know, actual full data set.
08:06 And that way there's nothing to be gamed.  So if we look back at our first sheet our budget in 2021 was 925,000.
08:14 So lemme just, and that means advertising to sales ratio would be the final amount for 2021 oh, sorry, a budget divided by the final amount for 2021.
08:31 So it's 27.38%. And what we're saying basically is, you know, let's keep that consistent, right? We're a bigger business now than we were back then.
08:41 So let's also spend 27%, but of the 5 million that we're expecting to hit. So all we'd need to do now is multiply the 5 million by the 27%, and then we have our 2023 marketing budget, 1.367 million.
08:59 Cool. So that's how actually, like most businesses work out their marketing budgets there are obviously some downsides. It means that like you're gonna get a budget no matter what.
09:10 So even if, I don't know the, the business grew despite the fact that marketing was terrible marketing doesn't deserve credit, like marketing is still gonna be rewarded with a bigger budget.
09:21 It also kind of decreases the need for attribution. And this is why so many companies spend a lot of money on brand advertising without real proof that it's working is because they're just allocating a share of the total, total revenue to that the treating marketing as a cost rather than an investment.
09:40 So that is something to think about. But I would say even if you're building a bottom up budget and you're kind of doing proper attribution, doing everything right, it's still you know, useful to, to sense check this and see, know, we over under investing compared to competitors, you know like is 27% good for our industry or bad for industry.
10:01 And, and I usually like to kind of come at their budget from multiple angles, you know, top down like this, as well as bottom up from the tactic level and, and, and kind of attributing how well those tactics are gonna work.
10:13 Cool. All right. Hopefully that's useful.

What might the negatives be of using Advertising-to-Sales ratio to set budgets?

What might the benefits be of using Advertising-to-Sales ratio to set budgets?

Why benchmark the Advertising-to-Sales ratio based on last year’s budget?

The year is now completed and the CAGR was actually 30%. We’ve heard back from finance and they want to use a 25% Advertising-to-Sales Ratio for 2023. What should the budget be adjusted to?

This may be surprising to you but >90% of companies just set their marketing budgets as a percentage of next year’s revenue forecast.

How should we set our advertising budget?

Bank of Yu Offices – Performance Meeting

Your boss wants you to optimize further down the funnel, to people who activate their bank cards, but that takes between a few days and weeks for people to do. We can’t wait 28 days for the full conversion window to catch up, because we need to be responsive to market opportunities or performance issues. However when we compare last week to this week, this week’s performance always looks bad as last week has had more time for conversions to be recorded.


We really want to optimize to activations
But to do that we need to solve this lag issue
I’m wondering if we can just record what we would have seen on day 3
So for example instead of looking at data for last wednesday, which has had 7 days for people to activate cards, compared to this wednesday, which has had 1 day
Instead maybe we could cut the window to 3 days
So instead we’re looking at what wednesday’s performance would have been last saturday
Does that make sense?
Cohort the data, and filter for day 3 conversions
Take a look and see what’s possible
I’ll hook you up with a developer to record and pull the data


One thing that marketers consistently get wrong is not thinking in cohorts. When you spend money today on advertising, you aren’t guaranteed that the full benefit of that spend will occur the same day you spent the money. This is especially true for products where it takes some time for users to complete the conversion event. So for example if you had a banking app and some share of users take more than a week to convert, looking at performance week on week will be problematic as you’ll always be undercounting the current week. This might mean getting your spend allocation wrong by underspending, only to later see that the cost per acquisition (CPA) has come in better than expected later on as conversions ‘caught up’. This is an issue because it limits your volume when times are good, meaning you miss opportunities. It also can be a problem the other way, in that when times are bad and performance degrades, you don’t cut spend quick enough as you’re not sure where things will land after conversions come in. Waiting for a few weeks to make optimization decisions is too slow, and leaves serious money on the table.

The solution to this that I’ve found works is to think in terms of cohorted conversions. Rather than comparing last week to this week for example, compare day 3 conversions for both weeks. That way you’re comparing like for like – always a 3 day window – rather than giving last week an extra 7 days worth of conversions. To do this you need to record both the day that you’re reporting on, and the day in which you recorded the data, every day for the full conversion window (usually 28 days). Once you’ve done that you can artificially limit the window to 3 days, or whatever works best for your product, and compare apples to apples. What you’re in effect doing is looking back in time at what you would have seen if you looked at the report 3 days after that point in time, rather than what you see now. The hope being that if your Day 3 conversions are a consistent share of what your eventual Day 28 conversions will be, you can make optimization decisions in days instead of weeks, and be more confident of where you’ll end up when you look back on this time period next month. Working this way gives you a consistent and stable benchmark to compare against, and increases your flexibility in being able to respond quickly to opportunities or performance issues.


Cohorted Conversions

00:02 Hey, have you ever had this problem where the cost acquisition goes really high in the past few days? But then after you optimize and maybe you, you know, decrease spend you eventually see it come back down you know, to normal levels.
00:17 Well, you know, this is pretty common with apps or, you know, businesses, products where the conversions are lagged. You know, in this case, this is a finance app where it takes a few days you know, usually for people to activate the account.
00:31 And this is a, you know, account activated conversion. So the problem with this issue is that, yeah, you could kind of, you know, I guess like, you know, just optimize after you had a week's worth of data you know, and, and not look at this past week.
00:48 But that's not really good enough because you wanna make you know, decisions in real time.  And you can't really trust this data cuz even though it says it's a hundred dollars now, like you've only had 40 activations, you might have like another 60 or, you know, 160 come in over the next few days for that time period.
01:07 So how do you solve this problem? Well the way that I've done it in the past is with cohorted conversions.
01:13 So let me show you what that looks like. The important difference between, you know, conversions and cohorted conversions is that you're kind of defining a window to look back to record the conversions.
01:29 So let me just take, you know this as an example. So we have the report date so this is for the date of the 1st of December.
01:39 And then you have the record date. So that is the date it was actually recorded this number, right?  So we have, you know accounts activated here.
01:50 So if we, so if we, you know, filter down to, you know, just a specific date, so this is the, you know, 21st or so, yeah, 2021, let's say this day here.
02:04 Cool. So, and then let's just filter down to a specific conversion event here. So you can see here you know, for the the 31st we just have, you know, zero days.
02:17 We, we recorded the data on the same day and then we recorded the data one day after, and then, you know, two days after, and then three days after and split by campaign, you can see that let's just look at Android.
02:32 It went from like two activations, two activations, and eight activations, and then four days, it was 15 activations for Android.
02:38 And then you know, and then there was 21 activations for Android. So there's a big difference between when you actually recorded the data for that date and when the actual date is.
02:50 So the way that you get data like this is you set up, you know, some sort of report or job that basically just records the Data for each date, for each campaign for the last, say 28 days every single day in a spreadsheet.
03:06 So you can do that in something like kitchen dot io or Supermetrics funnel io or you could set it up yourself with like a Google Cloud function or AWS slam it and, or, you know, talk to a developer to do that for you.
03:23 But once you have the data like this, then you can do something interesting. So I'm just gonna, you know, filter all these back and go to this cohort tab here.
03:36 So what this is showing you is this is the date. So this, you know, this is like the, the fourth of December, 2021.
03:44 And this is the number of days since it's been recorded. So this is the data we recorded for that date on, on that day, and then a day after, then two days after, and so on.
03:56 And you can see you know, I filtered this just for the, you know, the 4th of December onwards. You can see I've got like a full 28 days worth of data for that date.
04:07 But then obviously as we go forward in time then you know, so this date here, you know, this row, the 17th row 17 this is the 18th of December for, for last year.
04:19 So at the time we did this report, we didn't have 28 days worth of data for this day, right? So we didn't have the full time to catch up.
04:29 And, you know, same here, like, kind of goes down in a diagonal because these dates you know, haven't had the full time period to catch up.
04:38 And then all the way through to, you know, that we did the report on the the 15th.  And we just had one day's worth of data, or on the 14th, we had one day's worth of data here.
04:49 So this can kind of visually show us the problem, like if we look, if we're optimizing to our cost per acquisition you know, for the, for yesterday we've only had 59 activations but like, we're gonna get more over time, right?
05:06 We just don't know what they're gonna be. So the intuition I want you to have is that when you're looking back in a previous window, so maybe you're comparing this week to last week last week has had more time to catch up.
05:21 So what you have to do is arbitrarily cut it off and the, you know, the way I usually do it is cut it off in day three and it gives you enough days worth of data to optimize to.
05:33 But like, you know, it gives you enough time. It depends on the product, obviously it gives you enough time usually to see early signs of how well that cohort is doing.
05:42 But like, you don't have to wait for too long. You'd have to wait for seven days or 14 days to, to prove it.
05:47 Cool. So so if you look here, if you're comparing day three, so obviously you can't optimize for the past few days, but like, you know, for the, for the 11th, we actually do have Data, data.
05:57 So we have 155, and then the day before we have 138, and then 1 74, 2 42. So when we're comparing, say like, okay, so like this is the eighth, and if you look we've got, you know, 1000, sorry on the eighth for the eighth we have now so far the latest data, if we put, look, the report on this day, you'd have 287 conversions.
06:27 And that's doing pretty well compared to say like you know, the you know, like the 28th, right? A hundred conversions.
06:36 So it's three times better, but it's actually even better than that because on day three we had 197 you know, compared to you know, say like 99, right?
06:51 So so we're kind of seeing the if you compare like day three conversions, you're getting an apples to apples comparison.
07:01 And if you're optimizing to day three then you can kind of trust that over time as these cohorts improve then you know, as they prove out then you're gonna, you know, have been optimizing to the right cpa throughout that period.
07:16 Cool. So one of the ways you can look at that, I'm just here looking at, you know, day 28. So like, these are the days we have 28 days worth of data for let's divide in this case let's divide day three by day 28.
07:30 So for those periods, so, you know, if we look at you know, the fourth yeah, we know how many conversions we had recorded on day three, 140 and by the end of the period 28 days, we had had 245 come through.
07:50 So day three was 57% of the total. And you can plot that over time. You can see that it pretty much goes up and down within a band of, you know, between 40 and and 60%, and the average is 50%.
08:05 So what that tells you in terms of practical terms is if you look at the day three conversion figures so like if we look at, you know we've got 149, we're probably gonna get another 149 by, by day 28, right?
08:22 Once we've got that data. So that's how you kind of optimize. So there's two, two ways you can look at it.
08:26 One is look at day three data and multiply it. But that gets kind of complicated. The best thing to do, I think is to filter, you know, in a report for day three.
08:36 So that's what I've done here. And the way that I did that is you just create a pivot table, just like we did before with the cpa.
08:46 And what you do is you just filter. So the CPA before we had had, we'd summarized by maximum conversions,  In this case we're just doing the, the sum but we're filtering for days since, right?
09:03 So it's just day three data. So if we take it off and put day two you know, then it's just day two data, or if we do it like, you know, 28 days and we can see that we only have this many days worth of 28 days data.
09:18 Cool. So I'm just gonna undo that. So because we're filtering just for day three so this is what we recorded on day three for the fourth.
09:27 This is what we recorded you know, on day three for the 11th. And it tells a very different story. So if we look at the CPA before it, it, that's telling us that you know, CPA has always been around, you know, below $25.
09:42 We had a small spike here, but then it's gone really bad recently, and that's actually not the case. It's the opposite the day three cpa, or, you know, in this case cost per day three.
09:52 I've called it. You know, day three CPA is always around, you know, 25, almost $30. And we had like a pretty worrying spike here.
10:02 And cause we were tracking this we saw that spike early, right? Like we didn't wait. We didn't just write it off as like, oh, we just have not enough conversions coming yet.
10:11 We spotted it early, so a couple of days after we started to see the d d three spike, we actually cut spend, and that was allowed us to protect our margins here.
10:21 So we managed to bring down the cost for day three. And then recently it looks like day three conversions have been really good.
10:29 So we started to raise that spend, and then we've seen kind of a, you know, a return to performance here.
10:35 So you know, if our long term goal is to kinda keep the cost for day three below $30, we're actually been, you know, well below that.
10:43 You know, so if our actual, you know conversion goal is like, you know, keep it, keep it around in this case, like, say 19 or $20 we, we know that like, you know, with a day three cost per conversion this low we're actually doing really well.
11:02 And when, when the results come in over time, it's gonna be well within our target. Cool. So hopefully that makes sense.
11:11 You know, the way that, you know, you would do this typically, like I said, would be to export both the day it was recorded and the, the amount.
11:22 And so you have like 28 entries essentially for any dates that, you know, have had 28 days.  The reason why we use 28 is just, that's the look back window in Facebook, so it stops recording any conversions after that.
11:36 Same for Google. So you know, that depends on the platform. But but yeah, this is this is just an intuition that you can develop.
11:46 You know, you could obviously build your own products around this or, or internal dashboards, et cetera. But this is the way that I've solved this.
11:54 Optimizing to the cohort, like where, where that cohort is or where it was back then is the key I think, to solving this delayed attribution issue.

Why can’t you compare last week’s performance to this week?

What is the sweet spot for cohorted conversions?

What do you need to set up to get the data needed?

How do you optimize your campaigns if conversions can take up to a couple of weeks to happen? If you compare this week to last week’s data, last week has had 7 more days to record conversions…

It takes too long to optimize to that

Vexnomics Office – Sales-Marketing Planning Meeting

In your quarterly sales and marketing planning meeting, somebody brings up the fact that we haven’t done any sort of analysis on the pipeline to see what leads are more or less likely to convert. Right now it’s entirely on sales to figure this out, but nobody sees the full picture.

Now we have a decent volume of leads, it makes sense to do a pipeline analysis
Each person on the sales team has a different idea of what converts
So it’d be good to zoom out and see what really leads to conversions at a macro level
What attributes and demographics, etc
Is that something you can handle for us?

Sales pipeline analysis is an essential part of the sales process, and marketing plays a crucial role in this process by generating leads and guiding potential customers through the sales funnel. One key aspect of pipeline analysis is segmenting the funnel into different stages and analyzing the attributes of leads at each stage to identify patterns and predict the likelihood of a sale.

By segmenting the sales funnel, marketers can gain a better understanding of the different stages of the sales process and the characteristics of leads that are most likely to convert into customers. This allows them to develop targeted campaigns and messaging that are tailored to the specific needs and interests of potential customers at each stage of the funnel.

One important factor in predicting the likelihood of a sale is the attributes of the lead, such as their demographics, interests, and behavior. By examining these attributes and comparing them to the close rate at each stage of the funnel, marketers can identify the characteristics of leads that are most likely to convert into customers.

For example, if a marketing team is analyzing the conversion rate of leads in the middle of the funnel, they may find that leads with a certain demographic profile or interest in a specific product are more likely to convert. By focusing their efforts on targeting leads with these attributes, the team can improve the efficiency of the sales process and increase the overall close rate.

Segmenting the sales funnel and analyzing the attributes of leads is an important part of sales pipeline analysis. By understanding the characteristics of leads that are most likely to convert, marketers can develop targeted campaigns and messaging that improve the efficiency of the sales process and increase the overall close rate.

Pipeline Analysis

00:01 Hi, I am gonna walk you through how to do pipeline analysis. So this is a useful piece of analysis to do regardless, like it's always useful to know what types of leads are more likely to close versus others.
00:15 But typically you would do this ahead of trying to define like what makes a marketing qualified lead. So if you're about to do some lead scoring this would be the preceding analysis.
00:26 And we do have a, a course of lead scoring as well. So we have here, you know, a list of leads and these have already been run through by sales.
00:35 Actually, let me just, you know, if I select this and I go format conditional formatting and then I want the text contains closed one.
00:51 Okay. So you can kind of see visually here, like very few of the leads actually close.  So you know, it's very hard to do analysis you know, very hard to optimize your campaigns.
01:04 And, and, you know, it takes some time for these leads to be kind of, you know, eventually filtered to the system.
01:10 So in this case, if it's a new lead that means that basically nobody picked up on it from sales you know, it hasn't been processed, so you can treat that as non-qualified.
01:22 Then the next stage is not accepted. So this is where you know, sales team have had a call, a fit call, like maybe 15 minutes, half an hour and then they've agreed that like there's actually not a good fit for that customer.
01:36 So that would be not accepted. So that, that is a lead that sales have touched. Opportunity comes before not accepted.
01:43 So opportunity is where sales are happy, that is a qualified lead but they couldn't get them on the phone. So if you kind of think about it you know, comes through as a new lead and then sales looks at it and says, yeah, that's an opportunity.
01:57 They're trying and contact them, and then they have a call and they either are not accepted or they're closed one.
02:03 So close to one is we actually kind of close the contract and there might be, you know, multiple stages. There might be more complex in this or less complex in your crm.
02:14 But this is just really an illustrative example. So what we want is close one, but we can't always get that right and we don't have enough data all the time.
02:22 So what we're gonna do is like pivot analysis just to see, you know, what, what are the different situations. Cool.
02:29 So so here we have the funnel and we have you know, how many people kind of get through each stage of the funnel, what the average value is.
02:40 Just in general, you can see that it really drops off around like opportunity in close one. So we, we don't have enough data really just to look at those, right?
02:51 Because, you know, if there's only seven close one out of 107 you know, it's gonna be really hard to do any analysis based on that, right?
03:02 Because you know, if we look at different categories, there might just be like one closed one in each category. So what we're gonna do is we're gonna treat this as like, this is some indication that there is quality there, right?
03:14 So like, if the sales market is an opportunity, then they thought there was quality there.  You know, if the sales even if they said not accepted, it was still worth getting on the phone with them, right?
03:26 So that's a good sign. And then close one obviously is the ultimate good sign. So what we're gonna do is kind of treat, you know, treat this as you know, the, the, this is quality lead, and then anything this new lead is, is not a quality lead.
03:39 And you can see here in terms of average value the just getting a lead is worth $300 to us, but getting a quality lead is worth three times as much $946.
03:51 So if we can kind of predict this, like what are the leads that are qualified, then we can optimize the cost per quality lead and then hopefully, you know, get more people through the funnel.
04:00 Cool. So we're not gonna cover like how to actually kind of lead score afterwards, but really it's just, just kind of understand what criteria are likely to make a lead.
04:09 Good. Cool. So what we've done is we've just done a pivot table. And, you know, the way you can do that is if you just click anywhere in here and press control A or command A, and then we go insert and then pivot table and then do it in a new sheet.
04:25 And then that's going to bring up something like this. So if you wanna edit the pivot table, just click in here.
04:32 So you can see here we put rows on the left hand side, that's the rows we put country and you know, each one of these pivot tables is a different segment.
04:42 So this is segment by the UTM content. So like, what asset did they come through on as a lead? Did they come from a webinar or, or an event or an ebook?
04:52 And then we also did like job roles here as well. So you know what the, what we're trying to do is segment the data by these different properties and then just see, you know, which ones are indicative of there being a good or bad fit.
05:09 So what we've done then is put the columns leads status, and then for values it would just counted how many they created that field.
05:16 So that's just a good way to get unique account. Cause they won't have the same created app. Each, each one is a come through a different second of the day.
05:27 Cool. So so once you have your pivot table, then the qualified column is just made up by selecting, you know, summing up any, anything that got into not accepted option to your close one, and then divided by the grand total.
05:43 So here we had 47% of the leads that came from the United Kingdom got into one of these buckets. Obviously this is cumulative in a way, right?
05:52 So you know, all of the not accepted were also leads at some point, right? So it's not right to say like eight divided by 12.
06:03 Actually you have to do like say eight divided by 12 plus eight, if that makes sense. So that's why we're divided by the grand total, because, you know, this is the current status of the leads, not their past status.
06:13 The past status would've been new lead for all of them, right? We had 23 of total leads and 12 of them were not qualified, if you think about it that way.
06:21 Cool. And one thing you can see straight away is like we get enough of, you know, ordered this descending by count of created leads.
06:32 So you can see straight away that u UK is our top country, right? It is drove 23 leads out of 107, so about 20%.
06:41 And then France is the next, and then we have Singapore. And a lot of the rest of the companies, countries are like relatively long tail.
06:49 We don't have that many leads. So so that's something to keep in mind because, you know, when we're looking at the score, like we can't just look at United States and say, yeah, we had like, you know, six leads from the United States altogether, two of them converted into close one.
07:06 That's a good thing, I guess. But like, that's not enough to like really, you know be confident. And you can work out the statistical significance of that.
07:15 But usually if this, you know, you can just kind of eyeball it to be honest. And, you know, you can make a rule of thumb and say like, you know, okay, well, you know, UK and France, we have like plenty of data here.
07:27 But then, you know, Singapore, we have nine leads, but they're actually just at the average qualified, right? So the average here is 34% and they're at 35%.
07:36 Same with Netherlands, they're at 33% and the average is 34%. So you can treat them as average, you don't need to worry too much about them.
07:44 So what you're looking for is like what categories, what, you know, attributes in the categories have a high number of leads and also a really different qualified score.
07:57 So you can see here France seems to perform terribly for us. Only 17% of those leads are qualified. So if we wanted to give it a score, what we can do is we divide the qualified by the average and then just multiply that by a hundred.
08:11 So this is basically an index. So it's saying, you know, that UK indexes 138 over a hundred, right? So it's 38% better than average, and France is half as good as average.
08:25 For the rest I'll just put a hundred because, you know, these two are average, so you don't need to really assign a score for them.
08:31 This one is like borderline, right? Like, but we only got, you know, we got nine leads and it's like pretty high which is pretty good.
08:41 So that's kind of like a judgment call. You know, you could, if you wanted to do this, you could put that here and then kind of give that an index.
08:49 The you know, the, but like, still kind of remains to be seen like, you know, we, we have twice as much data for the UK as we do Italy.
08:59 So we might have to kind of think about, you know, keep an eye on that and make sure that the score is correct.
09:05 Cool. So now, you know, we can break it down by other things as well. So let's say like, you know is there a difference in lead quality by the, the asset they come through in?
09:15 And this is something I've found most people forget about, but you know, different assets perform differently, right? So someone read an e-book, are they more or less likely to convert versus like a webinar or an event you know, et cetera.
09:29 So you can see the top two categories. We have a lot of leads for you know, the eBooks and you know, the score here is pretty low, right?
09:42 So they, they're actually less likely to convert.  There is something interesting here, the future, you know, the webinar, right? This this webinar and then this you know, this here, the e guide seems to perform pretty good.
09:55 1 0 1 guide of being a marketing scientist. So, you know, you might wanna kind of create scores for these. Yeah, and here we go.
10:02 So you can see like if it's a webinar or an e guide, you know, if it's this webinar specifically or if it's this e guide, then it seems to, you know, perform better than average and this one is average.
10:13 So just gonna set it at a hundred. Cool. And then final one here is job role. So you know, this is a, rather than the job title, which you know, there's tons and tons of job titles.
10:26 Let me kind of show you how many job titles we have.  Let me see. So if I get rid of job role and then I bring in the job title, there's a lot, right?
10:41 And you know, we do have, okay, I have 10 leads from social media interns, <laugh>, right? But, but like, that's the biggest category.
10:49 And, and then most of these don't, don't repeat. So what you could do is you could kind of group these and you could say like, I dunno, these are the VPs yeah, pivot group.
11:01 You go. So now you can kind of see yeah, this is like a terrible ui, but you can see the VP group you know, conversion rate.
11:13 So this is seven divided by 17. So like, that's one thing you could do. But in general, like job title is you know, it's usually like a free text field, so you end up with lots and lots of values.
11:26 So I'm just gonna get rid of that. And we're not gonna use that for for these at least right now.
11:34 But you could, you could make up like a grouping to automatically group them. What we did here instead, which I find is a little bit more useful, is when they fill in the lead form one thing we ask is like, you know, what, like, do you have responsibility over budget?
11:52 Right? Or so like, do you have budget oversight or are you a strategic decision maker, like a senior, senior leader?
11:59 Or are you in more in an operational role or a managerial role? Or are you none of the above? And obviously a lot of people don't fill this in, so I guess like 30% don't fill it in, but 60% are managerial roles and like you know, so that's kind of interesting.
12:15 And it, and it tells me here as well that like, not that many people are in operational roles, which is good because it doesn't look like we can convert anyone from operational roles.
12:25 And, and that is something that we can see, you know, see in just a small amount of data we have.
12:29 But it's also, you know, something that is like, just makes sense, right? Like if they don't have budget oversight, then they're probably not gonna be a good lead.
12:36 So you can come at this from, based on what the salespeople think and what they've found in, in terms of, you know, based on their logic as well.
12:44 And then and then also based on the data, so interesting, and this is something I find a lot like people who don't fill in that, that role, right?
12:54 Like, they're instantly worth less, right? They're worth half as much you know, than than if they do fill it in.
13:01 And if they say they're a man manager, then that's really, really good for us. Okay, cool. So, and we don't have enough detail on the rest.
13:09 Cool. Okay. So you know, he here actually as well, this is like converting at zero, right? So we might wanna say, you know, I might wanna just give this like a 50 or whatever.
13:21 So we don't have enough data, as much data as we do here, but like, let's just say that it's definitely like if they say none of the above, then you know, that's probably a bad sign as well, right?
13:31 So we can highlight that there. Cool. Let's practice taking another one. So what I'm gonna do is I'm just gonna copy this and then paste, and then we'll just go into edit and then we're gonna get rid of, so click out of the row, like get rid of that.
13:48 And then that just gives us a full funnel. And then we're gonna bring in let me see a number of employees, right?
13:55 Because this is usually like a, a pretty good indicator. So I just copied another thing here. So number of employees is really important you know, usually, right?
14:07 So let me just do descending by number of employees. And you can kind of see here that like most of the time, like most of our leads, 60 60% of our leads have at least 10,000 employees.
14:24 And it looks like that is a really good sign for us. It's 46% of them are qualified. And then even with like 5,000, it looks like 62% are qualified.
14:37 So this, you know, for this, I'm just gonna, I'm just gonna paste that in there.  So it looks like the score is pretty good if they have at least 5,000 employees.
14:47 We don't have any data for 35,000, but we could assume that the score is gonna be good there as well.
14:53 So, you know one thing you could do is just put this as like one 50, right? And then if they have a thousand employees, the quality goes way down, right?
15:05 So when I set that there, cool. And then, you know, for, for these guys you know, we could, we could say like, actually, like if they, you know, we could be restricted with this.
15:18 And so, like, especially if few sales people back you up on it, say like, look, they don't have at least a thousand employees, it's not even worth talking to you.
15:26 And then some people don't fill it in. It's actually a fewer people that don't fill it in. So I think we're, you know, we can kind of ignore that category.
15:34 Cool. So now what you would do with this analysis is you would take it and you kind of agree the scores based on sales and maybe you would update those scores every quarter based on historical data.
15:46 Like obviously the more historical data you have, the better you know, and then you would use this in your lead scoring.
15:52 So one simple way you could do it is you could just do a points based system and you can say like, if they have more than 5,000 employees, that's one point, you know, if they're a manager, that's another point.
16:05 And then if they're, if they come through ebook that we're deducting a point <laugh>, right? And then if they come from UK or adding a point, if they come from France for a deducting a point so that would be the simplest implementation and that would get you like 80% of the way there.
16:21 I know it's not very scientific, but you know, practical kind of application of this to like kind of steer your lead qualification in the right direction is an important thing.
16:33 Because there's a few reasons, right? One is right now, you know, the salespeople are having to go through every single lead.
16:40 So if you give them some sort of scoring, you go like uk good France, bad, like that's enough. At least like they're gonna go for the UK leads first, right?
16:48 And they're gonna leave the French leads to the end, and that is gonna help them prioritize. It's also gonna help you optimize, right?
16:55 So you're gonna look at your performance and you go like, huh, I'm gonna just really jack up the spend on my UK campaign, right?
17:02 I'm gonna decrease the spend on my friends campaign. You know, same thing here. Like, if eBooks aren't working, like why are we spending, you know, five grand on content writers to create the next ebook, right?
17:13 Like, let's produce more webinars and this is giving us like a little hint, like a hypothesis that this type of webinar seems to be working right?
17:21 And then like, maybe we'd do another e guide, cuz maybe they prefer that to eBooks, I'm not sure. Cool. So this is gonna give you kind of strategic analysis and it could also give you like an automated lead score in the future as well.
17:35 So like you know, you could do this statistically as well, but like you could also just do it like we've done it here in the spreadsheet and kind of you know, update these values over time.
17:46 You could turn this into a formula, so like you could do v lookups And say like, if the leads from the UK give it the score of 1 38, and then you can kind of like multiply the different scores together to get the final score.
17:56 So that's something you could do. But you know, we won't cover this cover that in this video. Just wanted to kind of show you how you kind of dig into these different segments and, and understand like what types of customers are really good for you.
18:11 Okay? Hopefully that was helpful.

Why is it important to do a pipeline analysis?

What countries drove the highest quality leads?

What percentage of leads aren’t qualified?

Why is it important to have enough leads in a category in order to assign it a score?

Looking historically at your lead data and segmenting by attributes gives you a good idea of what types of leads are more or less likely to close. This can feed into a storing system to automatically qualify leads, or just be used to align marketing better to sales.

How do we measure lead quality?

Vexnomics Office – Optimization Sprint Meeting

You’re talking about your great results and how you brought down the cost per lead, but your boss walks in and asks how many are qualified. You have no idea: sales haven't been through all the leads yet, and probably won’t get to it for weeks.

Waiting for sales to update the CRM is a path to madness
We need our own qualification system
Let me talk to sales and get an understanding of what historically converted
Then we’ll use those attributes to build a lead scoring system
That way we can optimize in near real-time


Lead scoring is a way for sales and marketing teams to prioritize their efforts and focus on the leads that are most likely to turn into paying customers. This is done by assigning each lead a score based on a set of criteria that are relevant to the organization's goals and target audience. For example, a lead's score might be determined by their demographic information, behavior data, and firmographic data.

Demographic information includes details about a lead's age, gender, and location, while behavior data includes information about how the lead has interacted with the organization in the past, such as which pages they have visited on the organization's website and how often they have opened and clicked on marketing emails. Firmographic data, on the other hand, includes information about the lead's company, such as its size and industry.

The goal of lead scoring is to help sales and marketing teams be more efficient and effective in their efforts. By prioritizing the leads that are most likely to convert, organizations can make the most of their time and resources. Lead scoring can also provide sales teams with a clear understanding of which leads are most valuable, allowing them to make more strategic decisions about how to pursue new business opportunities.

Overall, lead scoring is a valuable tool that can help organizations of all sizes and industries improve their sales and marketing efforts and drive more revenue. By prioritizing their leads, sales and marketing teams can focus on the ones that are most likely to become paying customers, ultimately helping the organization achieve its goals.

Lead Scoring

00:01 Hey, I'm just gonna walk you through how to qualify your leads more programmatically. And we're gonna do this in a Google sheet.
00:10 So you know, you can kind of understand how it works but once you see the principles, you'll be able to apply it really you know, wherever you need to set it up.
00:19 Cool. So this is a typical, you know, spreadsheet you might get from sales output, from the crm based on a lead generation campaign.
00:30 So if you look you know, we've been producing these eBooks and e guides and webinars and different events, and they're driving leads.
00:39 And some of those leads are qualified and some aren't. So what we have is a few different leads. Status. One is a new lead, which means it hasn't been seen yet.
00:51 It hasn't been looked at by sales not accepted, means it's been looked at by sales, and it's not a good fit.
00:57 So this is usually an override, meaning it doesn't matter how qualified the lead looks. We can't, you know we can't count it as a qualified lead if sales have said no.
01:09 Although sometimes if you see that they're being mis qualified, you can you know, go back over it. And then there's some situations where sales have already looked at lead.
01:20 Maybe they've already had a call and it says there's an opportunity. So opportunity is when sales accepts the lead, and they're, you know, following up with the proposal.
01:32 They've had a, had a call. Cool. So we basically what we want to do is, like, we know that not all of these leads are qualified and we need to figure out you know, what a cost per qualified lead was.
01:46 In order to do that, we need to qualify these leads. And you don't want to be doing what the sales team is doing, right?
01:51 And going through each one of these manually, like looking up the company, looking up the job title, et cetera. So you just need some more programmatic way of figuring it out.
02:00 Cool. So the way that I find is useful is you know, first there's gonna be, you know, specific countries which are more likely to create good quality leads than others.
02:17 So I'm just gonna say country. And then gonna do data, data cleanup and then gonna remove duplicates. So I just copied and pasted the countries, and then now I have a list of all the countries.
02:31 And then I'm just gonna put a score here. So for United States, America, I'm gonna put 10, you know, one of richest countries in the world.
02:43 So, you know, like much more likely to be high quality. And then we have, you know, Sweden, Luxembourg also, you know, pretty rich.
02:52 Let's say just let's make Europe eight Western Europe eight middleland keep in France. Philippines gonna say three. It's a lower income country, Malaysia.
03:08 And, you know, you, you could actually actually, so Singapore is, you know, much more like Norway, right? In terms of demographics.
03:16 So you know, you're gonna be able to derive these numbers properly by looking at your past data and seeing, you know, leads from Belgium are pretty good leads from, you know, other countries are not as good.
03:31 You know, but the but the main point is really that like some countries will provide more qualified leads than others, and you kind of wanna weight the score, but you don't want to like, completely dismiss a lead from the Philippines.
03:45 Cause say for example, they're you know, actually like a senior VP or something, right? Like maybe they're actually qualified. Cool.
03:53 So country is just one score. And then we also have number of employees. And that one's a little bit easier, I guess.
04:07 Cause you know, this is b2b, you know, company. And if they have like a good number of employees that's gonna be that's gonna be really influential, right?
04:22 So I'm gonna put this score there. So this, you know, if like, if it's a really big company, like 10,000 people plus then I'm gonna give that look at score 20.
04:32 Cause I think that's really important, much more important than the country. And then if it's like 5,000, let's give 10, and then this is a five.
04:43 And then if it's missing just don't give any score.  And then actually, let's make these no score as well. Unknown, actually, yeah, let's just make these five because I would just assume that they're average.
05:06 If it's a one employee or 10 employees, we know that they're not gonna attend to customers. They're a lot less likely to tend to customers.
05:15 Cool. So again, these scores, I'm just making them up, but you would use historical data, you would say, say like, you know maybe, you know, companies that have 10,000 employees are twice as likely to become valuable customers than you know, companies that have 5,000 employees.
05:34 And then we're gonna do it based on one more thing, which is job role. So let's grab all the job roles, okay?
05:46 Right. And then should put this, that here and again, when I'm doing this, I'm just, you know, control shift down or command shift down to grab the whole column.
05:56 And then control shift v adds paste without formatting. Cool. So I'm just gonna remove duplicates here. Okay. So it looks like we have a few different job roles and a blank job role.
06:20 Just gonna put the scores. Sorry, let's give this a five again. Then. Managerial role is going to be a 10 about five for none of the above.
06:31 Operational role is a one because that's not good for us. And you know, they need to be in a managerial role to be the right type of a client for us.
06:41 Budget oversight, let's give them let's give them a seven, right? Cause they have some influence. And then strategic decision maker.
06:52 Let's give that a 20. So they're the, the big hot show, right? Cool. So you know, you could keep, you could keep going with this.
06:59 Like you could do it based on job title or, you know, whatever it is. But I think this is gonna be enough to demonstrate for us.
07:07 Cool. So now we just need to put together a lead score. And then what we're gonna do is the look up we're gonna look up the country first, and then that first F four to grab that group.
07:28 So then it doesn't change as we move down. I'm gonna return the second thing here and then and then oppose zero at the end to make not a fuzzy match.
07:39 Cool. So let's say, okay, let's actually make a different thing for each of, so this is country score. This just makes it cleaner.
07:48 And then we're gonna do the same thing here. But this is gonna be please score. And here, instead of looking at this, gonna look at this table, right?
08:08 And remember to press F four, or you can put the dollar signs in yourself. And here we're looking at the number of employees, which is this field here.
08:18 And the rest is fine. Cool. And then down here we've got some nas so did not find value blank. Oh, yeah, yeah.
08:37 So I found this as an issue. Sometimes you just wrap it in an if error. That's, if you don't find the lookup, then you know, then it's just gonna return blank.
08:51 In this case, we gave a a value of five if there's an error. So let's try that. Yeah, there we go.
08:59 It's filled it in with a five. Cool. Here we go. All right, employee score. And then one more, which is the job roll score, roll score.
09:22 Cool. So if you look up again, and then the job roll, I'll look it up over here at four. It's The second column we want, and we don't wanna fuzzy match.
09:36 Okay, here we go. So that looks like it's good. All right, cool. So now we can do the total lead score, right?
09:48 And that is that plus that, that, okay. And let's just c just gonna press control A and then what's the best way to look at this?
10:11 Let's do, actually, let me just do some sort of chart. So not a pie chart columns. Cool. So this isn't like, particularly easy to see.
10:44 Yeah, let's do that. Then we can sort this. So let's create a filter and then sort by lead score. Cool.
10:56 All right, here we go. So this is more useful visualization. All right, cool. So it looks like, you know, most of our leads, you know, are around this 38 lead score, which is pretty good, right?
11:09 So that's these guys, right? Like You know, they come through here like a VP of growth in there, VP of finance, so that's, you know, interesting.
11:20 Cool. So we have a lot of 30 eights, and then we have like, kind of lower amounts down here, and we have a, you know, a bunch kind of in the lower bracket.
11:29 So it really depends. And again, you know, you kind of have to set this as a negotiation with sales. But let's say that the cutoff is 20, right?
11:40 So if anything below a 20 is gonna be not qualified. So let's make another column and yeah, this is going to, you know, get rid of the Worst offenders, right?
11:58 But then you know, but then it's gonna like, you know, it is gonna make sure that we like at least looking at some of the leads that, that have potential, right?
12:08 So if that is you know, more than 20, then it's an mql. Cool. So there we go. Now we have false and true.
12:18 Cool. So so this is how I do it. The benefit of doing it programmatically this way is that a, it saves you a lot of time because now you have this spreadsheet, you could literally just paste in more leads and then it'll update.
12:32 You could also just write some code. You know, you could give this to a developer to automate, so it could actually push straight into HubSpot or whatever CRM you're using.
12:41 Just as soon as they fill in the form. That's great. That's like a ideal situation. Then you don't even have to do this spreadsheet.
12:48 The other reason is that you can't wait for sales to say what leads are qualified. You have to optimize. And, you know, we've seen here that, you know, of our leads, like of all of our leads, we've got 77 that are qualified, and And 107 leads total.
13:12 So, you know, our MQL rate is 27 times seven, so 71% cost per qualified. Yeah, 71% of our leads are qualified.
13:25 So it's pretty high. But like, you know, that's not always gonna be the case. You know, and you know, when you're optimizing your campaigns, you can kind of keep an eye on this figure and say, are we getting high quality leads?
13:37 You know, are there some you know are there some types of you know, campaign that are getting low quality leads?
13:46 So if you do a pivot table, you can see that. So just gonna put here, like the campaign. And then I'm just gonna bring in values like nql, and then just gonna do a count of like created app.
14:06 Cool. So count of nql, and this one is just gonna be some, Yeah You know what? Here we go. Let's make this If It's true, then it's gonna be one other is gonna be a zero.
14:36 So now that's gonna be a number, then that's a lot easier. Okay, so here we go. So now we have the mql.
14:44 So, you know, we could, we could make a, you know, custom formula here in the pivot table or, you know, just for speed and bring here like this.
14:53 So we can see that, you know, our qualification rate. We're just gonna do additional forming. So you can see this you know, our qualification rate here is you know here we go.
15:05 It's it's, it's much higher for some channels than others, like for some campaigns than others. So we'd wanna look into that.
15:13 You know, for some of the campaigns who are targeting different countries might have higher rates than others. So this is really, really key to doing any type of analysis.
15:21 And doing it programmatically means that like, there's no arguments. It doesn't really matter what sales says. Like if you've agreed on the criteria then you can, you know, what to optimize to.
15:31 You don't have to wait for them to come back. You know, where you do see conflicts like you know, maybe if like, we can filter down here.
15:41 So if I go data and then I you know, create the filter, let me filter to anyone that I think is qualified but they think is not qualified right?
15:54 So now you can dig into these and say, Hey why did you disqualify this guy? Like, he's a VP of marketing and this one's a VP of finance, and they're saying lead fit, right?
16:02 I need more information, right? And you can maybe win some of these back and say, Hey you know do we get the qualification criteria wrong?
16:10 Or you know, did we, were we too hasty to reject this one? Or maybe there's an additional criteria that we can add, you know, something else in the form that we can, we can ask.
16:19 Cool. Okay. So hopefully you understand that. And you know, you get the concept it's you know, incredibly useful. It really just depends on how complex or how easy you want to go with the filtering requirements.
16:33 But I would start simple and just kind of build on from there.


Why is it important for marketing to own lead scoring?

How many qualified leads were rejected by Sales?

What percentage of the leads would have been qualified if the required minimum lead quality score was 30 not 20?

How do you determine the criteria for lead scoring and know the value of each attribute?

Working out the cost per qualified lead oftentimes requires a long-winded manual process, involving waiting for sales to follow up in the CRM. In practice it’s better to do this programmatically based on specific attributes you both agree are indicators of lead quality. That way you can optimize in near-real-time.

What's our cost per qualified lead?

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. 

Here's the data I promised from the PPC account. 
You should take this and calculate the main KPIs like CTR and Conversion rate.
Let me know if you get stuck!

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.

Calculating KPIs

0:00 Okay, let's figure out how to do the calculations for KPIs. These are the standard KPIs that you'll want to calculate for any advertising campaign.
0:10 So over here we have the raw data this is the spend, impressions, clicks, etc. And we want to be able to calculate the final amounts right here.
0:25 So this is the cost per thousand impressions, the click-through rate, cost per click and conversion rate. Alright, so I'm doing this with the pivot table.
0:34 The reason why is because you could calculate it here or just kind of, you know, average these conversion rates, but it's going to turn out to be wrong.
0:45 If you you know, select all of these then you can see the sum is obviously wrong 110%. The average is 0.82%.
0:56 But if you look in the pivot table that's wrong. It's it's actually one point. One, three percent. It's because you can't average an average.
1:04 The, the conversion rate is already an average conversion rate for this line item. And therefore you actually kind of have to sum up all.
1:16 The clicks sum up all of the leads and then get the total conversion rate for that for that section. And that's what a pivot table is doing for you.
1:26 Okay. So let me just show you this works. I'm going to press command A. Control a. I'm going to go to a concert and then pivot table in a new sheet.
1:38 And then on, on this side, we want to kind of add the keyword which is what we're breaking it down by.
1:45 And then into the value. So we're going to add all the different things that we need. So spend impressions, clicks, and leads, conversions.
1:59 So this is just the raw data, right? Like we're not. Including any of the calculated metrics because we're going to calculate them ourselves.
2:06 And one way we can do this is if we wanted to, you know, calculate the CPM, up for a thousand impressions, we could say, spend you know, divided by.
2:19 The impressions divided by 1000 in brackets, and we get the CPM. But the problem is if we went and then change this, so say we wanted to break this down by device.
2:34 Yeah, then we're going to run into problems because now I have this like divided by zero error because there's nothing here.
2:41 And now there's you know, like it's not kind of calculating it properly. So the way that you want to do this is have this built into part of the pivot table itself.
2:53 And in order to do that, you need to add it as a calculated field. So click on add and then calculated field.
2:59 And then the way it works here is you can just put spend divided by corrections and that is divided by 1000.
3:13 And there we go. We just need to change the name here. So we're going to collect impressions and now oh sorry, CPM, that was for 1000 impressions.
3:24 And now whenever we change this, so say if we get rid of that and we put you know, by ad group in here or we could change it to campaign.
3:35 Now the CPM is always with us. So that's really helpful. The other calculations here are the CPM is the CTR.
3:49 So the CTR is. The clicks divided by fractions. Then we have the cost per click. So that's the cost divided by.
4:09 Enter. Oh, so here we go. I've put the wrong thing in here. I put some costs. So there we go.
4:17 Now it's, now it's fixed. What are the other ones? Yeah. CVR. That's the other one here. Then CVR. Click add calculated field.
4:29 And then this is the convergent. Oh, sorry. Leads. Dotted by declared. Cool. One thing you can do here is format it like this.
4:47 So we got a percentage but. The one thing I would say just to kind of let you know, is if this was Culturally different.
4:59 So say, you know, if it wasn't leads, it was leads. And then you wouldn't be able to use the method that we looked about here, right?
5:14 We'd, we wouldn't just type leads. We have to put it into quotations like this. So here we go. It's automatically changed it for us to leads dash website.
5:24 And then it will change back, which is kind of nice because it's referencing that. So I'm just going to change that back So that's how you create calculated field.
5:34 You know, you can label them now as well. CPC and that's and now that you have these calculated fields, you can basically kind of drag in and out the different dimensions.
5:47 And see, you know, how does that change the cost per click, the conversion rate, etc. I am hoping that was helpful.

What is the purpose of using a pivot table to calculate KPIs?

How do you add a calculated field to a pivot table?

How can you tell if a pivot table is accurately calculating the KPIs?

What is the formula for calculating the cost per thousand impressions (CPM)?

What is the difference between calculating a KPI in the raw data versus a pivot table?

The number one reason I would fail someone in an interview when running my agency, was simple. They couldn’t do basic calculations in GSheets / Excel for the main KPIs.

Calculating all the KPIs

You've finished calculating the main KPIs, but as for calculating how much we should be paying per click, you have no idea. 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

It’s important to make sure you get the most out of your Google Ads campaigns. One way to do this is to optimize your bids. Bid optimization is the process of setting the maximum amount you are willing to pay for each click on an ad. This can be tricky, as you want to pay as little as possible while still getting the most out of your ads. To do this, you’ll need to look at the data and calculate the return on ads spend (ROAS). 
ROAS is calculated in a pivot table. To edit the pivot table, click edit and you can see the keyword and different columns. You’ll also have calculated fields such as CPM, CTR, etc. which will give you a good idea of why the ROAS is good or bad. For example, if you have a keyword that has a good cost per click but a poor conversion rate, the ROAS will be low. 
Once you have diagnosed the data, you can work out the suggested bid. This can be done by multiplying the lead worth (e.g. $45) by the leads to find the revenue generated. Then divide the revenue by clicks to get the revenue per click. Then calculate the cost per click and divide by clicks minus one. Then divide the two together. This will give you the amount higher the revenue per click is than the cost per click. 
Alternatively, you can add a calculated field in the pivot table to compare the revenue per click to the cost per click. This will help you decide on a suggested bid. For example, if the CPC is 55 cents, that could be the same as the suggested bid. If the CPC is 70 cents, you could drastically cut the bid.
To make sure you don’t make drastic changes, you can also add a dampener such as a 20% cap. This means you will never increase the bid by more than 20%. 
Bid optimization is a great way to get the most out of your Google Ads campaigns. By looking at the data and calculating the ROAS, you can determine the suggested bid. However, it’s important to keep in mind that this is not always foolproof and you may want to let Google set the bids. You can also add a dampener such as a 20% cap to make sure you don’t make drastic changes.

Bid Optimization

0:00 Okay, you want to figure out how much to pay per click for your Google ads campaigns. This is the raw data and we have the keywords ad groups and then all of the spend clicks click through etc.
0:15 But the main thing we're interested in is the ROAS, the return on ads spend. And this is calculated in a pivot table.
0:22 So if you want to edit the pivot table, click edit, and then you can see here, we have the keyword and then the different columns.
0:28 Now we have these calculated fields. So you can see the CPM, the CTR, et cetera. I'll give you a good idea of why the ROAS is good or bad.
0:37 For example, for product recommendations, keyword, the cost per click is pretty good, but the conversion rate is pretty poor. And that's why the ROAS is low, right?
0:50 So you can diagnose those and kind of dig into that data as you need. But the the main thing we care about is that a lead is worth $45 to us.
1:01 So the return on ads. And then to work out the suggested bid. What I like to do, and this isn't a foolproof way, you know, there's a more advanced way to do it.
1:24 And typically, honestly, you would, you know, let Google set the bids, etc. But but if you wanted to set it manually, this is the way I, I tend to do it.
1:34 So first of all you get the $45 and multiply it by the leads. So that gives that gives you how much kind of revenue that keyword generated.
1:46 And then you divide the revenue by clicks, so give you revenue per click. And then and then you get the, you calculate the cost per clicks and divided by clicks minus one.
1:56 And you divide those two together. So what you get is essentially how much higher is the revenue per click than the cost per click.
2:05 And you could calculate that as a separate field as well. So if you wanted to add a calculated field in here, that was you know in this case $45 speeds divided by clicks.
2:24 Then that would give you the revenue per click and you could compare them. So. The the CPC here is 55 cents.
2:36 I'd say that's the same as the suggested bid. So if this is basically wanting to get it to. Break even with the CPC, that's what you do.
2:45 Cool. So in this case, you would, you know, drastically cut the bids of you know, this keyword, for example, the CPC is 70.
2:56 We're suggesting it should be 20. Whereas with others, you'd push them up. And you might want to add some sort of dampener on here.
3:04 So if this is this is like the raw amount you could also kind of cap it at like, say 20%.
3:13 Maybe you know, the way you would do that is just say max oh, sorry, min Okay, so. This there you go.
3:28 So that means that you would never increase the bid by more than 20%. In this case, it's just camping at 20%.
3:35 So that's one way you could do it, but that, that way you'd know. And you know, you're not kind of making too drastic over change.
3:41 Anyway this is you know, kind of a simple way of doing it. There's, you know, more calculated ways you could find the derivatives, whatever.
3:48 But this should be good enough for your purposes. Alright, hopefully that was useful.

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?

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

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?

Vexpower Learning Certificate of Completion

Skill Path: Media Buyer