+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 <[email protected]> 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 mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/analytics
