+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
>
>
>
>