How to select google analytics segment in google big query? SQL

Question

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

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

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

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

Answer 1

• In order to see all fields that are exported there, you can look at BigQuery GA Schema 

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

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

• This field is defined at the session-level.