CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
voyager
voyager

Reputation: 343

Counting app screen-views using Google Analytics BigQuery export

I'm trying to count the number of app screen-views for a particular screen using the Google Analytics BigQuery data export. My approach would be to count the number of hits with a screen-view hits.type. For instance, to count the number of page-views on the web version of our app I would count the number of hits with hits.type = 'PAGE'. but I can't see how to do this on app because there is no "SCREENVIEW" hits.type value.

This is the description of hits.type from Google (https://support.google.com/analytics/answer/3437719?hl=en):

The type of hit. One of: "PAGE", "TRANSACTION", "ITEM", "EVENT", "SOCIAL", "APPVIEW", "EXCEPTION".

Is there another way to do this that I'm missing?

I've tried using the totals.screenviews metric:

SELECT
  hits.appInfo.screenName,
  SUM(totals.screenviews) AS screenViews
FROM (TABLE_DATE_RANGE([tableid.ga_sessions_], TIMESTAMP('2018-01-12'), TIMESTAMP('2018-01-12') ))
GROUP BY
  hits.appInfo.screenName

But that returns numbers that are too high.

Upvotes: 2

Views: 3035

Answers (2)

Daniela Priscila Nunes Mendes
Daniela Priscila Nunes Mendes

Reputation: 11

The hit.type is ‘APPVIEW’, because it no counts events.

#standardSQL SELECT hit.appInfo.screenName name, count(hit.appInfo.screenName) view FROM project_id.dataset_id.ga_sessions_*, UNNEST(hits) hit WHERE type = 'APPVIEW' GROUP BY name)

Upvotes: 1

Willian Fuks
Willian Fuks

Reputation: 11787

Legacy SQL automatically unnest your data which explains why your SUM(totals.screenviews) ends up being much higher (basically this field gets duplicated).

I'd recommend solving this one in Standard SQL, it's much easier and faster. See if this works for you:

#standardSQL
SELECT
  name,
  SUM(views) views
FROM(
  SELECT 
    ARRAY(SELECT AS STRUCT appInfo.screenName name, COUNT(1) views FROM UNNEST(hits) WHERE type = 'APPVIEW' GROUP BY 1) data
  FROM `projectId.datasetId.ga_sessions_*`
  WHERE TRUE
    AND EXISTS(SELECT 1 FROM UNNEST(hits) WHERE type = 'APPVIEW')
    AND _TABLE_SUFFIX BETWEEN('20180112') AND ('20180112')
), UNNEST(data)
GROUP BY 1
ORDER BY 2 DESC

Upvotes: 2

Related Questions