google-bigquery - How to manipulate a query for 'Average Session Duration' to 'Average Session Duration by DAU' using Google Analytics in BigQuery?

Question

I currently have a query that returns 'Average Session Duration Across Platforms.' I need it to now return 'Average Session Duration by DAU Across Platforms.' The difference between the three tables below is iOS, Android and web data. user_id is a custom dimension we have in GA that I could use, but adding this alone is not going to get me the results I am looking for. I think I need to add the user_id custom dimension and do something with the date, to get unique users by date. However, I am new to using BigQuery and am unable to do this on my own. I realize this ask is vague - happy to update my question accordingly.

WITH
  base AS (
  SELECT
    *
  FROM (
    SELECT
      _TABLE_SUFFIX AS table_date,
      "web" AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*`
    UNION ALL
    SELECT
      _TABLE_SUFFIX AS table_date,
      "iOS" AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*`
    UNION ALL
    SELECT
      _TABLE_SUFFIX AS table_date,
      "Android" AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*` )
  WHERE
    NOT REGEXP_CONTAINS(table_date, "intraday|backup") )
SELECT
  FORMAT_DATE("%E4Y-%m-01", PARSE_DATE("%E4Y%m%d", MIN(table_date))) AS
ymd,
  AVG(COALESCE(totals.timeOnSite, totals.timeOnScreen, 0)) as timeOnSite
 FROM
  base
WHERE
  table_date > "20180101"
  GROUP BY 1
ORDER BY 1

标签： google-bigquery

Answer 1

我认为您需要在两个级别上进行聚合。这有帮助吗？

WITH base AS (
  SELECT
    *
  FROM (
    SELECT
      _TABLE_SUFFIX AS table_date,
      'web' AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*`
    UNION ALL
    SELECT
      _TABLE_SUFFIX AS table_date,
      'iOS' AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*`
    UNION ALL
    SELECT
      _TABLE_SUFFIX AS table_date,
      'Android' AS app_source,
      *
    FROM
      `XXXX.ga_sessions_*` 
  )
  WHERE NOT REGEXP_CONTAINS(table_date, 'intraday|backup') 
)

select 
   app_source,
   parse_date('%Y%m%d', table_date) as date,
   count(distinct user_id) as total_users,
   AVG(coalesce(totals.timeOnSite, totals.timeOnScreen, 0)) as average_time_on_site
from base
group by 1,2
order by 1,2