content: destination_dataset = "firefox_ios_derived"
  platform = "firefox_ios"
  owners = ["rzhao@mozilla.org"]  # optional; users getting notification if funnel run fails
  version = "1"  # optional; default is set to 1
  
  [funnels]
  
  [funnels.ios_onboarding_funnel]
  
  friendly_name = "Firefox for iOS Onboarding Funnel"
  description = "Funnel Steps for Firefox for iOS Onboarding"
  steps = [
          "new_profile",
          "first_card_impression",
          "first_card_primary_click",
          "first_card_secondary_click",
          "first_card_close_click",
          "first_card_multiple_choice_click",
          "second_card_impression",
          "second_card_primary_click",
          "second_card_secondary_click",
          "second_card_close_click",
          "second_card_multiple_choice_click",
          "third_card_impression",
          "third_card_primary_click",
          "third_card_secondary_click",
          "third_card_close_click",
          "third_card_multiple_choice_click",
          "fourth_card_impression",
          "fourth_card_primary_click",
          "fourth_card_secondary_click",
          "fourth_card_close_click",
          "fourth_card_multiple_choice_click",
          "fifth_card_impression",
          "fifth_card_primary_click",
          "fifth_card_secondary_click",
          "fifth_card_close_click",
          "fifth_card_multiple_choice_click",
           "sync_sign_in",
           "allow_notification"
           ]
  
  dimensions = [
                "funnel_id",
                "repeat_first_month_user",
                "retained_week_4",
                "country",
                "ios_version",
                "channel",
                "device_model",
                "device_manufacturer",
                "first_seen_date",
                "adjust_network",
                "adjust_campaign",
                "adjust_creative",
                "adjust_ad_group"
                ]
  
  [steps.new_profile]
  friendly_name = "New Profile"
  description = "Total New Profiles"
  data_source = "onboarding_events"
  select_expression = "ic.client_id"
  aggregation = "count distinct"
  
  
  [steps.first_card_impression]
  friendly_name = "First Card"
  description = "First Onboarding Card Impression"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
  event_name = 'card_view' AND
  event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.first_card_primary_click]
  friendly_name = "First Card Primary Click"
  description = "First Onboarding Card Primary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
      event_name = 'primary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.first_card_secondary_click]
  friendly_name = "First Card Secondary Click"
  description = "First Onboarding Card Secondary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
      event_name = 'secondary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.first_card_close_click]
  friendly_name = "First Card Close Click"
  description = "First Onboarding Card Close Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
      event_name = 'close_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.first_card_multiple_choice_click]
  friendly_name = "First Card Multiple Choice Click"
  description = "First Onboarding Card Multiple Choice Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
      event_name = 'multiple_choice_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.second_card_impression]
  friendly_name = "Second Card"
  description = "Second Onboarding Card Impression"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
  event_name = 'card_view' AND
  event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.second_card_primary_click]
  friendly_name = "Second Card Primary Click"
  description = "Second Onboarding Card Primary Button Click"
  data_source = "onboarding_events"
  # join_previous_step_on = "ac.client_id"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
      event_name = 'primary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.second_card_secondary_click]
  friendly_name = "Second Card secondary Click"
  description = "Second Onboarding Card secondary Button Click"
  data_source = "onboarding_events"
  # join_previous_step_on = "ac.client_id"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
      event_name = 'secondary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.second_card_close_click]
  friendly_name = "Second Card Close Click"
  description = "Second Onboarding Card Close Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
      event_name = 'close_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.second_card_multiple_choice_click]
  friendly_name = "Second Card Multiple Choice Click"
  description = "Second Onboarding Card Multiple Choice Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
      event_name = 'multiple_choice_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.third_card_impression]
  friendly_name = "Third Card"
  description = "Third Onboarding Card Impression"
  data_source = "onboarding_events"
  # join_previous_step_on = "ac.client_id"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
  event_name = 'card_view' AND
  event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.third_card_primary_click]
  friendly_name = "Third Card Primary Click"
  description = "Third Onboarding Card Primary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
      event_name = 'primary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.third_card_secondary_click]
  friendly_name = "Third Card secondary Click"
  description = "Third Onboarding Card secondary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
      event_name = 'secondary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.third_card_close_click]
  friendly_name = "Third Card Close Click"
  description = "Third Onboarding Card Close Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
      event_name = 'close_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.third_card_multiple_choice_click]
  friendly_name = "Third Card Multiple Choice Click"
  description = "Third Onboarding Card Multiple Choice Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
      event_name = 'multiple_choice_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fourth_card_impression]
  friendly_name = "fourth Card"
  description = "fourth Onboarding Card Impression"
  data_source = "onboarding_events"
  # join_previous_step_on = "ac.client_id"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '4' AND
  event_name = 'card_view' AND
  event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fourth_card_primary_click]
  friendly_name = "fourth Card Primary Click"
  description = "fourth Onboarding Card Primary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '4' AND
      event_name = 'primary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fourth_card_secondary_click]
  friendly_name = "fourth Card secondary Click"
  description = "fourth Onboarding Card secondary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '4' AND
      event_name = 'secondary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fourth_card_close_click]
  friendly_name = "fourth Card Close Click"
  description = "fourth Onboarding Card Close Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '4' AND
      event_name = 'close_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fourth_card_multiple_choice_click]
  friendly_name = "Fourth Card Multiple Choice Click"
  description = "Fourth Onboarding Card Multiple Choice Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '4' AND
      event_name = 'multiple_choice_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fifth_card_impression]
  friendly_name = "fifth Card"
  description = "fifth Onboarding Card Impression"
  data_source = "onboarding_events"
  # join_previous_step_on = "ac.client_id"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '5' AND
  event_name = 'card_view' AND
  event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fifth_card_primary_click]
  friendly_name = "fifth Card Primary Click"
  description = "fifth Onboarding Card Primary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '5' AND
      event_name = 'primary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fifth_card_secondary_click]
  friendly_name = "fifth Card secondary Click"
  description = "fifth Onboarding Card secondary Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '5' AND
      event_name = 'secondary_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fifth_card_close_click]
  friendly_name = "fifth Card Close Click"
  description = "fifth Onboarding Card Close Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '5' AND
      event_name = 'close_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.fifth_card_multiple_choice_click]
  friendly_name = "Fifth Card Multiple Choice Click"
  description = "Fifth Onboarding Card Multiple Choice Button Click"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '5' AND
      event_name = 'multiple_choice_button_tap' AND
      event_category = 'onboarding' THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.sync_sign_in]
  friendly_name = "Signed into Sync"
  description = "User Successfully Signed into FxA (Signed in OR up)"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN event_name IN ('login_completed_view', 'registration_completed_view') AND
      event_category = 'sync'
  THEN ic.client_id END"""
  aggregation = "count distinct"
  
  [steps.allow_notification]
  friendly_name = "Allowed Notification"
  description = "User Allowed Notification during Onboarding"
  data_source = "onboarding_events"
  select_expression = """CASE WHEN event_category = 'onboarding' AND event_name = 'notification_permission_prompt'
  AND JSON_VALUE(event_extra.granted) = 'true'
  THEN ic.client_id END"""
  aggregation = "count distinct"
  
  
  [data_sources]
  
  [data_sources.onboarding_events]
  from_expression = """
    firefox_ios.firefox_ios_clients ic --each client_id has only one row
    LEFT JOIN (
      SELECT * FROM `moz-fx-data-shared-prod`.firefox_ios.retention_clients
      WHERE submission_date = @submission_date
        AND metric_date = DATE_SUB(@submission_date, INTERVAL 27 DAY)
        AND new_profile_metric_date
    ) AS r -- we only new_profile retention
    USING(client_id)
    LEFT JOIN
      (SELECT * FROM firefox_ios.events_stream eu WHERE DATE(submission_timestamp) = @submission_date) eu
    USING(client_id)
    LEFT JOIN
      (SELECT client_id,
      ANY_VALUE(JSON_VALUE(event_extra.sequence_id)) AS funnel_id,
      1 + LENGTH(ANY_VALUE(JSON_VALUE(event_extra.sequence_id))) -
               LENGTH(REPLACE(ANY_VALUE(JSON_VALUE(event_extra.sequence_id)), '_', '')) AS number_of_onboarding_cards
      FROM firefox_ios.events_stream
      WHERE JSON_VALUE(event_extra.sequence_id) IS NOT NULL
      AND DATE(submission_timestamp) = @submission_date
      GROUP BY 1) funnel_ids
    USING(client_id)
  
  """
  submission_date_column = "DATE(ic.submission_timestamp)"
  client_id_column = "ic.client_id"
  
  
  [dimensions]
  
  [dimensions.funnel_id]
  data_source = "onboarding_events"
  select_expression = "COALESCE(funnel_id, 'no_onboarding_reported')"
  friendly_name = "Funnel ID"
  description = "ID of the Onboarding Funnel"
  client_id_column = "ic.client_id"
  
  # these dimensions are sourced from `retention_clients`
  [dimensions.repeat_first_month_user]
  friendly_name = "Repeat First Month User"
  description = "Whether the Client is a Repeat First Month User"
  data_source = "onboarding_events"
  select_expression = "COALESCE(r.repeat_profile, FALSE)"
  client_id_column = "r.client_id"
  
  [dimensions.retained_week_4]
  friendly_name = "Repeat First Month User"
  description = "Whether the Client is Retained in their Fourth Week"
  data_source = "onboarding_events"
  select_expression = "COALESCE(r.retained_week_4, FALSE)"
  client_id_column = "r.client_id"
  
  # these dimensions are sourced from firefox_ios_clients
  [dimensions.country]
  data_source = "onboarding_events"
  select_expression = "ic.first_reported_country"
  friendly_name = "Country"
  description = "Client's First Reported Country"
  client_id_column = "ic.client_id"
  
  [dimensions.ios_version]
  data_source = "onboarding_events"
  select_expression = "ic.os_version"
  friendly_name = "First Reported iOS OS Version"
  client_id_column = "ic.client_id"
  
  [dimensions.channel]
  data_source = "onboarding_events"
  select_expression = "ic.channel"
  friendly_name = "First Reported Release Channel"
  client_id_column = "ic.client_id"
  
  [dimensions.device_model]
  data_source = "onboarding_events"
  select_expression = "ic.device_model"
  friendly_name = "First Reported Device Model"
  client_id_column = "ic.client_id"
  
  [dimensions.device_manufacturer]
  data_source = "onboarding_events"
  select_expression = "ic.device_manufacturer"
  friendly_name = "First Reported Device Manufacturer"
  client_id_column = "ic.client_id"
  
  [dimensions.first_seen_date]
  data_source = "onboarding_events"
  select_expression = "ic.first_seen_date"
  friendly_name = "First Seen Date"
  description = "First day this client_id shows up in our data."
  client_id_column = "ic.client_id"
  
  [dimensions.adjust_network]
  data_source = "onboarding_events"
  select_expression = "ic.adjust_network"
  friendly_name = "Adjust Network"
  client_id_column = "ic.client_id"
  
  [dimensions.adjust_campaign]
  data_source = "onboarding_events"
  select_expression = "ic.adjust_campaign"
  friendly_name = "Adjust Campaign"
  client_id_column = "ic.client_id"
  
  [dimensions.adjust_creative]
  data_source = "onboarding_events"
  select_expression = "ic.adjust_creative"
  friendly_name = "Adjust Creative"
  client_id_column = "ic.client_id"
  
  [dimensions.adjust_ad_group]
  data_source = "onboarding_events"
  select_expression = "ic.adjust_ad_group"
  friendly_name = "Adjust Ad Group"
  client_id_column = "ic.client_id"
  
  