sql_generators/funnels/configs/android_onboarding.toml

destination_dataset = "fenix_derived"
platform = "fenix"
owners = ["loines@mozilla.org"]  # optional; users getting notification if funnel run fails
version = "1"  # optional; default is set to 1

[funnels]

[funnels.onboarding_funnel]

friendly_name = "Firefox for Android Onboarding Funnel"
description = "Funnel Steps for Firefox for Android Onboarding"
steps = [
        "new_profile",
        "first_card_impression",
        "first_card_primary_click",
        "first_card_secondary_click",
        "second_card_impression",
        "second_card_primary_click",
        "second_card_secondary_click",
        "third_card_impression",
        "third_card_primary_click",
        "third_card_secondary_click",
         "onboarding_completed",
         "sync_sign_in",
         "default_browser"
         ]

dimensions = [
              "funnel_id",
              "country",
              "locale",
              "android_version",
              "channel",
              "device_model",
              "device_manufacturer",
              "first_seen_date",
              "adjust_network",
              "adjust_campaign",
              "adjust_creative",
              "adjust_ad_group",
              "install_source",
              "repeat_first_month_user",
              "retained_week_4"
              ]
[steps.new_profile]
friendly_name = "New Profile"
description = "Total New Profiles"
data_source = "onboarding_events"
# join_previous_step_on = "ac.client_id"
select_expression = "ac.client_id"
aggregation = "count distinct"

[steps.first_card_impression]
friendly_name = "First Card"
description = "First Onboarding Card Impression"
data_source = "onboarding_events"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
    JSON_VALUE(event_extra.action) = 'impression' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'primary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '1' AND
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'secondary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.client_id END"""
aggregation = "count distinct"

[steps.second_card_impression]
friendly_name = "Second Card"
description = "Second Onboarding Card Impression"
data_source = "onboarding_events"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '2' AND
    JSON_VALUE(event_extra.action) = 'impression' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'primary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'secondary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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
    JSON_VALUE(event_extra.action) = 'impression' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'primary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.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"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN JSON_VALUE(event_extra.sequence_position) = '3' AND
    JSON_VALUE(event_extra.action) = 'click' AND
    JSON_VALUE(event_extra.element_type) = 'secondary_button' AND
    event_name != 'completed' AND
    event_category = 'onboarding' THEN ac.client_id END"""
aggregation = "count distinct"

[steps.onboarding_completed]
friendly_name = "Onboarding Completed"
description = "Onboarding Completed Event"
data_source = "onboarding_events"
# join_previous_step_on = "ac.client_id"
select_expression = """CASE WHEN
    event_name = 'completed' AND
    event_category = 'onboarding' THEN ac.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 ('sign_in', 'sign_up') AND
    event_category = 'sync_auth'
THEN ac.client_id END"""
aggregation = "count distinct"

[steps.default_browser]
friendly_name = "Made Fenix Default Browser"
description = "User changed their default browser to Firefox"
data_source = "onboarding_events"
select_expression = """CASE WHEN event_name = 'default_browser_changed' AND
    event_category = 'events'
THEN ac.client_id END"""
aggregation = "count distinct"

[data_sources]

[data_sources.onboarding_events]
from_expression = """
  fenix.firefox_android_clients ac
  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 fenix.events_stream eu WHERE DATE(submission_timestamp) = @submission_date) AS eu
  USING(client_id)
  LEFT JOIN
    (SELECT client_id,
    JSON_VALUE(ANY_VALUE(event_extra.sequence_id)) AS funnel_id
    FROM fenix.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 = "ac.submission_date"
client_id_column = "ac.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 = "ac.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_android_clients
[dimensions.country]
data_source = "onboarding_events"
select_expression = "ac.first_reported_country"
friendly_name = "Country"
description = "Client's First Reported Country"
client_id_column = "ac.client_id"

[dimensions.locale]
data_source = "onboarding_events"
select_expression = "ac.locale"
friendly_name = "Locale"
description = "Client's First Reported Language"
client_id_column = "ac.client_id"

[dimensions.android_version]
data_source = "onboarding_events"
select_expression = "ac.os_version"
friendly_name = "First Reported Android OS Version"
client_id_column = "ac.client_id"

[dimensions.channel]
data_source = "onboarding_events"
select_expression = "ac.channel"
friendly_name = "First Reported Release Channel"
client_id_column = "ac.client_id"

[dimensions.device_model]
data_source = "onboarding_events"
select_expression = "ac.device_model"
friendly_name = "First Reported Device Model"
client_id_column = "ac.client_id"

[dimensions.device_manufacturer]
data_source = "onboarding_events"
select_expression = "ac.device_manufacturer"
friendly_name = "First Reported Device Manufacturer"
client_id_column = "ac.client_id"

[dimensions.first_seen_date]
data_source = "onboarding_events"
select_expression = "ac.first_seen_date"
friendly_name = "First Seen Date"
description = "First day this client_id shows up in our data."
client_id_column = "ac.client_id"

[dimensions.adjust_network]
data_source = "onboarding_events"
select_expression = "ac.adjust_network"
friendly_name = "Adjust Network"
client_id_column = "ac.client_id"

[dimensions.adjust_campaign]
data_source = "onboarding_events"
select_expression = "ac.adjust_campaign"
friendly_name = "Adjust Campaign"
client_id_column = "ac.client_id"

[dimensions.adjust_creative]
data_source = "onboarding_events"
select_expression = "ac.adjust_creative"
friendly_name = "Adjust Creative"
client_id_column = "ac.client_id"

[dimensions.adjust_ad_group]
data_source = "onboarding_events"
select_expression = "ac.adjust_ad_group"
friendly_name = "Adjust Ad Group"
client_id_column = "ac.client_id"

[dimensions.install_source]
data_source = "onboarding_events"
select_expression = "ac.install_source"
friendly_name = "Install Source"
client_id_column = "ac.client_id"