WMDE Thank You Campaign 2019/2020: Interim Report

Feedback should be send to goran.milovanovic_ext@wikimedia.de.

The campaign is run from 2020/01/01 to 2020/01/19.

CURRENT UPDATE: Complete dataset as of 2020/01/19; no data on training modules, banner expansion clicks, and banner closing quotes are available yet.

0. Data Acquisiton

NOTE: the Data Acquisition code chunk is not fully reproducible from this Report. The data are collected by running the script ThankYou_2020_PRODUCTION.R from the stat1004 WMF statistics server, collecting the data as .tsv and .csv files, aggregating on the server with ThankYou_2020_Aggregation.R locally, copying manually, and processing locally.

0.1 Daily Update

# !diagnostics off
### --- WMDE Thank You Campaign 2020 PRODUCTION
### --- Dec 23, 2019.
### --- run from: stat1004
### --- CAMPAIGN START: January 1, 2020.
### --- Daily reporting starts on January 2, 2020.

### --- libraries
library(tidyverse)
library(data.table)
library(lubridate)

### --- dir structure
campaignPath <- '/home/goransm/Analytics/NewEditors/Campaigns/2020_ThankYou/'
dataDir <- paste0(campaignPath, "_data/")
analyticsDir <- paste0(campaignPath, "_analytics/")

### --- determine cetDay
cetDay <- Sys.time()
cetDay
attr(cetDay, "tzone") <- "Europe/Berlin"
# - one day behind for crontab
# - (i.e. waiting for wmf.webrequest to complete is data acquisition)
cetDay <- ymd(
  strsplit(as.character(cetDay), 
           split = " ", 
           fixed = T)[[1]][1]
) - 1

### ----------------------------------------------------------
### --- Banner Impressions
### ----------------------------------------------------------

# - function: wmde_collect_banner_impressions
wmde_collect_banner_impressions <- function(uri_host, 
                                            uri_path, 
                                            uri_query, 
                                            cetDay,
                                            queryFile,
                                            fileName,
                                            dataDir) {
  
  # - NOTE:
  # - expected format for cetDay is: YYYY-MM-DD
  
  # - to dataDir
  setwd(dataDir)
  
  # - libraries
  library(stringr)
  
  # - WHERE condition: create datetime_condition
  cet_condition <- seq(
    from = as.POSIXct(paste0(cetDay," 0:00"), tz = "Europe/Berlin"),
    to = as.POSIXct(paste0(cetDay," 23:00"), tz = "Europe/Berlin"),
    by = "hour"
  ) 
  attr(cet_condition, "tzone") <- "UTC"
  cet_condition <- as.character(cet_condition)
  cet_condition <- unlist(str_extract_all(cet_condition, "^([[:digit:]]|\\s|-)*"))
  cet_years <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][1]
    })
  cet_months <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][2]
    })
  cet_months <- gsub("^0", "", cet_months)
  cet_days <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][3]
    })
  cet_days <- gsub("^0", "", cet_days)
  cet_hours <- sapply(strsplit(cet_condition, split = " ", fixed = T), 
                      function(x) {
                        x[2]
                      })
  cet_hours <- gsub("^0", "", cet_hours)
  datetimeCondition <- paste0(
    "year = ", cet_years, " AND ",
    "month = ", cet_months, " AND ",
    "day = ", cet_days, " AND ", 
    "hour = ", cet_hours
  )
  datetimeCondition <- paste("(", 
                             datetimeCondition, 
                             ")",
                             collapse = " OR ", 
                             sep = "")
  
  # - WHERE condition: create uri_path_condition
  if (length(uri_path) > 1) {
    uri_path_condition <- paste0("(",
                                 paste(
                                   paste0("uri_path = '", uri_path, "'"),
                                   collapse = " OR ", sep = " "),
                                 ")"
    )
  } else {
    uri_path_condition = paste0("uri_path = '", uri_path, "'")
  }
  
  # - WHERE condition: create uri_host_condition
  if (length(uri_host) > 1) {
    uri_host_condition <- paste0("(",
                                 paste(
                                   paste0("uri_host = '", uri_host, "'"),
                                   collapse = " OR ", sep = " "),
                                 ")"
    )
  } else {
    uri_host_condition = paste0("uri_host = '", uri_host, "'")
  }
  
  # - WHERE condition: create uri_query_condition
  if (length(uri_query) > 1) {
    uri_query_condition <- paste0("(",
                                  paste(
                                    paste0("uri_query LIKE '%", uri_query, "%'"),
                                    collapse = " OR ", sep = " "),
                                  ")"
    )
  } else {
    uri_query_condition = paste0("uri_query LIKE '%", uri_query, "%'")
  }
  
  # - compose HiveQL query
  hiveQuery <- paste0(
    "USE wmf;
    SELECT uri_query FROM webrequest
    WHERE (",
    uri_host_condition, " AND ",
    uri_path_condition, " AND ",
    uri_query_condition, " AND ",
    "(", datetimeCondition, ")",
    ");"
    )
  
  # - write hql
  write(hiveQuery, queryFile)
  # - execute hql script + Kerberos Auth
  hiveArgs <- 'sudo -u analytics-privatedata kerberos-run-command analytics-privatedata /usr/local/bin/beeline -f'
  hiveInput <- paste0(queryFile, ' > ', fileName)
  # - command:
  hiveCommand <- paste(hiveArgs, hiveInput)
  return(
    system(command = hiveCommand, wait = TRUE)
  )
}

# - set params to wmde_collect_banner_impressions
# - for the Thank You Campaign 2019/2020
uri_host <- c('de.wikipedia.org', 'de.m.wikipedia.org')
uri_path  <- '/beacon/impression'
uri_query <- c('WMDE_2019_2020_thx_')
queryFile <- 'thankyou2020_BannerImpressions.hql'
fileName <- paste0(dataDir, "bannerImpressions_", cetDay, ".tsv")

# - collect Banner Impression data
wmde_collect_banner_impressions(uri_host,
                                uri_path,
                                uri_query,
                                cetDay,
                                queryFile,
                                fileName,
                                dataDir)

# - function: wmde_process_banner_impressions
wmde_process_banner_impressions <- function(fileName,
                                            dataDir, 
                                            cetDay, 
                                            campaignName) {
  
  # - to dataDir
  setwd(dataDir)
  
  # - libraries
  library(stringr)
  library(dplyr)
  
  # - load
  bannerData <- read.delim(fileName, 
                           stringsAsFactors = F)
  colnames(bannerData) <- 'uri_query'
  
  # - clean
  wStart <- which(bannerData$uri_query == "uri_query")
  bannerData <- bannerData[(wStart + 1):(dim(bannerData)[1] - 2), ]
  
  # - split
  bannerData <- strsplit(bannerData, split = "&", fixed = T)
  # - extract relevant fields
  # - banner:
  banner <- sapply(bannerData, function(x) {
    x[which(grepl("^banner=", x))]
  })
  banner <- gsub("^banner=", "", banner)
  # - recordImpressionSampleRate:
  recordImpressionSampleRate <- sapply(bannerData, function(x) {
    x[which(grepl("^recordImpressionSampleRate=", x))]
  })
  recordImpressionSampleRate <- as.numeric(
    gsub("^recordImpressionSampleRate=", "", recordImpressionSampleRate)
  )
  # - result:
  result <- sapply(bannerData, function(x) {
    x[which(grepl("^result=", x))]
  })
  result <- gsub("^result=", "", result)
  
  # - compose table:
  bannerObservations <- data.frame(banner = banner, 
                                   recordImpressionSampleRate = recordImpressionSampleRate, 
                                   result = result, 
                                   stringsAsFactors = F)
  
  # - filter for result=show
  bannerObservations <- dplyr::filter(bannerObservations,
                                      result == "show")
  
  # - correction for recordImpressionSampleRate
  bannerObservations$recordImpressionSampleRate <- 
    1/bannerObservations$recordImpressionSampleRate
  
  # - aggregate:
  bannerObservations <- bannerObservations %>% 
    dplyr::select(banner, recordImpressionSampleRate) %>% 
    dplyr::group_by(banner) %>% 
    dplyr::summarise(impressions = sum(recordImpressionSampleRate))
  
  # - add cetDay, me
  bannerObservations$date <- cetDay
  bannerObservations$campaign <- campaignName
  
  # - store:
  write.csv(bannerObservations, 
            paste0("bannerImpressionsAggregated_",
                   cetDay,
                   ".csv"
            )
  )
  
}

# - wrangle Banner Impression data
campaignName <- "2020_ThankYou"
wmde_process_banner_impressions(fileName = fileName, 
                                dataDir = dataDir, 
                                cetDay = cetDay,
                                campaignName = campaignName)

### ----------------------------------------------------------
### --- Banner Expansion Clicks + Banner Closing Quote
### --- Schema:WMDEBannerEvents
### --- https://meta.wikimedia.org/wiki/Schema:WMDEBannerEvents
### ----------------------------------------------------------
# - Schema:WMDEBannerEvents is empty

### ----------------------------------------------------------
### --- Pageviews
### ----------------------------------------------------------

# - function: wmde_collect_pageviews
wmde_collect_pageviews <- function(uri_host,
                                   uri_path,
                                   cetDay,
                                   queryFile,
                                   fileName,
                                   dataDir) {
  
  # - NOTE:
  # - expected format for cetDay is: YYYY-MM-DD
  
  # - to dataDir
  setwd(dataDir)
  
  # - libraries
  library(stringr)
  
  # - WHERE condition: create datetime_condition
  cet_condition <- seq(
    from = as.POSIXct(paste0(cetDay," 0:00"), tz = "Europe/Berlin"),
    to = as.POSIXct(paste0(cetDay," 23:00"), tz = "Europe/Berlin"),
    by = "hour"
  ) 
  attr(cet_condition, "tzone") <- "UTC"
  cet_condition <- as.character(cet_condition)
  cet_condition <- unlist(str_extract_all(cet_condition, "^([[:digit:]]|\\s|-)*"))
  cet_years <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][1]
    })
  cet_months <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][2]
    })
  cet_months <- gsub("^0", "", cet_months)
  cet_days <- sapply(
    strsplit(cet_condition, split = " ", fixed = T), function(x) {
      strsplit(x, split = "-")[[1]][3]
    })
  cet_days <- gsub("^0", "", cet_days)
  cet_hours <- sapply(strsplit(cet_condition, split = " ", fixed = T), 
                      function(x) {
                        x[2]
                      })
  cet_hours <- gsub("^0", "", cet_hours)
  datetimeCondition <- paste0(
    "year = ", cet_years, " AND ",
    "month = ", cet_months, " AND ",
    "day = ", cet_days, " AND ", 
    "hour = ", cet_hours
  )
  datetimeCondition <- paste("(", 
                             datetimeCondition, 
                             ")",
                             collapse = " OR ", 
                             sep = "")
  
  # - WHERE condition: create uri_path_condition
  if (length(uri_path) > 1) {
    uri_path_condition <- paste0("(",
                                 paste(
                                   paste0("uri_path = '", uri_path, "'"),
                                   collapse = " OR ", sep = " "),
                                 ")"
    )
  } else {
    uri_path_condition = paste0("uri_path = '", uri_path, "'")
  }
  
  # - WHERE condition: create uri_host_condition
  if (length(uri_host) > 1) {
    uri_host_condition <- paste0("(",
                                 paste(
                                   paste0("uri_host = '", uri_host, "'"),
                                   collapse = " OR ", sep = " "),
                                 ")"
    )
  } else {
    uri_host_condition = paste0("uri_host = '", uri_host, "'")
  }
  
  # - compose HiveQL query
  hiveQuery <- paste0(
    "USE wmf;
    SELECT uri_path, uri_query, referer FROM webrequest
    WHERE (",
    uri_host_condition, " AND ",
    uri_path_condition, " AND ",
    "(", datetimeCondition, ")",
    ");"
    )
  
  # - write hql
  write(hiveQuery, queryFile)
  # - execute hql script:
  hiveArgs <- 'sudo -u analytics-privatedata kerberos-run-command analytics-privatedata /usr/local/bin/beeline -f'
  hiveInput <- paste0(queryFile, ' > ', fileName)
  # - command:
  hiveCommand <- paste(hiveArgs, hiveInput)
  return(
    system(command = hiveCommand, wait = TRUE)
  )
}

# - set params to wmde_collect_pageviews
# - for the Autumn Banner Campaign 2018
uri_host <- c('de.wikipedia.org', 'de.m.wikipedia.org')
uri_path  <- c('/wiki/Wikipedia:Wikimedia_Deutschland/LerneWikipedia')

# - set params for wmde_collect_pageviews
queryFile <- 'thankyou2020_Pageviews.hql'
fileName <- paste0("pageviews_", cetDay, ".tsv")

# - collect Pageviews data
wmde_collect_pageviews(uri_host,
                       uri_path,
                       cetDay,
                       queryFile,
                       fileName,
                       dataDir)

### --- Wrangle Pageviews
# - function: wmde_process_pageviews
wmde_process_pageviews <- function(fileName,
                                   dataDir, 
                                   uri_query_filter, 
                                   cetDay = cetDay,
                                   campaignName = campaignName) {
  
  # - to dataDir
  setwd(dataDir)
  
  # - libraries
  library(stringr)
  library(dplyr)
  library(tidyr)
  library(data.table)
  
  # - load
  pageviewsData <- readLines(fileName)
  wStart <- which(grepl("^uri_path", pageviewsData))
  pageviewsData <- pageviewsData[(wStart + 2):(length(pageviewsData) - 2)]
  pageviewsData <- data.frame(dat = pageviewsData, 
                              stringsAsFactors = F)
  pageviewsData <- separate(pageviewsData,
                            dat,
                            into = c('uri_path', 'uri_query', 'referer'),
                            sep = "\t")
  # - apply uri_query_filter
  # - NOTE: Autumn 2018, looking in both: uri_query, referer
  w_uri_query <- which(grepl(uri_query_filter, pageviewsData$uri_query))
  w_uri_query_referer <- which(grepl(uri_query_filter, pageviewsData$referer))
  w_uri_query <- unique(c(w_uri_query, w_uri_query_referer))
  pageviewsData <- pageviewsData[w_uri_query, ]
  w_uri_query_referer <- which(grepl(uri_query_filter, pageviewsData$referer))
  w_uri_query_referer_delete <- setdiff(1:dim(pageviewsData)[1], w_uri_query_referer)
  pageviewsData$referer[w_uri_query_referer_delete] <- ''
  # - when there is no uri_query, use the query from the referer field if present there
  pageviewsData$referer <- str_extract(pageviewsData$referer, "\\?campaign=.*$")
  pageviewsData$referer[is.na(pageviewsData$referer)] <- ""
  pageviewsData$referer <- gsub("?campaign=", "", pageviewsData$referer, fixed = T)
  pageviewsData$uri_query <- gsub("?campaign=", "", pageviewsData$uri_query, fixed = T)
  pageviewsData$uri_query[pageviewsData$uri_query == ""] <- 
    pageviewsData$referer[pageviewsData$uri_query == ""]
  pageviewsData <- dplyr::filter(pageviewsData, 
                                 uri_query != "")
  pageviewsData$referer <- NULL
  # - clean up a bit:
  pageviewsData$uri_query <- gsub("/.*$", "", pageviewsData$uri_query)
  
  # - aggregate:
  pageviewsData <- pageviewsData %>% 
    dplyr::group_by(uri_query, uri_path) %>% 
    dplyr::summarise(pageviews = n())
  colnames(pageviewsData) <- c('Tag', 'Page', 'Pageviews')
  
  # - add cetDay, campaignName
  pageviewsData$date <- cetDay
  pageviewsData$campaign <- campaignName
  
  # - store:
  write.csv(pageviewsData, 
            paste0("pageviewsAggregated_",
                   strsplit(
                     strsplit(fileName, split = "_", fixed = T)[[1]][2],
                     split = ".", 
                     fixed = T)[[1]][1],
                   ".csv"
            )
  )
  
}

# - set params to wmde_process_pageviews
# - for the Autumn Banner Campaign 2018
uri_query_filter <- 'WMDE_2019_2020_thx_'

# - wrangle pageviews
wmde_process_pageviews(fileName = fileName,
                       dataDir = dataDir,
                       uri_query_filter = uri_query_filter, 
                       cetDay = cetDay,
                       campaignName = campaignName) 

### ----------------------------------------------------------
### --- User Registrations
### --- HiveQL query: event.ServerSideAccountCreation table
### ----------------------------------------------------------
hiveQL <- 
  "SELECT year, month, day, hour,  event.campaign, event.userId, event.userName 
    FROM event.serversideaccountcreation 
    WHERE 
      year = 2020
      AND month = 1 
      AND day >= 1
      AND (event.campaign LIKE '%WMDE_2019_2020_thx_%');"
# - write hql
write(hiveQL, paste0(dataDir, 'user_registrations.hql'))
### --- output filename
filename <- paste0(dataDir, 'user_registrations.tsv')
### --- execute hql script:
hiveArgs <- 'sudo -u analytics-privatedata kerberos-run-command analytics-privatedata /usr/local/bin/beeline --silent --incremental=true --verbose=false -f'
hiveInput <- paste(paste0(dataDir, 'user_registrations.hql'),
                   " > ",
                   filename,
                   sep = "")
# - command:
hiveCommand <- paste(hiveArgs, hiveInput)
system(command = hiveCommand, wait = TRUE)

0.2 Data Aggregation

NOTE: Not run from this report; the data were already pre-processed and aggregated by the following R script before being submitted to analytical procedures:

# !diagnostics off
### --- Report Generation for the Thank You 2019/2020 Campaign
### --- run locally

### --- dir structure
campaignPath <- '/home/goransm/Work/___DataKolektiv/Projects/WikimediaDEU/_WMDE_Projects/_misc/NewEditors_Team/2020_ThankYou/'
dataDir <- paste0(campaignPath, "_data/")
analyticsDir <- paste0(campaignPath, "_analytics/")

### --- Report Banner Impression Data

# - function: wmde_report_banner_impressions
wmde_report_banner_impressions <- function(dataDir) {
  
  # - Setup
  library(data.table)
  library(dplyr)
  
  # - list files:
  lF <- list.files(dataDir)
  
  # - filter aggregated banner impression data
  lF <- lF[grepl("bannerImpressionsAggregated_", lF, fixed = T)]
  
  # - load files and merge
  bannerData <- vector(mode = "list", length = length(lF))
  for (i in 1:length(lF)) {
    if (grepl("csv$|tsv$", lF[i])) {
      bannerData[[i]] <- fread(paste0(dataDir, lF[i]))
    } else {
      bannerData[[i]] <- NULL
    }
  }
  bannerData <- rbindlist(bannerData)
  bannerData$V1 <- NULL
  
  # - aggregates
  perBannerTotals <- bannerData %>% 
    select(banner, impressions) %>% 
    group_by(banner) %>% 
    summarise(totalImpressions = sum(impressions))
  perDayTotals <- bannerData %>% 
    select(date, impressions) %>% 
    group_by(date) %>% 
    summarise(totalImpressions = sum(impressions))
  
  # - output
  return(
    list(bannerImpressionsReport = bannerData, 
         perBannerTotals = perBannerTotals, 
         perDayTotals = perDayTotals)
  )
  
}

# - Report banner impressions
bannerImpressionsData <- wmde_report_banner_impressions(dataDir)
bannerImpressionsFile <- bannerImpressionsData$bannerImpressionsReport
write.csv(bannerImpressionsFile, paste0(analyticsDir, "bannerImpressionsFile.csv"))
bannerTotals <- bannerImpressionsData$perBannerTotals
write.csv(bannerTotals, paste0(analyticsDir, "bannerTotals.csv"))
bannerDayTotals <- bannerImpressionsData$perDayTotals
write.csv(bannerDayTotals, paste0(analyticsDir, "bannerDayTotals.csv"))


### --- Report Pageviews Data

# - function: wmde_report_pageviews
wmde_report_pageviews <- function(dataDir) {
  
  # - Setup
  library(data.table)
  library(dplyr)
  
  # - list files:
  lF <- list.files(dataDir)
  
  # - filter aggregated banner impression data
  lF <- lF[grepl("pageviewsAggregated_", lF, fixed = T)]
  
  # - load files and merge
  pageviewsData <- vector(mode = "list", length = length(lF))
  for (i in 1:length(lF)) {
    if (grepl("csv$|tsv$", lF[i])) {
      pageviewsData[[i]] <- fread(paste0(dataDir, lF[i]))
    } else {
      pageviewsData[[i]] <- NULL
    }
  }
  pageviewsData <- rbindlist(pageviewsData)
  pageviewsData$V1 <- NULL
  
  # - aggregates
  perDayTotals <- pageviewsData %>% 
    select(date, Pageviews) %>% 
    group_by(date) %>% 
    summarise(totalPageviews = sum(Pageviews))
  perTagTotals <- pageviewsData %>% 
    select(Tag, Pageviews) %>% 
    group_by(Tag) %>% 
    summarise(totalPageviews = sum(Pageviews))
  perPageTotals <- pageviewsData %>% 
    select(Page, Pageviews) %>% 
    group_by(Page) %>% 
    summarise(totalPageviews = sum(Pageviews))
  perPageDayTotals <- pageviewsData %>% 
    select(Page, date, Pageviews) %>%
    group_by(Page, date) %>% 
    summarise(totalPageviews = sum(Pageviews))
  perTagDayTotals <- pageviewsData %>% 
    select(Tag, date, Pageviews) %>%
    group_by(Tag, date) %>% 
    summarise(totalPageviews = sum(Pageviews))
  perTagPageTotals <- pageviewsData %>% 
    select(Tag, Page, Pageviews) %>%
    group_by(Tag, Page) %>% 
    summarise(totalPageviews = sum(Pageviews))
  
  # - output
  return(
    list(pageviewsDataReport = pageviewsData, 
         perDayTotals = perDayTotals, 
         perTagTotals = perTagTotals, 
         perPageTotals = perPageTotals, 
         perPageDayTotals = perPageDayTotals, 
         perTagDayTotals = perTagDayTotals,
         perTagPageTotals = perTagPageTotals)
  )
  
}

# - Report pageviews:
pageviewsData <- wmde_report_pageviews(dataDir)
pageviewsReportFile <- pageviewsData$pageviewsDataReport
write.csv(pageviewsReportFile, paste0(analyticsDir, "pageviewsReportFile.csv"))
pageviews_perDayTotals <- pageviewsData$perDayTotals
write.csv(pageviews_perDayTotals, paste0(analyticsDir, "pageviews_perDayTotals.csv"))
pageviews_perTagTotals <- pageviewsData$perTagTotals
write.csv(pageviews_perTagTotals, paste0(analyticsDir, "pageviews_perTagTotals.csv"))
pageviews_perPageTotals <- pageviewsData$perPageTotals
write.csv(pageviews_perPageTotals, paste0(analyticsDir, "pageviews_perPageTotals.csv"))
pageviews_perPageDayTotals <- pageviewsData$perPageDayTotals
write.csv(pageviews_perPageDayTotals, paste0(analyticsDir, "pageviews_perPageDayTotals.csv"))
pageviews_perTagDayTotals <- pageviewsData$perTagDayTotals
write.csv(pageviews_perTagDayTotals, paste0(analyticsDir, "pageviews_perTagDayTotals.csv"))
pageviews_perTagPageTotals <- pageviewsData$perTagPageTotals
write.csv(pageviews_perTagPageTotals, paste0(analyticsDir, "perTagPageTotals.csv"))

### --- Report User Registrations

# - function: wmde_report_registrations_hive
wmde_report_registrations_hive <- function(dataDir, campaign) {
  
  # - Setup
  library(data.table)
  library(dplyr)
  
  # - list files:
  lF <- list.files(dataDir)
  
  # - filter aggregated user registration data
  lF <- lF[grepl("user_registrations.tsv", lF, fixed = T)]
  
  # - load file and wrangle columns
  registrationData <- fread(paste0(dataDir, lF), header = T)
  colnames(registrationData)[5] <- 'event_campaign'
  colnames(registrationData)[6] <- 'event_userId'
  registrationData$campaign <- campaign
  registrationData$day <- ifelse(nchar(registrationData$day) == 1,
                                 paste0("0", registrationData$day), 
                                 registrationData$day)
  registrationData$date <- paste(registrationData$year, 
                                 registrationData$month,
                                 registrationData$day,
                                 sep = "-")

  # - aggregates
  perDayTotals <- registrationData %>% 
    select(date) %>% 
    group_by(date) %>% 
    summarise(totalRegistrations = n())
  perTagTotals <- registrationData %>% 
    select(event_campaign) %>% 
    group_by(event_campaign) %>% 
    summarise(totalRegistrations = n())
  perTagDayTotals <- registrationData %>% 
    select(event_campaign, date) %>% 
    group_by(event_campaign, date) %>% 
    summarise(totalRegistrations = n())
  
  # - full registration dataset: non-aggregated registrations
  # - filter data:
  fullRegData <- registrationData %>% 
    select(event_userId, 
           event_campaign, 
           date, 
           campaign)
  
  # - output
  return(
    list(registrationsDataReport = registrationData, 
         perDayTotals = perDayTotals, 
         perTagTotals = perTagTotals, 
         perTagDayTotals = perTagDayTotals,
         fullRegistrationDataset = fullRegData)
  )
  
}

# - Report upon user registrations
campaign <- '2020_ThankYou'
userRegData <- wmde_report_registrations_hive(dataDir, campaign)
userRegistrationsReportFile <- userRegData$registrationsDataReport
write.csv(userRegistrationsReportFile, paste0(analyticsDir, "userRegistrationsReportFile.csv"))
userRegistrations_perDayTotals <- userRegData$perDayTotals
write.csv(userRegistrations_perDayTotals, paste0(analyticsDir, "userRegistrations_perDayTotals.csv"))
userRegistrations_perTagTotals <- userRegData$perTagTotals
write.csv(userRegistrations_perTagTotals, paste0(analyticsDir, "userRegistrations_perTagTotals.csv"))
userRegistrations_perTagDayTotals <- userRegData$perTagDayTotals
write.csv(userRegistrations_perTagDayTotals, paste0(analyticsDir, "userRegistrations_perTagDayTotals.csv"))
fullUserRegistrations <- userRegData$fullRegistrationDataset
write.csv(fullUserRegistrations, paste0(analyticsDir, "fullRegistrationDataset.csv"))

0.3 Banner Actions Data Set

NOTE: Not run from this report; the data were already pre-processed and aggregated by the following R script before being submitted to analytical procedures:

# !diagnostics off
### --- Report Generation for the Thank You 2019/2020 Campaign
### --- run locally

### --- dir structure
campaignPath <- '/home/goransm/Analytics/NewEditors/Campaigns/2020_ThankYou/'
dataDir <- paste0(campaignPath, "_data/")
analyticsDir <- paste0(campaignPath, "_analytics/")
# - compose HiveQL script
hiveQL <-
  "USE event; 
  SELECT year, month, day, event.bannerName as banner, event.bannerAction as action, COUNT(*) as count 
  FROM wmdebannerevents 
  WHERE year = 2020 AND month = 1 AND 
    (event.bannerName LIKE \"WMDE%\" OR event.bannerName LIKE \"WPDE%\") 
  GROUP BY year, month, day, event.bannerName, event.bannerAction;"
# - write hql
write(hiveQL, paste0(dataDir, 'banner_actions.hql'))
### --- execute hql script:
filename <- paste0(dataDir, 'banner_actions_WMDE_ThankYou2020.tsv')
hiveArgs <- 'sudo -u analytics-privatedata kerberos-run-command analytics-privatedata /usr/local/bin/beeline --silent --incremental=true --verbose=false -f'
hiveInput <- paste(paste0(dataDir, 'banner_actions.hql'),
                   " > ",
                   filename,
                   sep = "")
# - command:
hiveCommand <- paste(hiveArgs, hiveInput)
system(command = hiveCommand, wait = TRUE)

0.4 User Edits

0.4.1 User Edits via revision_actor_temp

library(data.table)
# !diagnostics off
### --- script: ThankYou2020_UserEdits.R
### --- User Edits Acquisition for the WMDE Thank You Campaign 2019/2020
### --- run from stat1004
### -----------------------------------------------------------------------
### --- User Edits
### -----------------------------------------------------------------------
### --- dir structure
campaignPath <- '/home/goransm/Analytics/NewEditors/Campaigns/2020_ThankYou/'
dataDir <- paste0(campaignPath, "_data/")
analyticsDir <- paste0(campaignPath, "_analytics/")
startTimestamp <- '20200101000000'
# - get user ids
userRegistrations <- read.csv(paste0(analyticsDir, 
                                     'userRegistrationsReportFile.csv'))
rev_user <- userRegistrations$event_userId
rev_user_text <- as.character(userRegistrations$username)
# - iterate over rev_user
for (i in 1:length(rev_user)) {
  # - SQL query
  sqlQuery <- paste("\"SELECT actor.actor_id, actor.actor_user, actor.actor_name, revision_actor_temp.revactor_timestamp FROM actor LEFT JOIN revision_actor_temp ON (actor.actor_id = revision_actor_temp.revactor_actor) WHERE (revision_actor_temp.revactor_timestamp >= 20191101000000 AND actor.actor_user = ", rev_user[i], ");\"");
  ### --- output filename
  filename <- paste(dataDir,'userEdits', "_", i, ".tsv", sep = "")
  ### --- execute sql script:
  sqlLogInPre <- paste0('/usr/local/bin/analytics-mysql dewiki -e ')
  sqlInput <- paste(sqlQuery,
                    " > ",
                    filename,
                    sep = "")
  # - command:
  sqlCommand <- paste(sqlLogInPre, sqlInput)
  system(command = sqlCommand, wait = TRUE)
  # - report
  print(paste0("DONE: user ", i, "."))
}
### --- END run SQL scripts
# - load user edits:
lF <- list.files(dataDir)
lF <- lF[grepl("^userEdits_", lF)]
userEdits <- lapply(paste0(dataDir, lF), fread)
userEdits <- rbindlist(userEdits)
# - store user edits:
write.csv(userEdits, paste0(analyticsDir, 'userEdits.csv'))

1. Campaign Banners and Pages

This section presents all data and statistics on the campaign banners and pages.

1.1 Banner Impressions

1.1.1 Banner Impressions Overview

Chart 1.1.1 Daily Banner Impressions

dataSet <- read.csv(
  '_analytics/bannerImpressionsFile.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
dataSet$platform <- sapply(dataSet$banner, function(x) {
  if (grepl("dskt", x)) {
    return('dskt')
  } else if (grepl("mob", x)) {
    return('mob')
  } else if (grepl("ipad", x)) {
    return('ipad')
  } else {
    return(NA)
  }
})
dataSet$banner <- gsub("WMDE_2019_2020_thx_", "", dataSet$banner)  
dataSet$banner <- gsub("dskt|mob|ipad", "", dataSet$banner)  
# - Visualize w. {ggplot2}
ggplot(dataSet, aes(x = date,
                    y = impressions,
                    group = banner,
                    color = banner,
                    fill = banner,
                    label = impressions,
                    )) + 
  geom_path(size = .5) + 
  geom_point(size = 1.5) +
  scale_y_continuous(labels = comma) +
  geom_text_repel(size = 6, show.legend = FALSE) + 
  facet_wrap(~platform, nrow = length(unique(dataSet$platform))) + 
  ggtitle('Thank You Campaign 2019/2020: Banner Impressions') +
  theme_minimal() + 
  theme(axis.text.x = element_text(angle = 90, size = 18)) +
  theme(axis.text.y = element_text(size = 18)) +
  theme(axis.title.x = element_text(size = 18)) +
  theme(axis.title.y = element_text(size = 18)) +
  theme(plot.title = element_text(size = 20)) + 
  theme(strip.text.x = element_text(size = 18)) + 
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 18)) + 
  theme(legend.position = "right")

1.1.1 Banner Impressions Overview: Table

Table 1.1.1. Daily Banner Impressions

### --- Full Dataset (Table Report)
dataSet <- dplyr::select(dataSet,
                         platform, banner, date, impressions)
datatable(dataSet)

1.1.2 Total Banner Impressions

Chart 1.1.2. Total Banner Impressions

dataSet <- read.csv(
  '_analytics/bannerTotals.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
dataSet$banner <- gsub("WMDE_2019_2020_thx_", "", dataSet$banner)
ggplot(dataSet, aes(x = banner, 
                    y = totalImpressions, 
                    color = banner, 
                    fill = banner, 
                    label = totalImpressions)) + 
  geom_bar(width = .5, stat = "identity") + 
  scale_y_continuous(labels = comma) +
  ggtitle('Thank You Campaign 2019/2020: Banner Impressions') +
  theme_minimal() + 
  geom_label_repel(size = 3.5, color = "white", show.legend = FALSE) + 
  scale_y_continuous(labels = comma) +
  theme(axis.text.x = element_text(angle = 90, size = 8)) +
  theme(plot.title = element_text(size = 10)) +
  theme(legend.title = element_blank()) + 
  theme(legend.position = "right") + 
  theme(axis.text.x = element_blank())

1.1.3 Banner Impressions per Day

Chart 1.1.3. Banner Impressions per Day

dataSet <- read.csv(
  '_analytics/bannerDayTotals.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
dataSet$year <- substr(dataSet$date, 1, 4)
dataSet$month <- substr(dataSet$date, 6, 7)
dataSet$day <- substr(dataSet$date, 9, 10)
dataSet$day <- ifelse(nchar(dataSet$day) == 1, 
                      paste0("0", dataSet$day),
                      dataSet$day)
dataSet$date <- paste(dataSet$year, dataSet$month, dataSet$day, sep = "-")
ggplot(dataSet, aes(x = date, 
                    y = totalImpressions, 
                    label = totalImpressions)) +
  geom_line(size = .25, group = 1, color = "darkblue") +
  geom_point(size = 1.5, color = "darkblue") + 
  geom_point(size = 1, color = "white") + 
  scale_y_continuous(labels = comma) +
  ggtitle('Thank You Campaign 2019/2020: Banner Impressions') +
  theme_minimal() + 
  geom_label_repel(size = 3.5, show.legend = FALSE) + 
  scale_y_continuous(labels = comma) +
  theme(axis.text.x = element_text(angle = 90, size = 13)) +
  theme(axis.text.y = element_text(size = 13)) +
  theme(axis.title.x = element_text(size = 13)) +
  theme(axis.title.y = element_text(size = 13)) +
  theme(plot.title = element_text(size = 15)) + 
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 13)) + 
  theme(legend.position = "right")

1.1.4 Banner Actions: full data set

bannerImpressions <- read.csv(
  '_analytics/bannerImpressionsFile.csv',
  header = T,
  row.names = 1,
  check.names = F,
  stringsAsFactors = F)
bannerActions <- read.table(
  '_analytics/banner_actions_WMDE_ThankYou2020.tsv',
  sep = "\t",
  header = T,
  check.names = F,
  stringsAsFactors = F)
bannerActions$banner <- gsub("WPDE", "WMDE", bannerActions$banner, fixed = T)
bannerActions$month <- ifelse(nchar(bannerActions$month) == 1,
                              paste0("0", bannerActions$month), 
                              bannerActions$month)
# bannerActions$day <- ifelse(nchar(bannerActions$day) == 1,
#                               paste0("0", bannerActions$day), 
#                               bannerActions$day)
bannerActions$date <- paste(bannerActions$year, bannerActions$month, bannerActions$day, 
                            sep = "-")
bannerActions <- dplyr::select(bannerActions, 
                               banner, action, date, count)
dataSet <- dplyr::left_join(bannerActions, 
                            dplyr::select(bannerImpressions, 
                                          banner, date, impressions),
                            by = c("banner" = "banner", "date" = "date")
)
dataSet <- dplyr::arrange(dataSet, date, banner)
dataSet$platform <- sapply(dataSet$banner, function(x) {
  if (grepl("dskt", x)) {
    return('dskt')
  } else if (grepl("mob", x)) {
    return('mob')
  } else if (grepl("ipad", x)) {
    return('ipad')
  } else {
    return(NA)
  }
})
dataSet$banner <- gsub("WMDE_2019_2020_thx_|WPDE_2019_2020_thx_", "", dataSet$banner)  
dataSet$banner <- gsub("dskt|mob|ipad", "", dataSet$banner)
dataSet <- dplyr::filter(dataSet,
                         !(dataSet$date %in% c("2020-01-20",
                                               "2020-01-21", 
                                               "2020-01-22", 
                                               "2020-01-23")
                           ))
dataSet$year <- substr(dataSet$date, 1, 4)
dataSet$month <- substr(dataSet$date, 6, 7)
dataSet$day <- substr(dataSet$date, 9, 10)
dataSet$day <- ifelse(nchar(dataSet$day) == 1, 
                      paste0("0", dataSet$day),
                      dataSet$day)
dataSet$date <- paste(dataSet$year, dataSet$month, dataSet$day, sep = "-")
dataSet <- dataSet[, c('platform', 'banner', 'action', 'count', 'impressions', 'date')]
datatable(dataSet)

1.1.4 Banner Actions: mini-banner-expanded

The charts are organized by platform (dskt, mob, or ipad) and _ctrl vs. _var banners. The vertical axes represent % of banner actions relative to the number of banner impressions served from that banner. The data points are labeled by exact counts of banner actions.

pFrame <- dplyr::filter(dataSet,
                        grepl("expanded", dataSet$action))
pFrame$action <- NULL
pFrame$percent <- round(pFrame$count/pFrame$impressions, 2) * 100
pFrame$impressions <- NULL
# - Visualize w. {ggplot2}
ggplot(pFrame, aes(x = date,
                   y = percent,
                   color = banner,
                   fill = banner,
                   label = count,
                    )) + 
  geom_line(size = .25, group = 1) + 
  geom_point(size = 1.5) +
  geom_point(size = 1, color = "white") +
  scale_y_continuous(labels = comma) +
  ggtitle('Thank You Campaign 2019/2020: Banner Expand') +
  facet_wrap(~platform + banner, ncol = 2) +
  theme_minimal() + 
  geom_text_repel(size = 6, show.legend = FALSE) + 
  theme(axis.text.x = element_text(angle = 90, size = 13)) +
  theme(axis.text.y = element_text(size = 13)) +
  theme(axis.title.x = element_text(size = 13)) +
  theme(axis.title.y = element_text(size = 13)) +
  theme(plot.title = element_text(size = 18)) + 
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 13)) + 
  theme(legend.position = "top")

1.1.5 Banner Actions: banner-closed

The charts are organized by platform (dskt, mob, or ipad) and _ctrl vs. _var banners. The vertical axes represent % of banner actions relative to the number of banner impressions served from that banner. The data points are labeled by exact counts of banner actions.

pFrame <- dplyr::filter(dataSet,
                        grepl("closed", dataSet$action))
pFrame$action <- NULL
pFrame$percent <- round(pFrame$count/pFrame$impressions, 2) * 100
pFrame$impressions <- NULL
# - Visualize w. {ggplot2}
ggplot(pFrame, aes(x = date,
                   y = percent,
                   color = banner,
                   fill = banner,
                   label = count,
                    )) + 
  geom_line(size = .25, group = 1) + 
  geom_point(size = 1.5) +
  geom_point(size = 1, color = "white") +
  scale_y_continuous(labels = comma) +
  ggtitle('Thank You Campaign 2019/2020: Banner Close') +
  facet_wrap(~platform + banner, ncol = 2) +
  theme_minimal() + 
  geom_text_repel(size = 6, show.legend = FALSE) + 
  theme(axis.text.x = element_text(angle = 90, size = 13)) +
  theme(axis.text.y = element_text(size = 13)) +
  theme(axis.title.x = element_text(size = 13)) +
  theme(axis.title.y = element_text(size = 13)) +
  theme(plot.title = element_text(size = 18)) + 
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 13)) + 
  theme(legend.position = "top")

1.2 Pageviews

1.2.1 Pageviews Overview

Chart 1.2.1. Pageviews Overview.

1.2.1 Pageviews Overview: Table

Table 1.2.1. Pageviews Overview

### --- Full Dataset (Table Report)
datatable(dataSet %>% arrange(desc(totalPageviews)))

1.2.3 Pageviews Overview: totals per Tag/Page

Chart 1.2.3. Pageviews Overview: totals per Tag/Page

1.2.4 Pageviews per Tag, daily totals

Chart 1.2.4. Pageviews per Tag, daily totals. The numbers reported in the data point labels are exact. The erroneous WPDE tag (see: Phab T240351) is marked separately.

dataSet <- read.csv(
  '_analytics/pageviews_perTagDayTotals.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
dataSet <- filter(dataSet, 
                  !(grepl("?&piwik_", dataSet$Tag)))
dataSet$Tag <- gsub("\\?&campaign=WMDE_2019_2020_", "", dataSet$Tag)
dataSet$Tag <- gsub("\\?&campaign=WPDE_2019_2020_", "WPDE_", dataSet$Tag)
dataSet <- dataSet %>% 
  group_by(Tag, date) %>% 
  summarise(totalPageviews = sum(totalPageviews))
dataSet$platform <- sapply(dataSet$Tag, function(x) {
  if (grepl("dskt", x)) {
    return('dskt')
  } else if (grepl("mob", x)) {
    return('mob')
  } else if (grepl("ipad", x)) {
    return('ipad')
  } else {
    return(NA)
  }
})
ggplot(dataSet, aes(x = date,
                    y = totalPageviews,
                    group = Tag,
                    color = Tag,
                    fill = Tag,
                    label = totalPageviews,
                    )) + 
  geom_path(size = .25) + 
  geom_point(size = 1.5) +
  geom_point(size = 1, color = "white") +
  scale_y_continuous(labels = comma) +
  geom_text_repel(size = 5, show.legend = FALSE) +
  facet_wrap(~platform, ncol = 1) +
  ggtitle('Thank You Campaign 2019/2020: Pageviews per Tag, daily totals') +
  theme_minimal() + 
  theme(axis.text.x = element_text(angle = 90, size = 18)) +
  theme(axis.text.y = element_text(size = 18)) +
  theme(axis.title.x = element_text(size = 18)) +
  theme(axis.title.y = element_text(size = 18)) +
  theme(plot.title = element_text(size = 20)) + 
  theme(strip.text.x = element_text(size = 18)) + 
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 18)) + 
  theme(legend.position = "right")

2. User Registrations

All data on user registrations are presented in this section.

2.1 Registrations per tag and day

Chart 2.1. Registrations per tag and day. Please note: points with no data labels signify 0 user registrations.

### --- Full Dataset (Table Report)
datatable(dplyr::arrange(dataSet, event_campaign, date, desc(Registrations)))

2.2 Total registrations per tag

Chart 2.2. Total registrations per tag.

dataSet <- dataSet %>% 
  group_by(event_campaign) %>% 
  summarise(totalRegistrations = sum(Registrations))
ggplot(dataSet, aes(x = event_campaign, 
                    y = totalRegistrations, 
                    color = event_campaign,
                    fill = event_campaign,
                    label = totalRegistrations)) + 
  geom_bar(width = .5, stat = "identity") + 
  scale_y_continuous(labels = comma) +
  ggtitle('Thank You Campaign 2019/2020: Total Registrations per Tag') +
  theme_minimal() + 
  geom_label_repel(size = 3.5, color = "white", show.legend = FALSE) + 
  scale_y_continuous(labels = comma) + 
  ylab("Registrations") +
  theme(axis.text.x = element_text(angle = 90, size = 8)) +
  theme(plot.title = element_text(size = 10)) +
  theme(legend.title = element_blank()) +
  theme(legend.text=element_text(size = 14)) + 
  theme(legend.position = "right")

3. User Edits

All data on user edits are presented in this section.

3.1 User edits: distribution

userEdits <- read.csv(
  '_analytics/userEdits.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
userEdits$revactor_timestamp <- as.character(userEdits$revactor_timestamp)
dataSet <- userEdits %>%
  dplyr::select(actor_user) %>%
  dplyr::group_by(actor_user) %>% 
  dplyr::summarise(edits = n())
# - Edit | 1 | 2-4 | 5-9 | 10-49 | >50
editBoundaries <- list(
  c(0, 1), 
  c(2, 4),
  c(5, 9),
  c(10, 49)
)
dataSet$editClass <- sapply(dataSet$edits, function(x) {
  wEC <- sapply(editBoundaries, function(y) {
    x >= y[1] & x <= y[2]
  })
  if (sum(wEC) == 0) {
    return(">= 50")
  } else {
    return(paste0("(",
                  editBoundaries[[which(wEC)]][1],
                  " - ",
                  editBoundaries[[which(wEC)]][2], 
                  ")"
                  )
    )
  }
})
editClass <- as.data.frame(table(dataSet$editClass), 
                           stringsAsFactors = F)
colnames(editClass) <- c('Edit Class', 'Num.Users')
editClass$order <- as.numeric(sapply(editClass$`Edit Class`, function(x) {
  lower <- str_extract(x, '[[:digit:]]+')
}))
editClass <- arrange(editClass, order)
editClass$order <- NULL
datatable(editClass)

4. Training Modules

All data on training modules are presented in this section.

4.1 Started Training Modules

Only one campaign registered user started a training module.

tModules <- read.csv('_analytics/wmde_training_data_2020-01.csv',
                     header = T,
                     check.names = F,
                     stringsAsFactors = F)
userReg <- read.csv(
  '_analytics/userRegistrationsReportFile.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
tModules <- tModules %>% 
  dplyr::filter(tModules$username %in% userReg$username)
tModules_Overview <- tModules %>%
  dplyr::select(training_module) %>% 
  dplyr::group_by(training_module) %>% 
  dplyr::summarise(num_users = n())
datatable(tModules_Overview)

4.2 Completed Training Modules

The following table presents the statistics on completed training modules. NOTE. Only one user has completed a training module.

tModules <- read.csv('_analytics/wmde_training_data_2020-01.csv',
                     header = T,
                     check.names = F,
                     stringsAsFactors = F)
userReg <- read.csv(
  '_analytics/userRegistrationsReportFile.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
tModules <- tModules %>% 
  dplyr::filter(tModules$username %in% userReg$username)
tModules <- tModules %>%
  dplyr::filter(nchar(module_completion_date) > 0)
tModules_Overview <- tModules %>%
  dplyr::select(training_module) %>% 
  dplyr::group_by(training_module) %>% 
  dplyr::summarise(num_users = n())
datatable(tModules_Overview)

4.3 Completed Training Modules and User Edits

The following table presents the statistics on completed training modules and the respective users’ edits. NOTE. The one and the only user who completed any training modules made 6 edits.

tModules <- read.csv('_analytics/wmde_training_data_2020-01.csv',
                     header = T,
                     check.names = F,
                     stringsAsFactors = F)
userReg <- read.csv(
  '_analytics/userRegistrationsReportFile.csv',
                    header = T,
                    row.names = 1,
                    check.names = F,
                    stringsAsFactors = F)
tModules <- tModules %>% 
  dplyr::filter(tModules$username %in% userReg$username)
tModules <- tModules %>%
  dplyr::filter(nchar(module_completion_date) > 0)
tModules_Edits <- tModules %>%
  dplyr::left_join(userEdits, 
                   by = c("username" = "actor_name")) %>% 
  dplyr::filter(!is.na(revactor_timestamp)) %>% 
  dplyr::group_by(training_module) %>% 
  dplyr::summarise(edits = n())
datatable(tModules_Edits)