大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载

点一下关注吧！！！非常感谢！！持续更新！！！

Java篇开始了！

目前开始更新 MyBatis，一起深入浅出！

目前已经更新到了：

• Hadoop（已更完）
• HDFS（已更完）
• MapReduce（已更完）
• Hive（已更完）
• Flume（已更完）
• Sqoop（已更完）
• Zookeeper（已更完）
• HBase（已更完）
• Redis （已更完）
• Kafka（已更完）
• Spark（已更完）
• Flink（已更完）
• ClickHouse（已更完）
• Kudu（已更完）
• Druid（已更完）
• Kylin（已更完）
• Elasticsearch（已更完）
• DataX（已更完）
• Tez（已更完）
• 数据挖掘（已更完）
• Prometheus（已更完）
• Grafana（已更完）
• 离线数仓（正在更新…）

章节内容

上节我们完成了如下的内容：

• 会员活跃度 WDS 与 ADS 导出到 MySQL
• 广告业务 需求分析

点击次数

需求分析

广告AD

• action 用户行为；0 曝光；1 曝光后点击；2 购买
• duration 停留时长
• shop_id 商家id
• event_type ad"
• ad_type 格式类型；1 JPG；2 PNG；3 GIF；4 SWF
• show_style 显示风格，0 静态图；1 动态图
• product_id 产品id
• place 广告位置；首页=1，左侧=2，右侧=3，列表页=4
• sort 排序位置

分时统计

• 曝光次数、不同用户ID数（公共信息中的uid）、不同用户数（公共信息中的device_id）
• 点击次数、不同用户ID数、不同用户数（device_id）
• 购买次数、不同用户ID数、不用用户数（device_id）

DWD => DWS(不需要) => ADS，在某个分析中不是所有的层都会用到。

创建ADS层表

USE ods;-- 如果表已存在，则删除它DROPTABLEIFEXISTS ads.ads_ad_show;-- 创建新的 ads.ads_ad_show 表CREATETABLE ads.ads_ad_show (
    cnt BIGINT,
    u_cnt BIGINT,
    device_cnt BIGINT,
    ad_action TINYINT,hour STRING
)
PARTITIONED BY(`dt` STRING)ROW FORMAT DELIMITED 
FIELDSTERMINATEDBY',';

执行结果如下图所示：

加载ADS数据

vim /opt/wzk/hive/ads_load_ad_show.sh

写入的内容如下所示：

#!/bin/bash# 加载系统环境变量
source /etc/profile

# 确定操作日期，默认为前一天if[-n "$1"];then
  do_date=$1else
  do_date=$(date-d "-1 day"+%F)
fi

# Hive SQL语句sql="
INSERT OVERWRITE TABLE ads.ads_ad_show
PARTITION (dt='$do_date')
SELECT 
  COUNT(1) AS cnt,
  COUNT(DISTINCT uid) AS u_cnt,
  COUNT(DISTINCT device_id) AS device_cnt,
  ad_action,
  hour
FROM dwd.dwd_ad
WHERE dt='$do_date'
GROUP BY ad_action, hour;
"# 执行 Hive SQL
hive -e "$sql"

写入结果如下图所示：

漏斗分析（点击率购买率）

基本介绍

漏斗分析（Funnel Analysis）是一种常用于大数据分析的技术，广泛应用于产品、用户行为分析、营销效果评估等领域。漏斗分析的核心概念是通过定义一系列的步骤或阶段，跟踪用户或客户在每个阶段的流失情况，从而帮助分析问题所在并优化转化率。

基本概念

漏斗分析的“漏斗”通常由多个步骤组成，代表着用户或客户在从开始到最终目标的过程中经过的一系列阶段。例如，在电商平台中，漏斗可能包括以下步骤：

• 访问网站
• 浏览商品
• 添加商品到购物车
• 完成支付

漏斗的形状类似于漏斗，通常在漏斗的顶部有大量用户或潜在客户，但随着每一步的推进，逐渐流失，因此漏斗的下部会比顶部更狭窄。

分析的目标

漏斗分析的主要目标是：

• 识别流失率高的环节：通过分析各个阶段的转化率，找到用户流失严重的环节，进而进行优化。
• 提高转化率：通过减少在漏斗中某个阶段的用户流失，提升整体转化率。
• 优化用户体验：通过分析漏斗阶段，改善产品设计、用户界面、营销策略等，提高用户的参与度和满意度。

分析的步骤

漏斗分析通常包括以下几个步骤：

• 定义漏斗阶段：根据分析目标，确定漏斗中的各个阶段。这些阶段应该是用户行为的关键环节。例如，电商网站的漏斗可能包括用户浏览商品、加入购物车、结账等环节。
• 数据收集与清洗：收集每个阶段的相关数据，并进行清洗和预处理，以确保数据的准确性和一致性。
• 分析用户流失情况：通过计算每个阶段的转化率，分析用户在不同阶段的流失情况。比如，从浏览商品到加入购物车的转化率是多少，是否有大量用户在这一步骤中流失。
• 优化和调整：根据分析结果，采取措施来优化漏斗的关键阶段。例如，发现用户在支付页面流失较多，可能需要优化支付流程、提供更多支付方式或减少页面加载时间。

应用场景

漏斗分析在多个领域中有广泛的应用：

• 电商平台：分析从用户访问到完成购买的全过程，找出转化率较低的环节，进行优化。
• SaaS产品：跟踪用户从注册到付费订阅的转化过程，评估试用期用户的留存情况。
• 移动应用：分析用户安装、首次启动、注册、使用等行为流程，提升用户留存和活跃度。
• 广告营销：分析广告点击到购买的转化过程，评估不同广告渠道的效果。

关键指标

漏斗分析通常关注以下几个关键指标：

• 转化率（Conversion Rate）：每个阶段的转化率是漏斗分析中的核心指标。它表示用户在某一阶段成功完成目标行为的比例。转化率 = （当前阶段的用户数）/（上一阶段的用户数）。
• 流失率（Drop-off Rate）：每个阶段的流失率表示用户在该阶段流失的比例，通常流失率 = 1 - 转化率。
• 漏斗效能（Funnel Efficiency）：整体漏斗的效能反映了用户在各阶段转化的情况，常通过漏斗的宽度和深度来判断。

需求分析

分时统计：

• 点击率 = 点击次数 / 曝光次数
• 购买率 = 购买次数 / 点击次数

创建ADS层表

use ads;droptableifexists ads.ads_ad_show_rate;createtable ads.ads_ad_show_rate(hour string,
  click_rate double,
  buy_rate double) PARTITIONED BY(`dt` string)row format delimited fieldsterminatedby',';

执行的结果如下图所示：

行转列：
方法一：

SELECTSUM(CASEWHEN ad_action='0'THEN cnt END)AS show_cnt,SUM(CASEWHEN ad_action='1'THEN cnt END)AS click_cnt,SUM(CASEWHEN ad_action='2'THEN cnt END)AS buy_cnt,hourFROM ads.ads_ad_show
WHERE dt='2020-08-02'ANDhour='01'GROUPBYhour;

方法二：

SELECTMAX(CASEWHEN ad_action='0'THEN cnt END)AS show_cnt,MAX(CASEWHEN ad_action='1'THEN cnt END)AS click_cnt,MAX(CASEWHEN ad_action='2'THEN cnt END)AS buy_cnt,hourFROM ads.ads_ad_show
WHERE dt='2020-08-02'ANDhour='01'GROUPBYhour;

加载ADS层数据

vim /opt/wzk/hive/ads_load_ad_show_rate.sh

编写的内容如下所示：

#!/bin/bash# 加载系统环境变量
source /etc/profile

# 确定操作日期，默认为前一天if[-n "$1"];then
  do_date=$1else
  do_date=$(date-d "-1 day"+%F)
fi

# Hive SQL语句sql="
WITH tmp AS (
  SELECT 
    MAX(CASE WHEN ad_action='0' THEN cnt END) AS show_cnt,
    MAX(CASE WHEN ad_action='1' THEN cnt END) AS click_cnt,
    MAX(CASE WHEN ad_action='2' THEN cnt END) AS buy_cnt,
    hour
  FROM ads.ads_ad_show
  WHERE dt='$do_date'
  GROUP BY hour
)
INSERT OVERWRITE TABLE ads.ads_ad_show_rate
PARTITION (dt='$do_date')
SELECT 
  hour,
  CASE WHEN show_cnt > 0 THEN click_cnt / show_cnt ELSE 0 END AS click_rate,
  CASE WHEN click_cnt > 0 THEN buy_cnt / click_cnt ELSE 0 END AS buy_rate
FROM tmp;
"# 执行 Hive SQL
hive -e "$sql"

写入的内容如下所示：

广告效果分析

需求分析

活动曝光效果评估：
行为（曝光、点击、购买）、时间段、广告位、商品，统计对应的次数
我们需要：
时间段、广告位、商品、曝光次数最多的前100个

创建ADS层表

use ads;droptableifexists ads.ads_ad_show_place;createtable ads.ads_ad_show_place(
  ad_action tinyint,hour string,
  place string,
  product_id int,
  cnt bigint)PARTITIONED BY(`dt` string)row format delimited fieldsterminatedby',';droptableifexists ads.ads_ad_show_place_window;createtable ads.ads_ad_show_place_window(hour string,
  place string,
  product_id int,
  cnt bigint,
  rank int)PARTITIONED BY(`dt` string)row format delimited fieldsterminatedby',';

执行结果如下图所示：

加载ADS层数据

vim /opt/wzk/hive/ads_load_ad_show_page.sh

写入的内容如下所示：

#!/bin/bash# 加载系统环境变量
source /etc/profile

# 确定操作日期，默认为前一天if[-n "$1"];then
  do_date=$1else
  do_date=$(date-d "-1 day"+%F)
fi

# Hive SQL语句sql="
INSERT OVERWRITE TABLE ads.ads_ad_show_place
PARTITION (dt='$do_date')
SELECT 
  ad_action,
  hour,
  place,
  product_id,
  COUNT(1) AS cnt
FROM dwd.dwd_ad
WHERE dt='$do_date'
GROUP BY 
  ad_action, 
  hour, 
  place, 
  product_id;
"# 执行 Hive SQL
hive -e "$sql"

执行结果如下图所示：

vim /opt/wzk/hive/ads_load_ad_show_page_window.sh

写入的内容如下所示：

#!/bin/bash# 加载系统环境变量
source /etc/profile

# 确定操作日期，默认为前一天if[-n "$1"];then
  do_date=$1else
  do_date=$(date-d "-1 day"+%F)
fi

# Hive SQL语句sql="
INSERT OVERWRITE TABLE ads.ads_ad_show_place_window
PARTITION (dt='$do_date')
SELECT *
FROM (
  SELECT 
    hour,
    place,
    product_id,
    cnt,
    ROW_NUMBER() OVER (PARTITION BY hour, place, product_id ORDER BY cnt DESC) AS rank
  FROM ads.ads_ad_show_place
  WHERE dt='$do_date' AND ad_action='0'
) t
WHERE rank <= 100;
"# 执行 Hive SQL
hive -e "$sql"

执行结果如下图所示：