助睿数智实操教程：浏览器大屏分析数据加工 前言在前面实验中我们已经将 800 多万条半结构化日志解析为 behavior_events 明细表并完成了初步的 browser_coverage 和 browser_hourly 统计。但要支撑一个完整的浏览器市场分析大屏还需要加工更多维度的目标表。本实验就是完成这些「弹药」的准备——基于 daily_browser_detail 用户-日-浏览器-小时明细表依次加工出周活跃趋势、使用频率分布、浏览器使用数量分布、工作日与周末对比、核心指标概览以及用户画像统计共六类目标表。一、实验说明1.1 实验目的基于「用户-日-浏览器-小时」明细表完成数据大屏所需的各项统计表加工包括浏览器市场格局统计、周活跃趋势、使用频率分布、使用数量分布、工作日周末对比、用户画像统计1.2 实验环境实验平台助睿在线实验平台 HTTPS://lab.guilian.cn/数据处理助睿 ETL 数据集成平台数据规模1000 用户800 万条行为记录约 825MB二、实验数据本实验基于上个实验产出的数据以及原始 demographic 用户属性表。上个实验已输出的数据包括 daily_browser_detail用户-日-浏览器-小时明细表将在本实验 6.1 节中输出、browser_coverage浏览器市场覆盖率统计表、browser_hourly浏览器时段活跃统计表。三、整体分析框架3.1 业务问题与分析维度在制作数据大屏之前需要明确大屏要展示什么。大屏需要的是聚合后的统计结果而非原始明细多个图表可能共用同一份聚合数据。本实验围绕以下十二个业务问题展开业务问题为什么重要哪个浏览器用户最多了解市场领导者判断自身产品市场地位哪个浏览器用户用得最久用户数多不代表粘性高使用时长反映真实依赖度用户活跃度在增长还是下降判断产品生命周期及时发现衰退信号用户什么时候最活跃优化推送和运营时机用户是重度使用还是偶尔打开区分核心用户和边缘用户制定差异化策略用户同时用几个浏览器了解用户忠诚度判断竞品替代风险用户还用什么其他浏览器识别主要竞争对手工作日和周末使用习惯有何不同区分工作场景和娱乐场景核心用户是谁性别、年龄、职业明确目标用户群体用户的教育水平如何影响产品复杂度设计用户的收入水平如何影响商业化策略用户分布在哪里城市、省份指导区域市场拓展3.2 需要加工的目标表大屏一目标表市场行为分析目标表对应维度数据来源 browser_overview核心指标 daily_browser_detailbrowser_coverage市场格局 daily_browser_detail已输出browser_weekly_active周活跃趋势 daily_browser_detailbrowser_hourly时段偏好 daily_browser_detail已输出browser_frequency_stats使用频率 daily_browser_detailbrowser_multi_usage浏览器使用数量 daily_browser_detailbrowser_cooccurrence竞品重叠 daily_browser_detailbrowser_weekday_weekend工作日 vs 周末目标表对应维度数据来源browser_overview核心指标daily_browser_detailbrowser_coverage市场格局daily_browser_detail已输出browser_weekly_active周活跃趋势daily_browser_detailbrowser_hourly时段偏好daily_browser_detail已输出browser_frequency_stats使用频率daily_browser_detailbrowser_multi_usage浏览器使用数量daily_browser_detailbrowser_cooccurrence竞品重叠daily_browser_detailbrowser_weekday_weekend工作日vs周末daily_browser_detaildaily_browser_detail大屏二目标表用户画像分析user_profile_stats按性别、年龄、学历、职业、收入、省份、居住地类型分组统计数据来源为 demographic 和 daily_browser_detail。四、实验步骤4.1 准备用户-日-浏览器-小时明细表上个实验的「互联网用户行为日志数据清洗抽取」转换流已经包含了生成明细数据的完整逻辑但只输出了 browser_coverage 和 browser_hourly 两个分支。需要将其复制一份改为输出明细表。4.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 CHARSETutf8mb4COMMENT『用户_日_浏览器_小时明细表』;点击「运行」按钮执行转换流。4.1.2 复制转换流在上个实验的项目中找到「互联网用户行为日志数据清洗抽取」转换流右键选择「复制」。右键根目录点击「粘贴」。粘贴后右键重命名为「输出用户日浏览器小时明细表」。这里需要注意上个实验中「排序记录 1」组件仅按照 process_name 升序排序而分组组件的分组字段是 user_id、usage_date、process_name、hour所以需要更正「排序记录 1」组件的排序字段与分组组件的分组字段一致否则会出现多条重复数据。双击「排序记录 1」组件将排序字段改为 user_id、usage_date、process_name、hour 四个字段全部升序。4.1.3 浏览器名称映射在分组组件后拖入「值映射」组件值映射组件的输出端连接到原分支 A 的分组 1 组件复制一份也发送到原分支 B 的排序记录 2 组件。双击值映射组件按照以下映射表将进程名映射为浏览器中文名进程名映射为中文字段值 iexplore.exeIE 浏览器360chrome.exe360 极速360se.exe360sechrome.exeGooglesogouexplorer.exe搜狗QQBrowser.exeQQ进程名映射为中文字段值iexplore.exeIE浏览器360chrome.exe360极速360se.exe360sechrome.exeGooglesogouexplorer.exe搜狗QQBrowser.exeQQ浏览器浏览器注意需要检查上个实验「4.5.3 过滤记录」中的匹配条件如果匹配值包含了 EXCEL.EXE、WINWORD.EXE、AlilM.exe需要删除因为这三个不是浏览器。另外转换流中分组组件的聚合类型如果是「个数」需要改成「统计不同值的数量(N)」并在分支 A 的「分组 1」组件前添加排序记录组件按 process_name 升序排序。4.1.4 添加表输出并执行拖拽「表输出」组件到画布中值映射组件连接到表输出组件。双击配置数据库连接选「团队私有数据库」目标表选 daily_browser_detail勾选「裁剪表」清空原有数据勾选「指定数据库字段」建立字段映射。完成后点击「运行」按钮执行转换流。4.2 创建目标数据表新建转换流「创建浏览器大屏分析目标数据表」拖入「执行一个 SQL 脚本」组件。数据库连接选「团队私有数据库」输入以下 SQL使用 DROP TABLE 避免重复建表时报错一次性创建 6 张目标表-- 1.核心指标概览表 DROP TABLE IF EXISTS browser_overview;CREATE TABLE browser_overview (metric_name VARCHAR(50) NOT NULL,metric_value DECIMAL(12,2) NOT NULL) COMMENT『核心指标概览表』;-- 2.各浏览器周活跃趋势表 DROP TABLE IF EXISTS browser_weekly_active;CREATE TABLE browser_weekly_active (browser_name VARCHAR(50) NOT NULL,week_range VARCHAR(20) NOT NULL,active_user_count INT NOT NULL) COMMENT『各浏览器周活跃趋势表』;-- 3.浏览器使用频率分布表 DROP TABLE IF EXISTS browser_frequency_stats;CREATE TABLE browser_frequency_stats (browser_name VARCHAR(50) NOT NULL,usage_level VARCHAR(10) NOT NULL,user_count INT NOT NULL) COMMENT『浏览器使用频率分布表』;-- 4.用户使用浏览器数量分布表 DROP TABLE IF EXISTS browser_multi_usage;CREATE TABLE browser_multi_usage (browser_count VARCHAR(10) NOT NULL,user_count DECIMAL(5,2) NOT NULL) COMMENT『用户使用浏览器数量分布表』;-- 5.浏览器工作日周末对比表 DROP TABLE IF EXISTS browser_weekday_weekend;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) COMMENT『浏览器工作日周末对比表』;-- 6.用户画像统计表 DROP TABLE IF EXISTS user_profile_stats;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) COMMENT『用户画像统计表』;点击「运行」按钮执行转换流。4.3 各浏览器周活跃趋势表数据抽取目标统计每个浏览器在第 1-4 周的每周活跃用户数。新建转换流「各浏览器周活跃趋势表数据抽取」。拖入「表输入」组件数据库连接选「团队私有数据库」获取 daily_browser_detail 的 SQL 查询语句。拖入「字段选择」组件创建连线。双击组件点击「元数据」标签右键插入一行字段名输入 usage_date类型选 Date格式为「yyyy-MM-dd」。这一步将 usage_date 的字符串格式转为日期格式便于后续值映射。拖入「值映射」组件创建连线。双击组件使用的字段名选「usage_date」目标字段名空覆盖输入「week_range」表示创建新字段 week_range 存储映射结果。插入行将每个日期映射为对应的周区间5/7-5/13 为第 1 周、6/4-6/10 为第 2 周、7/2-7/8 为第 3 周、8/6-8/12 为第 4 周。拖入「排序记录」组件创建连线按 browser_name、week_range 升序排序。拖入「分组」组件创建连线分组字段为 browser_name 和 week_range聚合时对 user_id 进行去重计数得到 active_user_count聚合类型选「统计不同值的数量(N)」。拖入「表输出」组件创建连线。数据库连接选「团队私有数据库」目标表选 browser_weekly_active勾选「裁剪表」和「指定数据库字段」映射字段后执行转换流。4.4 各浏览器使用频率分布表数据抽取目标按轻/中/重度划分用户使用频率。新建转换流「使用频率分布数据抽取」。拖入「表输入」组件数据库连接选「团队私有数据库」获取 daily_browser_detail 的 SQL 查询。拖入「排序记录」组件创建连线按 user_id、browser_name 升序排序。拖入「分组」组件创建连线分组字段为 user_id 和 browser_name聚合总使用时长 total_seconds SUM(total_duration_sec)。拖入「增加常量」组件创建连线。新增字段 hour_m_s类型设置为 Integer值固定为 3600一小时3600 秒。拖入「计算器」组件创建连线。新增字段 total_hours计算公式为 A/B字段 A 为 total_seconds字段 B 为 hour_m_s保留 2 位小数。这样就将秒转换成了小时。拖入「JavaScript 代码」组件创建连线。输入以下 JS 代码划分使用频率等级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 『重度』;}usage_level 字段需要在之前的「增加常量」组件中提前新增类型 String值留空。点击 JS 组件的「获取变量」按钮自动获取变量。拖入「排序记录」组件创建连线按 browser_name、usage_level 升序排序。拖入「分组」组件按 browser_name 和 usage_level 分组聚合 user_count 为 user_id 去重计数。拖入「表输出」组件目标表选 browser_frequency_stats裁剪表并映射字段执行转换流。4.5 各浏览器使用数量分布表数据抽取目标统计用户使用 1 种、2 种、3 种及以上浏览器的用户数。新建转换流「浏览器使用数量分布数据抽取」。拖入「表输入」组件获取 daily_browser_detail 数据。拖入「排序记录」组件按 user_id 升序排序。拖入「分组」组件分组字段为 user_id聚合browser_cnt 浏览器名称去重计数统计不同值的数量(N)。拖入「JavaScript 代码」组件创建连线。输入以下 JS 代码划分浏览器数量等级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 升序排序拖入「分组」按 browser_count 分组统计 user_countuser_id 去重计数拖入「表输出」到 browser_multi_usage 表裁剪表并映射字段后执行。4.6 各浏览器工作日周末对比表数据抽取目标统计各浏览器工作日与周末的使用时长对比。新建转换流「浏览器工作日周末对比数据抽取」。拖入「表输入」组件获取 daily_browser_detail 数据。拖入「JavaScript 代码」组件创建连线输入以下 JS 代码判断日期类型var date usage_date;var dayOfWeek date.getDay();var day_type 「」;if (dayOfWeek 1 dayOfWeek 5) {day_type 「工作日」;} else {day_type 「周末」;}点击「获取变量」。拖入「排序记录」组件按 browser_name、day_type 升序排序。拖入「分组」组件按 browser_name 和 day_type 分组聚合avg_seconds平均使用时长、total_seconds总使用时长、user_countuser_id 去重计数。总使用时长单位是秒数值很大不够直观。参考 4.4 节的方法使用「增加常量」组件常量 3600和「计算器」组件total_seconds/3600将总使用时长转为小时。然后使用「字段选择」组件删除计算过程中产生的中间字段。拖入「表输出」组件目标表选 browser_weekday_weekend裁剪表并映射字段后执行转换流。4.7 核心指标数据抽取目标将大屏顶部四个指标卡的数据存入一张通用的键值对表中。新建转换流拖入「表输入」组件数据库连接选「团队私有数据库」在 SQL 语句框中输入以下 SQL 一次性算出四个指标SELECTROUND(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_detailWHERE 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_detailWHERE usage_date BETWEEN 『2012-05-07』 AND 『2012-07-08』GROUP BY user_id HAVING SUM(total_duration_sec)/360030) t)*100.0/COUNT(DISTINCT user_id),2) AS heavy_ratioFROM daily_browser_detail接下来使用「行转列」组件将字段名称转为指标名称、字段值转为指标值total_hours→metric_name 为 total_hours、metric_value 为对应值avg_hours、active_ratio、heavy_ratio 同理。然后使用「值映射」组件将指标名称映射为中文total_hours→总使用时长、avg_hours→人均使用时长、active_ratio→活跃用户占比、heavy_ratio→重度用户占比。最后拖入「表输出」组件目标表选 browser_overview裁剪表并映射字段后执行。4.8用户画像表加工目标统计每个浏览器按性别、年龄、学历、职业、收入、居住地类型、省份的用户分布。4.8.1 获取人口属性数据点击「公共空间」→「数据资源」找到 demographic.csv 数据卡片。点击右上角「更多」→「导出」选择导出到根目录点击「确定」。刷新文件库即可看到 demographic.csv。4.8.2 CSV 文件输入新建转换流「用户画像表加工」。拖入「CSV 文件输入」组件双击打开配置点击「浏览文件」选择 demographic.csv点击「确定」。列分隔符和封闭符保持不变编码选「UTF-8」。在空白表格处右键「获取字段」获取成功后点击「确认」。4.8.3 年龄分段原人口属性数据中有出生年份BIRTHDAY 字段但没有年龄字段。拖入「增加常量」组件新增字段 year值设为 2012数据采集年份。拖入「计算器」组件计算 age year - BIRTHDAY。拖入「JavaScript 代码」组件将年龄划分为四段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』;}4.8.4 读取明细数据并关联拖入「表输入」组件数据库连接选「团队私有数据库」获取 daily_browser_detail 的 SQL 查询。现在有两个数据流CSV 输入的属性数据流和表输入的行为数据流。使用「记录集连接」组件将两个数据源通过 user_id 关联。注意记录集连接之前必须对两个数据源分别排序。先拖入两个「排序记录」组件排序记录 1 从表输入连线按 user_id 升序命名「明细数据按用户 ID 排序」排序记录 2 从 CSV 输入连线连线类型选「主输出步骤」按 user_id 升序。然后拖入「记录集连接」组件第一步选排序记录 1第二步选排序记录 2连接类型选 INNER JOIN连接字段都设为 user_id。4.8.5 分组聚合与表输出拖入「排序记录」组件按 browser_name、GENDER、EDU、JOB、INCOME、PROVINCE、ISCITY、age_group 升序排序。拖入「分组」组件按以上八个维度分组聚合 user_count 为 user_id 去重计数统计不同值的数量(N)。拖入「表输出」组件目标表选 user_profile_stats勾选「裁剪表」和「指定数据库字段」映射字段后执行转换流。4.8.6 验证结果点击「元数据」标签右键「团队私有数据库」选择「加载元数据」。进入「数据探查」页面依次双击 browser_coverage、browser_hourly、browser_weekly_active、browser_frequency_stats、browser_multi_usage、browser_weekday_weekend、browser_overview、user_profile_stats 等目标表切换到「查询」标签页确认数据条数和数值范围符合预期。五、实验总结本次实验以 daily_browser_detail 用户-日-浏览器-小时明细表为核心数据源通过助睿 ETL 平台依次完成了浏览器周活跃趋势、使用频率分布、浏览器使用数量分布、工作日与周末使用对比、核心指标概览以及用户画像统计共六类目标表的加工全流程。整个实验过程涵盖了表输入、字段选择、过滤记录、排序分组、值映射、JavaScript 代码、增加常量、计算器、记录集连接、行转列、CSV 文件输入等十余种 ETL 组件的组合使用同时涉及 SQL 聚合查询、年龄计算与分段、使用频率分级、星期判断等数据处理技巧。关键操作经验总结如下第一分组聚合前务必确认排序字段与分组字段一致否则会产生重复数据第二记录集连接之前必须对两个数据源分别按关联字段排序这是 JOIN 操作正确执行的前提第三聚合类型中「统计不同值的数量(N)」等价于 SQL 的 COUNT(DISTINCT)应优先使用而非简单「个数」第四浏览器名称映射使用值映射组件可在源头统一命名规范避免后续分析时名称不一致第五涉及大量中间字段时及时用字段选择组件清理冗余保持数据流清晰可控。至此数据大屏所需的全部统计表加工完毕下一实验将基于这些目标表在助睿 BI 中完成可视化大屏的搭建与发布。