Fix iOS vs. Android detection in mobile apps uniques tables
Closed, ResolvedPublic

Description

As we found earlier in the case of pageviews (cf. T148663#2764417 ), the iOS app user agent changed from containing iPhone to containing iOS back in September 2016. The pageviews code was updated, but not the Oozie jobs calculating daily and monthly uniques.

The issue is also affecting the Android numbers, because the queries assume that everything that is not iOS is Android:

CASE WHEN user_agent LIKE('%iPhone%') THEN 'iOS' ELSE 'Android' END AS platform

(which BTW seems somewhat questionable in itself, but that's for another day).

It looks like this bug is currently inflating Android daily uniques by up to 10% or more.

Tbayer created this task.Apr 19 2017, 11:55 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 19 2017, 11:55 PM

Change 349140 had a related patch set uploaded (by HaeB):
[analytics/refinery@master] Fix iOS app detection for monthly and daily uniques

https://gerrit.wikimedia.org/r/349140

Thanks for the patch Tilman !
Just made a quick check (see below), and so far we are fine with the current settings (Android as other), but I agree, we should use an explicit definition instead of an implicit one.
With this though, question becomes, what do we do with others?

SELECT
    year,
    month,
    day,
    user_agent LIKE '%iOS%' as ios,
    user_agent LIKE '%iPhone%' as iphone,
    user_agent LIKE '%Android%' as android,
    count(1) as c
FROM wmf.webrequest
WHERE user_agent LIKE('WikipediaApp%')
    AND ((parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'action') = 'mobileview' AND uri_path == '/w/api.php')
        OR (uri_path LIKE '/api/rest_v1%' AND uri_query == ''))
    AND COALESCE(x_analytics_map['wmfuuid'],
                 parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'appInstallID')) IS NOT NULL
    AND webrequest_source IN ('text')
    AND year=2017
    AND month=4
    AND day=19
GROUP BY year,
    month,
    day,
    user_agent LIKE '%iOS%',
    user_agent LIKE '%iPhone%',
    user_agent LIKE '%Android%'
ORDER BY c DESC
LIMIT 100;



year  month day ios iphone android count
2017  4 19  false false true  14683943
2017  4 19  true  false false 4957512
2017  4 19  false true  false 380654

Change 349140 merged by Joal:
[analytics/refinery@master] Fix iOS app detection for monthly and daily uniques

https://gerrit.wikimedia.org/r/349140

JAllemandou moved this task from Next Up to Ready to Deploy on the Analytics-Kanban board.
Nuria added a subscriber: Nuria.Apr 21 2017, 6:48 PM

I think that for these jobs to be updated timely updates should be own by apps teams.

Nuria closed this task as Resolved.Apr 21 2017, 6:48 PM