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(a)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(a)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(a)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(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/analytics-internal