Playing with R and SQLite


There are multiple questions:

  1. The operating regions are indicated by region_id. Generate a report of the average hourly_charge in each operating region as well as the overall average.

  2. Assuming that a booking is completed if it is not cancelled by the customer and has no reschedule events, generate a report based on the calendar week (running Sun-Sat) of the number of bookings done, number of bookings done using coupons, total hours booked, and number of bookings which were cancelled by the customer.

  3. Recurring bookings are bookings which happen on a regularly scheduled basis and are indicated by recurring_id and a frequency (freq) indicating how many weeks pass between each booking in the series. Determine the distribution of bookings based on the frequency of the recurring booking to which they belong across the days of the week on which they were completed.

  4. Say we have a problem with customers canceling and rescheduling bookings. Assuming all the bookings are from different users, pull metrics which you believe would give a general profile of these problem users.


  • Question asked before
  • handy_bookings_train.csv and handy_booking_test.csv


  • This document
#Set up
setwd("~/R projects/handy")
#load csv sql to be able to do sql queries on the csv files
#the R library use sqlite
#Some graph
#load data

#Question 1

The operating regions are indicated by region_id. Generate a report of the average hourly_charge in each operating region as well as the overall average.

#average per region
avg.region<-sqldf("SELECT region_id as region,avg(hourly_charge) as average_charge  FROM 'ds.train' GROUP BY region_id")
## Loading required package: tcltk
#overall average
#I wasn't sure if you wanted the overall average weighted by region or just the overall average so I made both query
avg.overall<-sqldf("select avg(hourly_charge) from 'ds.train'")
#average overall weighted by region
avg.region.overall<-sqldf("select avg(t.average_charge) from (SELECT avg(hourly_charge) as average_charge from 'ds.train' group by region_id) t")
#This output the same result but it's kind of cheating since I'm using R and not pure sql
#avg.region.overall<-sqldf("select avg(average_charge) FROM 'avg.region'")
#If I had to pick one, I think it makes more sense to use the avg.overall one.

##    region average_charge
## 1       4       2981.250
## 2       5       2853.342
## 3       6       2838.129
## 4       8       2969.583
## 5       9       2886.047
## 6      10       2936.000
## 7      11       2928.125
## 8      12       2945.614
## 9      13       3000.000
## 10     14       3146.032
## 11     15       3008.125
## 12     16       3108.333
## 13     17       3088.298
## 14     18       3077.778
## 15     20       3165.385
## 16     21       3003.175
## 17     22       3144.000
## 18     23       3053.704
## 19     24       3108.730
## 20     25       3068.831
## 21     26       3108.511
## 22     27       3047.826
## 23     28       2929.592
## 24     29       3095.455
## 25     31       3032.895
## 26     32       2906.061
## 27     33       3013.115
## 28     34       2868.750
## 29     35       3059.677
## 30     36       1007.380
## 31     38       3098.485
## 32     41       1000.000
## 33     42       1000.000
ggplot(data=avg.region,aes(x=region,y=average_charge))+geom_histogram(stat="identity")+scale_x_continuous(breaks=seq(min(avg.region$region),max(avg.region$region),1))+xlab("Region ID")+ylab("Average Charge")+ggtitle("Average charge per region")

##   avg(hourly_charge)
## 1           2674.819
##   avg(t.average_charge)
## 1              2832.674

#Question 2

Assuming that a booking is completed if it is not cancelled by the customer and has no reschedule events, generate a report based on the calendar week (running Sun-Sat) of the number of bookings done, number of bookings done using coupons, total hours booked, and number of bookings which were cancelled by the customer.

#The way I understood the question was to ask for the day of the week (sun-sat) of the week number (in the year). I am not sure it is exactly what you meant.
#Additionally, it wasn't clear to me if you wanted the report to be for the day of the week it was added or it was starting. I figured that added made more sense.
#This question is kind of tricky because I did not know exactly one row entry mean and what the column mean. I guess it was part of the exercise that's why I didn't ask.
#I have noticed that some date cancelation were after the start date and date start was always after date added. It made me assume that each row is for each customer, date added is when they added their order, date start is when they first start and frequencies indicate the  frequency of the task. Therefore, you can cancel after the start if your frequency is more than one.
#I assumed that an event would be canceled when we had a cancel date and not reschuduled when  reschedule_events_count was 0
#I also assumed this report was to give information of "what have been done" which mean that the booking hours should be only to the one who didn't cancel.

report<-sqldf("select cast (strftime('%W', date_start) as integer) as weeknumb, case cast (strftime('%w', date_added) as integer)
  when 0 then 'Sunday'
  when 1 then 'Monday'
  when 2 then 'Tuesday'
  when 3 then 'Wednesday'
  when 4 then 'Thursday'
  when 5 then 'Friday'
  when 6 then 'Saturday'
  else 'Unknwon' end as dow,
  count(case when (reschedule_events_count=0 OR customer_cancelation_date is null) then 1 end) as booking_done,
  count(case when (reschedule_events_count=0 OR customer_cancelation_date is null AND coupon is not null) then 1 end) as booking_done_coupon,
  sum(case when (reschedule_events_count=0 OR customer_cancelation_date is null) then hrs else 0 end) as booking_hours,
  count(case when (customer_cancelation_date is not null AND reschedule_events_count>0) then 1 end) as booking_cancelled
  from 'ds.train'
  group by weeknumb,cast (strftime('%w', date_added) as integer) order by weeknumb, cast (strftime('%w', date_start) as integer)")

##Please note that I could definitely have breaked down this query into string vars in order to not repeat myself. However, it was saying to use SQL. Therefore, I assumed I should do as if I could only do SQL in a terminal.
##    weeknumb       dow booking_done booking_done_coupon booking_hours
## 1        48    Sunday            7                   7          19.0
## 2        48  Thursday          108                  97         287.5
## 3        48  Saturday           57                  51         145.0
## 4        48    Monday          291                 275         771.0
## 5        48 Wednesday          141                 128         390.0
## 6        48   Tuesday          259                 238         658.5
## 7        48    Friday           89                  72         236.5
## 8        49    Monday          261                 240         716.5
## 9        49  Saturday          163                 148         449.5
## 10       49    Sunday          162                 149         448.0
## 11       49   Tuesday          316                 286         844.5
## 12       49  Thursday          243                 215         695.5
## 13       49    Friday          221                 202         617.5
## 14       49 Wednesday          250                 224         688.0
## 15       50    Sunday          151                 139         422.5
## 16       50    Monday          295                 273         815.0
## 17       50   Tuesday          267                 248         742.5
## 18       50 Wednesday          215                 192         596.5
## 19       50  Thursday          148                 125         431.0
## 20       50  Saturday          106                  96         314.0
## 21       50    Friday          119                 107         344.0
##    booking_cancelled
## 1                  0
## 2                  7
## 3                  2
## 4                  5
## 5                  4
## 6                  6
## 7                  4
## 8                  6
## 9                  3
## 10                 4
## 11                11
## 12                 6
## 13                18
## 14                10
## 15                 4
## 16                 9
## 17                13
## 18                 9
## 19                 3
## 20                 1
## 21                 6


Recurring bookings are bookings which happen on a regularly scheduled basis and are indicated by recurring_id and a frequency (freq) indicating how many weeks pass between each booking in the series. Determine the distribution of bookings based on the frequency of the recurring booking to which they belong across the days of the week on which they were completed.

#On this question, I am really confused. Do you want me to give you the  classification model using machine learning (I see there are train and test data)?
#Do you want me to give you the shape of the data (normal distribution, linear...)
#Do you want me to give you some descriptive informations such as the range, mean, variance..
#The first line says SQL, I will just give you some general information but please tell me if you want more. I can definitely do it if that's what you are looking for.

distribution<-sqldf("select cast (avg(freq) as integer) as freq,case cast (strftime('%w', date_start) as integer)
  when 0 then 'Sunday'
  when 1 then 'Monday'
  when 2 then 'Tuesday'
  when 3 then 'Wednesday'
  when 4 then 'Thursday'
  when 5 then 'Friday'
  when 6 then 'Saturday'
  else 'Unknwon' end as dow,
  count(*) as freq_count
  from 'ds.train' where (recurring_id is not null AND freq is not NULL) AND (reschedule_events_count=0 OR customer_cancelation_date is null)
  group by cast (strftime('%w', date_start) as integer),freq order by cast (strftime('%w', date_added) as integer)")

ggplot(data=distribution,aes(x=as.factor(freq),y=freq_count))+geom_histogram(stat="identity")+facet_wrap(~dow)+xlab("Frequence")+ylab("Number of completed booking")+ggtitle("Number of completed booking per frequency accross the day of the week")

##Question 4

Say we have a problem with customers canceling and rescheduling bookings. Assuming all the bookings are from different users, pull metrics which you believe would give a general profile of these problem users.

#We can do many things to try to find some correlation between the data
#One thing we could look at is the disperssion of the cancelation date
#Did they usually cancel at the day of the week
#We could use machine learning again with a binary response which would be canceled and reshudeled against normal or find a linear correlation using pearson moment correlation for example.
#However, the way I understood the challenge was to only use pure SQL (without help from any programming language as if you where in a sql terminal) for each question which highly limit the possibilities.
#What we want to know is why these users are different than the one who don't cancel or reschedule
#Therefore, our metric should be able to compare both type of users.
#Moreover, there is the notion of time. Was there a special event that made customers leave. Therefore, a timeline of percentage of user leaving vs total user would be interesting.
#Also, it would be interesting to see the acquisition vs retention rate. Do people who cancel are first time user or people who have had services every week (looking at freq)

metric.retention<-sqldf("select (cast (strftime('%W', date_added) as integer) +(cast (strftime('%w', date_added) as integer))/7.0) as date_event, (100.0*(count(case when(reschedule_events_count>0 AND customer_cancelation_date is not null) then 1 end))/count(*)) as perc,
             (avg(case when(reschedule_events_count>0 AND customer_cancelation_date is not null) then freq  end)) as freq_leaving,
           (avg(case when(reschedule_events_count=0 OR customer_cancelation_date is null) then freq end)) as freq_not_leaving

            from 'ds.train' group by cast (strftime('%W', date_added) as integer),cast (strftime('%w', date_added) as integer) order by cast (strftime('%W', date_added) as integer), cast (strftime('%w', date_added) as integer)")

ggplot(data=metric.retention,aes(x=date_event,y=perc))+geom_point()+scale_x_continuous(breaks=seq(min(metric.retention$date_event),max(metric.retention$date_event),0.5))+geom_smooth()+xlab("Time")+ylab("Percentage of uncompleted booking")+ggtitle("Percentage of uncompleted booking accross time")
## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.

#We can see that there is a peak during the week 49 (saturday)
#It would be interesting to see if we had a problem of in our server or some reason that the data itself wouldn't tell us

ggplot(data=metric.retention,aes(x=date_event,y=freq_not_leaving))+geom_point()+scale_x_continuous(breaks=seq(min(metric.retention$date_event),max(metric.retention$date_event),0.5))+geom_line()+geom_point(aes(x=date_event,y=freq_leaving,color="red"))+geom_line(aes(x=date_event,y=freq_leaving,color="red"))+xlab("Time")+ylab("Average frequency")+ggtitle("Average frequency of uncompleted (red) vs completed booking")
## Warning: Removed 1 rows containing missing values (geom_point).
## Warning: Removed 1 rows containing missing values (geom_path).
## Warning: Removed 3 rows containing missing values (geom_point).
## Warning: Removed 2 rows containing missing values (geom_path).

#We can see that while the average frequency for the staying has a small variation, the variation of the one for the one leaving is really higher. We can conclude that the one staying have usually a freq of 3 while the one leaving have other frequencies. Therefore, we should promote having frequency of 3 to keep them.

#Now, let's look at other interesting variables such as wether or not they have coupon (a deeper analysis of which coup trigger retention or not could be done as well),if they clicked for extra home cleaning, total chargethe region,if there was a campaign,number of provider, if provider is requested, if it was peak priced.
#If I had to do it only with sql I would look at the average and the variance of each variable for the group leaving and staying. This would give me a good global idea if there are some odd behavior and which metric makes more sense.

#However, I believe that the best way to know if someone is going to leave or not is to do preddictive analysis. Therefore, I'll be using a bit of machine learning.
## randomForest 4.6-10
## Type rfNews() to see new features/changes/bug fixes.
## Attaching package: 'dplyr'
## The following object is masked from 'package:stats':
##     filter
## The following objects are masked from 'package:base':
##     intersect, setdiff, setequal, union
#Cleaning data removing meaningless variables
rf.train<- ds.train %>% mutate(has_coupon=as.factor(as.numeric(,total_charge=as.numeric(hrs*hourly_charge),cancel=as.factor(as.numeric(! & reschedule_events_count>0))) %>% select_("-row_id","-customer_cancelation_date","-date_start","-date_added","-hrs","-hourly_charge","-coupon","-recurring_id","-reschedule_events_count","-user_bookings_count","-user_cancelled_bookings_count","-region_id")

rf.test<- ds.test %>% mutate(has_coupon=as.factor(as.numeric(,total_charge=as.numeric(hrs*hourly_charge),cancel=as.factor(as.numeric(! & reschedule_events_count>0))) %>% select_("-row_id","-customer_cancelation_date","-date_start","-date_added","-hrs","-hourly_charge","-coupon","-recurring_id","-reschedule_events_count","-user_bookings_count","-user_cancelled_bookings_count","-region_id")




##      0 1 class.error
## 0 3948 0           0
## 1   52 0           1
#We can see that the confusion matrix is really bad, it is unecessary to look at the prediction, the clasifier is just bad
#pred<-predict(clf,rf.test %>% select_("-cancel"))
#Let's try with adaboost
############      AdaBoost   ###############
#I picked adaboost because it is supposed to be a great mix between accuracy and speed for high dim predictors with NA.
## Loading required package: rpart
#Helper function that allow to know what are the dependencies between vars
#Input: dataframe, name of y in dataframe, type of boost you want to apply, number of iteration and depth
#Output: classifier
  n <- nrow(data)
  ind <- sample(1:n)
  xnam <- paste(names(data[-indy]), sep="")
  fmla <- as.formula(paste(y," ~ ", paste(xnam, collapse= "+")))
  trainval <- ceiling(n * .5)
  testval <- ceiling(n * .3)
  train <- data[ind[1:trainval],]
  test <- data[ind[(trainval + 1):(trainval + testval)],]
  valid <- data[ind[(trainval + testval + 1):n],]
  control <- rpart.control(cp = -1, maxdepth = cdepth,maxcompete = 1,xval = 0)
  clf <- ada(fmla, data = train, test.x = test[,-indy], test.y = test[,indy], type =adatype, control = control, iter = adaiter)
  clf <- addtest(clf, valid[,-indy], valid[,indy])

##Now we compute our adaboost and do our prediction and confusion matrix
#Take labels and assign variables after doing adaboost, prediction, confusion matrix and the variable importance
  xtest<-rf.test %>% select_("-cancel")
  ytest<-rf.test %>% select_("cancel")
  #confusion matrix
  #var importance<-varplot(ada.clf,,type='scores')
##           Final Prediction
## True value    0
##          0 1969
##          1   31
#Again the confusion matrix is really bad, we can't predict if it is canceled
#We can't easily predict it
#Even if we could, the testing set is actually consisting of only completed booking which give us an unbalanced set
#I will then just look at the classification tree
## Loading required package: grid
## Loading required package: mvtnorm
## Loading required package: modeltools
## Loading required package: stats4
## Loading required package: strucchange
## Loading required package: zoo
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
##     as.Date, as.Date.numeric
## Loading required package: sandwich

#We can see that the best metric to know if we complete or not a booking is if we have a provider or not. Other than that
#We could go further and look at the total charge, frequency, has_campaign, clicked_extra_home_cleaning, peak_priced, is_provider_requested, has_coupon.
#However, this is mostly descriptive analysis which trigger less accurate result than the classification done.

Playing with R – A bit of machine learning


The question is: Tell us something interesting about the ping backs we receive from videos.
*Question asked before
*tsv data file
*pdf file with data format
*This document

I picked R in order to do this analysis as it did appears to me that this is mainly an exploratory data analysis and R markdown + ggplot2 are very conveniant for that in my opinion.

We will be proceeding in this order:
*Import the data
*Clean the data
*Explore the data
*Try to find a predective model
*Explore the predective features that were found important
*Make asumption from findings

###1. Import the data

Quite easy here. The data being small I can just use read.csv without using data.table

#library import

##2.Clean the data

#Name it according to the pdf with the addition of the python script for the user agent detail

##do some basic lookup of fields to see if they are usefull
#I am ommiting some parts as it would make it unecessary too long
#It was said to be from the  website
#Therefore let's check if all the data are from it
#ok the table tells us that all data are from the same domain
#Therefore, it doesn't give us any usefull information, let's remove it.
#urls give us lot of usefull informations such as
#port,hostname of video url, extension, the path to the video,query (if there is one),params
#username,fragment,password,scheme as defined in 
#let's break down the page url and video url

#This is an helper function to breakdown the urls
#Input: ds => data frame and field => field of data frame that contain url
#Output: a data frame of 10 variables of format field.variable where variable is:
  url.struct[sapply(url.struct, is.null)] <- NA 
  url.df<-data.frame(matrix(unlist((url.struct)), nrow=ncol(url.struct), byrow=T),stringsAsFactors=F)
  url.df<-url.df %>% mutate(ext=(file_ext(as.character(path))))
  names(url.df)<-sapply(names(url.df),function(x) paste0(field,".",x))


#We tell R that empty string is NA
ds[ds==''] <- NA 
#One interesting thing that we can now extract from the data is if it is mobile or not
#It can be useful because the dataset is very small
##A quick look at the pdf and using this command
##show me that it was coded as a dummy variable let's regroup them and add ping.not.embed for easy of use 
##Finally, let's remove all the empty column (the one who only have NA)
notEmpty<-names(which(sapply(ds[],function(x) !all(
ds[,notEmpty] <- lapply(ds[,notEmpty] , function(x) factor(x,exclude=NA))

Here are all our finals features after the data cleaning:
id, date, user.agent, browser, country, page.url, title, video.url,, ping.embed,, ping.complete, ua.device.brand, ua.device.model,, ua.os.major, ua.os.minor,, ua.os.patch,,,,, page.url.scheme, page.url.hostname, page.url.path, page.url.query, page.url.fragment, page.url.ext, video.url.scheme, video.url.hostname, video.url.port, video.url.path, video.url.query, video.url.ext, mobile, ping.not.embed, ping
Same of them are redundant (or the same but in more detail) but it will help to have a direct accessor when we want to explore them in deeper detail

The last call is a tought call which is, should I keep or not empty observation. In order to know that we first need to estimate the importance of them.
Let’s look at them.

We can see that most data which are missing are from the data I generated. This is not actually a problem since they are mainly here to give extra information if we want to be very specific about a certain observation.
Additionally, we can clearly see a pattern between missing value and if we take the column we see that there are 0.895% of missing data which is really small.In addition for the video extension there 2.805% of missing data, this is relatively small.
These 348 correspond actually to all the videos. Removing it wouldn’t allow us to predict any ping from
Where it becomes tricker it is for the title.
There is 57.493% of missing video title.
What to do then? A quick glance at the data tell me that there is no video with the same id but a different title. Therefore, when I am going to predict the data, I’ll just omit title which will overfit the model. As for, I think it should be identified aside (using a knn or other model) to see if there is any special pattern that could explain it.
The trickiest question, is should I or not remove the youtube videos which have no extension, by doing that I really impute the model from maybe usefull informations. I have decided not to.


ds<-ds %>% filter(!

##3. Explore the data

#Set the seed for reproductible example

####We will be looking at the ping. Can we predict it from our dataset?

The idea, is to look if we can raisonabely predict some state of ping. If so, what are the important variables that drive our prediction. In order to do that I am going to use multiple classifier: Adaboost, SVM, GBM and randomForest.

First we reduce the number of feature to only keep the one that have significance using a wrapper approach for subset selection.

#This wrapper approach wasn't actually giving me better result. I assumed it was because of the highly unbalanced data. I ended up doing it myself with trial.
#Wrapper approach for feature reduction using hill climbing search
#Input: data, y (binary)
#Output: list of selected factor reducing the error rate
  evaluator <- function(subset) {
    #k-fold cross validation
    k <- 5
    splits <- runif(nrow(data))
    results = sapply(1:k, function(i) {
    test.idx <- (splits >= (i - 1) / k) & (splits < i / k)
    train.idx <- !test.idx
    test <- data[test.idx, , drop=F]
    train <- data[train.idx, , drop=F]
    tree <- rpart(as.simple.formula(subset, y), train)
    error.rate = sum(with(test, get(y)) != predict(tree, test,type="c")) / nrow(test)
    return(1 - error.rate)
  subset <-[-which(names(data)==y)], evaluator)

I also chose not to use another training sample for the feature selection as the data is already very small.
Finally, one of the main issues in wrapper method is overfitting and there are feature that are obiously overfitting/irrelevent.
Therefore, I decided to remove all the ua (except the OS, I gained more levels about the browser but the dataset being too small I decided not to use it since it would create too much of unbalanced data) fields before computing it
I am also removing informations that just wouldn’t make sense (i.e. the date being only for one day and we don’t have the “local” time but only the standardized time +0000 for all data)

Remove other factors that are irrelevent for the analysis:
*title (explained before)
*page.url.scheme has only one level which is http
*page.url.path has no meaning for predicting
*page.url.ext has only one level which is html
*video.url.port only 2.9363917% of the data and not relevant
*date is more debatable but the data being only on one day and not local zone I don’t see it making any sens. The only thing we could get out of it is if we have no ping at all during a certain period which could mean a failure of the server but this is more in the observation field than the prediction (we can’t predict a server failure from this data) which is what we are trying to achieve now
*ping.embeded,, ping.complete, ping that are our labels
*id even though this one is interesting, the high number of factor (1355) make it a bad predictor (even using svm or gbm).

#Get the ua names index that was generated
#remove other factors that have very few meaning



#I went into several trial and realized that this step wasn't necessary for this analysis
#However, I didn't remove it as I think the feature selection is key to the process. Therefore, I wanted to show that I actually did work on the feature selection.<-ind.features.filter<-feature.reducer(ds[c(ind.features.filter,"ping.not.embed")],"ping.not.embed")<-feature.reducer(ds[c(ind.features.filter,"ping.embed")],"ping.embed")<-feature.reducer(ds[c(ind.features.filter,"")],"")<-feature.reducer(ds[c(ind.features.filter,"ping.complete")],"ping.complete")

Here are our features:
user.agent, browser, country,, page.url.hostname, video.url.scheme, video.url.hostname, video.url.ext, mobile,
If we look into their level we can see that we have features with very high dimensions.
In order to deal with high dimensional data, we will be using three classifiers that deal nicely with high categorical levels features and use binary classification if needed:
*Generalized Boosted Regression Model (GBM)

##Setting training and testing set vars

#Creating a function to create my sample of test from a subset of the data
#Input: data, name of features to keep, labels (what we want to predict), traning.percentage
#Output: assignement of global variable train,test with this pattern train.labelname,test.labelname 
#Warning: will overide any existing var with the same name
n <- nrow(data)
ind <- sample(1:n)
trainval <- ceiling(n * traning.percentage)
    test <- data[ind[(trainval + 1):n],c(ind.features.filter,x)]
    namTrain <- paste("train", x, sep = ".")
    namTest <- paste("test", x, sep = ".") 
    assign(namTrain,train,envir = .GlobalEnv)
    assign(namTest,test,envir = .GlobalEnv)


############      AdaBoost   ###############
#I picked adaboost because it is supposed to be a great mix between accuracy and speed for high dim predictors.

#Helper function that allow to know what are the dependencies between vars
#Input: dataframe, name of y in dataframe, type of boost you want to apply, number of iteration and depth
#Output: classifier
  n <- nrow(data)
  ind <- sample(1:n)
  xnam <- paste(names(data[-indy]), sep="")
  fmla <- as.formula(paste(y," ~ ", paste(xnam, collapse= "+")))
  trainval <- ceiling(n * .5)
  testval <- ceiling(n * .3)
  train <- data[ind[1:trainval],]
  test <- data[ind[(trainval + 1):(trainval + testval)],]
  valid <- data[ind[(trainval + testval + 1):n],]
  control <- rpart.control(cp = -1, maxdepth = cdepth,maxcompete = 1,xval = 0)
  clf <- ada(fmla, data = train, test.x = test[,-indy], test.y = test[,indy], type =adatype, control = control, iter = adaiter)
  clf <- addtest(clf, valid[,-indy], valid[,indy])

#AdaBoost is waiting for a binary response therefore we use our dummy variables only

##Now we compute our adaboost and do our prediction and confusion matrix
#TODO use parallel to make it faster
#Take labels and assign variables after doing adaboost, prediction, confusion matrix and the variable importance
  #confusion matrix
  #var importance<-varplot(ada.clf,,type='scores')
  #Create the variables names
  #store it
  assign(name.clf,ada.clf,envir = .GlobalEnv)
  assign(name.pred,ada.pred,envir = .GlobalEnv)
  assign(name.conf,ada.conf,envir = .GlobalEnv)
  assign(,,envir = .GlobalEnv)

#Let's check if we found corrects model

Note that usually, we try to maximize the accuraccy by adding/removing features when we don’t have a specific question (if the clf don’t do it for you).

Result for:
*Not Embed

## Call:
## ada(fmla, data = train, test.x = test[, -indy], test.y = test[, 
##     indy], type = adatype, control = control, iter = adaiter)
## Loss: exponential Method: gentle   Iteration: 70 
## Training Results
## Accuracy: 0.767 Kappa: 0.539 
## Testing Results
## Accuracy: 0.718 Kappa: 0.442 
## Accuracy: 0.709 Kappa: 0.428

We can see that the model is correct (relatively moderately high Accuracy/Kappa).

Let’s check if we had enough iterations:

Yes it’s ok

Let’s look into the confusion matrix on the test subject

## Confusion Matrix and Statistics
## ada.pred   0   1
##        0 812 181
##        1 508 957
##                Accuracy : 0.7197          
##                  95% CI : (0.7015, 0.7374)
##     No Information Rate : 0.537           
##     P-Value [Acc > NIR] : < 2.2e-16       
##                   Kappa : 0.4472          
##  Mcnemar's Test P-Value : < 2.2e-16       
##             Sensitivity : 0.6152          
##             Specificity : 0.8409          
##          Pos Pred Value : 0.8177          
##          Neg Pred Value : 0.6532          
##              Prevalence : 0.5370          
##          Detection Rate : 0.3303          
##    Detection Prevalence : 0.4040          
##       Balanced Accuracy : 0.7281          
##        'Positive' Class : 0               

The model prediction is correct.
Let’s look then at the variable importance.

Therefore, it seems like the fact that we are on mobile and the protocol (scheme) and os used
influence the most the fact that we will or not load the video. Which is common sense since we might load (or not) the video if our mobile/computer can (or not) read a specific protocol.


I am going to go a bit faster, I did check the iteration/kappa/accuracy of the classifier whch are correct. The confusion matrix is also correct. Therefore, I can show you the var importance.

We can see that the country play an important role which did surprise me at first and then I figured out that it was certainly because the data was unbalanced. Then the classifier did put scheme and operating system.


Kappa is really low and therefore the test failed has no meaningfull result.


Same thing.

An interesting fact is that the sensitivity (with 0 (not) being the positive class) was really high for both of them.
This tell us that the model is good to know when a video won’t be played or completed which is consistant with the two other states. Additionally, we can explain it by the high unbalance of the data.

We have seen that some prediction were inacurate. We have two main possibilities:
*Change the features (we could use a feature ranked approach such as chi-squared or information gain since the wrapper one didn’t result in effective results) or transform the features
*Use another model (which can also help for feature selection)

Adaboost was a great example, I’m going to go faster now for the other classifier.


############      GBM   ###############
##I picked GBM because it is robust and relatively fast
#It also allow feature to have 1024 levels (unlike randomforest)
#Finally, it won several kaggle competition, thanks to its high acuracy


xTrain< %>% select(-ping,-user.agent)
xTest< %>% select(-ping,-user.agent)


#a high guess of how many trees we'll need
ntrees = 500

#An optimation would be to do cross validation to set the parameters
gbm.model <-
x = xTrain 
, y = yTrain 
#x and y instead of using a formula
, distribution = "multinomial"
#use bernoulli for binary outcomes
#other values are "gaussian" for GBM regression
#or "adaboost"
, n.trees = ntrees
#Choosed this value to be large, then we will prune the
#tree after running the model
, shrinkage = 0.01
#smaller values of shrinkage typically give slightly better performance
#the cost is that the model takes longer to run for smaller values
, interaction.depth = 3
#TODO: use cross validation to choose interaction depth
, n.minobsinnode = 300
#n.minobsinnode has an important effect on overfitting
#decreasing this parameter increases the in-sample fit,
#but can result in overfitting
, nTrain = round(nrow(xTrain) * 0.8)
#select the number of trees at the end
# ,var.monotone = c()
#can help with overfitting, will smooth bumpy curves
, verbose = F #print the preliminary output

#Verify we had enough tree
gbm.perf(gbm.model, = TRUE)
## Using test method...

## [1] 500
#Relative influence among the variables can be used in variable selection or seing if 
#parameter tunning (i.e. if there is overfitting)

##                                   var    rel.inf
##            67.0141245
## country                       country 23.7720645
## page.url.hostname   page.url.hostname  5.3680470
##     3.6998816
## browser                       browser  0.1458824
## video.url.scheme     video.url.scheme  0.0000000
## video.url.hostname video.url.hostname  0.0000000
## video.url.ext           video.url.ext  0.0000000
## mobile                         mobile  0.0000000
#Print tree

##Rest is commented since it was more for the exploration than drawing conclusion
#look at the effects of each variable
#for(i in 1:length(gbm.model$var.names)){
#  print(i)
#plot(gbm.model, i.var = i
#, ntrees = gbm.perf(gbm.model, = FALSE) #optimal number of trees
#, type = "response" #to get fitted probabilities
#Try to predict our test variables on the best tree
#gbm.test.predict<- predict(object = gbm.model,newdata =select(,-ping,-user.agent)
#, n.trees = gbm.perf(gbm.model, = FALSE)
#, type = "response")

#roc.area(select(,ping.embed)[,1], gbm.test.predict)$A



GBM is actually not working well at all here. looking at its tree and summary we can see too much importance on the first variable. I tried to tweak the param a lot and chage the features but it just would not work for this model. I therefore, turned myself into SVM that is also well designed for a problem with few data and lot of dimension.


#I did include my code for svm, but I actually mostly used it during my exploratory phase where I was trying to do a rapid feature selection
##Need to do cross validation and parameter tunning and refraction of code
  xnam <- paste(names(data[-indy]), sep="")
  fmla <- as.formula(paste(y," ~ ", paste(xnam, collapse= "+")))

  #confusion matrix
  #Create the variables names
  #store it
  assign(name.clf,svm.clf,envir = .GlobalEnv)
  assign(name.pred,svm.pred,envir = .GlobalEnv)
  assign(name.conf,svm.conf,envir = .GlobalEnv)


## Confusion Matrix and Statistics
##       0   1
##   0 906 397
##   1 377 734
##                Accuracy : 0.6794         
##                  95% CI : (0.6603, 0.698)
##     No Information Rate : 0.5315         
##     P-Value [Acc > NIR] : <2e-16         
##                   Kappa : 0.3555         
##  Mcnemar's Test P-Value : 0.4946         
##             Sensitivity : 0.7062         
##             Specificity : 0.6490         
##          Pos Pred Value : 0.6953         
##          Neg Pred Value : 0.6607         
##              Prevalence : 0.5315         
##          Detection Rate : 0.3753         
##    Detection Prevalence : 0.5398         
##       Balanced Accuracy : 0.6776         
##        'Positive' Class : 0              

#Things I didn't do: tune and tweak it.

Looking at the prediction (confusion matrix), SVM offers slight betters result than adaboost. Finally, I was wondering what random forest would give me, just to be sure which feature to look into more detail.

## Random Forest
##The idea behind using random forest was that it is a great tool for feature selection as well since the built in algorithm will automatically put aside predictor he doesn't like.
##However, you have to code dummy variables because I have too many level for some predictor
##It stills is really heavy because it create a lot of dummy variables and my old computer
##really did not enjoy it.
##The main variable of interest was country, I could have transformed it into continent to reduce the dimension.
##However, I feel like the final result would give informations way too broad
#Another alternative which I used was to drop it and only keep  variables with less than 54 classes

##dummy creation unused

#reduce the subset
ind.features.filter.rf<-names(ds[,unlist(lapply(ds,function (x)length(levels(x))<50))])

#random forest classifier
  xnam <- paste(names(data[-indy]), sep="")
  fmla <- as.formula(paste(y," ~ ", paste(xnam, collapse= "+")))

  #confusion matrix
  #Create the variables names
  #store it
  assign(name.clf,rf.clf,envir = .GlobalEnv)
  assign(name.pred,rf.pred,envir = .GlobalEnv)
  assign(name.conf,rf.conf,envir = .GlobalEnv)


## Confusion Matrix and Statistics
##        0    1
##   0 1562  289
##   1  214  349
##                Accuracy : 0.7916          
##                  95% CI : (0.7749, 0.8077)
##     No Information Rate : 0.7357          
##     P-Value [Acc > NIR] : 9.952e-11       
##                   Kappa : 0.4432          
##  Mcnemar's Test P-Value : 0.0009686       
##             Sensitivity : 0.8795          
##             Specificity : 0.5470          
##          Pos Pred Value : 0.8439          
##          Neg Pred Value : 0.6199          
##              Prevalence : 0.7357          
##          Detection Rate : 0.6471          
##    Detection Prevalence : 0.7668          
##       Balanced Accuracy : 0.7133          
##        'Positive' Class : 0               

My observation from the confluence matrix and the variable importance for its best prediction (which was ping.embed), showed me that random forest think that the OS,page domain and the browser are of interest.

#####Prediction conclusions:

After many iteration on R with different ranking method, I found out that it wasn’t possible to predict if someone play a video or completed it with an accurate rate. I even transformed the response by creating a new variable “launched” (which was play OR complete) but the result weren’t better (using naives bayes or adaboost with many different feature combinaison set). I concluded that the information given weren’t enough and I could just predict if the video was embed or not embed. This is due of a set really too unbalanced.
Nevertheless, I found out that the OS, scheme, page hostname and country were of interrest.
The next step is to dig into these variables to find some fun informations.

Since this analysis was supposed to be just a quick glance, I decided to just plot two three charts and not go into the detail.


Let’s start with some world map. We have seen that it was suposed to be related to embed<-ds %>% group_by(country) %>% summarize(n=n())
## 103 codes from your data successfully matched countries in the map
## 2 codes from your data failed to match with a country code in the map
## 140 codes from the map weren't represented in your data
mapCountryData(jc, nameColumnToPlot="n", mapTitle="Number of Observation per country",
  oceanCol="lightblue", missingCountryCol="lightgrey")
## You asked for 7 categories, 6 were used due to pretty() classification

We see that our data is actually very highly unbalanced and therefore must be correlated to the american behavior.
If you actually look into more detail and take the country that have more than 1% of the data. You find out that there is only indonesia and the US:

ggplot(data=filter(,n>.1*nrow(ds)),aes(x=country,y=n))+geom_histogram(stat="identity")+ggtitle("Number of observation for country that have more than 1% of the data")+ylab("Number of observation")

A lot more has to be done and now is kind of the funniest part. I quickly noticed that the US would either not load the video at all or play/complete it.

Note, I didn’t had time to finish the analysis but here are some table that are interesting even if we should filter them first because sometimes we have only one observation.

##           Android            Chrome     Chrome Mobile Chrome Mobile iOS 
##                28              3971                49                 2 
##          Chromium           Firefox    Firefox Mobile         Iceweasel 
##                27              6306                 1                 8 
##                IE         IE Mobile              Iron     Mobile Safari 
##              1029                13                 1               178 
##             Opera      Opera Mobile            Puffin            Safari 
##                52                 1                 1               625 
##        UC Browser 
##                 2
##                                 complete      embed  not.embed       play
##   Android Stock Browser       0.03571429 0.50000000 0.25000000 0.21428571
##   Chrome                      0.04543210 0.46543210 0.32098765 0.16814815
##   ChromeiOS                   0.00000000 1.00000000 0.00000000 0.00000000
##   Firefox                     0.13792557 0.11718131 0.58353127 0.16136184
##   Microsoft Internet Explorer 0.11228407 0.23704415 0.44817658 0.20249520
##   Opera                       0.01923077 0.57692308 0.25000000 0.15384615
##   Other                       0.00000000 0.75000000 0.00000000 0.25000000
##   Safari                      0.06491885 0.39575531 0.33957553 0.19975031
##           complete       embed   not.embed        play
##   flv  0.000000000 1.000000000 0.000000000 0.000000000
##   m3u8 0.013145540 0.399061033 0.399061033 0.188732394
##   m4a  0.033426184 0.635097493 0.169916435 0.161559889
##   mp3  0.009009009 0.252252252 0.576576577 0.162162162
##   mp4  0.114104291 0.226476814 0.490090437 0.169328459
##   smil 0.017391304 0.408695652 0.460869565 0.113043478
##                                   complete      embed  not.embed
##      0.07005348 0.47593583 0.27219251
##        0.09513606 0.33104778 0.39168631
## 0.04687500 0.54687500 0.23437500
##             0.14666352 0.01438340 0.68380099
##   0.01379310 0.42758621 0.35862069
##      0.00937500 0.50937500 0.37500000
##  0.01785714 0.32142857 0.53571429
##                0.00000000 0.00000000 0.50000000
##          0.00000000 0.50000000 0.00000000
##       0.00000000 0.73364486 0.00000000
##                   0.04000000 0.32000000 0.36000000
##         0.01739130 0.40869565 0.46086957
##               0.04641350 0.60337553 0.19831224
##                                       play
##      0.18181818
##        0.18212985
## 0.17187500
##             0.15515209
##   0.20000000
##      0.10625000
##  0.12500000
##                0.50000000
##          0.50000000
##       0.26635514
##                   0.28000000
##         0.11304348
##               0.15189873
##         complete     embed not.embed      play
##   http 0.1021380 0.2568064 0.4696843 0.1713713
##   rtmp 0.0093750 0.5093750 0.3750000 0.1062500
##       complete      embed  not.embed       play
##   0 0.10120884 0.25752397 0.47186328 0.16940392
##   1 0.04013378 0.49832776 0.28093645 0.18060201
## starting httpd help server ... done
##  [1] "id"                 "date"               "user.agent"        
##  [4] "browser"            "country"            "page.url"          
##  [7] "title"              "video.url"          ""          
## [10] "ping.embed"         ""          "ping.complete"     
## [13] "ua.device.brand"    "ua.device.model"    ""  
## [16] "ua.os.major"        "ua.os.minor"        ""      
## [19] "ua.os.patch"        ""        ""       
## [22] ""       ""        "page.url.scheme"   
## [25] "page.url.hostname"  "page.url.path"      "page.url.query"    
## [28] "page.url.fragment"  "page.url.ext"       "video.url.scheme"  
## [31] "video.url.hostname" "video.url.port"     "video.url.path"    
## [34] "video.url.query"    "video.url.ext"      "mobile"            
## [37] "ping.not.embed"     "ping"

Playing with R and Facebook Graph API


Playing with facebook

Tuesday, December 09, 2014

##This is a quick example of some data exploration with facebook

We will get my the posts/stories, and try to find out if there is some kind of relationship between the number of comments and the number of likes. The collection of my posts was inspired by with some modification to adapt to the lastest (v2.2) graph API queries.

# go to '' to get your access token
facebook <- function(query,token){
  myresult <- list()
  i <- 0 
  next.path<-sprintf( "",query, access_token)
  # download all my posts
  while(length(next.path)!=0) {
    #You might get some unexpected escape (with warning), you should keep them
    myresult[[i]]<-fromJSON(getURL(next.path, ssl.verifypeer = FALSE, useragent = "R" ),unexpected.escape = "keep")
  return (myresult)  
## Warning in fromJSON(getURL(next.path, ssl.verifypeer = FALSE, useragent =
## "R"), : unexpected escaped character 'm' at pos 89. Keeping value.
## Warning in fromJSON(getURL(next.path, ssl.verifypeer = FALSE, useragent =
## "R"), : unexpected escaped character 'o' at pos 51. Keeping value.
# parse the list, extract number of likes/comments and the corresponding text (status) and id
parse.master <- function(x, f)
  sapply(x$data, f)
parse.likes <- function(x) if(!['likes.summary.total_count'])) (as.numeric(unlist(x)['likes.summary.total_count'])) else 0
mylikes <- unlist(sapply(myposts, parse.master, f=parse.likes))
parse.comments <- function(x) if(!['comments.summary.total_count'])) (as.numeric(unlist(x)['comments.summary.total_count'])) else 0
mycomments <- unlist(sapply(myposts, parse.master, f=parse.comments))
parse.messages <- function(x) if(!is.null(x$message)){ x$message} else{if(!is.null(x$story)){x$story} else {NA}}
mymessages <- unlist(sapply(myposts, parse.master, f=parse.messages)) <- function(x) if(!is.null(x$id)){ x$id} else{NA}
myid <- unlist(sapply(myposts, parse.master,
parse.time <- function(x) if(!is.null(x$created_time)){x$created_time} else{NA}
mytime <- unlist(sapply(myposts, parse.master, f=parse.time))
#put everything into a data.frame

Now we can play with the data :-)

#most commented
##                                postId
## 525 10152254267528671_128457423841248
##                                     message likes.count comments.count
## 525 sera à grenoble l'an prochain. Fuck it.           0             18
##                    time year dom hour        wd month
## 525 2010-06-09 16:38:02 2010   9   16 Wednesday  June
#most liked
##                                 postId
## 39 10152254267528671_10152119139903671
##                                                message likes.count
## 39 Internship found. Apartment found. What's next ? :)          18
##    comments.count                time year dom hour      wd month
## 39              5 2014-05-20 00:41:34 2014  20    0 Tuesday   May

Let’s look into the relationship between post and comments
Let’s group them by comments number and sum their likes number to have a better idea of the relationship. <- fbPosts %>% group_by(comments.count) %>% summarise(sumLikes=sum(likes.count),posts=n()) %>% ungroup() %>% arrange(comments.count)
g1<-ggplot(,aes(x=comments.count,y=sumLikes))+geom_point(aes(size=posts))+geom_smooth(method="glm", family="quasipoisson")+xlab("Number of comments")+ylab("Number of likes")+ggtitle("Comments and likes per post")+scale_x_continuous(breaks=seq(0,50,5))+scale_y_continuous(breaks=seq(0,200,25))

  return (ds %>% group_by_(fc) %>% summarise_(sumComment=interp(~sum((comments.count))),posts=interp(~n()),ratioC=interp(~sumComment/posts),sumLike=interp(~sum(likes.count)),ratioL=interp(~sumLike/posts)) %>% ungroup() %>% arrange_(fc))  
  return (
    ggplot(data=ds,environment = environment())+geom_point(aes_string(size="posts",x=(xvar),y=("sumLike")))+geom_line(aes_string(x=(xvar),y="sumLike"))+geom_point(aes_string(x=(xvar),y="sumComment",size="posts"),color="red")+geom_line(aes_string(x=(xvar),y="sumComment"),color="red")+xlab(paste(xvar))+ylab("Number of likes and comments")+ggtitle(paste("Number of likes (black) and comments (red) per",xvar))
  return (
    ggplot(data=ds,environment = environment())+geom_point(aes_string(size="posts",x=(xvar),y=("ratioL")))+geom_line(aes_string(x=(xvar),y="ratioL"))+geom_point(aes_string(x=(xvar),y="ratioC",size="posts"),color="red")+geom_line(aes_string(x=(xvar),y="ratioC"),color="red")+xlab(paste(xvar))+ylab("Ratio likes/post and comments/post")+ggtitle(paste("Number of likes (black) and comments (red) per post per",xvar))

## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?

In my case, we can see that there is a higher concentration of low comment posts.
We can see that There is an inverse relationship with the number of like and number of comments. We also see some interesting relationship with the time series.

Why do you think it is so?

Playing with R – EDA analysis for a job


Set up

opts_knit$set( = imgur_upload, base.url = NULL) # upload all images to

This is the global set up for the code.


## Warning in fread("episode_series_review.csv", header = T, sep = ","):
## Stopped reading at empty line 83846 of file, but text exists afterwards
# df <- read.csv("watched",nrows=20000,header=T,sep="|")

##Various Fixes

#fixing gender
ds$gender.fix[ds$gender.fix=="f"]  <- "F" 
ds$gender.fix[ds$gender.fix=="m"]  <- "M"
ds$gender.fix[ds$gender.fix==""]  <- NA 
#Fixing empty string
ds$promo_code.fix[ds$promo_code.fix==""]  <- NA 

#fixing id to name
ds$country_id <- epCountry$name[match(ds$country_id, epCountry$id)]
#fixing dates
options(chron.year.expand = 
     function (y, = 13, century = c(1900, 2000), ...) {
        chron:::year.expand(y, =, century = century, ...)
ds$<-as.Date(chron(format(as.Date(ds$dob, "%m/%d/%y"), "%m/%d/%y")))



Looking at the progression of premium worlwide

ds.fc_by_date_region<- ds %>% filter(! %>% group_by(,region_id) %>% summarise(n=n()) %>% ungroup() %>% arrange(
(region<-ggplot(aes(x =, y = n),data = ds.fc_by_date_region) +   geom_line()+facet_wrap(~region_id)+xlab("Date become premium")+ylab("Number of premium")+ggtitle("Premium joined per region"))


In term of raw premium number Latin America is doing the best. It is interesting to note that theere is a number of country that are not referenced. Let’s focus on Latin America

Latin America momentum<-subset(ds,region_id=="Latin America")$<-as.Date($premium_since,format="%m/%d/%y")<- %>% filter(! ! %>% group_by(,country_id) %>% summarise(n=n()) %>% ungroup() %>% arrange(
(momentum<-ggplot(aes(x =, y = n),data = +   geom_line()+facet_wrap(~country_id)+ggtitle("Customers who became premium")+ylab("Number of premium")+xlab("Date became premium in Latin America"))


We notice that Peru and Mexico are leading, let’s look into more detail

g1_Mexi<-ggplot(aes(x =, y = n),data = filter(,country_id=="Mexico",>c("2014-05-01"))) +   geom_point(alpha=1/2)+geom_smooth()+ggtitle("Customers who became premium for Mexico")+ylab("Number of premium")+xlab("2014 Date became premium")+coord_cartesian()
g1_Peru<-ggplot(aes(x =, y = n),data = filter(,country_id=="Peru")) +   geom_point()+geom_smooth()+ggtitle("Customers who became premium for Peru")+ylab("Number of premium")+xlab("2014 Date became premium")
## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.
## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.


We can see that both country have more and more premium customers. These two countries are interesting because their growth is significative. If we look back into Latin America Chart, we can see that these are the one with significant amount of data that have the highest growth. Let’s look about the gender.

##Focusing on Mexico these both countries


ds.mexico.by_gender<- ds.mexico %>% filter(! %>% group_by(,gender.fix) %>% summarise(n=n()) %>% ungroup() %>% arrange(

ds.peru.by_gender<- ds.peru %>% filter(! %>% group_by(,gender.fix) %>% summarise(n=n()) %>% ungroup() %>% arrange(

g2_Mexi<-ggplot(aes(x =, y = n,color=gender.fix),data = ds.mexico.by_gender) +   geom_point(alpha=1/2)+ggtitle("Customers who became premium for Mexico")+ylab("Number of premium")+xlab("2014 Date became premium")+coord_cartesian()+scale_y_continuous(breaks=seq(0,300,25))
g2_Peru<-ggplot(aes(x =, y = n,color=gender.fix),data = ds.peru.by_gender) +   geom_point(alpha=1/2)+ggtitle("Customers who became premium for Peru")+ylab("Number of premium")+xlab("2014 Date became premium")+coord_cartesian()+scale_y_continuous(breaks=seq(0,300,25))


we can’t see clearly but it seems that women are leading it and the gap stay constant let’s look into it.

ds.peru.fc_gender.all<-ds.peru %>% group_by(,gender.fix) %>% filter(! %>% summarise(n=n()) %>% ungroup() %>% arrange(
ds.peru.fc_gender.all.wide<-dcast(ds.peru.fc_gender.all,,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.peru.fc_gender.all.wide<-ds.peru.fc_gender.all.wide %>% group_by( %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(

ds.peru.fc_gender.nopremanymore<-ds.peru %>% group_by(,gender.fix) %>% filter(!,is_premium==0,! %>% summarise(n=n()) %>% ungroup() %>% arrange(
ds.peru.fc_gender.nopremanymore.wide<-dcast(ds.peru.fc_gender.nopremanymore,,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.peru.fc_gender.nopremanymore.wide<-ds.peru.fc_gender.nopremanymore.wide %>% group_by( %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(

#g.fc_gender.prem<-ggplot(data=ds.age.fc_gender.prem.wide,aes(x=age,y=percentF))+geom_smooth()+geom_hline(yintercept=1,alpha=0.2,linetype=2)+coord_cartesian(ylim=c(0,1),xlim=c(15,50))+ylab("Premium %girls")+scale_x_continuous(breaks=seq(0,50,2))+geom_point(alpha=1/50)
g.peru.fc_gender<-ggplot(data=ds.peru.fc_gender.all.wide,aes(,y=percentF))+geom_line()+geom_hline(yintercept=1,alpha=0.2,linetype=2)+coord_cartesian(ylim=c(0,1))+ylab("%girls")+scale_y_continuous(breaks=seq(0,1,.05))+geom_line(data=ds.peru.fc_gender.nopremanymore.wide,aes(,y=percentF),colour="green")+ggtitle("%girls by date joined as premium for Peru (green=was_premium,black=all premium)")+xlab("Date joined as premium")

ds.mexico.fc_gender.all<-ds.mexico %>% group_by(,gender.fix) %>% filter(! %>% summarise(n=n()) %>% ungroup() %>% arrange(
ds.mexico.fc_gender.all.wide<-dcast(ds.mexico.fc_gender.all,,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.mexico.fc_gender.all.wide<-ds.mexico.fc_gender.all.wide %>% group_by( %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(

ds.mexico.fc_gender.nopremanymore<-ds.mexico %>% group_by(,gender.fix) %>% filter(!,is_premium==0,! %>% summarise(n=n()) %>% ungroup() %>% arrange(
ds.mexico.fc_gender.nopremanymore.wide<-dcast(ds.mexico.fc_gender.nopremanymore,,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.mexico.fc_gender.nopremanymore.wide<-ds.mexico.fc_gender.nopremanymore.wide %>% group_by( %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(

#g.fc_gender.prem<-ggplot(data=ds.age.fc_gender.prem.wide,aes(x=age,y=percentF))+geom_smooth()+geom_hline(yintercept=1,alpha=0.2,linetype=2)+coord_cartesian(ylim=c(0,1),xlim=c(15,50))+ylab("Premium %girls")+scale_x_continuous(breaks=seq(0,50,2))+geom_point(alpha=1/50)
g.mexico.fc_gender<-ggplot(data=ds.mexico.fc_gender.all.wide,aes(,y=percentF))+geom_line()+geom_hline(yintercept=1,alpha=0.2,linetype=2)+coord_cartesian(ylim=c(0,1))+ylab("%girls")+scale_y_continuous(breaks=seq(0,1,.05))+geom_line(data=ds.mexico.fc_gender.nopremanymore.wide,aes(,y=percentF),colour="green")+ggtitle("%girls by date joined as premium for Mexico (green=was_premium,black=all premium)")+xlab("Date joined as premium")


We don’t see any notifiable difference. Except recently

## Source: local data frame [6 x 7]
##   F  M N  percentF  percentM percentN
## 1         2014-10-08 102 19 0 0.8429752 0.1570248        0
## 2         2014-10-09  66 13 0 0.8354430 0.1645570        0
## 3         2014-10-11   1  0 0 1.0000000 0.0000000        0
## 4         2014-10-13   1  0 0 1.0000000 0.0000000        0
## 5         2014-10-15   1  0 0 1.0000000 0.0000000        0
## 6         2014-10-22   1  0 0 1.0000000 0.0000000        0
## Source: local data frame [6 x 7]
##   F  M N  percentF  percentM percentN
## 1         2014-10-08 151 18 0 0.8934911 0.1065089        0
## 2         2014-10-09  61 14 0 0.8133333 0.1866667        0
## 3         2014-10-12   1  0 0 1.0000000 0.0000000        0
## 4         2014-10-14   3  0 0 1.0000000 0.0000000        0
## 5         2014-10-15   1  0 0 1.0000000 0.0000000        0
## 6         2014-10-18   1  0 0 1.0000000 0.0000000        0

We can’t see that we actually don’t have much data which explain the difference at the end.
Therefore, we can say that there is a majority of girls and there is no pick when they became premium in term of gender. Let’s now evaluate how long they generally stay premium. In order to do that there are different way to do it. We can look into the the next pay date. If the next pay date is less than max(last_active,last_login) (we assume someone either login or was active the last day the data were taken) it means they are not premium anymore because they don’t pay the premium membership anymore (assuming you don’t allow people to pay in more than once for other products). Another way of looking at it is to look at people who joined as premium ( not NA) but are not premium anymore (is_premium=0).

la<- max(c(max(ds$,na.rm = T),max(ds$,na.rm = T)))
## [1] 6249

We can see some issues with the data right away

## [1] 3932

We have 3932 data that are reported to be premium but the next pay date is inferior as of the last day the data were taken.

## [1] 124247

Additionally, we have 124247 which has been reported as being premium one day since is not NA and have the is_premium at false which mean they are not premium anymore.
It is hard to make sens out of these data but we will assume that the boolean is_premium and the are more relevant than the first assumption since the first assumption assume that people pay everymonth when they might be able to pay once and leave and then come back. Therefore it could not be possible to monitor when they will pay next time.

##  30  31 365 
##   1   5   3

We can see that we don’t have much information concerning their subscription habit in term of frequency to pay.
However, we have seen the in Latin America, Peru and Mexico. We have a majority of girls that stay equally in term of percentage compared to male with a market with a growing number of premium. Therefore, advertising specially for these two country could be interesting. Let’s look if they are more attracted by one promo rather than another and also what kind of subscription they register to.

##Promo related to acquisition<- ds %>% filter(!,is_premium==1,>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(
promoForActualPremium<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+ggtitle("Effect of promotion on premium customers who are still premium (true=joined with a promo code, false=without)")+xlab("Joined date  as premium in 2014")+ylab("Number of premium")<- ds %>% filter(!,is_premium==0,>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(
promoForInactivePremium<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+ggtitle("Effect of promotion on premium customers who are not premium anymore (true=joined with a promo code, false=without)")+xlab("Joined date  as premium in 2014")+ylab("Number of premium")

We can clearly see that user who decided to stop being premium are the one who didn’t join with a promo code. Therefore, in order to retain our user, we should make sure that they join with a promo code.

Let’s look into Peru and Mexico.<- ds %>% filter(!,is_premium==1,country_id=="Mexico",>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(<- ds %>% filter(!,is_premium==0,country_id=="Mexico",>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(<- ds %>% filter(!,is_premium==1,country_id=="Peru",>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(<- ds %>% filter(!,is_premium==0,country_id=="Peru",>c("2014-04-01")) %>% group_by(,promo=! %>% summarise(n=n()) %>% ungroup() %>% arrange(<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+xlab("Date joined as premium in 2014")+ylab("Number of premium")+ggtitle("Number of premium who are still premium by date joined in Mexico")<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+xlab("Date joined as premium in 2014")+ylab("Number of premium")+ggtitle("Number of premium who are not premium anymore by date joined in Mexico")<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+xlab("Date joined as premium in 2014")+ylab("Number of premium")+ggtitle("Number of premium who are still premium by date joined in Peru")<-ggplot(aes(x =, y = n,color=promo),data = +   geom_point()+xlab("Date joined as premium in 2014")+ylab("Number of premium")+ggtitle("Number of premium who are not premium anymore by date joined in Peru")


We can see that there is not one person who joined with a promo code and is not premium anymore. However, the effect of promo code in Mexico and Peru is very low. If we assume that we didn’t do special marketing campaign on this demographic area, we should then consider doing it for specially these two countries because this is how globally we retain our customer from leaving.

Let’s look at successfull promo.

#same<- ds %>% filter(!,>c("2014-04-01")) %>% group_by(,promo_code) %>% summarise(n=n()) %>% ungroup() %>% arrange(

#allCampaign<-ggplot(aes(x =, y = n,color=promo_code),data = +   geom_point()
#organic are leading

bottomCampaign<-ggplot(aes(x =, y = n,color=promo_code),data = filter(,n<100,n>10,promo_code!="")) +   geom_point() + ggtitle("effect on promo (who attracted more than 10 customer in one day per date joined")+xlab("Date joined as premium in 2014")+ylab("Number of premium")

organic<-ggplot(aes(x =, y = n,color=promo_code),data = filter(,promo_code=="")) +   geom_point()+ggtitle("effect on organic campaign customer in one day per date joined")+xlab("Date joined as premium in 2014")+ylab("Number of premium")


We can see that the 5 year promo was the most successfull promotion and organic premium seem to be affected by the season (less in summer when they go in vacation). Therefore, I’d recommand to do more marketing as the 5YEARS promo marketing campaign and targeted marketing to Peru and Mexico that respect their values.

##Let’s look at their age.

length(filter(ds,Sys.Date()< |$$
## [1] 0.1840954

looking at the date of birth we can note that most of them are inacurrate
About 18.5% of the date is wrong

## [1] 4.880942
## [1] 4.204354

Moreover, there isn’t a gap between the gender of the data we have and the one we ignore (the 18.5% ignored are not only male/female)

Let’s now calculate their age and look at the global trends.

#We trunc the data because it does not add any value to not do it and it allows us to group the data more and have better prediction
#fixing gender
#ds.age$gender.fix[ds.age$gender.fix=="f"]  <- "F" 
#ds.age$gender.fix[ds.age$gender.fix=="m"]  <- "M"
#ds.age$gender.fix[ds.age$gender.fix==""]  <- NA 
g.age.all<-ggplot(aes(x=age,fill=gender.fix),data=ds.age)+geom_histogram(binwidth=1)+scale_x_continuous(breaks=seq(0,100,5))+xlab("Age in years")+ylab("Number of customer")+ggtitle("Age related to number of customers")
g.age.trunc<-ggplot(aes(x=age,fill=gender.fix),data=ds.age)+geom_histogram(binwidth=1)+coord_cartesian(xlim=c(15,30))+scale_x_continuous(breaks=seq(0,100,1))+xlab("Age in years")+ylab("Number of customer")+ggtitle("Age related to number of customers")

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    2.00   20.00   24.00   27.93   32.00  102.00

We can clearly see that woman are more attracted to our product and that our main customers database are between 18 and 25.

Let’s take a demographic broader look


We can see that Latin America have way more girls in percentage than boys than US/Canada.
There is a wrong region id that we should ignore, since North America is supposed to be active, we could assume the NA belong to them (id=8). Let’s try to find out if we can have the country id for our NA

## [1] NA
## [1] 243536

We can see that we can’t get the country id. However, there are too many data in these rows. We can’t ignore them. Since, we want to see globally if there is a country that have more men than women, let’s keep it.
Let’s take a demographic look to see if there are main differences between countries that have enough data (more than 10k)

#No country with special outlet
countryList<-unique((ds.age %>% group_by(country_id)%>%filter(!,n()>10000)%>%ungroup())$country_id)

ggplot(aes(x=age,fill=gender.fix),data=filter(ds.age,!,country_id %in% countryList))+geom_histogram(binwidth=1)+coord_cartesian()+facet_wrap(~country_id)

We can see that our main customers are from Canada, Indonesia, Malaysia,Mexico, Peru, Philippines and USA with an important number of unknown.

There isn’t a main difference globally.

Now let’s look in term of monetization.

g.age.noprem<-ggplot(aes(x=age,fill=gender.fix),data=filter(ds.age,!,is_premium==0))+geom_histogram(binwidth=1)+coord_cartesian()+ylab("Number of customer not premium")+scale_x_continuous(breaks=seq(0,100,5))+xlab("Age in year")+ggtitle("Number of customer not premium per age")

g.age.prem<-ggplot(aes(x=age,fill=gender.fix),data=filter(ds.age,!,is_premium==1))+geom_histogram(binwidth=1)+coord_cartesian()+ylab("Number of customer premium")+scale_x_continuous(breaks=seq(0,100,5))+ggtitle("Number of customer premium per age")

We can see that globally the trend is the same except it seems like there are more men that monetized than girl compared to the custumer not premium. Let’s look into more detail.

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    2.00   19.00   23.00   27.37   31.00  102.00
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    2.00   20.00   23.00   26.96   30.00  102.00

As we said no major difference in term of age between premium and not premium. Let’s look in term of frequencies

ds.age.fc_convert<-ds.age %>% group_by(age,is_premium) %>% filter(! %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_convert.wide<-dcast(ds.age.fc_convert,age~is_premium,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_convert.wide<-ds.age.fc_convert.wide %>% group_by(age) %>% mutate(percentConversionRate=prem/(nonprem+prem)) %>% ungroup() %>% arrange(age)

g.fc_convert.all<-ggplot(data=ds.age.fc_convert.wide,aes(x=age,y=percentConversionRate),breaks=seq(10,60,5))+geom_smooth()+coord_cartesian(ylim=c(0,0.40),xlim=c(10,80))+ylab("% of premium conversion")+scale_x_continuous(breaks=seq(0,80,2))+geom_line()+ggtitle("Conversion rate by age for male and female")+scale_y_continuous(breaks=seq(0,0.4,0.05))

ds.age.fc_convert.male<-ds.age %>% group_by(age,is_premium) %>% filter(!,gender.fix=="M") %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_convert.male.wide<-dcast(ds.age.fc_convert.male,age~is_premium,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_convert.male.wide<-ds.age.fc_convert.male.wide %>% group_by(age) %>% mutate(percentConversionRate=prem/(nonprem+prem)) %>% ungroup() %>% arrange(age)

g.fc_convert.male<-ggplot(data=ds.age.fc_convert.male.wide,aes(x=age,y=percentConversionRate),breaks=seq(10,60,5))+geom_smooth()+coord_cartesian(ylim=c(0,0.40),xlim=c(10,80))+ylab("% of premium conversion")+scale_x_continuous(breaks=seq(0,80,2))+geom_line()+ggtitle("Conversion rate by age for male")+scale_y_continuous(breaks=seq(0,0.4,0.05))

ds.age.fc_convert.female<-ds.age %>% group_by(age,is_premium) %>% filter(!,gender.fix=="F") %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_convert.female.wide<-dcast(ds.age.fc_convert.female,age~is_premium,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_convert.female.wide<-ds.age.fc_convert.female.wide %>% group_by(age) %>% mutate(percentConversionRate=prem/(nonprem+prem)) %>% ungroup() %>% arrange(age)

g.fc_convert.female<-ggplot(data=ds.age.fc_convert.female.wide,aes(x=age,y=percentConversionRate),breaks=seq(10,60,5))+geom_smooth()+coord_cartesian(ylim=c(0,0.40),xlim=c(10,80))+ylab("% of premium conversion")+scale_x_continuous(breaks=seq(0,80,2))+geom_line()+ggtitle("Conversion rate by age for female")+scale_y_continuous(breaks=seq(0,0.4,0.05))

## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.
## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.
## geom_smooth: method="auto" and size of largest group is <1000, so using loess. Use 'method = x' to change the smoothing method.

The 19,31 are our main customers and there is no significant change in term of conversion rate or gender for them. However, we do not convert them as much as the older customers. We also see a high conversion rate for the under 18 (with a pick at 12), which might be either fake data or the parent that pay for them. Therefore, it is harder to convert 19,28 but we have a bigger pool of data for them.

It seems also that the gender has no effect on the conversion rate.

Now let’s look if there is a notable difference in term of percentage of girl that is/was premium and the one who are not.

ds.age.fc_gender.noprem<-ds.age %>% group_by(age,gender.fix) %>% filter(!,is_premium==0) %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_gender.noprem.wide<-dcast(ds.age.fc_gender.noprem,age~gender.fix,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_gender.noprem.wide<-ds.age.fc_gender.noprem.wide %>% group_by(age) %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(age)

ds.age.fc_gender.prem<-ds.age %>% group_by(age,gender.fix) %>% filter(!,is_premium==1) %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_gender.prem.wide<-dcast(ds.age.fc_gender.prem,age~gender.fix,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_gender.prem.wide<-ds.age.fc_gender.prem.wide %>% group_by(age) %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(age)

ds.age.fc_gender.nopremanymore<-ds.age %>% group_by(age,gender.fix) %>% filter(!,is_premium==0,! %>% summarise(n=n()) %>% ungroup() %>% arrange(age)
ds.age.fc_gender.nopremanymore.wide<-dcast(ds.age.fc_gender.nopremanymore,age~gender.fix,value.var="n",fun.aggregate = sum, na.rm = TRUE)
ds.age.fc_gender.nopremanymore.wide<-ds.age.fc_gender.nopremanymore.wide %>% group_by(age) %>% mutate(percentF=F/(F+M+N),percentM=M/(F+M+N),percentN=N/(F+M+N)) %>% ungroup() %>% arrange(age)

#g.fc_gender.prem<-ggplot(data=ds.age.fc_gender.prem.wide,aes(x=age,y=percentF))+geom_smooth()+geom_hline(yintercept=1,alpha=0.2,linetype=2)+coord_cartesian(ylim=c(0,1),xlim=c(15,50))+ylab("Premium %girls")+scale_x_continuous(breaks=seq(0,50,2))+geom_point(alpha=1/50)
g.fc_gender.noprem+geom_line(data=ds.age.fc_gender.prem.wide,aes(x=age,y=percentF),colour="red")+geom_line(data=ds.age.fc_gender.nopremanymore.wide,aes(x=age,y=percentF),colour="green")+ggtitle("%girls by age (red=premium,green=was_premium,black=not premium)")

We can see that in proportion we have more girl than guys that are premium than not. Moreover, girls tend to convert more than guy especially when they get older. Therefore, girls who try our product are more likely to become premium members. Finally, we can see that there are more boy than girls that join our website as they get older. An assumption could be that they do register for their kid and the male do it as (regretfully) male still have the more income than women in average in couple (at least in Europe:

Therefore, we can conclude that we should also do target marketing our advertisements for couple who have young children and especially the male in the couple who is 50+.

##Where is our conversion rate the most important?

(sort(table(filter(ds)$country_id),decreasing = T))[1:3]
## United States   Philippines        Canada 
##        170203         30696         24130
(sort(table(filter(ds,is_premium==1)$country_id),decreasing = T))[1:3]
## United States          Peru        Mexico 
##          9457          3210          2545
(sort(table(filter(ds,is_premium==0)$country_id),decreasing = T))[1:3]
## United States   Philippines      Malaysia 
##        160746         30678         23208

We can see that USA, Peru, Mexico, Canada are our main customers as we said earlier.
Let’s look who got the best conversion rate (people who have the best ratio premium/customer base). In order to do that we will focus on these who have enough observation in order to it to make sens (>20000).

ds.prem_fc<-ds %>% group_by(country_id) %>% summarise(premiumPerc=sum(is_premium)/n(),number_observation=n()) %>% ungroup() %>% arrange(country_id)

g.premium.conversion<-ggplot(data=filter(ds.prem_fc,number_observation>20000),aes(x=country_id,y=premiumPerc))+geom_histogram(stat="identity")+xlab("Country Name")+ylab("Percentage of conversion to premium")+ggtitle("Conversion rate by country for country who have more than 2k observations")
g.premium.nbObservation<-ggplot(data=filter(ds.prem_fc,number_observation>20000),aes(x=country_id,y=number_observation))+geom_histogram(stat="identity")+xlab("Country Name")+ylab("Number of customers")+ggtitle("Number of customers by country for country who have more than 2k observations")


As we have seen so far, Peru and Mexico have very high potential since their conversion rate are the highest. Canada and the USA are following them. However, the USA has way more customer than the others. Therefore, focusing on them primarely make sens. Additionally, we should look into demographically where these users are to do special targeting campaign only for these cities.

##Looking into USA states and cities


ds.zipmap.premium<-ds %>% filter(!,zip!="",country_id=="United States",is_premium==1) %>% group_by(zip) %>% summarise(valuePostal=n())
ds.zipmap.premium<-ds.zipmap.premium %>% filter(! %>% group_by(zip.fix) %>% summarise(value=sum(valuePostal))
ds.zipmap.premium$region<-ds.zipmap.premium$zip.fix<-zip_map(ds.zipmap.premium,buckets=9,zoom    = c("california","florida","new york","texas"))+ggtitle("Premium in California, Florida, New York and Texas")+scale_color_brewer(name="Customer", palette=8)
## Scale for 'colour' is already present. Adding another scale for 'colour', which will replace the existing scale.<-zip_map(ds.zipmap.premium,buckets=9)+ggtitle("United State premium")+scale_color_brewer(name="Customer", palette=8)
## Scale for 'colour' is already present. Adding another scale for 'colour', which will replace the existing scale.
ds.zipmap.premium$city <- zipcode$city[match(as.character(ds.zipmap.premium$region), as.character(zipcode$zip))]
ds.zipmap.premium.plot<-ds.zipmap.premium %>% group_by(city) %>% summarise(value=sum(value)) %>% ungroup() %>% arrange(city)<-ggplot(data=filter(ds.zipmap.premium.plot,value>100),aes(x=city,y=value))+geom_histogram(stat="identity")+scale_y_continuous(breaks=seq(0,30000,50))+ggtitle("Number of premium for the top cities")+xlab("City name")+ylab("Number of premium")

ds.zipmap.premium$ <- zipcode$state[match(as.character(ds.zipmap.premium$region), as.character(zipcode$zip))]
ds.zipmap.premium$state <- state.regions$region[match(as.character(ds.zipmap.premium$, as.character(state.regions$abb))]
ds.statemap.premium<-ds.zipmap.premium %>% group_by(state) %>% summarise(value=sum(value))

g.state.premium<-state_choropleth(ds.statemap.premium,buckets = 9)+ggtitle("Premium per state")
g.state.premium.state<-ggplot(data=filter(ds.statemap.premium,value>300),aes(x=state,y=value))+geom_histogram(stat="identity")+scale_y_continuous(breaks=seq(0,30000,250))+ggtitle("Number of premium for the top states")+xlab("City name")+ylab("Number of premium")

ds.zipmap.not_prem<-ds %>% filter(!,zip!="",country_id=="United States",is_premium==0) %>% group_by(zip) %>% summarise(valuePostal=n())
ds.zipmap.not_prem<-ds.zipmap.not_prem %>% filter(! %>% group_by(zip.fix) %>% summarise(value=sum(valuePostal))
ds.zipmap.not_prem$region<-ds.zipmap.not_prem$zip.fix<-zip_map(ds.zipmap.not_prem,buckets=9,zoom    = c("california","florida","new york","texas"))+ggtitle("not_prem in California, Florida, New York and Texas")+scale_color_brewer(name="Customer", palette=8)
## Scale for 'colour' is already present. Adding another scale for 'colour', which will replace the existing scale.<-zip_map(ds.zipmap.not_prem,buckets=9)+ggtitle("United State not premium")+scale_color_brewer(name="Customer", palette=8)
## Scale for 'colour' is already present. Adding another scale for 'colour', which will replace the existing scale.
ds.zipmap.not_prem$city <- zipcode$city[match(as.character(ds.zipmap.not_prem$region), as.character(zipcode$zip))]

ds.zipmap.not_prem.plot<-ds.zipmap.not_prem %>% group_by(city) %>% summarise(value=sum(value)) %>% ungroup() %>% arrange(city)<-ggplot(data=filter(ds.zipmap.not_prem.plot,value>1500),aes(x=city,y=value))+geom_histogram(stat="identity")+scale_y_continuous(breaks=seq(0,30000,500))+ggtitle("Number of not premium for the top cities")+xlab("City name")+ylab("Number of not premium")

ds.zipmap.not_prem$ <- zipcode$state[match(as.character(ds.zipmap.not_prem$region), as.character(zipcode$zip))]
ds.zipmap.not_prem$state <- state.regions$region[match(as.character(ds.zipmap.not_prem$, as.character(state.regions$abb))]
ds.statemap.not_prem<-ds.zipmap.not_prem %>% group_by(state) %>% summarise(value=sum(value))

g.state.not_prem<-state_choropleth(ds.statemap.not_prem,buckets = 9)+ggtitle("not premium per state")
g.state.not_prem.state<-ggplot(data=filter(ds.statemap.not_prem,value>4000),aes(x=state,y=value))+geom_histogram(stat="identity")+scale_y_continuous(breaks=seq(0,30000,2500))+ggtitle("Number of not premium for the top states")+xlab("City name")+ylab("Number of not premium")

ds.zipmap.all<-ds.zipmap.all %>% group_by(city) %>% summarise(percentPrem=sum(prem_value)/(sum(prem_value)+sum(not_prem_value)),observation=sum(prem_value)+sum(not_prem_value)) %>% ungroup() %>% arrange(city)<-ggplot(data=(filter(ds.zipmap.all,observation>2000)),aes(x=city,y=percentPrem))+geom_histogram(stat="identity")+ggtitle("Conversion rate in city with more than 2k customers")+xlab("City name")+ylab("Conversion rate")<-ggplot(data=(filter(ds.zipmap.all,observation>2000)),aes(x=city,y=observation))+geom_histogram(stat="identity")+ggtitle("Number of customer in city with more than 2k customers")+xlab("City name")+ylab("Number of customer")





We can see that in the USA, we should focus on Houston and New York that have respectively the highest number of conversion for a “big city” and the highest number of customer.
If we keep our logic, we should try to attrack young female people with promotion.


prem.fc_by_date_platform<- prem %>% filter(! & ! %>% group_by(date,platform) %>% summarise(n=n()) %>% ungroup() %>% arrange(date)
churnplat<-ggplot(aes(x = date, y = n),data = prem.fc_by_date_platform) +   geom_line()+facet_wrap(~platform)+ggtitle("Number of comments in time per platform")+ylab("Number of comment")+xlab("Date for year 2014")
churn_www<-ggplot(aes(x = date, y = n),data = filter(prem.fc_by_date_platform,platform=="www",date>c("2014-04-01"))) +   geom_point()+ggtitle("Number of comments in time for www")+ylab("Number of comment")+xlab("Date for year 2014")
## geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?


Churn tell us that we have to focus on our www platform as it is our highest.

Focusing on Canada and USA

#canada premium vs world
## [1] 4.218815
#premium vs all
## [1] 3.847
ds.fc_by_date_canada<- ds.canada %>% filter(! %>% group_by(,is_premium) %>% summarise(n=n()) %>% ungroup() %>% arrange(
#promocode ranking
sort(table(ds$promo_code),decreasing = T)[1:10]
##                              Awards2012              5YEARS 
##              628797                5229                3906 
##           FLASHSALE       DFLovesCanada       ThanksMom2013 
##                1972                1821                 674 
##            DFSale25 2011_08_email_promo      DFAnnualSale25 
##                 667                 620                 524 
##     studentdiscount 
##                 503
#5YEARS and Award2012 most famous

## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.

ggplot(data=filter(ds,country_id=="United States" | country_id=="Canada" ),aes(,fill=gender.fix))+geom_histogram()+facet_wrap(~country_id)
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.
## stat_bin: binwidth defaulted to range/30. Use 'binwidth = x' to adjust this.

We can see that there is a peak for the United State and Canada. We have seen previously this peak and we have seen that it wasn’t related to a promo code. It would be interresting to investigate further on what happenned during this period (was there a lunch of a mobile app..)

ds.prem.joined <- ds %>% filter(premium_join_delay>0,! %>% group_by(premium_join_delay,gender.fix) %>% summarise(n=n()) %>% ungroup() %>% arrange(premium_join_delay)

ds.prem.joined.wide<-dcast(ds.prem.joined,premium_join_delay~gender.fix,value.var="n",fun.aggregate = sum, na.rm = TRUE)

g.prem.joined<-ggplot(data=ds.prem.joined.wide,aes(x=premium_join_delay, y=(M)))+geom_point(color="red")+geom_point(data=ds.prem.joined.wide,aes(x=premium_join_delay, y=F),color="blue")+ggtitle("Time in day customer take between when they join and when they become premium, blue=women, red=men")+xlab("Time in number of days")+ylab("Number of premium converted")



We can see that they become premium in the first year and the more we wait the more unlikely they become premium. Therefore, we should focus our advertising to new user extensively.

In conclusion, we have seen that:

  • We should target female
  • We should target 20 to 30 female or 50+ familly male in couple who have young female child
  • We should target Mexico, Peru and Philippines
  • We should target Huston and New York
  • We should target people who just registered
  • We should do more promo code as the 5 Years or Award 2012 to retain users as they are more unlikely to leave

Raspberry pi too hot ? Let’s do something about it !



Today we will see what to do if your raspberry pi is too hot.

Is my Raspberry Pi too hot ?

This is a good question. The rapid answer: no if you don’t tweak or overclock it. By the way, here is a good tutorial to overclock it but be careful because you’ll lose warranty.

If you want a long answer, go here it is in Spanish but you can use google translation to have it in English. If you are interested, you should definitely read it !
Here are the hottest temperature observed during the test:


As you can see the main danger comes from the LAN9512 Ethernet/USB chip who can reaches about 65°C. This is too hot to touch for more than a few seconds, but the chip can go up to 70°C, so it’s still alright.

What can we do ?

There are several things you can do to cool down your raspberry pi. First, don’t overclock it, overclocking on a plug computer like the raspberry pi isn’t worth to lose warranty. Remember, it is not your home computer it can’t do everything.

Till, if you must overclock it here are two solution you could use in order to cool it down.
Buy or reuse Heatsinks. It is not expensive to buy, if it makes you feel less worried, you should do it.
Also, I like how it looks with heatsinks on it !

Finally, you can buy Iceberg case for 58€. If you want to know a bit more about it read below.

Why the ICEberg Case V3 for Raspberry Pi cools it ?

  1. First Case Aluminium with direct Coolling for CPU & LAN chipset
  2. Heat transfer with Silicon Thermal Pad
  3. Light LED Guide on the side (Option)
  4. 100% Aluminium machined CNC
  5. Case Design Pro
  6. GPU port available
  7. Upgrade with Vesa Mount
  8. Gloss finish
  9. Rubber foot
  10. Option with or without logo (ICEberg/Raspberry)

By the way, you can’t and shouldn’t (the case is enough on it own) put the heatsinks and this case together. As you can see on this video tutorial, you already use the slot with the case.
I also like its design !

My Conclusion:

What is the point in buying a raspberry pi at 30$ and a case at 58€ when the chance of overheating are so small ? Just use your warranty or buy a new one !

On the other hand, if you have overclocked your raspberry pi or feel like it is too hot, you can buy or reuse some heatsinks. It works and it is cheap.
At the moment, I didn’t overclock my raspberry pi and then there is no point for me to buy heatsinks (and I don’t have any heatsinks to reuse). Until I overclock my pi, I won’t use heatsinks.


I want my raspberry pi to be a media center. Help me please !


Do you want to watch all your movie with your raspberry pi ?
Here is a quick how to, don’t forget by a noob for noobs.

If you want to know what you do need to make it work I suggest you to take a look here. The idea now is to install an operating system that allow you to have a media center.
As far as I know there are three way to get a media center in your raspberry pi which all are based on XBMC :

Which one to use ?

To be honest it is a question of point of view. In my case, I think that if you want a media center. It should only do it.
Openelec is a media center that can’t do anything else. In the other case, Raspbmc and XBMC on a linux distrib can do much more. I do think that the raspberry pi is powerful but not powerful enough to do everything in the same time. I did pick to go on Openelec because it is easy to install, I had no problem and I don’t want my movies to be laggy because of my file server (or whatever you want) running in background is using all my cpu while I watch a 1080p movie.
If you ask me I’d say, try install openelec if it works stay with it. If it doesn’t, give a try to google and forums to make it so. If it still doesn’t work, do the same with Raspbmc. If nothing works install XBMC in a linux distrib, it will have a bigger community (the linux one) and maybe more updated package but you shouldn’t put too much hope on the last one. Also, we all got the same raspberry which mean you must not be the first one to have a problem with it.

How to install it:

I don’t think it is a good idea to do another tutorial to install theses solution when there are already so many online. What I like is video so I will give you video tutorials link to install it.
To install Openelec or Raspbmc watch this, also they tell you the difference between them (somehow what I already said but in a different way). If you have any problem installing Openelec or you would like to tweak it a little read this.
I couldn’t find a nice video to install it in a linux distrib so here is a quick how to:

How to install xbmc on debian
1. add deb squeeze main non-free to /etc/apt/sources.list
2. execute apt-get update, you might see some error, ignore them as of now
3. execute apt-get install deb-multimedia-keyring
4. now its time to add GPG key, download the gpg key package from wget … -4_all.deb
5. install the downloaded package dpkg -i debian-multimedia-keyring_2010.12.26-4_all.deb
6. again do the apt-get update
7. now install the xbmc apt-get install xbmc

So now you know how to install your media center, you also know that I picked Openelec. On the media center part I will now focus only on Openelec. On a the following posts about openelec I will tell what I think about it. What configuration and addon I like using and how to make your own addon !




Ok I got my raspberry pi (model B). Now what do I do ?


If like me you got your new raspberry pi and you didn’t buy earlier anything here’s some help.
First, do not rush things ! It might sound stupid but as a good noob I did rush things and.. well I got an extra hub and wifi card for nothing. I changed them but I had to go back to the store. Not a big deal, but still a waste of time.
Now let’s think about it. What do I need to my raspberry pi ?
It depends what you want to do with it !

For all usages:
How to get it:

Getting the power supply is quite easy. You can use one you already have. Or buy a new one, here for example (Well, I like adafruit, they do nice tutorial and good goods. Most of my to buy link will link you there).  You can also run it from a battery. Here is a must read page if you want to do it. And here is a good battery pack (it is in my wishlist).
A wired power supply

Here is the most important part, do not buy a random sd card ! There are plenty of sd card that are not compatible with the raspberry pi. Moreover, if the sd card is slow, your raspberry will also be slow !
If you already have an sd card and want to know if it will work, here is a good list of compatible and non compatible cards. And here is a benchmark of theses.


For Media Center usage:
  • An external hard drive source.
  • A remote.

To get an external hard drive source you do have different option. You can use a local hard drive that you are already using (on your computer for example) and share it with the media center. In order to do that you can use a network file sharing protocol (SMB, UPnP, AFP…). There are plenty of way to share files thought your local network but one thing you should be aware of is that you’ll want to stream HD video which mean you need to have a good bandwidth speed. I highly not recommend to use it with a wireless 802.11g as you can see here it is not reliable. You can do it with a wireless 802.11n or BPL (200 mb/s is enough). Otherwise, you can plug an external hard drive but it has to have its own power supply.

Before buying a remote, if you already have one you don’t use try it ! It was my case, and it worked like a charm. It is a windows media center remote. If it is not your case, take a look here to choose one or if you feel like in a hacking spirit take a look here.

For GPIO usage:
  • A buffer to protect your gpio pin
  • Wiring cable
  • Resistor, sensor, lcd, motor, battery…
How to get it:

The raspberry pi comes with gpio but it is not protected at all. This mean that you can kill your card if you do a bad wiring. Before doing anything protect your board with a buffer ! In order to protect your board you have to read this. A good board to get is the getboard but it is a bit expensive for me so I’ll be using transistors. Another thing you should consider is buying  the Adafruit Pi Cobbler Breakout Kit for Raspberry Pi. I did my own with a spot welder but to be honest, it is quite a mess of wires.

My raspberry pi

Once you have your cobbler, buy some leds and resistor and have fun !

I did not speak about usb hub or keyboard or moose because I don’t use it since I always use either my remote or ssh which means I don’t need extra usb ports. If you need them, take a look at adafruit. The important thing is that the usb hub has to be powered and compatible with the raspberry pi.


Time for some Raspberry pi !


I just bought my Raspberry pi and I will try to explain stuff about gpio and different usage of the raspberry pi. There might be mistake in some of my explaination and I hope there will be people to notice them.
But first, what is a raspberry pi ?
Here is the wikipedia answer:

The Raspberry Pi is a credit card sized single-board computer developed in the UK by the Raspberry Pi Foundation with the intention of stimulating the teaching of basic computer science in schools.

The Raspberry Pi has a Broadcom BCM2835 system on a chip (SoC), which includes an ARM1176JZF-S 700 MHzprocessor, VideoCore IV GPU, and 256 megabytes of RAM. It does not include a built-in hard disk or solid-state drive, but uses an SD card for booting and long-term storage. The Foundation’s goal is to offer two versions, priced at US$ 25 and US$ 35. The Foundation started accepting orders for the higher priced model on 29 February 2012.
The Foundation provides Debian and Arch Linux ARM distributions for download. Also planned are tools for supporting Python as the main programming language, with support for BBC BASIC, (As “Brandy Basic”, the BBC BASIC clone), C, and Perl.

Well, to put it simple. A raspberry pi is a little microchip (or a Plug Computer) that can runs linux and HD video. There are two model who are almost the same (model A and B, the B got a  second usb port and an ethernet controller).

Why bother ?
Because the model A costs US$ 25 and the model B costs US$ 35. It means, for thirty dollars you can have a linux server or a media center or even play with the gpio !

Aw, by the way you must be thinking. What is gpio ?
Go go wikipedia :

General Purpose Input/Output (GPIO) is a generic pin on a chip whose behavior (including whether it is an input or output pin) can be controlled (programmed) through software.GPIO pins have no special purpose defined, and go unused by default. The idea is that sometimes the system integrator building a full system that uses the chip might find it useful to have a handful of additional digital control lines, and having these available from the chip can save the hassle of having to arrange additional circuitry to provide them. For example, the Realtek ALC260 chips (audio codec) have 8 GPIO pins, which go unused by default. Some system integrators (Acer laptops) employing the ALC260 use the first GPIO (GPIO0) to turn on the amplifier used for the laptop’s internal speakers and external headphone jack.

Again, to put it simple. GPIO are pins that let you be able to plug motors, lcd, led I am a noob!

Yes, yes we’ll try to do it together !

Well I think you already understood that theses pages are by a noob for noobs, I will try to explain things as simple as possible so everyone can understand it, so feel free to give advise or ask if you didn’t understand something !

Nuit de l’info competition


Today I’d like to speak about the nuit de l’info (night of informatic) competition.

Every year there’s a competition famous for French “geek”. It has many famous sponsor like Microsoft, Oracle, Mozilla, SAP…
Here’s how it works:

  • You have several challenges by different sponsors.
  • You subscribe to the challenges you want to compete in.
  • You learn about the differents challenge during the evening.
  • You have all the night to develop your applications.
  • The next day you have the results.
  • Every winner of every challenge get a prize.

This year  the event occured during the night of the 2 of december. Our Team (FITA) did compete in 4 challenges. I’m happy to announce that we won 2 challenges (one of them sponsorised by Microsoft itself !). Both challenge were to develop a windows phone 7 application.
Moreover, I’d like to point out that at the beginning of the night nobody in the team had ever done a wp7 application !

I’d like to thanks every member of the Fita team but also the nuit de l’info staff for their very good job !

Flash introduction of


A long time ago I decided to do my website totally in Flash. At the end I did not do it because my website is to show my CV to people whereas Flash do not allow to refer website.
At this time I made an introduction for my website in Flash. This is not the final version (no preloader, not good size)…
I did use AE and AF since you can integrate AE works in flash now.
Since there’s no preloader and the browser size is not correct I advise you to use right click and download as in order to enjoy the show. If you’ve any questions feel free to comment.
Here’s the link.

Go to Top