1. 这个项目到底在解决什么问题营销自动化这个词听起来很性感但落到实际场景里它要干的事情无非是“在合适的时间把合适的内容通过合适的渠道发给合适的用户”。这四个“合适”背后全是数据问题。我当时接手的是一个电商品牌的数据团队营销部门已经堆了 CRM、广告投放平台、订单交易库、客服工单等七八个数据源。每个系统的数据都在各自生长CRM 里记录的是用户资料和会员等级广告平台只有曝光点击和消耗订单库管的是交易状态客服系统存的是沟通记录。营销自动化最基础的人群圈选、活动触达、效果复盘都要靠人工把这几个系统导出来拼在一起。一个活动做完复盘报告往往要等两位数的时间窗口而且数字口径经常对不上。所以“多源数据 OLAP 架构演进”这个项目的本质不是给公司“上一个 OLAP 引擎”而是重建一套能够支撑营销自动化的数据底座。OLAP 在这里起的作用是把杂乱的多源数据组织成可分析、可计算、可查询的多维结构让营销人员不用再和几十张业务表打交道而是直接面对“用户、时间、渠道、商品、行为”这样清晰的维度模型。这个项目适合谁看第一类是营销数据分析师你要知道为什么很多分析需求卡在数据准备阶段第二类是数据开发你知道选型 OLAP 不只是在 ClickHouse、Doris、Elasticsearch 之间挑一个更要知道架构怎么演进才不会把自己坑进去第三类是负责技术决策的人想理解“数据驱动”和“自动化”之间的那条桥到底长什么样。我在整个项目中最大的体会是OLAP 架构演进一旦脱离了业务场景就会变成纯粹的技术自嗨。所以下面的内容我不打算讲太多花哨的概念而是把我走过的路线、做过的取舍、踩过的坑一条条拆给你看。2. 先回顾一下最开始的那套数据架构为什么撑不住2.1 营销团队当时的“数据基建”是什么样最早期的架构非常常见每天凌晨用调度工具把几个业务系统的数据同步到 MySQL 数仓再用存储过程或者 SQL 脚本算一堆宽表。运营要看数据的时候提工单给开发开发按口径写一段 SQL跑完导出 Excel一般当天能出结果已经算快。这套结构在业务量小的时候够用但营销自动化的需求一上量问题就被顶出来了。第一个突出来的问题是数据割裂。做一次短信触达需要先从 CRM 拉出会员等级符合条件的人群再去订单库里判断近 90 天有没有购买行为还要去广告平台后台查最近 7 天点击过但没有下单的用户。三个平台各有各的用户标识有的用手机号有的用设备 ID有的用业务订单号合并不好数据就永远对不齐。第二个问题是多维分析的效率太差。营销人员天然想按“时间、渠道、商品类目、新老客”这几个维度随便组合看数据。传统 MySQL 的 COUNT 和 GROUP BY 在千万级甚至亿级数据上跑不动跑一次全渠道转化漏斗一个小时起步。这种情况在技术上是可以优化的但它实际上引出的问题更麻烦因为分析太慢大家就不愿意做分析了全凭活动经验在做决策。第三个问题是自动化触达需要近实时的数据支撑。营销自动化的核心场景是一套实时规则引擎用户在 APP 上加购却迟迟不付款系统即时给他发一张券用户连续三次访问活动页但没下单系统自动推送优惠信息。这类触发式营销需要秒级到分钟级的数据延迟。传统批处理链路只能在下一个调度周期才能把数据算出来等用户已经被触达过了规则才姗姗来迟。2.2 为什么传统“跑数表”模式解决不了传统模式下大家习惯于把每个分析需求落成一张表。今天运营要“加购未支付用户表”明天要“高活跃流失预警表”后天要“渠道转化明细表”日子一长表越堆越多口径乱成一锅粥。更痛的是这些宽表大都基于固定的汇总粒度。比如你建了一张“用户-商品-渠道-日聚合表”一旦运营提出“我想看看这些用户最近 2 小时的行为”这张表立刻就没用了因为聚合粒度已经锁死在“天”。在营销自动化场景里分析维度是高频变化的。渠道可以新增页面上一个按钮改名商品类目要调整层级甚至连“新客”的定义都会从“首次下单”改成“最近 365 天未下单”。固定粒度的表改一个口径就要重写一大片逻辑这种维护成本在架构上属于不可持续。OLAP 架构的核心不是解决“某一条查询能不能变快”而是把数据组织的逻辑从“按需求建表”改成“先建立多维模型再按需要的组合去自由查询”。2.3 架构演进的根本驱动力是什么我个人拆解下来真正推动这个项目立项的是四个业务诉求多源融合CRM、订单、广告、行为日志必须能在同一个分析模型里关联。口径统一所有人打开数据面板看到“转化率”的含义必须一致。实时能力触发式营销要分钟级甚至秒级的数据反馈。灵活分析营销运营要能自助圈人、自助看效果不再依赖开发排期。这四个诉求没有一个是靠单纯加机器或者加几张宽表能解决的。OLAP 架构演进本质上是对数据组织方式的重构。3. OLAP 架构演进的两个阶段从“多维建模”到“面向分析弹性的服务”3.1 第一演进阶段把数据组织方式改成多维模型刚开始做演进时我没有直接上最新的引擎而是先把数仓模型改了这是我认为最稳妥的一步。以前团队建表习惯是“运营要什么字段就拖什么字段”现在改成标准的多维建模事实表和维度表分离。比如营销事件事实表每一行记录一个用户一次营销触达的完整事实在什么时间、哪个渠道、触达了哪个用户、展示的是什么内容、有没有曝光、有没有点击、有没有转化。用户维度表放手机号、设备 ID、会员等级、城市、注册时间渠道维度表放渠道名称、渠道类型、投放媒介。分析时把事实表和维度表通过主键关联然后自由切片。这一步收益非常大。营销团队以前每次活动复盘要把十几个指标做成一个 Excel 看板现在分析师直接通过 BI 工具拖拽维度组合几秒钟就能出结果。批处理的没改但“灵活分析”这个能力先立起来了。3.2 第二演进阶段从离线批量到准实时和实时并存离线多维模型稳定运行一段时间后我意识到光有离线还不够。营销自动化里的触发规则必须消费实时数据。于是我们把链路拆成两条一条是离线链路负责日级的深度分析另一条是准实时链路数据通过消息队列流入流式计算任务经过清洗、关联、聚合后写入 OLAP 引擎。业务活动期间的“实时大屏”和“自动化触发规则”全部建立在准实时链路上。这个阶段OLAP 引擎的角色不再是简单的报表查询而是成为一套“数据服务底座”。查询请求不仅来自 BI还来自人群圈选接口、自动化触达引擎、数据分析师的 Ad-hoc SQL。3.3 引擎选型的核心权衡MOLAP、ROLAP 和搜索式分析市场上 OLAP 引擎类型很多我这个项目里真正深度对比过的有三类类型代表方向优势劣势适用场景MOLAP预计算模型Kylin、Doris 的预聚合模型查询极快可以支撑超大数据量模型构建有延迟维度组合受限固定维度的经营分析、财务指标ROLAP实时计算模型ClickHouse、Doris 的明细查询明细查询灵活实时写入能力强高并发复杂关联需要优化实时漏斗、用户行为分析、人群圈选搜索引擎式分析Elasticsearch查询语法灵活模糊搜索能力强明细大扫描性能衰减明显复杂多表关联弱日志分析、倒排索引查询、低基数聚合简单说营销自动化的主场景是“用户行为分析 人群圈选 实时指标”用了很重的 MOLAP 反而不合适。我们在整体架构上采用了“ROLAP 为主、预聚合为辅”的混合方案明细层数据实时写入 ClickHouse/Doris指标层用物化视图或预聚合表做一些热点指标加速。3.4 很多团队问能不能用 Elasticsearch 实现 OLAP这是一个很现实的问题。因为 Elasticsearch 的 Kibana 看起来能做图表聚合语法也支持 sum、count、terms 这些很多团队就尝试用 ES 去承担 OLAP 职责。我用过也踩过坑。Elasticsearch 的聚合能力适合低基数、固定模式的查询比如“求这几天每个渠道的曝光量”、或者“按用户性别统计订单数”这种场景 ES 可以跑得很快。但一旦进入高基数明细分析比如“分析 3 亿条行为日志中每个用户每天的行为序列”Elasticsearch 的深度分页和大聚合会让集群内存直接告警响应时间变成几十秒甚至 OOM。更麻烦的是 ES 的多表关联能力很弱。营销分析最常做的事情是“把订单事实表、用户维度表、渠道维度表关联起来”在 ES 里做这种关联要么提前 join 成大宽表要么用嵌套文档前者数据冗余严重后者查询性能下降。所以我的建议是Elasticsearch 可以出现在营销自动化的架构里但定位是“可检索的数据索引”比如运营搜索某类用户记录、排查单用户行为日志而不是把它当作通用 OLAP 引擎去承接全量多维分析。真正的 OLAP 分析交给 ClickHouse 或 Doris 这类列式存储引擎更合适。4. 多源数据接入的实操细节从源系统到 OLAP 的五步走4.1 第一步盘点源系统确定主键映射和语义多源数据接入前最混乱的就是“同一个用户在不同系统里的 ID 不一样”。我们项目里当时有四套用户标识CRM 的会员 ID、APP 的设备 ID、广告平台的投放 ID、订单库的手机号。前两周的工作全花在打通这些 ID 上。我最终的方案不是粗暴地选一个主键而是做了一张“用户映射表”把所有标识统一关联到一个全局的 user_key 上。这张映射表本身不复杂难点在实时更新。每当某个系统报来一个新的设备 ID 或手机号映射关系就要通过规则引擎去匹配同人逻辑这个过程的准确性直接决定了后面所有多维分析的结果质量。这个步骤给后来者的建议只有一个不要急着写同步任务先把四种数据资产的字段字典和主键逻辑理清。漏掉这一步后面越跑越乱。4.2 第二步确定同步机制实时和离线分开不同源系统的数据更新频率差异很大。CRM 的用户资料一天变几次订单表每秒都在产生新数据广告平台的报表则以小时粒度提供。所以同步机制不能一刀切。我一般会把同步分成三类全量同步适用于维度表比如商品类目表、渠道配置表。每天跑一次就行。增量同步适用于事实表达比如订单表用时间戳或自增 ID 抽取新数据频率可以做到每 5 分钟一次。日志实时同步适用于行为事件比如浏览、点击、加购。通过监听 binlog 或业务埋点日志走消息队列接入流式计算。在技术选型上业界常见的组合是“Canal/Debezium 监听数据库变更 Kafka 传输 Flink 做流处理”。这套链路对业务侵入小又能保证秒级到分钟级的延迟是营销自动化时效性的主要支柱。4.3 第三步数据清洗和口径归一多源数据进到统一模型之前清洗这一步绕不开。我们遇到的典型问题包括时间格式不统一有的库存的是时间戳有的是yyyy-MM-dd HH:mm:ss还有的带着时区后缀必须统一转换成 ISO 8601 标准格式。币种和金额不一致广告平台报的是消耗金额订单库存的是订单实付金额前者单位是分后者单位是元汇总之前必须先统一。渠道命名混乱同一个渠道在 CRM 里叫“信息流A”在广告平台里叫“ad_group_A”必须维护一套渠道映射字典。清洗规则我建议写成两层第一层在接入阶段做基础格式清洗第二层在 OLAP 模型层做业务口径转换。这样即使在源头改了字段分析层也不至于一夜之间崩掉。4.4 第四步构建统一的营销事件模型多源数据打通清洗之后最核心的工作是建设统一事件模型。这个模型的核心是一张“营销事件事实表”结构大致是这样CREATE TABLE marketing_event ( event_id String, user_key String, device_id String, event_time DateTime, event_type String, -- exposure / click / order / receive_coupon channel_id UInt64, campaign_id UInt64, content_id String, scene_id String, order_id String, pay_amount Decimal(18, 2), attribute MapString, String ) ENGINE MergeTree() PARTITION BY toYYYYMMDD(event_time) ORDER BY (event_time, user_key, event_type);这张表听起来简单但设计的时候有几个细节特别重要。一是分区字段必须选择查询频率最高的时间字段二是排序键要把“事件时间、用户、事件类型”的优先级排好否则 OLAP 查询永远在扫全表三是对多源系统的来源字段要做保留方便以后排查数据质量问题。4.5 第五步指标口径管理是 OLAP 项目成败的分水岭技术接入做得再好如果“转化率”的口径在 CRM 和订单库里不一致整个项目照样会被业务抛弃。我在项目里定了一个规矩所有对外提供的指标必须在指标字典里登记三样东西——指标名称、计算逻辑、适用场景。比如“用户转化率”就必须写清楚分子是“发生了首笔订单支付行为的用户数”分母是“活动触达用户数”统计窗口是自然日还是活动期都写死。有了指标字典OLAP 模型才敢把指标结果向下游业务服务输出。后续不管 BI 报表、自动化触发规则还是算法团队取数都必须从指标字典中找到对应的标准定义不允许各算各的。5. 营销自动化场景是怎么落地的5.1 人群圈选从“等开发跑数”变成“运营自己拉”营销自动化第一个核心能力是“随便拖几个条件就能圈出人群”。支撑这个能力的是 OLAP 快速查询。运营原来提需求“找出最近 30 天加购超过 3 次但没有下单的女性用户”开发得写一段长 SQL上午提需求下午出结果。现在运营直接在圈人后台点几个筛选项后台把它翻译成 OLAP 查询SELECT user_key FROM ( SELECT user_key, countIf(event_type add_cart) AS add_cnt, countIf(event_type order) AS order_cnt FROM marketing_event WHERE event_time now() - INTERVAL 30 DAY GROUP BY user_key ) WHERE add_cnt 3 AND order_cnt 0;这类查询在 OLAP 引擎上如果命中了分区和排序键一般 200 毫秒到 2 秒就能出结果。我实测下来关键在于尽量把过滤条件下推到引擎层执行而不是在查询结果里再筛选。所以圈人后台的筛选条件设计本质上是给每个条件绑定一个“下推字段”而不是用程序逐条过滤。5.2 效果归因分析多源数据关联的价值体现营销自动化的归因是个很经典的 OLAP 场景。用户先在某广告平台看到广告过了一天从搜索引擎进入官网再过了两天在 APP 内完成下单。到底这笔订单算哪个渠道带来单独看广告平台数据它只看到点击没看到成交单独看订单库只能看到最终来源。我在项目里落地的是“事件路径归因”方案把每个用户从首次触达到最终转化之间产生的所有营销事件按照时间排序拼成一条路径然后通过规则给路径上每个触达点分配权重。这个过程最耗费算力的地方是把同一个 user_key 下的多个事件做序列关联。一开始我尝试直接用 OLAP SQL 做这类路径分析结果发现 SQL 写起来很笨重而且引擎在长序列事件分析上性能衰减明显。后来换了思路归一化路径计算放在流处理任务里提前算好把归因结果写入单独的事件路径表OLAP 层只负责对结果做多维度汇总。架构上的分工变成了“流式计算负责复杂逻辑处理OLAP 负责海量数据查询”两边各干各擅长的效果明显好转。5.3 实时触达引擎的“冷却期”与频控实时触达场景里最怕的不是算不出来而是“同一时段把用户轰炸太狠”。比如一个新用户注册后 10 分钟内连续触发了三次“首单优惠”规则系统如果每次都发优惠券用户体验就会很差成本也会失控。OLAP 在这个场景起用的是“实时频控计数”能力。触发规则在决定是否触达之前先查一次固定时间窗口内的触达次数SELECT count() FROM marketing_event WHERE user_key {user} AND event_type IN (receive_coupon, push_sent) AND event_time now() - INTERVAL 24 HOUR;这个查询在明细表上加索引优化后单次查询成本很低但是触达引擎每秒要打几十次甚至上百次这种查询所以必须再叠加一层缓存。我们对近 1 小时内的频控计数做了本地缓存只有缓存过期时才回源查 OLAP这样既保证了频控的准确率又不会把引擎压垮。5.4 顺带说一句“小样本场景”里的模型和数据驱动问题营销自动化的数据驱动不会只有 SQL 和报表还会牵涉到预测模型比如预测用户流失概率、预测某个用户对某个优惠的心动程度。但这里有个很现实的坑营销活动覆盖的样本数往往并不大。在小样本场景下纯数据驱动的机器学习模型很容易在训练集上拟合得很好比如准确率 90% 以上但一上线应用到新用户群体效果立刻打折。这就是大家常说的“数据驱动模型拟合好但泛化不足”。道理其实不复杂——样本太少时模型会把训练数据里的噪声当成规律记住一遇到没见过的数据分布就失灵。我在这个项目里的做法是把“数据驱动”和“业务规则约束”结合起来。先用小样本数据训练一个初始模型但不让它直接决定触达动作而是让规则引擎给它加一层业务边界比如“该用户已经是高活跃用户不再推送睡眠唤醒优惠”“该用户 30 天内已被触达 5 次不参与本次活动”。模型负责给出倾向性排序规则负责保证业务正确。这套混合机制比起盲目追求模型指标在真实营销场景里要稳得多。6. 踩坑实录与常见问题排查6.1 多源数据口径对不上报表数据打架排查表问题现象根本原因解决办法广告平台和内部报表的消耗金额对不上一个算含税金额一个算不含税消耗在清洗层统一货币单位并保留原始金额字段备查CRM 会员数和订单用户数不一致会员表有历史未清理数据订单库缺少部分离线订单统一以 user_key 映射表为唯一用户主键业务字段按来源区分“转化率”在报表 A 和报表 B 不同分子/分母口径定义不同所有指标标准化到指标字典BI 层禁止新建自定义字段6.2 数据倾斜导致 OLAP 查询突然变慢有一次在做大促活动复盘时发现某个查询要跑 40 多秒查下来问题出在“爆款商品”上。一个商品承担了当天 40% 的订单量按商品 ID 做分组聚合时单个分片的计算压力远大于其他分片整个查询被这个热点拖住了。解决办法是在设计事实表时对明显存在热点的高基数维度加两层处理。第一层是按更细粒度分区把一天的 data 按小时或按渠道拆得更细第二层是查询层面做二次聚合先在小粒度上聚合出结果再把结果汇总成大盘指标避免所有明细一次性堆积到一个计算节点上。6.3 事件时间戳乱序实时指标一会儿高一会儿低多源数据接入时不同系统对“事件时间”的定义不一样。有的系统上报的是客户端时间有的上报的是服务器接收时间。客户端时间会被用户改本机时间影响服务器时间又可能和实际业务行为有偏差。两种混在一起实时指标就表现为前后抖动。最后定下的规矩是OLAP 分析统一使用事件发生时间也就是客户端埋点带上来的时间但在数据链路里同时保留“接收时间”字段用于监控数据到达延迟。遇到时间乱序严重的场景在流处理任务里加一个延迟容忍窗口窗口内允许晚到的数据修正之前已经计算好的结果。6.4 Elasticsearch 集群内存被打爆的教训前面说过用 ES 做 OLAP 的坑这里说一次具体事故。某个运营想分析近 90 天所有用户在每个内容位上的曝光点击明细直接在 Kibana 里拖了一个大范围聚合。当天下午 ES 集群 CPU 飙到 90%内存占用一路走高最后触发了熔断。这个事的教训有两个一是 ES 的聚合查询必须设置超时和结果数上限不能让用户随意拉全量大范围明细二是真正明细级的 OLAP 分析必须迁移到列式引擎上ES 只保留“最近 24 小时”的活跃数据用于快速检索和问题定位。7. 架构演进完成之后我再回头看的一些体会整套 OLAP 架构演进落地以后最有成就感的事情并不是查询速度从几十分钟变成了几百毫秒而是营销部门的同学开始愿意自己动手做分析了。以前运营提需求要排队现在圈人、看漏斗、做活动复盘都能自助完成。数据驱动从此不再是挂在嘴边的一句话而是真正长在了日常工作机制里。如果让我给正在做类似项目的团队一个建议我想说不要一上来就追求大而全的实时数仓和全套 OLAP 引擎矩阵。先从一两个最痛的营销场景切入把多源数据接入、多维建模、指标口径这三件事做扎实再逐步扩展到实时触达和算法应用。OLAP 架构演进说到底是一场数据组织方式的重构业务价值永远应该排在技术炫技前面。最后再分享一个小细节这个项目里让我最受益的是维护了一份“数据源血缘关系文档”。哪个字段从哪张业务表来经过了几层清洗和转换在哪个 OLAP 模型里被消费写得清清楚楚。这份文档在后续排查问题时救了我很多次强烈建议你也从一开始就建起来。