用戶畫(huà)像番外篇之隨筆三則

一則：開(kāi)發(fā)上的一點(diǎn)記錄

文章說(shuō)是生活隨筆，到不如說(shuō)是對(duì)本周開(kāi)發(fā)工作中的一些體會(huì)與思考的記錄。

這個(gè)專(zhuān)欄我想除了對(duì)知識(shí)上的一些記錄，以后也可以加入生活上的收獲。好記性不如爛筆頭，或許多年后再回看這些文章，回看進(jìn)步的歷程，也是一件很有成就感的事情。

4月份換了工作去做數(shù)據(jù)開(kāi)發(fā)，重點(diǎn)項(xiàng)目還是做用戶畫(huà)像。開(kāi)發(fā)時(shí)間排的很緊，平均每天要開(kāi)發(fā)1~2個(gè)標(biāo)簽。從和需求方確認(rèn)標(biāo)簽口徑，找標(biāo)簽對(duì)應(yīng)數(shù)據(jù)所在的表、字段定義、數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)，到寫(xiě)標(biāo)簽邏輯，上線驗(yàn)證標(biāo)簽正確性.... 時(shí)間簡(jiǎn)直不夠，更不要說(shuō)某些復(fù)雜口徑的一個(gè)標(biāo)簽都要寫(xiě)上百行的邏輯。

這周到現(xiàn)在又開(kāi)發(fā)了6個(gè)標(biāo)簽，寫(xiě)了一個(gè)調(diào)度腳本，正在進(jìn)行著一次數(shù)據(jù)邏輯調(diào)優(yōu)。下面挑兩個(gè)重要點(diǎn)的記錄一下：

1、任務(wù)調(diào)度腳本開(kāi)發(fā)

畫(huà)像數(shù)據(jù)目前是寫(xiě)在Spark SQL里面，通過(guò)每天定時(shí)任務(wù)調(diào)python腳本，來(lái)執(zhí)行Spark SQL。

但這樣的話開(kāi)發(fā)效率比較低，一方面開(kāi)發(fā)人員寫(xiě)完Hive SQL腳本后，還需要在外面包一層spark才打包成可執(zhí)行腳本，另一個(gè)方面對(duì)于每一個(gè)打包后的python腳本都要寫(xiě)一個(gè)crontab調(diào)度命令。

所以必須要優(yōu)化一下流程。優(yōu)化方式就是：

①開(kāi)發(fā)人員對(duì)畫(huà)像標(biāo)簽只需寫(xiě)Hive SQL腳本，傳到服務(wù)器對(duì)應(yīng)目錄下；

②通過(guò)一個(gè)python腳本，自動(dòng)掃描目錄下的sql文件，加載并替換掉sql中的日期變量，并將替換日期后的腳本文件包上spark去執(zhí)行；

③每天crontab命令只需定時(shí)調(diào)度該python腳本即可，不需要在每新上一個(gè)標(biāo)簽的Hive SQL邏輯，就上一條調(diào)度命令。

2、數(shù)據(jù)邏輯調(diào)優(yōu)

開(kāi)發(fā)出的標(biāo)簽很多了，但有的標(biāo)簽邏輯復(fù)雜，需要做進(jìn)一步調(diào)優(yōu)，提高每日跑批作業(yè)的執(zhí)行效率，這里就與日志數(shù)據(jù)相關(guān)的標(biāo)簽為例。

用戶近30日活躍時(shí)間段：這個(gè)口徑需要計(jì)算用戶近30天是在中午、下午、晚上哪個(gè)時(shí)間段訪問(wèn)次數(shù)最多，這顯然是一個(gè)與日志數(shù)據(jù)相關(guān)的口徑。

而記錄用戶訪問(wèn)行為的日志數(shù)據(jù)的情況是：

1、做了日期分區(qū)，每日全量更新歷史數(shù)據(jù)。而且日志數(shù)據(jù)量很大，每天都有億級(jí)pv；

2、這就導(dǎo)致了在每天跑批時(shí)都需要從近30天的訪問(wèn)日志中抽取數(shù)據(jù)計(jì)算，一次幾十億pv的計(jì)算，相當(dāng)耗費(fèi)計(jì)算資源了。

后來(lái)做的調(diào)優(yōu)方式是：

①首次刷數(shù)據(jù)時(shí)刷近30日用戶在每個(gè)時(shí)間段的活躍次數(shù)，做倒排序找出用戶活躍時(shí)間段；

②后續(xù)每天跑批任務(wù)時(shí)，只需計(jì)算前一天用戶訪問(wèn)各時(shí)間段對(duì)應(yīng)的次數(shù)（不通過(guò)日期分區(qū)字段找，對(duì)用戶訪問(wèn)時(shí)間做日期格式處理后通過(guò)訪問(wèn)日期來(lái)找），并與歷史數(shù)據(jù)做加總，找出其活躍時(shí)間段；

③這樣計(jì)算就免去了計(jì)算近30日的日志數(shù)據(jù)，僅需計(jì)算前一天的數(shù)據(jù)即可。

近期在不斷補(bǔ)充學(xué)習(xí)新知識(shí)，spark要搞起來(lái)了、shell命令要用熟起來(lái)了。都要投入精力搞。

寫(xiě)到這會(huì)已經(jīng)周五早上53分了，過(guò)幾個(gè)小時(shí)還要繼續(xù)投入。這周的一些想法先總結(jié)到這里。

我覺(jué)得生活也好、工作也好，或許就是在這么一天天的貌似不起眼的積累中，不斷進(jìn)步的。

作為一個(gè)多年的米粉，記得那次看紅米note3發(fā)布會(huì)的末尾，被他文案中樸實(shí)、真誠(chéng)的語(yǔ)句吸引了。在這里想用那句臺(tái)詞做結(jié)“我所有的向往”。向往著在每一個(gè)看似普通的日子中精彩生活、不斷進(jìn)步、奔騰向前。

二則：自動(dòng)發(fā)送郵件腳本

這段時(shí)間在對(duì)流量部門(mén)提供數(shù)據(jù)方面的支持，把每天自動(dòng)發(fā)送郵件的腳本講一講吧，雖然很基礎(chǔ)，好像沒(méi)什么可以說(shuō)的 ...

在日常運(yùn)營(yíng)工作中，數(shù)據(jù)提取人員面對(duì)眾多業(yè)務(wù)方的數(shù)據(jù)需求，往往應(yīng)接不暇。他們需要一套自動(dòng)化的程序去幫助他們完成一些周期性和重復(fù)性較強(qiáng)的工作。

為了減少重復(fù)性工作，數(shù)據(jù)提取人員可以使用Python自動(dòng)化腳本跑定時(shí)任務(wù)。將寫(xiě)好的HQL語(yǔ)句放入Python腳本中，并在linux服務(wù)器上設(shè)置crontab定時(shí)調(diào)度任務(wù)，保證每天定時(shí)自動(dòng)從數(shù)據(jù)倉(cāng)庫(kù)提取數(shù)據(jù)完畢后，將結(jié)果集寫(xiě)到excel中并發(fā)送郵件到數(shù)據(jù)需求方的郵箱。Python腳本代碼執(zhí)行如下

#coding: utf-8 search_data = """ 創(chuàng)建臨時(shí)表查詢(xún)昨日運(yùn)營(yíng)數(shù)據(jù)""" report_data = ''' select * from 上一步創(chuàng)建的臨時(shí)表 ''' import psycopg2 import smtplib import os import openpyxl import datetime from impala.dbapi import connect from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.mime.image import MIMEImage import pyhs2 # HIVE環(huán)境 wb = openpyxl.load_workbook('/home/path/username/daily_report_v1.xlsx') # 打開(kāi)服務(wù)器存儲(chǔ)路徑下的excel文件 # 連接HIVE環(huán)境 impala_conn = pyhs2.connect(host='10.xx.xx.xx', port=xxx, authMechanism="PLAIN", user='username', password='password', database='dwd') seo_h5_1 = impala_conn.cursor() h5_result = impala_conn.cursor() seo_h5_1.execute('''SET mapreduce.job.queuename=root.yydata''') seo_h5_1.execute(search_data) # 執(zhí)行HQL語(yǔ)句 # 取出來(lái)數(shù)據(jù) h5_result.execute(report_data) # 取出來(lái)數(shù)據(jù) h5_result = h5_result.fetchall() #放到sheet里面去 sheet = wb.get_sheet_by_name('daily_report') #daily_report表 #清除歷史數(shù)據(jù) for i in range(2,sheet.max_row + 1 ): for j in range(1,sheet.max_column + 1 ): sheet.cell(row=i,column=j).value = '' #填充結(jié)果數(shù)據(jù) for i in range(2,len(h5_result) + 2 ): for j in range(1,len(h5_result[i-2]) + 1 ): sheet.cell(row=i,column=j).value = h5_result[i-2][j-1] #關(guān)閉HIVE鏈接 impala_conn.close() wb.save('/home/path/usernamet/daily_report_v1.xlsx') # 保存excel文件 receiver = 'receiver_email@xxx.com' # 收件人郵箱地址 date_str = datetime.datetime.strftime(datetime.date.today()-datetime.timedelta(days=1),'%m%d') mail_txt = """ Dear All, 附件是運(yùn)營(yíng)日?qǐng)?bào)，請(qǐng)查收。 """ msgRoot = MIMEMultipart('mixed') msgRoot['Subject'] = unicode(u'日?qǐng)?bào)-%s' % date_str) #添加日期 msgRoot['From'] = 'sender_email@xxx.com' msgRoot['To'] = receiver msgRoot["Accept-Language"]="zh-CN" msgRoot["Accept-Charset"]="ISO-8859-1,utf-8" msg = MIMEText(mail_txt,'plain','utf-8') msgRoot.attach(msg) att = MIMEText(open('/home/path/usernamet/daily_report_v1.xlsx', 'rb').read(), 'base64', 'utf-8') att["Content-Type"] = 'application/octet-stream' att["Content-Disposition"] = 'attachment; filename="日?qǐng)?bào)2017%s.xlsx"' % date_str msgRoot.attach(att) smtp = smtplib.SMTP() smtp.connect('mail.address.com') smtp.login('sender_email@xxx.com', 'sender_password') for k in receiver.split(','): smtp.sendmail('receiver_email@xxx.com', k, msgRoot.as_string()) smtp.quit()

將上面的python腳本后放入連接到數(shù)據(jù)倉(cāng)庫(kù)的服務(wù)器上，在linux下設(shè)置crontab調(diào)度語(yǔ)句，如“10 16 * * * python /home/path/username/auto_email.py”表示每天下午16點(diǎn)10分執(zhí)行/home/ path/username/路徑下的auto_email.py文件。

執(zhí)行代碼后，程序?qū)⒆詣?dòng)執(zhí)行SQL語(yǔ)句連接到數(shù)據(jù)庫(kù)提取數(shù)據(jù)，提數(shù)完畢后將數(shù)據(jù)寫(xiě)入excel文件中，并自動(dòng)發(fā)送郵件到數(shù)據(jù)需求方郵箱。

這樣通過(guò)定時(shí)調(diào)度的腳本即可解決業(yè)務(wù)方每天對(duì)日?qǐng)?bào)數(shù)據(jù)的需求，將數(shù)據(jù)提取人員從繁重的機(jī)械性勞動(dòng)中解放出來(lái)。

三則：一次對(duì)渠道流量異常情況的分析

流量部門(mén)目前對(duì)APP線上推廣需要支付較多的渠道推廣費(fèi)用，但不同渠道帶來(lái)的用戶質(zhì)量、活躍度、消費(fèi)能力參差不齊

為了支持流量部門(mén)高效推廣，減少對(duì)垃圾渠道的投放費(fèi)用。需要對(duì)部分投放費(fèi)用較高，但是營(yíng)收、活躍度轉(zhuǎn)化較低的渠道需要重點(diǎn)分析

對(duì)于渠道流量進(jìn)行分析的幾個(gè)關(guān)鍵指標(biāo)：

根據(jù)AARRR模型，從獲取用戶到用戶付費(fèi)環(huán)節(jié)依次遞進(jìn)的關(guān)系，這里主要從激活、留存、付費(fèi)三個(gè)環(huán)節(jié)展開(kāi)

1）激活環(huán)節(jié)

①激活注冊(cè)轉(zhuǎn)化率：用戶從應(yīng)用商店下載APP后，不一定都會(huì)有注冊(cè)行為。對(duì)于刷下載量、用戶為搶紅包、賺積分等目的而進(jìn)行的下載，后續(xù)的注冊(cè)量會(huì)很低。對(duì)于一些問(wèn)題渠道來(lái)說(shuō)，他的激活注冊(cè)轉(zhuǎn)化率（注冊(cè)量/激活量）會(huì)遠(yuǎn)低于正常渠道；

②激活時(shí)間：在某些特殊情況下（如部門(mén)為沖KPI而刷下載量），一些問(wèn)題渠道的激活時(shí)間會(huì)存在問(wèn)題。正常來(lái)說(shuō)，用戶激活時(shí)間也符合人的正常作息時(shí)間段，而異常渠道因?yàn)榇嬖跈C(jī)器刷量的情況，激活時(shí)間段分布也就沒(méi)有那么規(guī)律了，下圖就是一個(gè)栗子：

橙色和黃色線對(duì)應(yīng)的渠道的激活時(shí)間分布存在一些不正常。

2）留存環(huán)節(jié)

③7日留存率：對(duì)正常渠道來(lái)說(shuō)，該渠道的用戶下載APP是為了使用，后續(xù)的留存會(huì)多一些。而對(duì)于刷量、刷積分下載、搶紅包下載等目的而下載的來(lái)說(shuō)，下載激活后可能接著就卸載掉或再不使用了。從7日留存率這個(gè)指標(biāo)也能看出一些問(wèn)題渠道；

④訪問(wèn)深度：這里就指PV/UV了，對(duì)于渠道來(lái)說(shuō)PV只該渠道一定時(shí)間段內(nèi)的用戶總訪問(wèn)量，UV只該時(shí)間段內(nèi)訪問(wèn)用戶數(shù)，相除代表該渠道每個(gè)用戶平均訪問(wèn)頁(yè)面數(shù)。正常來(lái)說(shuō)，用戶下載了APP即時(shí)不注冊(cè)也是為了使用或查看資訊等目的，因此訪問(wèn)深度不會(huì)很低。而問(wèn)題渠道的用戶根本目的不是為了使用產(chǎn)品，因此這些渠道的訪問(wèn)深度就很低了；

3）付費(fèi)環(huán)節(jié)

⑤用戶獲客成本：對(duì)正常渠道來(lái)說(shuō)，獲取的付費(fèi)用戶量按照AARRR這個(gè)模型一層層下來(lái)，付費(fèi)用戶數(shù)/激活用戶數(shù)（即付費(fèi)用戶獲取比例）會(huì)在一個(gè)正常邏輯區(qū)間內(nèi)。而對(duì)于垃圾渠道來(lái)說(shuō)，激活用戶人數(shù)可能會(huì)很多，但是付費(fèi)用戶人數(shù)很少，就會(huì)導(dǎo)致付費(fèi)用戶獲取比例極低，用戶獲取成本高的驚人....

現(xiàn)在刷下載的供應(yīng)商很多，在流量分析時(shí)候?qū)λ⑾螺d的供應(yīng)商進(jìn)行一些調(diào)研，了解他們的刷量模式和報(bào)價(jià)也是對(duì)分析很有幫助的。這會(huì)刷量不僅能刷激活、還能刷注冊(cè)、刷留存、刷好評(píng)....反正我們分析什么指標(biāo)，他們都能刷這些指標(biāo)的值....

但是垃圾渠道就是垃圾渠道，再怎么刷還是能分析出問(wèn)題和破綻的。