First, there's something wrong with the searched field as it is true in every case:
SELECT IF(event_searched = 0, 'no', 'yes') AS searched, COUNT(DISTINCT event_app_install_id, event_session_token) AS sessions FROM MobileWikiAppLanguageSettings_18113720;
And unfortunately I missed it during dev/QA for T190931. Looking at the debug info in Logcat, event_searched is indeed set to true even when not searching for a language.
If we try to join events in LanguageSettings with events in LanguageSearching using the app install ID and session tokens as keys, we should get something, but:
SELECT * FROM MobileWikiAppLanguageSettings_18113720 AS settings INNER JOIN MobileWikiAppLanguageSearching_18113721 AS searches ON ( settings.event_app_install_id = searches.event_app_install_id AND settings.event_session_token = searches.event_session_token );
unfortunately returns nothing :( In dev logs, session_token field works as expected, with the same token in LanguageSearching events as in LanguageSettings events.
This makes me suspect that there's a sampling issue that we couldn't see in development because sampling is turned off, so in production there's something funky going on. Both funnels use SAMPLE_LOG_100 so theoretically if a user's ID selects them for one funnel, they should be selected for the other too since the sampling is ID-based. Yet, when we compare which IDs can be found in LanguageSettings vs LanguageSearching, we get the following:
SELECT settings.app_install_id IS NOT NULL AS `ID in LanguageSettings`, searches.app_install_id IS NOT NULL AS `ID in LanguageSearching`, COUNT(*) AS `IDs` FROM ( SELECT DISTINCT event_app_install_id AS app_install_id FROM MobileWikiAppLanguageSettings_18113720 ) AS settings LEFT JOIN -- replace with RIGHT JOIN to get the remaining 9 IDs that are in searches but not settings (SELECT DISTINCT event_app_install_id AS app_install_id FROM MobileWikiAppLanguageSearching_18113721) AS searches ON settings.app_install_id = searches.app_install_id GROUP BY `ID in LanguageSettings`, `ID in LanguageSearching`;
|ID in LanguageSettings||ID in LanguageSearching||IDs|
So of the 952 unique IDs (query below), 687 (72%) have records in both LanguageSettings and LanguageSearching (although no shared session tokens as shown above), 256 (27%) have records in LanguageSettings but not in LanguageSearching (can't verify if this is what it should be since searched in LanguageSettings is broken), and 9 (1%) have records in LanguageSearching but not LanguageSettings.
SELECT COUNT(DISTINCT id) AS n_ids FROM ( SELECT DISTINCT event_app_install_id AS id FROM MobileWikiAppLanguageSettings_18113720 UNION SELECT DISTINCT event_app_install_id AS id FROM MobileWikiAppLanguageSearching_18113721 ) AS lang_search_set;