python畫漏斗圖

只需要輸入當日時間，就可以得到當日、上周同日、30日日均的漏斗圖，圖片如下


from pyechartsimport optionsas opts

from pyecharts.chartsimport Funnel

from pyecharts.fakerimport Faker

import numpyas np

import datetime

import pandasas pd

import webbrowser

import pymysql

##輸入當日時間

time ='2021/08/02'

time_lastweek = (datetime.datetime.strptime(time,"%Y/%m/%d")-datetime.timedelta(days=7)).strftime("%Y/%m/%d")

db = pymysql.connect(host="1ghfoc48gd0-7csowqjfug5w.cn-shenzhen.datalakeanalytics.aliyuncs.com",

? ? ? ? ? ? ? ? ? ? ? user="dladev_s1708083130941856",

? ? ? ? ? ? ? ? ? ? ? password="xlKs96AKfV1s57WvJ1", port=10000, database='ods', charset='utf8')

date_group = time.split('/')#拆分字符，用于查詢

date_group_lastweek=time_lastweek.split('/')

data =list()

#title = ['商品詳情頁uv', '填寫詳情頁uv', '支付選擇頁uv', '提交資料頁uv', '免密簽約頁uv']

##當日和上周同日

sql_lst = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2])]

cursor = db.cursor()

for sqlin sql_lst:

cursor.execute(sql)

columns_names = ['num']

df = pd.DataFrame(cursor.fetchall(), columns=columns_names)

data.append(int(df['num'][0]))

#title = ['落地頁：', '填寫資料頁：', '選擇支付頁：', '提交資料頁：', '簽約頁：']

#30日均

time_lastmonth = (datetime.datetime.strptime(time, "%Y/%m/%d") - datetime.timedelta(days=29)).strftime("%Y/%m/%d")

sql_lst1 = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

? ? ? ? ? "select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')"]

cursor = db.cursor()

for sqlin sql_lst1:

cursor.execute(sql)

columns_names = ['num']

df = pd.DataFrame(cursor.fetchall(), columns=columns_names)

data.append(round((df['num'][0])/30))

title = ['? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 落地頁：', '? ? ? ? 填寫資料頁：', '選擇支付頁：', '提交資料頁：', '簽約頁：']

print(data)

###畫漏斗圖

save_path ='C:/Users/EDZ/Desktop/工作/2021.08.06/' + time.replace('/', '') +'Funnel.html'

rate1=format((data[0]-data[5])/data[5], '.2%')

rate2=format((data[0]-data[10])/data[10], '.2%')

Title = [ title+'\n? ? ? ? ? 當日：%d? 相對轉(zhuǎn)化率:%.2f%%? 轉(zhuǎn)化率:(落地頁):%.2f%% \n? 上周同日：%d? 相對轉(zhuǎn)化率:%.2f%%? 轉(zhuǎn)化率:(落地頁):%.2f%% \n? ? ? 30日均：%d? 相對轉(zhuǎn)化率:%.2f%%? 轉(zhuǎn)化率:(落地頁):%.2f%% '% (data[idx], (data[idx]/data[idx-1])*100, (data[idx]/data[0])*100,data[idx+5], (data[idx+5]/data[idx-1+5])*100, (data[idx+5]/data[0+5])*100,data[idx+10], (data[idx+10]/data[idx-1+10])*100, (data[idx+10]/data[0+10])*100)if idx >0 else title+'\n? ? ? ? ? ? 當日：? ' +str(data[0]) +'\n? ? ? 上周同日：? ' +str(data[5]) +'? 同比變化率：'+str(rate1)+'\n? ? ? ? 30日均：? ' +str(data[10]) +'? 同比變化率：'+str(rate2)for title, idxin zip(title, range(len(data)))]

c = (

Funnel(init_opts=opts.InitOpts(width="1400px"))

.add('Test', [list(z)for zin zip(Title, data)], sort_='none')

.set_global_opts(title_opts=opts.TitleOpts(title=time+' 新用戶訪問漏斗圖'),

? ? ? ? ? ? ? ? ? ? legend_opts=opts.LegendOpts(is_show=False))

.render(save_path)

)

webbrowser.open(save_path)

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容