title: Accessing Struct(s) and Array(s) in Firebase Closed Funnels through BigQuery
body: <p>I stumbled unto this standard SQL BigQuery <a href="https://i.stack.imgur.com/4nAvz.png" rel="nofollow noreferrer">documentation</a> this week, which got me started with a Firebase Analytics Closed Funnel. I however got the wrong results (view image below). There should be no users that had a "Tutorial_LessonCompleted" before they did not start a "Tutorial_LessonStarted >> Lesson = 1 " first. This could be because of various reasons. </p>
  
  <p><strong>Questions:</strong> </p>
  
  <ol>
  <li>Is it wise to use the User Property = "first_open_time", or is it better to use the Event = "first_open". How would the latter implementation look like ? </li>
  <li>I suspect I am perhaps not correctly drilling down to: Event (String = "Tutorial_LessonStarted") >> parameter (String = "LessonNumber") >> value (String = "lesson1")? </li>
  <li><p>How would a filter on _TABLE_SUFFIX = '20170701' possibly work, I read this will be cheaper. Any optimised code suggestions are received with open arms and an up-vote!</p>
  
  <pre><code>#standardSQL
  SELECT
    step1, step2, step3, step4, step5, step6,
    COUNT(*) AS funnel_count,
    COUNT(DISTINCT user_id) AS users 
  FROM (
  SELECT
      user_dim.app_info.app_instance_id AS user_id,
      event.timestamp_micros AS event_timestamp,
      event.name AS step1,
      LEAD(event.name, 1) OVER (
        PARTITION BY user_dim.app_info.app_instance_id 
        ORDER BY event.timestamp_micros ASC) as step2,
      LEAD(event.name, 2) OVER (
        PARTITION BY user_dim.app_info.app_instance_id 
        ORDER BY event.timestamp_micros ASC) as step3,
      LEAD(event.name, 3) OVER (
        PARTITION BY user_dim.app_info.app_instance_id 
        ORDER BY event.timestamp_micros ASC) as step4,
      LEAD(event.name, 4) OVER (
        PARTITION BY user_dim.app_info.app_instance_id 
        ORDER BY event.timestamp_micros ASC) as step5,
      LEAD(event.name, 5) OVER (
        PARTITION BY user_dim.app_info.app_instance_id 
        ORDER BY event.timestamp_micros ASC) as step6
  FROM
      `......`, 
      UNNEST(event_dim) AS event, 
      UNNEST(user_dim.user_properties) AS user_prop  
  WHERE user_prop.key = "first_open_time"
  ORDER BY 1, 2, 3, 4, 5 ASC
  )
  WHERE step6 = "Tutorial_LessonStarted" AND EXISTS (
  SELECT * 
  FROM `......`, 
  UNNEST(event_dim) AS event,
  UNNEST(event.params)
  WHERE key = 'LessonNumber' AND value.string_value = "lesson1") GROUP BY step1, step2, step3, step4, step5, step6
  ORDER BY funnel_count DESC
  LIMIT 100;
  </code></pre></li>
  </ol>
  
  <p><strong>Note:</strong> </p>
  
  <ol>
  <li>Enter your query table FROM, i.e:<code>project_id.com_game_example_IOS.app_events_20170212</code>,</li>
  <li>I left out the funnel_count and user_count. </li>
  </ol>
  
  <p><strong>Output:</strong> </p>
  
  <p><a href="https://i.stack.imgur.com/C8RxC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/C8RxC.png" alt="Output"></a></p>
  
  <h2>----------------------------------------------------------</h2>
  
  <p>Update since original question above:</p>
  
  <p>@Elliot: I don’t understand why you said:  -- ensure that an event with lesson1 precedes Tutorial_LessonStarted. </p>
  
  <p>Tutorial_LessonStarted has a parameter "LessonNumber" with values lesson1,lesson2,lesson3,lesson4. </p>
  
  <p>I want to count all funnels that took place with a last step in the funnel equal to LessonNumber=lesson1. </p>
  
  <p>So, applied to event log-data for a brand new user's first session (aka: an user that fired first_open_time), the answer would be the table below: </p>
  
  <ul>
  <li>View.OnboardingWelcomePage </li>
  <li>View.OnboardingFinalPage </li>
  <li><strong>View.JamLoading</strong></li>
  <li><strong>View.JamLoading</strong> </li>
  <li><strong>Jam.UserViewsJam</strong> </li>
  <li><strong>Jam.ProjectOpened</strong> </li>
  <li><strong>View.JamMixer</strong></li>
  <li><strong>Tutorial.LessonStarted (This parameter “LessonNumber"'s value would be equal to “lesson1”)</strong> </li>
  <li>Jam.ProjectPlayStarted </li>
  <li>View.JamLoopSelector</li>
  <li>View.JamMixer </li>
  <li>View.JamLoopSelector </li>
  <li>View.JamMixer </li>
  <li>View.JamLoopSelector</li>
  <li>View.JamMixer </li>
  <li>Tutorial.LessonCompleted </li>
  <li>Tutorial.LessonStarted (This parameter “LessonNumber"'s value would be equal to “lesson2”)</li>
  </ul>
  
  <p>So it is important to firstly get all the users that had a <strong>first_open_time</strong> on a specific day, as well structure the events into a funnel so that the last event in the funnel is one which matches an event and a specific parameter value, and then form the funnel "backwards" from there. </p>
  
  <p><a href="https://i.stack.imgur.com/4nAvz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4nAvz.png" alt="enter image description here"></a></p>