日报、周报、月报,是不是运营的日常工作?
汇总数据、制作报告图表,是不是觉得费时间?
报表做的不好看,被老板批评,是不是时常发生?
每次老板要数据或分析结果时,是不是得弄半天?
那如何做效率高且又好看的日报表呢?
看完这篇分享就能掌握到自动化报表的知识了。
下面进入正题,我会以实际的案例来分享一个项目的自动化日报表是如何制作的。
为了更好的提升工作效率,最近一直在研究Excel自动化报表,也制作了一些报表案例,比如自动化周报、综合投放分析表,积累了一些做表的经验,对于自己业务的自动化日报,却一直没有完善,做过一版简单的,但随着业务发展,多款游戏+变现+内购+买量的综合需求,导致数据汇总和展示的难度变大,于是下定决心开始研究新版日报。
自动化日报的标准也很简单
除了数据手动更新外,其余全自动
日报的时间效率控制在3分钟以内
通过需求分析、汇总结构、可视化展示多个方面开始准备,但没想到难度系数远远超过之前做的报表,每遇到一个难点都要研究攻破,前后差不多两周时间终于完成。
简单汇总一下表格的特征
67个表连接
超过1000行M函数代码
错踪复杂的查询关系图
基本上是一个产品经理+程序员的开发工作了,不过成果也是显著的,把一个正常需要约2小时的复杂日报表优化到3分钟效率,并可视化展示。我也想总结一下这次做表的经验,分享给大家,给报表设计者提供一些逻辑和思路。
先展示一下Dashboad页,可以先看看最终完成的效果,然后再分步拆解设计思路
这个Dashboard就是最终完成的效果,通过文本框、数据表、图表等方式,清晰直观的数据可视化效果,在分步介绍之前,我们先简单介绍一下制作的主要流程
需求分析--源数据获取--数据清洗运算--
自动化--可视化展示
01 需求分析
做报表前的准备工作是非常重要的,第一步就是需求分析,根据业务类型,思考需要计算展示的数据内容,以及报表的阅读者是谁?自己、组内小伙伴、领导,所以要结合不同阅读者的需求来分析,一般来说关键指标、KPI完成度、日数据对比、趋势变化都是常用的需求,那我们先分析一下这个报表的需求类别
休闲游戏变现业务日报表
关键数据指标展示
多项目明细数据展示
多项目收入数据展示
项目日志展示
显示项目指标趋势变化
项目投放数据展示
项目投放回收计算
项目投放时间轴标记
02 源数据获取
需求分析完成后,可以记录下来,如果有遗漏也没关系,可以后续补充,但核心需求一定要明确,接下来就是要确认源数据了,一般源数据的来源有这几种,数据后台导出、邮件日志、数据仓库获取,基于Excel来存储数据,最简单的方法就是复制粘贴,对Sheet页进行命名,最终报表和源数据是在一个Excel文件里的,好处就是使用成本低,方便传输。
10个游戏项目的基础数据,分别来自不同的数据统计后台(Talking Data、Flurry等)
3个变现SDK收入数据,分别来自不同的变现平台(穿山甲、米盟、Admob)
不同投放平台的数据(头条、亿米、Facebook)
源数据表更新完成后,建立超级表并命名,然后通过Power Query加载成数据连接即可
03 数据清洗--数据运算
需求和源数据的工作完成后,就可以开始正式的做报表开发了,数据清洗运算其实是两个步骤,首先获取的源数据,并不是直接可以用来运算出结果的,这时就需要对数据进行处理,有下面几个维度
数据格式的规范化,日期、文本、数值
数据显示规范,需要转化为二维表
数据标题字段名称统一,英文加注释
对无用的数据进行删减,或者在分组计算的时候设置字段
对同类型分散的数据进行汇总
所以针对该报表的数据清洗主要是下面的步骤
多个游戏的基础数据(新增、DAU、留存)统一标题字段,合并游戏汇总表
不同变现平台的数据(展示、eCPM)统一标题字段,合并成变现汇总表
不同投放平台台的数据(展示、点击、下载、花费)统一标题字段,合并成投放汇总表
对于数据清洗,我觉得最简单的逻辑就是不要管源数据的格式,源数据怎么乱都无所谓,源数据只保留最原始的复制粘贴内容,一切操作都在PowerQuery里,并不会修改源数据。
数据运算则是报表制作过程中最复杂的一步,也是最费时间的一步,单个数据表的运算需求不难,但是27张源数据表的综合运算,对逻辑性的要求就非常高了,这里最好是先画一个脑图,结合之前的需求分析,每个需求需要多少张表来实现,每张表又关联哪些源数据,用脑图的方法列出来会清晰很多,然后运算过程中还有一个重要步骤,就是辅助表,比如日期表、KPI数据、游戏类型等,一个辅助表会减少很多计算步骤。
逻辑梳理完成后,就可以开始一步一步的进行数据运算了,接下来我会详细讲一下步骤,首先是制作辅助表,包含日期表、汇率表、KPI、留存表、排序表、游戏名标注等
日期表是最基本的,数据里的日期格式一般都是年月日,但是月数据汇总就需要用到年月,周对比需要用到星期,所以得创建一个日期表用做合并查询,用M函数或手动创建。
KPI表很简单,手动填写每月KPI,用来跟已完成的数据指标进行计算
游戏标注则是每个游戏会有一个别名或类型,用辅助表的方式可以自动查询
汇率表就是美元和人民币的汇率,但汇率其实是变化的,这里我是取的每个月第一天的汇率,用辅助表来计算
辅助表建立完成后,同样也是加载到Power Query中,辅助表一般放在同一个Sheet页
所有准备工作都完成后,接下来就是每个表的汇总计算了,这里我没法把具体的方法全部描述出来,将以思路和逻辑分析为主,操作全部在Power Query中完成。
前面步骤中,我们已经将不同的源数据进行数据清洗,并合并成三个主要的数据表,分别是游戏源数据表、变现源数据表、投放源数据表,所以接下来就将这三个表,再继续汇总合成,这里使用合并查询的方法,通过日期、游戏名、平台、渠道多条件进行数据匹配,最终汇总成一个总表,每个日期为一行数据。
汇总最终大表不难,汇总后,可以对需要的数据字段进行添加自宝义列,例如
游戏数据:
ARPU=收入/DAU
平均广告展示次数=总展示次数/DAU
变现数据:
收入数据直接使用,但eCPM需要用透视表的方式,分出一个辅助表
投放数据:
CTR=点击/展示
CPI=花费/激活
至此基础运算数据差不多完成,但并没有结束,接下来要进行自动化所需的计算
04 自动化 05.可视化
自动化和可视化是相关联的,所以就放在一起讲。
自动化可能是大家最关心的部分,在一张报表中,如果是一次性的计算,相对比较简单,无论是公式、图表,直接获取想要的数据计算即可,但同样的数据需求,要实现自动化,方法思路都要重新设计,有可能一个最简单的步骤,都需要代码来完成。我们再回到需求,前面提到自动化的原则是更新源数据,修改日期,其余全自动,等于所有的表都需要互相关联,看似很难,其实真的很难。
首先我们的报表是日报,那就需要一个当前日期,先建一个两个单元格的日期表,加载到Power Query(以后的所有表都需要Power Query来处理)
这个日期就是自动化报表最关键的部分,另看只有两个单元格,当更新数据后,就可手动更改数据截止日期,再刷新后自动化。
这时候就需要把自动化和可视化一起来思考了,大家回到上面的Dashboard图,首先我们要展示的是日关键数据指标,所以利用日期来合并查询所需的关键指标,然后再使用展开聚合的功能,把当月数据合并进来,添加自定义列,就完成了第一部分的数据需求。
数据表部分
可视化展示
说到这里,大家肯定好奇,单元格里的数据是怎么变现好看的文本框的,并且还可以自动变化,所以教大家一个不外传的小技巧,就是文本框除了编辑文字外,还可以引用单元格。
解决关键指标展示后,第二部分是分项目数据明细,这个就比较简单了,直接使用Power Query对日报日期和综合汇总表进行合并查询,再加载成超级表,直接在Dashbarod页进行单元格引用。
第三部分是项目日志,每天光汇总数据不行,还需要写一些事件分析,直接写就可以?当然有更好的方法,就是单独建一个项目日志的Sheet页,再用公式把日志内容根据当前日期引用过来,好处是可以对日志进行存档,以及当你更改日期后,显示的就是当天的日志。
这里就直接把公式粘贴出来了,一个简单的公式,也是这份报表里唯一使用到函数的地方
=IF(IFERROR(OFFSET(INDEX(项目日志!C:C,MATCH(日报日期[日期],项目日志!A:A,1)),ROW(A1)-1,0)," ")="","",IFERROR(OFFSET(INDEX(项目日志!C:C,MATCH(日报日期[日期],项目日志!A:A,1)),ROW(A1)-1,0)," "))
第四部分是重点项目的图表趋势,先讲一下需求,我们想展示的是近两周的数据趋势,这里的近两周就意味着是动态的,那怎么解决动态的问题?这里我们还是需要用到日报日期表,近两周就是当前日期-1,-1,一直减到第14个1,所以直接根据当前日期再建一个趋势日期辅助表就可以了。最后一个单元格直接引用日报日期表,上面的单元格-1上拉。
有了趋势日期后,趋势图表需要的数据就简单了,同样用到合并查询功能来完成,并加载到超级表,进行图表制作,用柱形图、折线图组合就行,不过美化才是最重要的。
这样只要更新一个日期后,趋势日期就会变,趋势数据同样变化,图表就变成动态了。
接下来的部分就是投放数据和累计投放数据了,看了前面的案例方法,基本上应该知道怎么做了,这里就直接展示了。
需要注意的是,里面的图表,有一个辅助线,用来显示ROI 100%时的参考线,这个是需要研究一下的。
最后我们来重点讲讲投放时间轴
首先还是分析需求,我希望能清晰的展示每个项目每天在不同渠道的投放记录,来查看不同项目的投放节奏,上面这个图表,基本上还是比较清晰的,包含日期、游戏名、平台、渠道、投放媒体,以及小标记。做这样一个图表其实不难,但难点在于如何实现自动化,所以需要从源数据出发,看哪个源数据能够处理这样的需求。肯定是从投放数据入手,那在投放汇总表里,我们可以想到,只要当天花费达到0元以上,表示当天投放,否则就是没有投放,所以这里使用条件列就可以了,就是IF函数,然后把不同渠道的结果显示为1、2、3,加载成数据表是这样子的,这个表也就是上面时间轴的源数据,而且是可以自动更新的哦。
那这样一份漂亮的自动化报表就完成了,是不是感觉很神奇很强大,其实看完这些步骤,就知道是一步步的操作,一个个的知识点汇总而成的。我总结了一个这个报表的难点,大家可以再回顾一下。
不同的数据源标题字段不一样,需要统一
不同的游戏项目会有小包、别名的情况,需要分别展示和汇总展示
单个项目的数据分平台(iOS/Android)、渠道(小米/TAPTAP)、地区(中国/美国)
变现平台数据,需要把收入汇总,再把eCPM单独处理,合并到当日数据表中
海外的变现平台,收入为美元,需要自动转换成人民币
日报需要展示当日数据,以及月汇总的数据,根据日期获取月份并聚合
需要根据不同的分成比例,来计算不同项目不同渠道不同平台的财收、CP分成、毛利,可用做财务结算表
数据表数量情况,一共67个表格
游戏源数据(18)
广告源数据(3)
投放源数据(6)
广告数据计算(6)
汇总数据表(6)
财收表(3)
日报展示(5)
趋势分析(7)
参数表(1)
67个表的查询关系
总结
这份报表完成后,功能当然不仅仅是日报了,根据源数据、运算表,可以轻松的生成月报表、可供财务结算的财报表、汇总数据加载成数据透视表,再辅以日程表、切片器,可以用来做游戏数据查询、投放数据查询,是不是再也不用担心老板的复杂数据需求了?
这篇文章,也是我第一次分享自动化报表的制作过程,里面有着非常多的干货、知识点,我自己也曾体验过每天做日报表的痛苦,我学习Excel的初因其实也是因为要做一个非常复杂的日报,最后想说的是,做好一份报表,方法并不难学,重点是你需要知道方法,再加上产品逻辑,慢慢就水到渠成了,同时也希望这篇文章能够帮助到大家,让每个人的日报都做得棒棒的。
欢迎大家加我的微信公众号:幻想小课堂
或者加我微信:幻想 进行讨论交流。