Sept2020web.Rmd

---
title: "Climate Matters Monthly Numbers: September 2020"
author: "Lisa Sheridan"
date: "10/26/2020"
output: html_document
---

```{r eval=FALSE}
#load script with functions saved
source("monthlyreport_functions.R")

#read and format dataset
library('googlesheets4')

data <- read_sheet("https://docs.google.com/spreadsheets/d/1OdmkPrplKPKEQ7yKaRJg1ccODx05DehXftfPmWzQrWs/edit#gid=119899987", sheet=2)

#parsing data pulled from master sheet
tracking.data <-reformat_googlesheet(data)

# CHOOSE MONTH TO SUBSET
tracking.data.month <- tracking.data[which(tracking.data$month=="September" & tracking.data$year=="2020"),]
```

```{r setup}
source("monthlyreport_functions.R")
data <- read.csv('September2020trackingdata.csv')
tracking.data <-reformat_googlesheet(data)
tracking.data.month <- tracking.data[which(tracking.data$month=="September" & tracking.data$year=="2020"),]
```


### Hits by Program (Expanded View)
```{r, Hits by Type and Program, echo=FALSE}
#expanded view
library("tidyr")
expanded.hits.draft <- hitsbyprogram(tracking.data.month)
expanded.hits <- expanded.hits.draft[,c("source", "Twitter", "fb", "other",
                                  "online.article", "radio", "tv")]
names(expanded.hits) <- c("Program", "Twitter", "Facebook", "Other ",
                          "Online Articles", "Radio", "TV")
#restrict data to CC, CM, CMN sources
expanded.hits.CMprograms <- subset(expanded.hits, Program=="CC"|Program=="CM"|Program=="CMN"|Program=="Total")
library("formattable")
expanded.hits.formatted <- formattable(expanded.hits.CMprograms,
                                       align=c("l", "c", "c", "c", "c", "c", "c"),
                                       list(''))
expanded.hits.formatted
```

### (Condensed View)
```{r}
#condensed view
condensed.hits.draft <- expanded.hits.draft %>%
  as_tibble() %>%
  mutate(
    social=rt+tw+fb+other,
    stories=online.article+radio
  )
condensed.hits <- condensed.hits.draft[,c("source", "social", "stories", "tv")]
names(condensed.hits) <- c("Program", "Social Media", "Stories", "TV")

#restrict data to CC, CM, CMN sources
condensed.hits.CMprograms <- subset(condensed.hits, Program=="CC"|Program=="CM"|Program=="CMN"|Program=="Total")

#design into publishable table
library(formattable)
condensed.hits.formatted <- formattable(condensed.hits.CMprograms,align=c("l", "c", "c", "c"))

condensed.hits.formatted
```


```{r eval=FALSE}
### Most Popular Assets this Month
#What were the most popular releases this month?
top10.releases <- releases.popularity(tracking.data.month)

#What were the most popular graphics this month?
top10.graphics <- graphics.popularity(tracking.data.month)

top10.releases
top10.graphics
```

### Hits by Region
```{r}
#compute hits by regions
month.hitsbyregion <- hitsbyregion(tracking.data.month)

month.hitsbyregion
```

### Heat Map of TV Coverage
```{r}
library('usmap')
library('ggplot2')
tvheatmap.withAJFox <- tvheatmap.withAJFox(tracking.data.month)
tvheatmap.withoutAJFox <- tvheatmap.withoutAJFox(tracking.data.month)

tvheatmap.withAJFox
tvheatmap.withoutAJFox
```


### Year-to-Date TV Airings
```{r echo=FALSE}
  #subset 2020 data
  data2020 <-tracking.data[which(tracking.data$year=="2020"),]
  #replace NAs in tv column with 0s
  data2020$tv[is.na(data2020$tv)] <- 0
  #summarize tv by month
  library("dplyr")
  tvbymonth <- data2020 %>%
    group_by(month) %>%
    summarise(tv=sum(tv))
  #add 20% to tv
  tvbymonth$tv <- tvbymonth$tv*1.2
  #order data by month
  library("dplyr")
  tvbymonth$month <- factor(tvbymonth$month, levels=month.name)
  #c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
  library("tidyverse")
  tvbymonth$month <- sort(tvbymonth$month)
  #add column for cumulative sum of tv airings
  tvbymonth$cumulative.tv <- cumsum(tvbymonth$tv)
  tvbymonth <- as.data.frame(tvbymonth)
  #tv goals
  tv.goals <- c(500, 1000, 1500, 2000, 2500, 3000, 3500, 4000, 4500, 5000, 5500, 6000)
  month <- month.name
  tvgoals.bymonth <- data.frame(month, tv.goals)
  #merge hits and goals
  tvgoalsandhits <- merge(tvbymonth, tvgoals.bymonth, by="month", all.y = TRUE)
  #remove tv column
  tvgoalsandhits <- subset(tvgoalsandhits, select=-tv)
  #reorder columns
  tvgoalsandhits <- tvgoalsandhits[,c(1,3,2)]
  #melt dataframe
  library("reshape2")
  melted.tvgoalsandhits <- melt(tvgoalsandhits)
  #make variable a factor
  melted.tvgoalsandhits$variable <- factor(melted.tvgoalsandhits$variable, levels = c("tv.goals", "cumulative.tv"))
  melted.tvgoalsandhits$variable <- sort(melted.tvgoalsandhits$variable)
  #plot chart
  library("ggplot2")
  tvgoalschart <- ggplot(melted.tvgoalsandhits, aes(month, value, group=variable)) + 
    theme(axis.text.x = element_text(angle=45, vjust=0.7)) +
    geom_line(aes(color=variable)) +
    labs(y="TV Airings", x="") +
    scale_fill_discrete(name="", labels=c("Goals", "Cumulative Airings"))
  
  tvgoalschart
```