User Tools

Site Tools


bidding

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

bidding [2015/09/22 15:17]
vincenzo created
bidding [2015/12/16 12:58] (current)
vincenzo
Line 1: Line 1:
-<​code ​python+<​code ​c
- ######################​###### +###SUMMARY### 
-  ​  +## CLICK EVENT 
- #      Manual bidding    # +#1AVERAGE CLICKS PER INSTALL
- #  ​  # +
- ##########################​##+
  
  
-Import ​the file; check that it must be downloaded in the "​pure"​ csv format in google adwords +get the data from BQ
-# add the cluster column and clean the column names without spaces+
  
 +library(bigrquery)
 +library(dplyr)
 +project <- "​panda-premium-analytics"​
  
- ​mb=read.delim("​C:/​Users/​Ana Isabel Gomez/​Desktop/​Data RawNGFINAL.txt"​)w+# query
  
-#Ask for the maximum and minimum CPC and for the COST/CONV.+sql <- " 
 +select * 
 +from adjust.02_mobile_step02  
 +group by id, log_date, network, event, timestamp, order, bundle_id, fb_campaign,​ campaign, event_country,​ ip 
 +order by id, timestamp 
 +"
  
-print("​Please,​ introduce the minimum bid: ") +##########################################################​
-min_bid <​-scan(,​what=numeric(),​1)+
  
-print("​Please,​ introduce the maximum bid: ") +data <- query_exec(sqlproject ​projectmax_pages = Inf)
-max_bid ​<-scan(,what=numeric(),1)+
  
-print("​Please,​ introduce the average CPC bid: ") +#1#
-AvgCPC_bid <​-scan(,​what=numeric(),​1)+
  
- # convert cost, clicks, costconv into numeric to apply division or roundings +data_noinstall ​<- data 
-  +data_install ​<- data
- mb$Costconv_num ​<- as.numeric(as.character(mb$Cost...conv)) +
- mb$Clicks_num ​<- as.numeric(as.character(mb$Clicks)) +
- mb$Cost_num <- as.numeric(as.character(mb$Cost)) +
- mb$Conversions_num <- as.numeric(as.character(mb$Conversions)) +
-  +
- # Remove the missing row (when it exists)+
  
- #mb <​-na.omit(mb) 
  
-#Creation ​of cluster+Average number ​of clicks that yield to no install, per network
  
- mb$Cluster=gsub("​_"​,"",​gsub("​\\d","",​ gsub("]([^/]*)$","​]",​mb$Ad.group))) +data_click_to_noinstalls <- data_noinstall %>% 
- mb$id <- 1:nrow(mb)+  group_by(idnetwork) %>% 
 +  filter(first(event)=='​click'​ & last(event)=='​click'​%>% 
 +  summarise(count=n()) %>% 
 +  ​group_by(network) %>% 
 +  summarise(avgClicks=mean(count) 
  
-# Adgroup level pivoting 
  
- adgroup_level <- aggregate(cbind(mb$Clicks_nummb$Conversions_num,​ mb$Cost_num) ~ Ad.group, data = mb, FUN = sum) +write.csv(data_click_to_noinstallsfile = '/​Users/​vincenzo/​Desktop/​1.install_sale_analysis/​ios/​IN/​2.\ Click\ Analysis'​)
-  +
- # Calculate campaign CPL+
  
- adgroup_level$CPL_adgroup ​<- adgroup_level$V3 ​adgroup_level$V2+data_click_to_noinstalls ​<- read.csv('​/Users/​vincenzo/​Desktop/​1.install_sale_analysis/​ios/​IN/​2.\ Click\ Analysis/​clicknoinstallgraph2.csv'​) ​
  
- # left join dataframe +ggplot(data=data_click_to_noinstalls,​ aes(x=network, y=avgClicks) 
-  +  ​geom_bar(colour="black", ​fill="#DD8888", ​width=.8stat="identity") + 
- adgroup_level$CPL_adgroup[adgroup_level$CPL_adgroup==Inf] <- 0 +  xlab("Network"​) ​+ ylab("Average Clicks"​) ​
- adgroup_level[is.na(adgroup_level)] <- 0 +  ggtitle("​Average number of clicks that yield to no installper network"​)
- adgroup_edited <- merge(mb, adgroup_level[,​c("Ad.group","​CPL_adgroup","​V1"​,"V2","V3")], by=c("Ad.group"​), ​all.x=FALSE) +
- +
  
-# Campaign level pivoting+write.csv(data_click_to_noinstalls,​ file = '/​Users/​vincenzo/​Desktop/​1.install_sale_analysis/​ios/​IN/​2.\ Click\ Analysis/​clicknoinstall.csv'​)
  
- campaign_level <- aggregate(cbind(mb$Clicks_nummb$Conversions_num,​ mb$Cost_num) ~ Campaign, data = mb, FUN = sum)+# Average number of clicks that yield to installper network
  
- # Calculate campaign CPL+data_click_to_install <- data %>% 
 +  group_by(id,​ network) %>% 
 +  filter(first(event)=='​click'​ & last(event)=='​install'​) %>% 
 +  summarise(count=n()) %>% 
 +  group_by(network) %>% 
 +  summarise(avgClicks=mean(count))
  
- campaign_level$CPL_campaign <- campaign_level$V3 / campaign_level$V2+ggplot(data=data_click_to_install,​ aes(x=network,​ y=avgClicks)) +  
 +  geom_bar(colour="​black",​ fill="#​DD8888",​ width=.8, stat="​identity"​) + 
 +  xlab("​Network"​) + ylab("​Average Clicks"​) + 
 +  ggtitle("​Average number of clicks that yield to a install, per network"​)
  
- # left join dataframe 
  
- campaign_level$CPL_campaign[campaign_level$CPL_campaign==Inf] <- 0 +write.csv(sum_click_to_install, file = '​data_click_to_install.csv'
- campaign_level[is.na(campaign_level)] <- 0 +write.csv(data_click_to_install, file = '/Users/vincenzo/Desktop/1.install_sale_analysis/​ios/​IN/​2.\ Click\ Analysis/​data_click_to_nointstalls.csv')
- campaign_edited <- merge(mbcampaign_level[,​c("​Campaign","​CPL_campaign","​V1","​V2","​V3"​)],​ by=c("​Campaign"​),​ all.x=FALSE) +
- +
-# Cluster level pivoting +
- +
- cluster_level <- aggregate(cbind(mb$Clicks_num,​ mb$Conversions_num,​ mb$Cost_num) ~ Cluster, data = mb, FUN = sum) +
- +
- # Calculate campaign CPL +
- +
- cluster_level$CPL_cluster <- cluster_level$V3 / cluster_level$V2 +
- +
- #​Calculate cluster conversionrate and Optimization Rate. +
- +
- cluster_level$Conversionrate_cluster <- cluster_level$V2 / cluster_level$V1 +
- cluster_level$Optimization_cluster <- round(1/​cluster_level$Conversionrate_cluster,​digits=0) +
- +
- # left join dataframe +
- +
- cluster_level$CPL_cluster[cluster_level$CPL_cluster==Inf] <- 0 +
- cluster_level$Conversionrate_cluster[cluster_level$Conversionrate_cluster==Inf] <- 0 +
- cluster_level$Optimization_cluster[cluster_level$Optimization_cluster==Inf] <- 0 +
- +
- cluster_level[is.na(cluster_level)] <- 0 +
-  +
- cluster_edited <- merge(mb, cluster_level[,​c("​Cluster","​CPL_cluster","​Conversionrate_cluster","​Optimization_cluster","​V1","​V3"​)],​ by=c("​Cluster"​),​ all.x=FALSE) +
- +
-#Order the merger data +
- +
-adgroup_edited<​-adgroup_edited[order(adgroup_edited$id),​] +
-campaign_edited<​-campaign_edited[order(campaign_edited$id),​] +
-cluster_edited<​-cluster_edited[order(cluster_edited$id),​] +
- +
-#CPL optimizate depending the clicks and clicks optimization and conversion =0. +
- +
-#​Hip=hypothetical +
- +
-mb$Hip_CPL_kw <- ifelse(((mb$Clicks_num > cluster_edited$Optimization_cluster) & (mb$Conversions_num=0)),​ mb$Cost_num,​ mb$Costconv_num) +
-mb$Hip_CPL_ad <- ifelse(((adgroup_edited$V1 > cluster_edited$Optimization_cluster) & (adgroup_edited$V2==0)),​ adgroup_edited$V3,​ adgroup_edited$CPL_adgroup) +
-mb$Hip_CPL_cmp <- ifelse(((campaign_edited$V1 > cluster_edited$Optimization_cluster) & (campaign_edited$V2==0)),​ campaign_edited$V3,​ campaign_edited$CPL_campaign) +
- +
- +
-#Clicks depending Cost/conv +
- +
-mb$ClicksKW <- ifelse(mb$Hip_CPL_kw==0,​ 0, mb$Clicks_num) +
-mb$ClicksAD <- ifelse(mb$Hip_CPL_ad==0,​ 0,​adgroup_edited$V1) +
-mb$ClicksCAMP <- ifelse(mb$Hip_CPL_cmp==0,​ 0, campaign_edited$V1) +
- +
-#Decision CPL_EDITED +
- +
-cplADzero <​-(mb$Hip_CPL_ad*0.6*mb$ClicksAD+mb$ClicksKW*mb$Hip_CPL_kw)/​(0.6*mb$ClicksAD+mb$ClicksKW) +
- +
-cplCMzero <​-(mb$Hip_CPL_cmp*0.3*mb$ClicksCAMP + mb$Hip_CPL_ad*0.6*mb$ClicksAD + mb$ClicksKW*mb$Hip_CPL_kw)/​(0.3*mb$ClicksCAMP+0.6*mb$ClicksAD+mb$ClicksKW) +
- +
-cplCL <​-(cluster_edited$CPL_cluster*0.1*cluster_edited$V1+mb$Hip_CPL_cmp*0.3*mb$ClicksCAMP + mb$Hip_CPL_ad*0.6*mb$ClicksAD + mb$ClicksKW*mb$Hip_CPL_kw)/​(0.1*cluster_edited$V1+0.3*mb$ClicksCAMP+0.6*mb$ClicksAD+mb$ClicksKW) +
- +
- ##Decision CPL old structure variables. +
- +
- mb$DecisionCPL <- round(ifelse((mb$ClicksKW > cluster_edited$Optimization_cluster & mb$Hip_CPL_kw > 0), mb$Hip_CPL_kw,​ +
- ifelse((mb$ClicksAD > cluster_edited$Optimization_cluster & mb$Hip_CPL_ad > 0), cplADzero,​ +
- ifelse((mb$ClicksCAMP > cluster_edited$Optimization_cluster & mb$Hip_CPL_cmp > 0), cplCMzero, cplCL))), digits=2) +
- +
- +
-# Decision Clicks +
- +
- mb$Decision_clicks <- round(ifelse(mb$Clicks_num > cluster_edited$Optimization_cluster,​ mb$Clicks_num,​ +
- ifelse(adgroup_edited$V1 > cluster_edited$Optimization_cluster,​ adgroup_edited$V1,​ +
- ifelse(campaign_edited$V1 > cluster_edited$Optimization_cluster,​ campaign_edited$V1,​ cluster_edited$V1))),​ digits=2) +
- +
-# Max CPC Bid +
- +
- mb$MaxCPC <- ifelse(mb$Clicks_num==0,​max_bid,​ifelse(mb$Decision_clicks > cluster_edited$Optimization_cluster,​ifelse(mb$DecisionCPL > AvgCPC_bid|mb$DecisionCPL==0,​min_bid,​max_bid),​max_bid)) +
- +
-#​Create ​file to export +
- +
-attach(mb) +
- +
-dataexport=data.frame(Keyword,​ Campaign, Ad.group, MaxCPC) +
- +
-detach(mb) +
- +
-# Write CSV output file +
- +
- write.csv(dataexport, file="C:/Users/Ana Isabel Gomez/Desktop/150309-sem-NG-bidexport-v2.csv",​row.names = FALSE) +
- +
- +
-<​code>​+
  
 +</​code>​
bidding.txt · Last modified: 2015/12/16 12:58 by vincenzo