from model.DateUtils import DateUtils from model.DataBaseUtils import * from model.log import logger from model.CommonUtils import * du = DateUtils() ck = CkUtils() db = MysqlUtils() log = logger() def get_pitcher_panel_channel(pitcher,channel,start,end,page,page_size,order_by,order): sql=f"""select channel,stage,platform,book, formatDateTime(dt,'%Y-%m-%d') as date, cost, first_order_amount, toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi, first_order_user,first_order_count, toDecimal32(if(first_order_user=0,0,cost/first_order_user),2) first_per_cost, view_count,click_count,follow_user, toDecimal32(if(click_count=0,0,follow_user/click_count),4) follow_rate, toDecimal32(if(follow_user=0,0,cost/follow_user),2) follow_per_cost, total_cost, toDecimal32(if(total_cost=0,0,total_amount/total_cost),2) back_rate from dw_daily_channel where dt>='{start}' and dt<='{end}' """ if pitcher!='all': sql += f" and pitcher='{pitcher}' " if channel!='': sql += f" and channel='{channel}' " sql += f" order by {order_by} {order} limit {page},{page_size} " print(sql) data=ck.execute(sql) key=['channel','stage','platform','book','date','cost','first_order_amount','first_roi','first_order_user', 'first_order_count','first_per_cost','view_count','click_count','follow_user','follow_rate','follow_per_cost', 'total_cost','back_rate'] return get_dict_list(key, data) def get_pitcher_panel_daily(pitcher, start, end, page, page_size, order_by, order): sql=f""" select formatDateTime(dt,'%Y-%m-%d') date ,pitcher, toDecimal32(sum(cost),2) cost, toDecimal32(sum(first_order_amount),2) first_order_amount, toDecimal32(if(cost=0,0,first_order_amount/cost),4) first_roi , toDecimal32(sum(order_amount),2) order_amount, toDecimal32(if(cost=0,0,sum(reg_order_amount)/cost),4) today_roi, toDecimal32(sum(total_cost),2) total_cost, toDecimal32(sum(total_amount),2) total_amount, toDecimal32(total_amount-total_cost,2) total_profit, toDecimal32(if(total_cost=0,0,total_amount/total_cost),4) total_roi from dw_daily_channel where dt>='{start}' and dt<='{end}' """ if pitcher != 'all': sql += f" and pitcher='{pitcher}' " sql += f" group by date,pitcher order by {order_by} {order} limit {page},{page_size} " print(sql) data = ck.execute(sql) key=['date','pitcher','cost','first_order_amount','first_roi','order_amount','today_roi','total_cost','total_amount','total_profit','total_roi'] return get_dict_list(key,data) def get_pitcher_panel_overview(pitcher): sql=f"""select pitcher, toDecimal32(cost,2) cost, toDecimal32(amount,2) amount, toDecimal32(roi,4) roi, channel_count,on_channel_count, off_channel_count, toDecimal32(this_month_cost,2) this_month_cost, toDecimal32(this_month_amount,2) this_month_amount, toDecimal32(this_month_roi,4) this_month_roi, toDecimal32(last_month_cost,2) last_month_cost, toDecimal32(last_month_amount,2) last_month_amount, toDecimal32(last_month_roi,4) last_month_roi, toDecimal32(last_month_far_amount,2) last_month_far_amount, follow_user from dm_pitcher_daily_page_total where dt='{du.get_n_days(-1)}'""" if pitcher != 'all': sql += f" and pitcher='{pitcher}' " print(sql) data=ck.execute(sql) print(data) key=['pitcher','cost','amount','roi','channel_count','on_channel_count','off_channel_count','this_month_cost','this_month_amount','this_month_roi', 'last_month_cost','last_month_amount','last_month_roi','last_month_far_amount','follow_user'] return get_dict_list(key,data) def get_channel_overview(channel,start,end,page,page_size,order_by,order): sql="""select channel,toString(dt) date, view_count,click_count, round(if(view_count=0,0,click_count/view_count),4) click_rate, follow_user, round(if(click_count=0,0,follow_user/click_count),4) follow_rate, round(if(follow_user=0,0,cost/follow_user),2) follow_per_cost, round(if(click_count=0,0,first_order_count/click_count),4) order_rate, round(if(first_order_user=0,0,cost/first_order_user),2) order_per_cost, round(cost,2) cost, first_order_count,first_order_user, round(first_order_amount,2) first_order_amount,order_count,order_user, round(order_amount,2) order_amount, round(order_amount-first_order_amount,2) old_order_amount, round(if(first_order_user=0,0,first_order_amount/first_order_user),2) first_amount_per_user, round(if(follow_user=0,0,first_order_amount/follow_user),2) amount_per_follow, round(if(first_order_user=0,0,cost/first_order_user),2) first_cost_per_user, round(if(follow_user=0,0,first_order_user/follow_user),4) new_user_order_rate, round(reg_order_amount,2) reg_user_amount, round(total_cost,2) total_cost, round(total_amount,2) total_amount, round(if(cost=0,0,first_order_amount/cost),4) day_roi, round(if(cost=0,0,reg_order_amount/cost),4) all_roi, 0 avg_new_order_rate, 0 old_user_once_order_rate from dw_daily_channel where 1=1 """ if channel!='': sql+=f" and channel='{channel}'" if start!='': sql+=f" and start>='{start}'" if end!='': sql+=f" and end<='{end}'" sql += f" order by {order_by} {order} limit {page},{page_size} " print(sql) return ck.execute(sql) def get_channel_again_order_trend(channel,date): a=[{'date':'2020-12-01', 'channel':'宝珠书屋', 'book':'熊出没', 'cost':23.23, 'reg_amount':23421.11, 'roi':0.23, 'new_follow':23, 'new_follow_per_cost':234.11, 'reg_user':111, 'reg_count':123, 'cost_per_user':123, 'avg_again_order_rate':0.24, 'avg_order_amount':1231.11, 'order_count':11, 'data':[{'user_order_count' :1, 'd1':{'origin':12, 'new':1231, 'move':12, 'now':2134, 'follow_order_rate':0.12}, 'd2': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd3': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd4': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd5': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd6': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12}, 'd7': {'origin': 12, 'new': 1231, 'move': 12, 'now': 2134, 'follow_order_rate': 0.12} } ] }] return a def get_channel_active(channel,start,end): a=[{ 'date':'2020-12-01', 'channel':'宝珠书屋', 'book':'魏汝稳', 'cost':234.11, 'reg_amount':123.11, 'roi':0.45, 'new_follow_user':11, 'new_follow_per_cost':234.11, 'order_user':222, 'order_count':12312, 'order_user_per_cost':123.11, 'day7_avg_act_rate':0.14, 'day7_avg_act_per_cost':5.23, 'day30_avg_act_rate':0.9334, 'day30_avg_act_rate':5.23, 'act_per_cost':43.11, 'd1':{ 'act_user':12, 'act_cost_per_cost':23.11, 'act_rate':0.4512}, 'd2': { 'act_user': 12, 'act_cost_per_cost': 23.11, 'act_rate': 0.4512}, 'd3': { 'act_user': 12, 'act_cost_per_cost': 23.11, 'act_rate': 0.4512} }] return a def get_channel_order_trend(channel,start,end): a=[{ 'date': '2020-12-01', 'channel': '宝珠书屋', 'book': '魏汝稳', 'cost': 234.11, 'reg_amount': 123.11, 'roi': 0.45, 'new_follow_user': 11, 'new_follow_per_cost': 234.11, 'order_user': 222, 'order_count': 12312, 'order_user_per_cost': 123.11, 'd1':{ 'order':232.11, 'roi':0.2341, 'add':0.4511, 'mult':1.12}, 'd2': { 'order': 232.11, 'roi': 0.2341, 'add': 0.4511, 'mult': 1.12}, 'd3': { 'order': 232.11, 'roi': 0.2341, 'add': 0.4511, 'mult': 1.12}, }] return a def get_channel_summary(channel,pitcher): sql = """select a.channel,state,'朋友圈' location,toString(start),toString(end),total_cost,total_amount, profit,roi,follow_user,follow_per_cost,order_user, if(follow_user=0,0,order_user/follow_user) order_tran_rate,if(order_user=0,0,total_cost/order_user) order_tran_cost from ( select channel,if(max(dt)>subtractDays(today(),10),'在投','停投') state, min(dt) start,max(dt) end from dw_daily_channel_cost where cost>0 {} group by channel) a left outer join( select channel,sum(follow_user) follow_user, if(follow_user=0,0,sum(cost)/follow_user) follow_per_cost from dw_daily_channel group by channel) b on a.channel=b.channel left outer join( select sum(reg_order_user) order_user,channel from dw_daily_channel where dt=subtractDays(today(),1) group by channel) c on a.channel=c.channel left outer join ( select total_cost, total_amount, channel, total_amount-total_cost profit, if(total_cost=0,0,total_amount/total_cost) roi from dw_channel_daily_total where dt=subtractDays(today(),1)) d on a.channel=d.channel where 1=1 """ if pitcher!='': sql=sql.format(f" and pitcher='{pitcher}'") else: sql=sql.format('') if channel!='': sql+=f" and channel='{channel}'" return ck.execute(sql) if __name__ == '__main__': # a=get_channel_overview('','','',1,10,'date','desc') a=get_channel_summary('','') print(a)