[ARCHIVED] 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.

[Jeff_F]

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  

[Alexandre_Sch]

Hey Jeff! 

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.

Regards,
Alexandre.

[Jeff_F]

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.

[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!

[brenden_goetz]

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!