暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 归因分析SQL实现

归因分析SQL实现

填充空白 2022-12-22
1499

碎碎念：

这周全国各地大大小小的朋友们都加入了羊群，小主我也未能幸免，虽然前2天高烧到40多度，烧到自己从来没想过这辈子还能烧这么高，烧到自己都开始怀疑，是不是要烧坏了咋还没降温，好在第3天慢慢恢复，目前仍有咳喘。期间精神正常，游离在各种群家人朋友同学同事间，相互关心相互帮助的状态中。


坊间流传的新冠10大酷刑，你目前经历了几个懂得都懂~

头脑清醒，那就不要停下。还是把想到的东西记录下来。

今天聊聊归因分析模型。以及怎么用SQL实现。

1.归因分析模型

在网上搜到一篇，写的很详细，也很好理解。包括什么是归因分析，几种常见的归因模型，以及如何选择归因模型，不明白的可以仔细看看。--不知道为什么粘贴不了链接和超链接。可自行搜索以下内容即可。

(7条消息) 归因分析(Attribution Analysis)模型解析_只有光头才能强的博客-CSDN博客_归因算法）

这里不再仔细介绍，以下会简要说几句什么是归因分析，几种常见的归因模型。

1.1 什么是归因分析？
归因，就是归结原因，归结源头，归功于谁？大家常常听过广告归因，在互联网广告行业，广告主在抖音，在头条，朋友圈，在微博，在知乎等不同渠道都投放了广告，用户在多个渠道浏览并点击了广告，最后下载了某应用或者购买了某商品。那么，不同渠道对下载或成单的贡献度如何？分别带来了多少收入？再比如，在应用内，用户进行了关键词搜索，推荐位点击，运营位点击后均进行了商品详情页浏览，最后进行了下单。那该笔订单该贡献给哪个运营活动？不同运营位的贡献如何？
归因分析(Attribution Analysis)要解决的问题就是广告效果的产生，其功劳应该如何合理的分配给哪些渠道。拓展下场景的话，归因分析用于分析待归因事件对目标类事件的发生的转化贡献。
对于上述广告投放场景，目前最普遍的解决方案，是采用末次触点归因，即认为是最后一次触点促进了下单或者下载。显然这种归因方式并不完全准确。而对于站内商品购买来说，各推广位广告栏位对目标事件都有贡献。我们需要依据业务特征，选择相对合理的归因方式来评估不同渠道或者不同推广位的贡献。当我们知道不同位置对目标转化的贡献，那么在内部资源匮乏情况下，我们可以更加合理的分配业务比重，甚至据此评估各业务团队的贡献。
             
1.2 常见的归因分析模型
包括首次触点归因，末次触点归因,线性归因，位置归因，时间递减归因等。其他参考

(7条消息) 归因分析(Attribution Analysis)模型解析_只有光头才能强的博客-CSDN博客_归因算法）或其他文献。

  • 首次触点归因：首次互动的渠道获得100%的功劳。
  • 末次触点归因：末次互动的渠道获得100%的功劳。
  • 线性归因：对于链路上所有的渠道，平等地分配他们的贡献权重。
  • 位置归因：基于位置的归因模型，也叫U型归因模型，它其实是混合使用了首次互动归因和末次互动归因的结果。即首末次贡献该40%的功劳，链路上其他触点平均分配剩下的20%的功劳。
  • 时间递减归因：链路上离目标转化事件最近的贡献最大，时间越远贡献越小。

1.3归因时间段
在实现归因分析之前。先再理一下归因分析的几个要素：目标事件，待归因事件，归因模型以及归因时间段。
在上边还未提到的是归因时间段，也就是说我们选择目标事件多久之前的待归因事件拿来分析贡献。这里的多久就是我们要限制的归因时间段或者叫窗口期。
因为很久很久之前如果有发生过某个广告位浏览，对目标事件的影响其实是微乎其微的，用户一般可能早已忘记之前曾经浏览的内容，而且用户的决策周期和购买周期一般也不必是几个月，如果几个月都没有购买或者做某事，可能当时已经买过了，本次再次发生购买(目标事件)，可能已经是新的需求了。

一般窗口期定义多久呢。比如7天、30天、90天、180天等，主要是为了规避掉一些时间上的干扰因素，具体我们还是要根据业务场景和分析需求而定，用户大致会在多长时间内做决策。可以分析通过分析用户的需求间隔等等。

2.归因分析的SQL实现

在说到在有行为序列时，如何实现归因分析。我们还是默认会有一张用户行为日志表。
注：这里的SQL实现，绝不代表任何数据产品的底层技术，纯属个人兴趣。

假设我们定义：
目标转化事件为：订单结果，
待归因事件为：运营位点击，搜索结果点击
归因时间段：1天
归因模型：首次归因("没有理由选择首次，仅做举例演示")

在进行归因分析时，各归因模型通用的计算逻辑:
    1.首先找到时间范围内的目标事件。然后找每个目标事件往前推窗口期内的待归因事件。
    2.对每个目标事件窗口期内的待归因事件按照时间进行排序。
    3.对目标事件窗口期内的待归因事件，按照不同的分析模型规则，设置权重。

    2.1首次归因：
    按上述1，2步骤后找到每个目标事件窗口期内的待归因事件并根据时间升序排序，首个事件的作用最强。筛选出首个事件即为贡献最大的事件称为功劳事件，而后统计功劳事件的贡献次数，人数，贡献总和，即为不同待归因事件贡献情况。具体如下：
      ##首次归因
      select c.e2,count(distinct c.u1) as renshu,count(c.u1) as gongxian_cishu,sum(mm) as gongxian_money
      from
      (select a.user_id as u1,a.time as t1,a.event as e1,a.mm,
       b.event as e2,b.time as t2,rank() over (partition by a.user_id,a.time order by a.user_id,b.time asc) as rk
      from
      (select user_id,event,date,time,total_price_of_commodity as mm
      from events
      where event='PayOrderDetail' and date>='2020-09-24' and date<='2020-09-30')a
      left join
      (select user_id,event,time
       from events
      where (event='BannerClick' or event='SearchResultClick') and date>='2020-09-22'and date<='2020-09-30')b
      on a.user_id=b.user_id and b.time>date_sub(a.time, interval 1 day) and b.time<=a.time)c
      where c.rk=1
      group by c.e2

      2.2末次归因：
      按上述1，2步骤后找到每个目标事件窗口期内的待归因事件并根据时间降序排序，首个事件的作用最强。筛选出首个事件即为贡献最大的事件称为功劳事件，而后统计功劳事件的贡献次数，人数，贡献总和，即为不同待归因事件贡献情况。具体如下：
        ##末次归因
        select c.e2,count(distinct c.u1) as renshu,count(c.u1) as gongxian_cishu,sum(mm) as gongxian_money
        from
        (select a.user_id as u1,a.time as t1,a.event as e1,a.mm,
         b.event as e2,b.time as t2,rank() over (partition by a.user_id,a.time order by a.user_id,b.time desc) as rk
        from
        (select user_id,event,date,time,total_price_of_commodity as mm
        from events
        where event='PayOrderDetail' and date>='2020-09-24' and date<='2020-09-30')a
        left join
        (select user_id,event,time
         from events
        where (event='BannerClick' or event='SearchResultClick') and date>='2020-09-22'and date<='2020-09-30')b
        on a.user_id=b.user_id and b.time>date_sub(a.time, interval 1 day) and b.time<=a.time)c
        where c.rk=1
        group by c.e2


        2.3 线性归因
        当线性归因时，按上述1，2步骤后找到每个目标事件窗口期内的待归因事件并根据时间升序排序，计算链路上的总数量，并平均分配链路上每个事件的贡献比例，则同时可计算链路上每个事件平均贡献的总金额。最终统计所有目标事件的链路情况，可得可得不同待归因事件对目标事件的总体贡献情况，包括贡献人数，贡献次数，贡献金额。
          ##线性归因
          select d.e2,count(distinct d.u1) as renshu,sum(d.quanzhong) as gongxian_cishu,sum(d.mm*quanzhong) as gongxian_money
          from
          (select c.u1,c.t1,c.e1,c.mm,
          c.e2,c.t2,
          1/cn as quanzhong


          from
          (select a.user_id as u1,a.time as t1,a.event as e1,a.mm,
          b.event as e2,b.time as t2,
          count() over (partition by a.user_id,a.time) as cn,
          rank() over (partition by a.user_id,a.time order by a.user_id,b.time asc) as rk1,
          rank() over (partition by a.user_id,a.time order by a.user_id,b.time desc) as rk2
          from
          (select user_id,event,date,time,total_price_of_commodity as mm
          from events
          where event='PayOrderDetail' and date>='2020-09-24' and date<='2020-09-30')a
          left join
          (select user_id,event,time
           from  events
          where (event='BannerClick' or event='SearchResultClick') and date>='2020-09-22'and date<='2020-09-30')b
          on a.user_id=b.user_id and b.time>date_sub(a.time, interval 1 day) and b.time<=a.time
          )c
          )d


          group by d.e2


          2.4 位置归因
          当位置归因时，首先找到每个待归因事件窗口期内的待归因事件并进行升序排序。然后对首尾事件各分配40%，计算链路上的总数量，并将剩余的20%平均分配给链路上其他位置每个事件。根据所有目标事件的链路情况，可得不同待归因事件对目标事件的总体贡献情况，包括贡献人数，贡献次数，贡献金额。代码如下：
            ##位置归因
            select d.e2,count(distinct d.u1) as renshu,
            sum(d.quanzhong) as cishu,sum(d.mm*d.quanzhong) as gongxian_money


            from
            (select
            c.u1,c.t1,c.mm,c.e1,c.e2,c.t2,
            (case when cn=1 then 1
            When cn=2 then 0.5
            when cn>2 and (rk1=1 or rk2=1) then 0.4
            when cn>2 and (rk1>1 and rk2>1) then 0.2/(cn-2) else 0 end )as quanzhong


            from
            (select a.user_id as u1,a.time as t1,a.event as e1,a.mm,
             b.event as e2,b.time as t2,
             count() over (partition by a.user_id,a.time) as cn,
            rank() over (partition by a.user_id,a.time order by a.user_id,b.time asc) as rk1,
            rank() over (partition by a.user_id,a.time order by a.user_id,b.time asc) as rk2
            from
            (select user_id,event,date,time,total_price_of_commodity as mm
            from events
            where event='PayOrderDetail' and date>='2020-09-24' and date<='2020-09-30')a
            left join
            (select user_id,event,time
             from events
            where (event='BannerClick' or event='SearchResultClick') and date>='2020-09-22'and date<='2020-09-30')b
            on a.user_id=b.user_id and b.time>date_sub(a.time, interval 1 day) and b.time<=a.time)c
            )d
            group by d.e2


            2.5 时间递减归因

            看了上述模型的计算。应该也看到了，各类归因分析模型的区别主要是如何分配链路上待归因事件的比重。假设时间递减归因中，待归因事件比重根据距离归因事件的时长的倒数，则需再对倒数做归一化， 【 1/(t(i)-t0)】除以所有【1/(t(i)-t0)】求和。其他亦可类比。


            仔细看看，没那么难吧。这次分享的主要就是这些。欢迎留言私聊：


            最后碎碎的：

            疫情即将迎来胜利。希望大家坚持到最后，进入决赛圈的继续坚持，注意防护。加入羊圈的不恐惧，注意好好休养，早日康复。杨过的注意防护以防2次感染。虽然致死率不高，但是生命只有一次，大家都好好照顾自己。

            希望大家能早日摘下口罩，尽情的呼吸自由的空气！




            sqldatepartitionselect
            文章转载自填充空白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

            评论