首页 最新文章网站编程正文

用VBA实现间接转化模型

space: normal">


在上次对间接转化模型进行理论论证和推演,并确认了其自洽性后,对归首、归末和归均三个模型进行了代码实现。
实现的底层基础:基于Matomo的访问数据和订单数据;
实现的主要工具:ExcelVBA
再结合mysql存储过程,其间设计里几个中间表用于数据沉淀;
交互方面充分运用excel自身的一些功能在结合界面化的技巧充分满足用户体验方面操作便捷性与易用性的要求。

以回溯90天为例(演示数据为模拟数据)


image.png

image.png

https://www.imitker.com/zb_users/upload/2020/01/202001301580393443825405.png" alt="image.png" style="color: rgb(51, 51, 51); font-family: mp-quote, -apple-system-font, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif; font-size: 17px; text-align: center; white-space: normal;"/>

image.png

image.png

image.png

insert into ma_product_indirect_90   SELECTconv(hex(a.idvisitor), 16, 16) AS idvisitor,DATE_FORMAT(DATE_ADD(b.visit_first_action_time,INTERVAL 8 HOUR),'%Y-%m-%d %T') as 访问时间,b.idvisit as 访问,
DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL 8 HOUR),'%Y-%m-%d %T') as 出单时间,a.idorder as 订单号,a.revenue as 保费,b.idsite pcwap,
-- 百度SEM
  (case when b.idsite in ('1','3') and (b.campaign_source in ('百度pc','百度yd','a01','a02') or right(b.referer_name,4) in ('bdpc','bdyd') or left(b.referer_name,3) in ('a01','a02')) then 'SEM-baidu'
   -- 360SEM
  when b.idsite in ('1','3') and (b.campaign_source in ('360pc','360yd','360','a03','a04') or right(b.referer_name,5) in ('360pc','360yd') or left(b.referer_name,3) in ('a03','a04')) then 'SEM-360'
-- 搜狗SEM
when b.idsite in ('1','3') and (b.campaign_source in ('搜狗pc','搜狗yd','a05','a06') or right(b.referer_name,4) in ('sgpc','sgyd') or left(b.referer_name,3) in ('a05','a06')) then 'SEM-sogou'
-- 其它SEM
when b.idsite in ('1','3') and b.campaign_source is null and right(b.referer_name,2) in ('pc','yd') then 'SEM-other'
-- 百度品专
when b.idsite in ('1','3') and (b.campaign_keyword ='kxb' or b.referer_keyword ='kxb' or b.referer_name in ('sp0.baidu.com' ,'bzclk.baidu.com') or b.campaign_source in ('bppz','a07','a08')) then '品专-baidu'
-- 360品专
when b.idsite in ('1','3') and (b.campaign_keyword ='360kxb' or b.referer_keyword ='360kxb' or b.campaign_source in ('a09','a10')) then '品专-360'
-- 百度SEO
when b.idsite in ('1','3') and  b.referer_name in ('baidu.com','m.baidu.com','www.baidu.com','Baidu') then 'SEO-baidu'
-- 360SEO
when b.idsite in ('1','3') and  b.referer_name in ('so.com','www.so.com','m.so.com','360search') then 'SEO-360'
-- 搜狗SEO
when b.idsite in ('1','3') and  b.referer_name in ('sogou.com','www.sogou.com','m.sogou.com','Sogou') then 'SEO-sogou'
-- 必应SEO
when b.idsite in ('1','3') and  b.referer_name = 'Bing' then 'SEO-bing'
-- 谷歌SEO
when b.idsite in ('1','3') and  b.referer_name ='Google' then 'SEO-google'
-- 神马SEO
when b.idsite in ('1','3') and  b.referer_name in ('sm.cn','yz.m.sm.cn','quark.sm.cn') then 'SEO-神马'
-- 其它SEO
when b.idsite in ('1','3') and b.referer_type = '2' and b.referer_name Not in ('baidu.com','m.baidu.com','www.baidu.com','Baidu','so.com','www.so.com','m.so.com','360search','sogou.com','www.sogou.com','m.sogou.com','Sogou','Bing','Google') then 'SEO-other'
-- 小程序
when b.idsite in ('1','3') and b.campaign_source in ('xiaochengxu','小程序','a15') then '小程序'
-- 微信
when b.idsite in ('1','3') and ((b.campaign_source in ('wx','微信','a11') and b.referer_name <> 'zhwz') or left(b.referer_name,2) = '推文' or RIGHT (b.campaign_keyword, 2) = 'wx' or RIGHT (b.referer_keyword, 2) = 'wx') then '微信'
-- 知乎
when b.idsite in ('1','3') and  (b.campaign_source ='a12' or b.referer_name in ('zh','zhhd','hd','zhwz','wz','zhpl','pl','知乎','link.zhihu.com')) then '知乎'
-- 网站
when b.idsite in ('1','3') and b.referer_type = '3' and b.referer_name not in ('sp0.baidu.com','bzclk.baidu.com','zhhd','hd','zhwz','wz','zhpl','pl','知乎','link.zhihu.com','wechat_session','m.baidu.com','www.baidu.com','www.so.com','m.so.com','www.sogou.com','m.sogou.com','www.google.com','m.google.com','yz.m.sm.cn','m.sm.cn') and RIGHT (b.referer_name, 2) not in ('pc', 'yd') then '网站'
-- 老保顾
when b.idsite in ('1','3') and  left(b.referer_name,3) = 'zh0' then '老保顾'
-- 社交
when b.idsite in ('1','3') and  (b.campaign_medium = 'social' or b.referer_name in ('wechat_session','wechat_timeline') or b.campaign_source in ('wechat_session','wechat_timeline')) then '社交'
-- 自媒体
when b.idsite in ('1','3') and  b.campaign_source in ('wxd0','a16') then '自媒体'
-- 引荐
when b.idsite in ('1','3') and  (b.campaign_source in ('芋儿拍','adconly','yj','a14') or right(b.referer_name,2) = '推广') then '引荐'
-- 直入
when b.idsite in ('1','3') and b.referer_type = '1' then '直入'
-- 快排
when b.idsite in ('11','14')  then '快排'
-- 其它
else '其它'end ) as 推广渠道,
b.referer_type as 初始来源,
b.campaign_source as 推广来源,b.campaign_medium as 推广计划,b.campaign_name as 推广单元,
case when b.campaign_keyword is null then b.referer_keyword else b.campaign_keyword end as 推广词,
b.campaign_content as 推广创意,
case when b.campaign_keyword = 'member_center_wx' then '会员中心wx' 
 when b.campaign_keyword = 'claim_quickly_wx' then '快速理赔wx'
else b.referer_name end as 来源名称,b.referer_keyword as 来源关键词
FROMma_log_conversion a,ma_log_visit b 
WHERE  a.idvisitor=b.idvisitor and a.idgoal = '0'
--  and DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL 8 HOUR),'%Y%m%d') >='20190601'  and DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL 8 HOUR),'%Y%m%d') <='20200107'
 and DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL 8 HOUR),'%Y%m%d') = DATE_FORMAT(DATE_SUB(SYSDATE(), INTERVAL 1 DAY),'%Y%m%d')
and DATE_FORMAT(DATE_ADD(b.visit_first_action_time,INTERVAL 8 HOUR),'%Y%m%d %T') >= DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL -90*24+8 HOUR),'%Y%m%d %T')
and DATE_FORMAT(DATE_ADD(b.visit_first_action_time,INTERVAL 8 HOUR),'%Y%m%d %T') <= DATE_FORMAT(DATE_ADD(a.server_time,INTERVAL 8 HOUR),'%Y%m%d %T')group by b.idvisit,a.idorder
order by a.idorder,b.visit_first_action_time

image.png

'Option Explicit
Sub indirect()
    '模板检查
    If Workbooks.Count = 0 Then
        MsgBox ("请打开间接转化模型的模板")
        Exit Sub
    ElseIf ActiveSheet.Range("B3").Value <> "refererType" Or ActiveSheet.Range("E3").Value <> "keyword" Or ActiveSheet.Range("C1").Value <> "开始日期:" _
            Or ActiveSheet.Range("L2").Value <> "归均模型" Then
            MsgBox ("当前模板不是间接转化模型的模板,请确认!")
            Exit Sub
        Else
            '清空数据
            ActiveSheet.Range("FA:FT").ClearContents
            ActiveSheet.Range("A5:E1500").ClearContents
            Cells(2, 3).Value = Form_indirect.ComboBox1.Value & "-" & Form_indirect.ComboBox2.Value & "-" & Form_indirect.ComboBox3.Value
            StartDate = Form_indirect.ComboBox1.Value & "-" & Form_indirect.ComboBox2.Value & "-" & Form_indirect.ComboBox3.Value
            Cells(2, 4).Value = Form_indirect.ComboBox6.Value & "-" & Form_indirect.ComboBox5.Value & "-" & Form_indirect.ComboBox4.Value
            EndDate = Form_indirect.ComboBox6.Value & "-" & Form_indirect.ComboBox5.Value & "-" & Form_indirect.ComboBox4.Value
            Cells(2, 5).Value = Form_indirect.ComboBox7.Value & "天"
            Period = Form_indirect.ComboBox7.Value & "天"
            flow_period = Form_indirect.ComboBox7.Value
            indirectTable = "ma_product_indirect_" & flow_period
            last_indirectTable = "ma_product_indirect_" & flow_period & "_last"
            first_indirectTable = "ma_product_indirect_" & flow_period & "_first"
            Form_indirect.Hide
            '定义mysql数据源(需要先在本地安装驱动及创建好数据源)
            Dim indirectoConn As New ADODB.Connection
            '连接mysql数据库
            indirectoConn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=rm-test.mysql.RDS.aliyuncs.com;PORT=3306;DB=matomo;UID=user;PWD=***;OPTION=3;"
            indirectoConn.Open
            '判断是否能够正常连接数据库
            If indirectoConn.State <> 1 Then
                Set indirectoConn = Nothing
                MsgBox "数据库联接失败!"
                Exit Sub
            End If
               '定义sql语句变量
            Dim all_channle_strSQL As String
            all_channle_strSQL = "SELECT  case when idsite='1' then 'PC' when idsite = '3' then 'WAP' else 'other' end,referer_channel, case when referer_name is null or referer_name='' then ' ' else replace(referer_name,'?','特殊处理') end,  "
            all_channle_strSQL = all_channle_strSQL & " case when campaign_medium is null or campaign_medium='' then ' ' else replace(campaign_medium,'?','特殊处理') end,"
            all_channle_strSQL = all_channle_strSQL & " case when campaign_keyword is null or campaign_keyword='' then ' ' else replace(campaign_keyword,'?','特殊处理')  end FROM  " & indirectTable
            all_channle_strSQL = all_channle_strSQL & " WHERE   DATE_FORMAT(buy_time, '%Y-%m-%d') >= '" & StartDate & "'  AND DATE_FORMAT(buy_time, '%Y-%m-%d')             all_channle_strSQL = all_channle_strSQL & " Group BY    idsite,referer_channel,  referer_name,  campaign_medium,  campaign_keyword"
            Dim all_channlerstMain  As ADODB.Recordset
            Set all_channlerstMain = New ADODB.Recordset
            '执行sql获取结果,adLockOptimistic表示执行过程中,数据可以正常操作
            all_channlerstMain.Open all_channle_strSQL, indirectoConn, 3, 3
            '数据非空判断
            If all_channlerstMain.EOF Then
                MsgBox "没有查询到任何渠道数据!"
                Exit Sub
            Else
                '将查询结果,从B5开始放置到当前的excel中(比一条条的处理效率大幅提升)
                Range("A5").CopyFromRecordset all_channlerstMain
            End If
            all_channlerstMain.Close: Set all_channlerstMain = Nothing
            '定义归末模型sql语句变量
            Dim L_indirectstrSQL As String
            '初始化
            L_indirectstrSQL = ""
            'L_indirectstrSQL
            L_indirectstrSQL = "SELECT  case when b.idsite='1' then 'PC' when b.idsite = '3' then 'WAP' else 'other' end,"
            L_indirectstrSQL = L_indirectstrSQL & " b.referer_channel,case when b.referer_name is null then ' ' else replace(b.referer_name,'?','特殊处理')  end ,"
            L_indirectstrSQL = L_indirectstrSQL & " case when b.campaign_medium is null then ' ' else replace(b.campaign_medium,'?','特殊处理')   end,"
            L_indirectstrSQL = L_indirectstrSQL & " case when b.campaign_keyword is null or b.campaign_keyword = '' then ' ' else replace(b.campaign_keyword,'?','特殊处理')  end,"
            L_indirectstrSQL = L_indirectstrSQL & " count(b.idorder),round(sum(b.price),2)"
            L_indirectstrSQL = L_indirectstrSQL & " from " & last_indirectTable & " b"
            L_indirectstrSQL = L_indirectstrSQL & "  where DATE_FORMAT(b.buy_time,'%Y-%m-%d') >= '" & StartDate & "' and DATE_FORMAT(b.buy_time,'%Y-%m-%d')             L_indirectstrSQL = L_indirectstrSQL & "  group by b.idsite,b.referer_channel,b.referer_name,b.campaign_medium,b.campaign_keyword"
            '显示进度条
            Call ShowPercentMsg("正在全力解锁" & Period & "归末模型")
            'Cells(10, 1).Value = L_indirectstrSQL
            '定义数据源执行结果对象
            Dim L_indirectrstMain  As ADODB.Recordset
            Set L_indirectrstMain = New ADODB.Recordset
            '执行sql获取结果,adLockOptimistic表示执行过程中,数据可以正常操作
            L_indirectrstMain.Open L_indirectstrSQL, indirectoConn, 3, 3
            'Cells(1, 17).Value = strSQL
            '数据非空判断
            If L_indirectrstMain.EOF Then
                MsgBox "归末模型没有查询到任何数据!"
                'Exit Sub
            Else
                '将归末模型的查询结果,从FA1开始放置到当前的excel中(比一条条的处理效率大幅提升)
                Range("FA2000").CopyFromRecordset L_indirectrstMain
            End If
            '关闭数据库连接
            L_indirectrstMain.Close: Set L_indirectrstMain = Nothing
            '定义归首模型sql语句变量
            Dim F_indirectstrSQL As String
            '初始化
            F_indirectstrSQL = ""
            F_indirectstrSQL = "SELECT  case when b.idsite='1' then 'PC' when b.idsite = '3' then 'WAP' else 'other' end,"
            F_indirectstrSQL = F_indirectstrSQL & " b.referer_channel,case when b.referer_name is null then ' ' else replace(b.referer_name,'?','特殊处理')  end ,"
            F_indirectstrSQL = F_indirectstrSQL & " case when b.campaign_medium is null then ' ' else replace(b.campaign_medium,'?','特殊处理')   end,"
            F_indirectstrSQL = F_indirectstrSQL & " case when b.campaign_keyword is null or b.campaign_keyword = '' then ' ' else replace(b.campaign_keyword,'?','特殊处理')  end,"
            F_indirectstrSQL = F_indirectstrSQL & " count(b.idorder),round(sum(b.price),2)"
            F_indirectstrSQL = F_indirectstrSQL & " from " & first_indirectTable & " b"
            F_indirectstrSQL = F_indirectstrSQL & "  where DATE_FORMAT(b.buy_time,'%Y-%m-%d') >= '" & StartDate & "' and DATE_FORMAT(b.buy_time,'%Y-%m-%d')             F_indirectstrSQL = F_indirectstrSQL & "  group by b.idsite,b.referer_channel,b.referer_name,b.campaign_medium,b.campaign_keyword"
            '显示进度条
            Call ShowPercentMsg("正在全力解锁" & Period & "归首模型")
            'Cells(10, 1).Value = indirectstrSQL
            '定义数据源执行结果对象
            Dim F_indirectrstMain  As ADODB.Recordset
            Set F_indirectrstMain = New ADODB.Recordset
            '执行sql获取结果,adLockOptimistic表示执行过程中,数据可以正常操作
            F_indirectrstMain.Open F_indirectstrSQL, indirectoConn, 3, 3
            'Cells(1, 17).Value = strSQL
            '数据非空判断
            If F_indirectrstMain.EOF Then
                MsgBox "归首模型没有查询到任何数据!"
                'Exit Sub
            Else
                '将归首模型的查询结果,从FH1开始放置到当前的excel中(比一条条的处理效率大幅提升)
                Range("FH2000").CopyFromRecordset F_indirectrstMain
            End If
            '关闭数据库连接
            F_indirectrstMain.Close: Set F_indirectrstMain = Nothing
            '定义归均模型sql语句变量
            Dim AVE_indirectstrSQL As String
            '初始化
            AVE_indirectstrSQL = ""
            AVE_indirectstrSQL = "select case when c.idsite='1' then 'PC' when c.idsite = '3' then 'WAP' else 'other' end,c.referer_channel,case when c.referer_name is null then ' ' else replace(c.referer_name,'?','特殊处理')  end ,case when c.campaign_medium is null then ' ' else replace(c.campaign_medium,'?','特殊处理')  end,case when c.campaign_keyword is null or c.campaign_keyword =''  then ' ' else replace(c.campaign_keyword,'?','特殊处理')  end,count(c.idorder),sum(c.aveprice)"
            AVE_indirectstrSQL = AVE_indirectstrSQL & " from (select a.idsite,a.idorder,a.referer_channel,a.referer_name,a.campaign_medium,a.campaign_keyword,a.price,a.channlevisitTimes,b.visitTimes,round(a.channlevisitTimes*a.price/b.visitTimes,2) as aveprice"
            AVE_indirectstrSQL = AVE_indirectstrSQL & " from (select idsite,idorder,referer_channel,referer_name,campaign_medium,campaign_keyword,price,count(referer_channel) as channlevisitTimes from ma_product_indirect_90"
            AVE_indirectstrSQL = AVE_indirectstrSQL & " where DATE_FORMAT(buy_time,'%Y-%m-%d') >= '" & StartDate & "' and DATE_FORMAT(buy_time,'%Y-%m-%d')             AVE_indirectstrSQL = AVE_indirectstrSQL & " group by idsite,idorder,referer_channel,referer_name,campaign_medium,campaign_keyword,price) a,"
            AVE_indirectstrSQL = AVE_indirectstrSQL & " (select idorder,count(referer_channel) as visitTimes from ma_product_indirect_90"
            AVE_indirectstrSQL = AVE_indirectstrSQL & " group by idorder) b where a.idorder=b.idorder) c group by c.idsite,c.referer_channel,c.referer_name,c.campaign_medium,c.campaign_keyword;"
            '显示进度条
            Call ShowPercentMsg("正在全力解锁" & Period & "归均模型")
            'Cells(10, 1).Value = indirectstrSQL
            '定义数据源执行结果对象
            Dim AVE_indirectrstMain  As ADODB.Recordset
            Set AVE_indirectrstMain = New ADODB.Recordset
            '执行sql获取结果,adLockOptimistic表示执行过程中,数据可以正常操作
            AVE_indirectrstMain.Open AVE_indirectstrSQL, indirectoConn, 3, 3
            'Cells(1, 17).Value = strSQL
            '数据非空判断
            If AVE_indirectrstMain.EOF Then
                MsgBox "归首模型没有查询到任何数据!"
                'Exit Sub
            Else
                '将归首模型的查询结果,从FH1开始放置到当前的excel中(比一条条的处理效率大幅提升)
                Range("FO2000").CopyFromRecordset AVE_indirectrstMain
            End If
            '关闭数据库连接
            AVE_indirectrstMain.Close: Set AVE_indirectrstMain = Nothing
            indirectoConn.Close: Set indirectoConn = Nothing
            
            '根据归首保费由高到低排序
            Dim j As Integer
            j = SumData(ActiveSheet, 1)
            ActiveSheet.sort.SortFields.Clear
            ActiveSheet.Sort.SortFields.Add2 Key:=Range("J5:J" & j), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            With ActiveSheet.Sort
                .SetRange Range("A5:O" & j)
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .APPly
            End With
            
            '打开屏幕刷新
            application.ScreenUpdating = True
            '焦点定位到文件首
            Cells(1, 1).Select
        End If
        '完成提醒
        MsgBox ("所有模型已全部解锁!")
    End Sub

写在最后

画外音:间接转化模型的最大价值在模型初探的时候已经说过,问题是该模型对实际的运营决策如何助力,如何转化为可落地的实实在在的具体动作,这就在于深度挖掘数据背后的本质,而对数据的理解涉及行为学、心理学,运营专业知识,这不单单是简单的扩大投入或收缩投入,更在于运营者对人性的把握,对世界的理解以及对数字本身的认知……

image.png


评论

百度搜索

新浪微博热搜榜

站点信息

  • 文章总数:511
  • 页面总数:7
  • 分类总数:29
  • 标签总数:869
  • 评论总数:286
  • 浏览总数:6900185
Copyright ©2019-2020.Powered by©刘相涛 辽ICP备14000512号-5