Device Usage in Canvas Data with Tableau

[brenden_goetz]

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!

Topic	Content
Tableau Dashboard	Screenshot: 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" ELSE "Unknown" END
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" ELSE "Other" END
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

[brenden_goetz]

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.

Cheers,

Brenden

[Jeff_F]

 @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.

[brenden_goetz]

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). 

Cheers!

[brenden_goetz]

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:

1. Tableau dashboard screenshot
2. Tableau workbook file (.twb) - anyone using Canvas Hosted Data and Tableau should be able to enter their credentials and the dashboard will populate with your data (you'll need to change the course_id in the custom sql query data source; you'll also want to change some language in the info/question mark hover-over tool tip).
3. Google Doc documenting our work

Topic	Content
Tableau Dashboard	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)

[sonya_corcoran]

Hey Brendan, 

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 sonya.corcoran@sydney.edu.au

Cheers

Sonya

[brenden_goetz]

Hi  @sonya_corcoran  -

Here are some estimations of our expenses (all in USD). There are obviously a lot of variables (what kind of deals you can work out, the amount of licenses, size of server, size of institution, etc), but hopefully this gives you an idea. Let me know if you have more questions. Also worth noting - we are on the old Tableau pricing model, but they recently changed their pricing model to subscription based.

Tableau Desktop

• $1200 per license (one-time) + $300/yr maintenance
• Two licenses for our team... each department at the University pays for their own right now.

Tableau Server

• $600 (one-time) +150/year maintenance per seat/user
• We started with 10 seats, then added 30 more for a total of 40... then moved to a Core License
• $245,000 for first year for 8 core license (one-time perpetual license), then $49k per year thereafter for maintenance

Canvas Hosted Data 

• In 2016 we paid about $14,500.  I think it is priced per FTE and how many years of stored data we have (2-3 for us).

Exasol Database Software

• Free small business edition (1 node and 200GB max)

Tableau Server Hardware/VM

• ~$5,000 for virtual machine

Exasol Server Hardware

• $14,000
• Dell PowerEdge R730 dedicated server

People!

• My salary
• Student employee (15hrs/week for 3-4 months for this particular project)

[sonya_corcoran]

Thanks so much for sharing  @bra2194094  - this is extremely helpful to move our workflow and decision making along.

Greatly appreciated.

[brenden_goetz]

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!

[stephen_woosley]

Thanks Brendan! Thanks again for sharing your findings and process with us. Looking forward to the video links being shared!