Untitled

cost_df = kw_df[["TRAFFICSOURCE_CAMPAIGN","TRAFFICSOURCE_KEYWORD","Cost","Impr.","Clicks","CTR"]]]
cost_df["cpc"] = cost_df["Cost"]/cost_df["Clicks"]
keyword_dict = dict(cost_df["Campaign_Keyword"].value_counts())
g1_dict = dict(df_g1["Campaign_Keyword"].value_counts())
g2_dict = dict(df_g2["Campaign_Keyword"].value_counts())
g3_dict = dict(df_g3["Campaign_Keyword"].value_counts())

total_occurence = list(keyword_dict.keys())

goal_keyword_df["Campaign_Keyword"] = pd.Series(list(keyword_dict.keys()))

temp_list = []
for x in total_occurence:
    temp_list.append(keyword_dict[x])  
goal_keyword_df["Total_Occurence"] = pd.Series(temp_list)

temp_list = []
for x in total_occurence:
    if x in conv_g1_occ:
        temp_list.append(g1_dict[x])  
    else:
        temp_list.append(0)
        
goal_keyword_df["Goal_1"] = pd.Series(temp_list)


temp_list = []
for x in total_occurence:
    if x in conv_g2_occ:
        temp_list.append(g2_dict[x])  
    else:
        temp_list.append(0)
        
goal_keyword_df["Goal_2"] = pd.Series(temp_list)


temp_list = []
for x in total_occurence:
    if x in conv_g3_occ:
        temp_list.append(g3_dict[x])  
    else:
        temp_list.append(0)
        
goal_keyword_df["Goal_3"] = pd.Series(temp_list)

sns.barplot(data=cpc_kw_df.sort_values(by=["Conversion_Rate_G2","cpc"],ascending=False).head(30),y="Campaign_Keyword",x="cpc")
sns.barplot(data=cpc_kw_df.sort_values(by=["Conversion_Rate_G1","cpc"],ascending=False).head(30),y="Campaign_Keyword",x="cpc")

cpc_kw_df.groupby(["keyword","campaign"]).first().drop(["Campaign_Keyword"],axis=1).sort_values(by="Total_Occurence",ascending=False).to_excel("KeywordGoalCost.xlsx")