当前位置： 首页 > article >正文

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

article 2024/12/5 3:41:07

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

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;

-- 如果表已存在，则删除它
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 ',';

执行结果如下图所示：
在这里插入图片描述

加载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"

写入结果如下图所示：
在这里插入图片描述

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

基本介绍

漏斗分析（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 ',';

执行的结果如下图所示：
在这里插入图片描述
行转列：
方法一：

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"

写入的内容如下所示：
在这里插入图片描述

广告效果分析

需求分析

活动曝光效果评估：
行为（曝光、点击、购买）、时间段、广告位、商品，统计对应的次数
我们需要：
时间段、广告位、商品、曝光次数最多的前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 ',';

执行结果如下图所示：
在这里插入图片描述

加载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"

执行结果如下图所示：
在这里插入图片描述

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"

执行结果如下图所示：
在这里插入图片描述


http://www.kler.cn/a/420566.html

相关文章：

  • Meta-Llama-3-8B-Instruct 模型的混合精度训练显存需求：AdamW优化器（中英双语）
  • 基于vite6+ vue3 + electron@33 实现的 局域网内互传文件的桌面软件
  • 基于Java Springboot宠物咖微信小程序
  • nlp培训重点
  • SpringBoot源码-Spring Boot启动时控制台为何会打印logo以及自定义banner.txt文件控制台打印
  • java基础教程3 java基础语法
  • 2024-11-29 学习人工智能的Day33 BP算法和神经网络小实验
  • Python字符串对齐的几种方法、Python填充与对齐、Python中英文对齐
  • 软件测试常问面试问题及项目流程相关概念
  • 深入实践：从零开始掌握GPT的应用开发
  • 错误信息 Uncaught (in promise) TypeError: extender is not a function
  • spark中将json数据转成dataset
  • Ubuntu 20.04 程序运行导致“段错误 (核心已转储)”的原因分析及解决方案 ubuntu
  • droppath
  • Qt的定时器应用案例 || Qt的图片添加显示
  • 2017 NHOI小学（C++）
  • MySQL 单表练习
  • C#中的集合初始化器
  • TongRDS分布式内存数据缓存中间件
  • 《数据结构》学习系列——图（下）
  • flink学习（14）—— 双流join
  • Redis开发05：使用stackexchange.redis库对redis进行增删改查
  • 前端【9种前端常见的设计模式】
  • 详解Qt Pdf之QPdfBookmarkModel 读取pdf标签页并显示
  • 创建 EC2块存储磁盘并将其连接到 Linux 实例
  • Vue3.5新版本特性一览-数组操作10倍性能提升+响应式属性解构+自定义组件优化+ssr水合改善+teleport支持defer!