Brenden Goetz

Device Usage in Canvas Data with Tableau

Discussion created by Brenden Goetz on Nov 2, 2016
Latest reply on Sep 29, 2017 by 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!

 

TopicContent
Tableau Dashboard

      Screenshot:

Screen Shot 2016-11-02 at 9.44.55 AM.png

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

Attachments

Outcomes