Free Media Efficiency Ratio (MER) Tracker Sheet

[00:00:00] Another benefit of using this tracking sheet is that you can avoid the need for Triple Whale and other third-party attribution platforms. But I want to talk about the evolution of 'my tracker' that I've been dealing with for some clients recently. Last week a client contacted me,

[00:00:30] their finance department asked for better profitability figures and they needed them for a 12- month period. So this is where it all started. I want to explain that, generally speaking, this ' My Tracker' is no longer just a Merge Tracker. I am now using some other statistics for this as well

[00:00:56] . If anyone wants to see this and use it for their own clients, there is a version available on Drive. It's basically plug-and- play. It will take a little time to set it up initially. This is like a use-case version that we are using for a specific client. So, you have to manually

[00:01:21] update your spending, traffic, customer revenue, all of these. So, this takes some time to do, but once everything is set up, when you do this every week, I find it easier to update last week's data on Monday. This helps me better understand how my ad performed last week, rather than just looking at the ad account and saying "everything is good or bad." In fact, this client is a good example of that. If we look at last

[00:01:53] week's data, anyway, this is what I saw earlier this week. At least in Meta, we spent about the same amount every week. The situation is almost the same at Google. Traffic actually increased week by week. Income has increased. New customer revenue decreased, but the conversion

[00:02:15] rate increased. So, rather than just going into an ad account and saying, "Okay, this campaign performed well last week, " it gives you more to talk about when discussing this information with clients. Two orders have arrived here. We received an order. We can discuss whether to do this or that.

[00:02:39] I think clients might not care as much when you're just talking about ad account statistics. But I think this is information they can really use on a business level. So you could say, not only is our traffic growing, but what we've done on Meta is working well for us, because Meta is the only ad platform for this client, so it's very easy.

[00:02:59] So we can say for sure, the work we did at Meta increased our traffic week after week, plus our conversion rate also increased, which doesn't usually happen. Typically, if traffic increases, the conversion rate stays the same or decreases. Yes, this way you can get a little more information to share with the client and say, our new customers have increased, but new customer revenue has decreased.

[00:03:28] So our new customer 's AOB is decreasing. And it has led to more discussions ever since. So you might ask, " What do we think is happening to these new customers when the new customer AOB drops by $40?" You can link it to a first time customer offer that we have on their website that week or something like that.

[00:03:46] Sure. Or it could be a landing page that we are sending top of the funnel traffic to. You could say, " Okay, maybe last week we started sending to PDP instead of collections page, and that's why new customer AOB dropped significantly. We should probably do it again like we did the week before." Yeah.

[00:04:11] So, this is something I'm doing more often now and it's leading to bigger business discussions instead of just talking about meta for 15 minutes and ending the call. Yeah, another benefit of this is, if you look, last week was a lot better according to our global metrics. We grew revenue week-over-week by

[00:04:37] over 10k. But if you look at meta alone, last week wasn't as good. From '13 to '19, this is the same period here, we had a 1.9 RoAS on the platform, compared to a 3.18 spend the week before from week 6 to '12. So, if you look at RoAS alone on the platform, it's like, "What you did is n't working and you need to change it," but our topline metrics are saying it's actually working better.

[00:05:14] That particular week. Okay. You went from the 13th to the 19th, yes, you were down from the previous week. We actually increased revenue but from the 6th to the 12th it was 50,000, but new customer revenue was up. Generally down but up in terms of new customers. Yeah. Are you comparing

[00:05:37] week 11 to week 12? I'm comparing 11 to 10. Yeah. Yeah. Comparing these two weeks. Okay. Yeah. So, week 10 here looks a lot worse. Yeah. Than before. But at least on the platform, week 10 was pretty good. So, it's like if you stick to what we did this week it could go in a different direction because it didn't actually benefit the business.

[00:06:12] So, I'm getting to the point where I'm calculating profitability a little bit more accurately than before. And that's by using the customer or client's COGS. I don't know if we ask that in our tally, but if not, we should ask and I should have known that. But that's the cost of goods sold per average

[00:06:37] order value. Is that it? Oh, there it is. Okay. Yeah. So, the cost of goods sold per AOV that they give us. On average, yeah, they give you an average number there. Yeah. So, they give us. Previously we asked about COGS per AOV and any shipping costs and other costs. And for this client, at $90 AOV

[00:07:05] , they gave us a total cost of $62. I'm looking at 145. Where is the 90 AOV? It's not in there, but that's what they gave us previously. Okay. So, what I do from that is, I take 90 and calculate that COGS percentage. So if $90 AOV has $62 COGS, that's about 32.5%. So if we take

[00:07:39] our average order value for any given week and multiply it by 32%, that's our COGS for that week. What's the basis of that 190? What's the basis of that? That's the AOV that they gave us, 62. Okay. Not 190, Okay. That's one. I thought you said 90. I thought I heard 90. 190. Sorry.

[00:08:04] I probably should have said 90. Yeah, in the data they gave us, this 62 dollars, their AOV is 190 dollars. So 62 COGS over 190. Yeah. So what I'm getting at is that their actual profit percentage is going to be against that. So it's going to be about 60 to 70 or 65 to 70%. Yeah,

[00:08:28] sure. So what can we do from that? After the percentage? Yeah. 32.5 takes that 32.5 and we multiply it by the AOV for any given week, and from that we can get their COGS. Okay, that's COGS, not profit percentage. Yeah, sorry. Yeah, that's their COGS percentage. Okay. Yeah. So we

[00:08:52] don't just want to limit ourselves to just 62 dollars, because that's not a fixed number. AOV is always changing. So just this last part, that last part again? AOV is not a fixed number. It's always changing. So if we were to, say, stick to a COGS of $62, and their AOV drops to $144, we'd be doing a huge miscalculation. Their

[00:09:15] profit margin is completely wrong. Yes, their profit margin goes up a little bit when the AOV goes up. Yes. So, actually all those weeks that you're tracking, they're all much higher than 90. And no, sorry. That's 190. 190. Yes. So, we're actually way lower. These are much lower than 190.

[00:09:36] So, their original COGS. Maybe we should take the new COGS number of 178, which is a little bit more accurate, I think. But yeah, but almost. Yes. But if we want to give them a specific week, that's an approximate way to get that number. You just weeks. The average spend this week is

[00:10:01] $47. Okay. Also, we can take that number and add it to it, and just to be clear, this is our new customer AOV, not the total AOV on Shopify, but only the new customer revenue. Then we also take our new customer acquisition cost So, if you take the two together, that's the total cost of acquiring a new customer.

[00:10:30] Excluding all the business costs. If you give this to the client and tell them to add their business costs if they have that information, you can get more granular. But in our case, I don't think we can be more specific than that. So I don't think most customers or clients give us that information.

[00:10:50] Then yeah, how are you generating new customer orders? Is that coming from Triple Whale? Shopify. Shopify. Okay. You're just using okay. And you're okay. How are we separating out their AOV? Is that also available in Shopify? That's a little calculation, but it's very easy. So, if you divide the

[00:11:13] new customer revenue by the new customer orders, you get the new customer AOV. Okay. We can analyze who the new customer is, what they bought. Yeah. Yeah. I can show you that very quickly. Yeah. And another benefit of using this tracking sheet is that you can use Triple Whale and other Third-party attribution platforms can be leveraged, especially for customer acquisition, which is generally very important. If

[00:11:45] you're using it, you can actually tell them that they don't need to pay that four or five hundred for Triple Whale. Absolutely. Yeah. Yeah. And for smaller clients that's a big amount. Oh yeah. Yeah. So I honestly think that if you can do that, it makes us more loyal. For example, "Hey,

[00:12:05] I don't think you need to pay that extra money." Why not use it for advertising or other things that benefit them? "You could say. Okay. So this would be in cohort analysis. Do you get revenue? That's not ah. You could just click on Sales by Channel and then you could break it down for first-time buyers.

[00:12:26] Click here. Ah, I think it's customers, customer type. Oh yeah. Yeah. First time. Yeah. And then you can get total customers or total new customers and total new customer revenue. Perfect. Yeah. So I think you're giving the client a lot of good information from that, that makes sense.

[00:12:55] Ah, and all you have to do to get profitability is calculate your new customer acquisition profitability, which is subtract that amount that we found earlier from your new customer AOV. So in this lower left section, you're doing a new calculation every week against the previous week.

[00:13:14] Yeah. You do n't really have to do it every week. Just because the client needs this information, it's good to have it available. And then if we want to look at it in a more comprehensive time frame, we can also look at LTV. This is triple Another benefit of a third-party attribution platform like Whale, but we do n't necessarily need them.

[00:13:38] I've been using this app recently called DataDrew, which is something that the client can install on their website. I think it's about $40 a month, but they have a 7- day free trial. So, the company probably won't like me saying this, but you can download it, have the client sign in, and be done with it. Take seven days to get all your

[00:13:59] information, then delete it, the client does n't have to pay anything, you get additional data about LTV and other things that I'm going to show you here. Is it roughly equivalent to a more expensive tool like Triple Whale? Yes, because it uses their Shopify data directly. So, I think that's

[00:14:22] absolutely correct, right? So, you don't even need to delete it. So, for $40, you get almost that much information. If you're doing a lot of transactions, it's better to keep it. Yeah, that's right. For $40, you get If you want, it's not that big of a deal. Yeah. And you can choose your

[00:14:39] time frame. I took two years with this client, but you can extend it to the entire business timeframe and get the highest level metrics. So you can look at AOV, new customers, first month, 3, 6, 12, 24 months, and LTV up to three years. For this business, they wanted to see the actual revenue coming in over 12 months instead of 24 months. So, we

[00:15:10] limited it to just one year. That's where this 229 number came from. But while I'm here, there's also some other interesting information that we can get from this app. One, basket analysis is really cool. That's basically, looking at what products are frequently purchased together. This

[00:15:33] client data might look a little off because they're using order protection. So order protection is tagged with each product. But if you look at it, you could say that the lifeguard brief and the body brief are frequently purchased together. So we could theoretically put that into their You could

[00:15:52] start promoting it as a bundle on the website, for example. Another thing is the repurchase rate, which I think is really good, you have to dig a little deeper to find this information. But basically what I found is that their Lifeguard brief is one of the products that new customers buy more often, and it also increases the rate at which other products are repurchased.

[00:16:21] If they buy it, 27% of those customers will buy another product in the future. They say that that product is one of the main things that attracts them. Yeah, that's right. And it's an entry- level product. So you can see, our AOV for the first time purchase is relatively low, which is 166.

[00:16:43] But the LTV on it is 447. Okay. You're not telling me that a pair of underwear costs 166, are you? I think they're probably buying more, but it's not a big difference. What I mean is that for an underwear company, it's a very high AOV, it's probably higher, but I see the word 'brief' a lot, so probably not. In

[00:17:03] my opinion, Honestly, they're probably $75-80 a pair. Yeah, it's a little strange. However, considering their product line, it's a very low-priced product. So, if someone is going to come in and buy this product, it's a very good entry- level price and it leads to a very high LTV. So, it's

[00:17:43] great to know that when it comes to attracting new customers, it's a good way to expand this product on social media and on Google through different images and campaigns. Yeah. Another good thing about this app is the different segments it offers. That is, they break down the entire

[00:18:01] customer list of the client into different segments. They have a champion list, loyal, promising, and so on. The way I use these is, champions and loyal customers are their two best audience segments. So I take these two and create 'look-alikes' from them. I take their list,

[00:18:26] which is the people who spend the most and buy frequently. So these are the best customers they can ask for. And their loyal customers often spend good amounts. So these are the top two top customers Segments. Total revenue and total purchases combined. And this is pulled from Shopify

[00:18:48] . And you create look-alikes from them. Yeah. And you can export this data directly from here. Okay. Yeah. That's great. And then, does it have an email tool or is it just for audio? Yeah. Yeah. Wow. Sorry, no, it does n't have that. You can connect it and send it to just one of those segments if you want.

[00:19:17] Yeah, that's right. It's like the second part of this. So ' Promising' is the people who are in the process of becoming champion buyers, ' Needs Attention' is the people who need attention, and ' Shouldn't Lose' is the people who have bought a lot in the past but have recently lost.

[00:19:37] Those are great for your email marketing segments. That's why it's so useful to connect to Clavio, let's re-engage all these ' Promising' people. Let's turn them into champion buyers. ' Needs Attention'. Let's follow them up and Let's build trust in ourselves again, and also in the 'shouldn't lose' ones.

[00:19:57] Maybe they haven't seen any good offers lately. We have to explain those things to them. But once you connect to Clavio, you can go directly into Clavio. You can create an email for them and choose the audience that you want. That's easy. Yeah. Exactly. For $40 it's a really good tool.

[00:20:14] Yeah. Or if you don't have Clavio, you can export it and use it with Mailchimp or other tools like that. Export it and upload it to whatever platform you're using. Yeah. Yeah. So, it's really good. For $40 a month, as you said, it has a lot of good data. If the client doesn't want to pay for it,

[00:20:35] and in this case, they don't find it that useful except for the information that I've provided. So, they delete it and don't have to pay. Basically, I record a Loom video explaining the things that I just discussed. And that information The video is saved in the recording. So, we can

[00:20:56] watch it again whenever we want. And the client gets the full details. And the same goes for calculating our 12-month profitability. So, you can take that 292 that we calculated from our LTV. You can go into more detail here and look at your new customer segments and figure out what your new customer LTV is. But for this purpose,

[00:21:24] I'm just using their total LTV. Then subtract the cost of acquiring a new customer. Yes, this client asked for some more profit figures to send to their finance department, and they really liked it, so I'm sharing it with you. It takes four or five minutes to update this sheet every week, and you have a lot of data at your fingertips.

[00:21:58] Yes. And I talked about this before. This is an example of using multi-channels, we highlight that this is a very bad week. That's why we're highlighting it in red and trying to analyze what happened that week or the week before that. And I'm also including the changes that we made in that particular week.

[00:22:32] This One week we added $25 a day to the broad campaign that had top 10 revenue, the next week we added another $25, and then we took $25 off the top 10 revenue. By recording what happened that week, if the buying cycle was long, you could say, "Okay, because of what we did this week, maybe after 3 weeks the revenue went down." The more you do this, the better you can understand what the change that was made two weeks ago might have been.

[00:23:15] Okay, that really had a negative impact, we didn't know that until two weeks ago, and the longer you do it, the more you can see those trends. It's also great to have these graphs. You can change what you want to see side by side and understand how different metrics affect each other.

[00:23:39] That's where the new MR is in blue and the total MR is in red. Yeah. No, sorry. Opposite. Yeah. So, and here are different metrics. I'm still figuring out what's important. I think it varies from client to client. What the client is most interested in is It changes. Creating a graph on it

[00:24:05] or changing the graphs to reflect what's important that week. That's it. That's all I have. Does anyone have any questions? Want to see anything else? No, this is great. Applause. Okay, thank you. Yeah. Yeah, we like that data-driven stuff. Yeah, that's really amazing. I didn't know there was a tool that did this much for so little.

[00:24:34] And it tracks, it tracks enc. Yeah. And there's a lot more to it. By importing Shopify cohort information, right? New customer information, that's usually, yeah. That's cool. Yeah. Yeah. So, you can avoid using third-party tools because we know they're not as accurate with attribution.

[00:24:54] So if they're using it, what do they rely on? Well, do they rely on Shopify? Triple Whale or Triple Whale, they do Google Ads and Meta Ads integration, so they rely on those tracking pixels. Yeah, but they also use UTMs, but still a lot We're finding that they're definitely not.

[00:25:23] Google gets more credit on those platforms, depending on what attribution model you're looking at, even if it doesn't change the results. Yeah. So, in that sense, because it's based on Shopify, it's likely to be better than Triple Whale or North Beam. Yeah. And you can use the tracking dock

[00:25:42] . Again, it's a little bit of a manual process, but look, it saves the client hundreds of dollars a month, because I think Triple Whale is based on revenue. Last time I spoke to a rep, it could be more than that. It could be in the thousands, depending on how much revenue you're generating.

[00:26:02] I think North Beam would be the same, right? It seems that way. Yeah. Yeah. They might be more expensive. I've never seen anyone use North Beam. Nothing like that. I had a client who used that for a short time, and then switched to Triple Whale, and Jonathan, in the last week or two, Triple Whale was only half the cost.

[00:26:25] We saw the problem that was showing. I think Triple Whale was only finding half of the actual cost. I think it was less than that. Yeah, it was way too low. So, yeah, it was completely broken. I don't know what caused it, but it was some kind of connection issue. It was weird. I had the

[00:26:44] same thing happen to me with North Beam last week. Yeah. Yeah. I think that sheet is really good. Hi everyone, I'm Mel, Senior Client Manager from Solutions 8. Today we're going to talk about smaller clients that don't have expensive facilities like North Beam or Triple Whale, and how I monitor their weekly performance. To that end,