Home > Technical & Creative Skills > Tracking Campaigns

---

Combine Cost and Revenue Stats Quickly & Easily (27)

---

When we're analyzing placement stats we need to know both the ad spend and revenue to make decisions.

Trouble is, a vast majority of traffic sources don't offer the option of passing cost data back to the tracker. And manually updating stats in the tracker can take ages, and is not very accurate, etc.

My buddy Egor (username egor) had whipped up an excel formula that has allowed us to combine two sets of data: revenue data from the tracker + cost data from the traffic source.

I've made it more user-friendly - you can download the spreadsheet from here:

https://www.dropbox.com/s/6bh5ofraz5...sets.xlsx?dl=0

(EDIT: Tim requested for a version that includes CTR so here it is:

https://www.dropbox.com/s/xi02jtxet0...wCTR.xlsx?dl=0)

(2nd EDIT: Justin has kindly made a more versatile version with a CPM column, which makes it easy to copy and paste the resulting data into the kill-whitelist stats spreadsheet - for details please see his post here:

http://stmforum.com/forum/showthread...l=1#post256532



Instructions:

1)Export placement stats from your tracker.

2)Export placement stats from your traffic source.

3)Copy and paste the following tracker stats into the spreadsheet: placement ID, # of conversions, and revenue.

4)Copy and paste the following traffic source stats into the spreadsheet: placement ID and cost.

5)Note what the row number of the last data entry on the spreadsheet is, and replace the "Ending row:" number. (Starting row will remain at "5" if that's where you started pasting data.)

6)Copy cell formula for these columns all the way down for all rows that have data: Conversion (Matched), Revenue (Matched), and Profits.

And that's it! You now have cost and revenue data combined and profits/ROI analyzed for each placement.

I could probably explain further on how the spreadsheet works but need to work on more urgent stuff right now - so I'll leave it to you guys to ask further questions and I'll answer them later? Sounds like a plan? Hope some of you will find that useful!



Amy

---

Pretty cool! Thanks.

Any way to add a CTR % for example?

---

Gold as always

Thanks for sharing!

---

Why would you need to look at CTR though?

I've added CTR to the spreadsheet for you - here it is:

https://www.dropbox.com/s/11twzwiucg...wCTR.xlsx?dl=0

Glad you guys like it! Egor's a whiz - this is such a time saver. Would have been a shame not to share this with everyone.


Amy

---

Thanks Amy, this is fantastic!

---

Oh by the way, could we this for tracking ROI of banners as well? I presume you could, but is this what you do Vortex? Thanks

---

Oh yeah! I suppose you could!

Basically this will be useful any time you need to match 2 sets of data pertaining to the same set of something (in this case, cost and revenue data pertaining to placements).

Thanks for finding another use for the tool!


Amy

---

LOL it's my unintended pleasure

Anyway back to it's intended function, how would you use this tool in combination with your ROI whitelist-kill spreadsheet? Where in the optimization process would it sit? Thanks Amy

---

The stats spreadsheet requires CPM and # of conversions as input parameters. Usually these 2 pieces of data are in 2 places - CPM stats would be in your traffic source dashboard, whereas conversions would be in your tracker. So how do you match them up? Use the tool in this thread.

This is a good reminder actually - I should link to this thread from the one about the stats spreadsheet. Better yet, when I have some time, I should incorporate the 2 tools into a single file.

Thanks again for your questions! And also for reminding me that sometimes, certain things that may be obvious to me (because I made these tools) may not be obvious to other members.


Amy

---

I'm sorry Amy, still don't get it lol. The whitelist spreadsheet requires CPM, impressions and # of conversion but the data collected for this cost/revenue spreadsheet is only cost and conversions. The whitelist calc would still need the CPM and impressions.

Maybe if I add a CPM and Impressions column next to the "cost" column under "Traffic Source Data" then pull that info at the same time as i pull the "website id" and "cost" data. Then the data will be in the exact order the whitelist spreadsheet needs (cpm, impressions, conversions) so you can just copy and paste.

Anyway, i don't wanna take up too much of your time explaining how this tool COULD be used for something other than it's original purpose. It's a great tool as it is for matching up cost/revenue! Looking forward to the "incorporation" though when you have time

---

Wow Justin thanks for going to the trouble! I've had a playing-around with your spreadsheets and everything looks correct to me. You've made them look really good as well - red and black are a sharp combo - gotta remember to try that with my next banners.

Your version is more versatile as well - people can plug in just placement CPM, or placement traffic cost and impressions, whichever data they can get from their traffic source data and/or Voluum data. And the rest of the values can be calculated. Nice work, and again thanks so much for taking the time! This type of collaboration truly makes us a great community. Best of luck with your own camps and if you need help with anything just ask.


Amy

---

Thanks Justin and Amy for these great tools. However one thing I don't understand is why we are not considering CTR in the formula for killing placements. This is because if we are not getting any clicks in the first place through a particular placement then we risk running that placement for a long time with no returns (just because their CPM and # of impressions is ok)?

---

You definitely have a point! And some people do use "rules of thumb" like "if x impressions without clicks" or "if after x impressions CTR is below y", the kill placement.

Such rules of thumb are not statistically sound because ROI is not associated with CTR (this is why CTR isn't taken into consideration), but can save you money when your estimates are accurate. This is why everyone has a different approach to cutting stuff. As long as your approach allows you to be correct more often than not, then I'd say go for it.

Thanks for your comment.


Amy

---

Hi

I am trying to make this spreadsheet work but I encountered a problem. I download the csv files from Voluum and my TS but the numbers do not correspond and some of the placement IDs look weird in Voluum.

Any ideas why this is? Is it a bug in passing placement IDs from TS to Voluum?

Placement IDs and impressions from Voluum vs TS (sorted by impressions):
http://imgur.com/a/gbkVi

---

Which traffic source is this? I'll take a look at my own stats.



Amy

---

This is from Adcash.

---

Just checked my stats and didn't see "weird" zoneids like the ones in your screenshot. But then I haven't run on Adcash in a while - the stats I was checking were from last year. So I don't know whether things have changed in the meantime.

I was thinking to maybe go over your tracking tokens, but obviously the zoneid token is working, or else nothing would get tracked. It's just that the formatting for some of the id's is different.

Could you maybe contact Adcash support to see if they could shed any light?



Amy

---

does Voluum aggregate data conversions for each placement?
In Adsbridge no

---

Depends on how you pass the data to your tracker, if you do it properly, then yes, you can check conversions per placement.

You can use the custom variables for this.

---

I highly doubt this is true.

I would suggest that you contact Adsbridge's support - they should be able to help you.

You will also need to get a postback url from Adsbridge and insert it into your affiliate network's global postback url field (after replacing some tokens).

Adsbridge should be able to help you. But if you still have questions, give us an example of an offer link from your affiliate network, and let us know which affiliate network it is, and we'll do our best to help.



Amy

---

Yes sorry now I have found how get this aggregate data thx @vortex thx @matuloo

---

Well of course.... you just have to send source variable from your traffic source to your campaign link... then you pass that info in yoiur offer url to the aff network (among other things). .. then from aff network you set a postback that will send back clickid to tracker... and then you can see conversions per source/placement.... This is like basic thing every tracker needs to have... If it doesn't have, then how they can call it a tracker?

Or I misunderstood the question?

Also: if a traffic source can track conversions, you can send it from Voluum (Traffic source postback URL field in the traffic source setup)..... , that would be {externalid}. Some traffic sources have this, some do not.... ANd some people prefer not to send it to the source, even the source supports it Also, "small" discrepancies in number of conversions are pretty normal thing (between Voluum / Aff network / Source)... Because of time zone differences, temporary tech problems etc.... However, discrepancies should not be big. If numbers are way different, then something is wrong.

---

I'm a total noob, would this work for Facebook stats using Binom tracking?

---

This is just an excel spreadsheet that can help you combine 2 sets of data that share a common data column.

In this particular case, the common data is placement IDs. The 2 sets of data are 1)costs by placement from the traffic source, and 2)revenue by placement from the tracker.

We're combining the 2 sets of data so that we can see both revenue AND cost data for each placement ID, so we'd know how profitable each placement is (or not).

But the same spreadsheet can be used to combine ANY 2 sets of data that share a common column.

I don't think this would be very relevant to FB. What is it exactly that you're trying to do?



Amy

---

Hi everyone,

For those interested I made another variation of the spreadsheet. When comparing my tracker data vs source data I typically also want to highlight which placements I've already excluded. The first version has a column where you drop all the excluded placements and it will highlight in red those placements in the source data column. The second version has a column for traffic sources that have an 'excluded' column with either 'yes' or 'no' when exporting the stats (such as with PropellerAds) and highlights them accordingly. It also combines what Justin had done with the column organization and kill-white list addition.





https://docs.google.com/spreadsheets...it?usp=sharing

Hopefully you find this useful!

---

The @justin, the links seems to be broken..just curious have you removed the link intentionally or it happens due to technical reason.

In any case, great work, really like your approach : )

---

Is this the link you're talking about? It's still working for me.

https://docs.google.com/spreadsheets...gid=1411945494

Perhaps try from another browser or another device/machine? Or clear your browser cache and try again. Must be a local issue of some sort.



Amy

---

Home > Technical & Creative Skills > Tracking Campaigns