Creating Interactive Longitudinal Dashboards of Survey Data Using
Creating Interactive, Longitudinal Dashboards of Survey Data Using Tableau Lauren M. Conoscenti, Ph. D. Steven F. Babbin, Ph. D. AIR Forum 2018, Orlando, FL May 30, 2018 Office of Institutional Research & Evaluation
Overview • Challenges of longitudinal data • Preparing data for Tableau • Dashboard demonstrations • Insights and lessons learned Office of Institutional Research & Evaluation
Challenges of longitudinal data Office of Institutional Research & Evaluation
Longitudinal data • Surveys are a key method of collecting data (especially at Tufts) • Our office logged almost 100 survey projects in AY 2016 -17! • Longitudinal survey data tell a story about changes over time • Student satisfaction from matriculation to graduation • Post-graduation outcomes across cohorts • Alumni giving before and after a campaign • Better understanding these changes can inform decision- making and drive improvement Office of Institutional Research & Evaluation
Longitudinal data • Tableau is a great tool for visualizing a single year of survey data • An improvement over static reporting (e. g. Excel, Word, PDF) • But what about for longitudinal projects? • Much trickier! Office of Institutional Research & Evaluation
Challenges: Creating longitudinal data sets • Historically, our longitudinal data sets were maintained erratically (or not at all)… and in a reactive fashion. • Longitudinal data requests were time consuming • Look up 2015 report, find data… look up 2014 report, find data… look up 2013 report, find data… etc. • Datasets created to fulfill certain requests or answer certain questions • Only the Geology department wants this specific data, so here’s data for only these questions, just for Geology! (…meanwhile, all other departments and all other items are ignored. ) • Did not easily permit breakouts by group • Oh, you want this broken out by gender? And now you want it broken out by school? And now you want it broken out by gender and school? Office of Institutional Research & Evaluation
Challenges: Using Tableau • Tableau is great, but it can be tough to learn and time consuming (especially for survey data) • Use Tableau to be proactive; a different kind of project than filling a specific request • We want our clients to be able to explore data, to answer questions they didn’t know they had • To do that, we need to make more choices and be more creative than the average project, while retaining possibilities for other analyses. • No way to “just throw it together” in a day or two. . . • …which is sometimes all the time we’re given to fill a request! Office of Institutional Research & Evaluation
Challenges: Using Tableau • Tableau is also very picky, especially when: • Questions or scales change over time • Individuals make independent decisions that make data merging challenging (e. g. formatting options, capitalization, etc. ) • New data sources or new software implemented • Suddenly carefully constructed Tableau calculated fields, sets, etc. that rely on certain kinds of data or variables become useless or inaccurate because: • Data that had been numeric is now a string • Data previously broken out into columns is now in a single column • Data that should be available for all respondents is now only available for some For example… Office of Institutional Research & Evaluation
The question is the same, but the punctuation is not… Office of Institutional Research & Evaluation
The choice options are essentially the same… but not really. “Agree Somewhat” and “Somewhat Agree” are not the same thing! (even if technically they are, in our heads…) Office of Institutional Research & Evaluation
Something changes in the data and Tableau yells at you… Office of Institutional Research & Evaluation
Challenges: Using Tableau • Even when your data are perfect, a screen is not endless • Longitudinal survey data can easily clutter up a great viz • Too many years of data • Too many items • Too many response options • Too many breakout groups • Too many colors! Office of Institutional Research & Evaluation
We’re gonna need a bigger screen… Office of Institutional Research & Evaluation
We’re gonna need a bigger screen… Office of Institutional Research & Evaluation
Challenges These problems are not unfamiliar or insurmountable… …But they do make the job more difficult and/or create work. So, how do we do this work? Office of Institutional Research & Evaluation
Preparing data for Tableau Office of Institutional Research & Evaluation
Quick note: Please note: there are many ways to do things! We presented just one possibility, but there are likely many other approaches. We’d love to hear how you have built your longitudinal files for Tableau! Office of Institutional Research & Evaluation
Overall strategies: Building Files • We assume you know the best practices in building Tableau-ready data files for surveys • If not: Read Tableau’s White Paper on visualizing survey data by Steve Wexler • If you need to reshape your data, we recommend checking out Qualtrics Tools! (Developed by a Tufts student!) https: //github. com/emmamorgan-tufts/Qualtrics. Tools/ • Contains an app to help with reporting, and R code for reshaping your data. Office of Institutional Research & Evaluation
Overall strategies: Building Files We need our longitudinal file to be reshaped and have demographic information, “meta data” or variable information, and the actual numeric and text response associated with each item. Image Credit: Tableau & Steve Wexler! Office of Institutional Research & Evaluation
Overall strategies: Building Files We need our longitudinal file to be reshaped and have demographic information, “meta data” or variable information, and the actual numeric and text response associated with each item. But also include each respondent’s year/survey iteration! Image Credit: Tableau & Steve Wexler! Office of Institutional Research & Evaluation
Overall strategies: Building Files • This is easy when your survey has stayed exactly the same over time. (Or, if there have been minimal coding changes, just go back and re-code – when you download data again, it’ll automatically use the new coding) Office of Institutional Research & Evaluation
Overall strategies: Building Files • Once all your survey files have consistent coding and have been reshaped, you can: • Merge together individual years’ of survey data into a single file or • Use Tableau unions to “stack” files (Don’t forget to include the survey year for each respondent in their demographics!) Office of Institutional Research & Evaluation
Overall strategies: Building Files • Tableau unions basically “add rows” of data. • Recommend that each year of data be in its own tab in an Excel file. • Can do some limited merging of columns that have different headers, but contain the same data (e. g. “Response ID” and “Response. ID”) • Each additional survey year, just create a new tab and union the new data. Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • But what if you have several years of surveys that aren’t consistent? (And you’re using Qualtrics? ) • No fear – shell surveys are here! Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Shell surveys help you create your framework for all items over time -- then import data to “merge” everything together • These will never be administered to respondents • Essentially building the headers for a giant spreadsheet and establishing the coding for each item • After building this framework, you can import data from your various surveys. • Will generate a large file with all the data from the various years – which you can then reshape Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Advantages of shell surveys: • Can contain all the items you need/want to analyze over the years • Can add items over time • Can address issues of inconsistent wording, options, etc. • Implement the coding scheme you want Yes, this is time-consuming. It’s worth it. (You only have to do it once, and then make updates in the future. ) Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Things to consider: • Have options been added/removed from matrix questions over time? If so, you’ll want your matrices to include all the questions spanning all the years. Asked only in 2014 and 2016 Last asked in 2015 Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Things to consider: • How different are questions from year to year? • If the question has changed meaning – due to the wording or the options – include all versions in the shell survey. Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics Can’t really compare a four-point scale with a five-point scale, so let’s include both questions. Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Things to consider: • What does checking a box in a check-all-that-apply question mean? You can “recode” these by creating a matrix so that your Tableau worksheet is clear about what the selection means. Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Next: • Change the coding and labeling for past years of survey data in Qualtrics and re-download data in a CSV OR • Change coding for past years of survey data in SPSS (Recode into Same Variables), export to CSV (When doing future surveys, keep variables and coding consistent with your Shell Survey so you don’t have to go back to re-code!) Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Get ready to import! (Specific directions for importing data can be found on the Qualtrics support site. ) • Use the headers generated by the shell survey as the headers for your individual-year data – the importing only works with consistent QIDs. In the “Data & Analysis” tab of your shell survey, select “Import Data…” Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Finally, re-download data from the Shell Survey and check it to make sure it worked. • Now you have a longitudinal file ready for reshaping! • And you’re ready to build some longitudinal dashboards! Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Keep in mind principles of good analysis • What does the client really want/need to know? • Sometimes ALL THE THINGS!!! isn’t the best approach. • Too much can overwhelm even the most data-literate client. • Which survey items lend themselves best to longitudinal visualization? • Some survey items are more elegant than others in Tableau. • Certain really-continuous-but-asked-as-a-categorical items are tricky • How often do you exercise? 1 -2 x/week, 3 -4 x/week, 5+/week • (Steve will show you an example) Office of Institutional Research & Evaluation
Overall strategies: Shell Surveys in Qualtrics • Use tooltips to your advantage • Especially now that you can embed vizzes in tooltips Office of Institutional Research & Evaluation
Overall strategies: Building Dashboards • Use parameters to create breakout groups • Which demographic or panel variables are of greatest importance? • Be careful of clutter! • Be mindful of small group sizes • Can hide small Ns using sets and/or calculated fields • Don’t use year as a singular filter! • If you can only look at one single year at a time, then what’s the point? • Enough talk: let’s get to the dashboards! Office of Institutional Research & Evaluation
Dashboard demonstrations Office of Institutional Research & Evaluation
Library Survey • Biannual library survey • Have done since 2000 • In 2016, clients wished to do more with their data • In 2018, clients wished to compare results for the first time! • Longitudinal analyses gave staff opportunity to evaluate how changes have impacted service use and satisfaction • Being able to break out by subject area, respondent group, and school was especially helpful. Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
Campus Problems • Data compiled from Senior Surveys • Client wanted to understand how different subgroups of students perceived campus issues over time • E. g. , “Sexual harassment is a campus problem” • Longitudinal analyses showed stark differences across groups over time • Data changed to reflect the rise of national topics across colleges Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
Veterinary students: Spay and Neuter Procedures • Data compiled from Cummings School of Veterinary Medicine exiting student surveys, 2014 -2017 • Clients wished to visually compare numbers of spay and neuter procedures by species, type of procedure, and location • Questions stayed the same across years and additional years of data were added to the dashboard for a longitudinal component… with mixed results Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
Engineering students: Study Abroad • Data compiled from undergraduate senior surveys, 2014 - 2016 • Clients wished to better understand why many engineering students did not study abroad • Longitudinal analysis highlighted the complexities of the issue Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
First-Year First-Week: Ice Cream • Work in progress, content re-labeled accordingly • Data available 2012 -2017, many questions ideal for visualization in Tableau • More data, more surprises • Layers of filtering and breakdowns reveal layers of trends Office of Institutional Research & Evaluation
Office of Institutional Research & Evaluation
Insights and lessons learned Office of Institutional Research & Evaluation
Insights from our clients • Library clients: • “Tremendously helpful” • Staff had not previously compared data over time • “Reassuring” to see expected changes (or lack of changes) • Engineering clients: • “I am thrilled with it” • “Very helpful to have this feedback, especially as it lends data to the anecdotes and affirms my directions” • Campus problems clients: • “It’s really amazing!” Office of Institutional Research & Evaluation
Lessons learned • Tableau is very helpful at highlighting trends in data that may otherwise be missed. • Being able to look at subgroups over time has helped our clients make better decisions. • Creating a tool that clients can use to answer their own questions is valuable. • Everyone loves to play with data! Office of Institutional Research & Evaluation
Lessons learned • Longitudinal dashboards in Tableau are more than just single-year dashboards with a new filter for year. • If you want to look at relationships over time, you need to see things together. • And not everything looks good in Tableau together! Be mindful of clutter. • Use all the Tableau tools you know to your advantage to keep things streamlined and simple. • Tooltips, parameters, sets, groups, calculated fields, etc. can help pack more information into your dashboard. Office of Institutional Research & Evaluation
Lessons learned • Crafting longitudinal data sets for Tableau requires a time investment • But once you get things set up, it’s easier to keep it going. • It may also require extra creativity and patience • If you’re stuck, someone has probably done what you’re trying to do – so reach out! • It’s worth it! Office of Institutional Research & Evaluation
Contact information Questions? Lauren. Conoscenti@tufts. edu Steven. Babbin@tufts. edu http: //go. tufts. edu/OIRE Office of Institutional Research & Evaluation
Thank you! Please remember to submit your evaluation for this session. Office of Institutional Research & Evaluation
- Slides: 55