Just wanted to share some of the work we have done regarding device usage in light of the Using Canvas Data with Canvas Mobile Apps event. We are using the Canvas Hosted Data service in Amazon Redshift, and primarily visualizing the data in Tableau. I attached the workbook below - if you use Redshift, simply enter your credentials and the workbook will (hopefully) pull in your data. Happy to answer any questions that rise, and hopefully get some feedback. (The user_agent field is messy and there is a lot of account for). Thanks!
File is attached; view is also available via Tableau Public
Tableau Calculated Field:
Categorize User_Agent as "mobile" or "desktop"
IF CONTAINS(LOWER([User Agent]), 'ipad') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'iphone') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'intel mac') THEN "Desktop"
ELSEIF CONTAINS(LOWER([User Agent]), 'windows nt') THEN "Desktop"
ELSEIF CONTAINS(LOWER([User Agent]), 'android') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'canvaskit') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/9') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/8') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/7') THEN "Mobile"
ELSEIF CONTAINS(LOWER([User Agent]), 'icanvas') THEN "Mobile"
Tableau Calculated Field:
Categorize type of device by User_Agent
IF CONTAINS(LOWER([User Agent]), 'ipad') THEN "iPad"
ELSEIF CONTAINS(LOWER([User Agent]), 'iphone') THEN "iPhone"
ELSEIF CONTAINS(LOWER([User Agent]), 'intel mac') THEN "Mac"
ELSEIF CONTAINS(LOWER([User Agent]), 'windows nt') THEN "PC"
ELSEIF CONTAINS(LOWER([User Agent]), 'android') THEN "Android Device"
ELSEIF CONTAINS(LOWER([User Agent]), 'canvaskit') THEN "Unknown iOS"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/9') THEN "Unknown iOS"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/8') THEN "Unknown iOS"
ELSEIF CONTAINS(LOWER([User Agent]), 'ios/7') THEN "Unknown iOS"
ELSEIF CONTAINS(LOWER([User Agent]), 'icanvas') THEN "Misc Mobile (Canvas App)"
ELSEIF CONTAINS(LOWER([User Agent]), 'x11') THEN "X11 Device"
Custom SQL Query for Tableau Data Source
(Should be customized to suit your needs at the Requests Table is huge)
select r.user_agent, count(*) as "CountRecords"
from requests r
left join enrollment_dim e
on r.course_id = e.course_id
where r.timestamp_day between '2016-10-15' and '2016-10-21'
and e.type = 'StudentEnrollment'
group by r.user_agent
Hi @george_markaria -
The custom SQL is up there - scroll down to the "Custom SQL Query for Tableau Data Source" section. Is that what you are looking for?
In this Device Usage dashboard, we used the [requests] and [enrollment_dim] tables.
I am happy to hop on a web call to walk through things if you are still having trouble. Feel free to PM me to set up a time.
@brenden_goetz this is great! Thank you for sharing. I was able to connect our Redshift data to the workbook and then produced a derivative dashboard. I am curious if you produced a later version? What were you creating with the tab APIs, etc.? And I see a few Dimensions that appear yet to be used: API Urls, Remote Ip, etc.
And/ or if there are additional workbooks that you are able to share? For me, I am just getting started with data and I am finding benefit in breaking down completed workbooks as they are helping me learn.
Hey @Jeff_F -
Glad you found the workbook helpful, and were able to build on it! I haven't really updated this particular one, but I have been chipping away during the past few months on getting into more detail with device usage - specifically, trying to understand what types of devices students are using in different course content areas (e.g. Quizzes, Discussions, etc). It's really just an extension of this workbook, but has required a fair bit of work deciphering the some of the fields in the requests table. It should be ready to share within the next few weeks.
As far as some of the unused Dimensions... I was playing around with calculated fields in Tableau to try to identify URLs that use the Canvas API. Canvas uses its own API for a lot of things, and so sometimes a single click by a user may generate multiple http requests. In terms of identifying what a user is doing, I have been ruling out the request URLs tied of the APIs (for the most part).
Update on our work exploring device usage in Canvas...
We dug a little deeper to see if device usage varied within content areas, and when students were "consuming" vs. participating. Because the requests table is so large, we have only been looking at it one course at a time. I am sharing a few things here:
File attached to post. Screenshot below.
|Documentation||We attempted to document our exploration in a Google Doc - Canvas Activity Observations. Take a look, and comment in the doc or on this post. Our hope is that it becomes a collaborative exploration to enrich the community understanding of Canvas Data (and the requests table in particular).|
There are a variety of calculated fields in the workbook, as well as various assumptions. We have tried to document as much as we can, but surely there will be questions/comments/critiques - let us know what you have to say! And I am happy to try to get this working for folks using tools besides Hosted Data and Tableau.
(Tagging @Jeff_F to make sure you see this)
Great talk at InstructureCon - I got a lot out of it. Would it be possible to share the costings of your endeavours. You did mention the Redshift costs etc. but it was very hard to hear as the room was so jam packed.
Happy to chat via email email@example.com
I wish to add that there are a few approaches to this and a Tableau Server/ Server Hardware/ Exasol Server Hardware may not be a requirement for the institution. We are not downloading the data but rather accessing via Redshift and this seems to be working well for our needs. In addition to our Redshift agreement (Hosted Data), we have 3 Tableau Desktop and a few dozen Tableau Online licenses. Pricing | Tableau Software
How are you?
I have a question: Are you guys using the request table on redshift? I saw on @brenden_goetz presentation that redshift are not working very well with the request table, so they went to exasol to have a better approach to it. On the presentation, they said something about querying a 2b rows in 1-2 minutes.
Thanks for all the information, this post is helping a lot to investigate the better approach to a more robust infraestructure.
Hello @Alexandre_Sch - sure thing, I've used aspects of the requests table from time to time. When reviewing the research question I am presented I make a determination if the data is within a single course/ set of courses/ etc. and apply filters. I will also exclude all the fields that are not necessary for answering the question and I have also applied a range of dates (e.g. 3-5 months) as a filter. If we are looking for a snapshot of OS / Browser/ mobile usage this just required a sample of the population.
And if I wanted it to be super fast I could also use the Tableau Prep application and create an export of the data set.
I didn't pursue this further as all the questions we had at the time were answered (I had other pressing needs). Also, for some reason I am recalling there being a proposed update to the requests table so that there would be new roll up tables created using some of these fields that would be regularly updated.
But what are your research questions? Perhaps you have something super awesome in mind that requires such investment.
I received a few request for slides from our InstructureCon 2017 presentation, so here they are (attached)! Video of the presentation will also be published sometime within the next few weeks hopefully.
Next steps for us - based on some great questions and feedback from the conference session - are to dig deeper into seeing how mobile app usage shows up in the requests table.
If you have any insights, questions, or feedback let me know!
Just wanted to let you know that the recording from our InstructureCon 2017 presentation is up: Intelligence for Successful Spy Operations: Canvas-Hosted Data and Tableau. It goes into more detail about our work exploring device usage in different content areas. Take a gander, and let us know if you have any questions!