## MANUAL RUN ## setGitBranchToSystem(); .g(); source("~/git/orch/src/VidServData/01 Ingest YouTubeMGMT Data into Snowflake.r") ## -------------------------------------------------------------------------------------------------- ## ## NOTE ## ## ## ## This script is dependent on the files having the following information in the file name ## ## ## ## c("content_type", "content_owner", "country_code", "file_mindate", "file_maxdate") ## ## -------------------------------------------------------------------------------------------------- ## ## TODO: ## If setting this up as an automated script or working off of FTP ## Capture the errors, give better error messages setScienceIfNot(proj="VidServData", subProj="IngestYTData", load=FALSE) setGitBranchToSystem(); .g() lib(bit64, quietly=TRUE) lib(reshape2, quietly=TRUE) options("snowflake_msg_sf" = FALSE) wh <- "LOOKER_WH_LARGE" dbname <- "PROD" tbl <- "premium_and_ugc" schema <- "youtubemgmt" tbl.ww <- paste0(tbl, "_worldwide") tbl.cc <- paste0(tbl, "_by_region") if (.Pfm != "Darwin") setSnowflake(wh=wh, dbname=dbname, start=FALSE) append <- FALSE files.ugc <- dir(ingest.p("UGC"), recursive=TRUE, full=TRUE) files.premium <- dir(ingest.p("Premium"), recursive=TRUE, full=TRUE) ## CONFIRM ALL FILES HAVE SIZE GREATER THAN 0 stopifnot(fileSize(files.ugc) > 0) stopifnot(fileSize(files.premium) > 0) ## As of 2016-02-14, the field names have changed ## Eventually we will should update everything to the new field names (and hence reverse the dict) ## But for now, we will map the new field names back to the old field names dict.new_names_to_old.cleaned <- as.dict( # New Field Name = Old Field Name youtube_ad_revenue_usd = "gross_revenue_usd" , cpm_usd = "impression_based_cpm_usd" , cards_shown = "card_impressions" , clicks_per_card_shown = "card_click_rate" , card_teasers_shown = "card_teaser_impressions" , clicks_per_card_teaser_shown = "card_teaser_click_rate" , estimated_partner_revenue_usd = "total_estimated_earnings_usd" , estimated_partner_ad_revenue_usd = "ad_earnings_usd" , estimated_partner_adsense_revenue_usd = "adsense_earnings_usd" , estimated_partner_doubleclick_revenue_usd = "doubleclick_earnings_usd" , partner_transaction_revenue_usd = "transaction_earnings_usd" , partner_revenue_per_transaction_usd = "earnings_per_transaction_usd" , ad_impressions = "verified_ad_impressions" , annotation_clicks = "clicks" , clickable_annotations_shown = "clickable_impressions" , clicks_per_clickable_annotation_shown = "click_through_rate" , annotation_closes = "closes" , closable_annotations_shown = "closable_impressions" , annotations_shown = "annotations_impressions" , youtube_red_partner_revenue_usd = "youtube_red_earnings_usd" , teaser_clicks_per_card_teaser_shown = "card_teaser_click_rate" ) dict.new_names_to_old <- as.dict( # New Field Name = Old Field Name "YouTube ad revenue (USD)" = "Gross revenue (USD)" , "CPM (USD)" = "Impression-based CPM (USD)" , "Cards shown" = "Card impressions" , "Clicks per card shown" = "Card click rate" , "Card teasers shown" = "Card teaser impressions" , "Clicks per card teaser shown" = "Card teaser click rate" , "Estimated partner revenue (USD)" = "Total estimated earnings (USD)" , "Estimated partner ad revenue (USD)" = "Ad earnings (USD)" , "Estimated partner AdSense revenue (USD)" = "AdSense earnings (USD)" , "Estimated partner DoubleClick revenue (USD)" = "DoubleClick earnings (USD)" , "Partner transaction revenue (USD)" = "Transaction earnings (USD)" , "Partner revenue per transaction (USD)" = "Earnings per transaction (USD)" , "Ad impressions" = "Verified Ad Impressions" , "Annotation clicks" = "Clicks" , "Clickable annotations shown" = "Clickable impressions" , "Clicks per clickable annotation shown" = "Click through rate" , "Annotation closes" = "Closes" , "Closable annotations shown" = "Closable impressions" , "Annotations shown" = "Annotations Impressions" , "YouTube Red partner revenue (USD)" = "YouTube Red earnings (USD)" , "Teaser clicks per card teaser shown" = "Card teaser click rate" ) favCols <- c("Favorites", "Favorites added", "Favorites removed") ## ======================================================================================================== ## ## SANITY CHECK: CONFIRM ALL COLUMNS IN ALL REPORTS ARE IDENTICAL ## ======================================================================================================== ## ## YT RED Data was added in Jan 2016. Thus new columns were introduced for those later reports. ## We capture those column names here so that the checks will continue to work new_YTRED_cols <- c("YouTube Red watch time (minutes)", "YouTube Red views", "YouTube Red watch time (hours)") # ## ALL FILES SHOULD HAVE THE SAME HEADERS... IF NOT, THEN REQUIRES MANUAL INSPECTION # extract_and_clean_headers_for_comparing <- function(F) { # favCols <- c("favorites", "favorites_added", "favorites_removed") # # extract("ww", F) %>% extract("20150701",.) %>% lapply(function(x) { # fread(x, nrows=5) %>% names %>% # ## make lowercase and cleanup # cleanColNamesForSQL_ %>% # {ifelse(is.na(dict.new_names_to_old[.]), ., dict.new_names_to_old[.])} %>% # unname %>% # ## Old versions had "impressions" twice # { # z <- . # if (sum(z == "impressions") == 2) # z[z == "impressions"] <- c("verified_ad_impressions","annotations_impressions") # z # ## New Version (as of 2016-02-21 no longer has 'favorites' columns # } %>% { # c(., setdiff(favCols, .)) # } %>% # setdiff(cleanColNamesForSQL_(new_YTRED_cols)) %>% # ## Put remiaing red columns to the end # { # remaining_red_cols <- extract("youtube_red", .) # setdiff(., remaining_red_cols) %>% c(remaining_red_cols) # } # }) # } ## ALL FILES SHOULD HAVE THE SAME HEADERS... IF NOT, THEN REQUIRES MANUAL INSPECTION extract_and_clean_headers_for_comparing <- function(F) { extract("ww", F) %>% extract("20150701",.) %>% lapply(function(x) { fread(x, nrows=5) %>% names %>% {ifelse(is.na(dict.new_names_to_old[.]), ., dict.new_names_to_old[.])} %>% unname %>% { z <- . if (sum(z == "Impressions") == 2) z[z == "Impressions"] <- c("Verified Ad Impressions", "Annotations Impressions") z ## New Version (as of 2016-02-21 no longer has 'favorites' columns } %>% { c(setdiff(., favCols), favCols) } %>% setdiff(new_YTRED_cols) %>% ## Put remiaing red columns to the end { remaining_red_cols <- extract("youtube(_| )red", ., ignore.case=TRUE) setdiff(., remaining_red_cols) %>% c(remaining_red_cols) } }) } stopifnot(extract_and_clean_headers_for_comparing(files.premium) %>% areEqual) stopifnot(extract_and_clean_headers_for_comparing(files.ugc) %>% areEqual) ## If the above fails, the following is for troubleshooting ## THIS IS JUST CHECKING premium -- REPEAT FOR UGC if (FALSE) { ## get the basenames of the files tmp_file_basenames <- extract("ww", files.premium) %>% extract("20150701",.) %>% basename() %>% removeText("Premium_|WW_|20150701_", .) ## extract the names tmp_nms <- extract_and_clean_headers_for_comparing(files.premium) ## These are the items which are different relative to the first set h1 <- tmp_nms[[1]] wh.diff_all <- which(!sapply(tmp_nms, function(x) {length(x) == length(h1) && all(x == h1)})) # x <- tmp_nms[[14]] # cbind(x, h1)[x != h1, ] ## find the one(s) that is (are) not duplicated wh.diff <- which(!duplicated(tmp_nms)) %>% setdiff(1) if (!length(wh.diff)) stop("\n\n Hey!! It looks like there are no differences.\n __\nDid you fix them? If so, congrats!\nIf not, WTF??") ## Show what is different for (w in wh.diff) { allSetDiff(A=tmp_nms[[1]], B=tmp_nms[[w]], A.nm=sprintf("%s (%i)", tmp_file_basenames[[1]], 1), B.nm=sprintf("%s (%i)", tmp_file_basenames[[w]], w), max.nm.char=100) %>% print catn("-----------------------------------------------------------------\n") } catn("The first file is ", sprintf("%s (%i)", tmp_file_basenames[[1]], 1)) cat("\nThe following files have headers different from the first ", sprintf("%s (%i)", tmp_file_basenames[wh.diff_all], wh.diff_all), sep="\n\t * ") catn("\nWithin the files whose headers are different, are they all the same?: ", ifelse(areEqual(tmp_nms[wh.diff_all]), "YES", "NO")) tmp_dates <- removeText("^.*_", removeExt(tmp_file_basenames[wh.diff_all])) %>% unique %>% as.Date(format="%Y%m%d") if (length(tmp_dates) == 1) catn("The date for these files is ", as.character(tmp_dates)) ## PART TWO ## NOTE: To run this, you have to manually source the read_file_list() function following the sanity check ## ## This is what I used to create the dict.new_names_to_old if (FALSE) { f1 <- extract("20150701",extract("ww", files.premium))[1] fw <- extract("20150701",extract("ww", files.premium))[w] catn("reading files: \n\t", f1, "\n\t", fw) ## USE THIS TO GET A DICT FOR THE CLEAN VERSION OF THE NAMES if (tmp_get_cleaned_version <- FALSE) { tmp_DT.1 <- read_file_list(f1)[, c(name_parts, "filename") := NULL] tmp_DT.w <- try(read_file_list(fw)[, c(name_parts, "filename") := NULL], silent=TRUE) ## if failed if (isErr(tmp_DT.w)) tmp_DT.w <- fread(fw) %>% cleanColNamesForSQL_ ## USE THIS TO GET A DICT FOR THE ORIGINAL VERISON OF THE NAMES } else { tmp_DT.1 <- fread(f1) tmp_DT.w <- fread(fw) setnames(tmp_DT.1, which(names(tmp_DT.1) == "Impressions"), c("Verified Ad Impressions", "Annotations Impressions")) } # setcolorderpt(tmp_DT.1, endCols=c(new_YTRED_cols, cleanColNamesForSQL_(new_YTRED_cols)), showWarnings=FALSE) # setcolorderpt(tmp_DT.w, endCols=c(new_YTRED_cols, cleanColNamesForSQL_(new_YTRED_cols)), showWarnings=FALSE) setcolorderpt(tmp_DT.1, endCols=extract("youtube(_| )red", tmp_DT.1)) setcolorderpt(tmp_DT.w, endCols=extract("youtube(_| )red", tmp_DT.w)) tmp_DT.same <- data.table(DT1=c(names(tmp_DT.1), rep("", max(0,length(tmp_DT.w)-length(tmp_DT.1)))), DTw=c(names(tmp_DT.w), rep("", max(0,length(tmp_DT.1)-length(tmp_DT.w)))) )[, same := DT1==DTw] tmp_dict <- tmp_DT.same[!(same) & DT1 != "", setNames(nm=DTw, obj=DT1)] dictAlign(tmp_dict, header.key="New Field Name", header.value="Old Field Name") } } ## ======================================================================================================== ## name_parts <- c("content_type", "content_owner", "country_code", "file_mindate", "file_maxdate") read_file_list <- function(files) { ll_DT <- lapply(files, function(file) { to_add <- removeText("\\.csv$", file) %>% basename() %>% strsplit("_") %>% unlist %>% setNames(nm=name_parts) %>% c(filename=file) fread(file, integer64="numeric" )[, Date := as.Date(Date, format="%b %d, %Y") ][, names(to_add) := as.list(to_add)] %>% ## Clean up the dual "Impressions" column from older files. Have to do it here and like this ## since if we try to do it after the rbind, it will create duplicate columns when we clean it (since the new files already have those names, and they will be dup'd with fill=TRUE) {if ("Impressions" %in% names(.)) setnames(., which(names(.) == "Impressions"), c("Verified Ad Impressions", "Annotations Impressions")) . } %>% setnamesByDict(dict=dict.new_names_to_old, noNeedToCheckInv=TRUE, showWarnings=FALSE, warn_for_new_return=FALSE) }) ## rbind them DT <- rbindlist(ll_DT, fill=TRUE) ## Check that no additional columns were created. ie, all the columns matched if (length(DT) - max(sapply(ll_DT, length))) warning ("Columns may have been incorretly added when rbind'ing in read_file_list()") ## CLEAN COLUMN NAMES AND ORDER setcolorderpt(DT, endCols="filename") cleanColNamesForSQL_(DT) DT[, file_mindate := as.Date(file_mindate, format="%Y%m%d")] DT[, file_maxdate := as.Date(file_maxdate, format="%Y%m%d")] return(DT) } ## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## ## REAL WORK STARTS HERE ## ## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## ## Check that there is a "Date" column in all of the files wh.missing_column_Date <- nwhich(!sapply(c(files.ugc, files.premium), function(f) "Date" %in% names(fread(f, header=TRUE, nrows=100)))) if (length(wh.missing_column_Date)) { msg.wrong_columns <- paste0("The following", length(wh.missing_column_Date), " file(s) do not have the correct column names\n\t", pasteC(wh.missing_column_Date, C="\n\t")) notifyAndEmail(subj="YouTube Data Error", msg=msg.wrong_columns) stop(msg.wrong_columns) } ## read the files, then combine them, filling in NAs DT.youtubemgmt <- rbind(read_file_list(files.ugc), read_file_list(files.premium), use.names=TRUE, fill=TRUE) ## CLEANING # setnames(DT.youtubemgmt, "estimated_minutes_watched", "watch_time_(mins)") # setnames(DT.youtubemgmt, "estimated_hours_watched", "watch_time_(hours)") setnames(DT.youtubemgmt, "watch_time_minutes", "watch_time_(mins)") setnames(DT.youtubemgmt, "watch_time_hours", "watch_time_(hours)") setnames(DT.youtubemgmt, "estimated_monetized_playbacks","monetized_playbacks") setnames(DT.youtubemgmt, "verified_ad_impressions","ad_impressions") ## NOPE: setnames(DT.youtubemgmt, "total_weighted_impession_cpm","total_impression_based_cpm") ## change names of content owner DT.youtubemgmt[content_owner == "Ent", content_owner := "Entertainment"] DT.youtubemgmt[content_owner == "Orch", content_owner := "Orchard"] DT.youtubemgmt[content_type == "Prem", content_type := "Premium"] ## orchard_estimated_net_usd is simply total estimated earnings, except for 'Entertainment' where it is (Total earnings LESS transaction earnings) DT.youtubemgmt[, orchard_estimated_net_usd := total_estimated_earnings_usd] DT.youtubemgmt[content_owner == "Entertainment", orchard_estimated_net_usd := total_estimated_earnings_usd - transaction_earnings_usd] ## ORDER AS FACTOR DT.youtubemgmt[, content_owner := toFactorWithExpectedLevels(content_owner, levels=c("Orchard", "IODA", "Entertainment"))] DT.youtubemgmt[, content_type := toFactorWithExpectedLevels(content_type, levels=c("Premium", "UGC"))] # ## Pad in any missing country-dates # ## The non-worldwide data ('xWW') gets pulled less often. Pad the missing dates with NAs # all_country_codes <- unique(DT.youtubemgmt$country_code) # all_dates <- unique(DT.youtubemgmt$date) # setkeyIfNot(DT.youtubemgmt, date, country_code, verbose=FALSE) # DT.youtubemgmt <- DT.youtubemgmt[CJ(all_dates, all_country_codes)] ## Keep only the latest file date, in case some files duplicate each others dates { verboseMsg(verbose, "Cropping to a single row per date-content-country") cols_to_unique_by <- c("date", "content_type", "content_owner", "country_code") ## first filter by max date; matching to the latest max date DT.youtubemgmt[, row_is_from_latest_file := file_maxdate == max(file_maxdate), by=cols_to_unique_by] ## then filter those by min date; matching to the latest min date. NOTE: using max(..) in both lines DT.youtubemgmt[(row_is_from_latest_file), row_is_from_latest_file := file_mindate == max(file_mindate), by=cols_to_unique_by] ## Make sure each group has exactly one row being kept. ## This is really only an issue for the 'blanks' introduced in the padding section above -- ie, for date-country combinations for which we have not pulled data this iteration ## ------------------------------------------------------------------------ ## If all are FALSE or NA make the first one TRUE DT.youtubemgmt[, row_is_from_latest_file := if (.N && all(!row_is_from_latest_file | is.na(row_is_from_latest_file))) c(TRUE, rep(FALSE, .N-1)) else row_is_from_latest_file, by=cols_to_unique_by] ## Confirm that every group has at least one row if (nrow(DT.youtubemgmt[, all(!row_is_from_latest_file | is.na(row_is_from_latest_file)), by=cols_to_unique_by][(V1)])) warning ("Some date-country_code combinations do not have ANY rows being kept") if (nrow(DT.youtubemgmt[, sumn(row_is_from_latest_file) > 1, by=cols_to_unique_by][(V1)])) warning ("Some date-country_code combinations have more than one row being kept -- this will create duplicate data") ## ------------------------------------------------------------------------ ## Drop the rows which are not from the latest file DT.youtubemgmt <- DT.youtubemgmt[(row_is_from_latest_file)] ## Confirm stopifnot(DT.youtubemgmt[, .N, by=cols_to_unique_by][, N==1]) ## drop the column DT.youtubemgmt[, row_is_from_latest_file := NULL] ## Identify which rows dont have revenue information tmp_DT.zeros <- DT.youtubemgmt[(date >= max(date) - 50), list(all_zero = all(gross_revenue_usd == 0)), keyby=date] ## The dates with all-zeros should be in a sequence and hit the end. If there are other zeros, thats a problem. maxDate <- tmp_DT.zeros[locateLastFALSE_before_lastTRUE(all_zero, warn_on_staggered=TRUE, msg="There are all-zeros for gross_revenue_usd BEFORE the tail end of the data"), date] ## Crop the data if (maxDate < max(DT.youtubemgmt$date)) { ## INFORM USER WHAT IS GETTING DROPPED message(sprintf("\t----- Cropping %i-days worth of data after %s ------", max(DT.youtubemgmt$date) - maxDate, maxDate)) DT.youtubemgmt[date >= maxDate, list(date, content_owner, content_type, gross_revenue_usd, monetized_playbacks, views, `watch_time_(mins)`, filename)] %>% {setkeyv(., names(.)[c(1,3,2)])} %>% print message("\t-----------------------------------------------------------") ## CROP DT.youtubemgmt <- DT.youtubemgmt[date <= maxDate] } DT.youtubemgmt <- unique(CJ.expand_DT_by_columns(DT.youtubemgmt, byCols=cols_to_unique_by), by=NULL) } ## Add in week & date information DT.youtubemgmt[, week := make_weeknumb_string(date)] DT.youtubemgmt[, days_in_week := lunique(date), by=list(year(date), month(date), week)] DT.youtubemgmt[, week_multip_factor := (7 / days_in_week)] ## Add in quarter information addQuarter_(DT.youtubemgmt, dateCol="date", quarter=TRUE, quarter_and_year=TRUE) ## add month_name DT.youtubemgmt[, month_name := format(date, "%b")] ## Key Columns and columns not used in numbers kCols <- c("date", "quarter_and_year", "quarter", "week", "week_multip_factor", "content_type", "content_owner", "days_in_week") # c("country_code", "file_mindate", "file_maxdate", "filename") unused <- sapply(DT.youtubemgmt, Negate(is.numeric)) %>% nwhich %>% setdiff(kCols) if (interactive()) { BackUpOrRestore("DT.youtubemgmt", force=TRUE) BackUpOrRestore("DT.youtubemgmt") } ## REMOVE WW DT.youtubemgmt.ww <- DT.youtubemgmt[country_code == "WW"] DT.youtubemgmt.xWW <- DT.youtubemgmt[country_code != "WW"] setInfo(DT.youtubemgmt.xWW, "The YouTube data removing country_code = 'WW'") setkeyIfNot(DT.youtubemgmt.ww, kCols, organize=TRUE, verbose=FALSE) setkeyIfNot(DT.youtubemgmt.xWW, kCols, organize=TRUE, verbose=FALSE) ## Aggregate. set the colorder to order of DT.youtubemgmt.aggd_ww DT.youtubemgmt.aggd_ww <- aggregateDT(DT.youtubemgmt.xWW, by=kCols, exclude=unused) DT.youtubemgmt.aggd_ww[, (unused) := NA_character_] DT.youtubemgmt.aggd_ww[, country_code := "ROW"] ## Clean up column and row order setkeyIfNot(DT.youtubemgmt.aggd_ww, kCols, verbose=FALSE) setcolorder(DT.youtubemgmt.aggd_ww, names(DT.youtubemgmt.ww)) if (nrow(DT.youtubemgmt.ww) == nrow(DT.youtubemgmt.aggd_ww)) { ## all of the kcol values should be true stopifnot(DT.youtubemgmt.ww[, kCols, with=FALSE] == DT.youtubemgmt.aggd_ww[, kCols, with=FALSE]) tmp_DT.numbs <- DT.youtubemgmt.aggd_ww[, !c(kCols, unused), with=FALSE] tmp_DT.numbs <- tmp_DT.numbs[, lapply(.SD, removeNA, rep=0)] DT.youtubemgmt.ROW <- cbind(DT.youtubemgmt.aggd_ww[, c(kCols, unused), with=FALSE], DT.youtubemgmt.ww[, !c(kCols, unused), with=FALSE] - tmp_DT.numbs ) ## Coerce classes as needed. But why is this needed? Something to do with padding the missing dates for (nm in names(DT.youtubemgmt.ROW)) { cls_to <- class(DT.youtubemgmt.xWW[[nm]])[[1]] cls_from <- class(DT.youtubemgmt.ROW[[nm]])[[1]] if (cls_to != cls_from) { if (cls_to == "Date") DT.youtubemgmt.ROW[, (nm) := as.Date(get(nm), origin=.origin)] else DT.youtubemgmt.ROW[, (nm) := as(get(nm), cls_to)] } } } else { stop ("Cannot create DT.youtubemgmt.ROW -- the row count in DT.youtubemgmt.ww was *not* the same as that of DT.youtubemgmt.aggd_ww") } ## combine ...................... ## Combine the pieces back into the main table. ## Use a temproary table first, to confirm numbers are correct tmp_DT.youtubemgmt.confirm <- rbind(DT.youtubemgmt.xWW, DT.youtubemgmt.ROW, use.names=TRUE, fill=TRUE) ## Confirm, and if all good, swap in the temp table ignore_these_cols_in_check <- c(favCols) %>% cleanColNamesForSQL_ wh.check <- mapply(function(x, y) abs(x - y) < 0.00001, sumTheTable(tmp_DT.youtubemgmt.confirm, ignore=c(kCols, unused)), sumTheTable(DT.youtubemgmt.ww, ignore=c(kCols, unused))) if(!all(wh.check | (names(wh.check) %in% ignore_these_cols_in_check) )) { which(!wh.check) stop (warningCols("The agg_ww does NOT add up to WW.\n\nHINT: Fields where there was an error are:", !wh.check)) } else { DT.youtubemgmt <- tmp_DT.youtubemgmt.confirm rm(tmp_DT.youtubemgmt.confirm) } ## combine ...................... ## ---- AGGREGATE MONTHLY AND YoY CHANGE ------------ ## DT.youtubemgmt.ww kCols.yoy <- c("content_type", "month", "year") kCols.no_yr <- kCols.yoy %>% setdiff("year") # DT.youtubemgmt.ww.aggd_by_month_content_type <- tmp_DT.youtubemgmt.ww.aggd_by_month_content_type <- DT.youtubemgmt.ww[date >= "2014-01-01", lapply(.SD, function(x) sumn(as.numeric(x))), .SD=c("gross_revenue_usd", "ad_earnings_usd", "views", "ad_impressions"), keyby=list(content_type, month=month(date), year=year(date))] %>% {.[, cpm := 1000 * gross_revenue_usd / ad_impressions]} %>% melt.data.table(id.vars=kCols.yoy) %>% {.[order(year), yoy_change := percentIncrease(value), by=c(kCols.no_yr, "variable")]} %>% dcast.data.table(makeFormula(L=c(kCols.yoy), R="variable"), value.var=c("value", "yoy_change")) %>% {setnames(., removeText("^value_", names(.)))} %>% setkeyIfNot(rev(kCols.yoy), organize=TRUE) kCols.yoy %<>% setdiff("content_type") kCols.no_yr <- kCols.yoy %>% setdiff("year") # DT.youtubemgmt.ww.aggd_by_month_content_type <- tmp_DT.youtubemgmt.ww.aggd_by_month <- DT.youtubemgmt.ww[, lapply(.SD, function(x) sumn(as.numeric(x))), .SD=c("gross_revenue_usd", "ad_earnings_usd", "views", "ad_impressions"), keyby=list(month=month(date), year=year(date))] %>% {.[, cpm := 1000 * gross_revenue_usd / ad_impressions]} %>% melt.data.table(id.vars=kCols.yoy) %>% {.[order(year), yoy_change := percentIncrease(value), by=c(kCols.no_yr, "variable")]} %>% dcast.data.table(makeFormula(L=c(kCols.yoy), R="variable"), value.var=c("value", "yoy_change")) %>% {setnames(., removeText("^value_", names(.)))} %>% setkeyIfNot(rev(kCols.yoy), organize=TRUE) tmp_DT.youtubemgmt.ww.aggd_by_month[, content_type := "TOTAL"] DT.monthly_aggregate_with_content_type <- rbind(tmp_DT.youtubemgmt.ww.aggd_by_month_content_type, tmp_DT.youtubemgmt.ww.aggd_by_month, use.names=TRUE) DT.monthly_aggregate_with_content_type[, activity_month := as.Date(sprintf("%s-%02i-01", year, month))] ## INGEST sfWarehouseOn(wh=wh, size="M") ingestIntoSQL(DT.youtubemgmt.ww, tbl=tbl.ww, schema=schema, datetime_type="TIMESTAMP_NTZ", snowflake_inuse=TRUE, append=append, drop=!append) ingestIntoSQL(DT.youtubemgmt, tbl=tbl.cc, schema=schema, datetime_type="TIMESTAMP_NTZ", snowflake_inuse=TRUE, append=append, drop=!append) ingestIntoSQL(DT.monthly_aggregate_with_content_type, tbl="monthly_aggregate_with_content_type", schema=schema, datetime_type="TIMESTAMP_NTZ", snowflake_inuse=TRUE, append=append, drop=!append) ## ======================================================================================= ## ## CREATE LOOKML ## ## ======================================================================================= ## ## ALIAS. Youtube changes the names of the report dict.yt_aliases <- list( ## field_name (current) = alias (old name) "ad_impressions" = "verified_ad_impressions" , "monetized_playbacks" = "estimated_monetized_playbacks" , "watch_time_hours" = "estimated_hours_watched" , "watch_time_mins" = "estimated_minutes_watched" , "content_owner" = "orchard_company" , "ad_impressions" = "verified_ad_impressions" , "monetized_playbacks" = "estimated_monetized_playbacks" ) ## Columns that should be listed as sales: dict.yt_labels <- list( .hidden = c("file_mindate", "file_maxdate", "filename") , sales = c("gross_revenue_usd", "total_estimated_earnings_usd", "ad_earnings_usd", "adsense_earnings_usd", "doubleclick_earnings_usd", "transaction_earnings_usd", "earnings_per_transaction_usd", "playback_based_cpm_usd", "impression_based_cpm_usd", "orchard_estimated_net_usd", "monetized_playbacks", "youtube_red_earnings_usd", "ad_impressions") , date = c("date", "days_in_week", "quarter", "quarter_and_year", "week", "month_name", "activity_month", "month", "year") , content = c("videos_in_playlists", "videos_added_to_playlists", "videos_added", "videos_published", "videos_removed_from_playlists", "content_type", "content_owner") , activity = c("closes", "closable_impressions", "close_rate", "likes", "likes_added", "likes_removed", "dislikes", "dislikes_added", "dislikes_removed", "shares", "comments", "favorites", "favorites_added", "favorites_removed", "annotations_impressions", "verified_ad_impressions", "click_through_rate", "clickable_impressions", "clicks", "impressions", "transactions", "average_percentage_viewed", "subscriber_views", "subscriber_minutes_watched", "card_clicks", "card_impressions", "card_click_rate", "card_teaser_clicks", "card_teaser_impressions", "card_teaser_click_rate", "estimated_monetized_playbacks", "views", "unique_cookies_web_only", "watch_time_(mins)", "watch_time_(hours)", "average_view_duration_minutes") , metadata = c("number_of_rows", "week_multip_factor") , .hidden = c() ) ## Add in the YoY Prefix for (nm in c("sales", "content", "activity")) dict.yt_labels[[nm]] %<>% c(., paste0("yoy_change", "_", .)) ## There should not be any column names without a label heading setdiff(names(DT.youtubemgmt), c(unlist(dict.yt_labels), "country_code")) .g(); sourceSupportFns(proj="Looker") f.out <- create_lookml_model_from_schema(schema=schema, dict.view_labels=dict.yt_labels, dict.alias=dict.yt_aliases, space_after_currency=FALSE, week_included=TRUE, quiet_output=TRUE, check_truncatedness=TRUE) ## RUN THIS ON LOCAL MACHINE if (.Pfm == "Darwin") { setScienceIfNot(proj="VidServData", load=FALSE) setGitBranchToSystem(); .g() folder.yt_lookmls <-"~/git/orch/out/VidServData/auto_generated_lookmls/youtubemgmt/" bringme(folder.yt_lookmls, over=TRUE) files_to_copy <- extractFilesFromFolder(folder.yt_lookmls, ext=".lookml", full=TRUE) file.copy(files_to_copy, "~/development/looks/.", over=TRUE) } ## ======================================================================================= ## sfShowTables(schema) saveImageTo() TRUE