SEM optimization
AEM 4550: Economics of Advertising
HW3 – Due at the beginning of the class Monday, March 8th
You are encouraged to work in groups of 3-4 students. Write legibly, type when you can. Include a title page, listing the homework number and the names of all the members of the study group.
PARTS I & II ANSWERS
Objective: to analyze online advertising effectiveness.
This question deals with the case “Air France Internet Marketing: Optimizing Google, Yahoo!, MSN, and
Kayak Sponsored Search” by Mark Jeffery; Lisa Egli; Andy Gieraltowski; Jessica Lambert; Jason Miller; Liz
Neely; Rakesh Sharma.
You will need the data that is posted on the course website in the spreadsheet “HW3_spreadsheet”.
When dealing with a large amount of data, such as the data on search keywords and transactions provided by Double Click, it is very beneficial to use Pivot Tables in Excel. If you have never used Pivot tables, here is a simple tutorial (it is easier than you think): http://office.microsoft.com/training/training.aspx?AssetID=RC102058721033&pid=CR100479681033 1. Create and print a Pivot table of the following descriptive statistics grouped by “Publisher Name”.
a.
b.
c.
d.
e.
Sum
Sum
Sum
Sum
Sum
Publisher Name
Google - Global
Google - US
MSN - Global
MSN - US
Overture - Global
Overture - US
Yahoo - US
Grand Total
of of of of of
Number of Users Clicking
Number of User Impressions
Number of Transactions
Total Revenue
Total Cost (Click Charges)
Sum of
Number
of Users
Clicking
72,895
192,109
11,217
10,808
60,899
119,323
45,598
512,849
Sum of
Number of Sum of
User
Number of
Impressions Transactions
1,808,326
797
3,855,689
1,550
139,979
129
170,120
140
17,898,727
372
17,062,488
289
933,345
662
41,868,674
3,939
Sum of Total
Revenue
$929,549.80
$1,745,481.80
$145,524.25
$181,549.80
$430,084.70
$347,433.25
$882,288.95
$4,661,912.55
Sum of
Total Cost
(Click
Charges)
$120,946.71
$353,640.60
HW3 – Due at the beginning of the class Monday, March 8th
You are encouraged to work in groups of 3-4 students. Write legibly, type when you can. Include a title page, listing the homework number and the names of all the members of the study group.
PARTS I & II ANSWERS
Objective: to analyze online advertising effectiveness.
This question deals with the case “Air France Internet Marketing: Optimizing Google, Yahoo!, MSN, and
Kayak Sponsored Search” by Mark Jeffery; Lisa Egli; Andy Gieraltowski; Jessica Lambert; Jason Miller; Liz
Neely; Rakesh Sharma.
You will need the data that is posted on the course website in the spreadsheet “HW3_spreadsheet”.
When dealing with a large amount of data, such as the data on search keywords and transactions provided by Double Click, it is very beneficial to use Pivot Tables in Excel. If you have never used Pivot tables, here is a simple tutorial (it is easier than you think): http://office.microsoft.com/training/training.aspx?AssetID=RC102058721033&pid=CR100479681033 1. Create and print a Pivot table of the following descriptive statistics grouped by “Publisher Name”.
a.
b.
c.
d.
e.
Sum
Sum
Sum
Sum
Sum
Publisher Name
Google - Global
Google - US
MSN - Global
MSN - US
Overture - Global
Overture - US
Yahoo - US
Grand Total
of of of of of
Number of Users Clicking
Number of User Impressions
Number of Transactions
Total Revenue
Total Cost (Click Charges)
Sum of
Number
of Users
Clicking
72,895
192,109
11,217
10,808
60,899
119,323
45,598
512,849
Sum of
Number of Sum of
User
Number of
Impressions Transactions
1,808,326
797
3,855,689
1,550
139,979
129
170,120
140
17,898,727
372
17,062,488
289
933,345
662
41,868,674
3,939
Sum of Total
Revenue
$929,549.80
$1,745,481.80
$145,524.25
$181,549.80
$430,084.70
$347,433.25
$882,288.95
$4,661,912.55
Sum of
Total Cost
(Click
Charges)
$120,946.71
$353,640.60