武子康

武子康

关注
发信
关注(28)粉丝(399)

大数据-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
  • 广告业务 需求分析

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

点击次数

需求分析

广告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;

-- 如果表已存在，则删除它
DROP TABLE IF EXISTS ads.ads_ad_show;

-- 创建新的 ads.ads_ad_show 表
CREATE TABLE ads.ads_ad_show (
    cnt BIGINT,
    u_cnt BIGINT,
    device_cnt BIGINT,
    ad_action TINYINT,
    hour STRING
)
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',';

执行结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

加载ADS数据

vim /opt/wzk/hive/ads_load_ad_show.sh

写入的内容如下所示：

#!/bin/bash

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

# 确定操作日期，默认为前一天
if [ -n "$1" ]; then
  do_date=$1
else
  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"

写入结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

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

基本介绍

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

基本概念

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

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

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

分析的目标

漏斗分析的主要目标是：

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

分析的步骤

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

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

应用场景

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

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

关键指标

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

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

需求分析

分时统计：

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

创建ADS层表

use ads;
drop table if exists ads.ads_ad_show_rate;
create table ads.ads_ad_show_rate(
  hour string,
  click_rate double,
  buy_rate double
) PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

执行的结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP
行转列：
方法一：

SELECT 
    SUM(CASE WHEN ad_action='0' THEN cnt END) AS show_cnt,
    SUM(CASE WHEN ad_action='1' THEN cnt END) AS click_cnt,
    SUM(CASE WHEN ad_action='2' THEN cnt END) AS buy_cnt,
    hour
FROM ads.ads_ad_show
WHERE dt='2020-08-02' AND hour='01'
GROUP BY hour;

方法二：

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='2020-08-02' AND hour='01'
GROUP BY hour;

加载ADS层数据

vim /opt/wzk/hive/ads_load_ad_show_rate.sh

编写的内容如下所示：

#!/bin/bash

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

# 确定操作日期，默认为前一天
if [ -n "$1" ]; then
  do_date=$1
else
  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"

写入的内容如下所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

广告效果分析

需求分析

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

创建ADS层表

use ads;
drop table if exists ads.ads_ad_show_place;
create table ads.ads_ad_show_place(
  ad_action tinyint,
  hour string,
  place string,
  product_id int,
  cnt bigint
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

drop table if exists ads.ads_ad_show_place_window;
create table ads.ads_ad_show_place_window(
  hour string,
  place string,
  product_id int,
  cnt bigint,
  rank int
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

执行结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

加载ADS层数据

vim /opt/wzk/hive/ads_load_ad_show_page.sh

写入的内容如下所示：

#!/bin/bash

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

# 确定操作日期，默认为前一天
if [ -n "$1" ]; then
  do_date=$1
else
  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"

执行结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

vim /opt/wzk/hive/ads_load_ad_show_page_window.sh

写入的内容如下所示：

#!/bin/bash

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

# 确定操作日期，默认为前一天
if [ -n "$1" ]; then
  do_date=$1
else
  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"

执行结果如下图所示：
大数据-238 离线数仓 - 广告业务 点击次数 ADS层、广告效果分析 ADS 层 需求分析与加载-LMLPHP

12-05 07:12
Powered by LMLPHP ©2024 武子康 0.007704