title: calculating cohort data in firebase -> bigquery, but I want to separate them by tracking source. Grouping wont work?
body: <p>I'm trying to calculate quality of users with cohort data in bigquery
  My current query is:</p>
  
  <pre><code>WITH analytics_data AS (
      SELECT user_pseudo_id, event_timestamp, event_name, app_info.id,geo.country as country,platform  ,app_info.id as bundle_id,
        UNIX_MICROS(TIMESTAMP("2019-12-05 00:00:00")) AS start_day,
        3600*1000*1000*24 AS one_day_micros
      FROM `table.events_*`
      WHERE _table_suffix BETWEEN "20191205" AND "20191218"
    )
    SELECT day_7_cohort / day_0_cohort AS seven_day_conversion FROM (
        WITH day_7_users AS (
          SELECT DISTINCT user_pseudo_id
          FROM analytics_data
          WHERE event_name = 'watched_20_ads' AND event_timestamp BETWEEN start_day AND start_day+(12*one_day_micros) 
        ), day_0_users AS (
        SELECT DISTINCT user_pseudo_id
        FROM analytics_data
        WHERE event_name = "first_open"
          AND bundle_id = "com.bundle.id" 
          AND country = "United States"
          AND platform = "ANDROID"
          AND event_timestamp BETWEEN start_day AND start_day+(1*one_day_micros)
      )
      SELECT 
        (SELECT count(*) 
         FROM day_0_users) AS day_0_cohort,(SELECT count(*) 
         FROM day_7_users 
         JOIN day_0_users USING (user_pseudo_id)) AS day_7_cohort
  )
  </code></pre>
  
  <p>the problem is that I'm unable to separate the users by tracking source.
  I want to separate the users by: tracking source and country.</p>
  
  <p>What I'm curently getting:
  <a href="https://i.stack.imgur.com/ZtEIf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZtEIf.png" alt="more "></a>
  what I would like to see:</p>
  
  <p><a href="https://i.stack.imgur.com/1sjKz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1sjKz.png" alt="enter image description here"></a></p>
  
  <p>What would be perfect:</p>
  
  <p><a href="https://i.stack.imgur.com/TuhMn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TuhMn.png" alt="enter image description here"></a></p>
  
  <p>I'm not sure if it's possible to write a query that would return the data in a single table, without involving more queries and data storage elsewhere.</p>