# Load the DBI package
# install.packages("RMySQL")
library(DBI)
# Connect to the MySQL database: con
con <- dbConnect(RMySQL::MySQL(),
dbname = "pocket",
host = "192.168.0.97",
port = 3306,
user = "kookbal",
password = "dkssud0545")
# Import the users table from tweater:
ad_log <- dbReadTable(con, "ad_log1s")
card_owns <- dbReadTable(con, "card_owns")
point_log <- dbReadTable(con,"point_log1s")
merge_log <- dbReadTable(con,"merge_log1s")
devices <- dbReadTable(con,"devices")
user_update_list <- dbReadTable(con,"user_update_lists")
user_temps <- dbReadTable(con, "user_temps")
users <- dbReadTable(con, "users")
point_park <- dbReadTable(con, "point_park_view_detail_logs")
# adbrix + madup + google
library(dplyr) # library(plyr)
##
## 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(tidyr)
library(stringr)
library(ggplot2)
require(xlsx)
## Loading required package: xlsx
## Loading required package: rJava
## Loading required package: xlsxjars
library(reshape2)
##
## Attaching package: 'reshape2'
## The following object is masked from 'package:tidyr':
##
## smiths
# library(grid)
library(ggspectra) # multiplot
library(plotly)
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:graphics':
##
## layout
library("RColorBrewer")
library(data.table)
## -------------------------------------------------------------------------
## data.table + dplyr code now lives in dtplyr.
## Please library(dtplyr)!
## -------------------------------------------------------------------------
##
## Attaching package: 'data.table'
## The following objects are masked from 'package:reshape2':
##
## dcast, melt
## The following objects are masked from 'package:dplyr':
##
## between, last
library(corrplot)
## Programatically Rename
startingDir <- "~/Google 드라이브/Pocket Planning/R_example/Finket"
filez <- list.files(startingDir, pattern = ".xlsx")
filez1 <- filez[str_detect(filez,gsub('-', '' , as.character(Sys.Date())))]
filez2 <- filez1[str_detect(filez1,gsub('~$', '' , filez1[]))]
## adbrix file arrange
Ad <- read.xlsx(filez2[2], sheetName = "Active User", stringsAsFactors = F)
Du <- read.xlsx(filez2[2], sheetName = "DAU", stringsAsFactors = F)
Rt <- read.xlsx(filez2[2], sheetName = "Retension", stringsAsFactors = F)
Nus <- read.xlsx(filez2[2], sheetName = "New User Session", stringsAsFactors = F)
Nu <- read.xlsx(filez2[2], sheetName = "New User", stringsAsFactors = F)
## Google Analytic file arrange
Ac <- read.xlsx(filez2[1], sheetName = 3, stringsAsFactors = F)
Ac1 <- na.omit(Ac)
colnames(Ac1) <- c("Date", "Error")
Ac2 <- Ac1[Ac1$Date >= "2016-04-22" & Ac1$Date <= as.character(Sys.Date()-1), ]
## Madup API file arrange
Pis <- read.xlsx(filez2[2], sheetName = "Point Integration Stastics", stringsAsFactors = F)
Lss <- read.xlsx(filez2[2], sheetName = "App Dau Stastics", stringsAsFactors = F)
Ujws <- read.xlsx(filez2[2], sheetName = "User Join Withdrawal Stastics", stringsAsFactors = F)
Lp <- read.xlsx(filez2[2], sheetName = "Lock Screen Stastics", stringsAsFactors = F)
## We make critical events When and What
When <- function(x) {
add_trace(x = c("2016-05-04", "2016-05-04"), y = c(0,4000), mode = "markers + text" ,
line = list(width = 1, color = 'red'), name = "Critical Error Modi" , text = "Critical Error Modification") %>%
add_trace(x = c("2016-06-02", "2016-06-02"), y = c(0,4000), mode = "markers + text" ,
line = list(width = 1, color = 'darkyellow'), name = "Point Park True" , text = "Point Park True") %>%
add_trace(x = c("2016-06-16", "2016-06-16"), y = c(0,4000), mode = "markers + text" ,
line = list(width = 1, color = 'darkgreen'), name = "My Picture", text = "My Picture") %>%
add_trace(x = c("2016-07-07", "2016-07-07"), y = c(0,4000), mode = "markers + text" ,
line = list(width = 1, color = 'blue'), name = "PointPark FALSE", text = "PointPark FALSE") %>%
add_trace(x = c("2016-04-22", as.character(Sys.Date()-1)), y = c(2593,2593), mode = "lines + text" ,
line = list(dash = "5px", color = "#737373"), name = "Min Deviation", text = "Min Deviation") %>%
add_trace(x = c("2016-04-22", as.character(Sys.Date()-1)), y = c(3768,3768), mode = "Lines + text" ,
line = list(dash = "5px", color = "#737373"), name = "Max Deviation", text = "Max Deviation")
}
When10 <- function(x) {
add_trace(x = c("2016-05-04", "2016-05-04"), y = c(0,4000)*10, mode = "markers + text" ,
line = list(width = 1, color = 'red'), name = "Critical Error Modi" , text = "Critical Error Modification") %>%
add_trace(x = c("2016-06-02", "2016-06-02"), y = c(0,4000)*10, mode = "markers + text" ,
line = list(width = 1, color = 'darkyellow'), name = "Point Park True" , text = "Point Park True") %>%
add_trace(x = c("2016-06-16", "2016-06-16"), y = c(0,4000)*10, mode = "markers + text" ,
line = list(width = 1, color = 'darkgreen'), name = "My Picture", text = "My Picture") %>%
add_trace(x = c("2016-07-07", "2016-07-07"), y = c(0,4000)*10, mode = "markers + text" ,
line = list(width = 1, color = 'blue'), name = "PointPark FALSE", text = "PointPark FALSE") %>%
add_trace(x = c("2016-04-22", as.character(Sys.Date()-1)), y = c(4500,4500)*10, mode = "lines + text" ,
line = list(dash = "5px", color = "#737373"), name = "Min Deviation", text = "Min Deviation") %>%
add_trace(x = c("2016-04-22", as.character(Sys.Date()-1)), y = c(6800,6800)*10, mode = "Lines + text" ,
line = list(dash = "5px", color = "#737373"), name = "Max Deviation", text = "Max Deviation")
}
# p_merge <- select(merge_log1s, updated_at, user_id, query_company, deal_total)
# p_merge$updated_at <- strptime(as.character(p_merge$updated_at), "%Y-%m-%d")
## DAU arrange 4/22 ~ 7/10 ##
Du1 <- Du[Du$Date >= "2016-04-22" & Du$Date <= as.character(Sys.Date()-1), ]
Du1 <- setattr(Du1, "row.names", c(1:nrow(Du1)))
colnames(Du1) <- c( "Date", "Sum", "Return.User", "New.User" )
Du1$Tot <- Du1$Return.User + Du1$New.User
## Rtension arrange 4/22 ~ 7/10 ##
Rt1 <- arrange(Rt, desc(Date))
Rt1[,3:37][is.na(Rt1[,3:37])] <- 0
Rt1 <- Rt1[Rt1$Date >= "2016-04-22" & Rt1$Date <= as.character(Sys.Date()-1) , ]
## Point Integration Arrange 4/22 ~ 7/10 ##
Pis1 <- Pis[Pis$date >= '2016-04-22' & Pis$date <= as.character(Sys.Date()-1), ]
colnames(Pis1) <- c( "Date", "M10", "M20", "M30", "M40", "M50",
"F10", "F20", "F30", "F40", "F50")
Pis1.ga <- gather(Pis1, key , value, -Date)
Pis1.ga <- arrange(Pis1.ga, desc(value))
## Plotting <- Point Integration arrange 4/22 ~ 7/10 ##
Pis1.ga %>%
plot_ly( x = Date , y = value , type = "bar", color = key ) %>% layout(barmode = "stack") %>%
When()
## Warning in RColorBrewer::brewer.pal(N, "Set2"): n too large, allowed maximum for palette Set2 is 8
## Returning the palette you asked for with that many colors
# Point Integration Stastics arrange by Month 4/22 ~ 7/10 ##
Pis.mon <- Pis
colnames(Pis.mon) <- c( "Date", "M10", "M20", "M30", "M40", "M50",
"F10", "F20", "F30", "F40", "F50")
Pis.mon$Date <- substr(Pis.mon$Date, 1, 7)
Pis.mon.ga <- gather(Pis.mon, key , value, -Date)
Pis.mon.ga <- arrange(Pis.mon.ga, desc(Date))
Pis.mon.ga %>%
plot_ly( x = Date , y = value , type = "bar", color = key ) %>% layout(barmode = "stack")
## Warning in RColorBrewer::brewer.pal(N, "Set2"): n too large, allowed maximum for palette Set2 is 8
## Returning the palette you asked for with that many colors
# ggplot(Pis1.ga, aes(x = Date, y = value, color = key, fill = key)) + geom_area()
## Lock Screen Point arrange 4/22 ~ 7/10 ##
Lp1 <- Lp[Lp$date >= '2016-04-22' & Lp$date <= as.character(Sys.Date()-1), ]
colnames(Lp1) <- c( "Date", "M10", "M20", "M30", "M40", "M50",
"F10", "F20", "F30", "F40", "F50")
Lp1.ga <- gather(Lp1, key , value, -Date)
Lp1.ga <- arrange(Lp1.ga, desc(value))
## Plotting <- Lock Screen Point arrange 4/22 ~ 7/10 ##
Lp1.ga %>%
plot_ly( x = Date , y = value , type = "bar", color = key ) %>% layout(barmode = "stack") %>%
add_trace( x = Date, y = Ac2$Error * 100 , name = "Error", data = Ac2) %>%
When10()
## Warning in RColorBrewer::brewer.pal(N, "Set2"): n too large, allowed maximum for palette Set2 is 8
## Returning the palette you asked for with that many colors
## Point Integration VS DAU correlation arrange 4/22 ~ 7/10 ##
Pis1$Potal <- rowSums(Pis1[,2:11])
Du1_Pis1 <- cbind(Du1, Pis1, "Date")
Du1_Pis1 <- cbind(Du1_Pis1, Rt1, "Date")
Du1_Pis1 <- cbind(Du1_Pis1, Ac2, "Date")
## Plotting Point Integration VS DAU correlation arrange 4/22 ~ 7/10 ##
Du1_Pis1 %>%
plot_ly( x = Date, y = Tot ,name = "DAU" ) %>%
add_trace( x = Date, y = Return.User, type = "line" ,name = "Return User") %>%
add_trace( x = Date, y = fitted((loess(Tot ~ as.numeric(Date)))), type = "line" , name = "Loess Return User") %>% # scatter's
add_trace( x = Date, y = New.User, type = "line" ,name = "New User") %>%
add_trace( x = Date, y = Potal / 1000, type = "bar" ,name = " Credit Point of Total / 1,000", col = "orange") %>%
add_trace( x = Date, y = Du1_Pis1$Error , name = "Error") %>%
When()
## Plotting Credit Card Point of Total 4/22 ~ 7/10 ##
Du1_Pis1 %>%
plot_ly( x = Date , y = Potal , type = "bar", color = Potal ) %>% layout(barmode = "stack")
## Plotting <- Lock Screen Point arrange 4/22 ~ 7/10 ##
Lp1.ga %>%
plot_ly( x = Date, y = value , type = "bar", color = key ) %>% layout(barmode = "stack") %>%
add_trace( x = Date, y = Error * 100 , name = "Error * 100", data = Ac2) %>%
add_trace( x = Date, y = Return.User * 10 , type = "line" ,name = "Return User * 10", data = Du1_Pis1) %>%
add_trace( x = Date, y = New.User * 100 , type = "line" ,name = "New User * 100", data = Du1_Pis1) %>%
When10
## Warning in RColorBrewer::brewer.pal(N, "Set2"): n too large, allowed maximum for palette Set2 is 8
## Returning the palette you asked for with that many colors
# %>% add_trace( x = Date, y = Potal, type = "bar" ,name = "Point of Total", data = Du1_Pis1)
# Correlation DAU Total & Credit Total & Retension & Credit Point by Age & Error <- 4/22 ~ 7/10 ##
d <- data.frame(Du1_Pis1$Tot, Du1_Pis1$Potal, as.numeric(Du1_Pis1$X.1d), Du1_Pis1$Return.User,
Du1_Pis1$New.User, Du1_Pis1$M50, Du1_Pis1$M40, Du1_Pis1$M30, Du1_Pis1$M20, Du1_Pis1$Error)
colnames(d) <- c("DAU", "PoinToT", "1DayRet", "NewUser", "ReturnUser", "M50", "M40", "M30", "M20", "Error")
e <- data.frame(Du1_Pis1$Tot, Du1_Pis1$Potal, as.numeric(Du1_Pis1$X.1d), Du1_Pis1$Return.User,
Du1_Pis1$New.User, Du1_Pis1$F50, Du1_Pis1$F40, Du1_Pis1$F30, Du1_Pis1$F20, Du1_Pis1$Error)
colnames(e) <- c("DAU", "PoinToT", "1DayRet", "NewUser", "ReturnUser", "F50","F40", "F30", "F20", "Error")
d <- cor(d)
e <- cor(e)
par(mfrow = c(1,2))
corrplot.mixed(d)
corrplot.mixed(e)
# Correlation DAU Total & Credit Total & Retension & Credit Point by Age & Error <- 4/22 ~ ##
a <- data.frame(Du1_Pis1$Tot, Du1_Pis1$Potal,as.numeric(Du1_Pis1$X.1d),Du1_Pis1$Return.User,
Du1_Pis1$New.User, Lp1$M50,Lp1$M40,Lp1$M30,Lp1$M20,Du1_Pis1$Error )
colnames(a) <- c( "DAU", "PoinToT", "1DayRet","NewUser","ReturnUser", "M50", "M40", "M30", "M20","Error")
b <- data.frame(Du1_Pis1$Tot, Du1_Pis1$Potal, as.numeric(Du1_Pis1$X.1d),Du1_Pis1$Return.User,
Du1_Pis1$New.User, Lp1$F50,Lp1$F40,Lp1$F30,Lp1$F20,Du1_Pis1$Error)
colnames(b) <- c( "DAU", "PoinToT", "1DayRet","NewUser","ReturnUser","F50","F40", "F30", "F20", "Error")
a <- cor(a)
b <- cor(b)
par(mfrow = c(1,2))
corrplot.mixed(a)
corrplot.mixed(b)
## Point DB arrange from DB 4/22 ~ ##
Pl <- subset(point_log, select = c(user_id, unlock_point_month, merge_point_confirmed, merge_point_canceled,
invitation_point_confirmed, ad_name, point_type, point))
Pl_merge <- filter(Pl, merge_point_confirmed == 1 & merge_point_canceled == 0)
## Plotting Point DB histogramfrom DB 4/22 ~ ##
hist(Pl_merge$point, breaks = 50 , col = "darkgreen", xlim=c(0,515900))
# Retension arrange 4/22 ~ 6 ##
Rt.sub <- subset(Rt, select = c(Date, X.1d, X.7d, X.30d))
Rt.ga <- gather(Rt.sub, retention, d.value, -Date)
Rt_plotly <- subplot(
Rt.ga %>%
plot_ly( x = Date , y = d.value, fill = retention, color = retention),
Rt.ga[Rt.ga$Date >= as.character(Sys.Date()-60) & Rt.ga$Date <=as.character(Sys.Date()-31) , ] %>%
plot_ly( x = Date , y = d.value, fill = retention, color = retention, ylim = c(0,0.6)),
Rt.ga[Rt.ga$Date >= as.character(Sys.Date()-30) & Rt.ga$Date <= as.character(Sys.Date()) , ] %>%
plot_ly( x = Date , y = d.value, fill = retention, color = retention, ylim = c(0,0.6)),
margin = 0.05,
nrows = 3
) %>% layout(showlegend = FALSE)
# Plotting Retension arrange 4/22 ~ 7/06 ##
Rt_plotly
# Plotting Retension with Error 4/22 ~ 7/06 ##
plot_ly(Ac, x = Ac$Date, y = Ac$Error/ 10000 ) %>%
add_trace( x = Date , y = d.value, fill = retention, color = retention, data = Rt.ga)
# par(mfrow = c(2,2))
## DAU 4/22 ~ 7/06 ##
Du <- subset(Du, select = c(Date, Ret..User, New.User))
Du.ga <- gather(Du, RN.key, RN.value, -Date)
Du_plotly <- subplot(
Du.ga[ Du.ga$RN.value != 0 ,] %>%
plot_ly(x = Date, y = RN.value, fill = RN.key, color = RN.key ),
Du.ga[ Du.ga$RN.value != 0 & Du.ga$Date >= as.character(Sys.Date()-60) & Du.ga$Date <= as.character(Sys.Date()-31),] %>%
plot_ly(x = Date, y = RN.value, fill = RN.key, color = RN.key ),
Du.ga[ Du.ga$RN.value != 0 & Du.ga$Date >= as.character(Sys.Date()-30) & Du.ga$Date <= as.character(Sys.Date()),] %>%
plot_ly(x = Date, y = RN.value, fill = RN.key, color = RN.key ),
margin = 0.05,
nrows = 3
) %>% layout(showlegend = F)
## Warning in RColorBrewer::brewer.pal(N, "Set2"): minimal value for n is 3, returning requested palette with 3 different levels
## Warning in RColorBrewer::brewer.pal(N, "Set2"): minimal value for n is 3, returning requested palette with 3 different levels
## Warning in RColorBrewer::brewer.pal(N, "Set2"): minimal value for n is 3, returning requested palette with 3 different levels
## Plotting DAU 4/22 ~ 7/06 ##
Du_plotly
## Credit Card Point Integration from DB 4/22 ~ 7/06 ##
pm_plotly <- subplot(
merge_log %>%
plot_ly( x = updated_at, y = deal_total, mode = "bars", color = user_id, size = deal_total),
merge_log[merge_log$updated_at >= as.character(Sys.Date()-60) & merge_log$updated_at <= as.character(Sys.Date()-31),] %>%
plot_ly( x = updated_at, y = deal_total, mode = "bars", color = user_id, size = deal_total),
merge_log[merge_log$updated_at >= as.character(Sys.Date()-30) & merge_log$updated_at <= as.character(Sys.Date()),] %>%
plot_ly( x = updated_at, y = deal_total, mode = "bars", color = user_id, size = deal_total),
margin = 0.05,
nrows = 3
) %>% layout(showlegend = T)
pm_plotly