当Matomo遇上excelVBA！

Matomo是一款免费开源的用户行为分析与流量统计软件（php+mysql），与google分析、百度统计相比，可以进行本地部署。经过一系列定制化改造基本能够满足公司数据运营的需要，在试行了几个月之后发现痛点集中到了数据报表生成效率低及流量渠道划分与实际不匹配的问题上。

数据报表生成效率低的主要原因是直接展现的数据集合相对分散，一些重要的数据需要通过一层层的钻取才能够获得，所以需要手工找，效率的低下可想而知，关键是人工操作带来的数据准确度可能给运营决策带来误导，曾经有几次把问题归结为统计工具统计的数据不准确，其实是统计者把数据搞错了，这锅只能工具来背了！

关于流量划分的问题，市面上的大多数流量统计分析工具都是相对标准化的，大的分类无非是直入、搜索引擎、第三方网站和推广，但想进一步细化就显得灵活性不够了，Matomo也不例外。

开源，本地化部署的最大优势就是可以看到源码，可以随心所欲的获取相关流量数据进行二次加工，虽然工具开发语言是php，但只要不是对核心模型的调整，二次开发也不在话下，但是需要谨慎，如何既能够最大限度的保持现在的版本，同时又能解决上面的痛点呢，首先想到的是使用第三方收费插件，由于该工具是国外的，在使用习惯上和国人显得有点相悖，同时也存在上面提到的问题——灵活性不足。

经过定性分析与数据库结构的研究，我觉得通过excel VBA是个可行的选择，在不动源码的情况下直接从数据库获取数据，再利用excel强大的数据处理功能基本可以做到一键解决痛点。

以前写过不少提升工作效率的VBA小工具，但大多来自现成的数据源，通过直接连接数据库的案例以前做过但不多，不过可行性没有问题的情况下最主要精力就是对数据结构的分析，对数据的模拟演绎以及归纳与总结了。

除了流量，订单恐怕是运营分析的核心了，流量转化情况，产品分布情况，各渠道占比分布，投放效果等，如果以订单为抓手，追溯用户行为路径，直到流量的源头就可以很容易的做到订单→产品→流量的数据分析模型了。

Matomo有一个显著的特点是数据的间接存储，明文都被转化成数字码(idaction)，比如url、网页标题，产品编码和名称、访问者被加密，核心源码被转换成16进制，所以通过爬虫爬取数据的路基本堵死了，这为数据的抽取增加了难度，但是有几个中心表如果破了，基本的主要矛盾也就解决了，毛主席说抓住了主要矛盾问题也就解决了一半。

这几个表就是log_visit(访问表)，log_action(行为字典表)和log_conversion(转化表)，其次是几个概念:访问，访客，行为，订单与转化，最后是这些概念如何具体的体现在上面的各个表中，互相之间是如何联结的，最终写出核心的sql，剩下的就是如何展现，如何一键生成最终报表的问题了。

这里有两个方向需要考虑，是流量→订单，还是订单→流量。在所有的统计分析工具中，都有一个共性的问题就是流量与转化的割裂，无论是前面提到的GA还是BA，以及GIO还是其他的如百夫长之类的流量统计工具极具同质化的产品，瓶颈也都出奇的一致，订单与流量的关联一般有两种方式，要么推要么拉，我们采用的是推拉结合的方式，拉的方式即Matomo通过埋点把订单拉走，这种方式存在丢单的问题；推的方式就是通过API接口主动把订单推到Matomo的数据库，由于必须依赖cookie机制，而该机制的稳定性一直被诟病，所以推拉结合可以一定程度上互补，最大程度上降低了丢单率。再回到刚才说的两个方向，由流量到订单由于流量的飘忽不定和”杂乱无章”分析起来仿佛无根的浮萍，就像拳头打在棉花上，所以以订单为切入点，顺藤摸瓜的找到访客特征，流量来源相对就容易多了。

确定了方向以后就该考虑具体怎么走了，一般情况下，无论对数据的分析与挖掘多么深入，都需要有基础数据做支撑，所以第一步应该把基础数据取出来，这样数据主要指订单相关的信息，包括订单所具有的基本属性，比如金额，产品及类别，流量来源，访客地域，购买时间等等，有了这些数据就可以进一步按不同维度制定统计规则了。

这次工具的制作真正的难点不在销量的统计上，而在访问量的统计上，访问量分为产品访问量和流量渠道访问量，特别是后者颇费了一番功夫，主要原因是我们从实际出发对流量渠道进行了二次划分，如何将原来的直入、搜索、网站和推广四大渠道细分为7大渠道后两者的流量总数不变，这就需要不能有流量漏到外面，确实是在不断地尝试中逐渐接近的，最接近的时候一天的访问量会有个位数的偏差，这说明还有优化的空间，但是应该满足运营的需要了。

除了前面提到的访问、行为和转化三个核心表外，还有一个表相对于log_action是真正的记录访客行为数据的，访客在log_visit的一次访问会对应log_link_visit_action里的多条记录，这个表里的记录是以访问路径（URL）为对象的，所以想统计每款产品的访问量就必须从这个表里抽取数据，这些URL是通过内置转换码可以在log_action(行为字典表)表里一一查到的，对于我们每个产品的详细页有一个固定的部分就是URL最后12位，比如123456.shtml，前面可能是wap开头，也可能是www开头，从实际数据来看，还有其它开头的URL，这些链接的访问虽然不多，但是不统计会影响数据的一致性，统计的话就会大大的降低统计效率，123456.shtml后面的内容就会更多样，任何加过参数的访问都会作为新的URL被记录，所以一个产品详细页可能对应上百个URL，统计该产品的访问量其实就是统计这些URL的记录数（要idvisit去重），这大大增加了sql语句的复杂度，并且必须用左右都模糊的查询，索引就不生效，查询效率肯定很低，我想的方案专门创建一个沉淀数据的表，通过mysql自身的定时任务机制，每天把前一天的数据先沉淀起来，沉淀的过程效率虽然低（执行一次大概40秒），但是对运营人员来说是无感知的，但IT人的“偏执”对这种哪怕有一次访问量对应不上的情况都是不能容忍的，所以后续仍需要进一步研究，直到查出的数据和通过matomo终端看到的数据完全一致。