Data Driven Business Models
Individual Assignment: CPC AD business model
Due date : November 21 (2:30pm).
You are the manager of a search engine advertising (SEA) campaign for cellphones and you would like to know whether your SEA campaigns are successful or not. For this purpose, you would like to analyze summary data of about 40 keywords for the last 6 weeks (see Excel-sheet “Data driven business models - CPC AD Data” tab “40 Keywords Data”). You know that each conversion generates a profit contribution of $100.
Question 1 (5 points)
What is the total profit generated by your SEA campaign in the last six weeks? Report your answer as a single $ figure. Provide the supporting Excel sheet printed on one sheet of paper.
Question 2 (5 points)
We will now estimate the multipliers in the price response function and the click response function. So far we used subjective estimates. You are fortunate and receive daily data for one of the keywords for the last 3 weeks (see tab “1 Keyword Data”).
Estimate the multiplier in the price response function. The basic approach to do this is to run a regression of ln(CPC) on the “average rank.” The coefficient of “average rank” β then be transformed into the multiplier by computing δ = 1/exp( β) . Report the estimated multiplier.
Question 3 (5 points)
Estimate the multiplier in the click response function. The basic approach to do this is to run a regression of ln(CTR) on the “average rank.” The coefficient of “average rank” θcan then be transformed into the multiplier by computing ξ = 1/exp(θ) . Report the estimated multiplier (see tab “1 Keyword Data”).
Question 4 (10 points)
Finally you would like to know the profit after acquisition costs for ranks 1 to 10 for this specific keyword. For your calculations, please assume 30,000 searches, a conversion rate of 2%, and a profit contribution of $100 per conversion.
Using the two multipliers, please calculate for ranks 1 to 10 the CPC and CTR (these are predicted from the regression models you estimated in questions 5 and 6 respectively), the number of clicks and conversions, the profit per conversion before acquisition costs, the CPO (cost per order or also called cost per conversion), the profit per conversion after acquisition costs, the profit before acquisition costs, the acquisition costs, and the profit after acquisition costs.
Please illustrate your results using a graph shows on the X-axis ranks 1 to 10, and on the Y-axis the profit before acquisition costs (use bars), the acquisition costs (use bars), and the profit after acquisition costs (use line).
Question 5 (5 points)
At what rank is the profit optimized for this keyword?