Mobile user login count

[melewis]

I'm trying to calculate the average number of times a student logs into Canvas, on an iPad, by program and term. I've been searching Canvas Data that's imported in our data warehouse to see if this exists.

In pseudonym_dim you have last_login_at, current_login_at. In pseudonym_fact you have login_count with the total login count per user. I can't find this information at the grain of individual login instances which should allow you to aggregate them by program and device. Does anyone know if this information is currently being tracked in Canvas Data?

[bneporadny]

Hi  @melewis ,

Few things here.

Unfortunately with mobile Canvas app once you're logged into Canvas you're logged in and are not forced to log in each time you open the app to access Canvas like you can be made to do when accessing Canvas via the web. 

You can however use the requests table‌ and look at the user_agent column to be able to see how/when and what students are viewing using the Canvas mobile app. 

Hope this helps. 

[melewis]

Thanks  @bneporadny !

What about the web user logins? I see in the "requests" table that the column "url" has entries of '/?login_success=1'. Is this the only "requests" row that indicates a login?

And I do see a few of these rows have a user agent for iPads such as "Mozilla/5.0 (iPad; CPU OS 12_1_1 like Mac OS X)...". Am I right to assume these rows would be for iPad browsers instead of the Canvas app?

[dtod]

Just posting a query that I had to run recently and seemed useful. It calculates mobile device and app usage as a percentage of all hits per user. It enabled me to graph and get a sense of how many users are "mobile only".

select p.unique_name, (r.mobile_count* 1.0/ total)*100 as mobileratio, (r.app_count* 1.0/ total)*100 as appratio
from (
    select 
        user_id,
        count(CASE WHEN lower(user_agent) like '%candroid%'
						or lower(user_agent) like '%canvas%'
						or lower(user_agent) like '%teacher%'
					THEN 1
					END) as app_count,
        count(CASE WHEN (lower(user_agent) like '%android%'
						or lower(user_agent) like '%ios%'
						or lower(user_agent) like '%iphone%')
						and not (lower(user_agent) like '%candroid%'
						or lower(user_agent) like '%canvas%'
						or lower(user_agent) like '%teacher%'
						)
					THEN 1
					END) as mobile_count,
		count(*) as total
    from requests 
    where timestamp_month = '2021-06'
    group by 1
    ) r
join pseudonym_dim p on p.user_id = r.user_id
;

[IanGoh]

Is this still up-to-date? I noticed there's now iCanvas (e.g., "iCanvas/6.8.6 (12669) iPhone/iOS 16.6.1") in the user-agent strings.  Can we just get away with looking at iCanvas and candroid for the apps?