如何在谷歌大查询中选择谷歌分析细分？ SQL

I create Google Analytics data source in Tableau. The data source has the segment by "new user".

我在Tableau中创建Google Analytics数据源。数据源具有“新用户”段。

Now, I would like to push the Google Analytics in Google Bigquery and create the same data source in Tableau by creating a data source from Google Bigquery.

现在，我想在Google Bigquery中推送Google Analytics，并通过从Google Bigquery创建数据源在Tableau中创建相同的数据源。

After checking the GA data source in Google Bigquery project. There is no segment in Bigquery.

检查Google Bigquery项目中的GA数据源后。 Bigquery中没有细分。

How to query by segment "new user" in Google Bigquery??

如何在Google Bigquery中按细分“新用户”进行查询？

1 个解决方案

#1

You can look at BigQuery GA Schema to see all fields that are exported there.

您可以查看BigQuery GA Schema以查看在那里导出的所有字段。

The field totals.newVisits has what you are looking for:

字段totals.newVisits有你想要的：

select
hits.transaction.transactionid tid,
date,
totals.pageviews pageviews,
hits.item.itemquantity item_qtd,
hits.transaction.transactionrevenue / 1e6 rvn,
totals.bounces bounces,
fullvisitorid fv,
visitid v,
totals.timeonsite tos,
totals.newVisits new_visit
FROM
    `project_id.dataset_id.ga_sessions*`,
    unnest(hits) hits
  WHERE
    1 = 1
    AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-05-10')
    AND TIMESTAMP('2017-05-10')
group by 
tid, date, pageviews, item_qtd, rvn, bounces, fv, v, tos, new_visit

Notice that this field is defined in the session level.

请注意，此字段在会话级别中定义。

#1