用戶畫像番外篇之隨筆三則

一則：開發(fā)上的一點記錄

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

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

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

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

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

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

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

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

①開發(fā)人員對畫像標簽只需寫Hive SQL腳本，傳到服務器對應目錄下；

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

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

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

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

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

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

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

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

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

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

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

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

近期在不斷補充學習新知識，spark要搞起來了、shell命令要用熟起來了。都要投入精力搞。

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

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

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

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

這段時間在對流量部門提供數(shù)據(jù)方面的支持，把每天自動發(fā)送郵件的腳本講一講吧，雖然很基礎，好像沒什么可以說的 ...

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

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

#coding: utf-8 search_data = """ 創(chuàng)建臨時表查詢昨日運營數(shù)據(jù)""" report_data = ''' select * from 上一步創(chuàng)建的臨時表 ''' 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') # 打開服務器存儲路徑下的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語句 # 取出來數(shù)據(jù) h5_result.execute(report_data) # 取出來數(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] #關閉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, 附件是運營日報，請查收。 """ msgRoot = MIMEMultipart('mixed') msgRoot['Subject'] = unicode(u'日報-%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="日報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ù)倉庫的服務器上，在linux下設置crontab調(diào)度語句，如“10 16 * * * python /home/path/username/auto_email.py”表示每天下午16點10分執(zhí)行/home/ path/username/路徑下的auto_email.py文件。

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

這樣通過定時調(diào)度的腳本即可解決業(yè)務方每天對日報數(shù)據(jù)的需求，將數(shù)據(jù)提取人員從繁重的機械性勞動中解放出來。

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

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

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

對于渠道流量進行分析的幾個關鍵指標：

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

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

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

②激活時間：在某些特殊情況下（如部門為沖KPI而刷下載量），一些問題渠道的激活時間會存在問題。正常來說，用戶激活時間也符合人的正常作息時間段，而異常渠道因為存在機器刷量的情況，激活時間段分布也就沒有那么規(guī)律了，下圖就是一個栗子：

橙色和黃色線對應的渠道的激活時間分布存在一些不正常。

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

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

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

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

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

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

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