初学者必看的2个BQ案例

作为一名分析师,GA是我日常使用频率较高的工具之一。GA会将识别用户到转化的整个流程分为四大阶段:受众特征 – 来源 – 互动行为 – 转化。

通过GA中的报告,就可以对来访用户进行系统分析。但抽样一直是令许多分析师头疼的问题,即便可以采用导出非抽样报告的方式解决,也依然会存在无法使用非抽样报告的场景:

如用户流或漏斗等针对于用户行为进行分析。

这种情况下,面对抽样数据怎么办?

BigQuery或许可以有所帮助。对于BigQuery相信很多伙伴已经很清楚了,如果还有不太清楚的,可以查看往期文章:BQ数据可视化

用上BQ问题是不是就解决了?

还没有,因为存在一个问题:

BQ与GA之间存在着一些字段区别,无法在BQ中直接拉取GA的维度与指标,如会话数,跳出率等,并没有直观的指标供选择,需要使用一定的逻辑去还原。

那么下面我就根据自己平时的经验列举2个小案例,帮助初始BQ的朋友了解如何使用BQ获取GA数据。

一.访问多日数据的方法

首先要了解:

①对于每个支持BigQuery集成的Google Analytics(分析)数据视图,系统都会添加一个数据集,并且会以该数据视图的ID作为数据集的名称。

②且在每个数据集中,系统会为每天导出的数据分别导入一个表,表命名格式为:ga_sessions_YYYYMMDD,当想对某一天数据进行某字段数据查询时,from后的表格书写方式便可使用上述格式。

如:可直接通过 ’ga_sessions_日期’ 格式进行某一天数据获取。

但只分析一天的数据必然不够,那如何对多日的数据进行查询呢?

1 . 查询2020/8/10 – 2020/8/16一周的数据

查询在两个固定日期范围的数据时,可使用_TABLE_SUFFIX函数与BETWEEN…AND…

格式:SELECT … 

  FROM `项目.数据集.ga_sessions_*`

  WHERE  _TABLE_SUFFIX  BETWEEN 起始日期 AND 终止日期

注:此时要将查询表格格式改为ga_sessions_*

2 . 查询过去7天的数据

在这个案例中,首先要掌握两个函数:

  • FORMAT_DATE:

表示将日期字符串调整为指定日期格式。FORMAT_DATE(格式,日期字符串)

如:将字符串格式 “2020-02-03” 转为日期格式。

那格式元素都有哪些?下面给大家推荐几个常用格式元素:

*更多格式说明可查看:

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=zh_cn

  • DATE_SUB:

指从date中减去指定的日期间隔。DATE_SUB(日期,INTERVAL n DAY/YEAR…)

如:动态获取今天的前一天  DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)

则查询过去七天的用户数,会话数,跳出率等指标可写为:

二.如何在BQ中获取重新定义渠道后的数据

当客户(其他部门)对于流量的分类有着自己独特的定义,或流量报告中存在过多的(other)项,我们会在GA中优先使用自定义渠道分组功能,但是随之而来就会有新的问题:GA中自定义渠道并不能同步至BQ中。

这种情况下,在BQ中其实可以使用case when语句进行渠道的改写。

下面就主要根据一个渠道的重新定义,分享下BQ渠道改写方式。

  • Case When:用于当满足什么条件时,返回结果值

Case When语法:

  • CONCAT函数:将一个或多个串联成一个结果。另如参数存在NULL,函数返回结果为NULL

CONCAT(Source,” ”,”/”,” ”,Medium) → Source / Medium

如:当source为Google,Bing且Medium为cpc时,渠道定义为Search;当Source为Youtube,Medium为cpv或cpm时,渠道定义为Video…

Step1: 取出默认渠道及来源媒介对应的会话数

Step2:使用case when将默认来源媒介进行重新定义

REGEXP_CONTAINS(字段名,正则表达式):表示字段正则表达式包含

Step3:经前面重定义后,结果渠道显示为Search,Video,Exclude三种形式

注:可根据实际业务或投放渠道进行自定义规则改写

介绍就到此结束啦~这两个案例其实对于初学者是很有用的,所以一定要进行测试!

后续也会为各位小伙伴们介绍一些Bigquery的其他小知识或案例,也欢迎留言,共同交流!

(添加客服,可加入交流群)

备注:昵称-地区-公司-职位

GA使用、数据学习、行业交流、干货分享……

点击此处添加图片说明文字

发表评论

电子邮件地址不会被公开。 必填项已用*标注