maVIn

niiiiice +rep

bluebuddha

If you guys are looking for a desktop calc, check out Qalculate (Qalculate! - the ultimate desktop calculator). It's amazing, not sure if it's for windows though.

tacoX

Numbers confuse me. I usually just go on hunches.

__________________

Cap'nShplanky

+rep. Thanks much for this. The math really is not all that hard, even for an English major like me. BUT this spreadsheet will help make sure I'm keeping everything important in mind. Plus it makes things a little easier & *quicker* and makes it less likely I'll forget or flake on something. Thanks again.

__________________

Mike

Since this thread keeps getting replied to a random times, I'll add mine.

+rep to me for saving my own ass I lost this spreadsheet somehow and thank god I uploaded it here. Yay! Sometimes even a blind squirrel finds a nut.

__________________

ΜΟΛΩΝ ΛΑΒE

FutureWEBSTORE

I am not the sharpest here. I clicked D

ldsmedia

Xentech, you got a point. You just have to remember that Americans think the greatest value of anything is the ability to monetize it. But the fact is can't monetize a worthless product forever. Nor can you monetize air (well there are some exceptions to that).

Ir all works together and in my mind it is all necessary. By the way Mike, I used your sheet to calculate what I could afford to spend on clicks for my fist campaign. Now I understand why it is a great deal of work to make money with ppc.

It's analyze and adjust then analyze and adjust again. don't do it too often but don't do it too infrequently either. YEESH

Super-Affiliate

I would not be judging myself as Super good in Maths, however, according to my experience and calculations,

I would go for "B"... This is because if I am doing PPC and getting 2500 clicks from very targetted Network (e.g. Adwords), and if the campaign converts at 4% (according to network ), then here is some maths..

2500 Clicks * 4% = 100 Leads = 2k$..= $1.5k in Profits within 24 hours (I would run this campaign only for 24 hours at its best)..

Guys, if you have any other option which can earn $1.5k from A,C, please post!

__________________
Yummy, Sex sell & so do AM

Super-Affiliate

Forgot to say thanks Mike. +Rep...the spreadsheet is really cool.

__________________
Yummy, Sex sell & so do AM

Submerge

I chose option B. But then I thought, where could I invest that $150 I didn't spend on Capaign C? Ah well.

RDion12

math sometimes make me sick.lol

__________________
[Annoying "monies on autopilot" sig here]

herman333

a b c

elabs

B's the smart option with its 4 fold return...and thanks for the handy spreadsheet.

gamernz

Didn't have a decent spreadsheet like yours so messed up the calculation on Option C and threw my vote there. Thanks for the sheet it should really help in the future.

maree

I agree; the spreadsheet is very helpful. The math exercise is very good, too, especially for noobs who need specific guidance. Thanks for taking the time to do this.

monkeymeat

Thanks for the spreadsheet, Mike! I'm a Business Major so I should have thought of it.
Probably explains why I'm still so Goddamn poor, lol.

bobsoap

Lol that is hilarious. I'll +rep you just for that once the 24 hours are over.

Traffic-Bug

Thanks for all the infos.

ring69

how can i make some money, with investment ? i have read some things, but even so, i am to newbie ^^

miketpowell

No offense to anyone that missed this but this is BASIC math.... I do much much more complicated stuff every day and I don't know how I would have been profitable without it.... But I am a math major and perhaps compensate with my lack of good marketing with great mathematical optimization.

Mike

I'm happy to see the spreadsheet is still helping people out. It is basic math and I have friends that wear out calculators doing it. Fortunately, I'm lazy and like to let my computer do all that work with just minimal input.

__________________

ΜΟΛΩΝ ΛΑΒE

ChrisB711

Thanks for the advice! We all need reminders like this from time to time.

Paul Moore

I have been using the new Wolfram Alpha search engine to do my math for me. It is pretty cool. Not like google in that it takes the worlds knowledge and allows the user to even do math calculations. Check it out.
Wolfram|Alpha

__________________
Fr.ee Online Coaching Starts (Jan 2010)
go here ==> Marketing With Paul dot com

Mike

That's pretty cool! Thanks for sharing!

__________________

ΜΟΛΩΝ ΛΑΒE

extor

Time for you to make a thread about some of that complicated optimization math then. I'm serious. And no offence to the OP.

Mike

Uh, okay. No offense taken?

__________________

ΜΟΛΩΝ ΛΑΒE

Putnam Junior

Thanks for the spread sheet!

knukk

Going to post some assignments for those that want to try out their skills. Will start out with one at the easy level. Will add assignments for different levels as we go on.

CPC = cost pr click
aCPC = average cost pr click
CTR = click through rate
CPM = cost pr thousand impressions


Assignment 1 (level: newbie)
Assume you have two campaigns:

1. teeth whitening
2. government grants

Statistics for the teeth whitening campaign:

• Ad impressions: 2 000 000
• Ad CTR: 1.25 %
• Landing page CTR: 15 %
• End page conversions: 75 %
• Payout: $30 pr conversion
• Ad aCPC: $1.50
• Ad cost scheme: CPC

Statistics for the government grants campaign:

• Ad impressions: 2 000 000
• Ad CTR: 0.95 %
• Landing page CTR: 35 %
• End page conversions: 50 %
• Payout: $40 pr conversion
• Ad cost scheme: CPM
• CPM: $ 50
• If it were on CPC cost scheme, the average CPC would be: $ 4.75
• Assume that it is not possible to run the campaign with CPC

Assume no other costs.
What costs are there to each campaign? How much revenue does each campaign generate? What are the profits and what campaign is the most profitable?
If you want to give it a go yourself I suggest you start calculating now before you read spoilers in posts below.

__________________

miketpowell

Whoops 50/9.5 =! $4.75

knukk

It's not supposed to be either.

__________________

knukk

End page = advertiser's page
This one is a bit more advanced.

Assignment 2
You have $ 10 000 and you are wondering on how to spend them.
Currently the money is in your bank account with an awesome 0 % yearly interest rate.
Your uncle is trying to convince you to invest the money in securities through which you will gain a 20 % annual return for sure. These earnings will be equally distributed throughout the year.

You are currently running two campaigns (online marketing):

1. Acai berries
2. Teeth whitening

Acai berries campaign stats:

• Ad impressions: 2 000 000
• Ad CTR: 2.25 %
• Landing page CTR: 40 %
• End page conversions: 40 %
• Payout: $30 pr conversion
• Ad aCPC: $3.50
• Ad cost scheme: CPC

The teeth whitening campaign is promoted via Facebook and Google AdWords:
Teeth whitening Facebook stats:

• Ad impressions: 5 250 000
• Ad CTR: 1.45 %
• Landing page CTR: 25 %
• End page conversions: 6 000
• Ad aCPC: $1.2
• Ad cost scheme: CPC

Teeth whitening AdWords stats:

• Ad impressions: 10 259 000
• Ad CTR: 0.45 %
• Landing page CTR: 30 %
• End page conversions: 35 %
• Ad aCPC: $1.75
• Ad cost scheme: CPC

Payout for teeth whitening conversions: $20 pr conversion

Question 1: Consider the Facebook ads you have running for teeth whitening. How many per cent of the customers that go from your landing page to the advertiser's page orders the product?

Due to the recent negative reviews on online Acai marketers in the news, you can assume that your landing page CTR will decrease by 22 %.

Question 2: How many will click through away from your landing page (and on to the end page) in the acai berries campaign? Calculate per cent and actual number.

Assume you'd like to keep the teeth whitening ads on the same network from now on. Thus, you will have to choose between Facebook Ads and Google AdWords.
You are only willing to continue the teeth whitening campaign with totally new ads on Facebook, that you will have to come up with.
The Facebook Ad Approval department disapproves 4/5 of your ads. You want 50 different ads up and running for targeting purposes and to test different ad copies.
Assume the Facebook approvals take 6 hours pr ad you submit. You only submit one at the time, since you're lazy. This means you wait for an approval or a disapproval before you submit your next ad. You will pause all ads until all 50 are approved.
Assume the stats via Facebook will be the same as previously, but take the lowered CTR (question 2) into consideration. The time you spend on adding new ads is the same time you would use to flip websites for a $ 1000 profit.

For AdWords you will use the same old ads.

Because of the recent negative against Acai you will have to tweak your landing page a little. You outsource this for $ 250 (sales letter & new LP design).
You are going to make 150 new Acai berry ads. This task will also be outsourced for $ 50, and is completed almost immediately. You are still going to use the ad network through which you have previously promoted it.

Question 3: Should you spend your money on the Acai berries or the Teeth Whitening campaign? Assume that you are to spend every penny of those $10 000. Show relevant calculations.

__________________

knukk

In Assigment 2 you are also supposed to show what network you would choose for the teeth whitening campaign.

__________________

knukk

Assignment 3
You are in discussions with an advertiser for a special deal on affiliating their product. The advertiser pays you NET30 (30 days after the commission was registered). This means if you sent leads worth of $ 1000 in commissions one day, you are credited that amount 30 days later. Currently you send leads worth $ 40 000 in commissions every month.

You offer this deal to the advertiser:
"You can decrease my commissions by 1 % if you pay me NET10 from now on."
Under what condition does he accept your offer?

__________________

knukk

Assignment 4
You have a website that is budgeted to generate $ 27 000 in revenues pr year the next 4 years.
Webhost costs are $ 1 000 pr year. Advertising costs are $ 2 000 pr year.
You outsource the article writing for the website. You will put on 40 articles pr month, 25 of which are 600 words (per article). The rest are 1000 words per article.
You will pay the article writer $4/100 words.

It is expected that you can sell it for $ 25 000 in 4 years.
Someone is offering you $ 50 000 for the website now.
The rate of return is 10 %.
What do you do - sell it now or keep it running and then sell it after 4 years?

__________________

knukk

Assignment 5
You are an email marketer. You have observed the following:
Total emails sent pr week for 10 weeks: 49872, 52034, 47322, 56999, 48932, 43456, 53104, 50888, 45032, 49999
Total costs pr week for 10 weeks (the same weeks as above): 1500, 1743, 1388, 1844, 1599, 1333, 1900, 1011, 1803, 1800
You want to estimate a function for your total costs for a week as a function of sent emails that week. What is it?

__________________

grimaceaz

Gonna take a stab at assignment 1.

What costs are there to each campaign?
teeth) $37,500.00
grant) $100,000.00

How much revenue does each campaign generate?
teeth) $84,375.00
grant) $133,000.00

What are the profits and what campaign is the most profitable?
teeth) $46,875.00
grant) $33,000.00

Teeth was more profitable with a 225% ROI vs 133% ROI for grants

Mike

Teeth Whitening
Cost: $3750
Revenue: $8430
Net: $4680
ROI: 124.8%
EPC: $3.37
CPA: $13.35
eCPM: $1.88

Grants
Cost: $100,000
Revenue: $133,000
Net: $33,000
ROI: 33%
EPC: $7
CPA: $30.08
eCPC: $5.26

No cheating here. Just me an my calcu-ma-tator (aka Spreadsheet)

__________________

ΜΟΛΩΝ ΛΑΒE

Mike

Check your 1000's separators...

__________________

ΜΟΛΩΝ ΛΑΒE

grimaceaz

oops...

dcaffiliated

I'm getting the same results. I've gone over this a few times now, Mike any idea what I'm doing wrong?

I know you said check your 1000 but I keep coming to the same solution. I'm not sure what error I'm making, since we have the same answer for the grant offer...the teeth whitening I must be doing something very wrong here...

• Ad impressions: 2 000 000
• Ad CTR: 1.25 %
• Landing page CTR: 15 %
• End page conversions: 75 %
• Payout: $30 pr conversion
• Ad aCPC: $1.50
• Ad cost scheme: CPC

This is the process I used:

#of ppl that clicked the ad : 2,000,000 * .0125 = 25,000
#of ppl that clicked through to offer : 25,000 * .15 = 3,750
#of conversions : 3750 * .75 = 2,812.5

revenue= 2,812.5 * $30 = $84,375
expense= 25,000 * $1.50 = $37,500
profit = $84,375 - 37,500 = $46,875

Mike

Hmmm...you know what, looks like I need to check myself I think you're right. Not sure where I got my numbers from.

__________________

ΜΟΛΩΝ ΛΑΒE

knukk

grimaceaz' and dcaffiliated's numbers are correct SEO_Mike, your government grants numbers are correct but the numbers for the teeth whitening are wrong.

My solution to assignment 1:
Teeth whitening:
Revenue: 2 000 000 * 0,0125 * 0,15 * 0,75 * 30 = 84 375
Costs: 2 000 000 * 0,0125 * 1.50 = 37 500
Profit: 46 875


Government grants:
Revenue: 2 000 000 * 0,0095 * 0,35 * 0,50 * 40 = 133 000
Costs: 2 000 * 50 = 100 000
Profit: 33 000

Thus, Teeth Whitening is the most profitable.

__________________

knukk

Your numbers for the teeth whitening campaign seem to be the correct numbers divided by 10 (with an approximation).

__________________

knukk

Well, I see now that it can be interpreted in two ways. So it was my mistake for not being clear there.

One interpretation, which I think you had, was that if you took the numbers presented and calculated the CPC from them you would get 5.26. Whereas I just meant that if it was on CPC cost scheme, considering the competition etc, it would be 4.75 (just a made up number). I was too unclear about that, but it didn't have anything to say for the other calculations

__________________

knukk

Does anyone want to give assignment 2 a go? Be sure to take necessary assumptions where needed. Provide the assumptions with the solutions.

__________________

kingwhat

Nice.

Why did one straw break the camel's back? Here's the secret:
the million other straws underneath it - it's all mathematics
- Mos Def

bigsherm

This is pretty cool, just did Assignment 2 here's what I got:

Question1:

19,031 to End Page.... 6,000 conversions / 19,031 = 31.5% Conversion for FB

To get that: 5,250,000 * .0145 * .25 = 19,031.

The new CTR on Acai would be 40% * .78 = 31.2%

Question 2:

Total clicks to the end page would be 891 => $10,000/3.5CPC = 2,857 Clicks X 31.2% (40% CTR * (1-.22))

and that new CTR is the 31.2% calculated above by 1 - .22 .

To get the total FB Profit:
10,000 / 1.2CPC = 8,333 Clicks X 25%CTR = 2,083 X 31.5% Conv. = 636 Conversions X $20 Payout = $13,120 Revenue - $10,000 Investment = $3,120 Profit

Assumption: The $1,000 from web site flipping doesn't affect the value of your FB investment as it will be applied to a campaign in which you can use it; AdWords and Acai.

AdWords Profit:

10,000 / 1.75CPC= 5,714 Clicks X 30% CTR = 2,083 X 35% Conv. Rate = 600 Conversions x $20 Payout = $12,000 Revnue + $1,000 from Site flipping = $13,00 Revenue - $10,000 invested = $3000 Profit for Adwords.

Acai Berry Profits:

$10,000 / $3.5 = 2,857 Clicks X 31.2% CTR (from Q2) = 891 X 40% = 356 Conversions X $30 Payout = $10,680 Revenue - $10,000 Invested - $300 Outsourced Work + $1000 Site Flipping= $1380 Profit.


Conclusion:

From what I've calculated, I'd go with the Facebook ad if it were my only investment.

knukk

bigsherm:

Answer 1: Correct.

Answer 2: Correct.

Answer 3: Much of the calculations are correct, but there is an important thing missing. Here are comments and pointers:

• Good notice about the website flipping. The $ 1 000 from website flipping is relevant since it represents an alternative cost. By adding the Facebook ads for approval, you give up the $ 1 000 profit. You chose to add the website flipping profit to the AdWords and Acai Berry campaign, which is fine. An alternative method would be to deduct the given up profit from the FB calculations, but it doesn't really matter where you add it or deduct it as long as it's consistent throughout the calculations as you are. As long as one is consisten throughout the calculations, the comparison will be correct. If someone wants a closer explanations to this let me know
• Pointer: In AdWords and Facebook you can load money from your bank account / card. When one choses to load one's money can have a great load of effect. Why? And when do you chose to fill your account with credit? PS! Also find out how long it takes you to get all your Facebook ads approved.

__________________

bigsherm

50/.8 = 63. Which is the total number of ads submitted to get the 50 approved. 63 ads x 6 hours to be approved each = 378 Hours / 24hr = 16 Days, assuming this lazy example can be disciplined enough to submit an ad every 6 hours for 16 consecutive days.

If this were on weekly wires, the Facebook campaign would lose it's relative value to the AdWords group because of the 3 week lag. With AdWords you could reinvest each week and grow it as rapidly as reinvestment allows (now, I'm assuming you can get it wired to the account you loaded it up with and it goes smooth and that your 13,000 in revenue can be realized in a week...haven't gotten here yet but I like the numbers )

With FB the ROI = 3,120 / 10,000 = 31.2% or .312 Cents of Profit per dollar spent.

Adwords = 30 cents per Dollar of Profit.

Without building a little Excel sheet and projecting this out, we can see this principle in the first 4 weeks.

WK1:
Adwords - $13000 Revenue
FB - 0

WK2:
Adwords - $13000 x (1+.30) = 16,900
FB - 0

WK3:
Adwords - $16,900 x 1.3 = 21,970
FB - $13,210

WK4:
AdWords - $21,970 x 1.3 = $28,561
FB - $13,210 x 1.312 = $17,331.52

AdWords takes over rather fast. The key here is rapid payment cycle, an argument can be made that getting to weekly wires may be as important as improving your ROI another 15%. With that, if you cannot break the 1k/wk threshold, maybe negotiating a payout decrease to get weekly wires to reinvest is a strong idea. "Just this once to see how it works"

Who knows, I'm still working a shitty job to save up a strong base like this to start PPC so I can immediately get to wires so I'm just saying what I've read so far.

I'll try some more of these throughout the days, thanks for the practice and the effort you put into creating these.

knukk

You're getting there, but it's not 4 out of 5 ads that are approved: 4 out of 5 ads are disapproved, and you must also take into consideration that the days you bind capital could be used to trade in securities (as mentioned in the assignment). To simplify the calculations I presumed a yearly 20 % ROI from the trading and that the profits were distributed equally throughout the year.

The idea with behind this assignment is to understand that actions have relevant alternative costs and to understand the implications of the assumptions one make.

Below is my solution to the assignment (correct solutions may vary a bit due to different assumptions).

Also, everyone should keep in mind that the way bigsherm calculated the profits by adding the website flipping profits of $ 1 000 to the AdWords and Acai campaigns is accepted. I did it the other accepted way, where I deduct the $ 1 000 from the Facebook campaign's profit.

It is also important to distinguish between a campaign's absolute profits and its relevant profits when one is comparing different campaigns one wants to chose the most profitable from.

Some numbers may vary slightly depending on how one round off numbers such as in Q1.

Here is my solution:
Q1: 6 000 / 19031,25 ~= 31,53 %

Q2:
number: 10000/3.5 * 0,4 * (1 - 0,22) = 891
(If one interprets the number to be the number from the historical data, and not with the new $10 000 budget, the number is: 2 000 000 * 0, 0225 * 0,4*0,78 = 14040)
percentage: 0,4 * (1-0,22) = 0,4 * 0,78 = 0,312 = 31,2 %

Q3:


First, you have to compare which of the ad networks to use.
Assuming filling up credits at once:
Earnings using Google AdWords:
Revenue = 10 000 / 1,75 * 0,3 * 0,35 * 20 = 12 000
Costs = 10 000
Add alternative cost for trading securities, depends on how long the campaign takes.
Assuming the campaign is done almost at once the alternative costs are 0.
Profits = 2 000


Earnings using Facebook:
Revenues = 10 000 / 1,2 * 0,25 * 0,3153 * 20 = 13 137,50
Ad Costs = 10 000
Alternative cost - website flipping $ 1 000
Alternative cost - trading securities: days it takes before all ads are approved (remember the assignment said that every ad was paused until every of the 50 ads were approved) = 50*5*6/24 = 62,5 days. --> Alternative cost = 10 000 * 0,20 * 62.5 / 360 = 347,22
Relevant profit = 1790,28


Thus, AdWords is the best choice.

Now, compare the acai berries vs the teeth whitening:
Acai berries:
Revenues: 10 000 / 3,5 * 0,312 * 0,40 * 30 = 10697,14
Ad costs: 10 000
Outsource new LP: 250
Outsource new ads: 50
Profit: 397,14


Teeth whitening profits = 2 000

Teeth whitening is the best.

__________________