As a reminder, there seemed to have been an impression issue on the Android app in December. To look further into this, it would be helpful to get updated impression numbers from Dec 2018 vs 2017 in order for us to be able to make a YoY comparison?
What we need:
Let me know if you have any questions.
Thank you!
Some of it is probably (hopefully) sanitized by now (install IDs scrubbed) but we might still have some anonymized impression data in MySQL.
@mpopov Just to clarify, this the the discussion we've been having over email since December, email SL "Re: Low Android performance (app fundraising)" . Please let me know if you have any questions. Thanks!
Assigning to @mpopov, who is balancing this with work on Android Notifications, SEO Analysis, Search metric migration, and Android Navigation analysis. @mpopov will document queries for @Jksamra to use in the future if needed.
To summarize from the email thread: @mpopov already provided the 2018 numbers for Android and iOS. This revealed that Android had fewer impressions than iOS. @jrobell, you would like to see the same numbers from 2017 for comparison purposes. Correct?
Hi, @jrobell! On Android: 40711 impressions in Dec 2017, 35882 impressions in Dec 2018 – a decrease of 11.8%
For reference, the number of active unique users on Android in December was 5.3M in 2017 and 4.7M in 2018 – a decrease of 11.7% (Hive query: SELECT year, unique_count FROM wmf.mobile_apps_uniques_monthly WHERE year IN(2017, 2018) AND month = 12 AND platform = 'Android')
Unfortunately I could not find any data for iOS from 2017. I think Feed analytics in iOS was added sometime summer 2018. For @Jksamra's future reference, the query I used with the EventLogging data in MariaDB is:
SELECT COUNT(1) AS n_impressions FROM ( SELECT `timestamp` FROM MobileWikiAppFeed_17836918 WHERE `timestamp` >= '20181201' AND `timestamp` < '20190101' AND INSTR(userAgent, '"os_family": "Android"') > 0 AND INSTR(userAgent, '"wmf_app_version": "2.') > 0 AND event_cardType = 15 AND event_action = 'cardShown' UNION ALL SELECT `timestamp` FROM MobileWikiAppFeed_18115458 WHERE `timestamp` >= '20181201' AND `timestamp` < '20190101' AND INSTR(userAgent, '"os_family": "Android"') > 0 AND INSTR(userAgent, '"wmf_app_version": "2.') > 0 AND event_cardType = 15 AND event_action = 'cardShown' UNION ALL SELECT `timestamp` FROM MobileWikiAppFeed_16432467 WHERE `timestamp` >= '20181201' AND `timestamp` < '20190101' AND INSTR(userAgent, '"os_family": "Android"') > 0 AND INSTR(userAgent, '"wmf_app_version": "2.') > 0 AND event_cardType = 15 AND event_action = 'cardShown' ) AS combined_impressions;
for 2018
and
SELECT COUNT(1) AS n_impressions FROM MobileWikiAppFeed_16432467 WHERE `timestamp` >= '20171201' AND `timestamp` < '20180101' AND INSTR(userAgent, '"os_family": "Android"') > 0 AND INSTR(userAgent, '"wmf_app_version": "2.') > 0 AND event_cardType = 15 AND event_action = 'cardShown';
for 2017