CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

hits.page.searchKeyword doesn't match Total Unique Searches

I'm playing around with BigQuery google_analytics_sample data.

I'm trying to retrieve the number of Total Unique Searches I'm seeing from the Google Analytics UI.

I'm running the following query:

SELECT
  hits.page.searchKeyword AS Search
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS GA,
  UNNEST(GA.hits) AS hits
WHERE
  (_TABLE_SUFFIX BETWEEN '20170101'
  AND '20171231')
and hits.page.searchKeyword IS NOT NULL

and I got 441 when the UI show 607 Total Unique Searches.

What do I'm missing?

Thanks.

Upvotes: 1

Views: 574

Answers (1)

rtenha
rtenha

Reputation: 3618

It looks like the linked table doesn't contain data for all dates in 2017.

SELECT
  max(_TABLE_SUFFIX) as max_suffix
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS GA
WHERE
  (_TABLE_SUFFIX BETWEEN '20170101'
  AND '20171231')

Try adjusting your date filters in the GA report.

Upvotes: 1

Related Questions