FORMULA’S APPLIED IN THIS EXERCISE
CTR = % of customers who click thru a link = No. Of clicks No. Of impressions
TCR = Sum of total volume of bookings / No. Of clicks
Take rate = probability Of booking = TCR * CTR
ROA = Return on ad dollars spent = Net revenue/cost
Q1. Which search engines produce the best marketing campaign results? In order to find which search engine produces the best marketing campaign result for Air-France, we have to calculate ROA(return on advertising) corresponding to every publisher.
Publisher Name| Sum of Amount| Sum of Total CAost| Sum of Net Revenue| ROA| Google - Global| 929549.8| 120946.712| 808603.088| 669%| Google - US| 1745481.8| 353640.5989| 1391841.201| 394%| MSN - Global| 145524.25| 12160.36244| 133363.8876| 1097%| MSN - US| 181549.8| 16098.48741| 165451.3126| 1028%|
Overture - Global| 430084.7| 64295.86214| 365788.8379| 569%| Overture - US| 347433.25| 141976.0745| 205457.1755| 145%| Yahoo - US| 882288.95| 46197.82462| 836091.1254| 1810%| | | | | |
Grand Total| 4661912.55| 755315.922| 3906596.628| 517%|
According to ROA analysis, Yahoo-US has got the highest ROA. Therefore, Yahoo-US produces the best marketing campaign results(as shown in chart on next page). But these ROA’s are being calculated in absence of market share knowledge of these publishers.
Q2. Which search engines should receive more marketing dollars? Which less? Should any be killed? The search engine which is giving biggest bang for the marketing buck in SEM, should receive more marketing dollars. But to find this, we need to plot 2 X 2 matrix between Average cost per click and probability of booking (take rate) among different publishers.
Detailed analysis of comparison between different publishers is summarized in table below:
Publisher Name| Sum of Clicks| Sum of Click Charges| Sum of Impressions| Sum of Total Volume of Bookings| CTR| TCR| Take Rate(Probability of booking)| Average Cost per click| Google - Global| 72895| 120946.7| 1808326| 797| 0.040| 0.011| 0.0441%| 1.66| Google - US| 192109| 353640.6| 3855689| 1550| 0.050| 0.008| 0.0402%| 1.84| MSN - Global| 11217| 12160.4| 139979| 129| 0.080| 0.012| 0.0922%| 1.08| MSN - US| 10808| 16098.5| 170120| 140| 0.064| 0.013| 0.0823%| 1.49| Overture - Global| 60899| 64295.9| 17898727| 372| 0.003| 0.006| 0.0021%| 1.06| Overture - US| 119323| 141976.1| 17062488| 289| 0.007| 0.002| 0.0017%| 1.19| Yahoo - US| 45598| 46197.8| 933345| 662| 0.049| 0.015| 0.0709%| 1.01| Grand Total| 512849| 755315.9| 41868674| 3939| 0.012| 0.008| 0.0096%| 1.47|
Using Take rate and Average cost per click, below matrix is plotted. Also the red lines(which divide the region into four quadrants) represent the overall probability of booking ie .0096% and overall average cost per click ie 1.47
More Marketing Dollars
The publishers with low average cost per click and high take rates are clearly working (bottom right of above matrix). For these publishers, we should consider increasing marketing spending. For example, MSN-Global and Yahoo-US lie in this bottom right quadrant. Therefore, both of them should receive more marketing dollars.
The high‐CPC, low‐take‐rate publishers are not working. We can consider killing funding for these publishers by doing deep analysis of which keywords etc are not working. But there are currently no such publishers (in top left quadrant) Hence no publisher should be killed.
Less funding: The top right quadrant of ABOVE matrix corresponds to high take rate but expensive cost per click. These publisher campaigns are working, but at a high cost. The idea is to seek out high‐ROA keywords and figure out what's working. Among the publisher’s lying in top...