firefox-ios-inactive-tabs.toml

[experiment]

segments = ["has_10_plus_tabs"]

[segments.has_10_plus_tabs]
select_expression = 'MAX(max_tabs) >= 10'
data_source = "daily_max_tabs"

## Data Sources
[segments.data_sources.daily_max_tabs]
from_expression = """(
    SELECT
        date as submission_date,
        client_id,
        MAX(average_tabs) AS max_tabs
    FROM (
        SELECT
            date,
            seq,
            c.client_id,
            1.0 * MAX(tabs_cumulative_count) / COUNT(1) AS average_tabs
        FROM
            (
                SELECT
                    DATE(submission_timestamp) as date,
                    client_info.client_id,
                    ping_info.seq,
                    ping_info.parsed_start_time AS start_time,
                    ping_info.parsed_end_time AS end_time,
                    metrics.counter.tabs_cumulative_count
                FROM
                    `mozdata.firefox_ios.metrics`
                WHERE
                    DATE(submission_timestamp) BETWEEN DATE_SUB("{{experiment.start_date_str}}", INTERVAL 15 DAY) AND "{{experiment.last_enrollment_date_str}}"
                    AND normalized_channel = 'release'
            ) c
        LEFT JOIN (
                SELECT
                    client_info.client_id,
                    ping_info
                FROM
                    `mozdata.firefox_ios.baseline`
                WHERE
                    DATE(submission_timestamp) BETWEEN DATE_SUB("{{experiment.start_date_str}}", INTERVAL 15 DAY) AND "{{experiment.last_enrollment_date_str}}"
                    AND ping_info.reason='inactive'
                    AND normalized_channel = 'release'
            ) b
        ON
            c.client_id = b.client_id
            AND b.ping_info.parsed_start_time BETWEEN c.start_time AND c.end_time
            AND b.ping_info.parsed_end_time BETWEEN c.start_time AND c.end_time
            GROUP BY 1, 2, 3
    )
    GROUP BY 1, 2
)"""
window_start = -14
window_end = 0