Jon, you might have missed this version of the query from Joseph, which should be more direct and faster (pasted below). However, I'm not sure you can deduce much about the session length from this result. It seems to me you'd have to group by IPs and track referrers over time. However, that doesn't really tell you much as lots of people show up with the same IP, IPs change over time (pretty quickly), etc. From Oliver's work on sessions, I remember that it's not really possible to find sessions over any significant period of time because the data changed too much. Talking to Oliver about this might help. That said, the percent of unique IPs that might be clicking through the site once or twice, according to this data, does not seem too high. I'd believe it.
-- results for query below
Total MapReduce CPU Time Spent: *3 minutes 56 seconds 660 msec*
OK
hits mobile_site is_internal num_pvs
5246 b false 8818
2878 b true 9100
31 a true 118 31 a false 64
SELECT
COUNT(DISTINCT ip) AS hits,
x_analytics_map['mf-m'] AS mobile_site,
(referer_class = "internal") as is_internal,
count(*) AS num_pvs
FROM
wmf.webrequest
WHERE TRUE = TRUE
AND webrequest_source = 'mobile'
AND year = 2015
AND month = 5
AND day = 25
AND hour = 1
AND agent_type <> 'spider'
AND is_pageview = TRUESELECT COUNT(DISTINCT ip) AS hits, x_analytics_map['mf-m'] AS mobile_site, (referer_class = "internal") as is_internal, count(*) AS num_pvs FROM wmf.webrequest WHERE TRUE = TRUE AND webrequest_source = 'mobile' AND year = 2015 AND month = 5 AND day = 25 AND hour = 1 AND agent_type <> 'spider' AND is_pageview = TRUE AND x_analytics_map['mf-m'] IS NOT NULL AND access_method IN ('mobile app', 'mobile web') GROUP BY (referer_class = "internal"), x_analytics_map['mf-m'] ORDER BY hits DESC LIMIT 50;
On Wed, Jun 3, 2015 at 11:27 PM, Jon Katz jkatz@wikimedia.org wrote:
Hi, Just bumping this question. Any thoughts on why my query might be returning such a high number of internal on mobile web beta? Doesn't this go against the wisdom of average pv/session = ~1?
Apologies if someones responded and I for some reason didn't see... Thanks, J
On Fri, May 29, 2015 at 11:10 AM, Jon Katz jkatz@wikimedia.org wrote:
+External
Hi, I realized I don't get any responses from internal--but Joseph sent me something helpful to me this morning so I saw all the responses..up to that point. I think.
Anyway, thanks for the help!! The strange thing for me seems to be that the numbers I get don't make that much sense to me. For beta, (using query below) I get:
Unique IPs num_pvs referrer
3638 5967 external
1972 5760 internal
I would have expected a much larger external-->internal referrer ratio. In other words, I would have expected that the vast majority of sessions or even ips only hit the site 1x in a given hour. Instead, I am seeing that 54% of IPs are clicking a link within that hour... I would probably expect to see #'s no more than 10%.
I am probably doing something wrong, right? I *know* that I am making convenient assumptions here that do not apply to edge cases, so let's not consider those unless you think they make a big difference. Perhaps by using the referer field I am inherently leaving out all of the external traffic for which we do not have data?
Thanks!
-J
SELECT COUNT(DISTINCT ip) AS Unique_IPs, x_analytics_map['mf-m'] AS mobile_site, count(*) AS num_pvs, CASE WHEN referer LIKE "%en.m.wikipedia%" THEN 'internal' ELSE 'external' END AS session_depth FROM wmf.webrequest WHERE TRUE = TRUE AND webrequest_source = 'mobile' AND year = 2015 AND month = 5 AND day = 25 and hour = 1 AND agent_type = "user" AND is_pageview = TRUE AND x_analytics_map['mf-m'] IS NOT NULL AND uri_host like "%en.m.wikipedia.org%" GROUP BY CASE WHEN referer LIKE "%en.m.wikipedia%" THEN 'internal' ELSE 'external' END, x_analytics_map['mf-m'] ORDER BY hits DESC LIMIT 50;
On Thu, May 28, 2015 at 2:30 PM, Jon Katz jkatz@wikimedia.org wrote:
Hi, Trying to run a hive query to rough-count number of 1-page-only, 'sessions' on mobile-web Here is the error I get
FAILED: ParseException line 15:22 missing KW_END at 'device_family' near 'device_family' line 15:35 missing EOF at ''] <> "Spider"\n AND is_pageview = TRUE\n AND x_analytics_map['' near 'device_family
Here is the query:
SELECT COUNT(DISTINCT ip) AS hits, x_analytics_map['mf-m'] AS mobile_site, count(*) AS num_pvs, CASE WHEN referer LIKE "%en.m.wikipedia%" THEN 'internal' ELSE 'Misc’ END AS session_depth FROM wmf.webrequest WHERE YEAR = 2015 AND MONTH = 5 AND DAY = 25 AND user_agent_map['device_family'] <> "Spider" AND is_pageview = TRUE AND x_analytics_map['mf-m'] IS NOT NULL AND uri_host like "%en.m.wikipedia.org%" GROUP BY session_depth, mobile_site ORDER BY hits DESC LIMIT 50;
Any advice?
Thanks!
Jon
Analytics-internal mailing list Analytics-internal@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics-internal