一則：開發上的一點記錄

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

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

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

這周到現在又開發了6個標簽，寫了一個調度腳本，正在進行著一次數據邏輯調優。下面挑兩個重要點的記錄一下：

1、任務調度腳本開發

畫像數據目前是寫在Spark SQL里面，通過每天定時任務調python腳本，來執行Spark SQL。

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

所以必須要優化一下流程。優化方式就是：

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

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

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

2、數據邏輯調優

開發出的標簽很多了，但有的標簽邏輯復雜，需要做進一步調優，提高每日跑批作業的執行效率，這里就與日志數據相關的標簽為例。

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

而記錄用戶訪問行為的日志數據的情況是：

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

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

后來做的調優方式是：

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

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

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

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

寫到這會已經周五早上53分了，過幾個小時還要繼續投入。這周的一些想法先總結到這里。

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

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

二則：自動發送郵件腳本

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

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

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

#coding: utf-8 search_data = """ 創建臨時表查詢昨日運營數據""" report_data = ''' select * from 上一步創建的臨時表 ''' 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環境 wb = openpyxl.load_workbook('/home/path/username/daily_report_v1.xlsx') # 打開服務器存儲路徑下的excel文件 # 連接HIVE環境 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) # 執行HQL語句 # 取出來數據 h5_result.execute(report_data) # 取出來數據 h5_result = h5_result.fetchall() #放到sheet里面去 sheet = wb.get_sheet_by_name('daily_report') #daily_report表 #清除歷史數據 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 = '' #填充結果數據 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腳本后放入連接到數據倉庫的服務器上，在linux下設置crontab調度語句，如“10 16 * * * python /home/path/username/auto_email.py”表示每天下午16點10分執行/home/ path/username/路徑下的auto_email.py文件。

執行代碼后，程序將自動執行SQL語句連接到數據庫提取數據，提數完畢后將數據寫入excel文件中，并自動發送郵件到數據需求方郵箱。

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

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

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

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

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

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

1）激活環節

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

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

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

2）留存環節

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

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

3）付費環節

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

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

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