自媒体运营分析：用ETL完成作品特征构建

#自媒体运营 #数据可视化 #数据清洗 #数据处理 #特征构建 #数据分析上一篇我们完成了自媒体作品数据的清洗与预处理本篇继续往下做基于清洗后的content_analysis表计算作品互动总数、提取标题关键词特征并输出关键词级别的互动分析表。一、为什么要做作品特征构建在实验7-1中我们已经把原始的自媒体作品数据清洗成了可分析的明细表content_analysis。这张表中已经包含了作品标题、平台、点赞数、收藏数、分享数、投币数、浏览量等基础字段。但如果只是停留在这些原始字段上后续分析仍然不够直观。比如我们想回答下面这些问题哪些作品综合互动表现更好标题中包含“保姆级”的作品互动效果是不是更高“零代码”“实战”“教程/指南”“踩坑”这些关键词对作品传播有没有影响某个关键词作品的平均互动数是否高于整体平均水平这些问题不能直接从原始字段中得到答案需要先进行特征工程。本次实验的核心就是把基础数据加工成更适合分析和可视化的特征字段。二、本次实验要完成什么本次实验主要完成两部分内容。第一部分更新content_analysis表。给每一条作品记录计算互动总数并提取标题关键词标志字段。第二部分新建title_feature_analysis表。统计每个关键词对应作品的平均互动表现用于后续可视化分析。任务输入数据输出结果用途更新作品级特征content_analysis更新后的content_analysis用于作品排名、趋势分析、明细分析输出关键词级汇总更新后的content_analysistitle_feature_analysis用于标题关键词效果分析三、实验环境与核心组件本实验继续使用ETL完成。数据处理工具ETL输入表content_analysis输出表更新后的content_analysis新建的title_feature_analysis本次实验会用到以下组件组件作用表输入读取content_analysis表字段选择保留后续计算需要的字段JavaScript代码判断标题中是否包含指定关键词计算器计算互动总数插入/更新按id回填字段到原表执行一个SQL脚本创建关键词汇总目标表过滤记录筛选包含某个关键词的作品排序记录为聚合和连接做准备分组计算平均互动数和样本数量增加常量给结果添加关键词名称记录集连接合并整体平均值和关键词平均值表输出写入title_feature_analysis表四、核心思路从“字段”到“特征”4.1 互动总数作品的互动行为不只有点赞还包括收藏、分享、投币等。为了更直观地衡量一篇作品的综合互动表现可以计算互动总数total_interaction likes favorites shares coins其中likes表示点赞数favorites表示收藏数shares表示分享数coins表示投币数主要用于 B站。计算完成后total_interaction就可以作为后续作品排名、标题效果分析和图表展示的重要指标。4.2 标题关键词特征标题是自媒体内容传播中非常重要的因素。 本实验选择五类关键词作为标题特征字段名判断规则has_best标题是否包含“保姆级”has_lowcode标题是否包含“零代码”has_practice标题是否包含“实战”has_tutorial标题是否包含“教程”或“指南”has_pit标题是否包含“踩坑”每个字段都是 0/1 标志包含关键词记为 1不包含关键词记为 0。这样就把原本不方便计算的标题文本转换成了可以统计和可视化的数值字段。五、实验前置检查开始之前先确认实验7-1已经完成并且数据库中存在content_analysis表。图2 检查实验7-1输出的content_analysis表是否已经生成5.1 检查是否有 id 字段本实验后面要使用“插入/更新”组件更新原表。 这个组件需要一个匹配字段用来判断当前数据流中的记录应该更新到数据库表中的哪一行。一般使用id字段作为匹配依据。如果content_analysis中已经有id字段可以直接继续操作。 如果没有可以执行下面的 SQL 添加ALTER TABLE content_analysis ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;注意如果表中已经存在主键不要重复添加否则可能报错。图3 检查content_analysis表中是否包含id、total_interaction和标题特征字段5.2 检查是否有待更新字段content_analysis中需要包含以下字段total_interaction has_best has_lowcode has_practice has_tutorial has_pit如果实验7-1建表时已经预留了这些字段就不用重复添加。 如果没有可以执行ALTER TABLE content_analysis ADDCOLUMN total_interaction INTDEFAULTNULLCOMMENT互动总数, ADDCOLUMN has_best TINYINT(1) DEFAULTNULLCOMMENT是否含保姆级, ADDCOLUMN has_lowcode TINYINT(1) DEFAULTNULLCOMMENT是否含零代码, ADDCOLUMN has_practice TINYINT(1) DEFAULTNULLCOMMENT是否含实战, ADDCOLUMN has_tutorial TINYINT(1) DEFAULTNULLCOMMENT是否含教程或指南, ADDCOLUMN has_pit TINYINT(1) DEFAULTNULLCOMMENT是否含踩坑;六、第一部分更新 content_analysis 表这一部分的目标是更新作品级特征。最终转换流结构如下表输入 → 字段选择 → JavaScript代码 → 计算器 → 插入/更新图4 作品级特征构建转换流读取明细表、提取标题特征、计算互动总数并回填6.1 新建“标题特征构建”转换进入助睿ETL后新建一个转换。建议命名为标题特征构建这个转换专门用于处理content_analysis中的作品级数据。图5 新建“标题特征构建”转换准备计算作品级特征6.2 表输入读取 content_analysis拖入“表输入”组件选择实验7-1使用的数据库连接。SQL 可以写成SELECT id, date, author_name, title, platform, likes, favorites, shares, coins, views, url, total_interaction, has_best, has_lowcode, has_practice, has_tutorial, has_pit FROM content_analysis;如果你不确定字段是否完整也可以先使用SELECT * FROM content_analysis;不过在正式实验中更建议显式写出字段后续配置时更清楚。图6 使用表输入组件读取content_analysis表中的作品明细数据6.3 字段选择保留计算字段表输入之后接入“字段选择”组件。这里保留后续计算需要的字段即可主要包括字段用途id插入/更新时作为匹配关键字title用于提取标题关键词likes计算互动总数favorites计算互动总数shares计算互动总数coins计算互动总数total_interaction待更新字段has_best待更新字段has_lowcode待更新字段has_practice待更新字段has_tutorial待更新字段has_pit待更新字段字段选择不是必须的但建议保留。 它可以让后续 JavaScript 和计算器组件接收到的字段更加清晰。图7 保留id、title、likes、favorites、shares、coins等后续计算字段6.4 JavaScript代码提取标题关键词接下来拖入“JavaScript代码”组件并连接到字段选择组件后面。这个组件用于判断标题中是否包含指定关键词。推荐代码如下var t title null ? : String(title); has_best t.indexOf(保姆级) ! -1 ? 1 : 0; has_lowcode t.indexOf(零代码) ! -1 ? 1 : 0; has_practice t.indexOf(实战) ! -1 ? 1 : 0; has_tutorial (t.indexOf(教程) ! -1 || t.indexOf(指南) ! -1) ? 1 : 0; has_pit t.indexOf(踩坑) ! -1 ? 1 : 0;这里的逻辑是标题包含“保姆级”has_best 1标题包含“零代码”has_lowcode 1标题包含“实战”has_practice 1标题包含“教程”或“指南”has_tutorial 1标题包含“踩坑”has_pit 1不包含则为 0。第一行代码var t title null ? : String(title);是为了避免标题为空时报错。 如果标题为空就当作空字符串处理。图8 在 JavaScript 代码组件中编写标题关键词匹配逻辑6.5 配置 JavaScript 输出字段只写代码还不够还要在 JavaScript 组件的输出字段区域添加新字段。需要添加字段名类型has_bestIntegerhas_lowcodeIntegerhas_practiceIntegerhas_tutorialIntegerhas_pitInteger如果这里不添加输出字段后面的计算器或插入/更新组件可能无法接收到这些结果。图9 添加has_best、has_lowcode、has_practice、has_tutorial、has_pit五个输出字段6.6 计算器计算互动总数JavaScript 代码之后接入“计算器”组件。互动总数公式为total_interaction likes favorites shares coins如果计算器支持多个字段直接相加可以直接生成total_interaction。如果只能两个字段相加可以分三步计算新字段计算公式tmp_1likes favoritestmp_2shares coinstotal_interactiontmp_1 tmp_2这样就能得到每篇作品的综合互动总数。图10 使用计算器计算total_interaction likes favorites shares coins6.7 插入/更新回填 content_analysis计算完成后拖入“插入/更新”组件。这里不要使用“表输出”。 因为本步骤不是新增作品记录而是更新原有记录中的字段。“插入/更新”组件可以按照id匹配原表中的记录只更新指定字段。关键配置如下配置项设置内容数据库连接选择实验7-1同一个数据库连接目标表content_analysis查询关键字id更新字段total_interaction、has_best、has_lowcode、has_practice、has_tutorial、has_pit字段映射如下流字段表字段ididtotal_interactiontotal_interactionhas_besthas_besthas_lowcodehas_lowcodehas_practicehas_practicehas_tutorialhas_tutorialhas_pithas_pit图11 配置插入/更新组件按id回填互动总数和标题特征字段6.8 运行并检查 content_analysis保存转换流点击运行。运行成功后关键节点会显示绿色对勾日志中不应出现ERROR。图12 运行“标题特征构建”转换确认节点显示绿色对勾运行完成后预览content_analysis表检查以下字段是否已经有值total_interaction has_best has_lowcode has_practice has_tutorial has_pit可以使用 SQL 检查SELECT id, title, likes, favorites, shares, coins, total_interaction, has_best, has_lowcode, has_practice, has_tutorial, has_pit FROM content_analysis LIMIT20;图13 查看content_analysis表确认互动总数和标题关键词字段已经生成七、第二部分创建 title_feature_analysis 表作品级特征更新完成后接下来要生成关键词级汇总表。这张表用于保存不同标题关键词对应作品的平均互动表现。7.1 创建目标表使用“执行一个SQL脚本”组件创建title_feature_analysis表。SQL 如下DROP TABLEIFEXISTS title_feature_analysis; CREATETABLE title_feature_analysis ( idINTNOTNULL AUTO_INCREMENT COMMENT自增主键, platform VARCHAR(20) COMMENT平台名称, feature_name VARCHAR(50) COMMENT标题关键词名称, avg_interaction DECIMAL(10,2) COMMENT含该关键词作品的平均互动总数, overall_avg DECIMAL(10,2) COMMENT整体平均互动总数, sample_count INTCOMMENT含该关键词的作品数量, PRIMARY KEY (id) ) ENGINEInnoDBDEFAULTCHARSETutf8mb4 COMMENT标题关键词互动分析表;说明一下几个关键字段字段含义platform平台名称feature_name关键词名称avg_interaction含该关键词作品的平均互动总数overall_avg整体平均互动总数sample_count含该关键词的作品数量图14 创建title_feature_analysis关键词级汇总表八、第三部分生成关键词级汇总数据新建一个转换建议命名为关键词级别汇总输出这一部分的目标是 基于更新后的content_analysis表统计每个关键词的平均互动数并和整体平均互动数进行对比。图15 新建关键词级汇总转换准备统计关键词互动表现8.1 表输入读取特征字段拖入“表输入”组件读取更新后的content_analysis表。SQL 建议写成SELECT id, platform, total_interaction, has_best, has_lowcode, has_practice, has_tutorial, has_pit FROM content_analysis WHERE total_interaction IS NOT NULL;这里只读取关键词汇总需要用到的字段即可。图16 使用表输入读取平台、互动总数和五个标题特征字段8.2 整体平均分支计算 overall_avg从表输入组件拉出第一条分支用来计算整体平均互动数。流程为表输入 → 排序记录 → 分组 → 增加常量排序记录排序字段可以选择id升序。分组不设置分组字段直接对total_interaction求平均值输出字段命名为overall_avg增加常量因为后面要和关键词分支连接所以需要添加一个关键词名称标签。 以“保姆级”为例添加feature_name 保姆级这样整体平均值这一行也带有相同的关键词标签后面可以和“保姆级”关键词分支连接。图17 整体平均分支排序后直接计算overall_avg并增加feature_name常量8.3 关键词分支以“保姆级”为例从同一个表输入组件再拉出第二条分支用来计算某个关键词作品的平均互动表现。以“保姆级”为例流程为表输入 → 过滤记录 → 排序记录1 → 分组1 → 增加常量过滤记录过滤条件设置为has_best 1表示只保留标题中包含“保姆级”的作品。过滤记录组件需要设置两个输出方向输出方向连接组件含义匹配成功排序记录1保留含关键词作品匹配失败空操作丢弃不含关键词作品图18 以“保姆级”为例过滤has_best 1的作品记录8.4 关键词分支计算 avg_interaction 和 sample_count过滤成功后接入“排序记录1”和“分组1”。排序字段可以选择id升序。分组组件不设置分组字段直接计算输出字段名源字段聚合方式含义avg_interactiontotal_interactionAverage / 平均值含该关键词作品的平均互动总数sample_countidCount / 计数含该关键词的作品数量聚合后再接入“增加常量”组件添加feature_name 保姆级这样关键词分支也会带有feature_name字段。图19 关键词分支配置计算avg_interaction和sample_count并增加feature_name常量8.5 记录集连接合并整体平均值和关键词平均值接下来拖入“记录集连接”组件。连接两个分支第一条分支整体平均分支第二条分支关键词平均分支。连接字段设置为第一个输入字段第二个输入字段feature_namefeature_name连接后同一关键词下的整体平均互动数和关键词平均互动数就会合并到同一行。最终得到的字段主要包括feature_name overall_avg avg_interaction sample_count如果你的转换流中保留了platform字段也可以把platform一起输出到结果表中。图20 按feature_name连接整体平均值和关键词平均值8.6 表输出写入 title_feature_analysis最后接入“表输出”组件将结果写入title_feature_analysis。字段映射如下流字段目标表字段platformplatformfeature_namefeature_nameavg_interactionavg_interactionoverall_avgoverall_avgsample_countsample_count注意如果当前流程没有输出platform字段可以在“增加常量”中补充平台值或者根据你的视频操作保持平台字段为空/默认值。 如果后续可视化需要区分 B站 和 CSDN建议在表输入或分组阶段保留platform维度。另外如果要连续写入多个关键词不要每次都勾选“清空表”或“裁剪表”。 否则前一个关键词的数据会被删除。更稳妥的方式是第一次运行前手动清空一次表TRUNCATE TABLE title_feature_analysis;然后依次写入五个关键词的数据。图21 将关键词汇总结果写入title_feature_analysis8.7 其他关键词怎么处理完成“保姆级”后其他关键词的处理逻辑完全一样。只需要修改两个地方过滤条件feature_name常量值。关键词过滤条件常量值保姆级has_best 1保姆级零代码has_lowcode 1零代码实战has_practice 1实战教程/指南has_tutorial 1教程/指南踩坑has_pit 1踩坑最简单的方式是复制“保姆级”这一整套流程然后只修改过滤条件和常量值。例如处理“零代码”时过滤条件改为has_lowcode 1常量值改为零代码其他组件配置保持不变。图22 复制关键词分支只修改过滤条件和feature_name常量值即可生成其他关键词数据九、检查最终结果全部关键词处理完成后打开title_feature_analysis表查看结果。可以使用SELECT * FROM title_feature_analysis;也可以按关键词排序查看SELECT feature_name, platform, avg_interaction, overall_avg, sample_count FROM title_feature_analysis ORDER BY feature_name, platform;正常情况下表中应该能够看到不同关键词对应的平均互动数、整体平均互动数和样本数量。图23 查看关键词级汇总结果确认avg_interaction、overall_avg、sample_count正常生成十、实验结果说明完成本次实验后我们得到了两类数据结果。第一类是更新后的content_analysis。 它仍然是作品级明细表但已经新增了互动总数和标题关键词特征字段。第二类是新建的title_feature_analysis。 它是关键词级汇总表用于分析不同标题关键词对应作品的互动效果。表名数据粒度用途content_analysis作品级作品排名、互动趋势、平台明细分析title_feature_analysis关键词级标题关键词互动效果分析图24 实验7-2最终输出更新后的content_analysis和新建的title_feature_analysis十一、常见问题与解决办法11.1 插入/更新组件找不到 id原因通常是content_analysis表中没有id字段。可以执行ALTER TABLE content_analysis ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;如果表中已有主键不要重复添加。11.2 JavaScript 运行后没有生成特征字段常见原因是只写了代码但没有在输出字段区域添加字段。解决办法在 JavaScript 输出字段区域添加has_best has_lowcode has_practice has_tutorial has_pit字段类型设置为 Integer。11.3 total_interaction 为空可能原因包括计算器字段选错likes、favorites、shares、coins中存在 NULL计算器输出字段没有映射到total_interaction。可以在表输入 SQL 中提前处理空值SELECT id, title, platform, IFNULL(likes, 0) AS likes, IFNULL(favorites, 0) AS favorites, IFNULL(shares, 0) AS shares, IFNULL(coins, 0) AS coins FROM content_analysis;11.4 title_feature_analysis 没有数据可能原因包括关键词过滤条件写错content_analysis中标题特征字段还没有更新表输出字段映射错误结果表被清空后没有重新写入。可以先检查某个关键词是否有数据SELECT COUNT(*) FROM content_analysis WHERE has_best 1;如果结果为 0需要先重新运行“标题特征构建”转换。11.5 多个关键词结果被覆盖如果每次运行关键词汇总时都勾选“清空表”或“裁剪表”前面关键词的结果会被删除。正确做法是第一次运行前清空一次title_feature_analysis后续写入不同关键词时不要再清空表。可以先执行TRUNCATE TABLE title_feature_analysis;十二、实验小结本次实验完成了自媒体运营分析中的作品特征构建。整个实验可以分成两个阶段第一阶段是更新作品级明细表。 我们使用 JavaScript 代码提取标题关键词特征使用计算器计算互动总数再通过插入/更新组件把结果回填到content_analysis表中。第二阶段是输出关键词级汇总表。 我们通过过滤、排序、分组、增加常量、记录集连接和表输出等组件统计不同标题关键词对应作品的平均互动表现并输出到title_feature_analysis表中。完成这一步后数据就不只是清洗后的明细数据而是进一步变成了可以直接用于分析和可视化的特征数据。下一步实验7-3就可以基于这些数据制作作品互动排名平台表现对比标题关键词效果分析自媒体运营综合仪表板。十三、完整流程回顾最后用一张流程回顾本次实验实验7-2作品特征构建 阶段一更新 content_analysis ├── 表输入读取 content_analysis ├── 字段选择保留必要字段 ├── JavaScript代码提取标题关键词 ├── 计算器计算 total_interaction └── 插入/更新回填作品级特征 阶段二生成 title_feature_analysis ├── 创建 title_feature_analysis 表 ├── 表输入读取更新后的 content_analysis ├── 整体平均分支计算 overall_avg ├── 关键词分支计算 avg_interaction 和 sample_count ├── 增加常量添加 feature_name ├── 记录集连接合并平均值 └── 表输出写入 title_feature_analysis数据分析并不是把数据导入工具后直接画图。 真正有价值的分析往往需要先把原始字段加工成能够表达业务含义的特征。在本次实验中我们把点赞、收藏、分享、投币加工成了互动总数 把标题文本中的关键词加工成了 0/1 标志字段 又进一步把关键词作品的互动表现汇总成了可视化分析表。这样后续制作图表时就不只是“展示数据”而是可以真正回答什么样的标题更容易获得互动 哪类关键词更适合用于自媒体内容运营 不同标题特征之间的互动效果是否存在差异到这里自媒体运营分析的数据基础就更加完整了。