Display Advertising Revenue

The chart below describes the display advertising revenue across five companies in five years in billions of dollars. Display advertising is advertisement found in the form of image, videos, rich text and others on the website. One example is Facebook, the ads that are presented on the right columns.

Part 1: Create a .CSV file (or optionally, a MySQL database!) that includes all of the information above:
Part 2: Read the information from your .CSV file into R, and use tidyr and dplyr as needed to tidy and transform your “data”:
Part 3: Perform analysis on the data.

Part 1:

Installing all the necessary packages needed for this data analysis.

library(sqldf)

## Loading required package: gsubfn
## Loading required package: proto
## Loading required package: RSQLite
## Loading required package: DBI

library(tidyr)

## Warning: package 'tidyr' was built under R version 3.2.2

library(dplyr)

## Warning: package 'dplyr' was built under R version 3.2.2

## 
## Attaching package: 'dplyr'
## 
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## 
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(curl)
library(ggplot2)
library(RColorBrewer)
library(ggthemes)

## Warning: package 'ggthemes' was built under R version 3.2.2

library(knitr)

Created a “wide” structure table using sqldf. Changed the row names in the table with the first column and then deleted the first column.

ad_revenue <-sqldf(c("Drop table if exists revenue","CREATE table revenue ( ID serial PRIMARY KEY, Year int (10) NOT NULL, Google int NULL, Facebook int NULL, Yahoo int NULL, Microsoft int NULL, AOL int NULL)",
  "INSERT INTO revenue (ID, Year, Google, Facebook, Yahoo, Microsoft, AOL) Values ('1','2009','0.36','0.56', '1.26', '0.37', '0.51'), ('2', '2010','0.86','1.21', '1.43', '0.51', '0.47'), ('3', '2011', '1.67', '1.73', '1.36', '0.6', '0.53'), ('4','2012','2.26','2.18', '1.35','0.9','0.7'), ('5','2013','2.99','3.17','1.27','0.79','0.73')", "Select * From revenue"))

## Loading required package: tcltk

rownames(ad_revenue) <- ad_revenue[,1]
ad_revenue <- ad_revenue[,-1]
ad_revenue

##   Year Google Facebook Yahoo Microsoft  AOL
## 1 2009   0.36     0.56  1.26      0.37 0.51
## 2 2010   0.86     1.21  1.43      0.51 0.47
## 3 2011   1.67     1.73  1.36      0.60 0.53
## 4 2012   2.26     2.18  1.35      0.90 0.70
## 5 2013   2.99     3.17  1.27      0.79 0.73

I created a .CSV file with the display ad revenue table in my local GitHub repository.

write.csv(ad_revenue, file = "C:/Users/Nabila/Documents/GitHub/Class-IS607/Project 2/Display Advertising Revenue/Ad_Revenue_Table.csv")

Part 2:

Using the library curl I uploaded the display advertising revenue table from my online GitHub repository.

display_ad <- read.csv(file="https://raw.githubusercontent.com/nabilahossain/Class-IS607/master/Project%202/Display%20Advertising%20Revenue/Ad_Revenue_Table.csv", header=TRUE, sep=",")
display_ad

##   X Year Google Facebook Yahoo Microsoft  AOL
## 1 1 2009   0.36     0.56  1.26      0.37 0.51
## 2 2 2010   0.86     1.21  1.43      0.51 0.47
## 3 3 2011   1.67     1.73  1.36      0.60 0.53
## 4 4 2012   2.26     2.18  1.35      0.90 0.70
## 5 5 2013   2.99     3.17  1.27      0.79 0.73

Using libraries dplyr and tidyr I transformed and tidied the data. I created a new table by transforming my original table from “wide” structure to “long” structure.

display_ad_revenue <- display_ad %>% select(-X) %>%  gather("Company", "Revenue", 2:6)
display_ad_revenue

##    Year   Company Revenue
## 1  2009    Google    0.36
## 2  2010    Google    0.86
## 3  2011    Google    1.67
## 4  2012    Google    2.26
## 5  2013    Google    2.99
## 6  2009  Facebook    0.56
## 7  2010  Facebook    1.21
## 8  2011  Facebook    1.73
## 9  2012  Facebook    2.18
## 10 2013  Facebook    3.17
## 11 2009     Yahoo    1.26
## 12 2010     Yahoo    1.43
## 13 2011     Yahoo    1.36
## 14 2012     Yahoo    1.35
## 15 2013     Yahoo    1.27
## 16 2009 Microsoft    0.37
## 17 2010 Microsoft    0.51
## 18 2011 Microsoft    0.60
## 19 2012 Microsoft    0.90
## 20 2013 Microsoft    0.79
## 21 2009       AOL    0.51
## 22 2010       AOL    0.47
## 23 2011       AOL    0.53
## 24 2012       AOL    0.70
## 25 2013       AOL    0.73

I transform and tidy the data more. I summarized the data, by finding the total revenue of display advertising by year and created a table dar1 (display ad revenue 1). Then I combine the tables, to create another table dar2 that has the revenue from the five companies and the total revenue for each year.

Company <- c("All Five Companies", "All Five Companies", "All Five Companies", "All Five Companies", "All Five Companies")
dar1 <- display_ad_revenue %>% group_by(Year) %>% summarise(Revenue = sum(Revenue)) %>% cbind(Company) %>% select(Year, Company, Revenue)
dar1

##   Year            Company Revenue
## 1 2009 All Five Companies    3.06
## 2 2010 All Five Companies    4.48
## 3 2011 All Five Companies    5.89
## 4 2012 All Five Companies    7.39
## 5 2013 All Five Companies    8.95

dar2 <- display_ad_revenue %>% bind_rows(dar1) %>% arrange(Year)

## Warning in rbind_all(x, .id): Unequal factor levels: coercing to character

dar2

## Source: local data frame [30 x 3]
## 
##     Year            Company Revenue
##    (int)              (chr)   (dbl)
## 1   2009             Google    0.36
## 2   2009           Facebook    0.56
## 3   2009              Yahoo    1.26
## 4   2009          Microsoft    0.37
## 5   2009                AOL    0.51
## 6   2009 All Five Companies    3.06
## 7   2010             Google    0.86
## 8   2010           Facebook    1.21
## 9   2010              Yahoo    1.43
## 10  2010          Microsoft    0.51
## ..   ...                ...     ...

I also find the total and the average revenue, by company and create table dar3.

dar3 <- display_ad_revenue %>% group_by(Company) %>% summarise(Total_Revenue = sum(Revenue), Average_Revenue = mean(Revenue)) %>% data.frame()
dar3

##     Company Total_Revenue Average_Revenue
## 1    Google          8.14           1.628
## 2  Facebook          8.85           1.770
## 3     Yahoo          6.67           1.334
## 4 Microsoft          3.17           0.634
## 5       AOL          2.94           0.588

Part 3:

Using ggplot2 I created a bar graph using table dar2. This will help me visualize the display ad revenue by year for each company and combined revenue for all five companies. Looking at the graph I can see that the combined revenue for the five years are steadily increasing from 2009 to 2013. In 2009 and 2010 Yahoo had the most revenue in display ad, then the other three companies. However this changes over time. In 2011 Facebook and Google had more revenue than Yahoo. In 2012 and 2013 Facebook and Google had almost twice the amount of display ad revenue then Yahoo.

ggplot(data = dar2, aes(x = Year, y = Revenue, fill = Company)) + geom_bar(stat ="identity", position="dodge") + scale_fill_brewer(palette = "PuBuGn")  + theme_hc(bgcolor = "darkunica") + scale_colour_hc("darkunica") + ggtitle("Graph 1: Display Advertising Revenue - Throughout the Years") + ylab("Display Ad Revenue in Billions of Dollars")

I created a line graph with display advertising revenues for the five companies over the period of five years. Looking at the graph I can see that the revenue for Google and Facebook has been increasing linearly over five year. On the other hand, the revenue for the revenue for Yahoo has been almost constant, around 1.3 billion dollars.

ggplot(data = display_ad_revenue, aes(x = Year, y = Revenue, group=Company, color = Company)) + geom_line() + geom_point(aes(shape = Company), size = 3.5) + scale_shape_manual(values = c(7:11)) + theme_igray() + scale_colour_tableau() + theme(legend.position = "top") + ggtitle("Graph 2: Display Ad Revenue across five company in five years") + ylab("Display Ad Revenue in Billions of Dollars")

The third graph, below shows the total and average revenue for the five companies. Looking at the graph we can see that Google and Facebook have very high total revenue over 8 billion dollars. However their average revenue is closer to 1.5 billion dollars, similar to Yahoo.

dar4 <- dar3 %>% gather("Revenue", "Dollars", 2:3)
ggplot(data = dar4, aes(x = Company, y = Dollars, fill = Revenue)) + geom_bar(stat ="identity", position="dodge") + theme_solarized() + scale_fill_brewer(palette = "Accent") + geom_text(aes(label = Dollars), hjust=0.5, vjust=-0.5, size=3) + theme(legend.position = "bottom") + ggtitle("Graph 3: Display Ad Revenue for Five Companies.") + ylab("Billions of Dollars")

I create a new table using knitr to show the display advertising revenue for five companies over five years. The table shows the total revenue by year and the total revenue by company. It also shows the total revenue of all companies over five years. It also has the average revenue of each company and the average revenue as a whole.

Total_Display_Ad <- dar2 %>% spread(Year, Revenue) %>% data.frame() %>% arrange(desc(Company)) %>% select(Company, "2009"=X2009, "2010"=X2010, "2011"=X2011, "2012"=X2012, "2013"=X2013) %>% left_join(dar3, by = "Company")

## Warning in left_join_impl(x, y, by$x, by$y): joining factor and character
## vector, coercing into character vector

Total_Display_Ad$Total_Revenue[is.na(Total_Display_Ad$Total_Revenue)]  <- display_ad_revenue %>% summarise(Total_Revenue = sum(Revenue))
Total_Display_Ad$Average_Revenue[is.na(Total_Display_Ad$Average_Revenue)]  <- display_ad_revenue %>% summarise(Average_Revenue= mean(Revenue))

Total_Display_Ad$Company[Total_Display_Ad$Company == "All Five Companies"] <-  "Total"

kable(Total_Display_Ad, align = "c", caption = "Table 1: Display Advertising revenue for five companies in five years.")

Table 1: Display Advertising revenue for five companies in five years.
Company	2009	2010	2011	2012	2013	Total_Revenue	Average_Revenue
Yahoo	1.26	1.43	1.36	1.35	1.27	6.67	1.334
Microsoft	0.37	0.51	0.60	0.90	0.79	3.17	0.634
Google	0.36	0.86	1.67	2.26	2.99	8.14	1.628
Facebook	0.56	1.21	1.73	2.18	3.17	8.85	1.77
AOL	0.51	0.47	0.53	0.70	0.73	2.94	0.588
Total	3.06	4.48	5.89	7.39	8.95	29.77	1.1908

Display Advertising Revenue

Nabila Hossain

October 11, 2015

Part 1:

Part 2:

Part 3: