Counting app screen-views using Google Analytics BigQuery export

2
votes

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.

2
google-analyticsgoogle-bigquery
Can someone please explain why this would have been down-voted? I'm trying my best here and getting a downvote without any feedback won't help me improve on my question-asking. – voyager
Not sure why it was downvoted, maybe because there was no example of what had been tried so far. Still, now the question seems reasonable to me – Willian Fuks

2 Answers

2
votes

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
1
votes

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)