Assignment: Search Engine Advertising
You are the manager of a search engine advertising (SEA) campaign for cell phones 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 "SEA Assignment Data" tab "40 Keywords Data"). You know that each conversion generates a profit contribution of $100.
Question 1
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
You would now like to compute the optimal bid for a specific keyword using the PROSAD system. To begin with, you use subjective estimates for the multipliers in the price response function and the click response function. The multiplier in the price response function is the percentage increase in prices per click with each improved rank position. Assume it is 25% (in other words, the parameter value = 1.25). The multiplier in the click response function is the percentage increase in clickthrough rates with each improved rank position. Assume it is 70% (parameter value = 1.70). You also assume a conversion rate of 2%. Calculate the optimal bid for this specific keyword given the above values. Hint: Remember that the formula for the optimal bid uses the natural log of the multipliers.
Question 3
You are not sure whether the conversion rate is really 2%. Google has reported an average clickthrough rate for the US and other Western European countries of 1%. Use a conversion rate of 1% to calculate the revised optimal bid. Why did the bid change in that direction?
Question 4
You decide that it might be worthwhile to also consider the long-term value of the customer to evaluate the profitability of your keywords instead of only the short-term value. Your colleague from the analytics team tells you that the average customer lifetime value (CLV) at your company equals $750 (this number is BEFORE subtracting acquisition cost). Again assume a conversion rate of 1%. Calculate the new optimal bid. What is the optimal bid now? Why did it change in that direction?
Question 5
We will now revisit 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 Excel-sheet "SEA Assignment Data" 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 6
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 Excel-sheet "SEA Assignment Data" tab "1 Keyword Data").
Question 7
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. 3 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 8
At what rank is the profit optimized for this keyword?
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Search-Engine-Advertising-Data.rar