【ETL 全流程实战】浏览器用户行为数据分析（附配置代码！！！）

目录第一部分浏览器市场与用户画像分析 - 数据加工1. 实验目的2. 实验环境3. 整体分析框架4. 实验步骤4.1 准备基础明细表 daily_browser_detail4.2 创建所有目标数据表4.3 各浏览器周活跃趋势表4.4 各浏览器使用频率分布表4.5 各浏览器使用数量分布表4.6 各浏览器工作日周末对比表4.7 核心指标数据抽取4.8 用户画像表加工4.9 验证结果第二部分浏览器市场分析——大屏静态布局制作第三部分浏览器市场分析——大屏数据接入第四部分实验总结第一部分浏览器市场与用户画像分析 - 数据加工21. 实验目的基于“用户-日-浏览器-小时”明细表完成数据大屏所需的各项统计表加工包括浏览器市场格局、周活跃趋势、使用频率分布、使用数量分布、工作日vs周末对比、用户画像统计。2. 实验环境平台助睿在线实验平台https://lab.guilian.cn/工具助睿数智 ETL 数据集成平台数据规模1000用户800万行为记录3. 整体分析框架业务问题对应目标表哪个浏览器用户最多用得最久browser_coverage已产出用户活跃度在增长还是下降browser_weekly_active用户是重度还是轻度使用browser_frequency_stats用户同时用几个浏览器browser_multi_usage工作日和周末使用习惯有何不同browser_weekday_weekend核心指标卡片browser_overview用户画像性别、年龄、学历等user_profile_stats4. 实验步骤4.1 准备基础明细表daily_browser_detail4.1.1 创建目标数据表新建转换流“创建用户_日_浏览器_小时明细表”拖入“执行一个SQL脚本”组件数据库连接选择“团队私有数据库”执行以下SQLCREATE TABLE IF NOT EXISTS daily_browser_detail ( user_id VARCHAR(50) NOT NULL COMMENT 用户ID, usage_date DATE NOT NULL COMMENT 使用日期, browser_name VARCHAR(50) NOT NULL COMMENT 浏览器名称, hour TINYINT NOT NULL COMMENT 小时, total_duration_sec INT NOT NULL COMMENT 总使用时长(秒), active_count INT NOT NULL COMMENT 活跃次数 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;点击“运行”按钮执行4.1.2 复制并修改转换流找到上个实验的“互联网用户行为日志数据清洗抽取”转换流 → 右键“复制” → 粘贴 → 重命名为“输出用户日浏览器小时明细表”关键修正双击“排序记录 1”组件将排序字段修改为user_id、usage_date、process_name、hour与分组组件保持一致4.1.3 添加浏览器名称映射在分组组件后添加“值映射”组件配置映射关系进程名映射为iexplore.exeIE浏览器chrome.exeGoogle360chrome.exe360极速360se.exe360sesogouexplorer.exe搜狗QQBrowser.exeQQ浏览器4.1.4 添加表输出拖入“表输出”组件配置数据库连接团队私有数据库目标表daily_browser_detail勾选“裁剪表”勾选“指定数据库字段”建立字段映射点击“运行”执行转换流4.2 创建所有目标数据表新建转换流“创建浏览器大屏分析目标数据表”拖入“执行一个SQL脚本”组件执行以下SQL创建6张表-- 1. 核心指标概览表 CREATE TABLE browser_overview ( metric_name VARCHAR(50) NOT NULL, metric_value DECIMAL(12,2) NOT NULL ); -- 2. 各浏览器周活跃趋势表 CREATE TABLE browser_weekly_active ( browser_name VARCHAR(50) NOT NULL, week_range VARCHAR(20) NOT NULL, active_user_count INT NOT NULL ); -- 3. 浏览器使用频率分布表 CREATE TABLE browser_frequency_stats ( browser_name VARCHAR(50) NOT NULL, usage_level VARCHAR(10) NOT NULL, user_count INT NOT NULL ); -- 4. 用户使用浏览器数量分布表 CREATE TABLE browser_multi_usage ( browser_count VARCHAR(10) NOT NULL, user_count DECIMAL(5,2) NOT NULL ); -- 5. 浏览器工作日周末对比表 CREATE TABLE browser_weekday_weekend ( browser_name VARCHAR(50) NOT NULL, day_type VARCHAR(10) NOT NULL, avg_duration_sec INT NOT NULL, total_duration_hour BIGINT NOT NULL, user_count INT NOT NULL ); -- 6. 用户画像统计表 CREATE TABLE user_profile_stats ( browser_name VARCHAR(50) NOT NULL, gender VARCHAR(10), age_group VARCHAR(10), edu VARCHAR(50), job VARCHAR(50), income VARCHAR(50), city_type VARCHAR(10), province VARCHAR(50), user_count INT NOT NULL );点击“运行”执行4.3 各浏览器周活跃趋势表配置项内容数据源daily_browser_detail分组字段browser_name、week_range聚合user_id去重计数 →active_user_count目标表browser_weekly_active步骤新建转换流“各浏览器周活跃趋势表数据抽取”拖入“表输入” → 选择daily_browser_detail拖入“字段选择” → 在“元数据”页将usage_date类型设为 Date拖入“值映射” → 将日期映射为周区间如 2012-05-07 → 5/7-5/13【总的需要转换为5/7-5/13、6/4-6/10、7/2-7/8、8/6-8/12不匹配的数据建议设为“其他”避免空值报错】拖入“排序记录” → 按browser_name、week_range升序拖入“分组” → 配置分组字段和聚合拖入“表输出” → 写入目标表勾选“裁剪表”4.4 各浏览器使用频率分布表配置项内容数据源daily_browser_detail中间计算每用户每浏览器总时长 → 转小时 → 分等级3h/3-10h/10h分组字段browser_name、usage_level聚合user_id去重计数 →user_count目标表browser_frequency_stats具体组件配置JavaScript代码var total_hours total_hours; var usage_level ; if (total_hours 3) { usage_level 轻度; } else if (total_hours 3 total_hours 10) { usage_level 中度; } else { usage_level 重度; }4.5 各浏览器使用数量分布表配置项内容数据源daily_browser_detail中间计算每用户使用浏览器种类数 → 分等级1种/2种/3种及以上分组字段browser_count聚合user_id去重计数目标表browser_multi_usageJavaScript代码var browser_cnt browser_cnt; var browser_count ; if (browser_cnt 1) { browser_count 1种; } else if (browser_cnt 2) { browser_count 2种; } else { browser_count 3种及以上; }按browser_count升序排列4.6 各浏览器工作日周末对比表配置项内容数据源daily_browser_detail中间计算根据日期判断工作日/周末分组字段browser_name、day_type聚合平均时长、总时长秒转小时、用户数目标表browser_weekday_weekendJavaScript代码判断工作日/周末var date usage_date; var dayOfWeek date.getDay(); var day_type ; if (dayOfWeek 1 dayOfWeek 5) { day_type 工作日; } else { day_type 周末; }4.7 核心指标数据抽取配置项内容数据源daily_browser_detail一次性SQL计算处理方式列转行 → 指标名映射为中文目标表browser_overview核心SQLSELECT ROUND(SUM(total_duration_sec) / 3600, 2) AS total_hours, ROUND(SUM(total_duration_sec) / 3600 / COUNT(DISTINCT user_id), 2) AS avg_hours, ROUND( (SELECT COUNT(DISTINCT user_id) FROM daily_browser_detail WHERE usage_date BETWEEN 2012-08-06 AND 2012-08-12 ) * 100.0 / COUNT(DISTINCT user_id), 2 ) AS active_ratio, ROUND( (SELECT COUNT(*) FROM ( SELECT user_id FROM daily_browser_detail WHERE usage_date BETWEEN 2012-05-07 AND 2012-07-08 GROUP BY user_id HAVING SUM(total_duration_sec) / 3600 30 ) t) * 100.0 / COUNT(DISTINCT user_id), 2 ) AS heavy_ratio FROM daily_browser_detailtotal_hourstotal_hoursmetric_valueavg_hoursavg_hoursmetric_valueactive_ratioactive_ratiometric_valueheavy_ratioheavy_ratiometric_value4.8 用户画像表加工4.8.1 导入人口属性数据进入“公共空间” → “数据资源” → 找到demographic.csv点击“更多” → “导出” → 选择根目录 → “确定”4.8.2 构建转换流新建转换流“用户画像表加工”拖入“CSV文件输入” → 读取demographic.csv→ 点击“获取字段”分隔符为英文逗号拖入“增加常量” → 新增字段year 2012拖入“计算器” → 计算年龄age year - BIRTHDAY拖入“JavaScript代码” → 划分年龄段拖入“表输入” → 读取daily_browser_detail分别对两个数据源按user_id排序拖入“记录集连接” → 连接类型选择“LEFT OUTER”连接字段均为用户ID拖入“排序记录” → 按所有分组字段排序拖入“分组” → 分组字段browser_name、GENDER、EDU、JOB、INCOME、PROVINCE、ISCITY、age_group聚合user_id去重计数拖入“表输出” → 写入user_profile_stats勾选“裁剪表”var age_group ; if (age 18) age_group 18; else if (age 25) age_group 18-25; else if (age 35) age_group 26-35; else age_group 35;排序记录组件共8个排序条件1browser_name是否否0否2GENDER是否否0否3EDU是否否0否4JOB是否否0否5INCOME是否否0否6PROVINCE是否否0否7ISCITY是否否0否8age_group是否否0否分组字段同排序字段共8个4.9 验证结果右键点击“团队私有数据库” → “加载元数据”点击“数据探查”查看各目标表数据是否符合预期输出表汇总目标表内容browser_weekly_active各浏览器每周活跃用户数browser_frequency_stats各浏览器轻/中/重度用户分布browser_multi_usage用户使用1/2/3种浏览器的数量分布browser_weekday_weekend各浏览器工作日vs周末使用时长对比browser_overview总时长、人均时长、周活跃占比、重度用户占比user_profile_stats各浏览器按性别/年龄/学历/职业/收入/地域的用户分布第二部分浏览器市场分析——大屏静态布局制作可视化工具助睿Max数据大屏实验数据模块子模块指标项组件关联数据表备注数据概览浏览器市场总使用时长指标卡browser_coverage所有用户累计使用时长小时人均使用时长指标卡browser_coverage总使用时长 / 覆盖用户数小时/周活跃用户占比指标卡browser_coverage周活跃用户数 / 覆盖用户数重度用户占比指标卡browser_frequency_stats使用时长10小时/周的用户占比市场格局用户规模用户数柱状图browser_coverage展示6个浏览器用户数使用规模使用时长饼图browser_coverage展示各浏览器使用时长占比使用粘性人均使用时长柱状图browser_coverage用户行为时间趋势周活跃趋势折线图browser_weekly_active展示第1-4周各浏览器活跃用户数变化使用习惯使用频率分布堆叠柱状图browser_frequency_stats轻/中/重度用户在各浏览器的占比时段偏好全天时段24小时活跃分布折线图browser_hourlyX轴小时Y轴活跃用户数不同颜色代表不同浏览器周内对比工作日vs周末分组柱状图daily_browser_detail对比工作日和周末的使用时长竞争关系使用数量浏览器使用数量分布饼图browser_multi_usage用户使用1种/2种/3种及以上浏览器的比例进入大屏管理中心首先添加我们所需的数据源。点击“新建大屏”进入大屏设计界面使用组件完成静态大屏设计。第三部分浏览器市场分析——大屏数据接入目的使用助睿Max的蓝图编辑器将之前实验加工好的数据表接入到大屏的各个图表组件中使图表能够动态展示真实数据。组件导入到蓝图编辑器后可以为该组件配置交互。切换至蓝图编辑器依次配置好所有节点保存后发布效果应如图第四部分实验总结问题分析本次实验中主要遇到了周活跃趋势表week_range字段空值入库报错问题主要原因是按照设置的4个范围映射字段后其余没有包含的字段将输出为NULL值不符合建表时的非空约束导致工作流无法正常运行。解决方式我们根据日志定位到了输出为NULL的组件在“不匹配时的默认值”设为“其他”既不影响我们的映射也不会造成空值。本次实验在之前的数据集成相关操作外还拓展了数据大屏的设计和数据接入方面的实践很新奇也不太熟练但在一步步配置中慢慢有理解到“为什么这样设置”、“如何将业务问题转换为设计的思维”等等助睿平台在学习中给我们提供了很好的学习实践平台和资源。