Preparing Survey Data for Analysis with Tableau Prep

okay well thank you so much for all of you that are here already for this preparing survey data webinar super fun to see a lot of familiar names old students and friends and fun to see some new names as well if this is your first time joining us for a webinar or you haven't had a class with me before extra welcome to you thanks for uh for joining us for the next half an hour we're looking forward to it so before we jump into some technical content i'm just going to give a quick background about who i am and what am i doing presenting to you today so if i can get powerpoint to agree with me maybe i can maybe i can't there we go all right uh so my name is eric i work with an organization called one number um i've been partnering with tableau and working with their product for five years now so across tableau tableau prep in the last year and alteryx for most of that time as well it probably taught around 2000 students how to prepare and visualize their data across a wide swath of industries um so those are the the main kind of challenges i focus on is helping people when they run into really complex problems and my job and my hope is to improve your life in some way so that could be improving your company increasing revenue decreasing costs might be helping make a process more efficient for you so that you spend less time doing manual repetitive things or going the long way around when there's maybe a faster way to solve the data problem so today's content is really around what the webinar is titled preparing survey data and it's a issue that i've run into across a lot of organizations what you'll typically see is there are a few issues when working with survey data we'll look at an example here in just a moment but how it usually works is right you have some sort of survey platform and eventually you either connect directly to a table of data or you export that data and then you get all your survey results and so what happens is and again i'm generalizing but each row of data is a single response to the survey and then each column is either a piece of demographic information about that respondent maybe when they took the survey or what their name is or their response id or it's a question so how satisfied were you with your purchase would you buy from us again would you recommend us to a friend any comments you have so what happens is because the data is structured like that with each question being its own column you run into issues with trying to visualize multiple questions at a time if you're just purely trying to visualize the responses as text that's maybe a little bit simpler but when you're getting into more complex types of survey response visuals having every question be its own column poses a bit of a challenge and another challenge to that is that usually with surveys you're going to have multiple response types what i mean by that is some questions might be a one through five some might be a 0 through 10 some might be true false some might be reform text responses so there's a lot of different types of responses you might have as well so what we're going to do to make it simple today because we're on a limited budget of time is we're just going to focus on one possible type of survey response visual and so it will be this this is called 100 stacked bar chart and it's overlaid with a dual axis showing an average response so you could see each bar represents a single question the sections of the bar represent what percent of respondents gave a one through five response to that question and then that little circle represents what the average response was okay so we're going to talk about how you get your survey data in such a format that you could build something like this and just as a quick background to that when your data is in oh i might have pulled up the wrong workbook here all right well so be it all right uh a little uglier than i hoped but you know what that happens so when your data is in a sort of traditional survey um uh how would i say this it's sort of a traditional way that a survey would spit it out it might look like this where your dimensions and your measures represent a bunch of questions and just to show you what this underlying data looks like it's basically this much like we talked about each row is a response after you get past the demographics each column is an individual question so when it's set up like this what you are able to do is you could visualize like a question at a time so this would be um what percent of respondents said they gave you a five on how your communication is rated versus a one but it's not easy to show for instance all four of these questions at once and this is what the survey data looks like after or the survey data after it's been spit out by tableau prep through the process we're going to talk about allows you to do what i'm hovering over just now so you might think well okay you know you can only look at one question at a time well what if we pivoted it in tableau desktop and depending on the way your survey data is structured that is a possible solution but only if each of your questions is the same question type so if they are all true false questions or they are all one through five questions what you could do is go back to your data source and then go ahead and select the headers shift select all those questions and just do a simple pivot okay so you end up with basically a question field and a response field now the issue with the data set i'm working with is that there are multiple response types so notice that response field called pivot field values right now it starts with some yes no questions but eventually you get far enough down and you'll see that there's some freeform text and then eventually there are some numeric responses as well but this field right now is recognized by tableau as a string field which means that it's text so you can't necessarily create an average or percent of total based on a string field so we could change this to be a number field but then our text responses like yes no or freeform responses are invalidated because tableau can't recognize those alphabetical characters as a numeric response so that's where you end up kind of running into a stopping point with tableau desktop and maybe you end up turning somewhere else so maybe you turn to sql or maybe you have some other third-party tool so what i'm going to do is show you okay let's take that same data and put it in tableau prep so that we can get it again in a format so we can do things like this all right so we're going to look at actually 10 steps to do that i am recording this webinar so if you want to come back through this later and re-watch any of this content or take screenshots you'll be able to do that so if any of these sections feel fast you can always revisit it at the same time if you have questions that come up feel free to toggle with the go to webinar menu and type your questions in i'm not going to answer questions until the end just because i'm bad at multitasking and i won't be able to look at questions while i'm also demonstrating but i'm happy to revisit any concepts or answer further questions as much as time allows when we're done so here's our 10 steps we're going to go ahead and go through these one by one and let me go ahead and do this i need to close this excel file so i can use it in tableau prep otherwise i'll hit an error so we'll go through these so number one load the data into tableau prep i've got prep already loaded because that sometimes can take a couple minutes just for the software to boot up so that's done i'm going to grab my excel file to prep and let it load okay so here's the response data you can see all of my different fields here looks like everything loaded the way that i would hope okay step number two create one branch or it's called this one clean tool in tableau prep that just houses demographic and filtering data so what i mean by filtering data is any question that you would want to filter your respondents by so if you have a question that says would you ever purchase from us again yes or no and you want to be able to filter on the nose and investigate those respondents in more detail you'd want to include that question in this branch so i'll go ahead and do that and i'm just going to give these steps really brief names so that i know what they are in real life do a better job annotating than i do it's okay response ids names city states i want all of that and i'm just going to leave all of my yes no questions as well i want those to be able to filter on and let's see if there's anything else i'd want to keep i think i'm going to remove all of my 0 through 10 1 through 5 and free form text questions at this step you don't have to but i'm going to do that here okay so that step is done okay next step is to create alternative branches for each question type so what that means is as you can see from this visual i'm going to create one branch for every type of question that i have in my survey responses and that's going to come directly from that initial first data input step so add a branch and i'll start with my yes no questions okay so really important when you do this step you want to make sure that you leave the response id as well because eventually we're going to need to tie this back to our initial demographic information and the response id is the way that you're going to sort of tie everything back together okay so i'm going to try and do this part similar quickly because it can be a little bit time consuming depending on how many different question types you have so sorry yes knows maybe next i'll do my 0 through 10 questions and again same process here okay if you ever end up making a mistake if you've used tableau prep a little bit you probably know how to undo a change but let's say that i accidentally actually removed a question there that i didn't need after all you can always come back to your changes pane and actually delete a change that you've made so that like all of those questions or in this case columns of data would come back okay got a couple of freeform text responses and in this case it's just filler text because this isn't real data i can't show you my client's data because and i'd probably get sued so i want that to happen okay i think i just have one more now which would be those one through five responses okay let me do a quick double check there yep that looks good okay so that step is done in real life that might take you a little bit longer as you're sorting through i set all that data up so that that could happen quickly okay now what i want to do is for each of those branches i just created i want to create a question type field so notice here basically what i'm going to be doing is creating a calculated field that just tells you what type of question is so and once i get to tableau desktop i could filter and say okay i just want to keep my one through five questions and i want to build this 100 stacked bar chart so that's actually going to be just in these same tools here create a calculated field question type this will be free form text and then you may not know this but when you're working in tableau prep something you can do is you can actually just copy a step from one part of your flow go to another part of your flow and paste that step in so i'm going to go ahead and edit this so instead of this being freeform text now i'm in my yes nose do that a couple more times here okay so we are good on that front step five so now that we've separated the different types of questions out out of this question type field we need to pivot each of the questions um or the columns of questions i should say in each of those branches so that we end up with two columns um what's going to be called question and response so back here i'm going to add a pivot to each of these steps other than the demographic step i don't need it there so in this case like okay uh let's see i'm gonna wind this out again a little bit so i'm gonna get these two questions i have and drop them both into the pivot values response id and question type can remain independent of that pivot same thing here select all those over there and a couple more times now oops right you can see a lot of this is just basically building out the same flow for each of your question types so there's a little bit of repetitive work as you go through this okay so now what i want to do is with those pivots i want to create a question field so the text like how would you rate our customer service like that becomes a question and the response is interesting because like we looked at in tableau desktop responses can be tricky when you pivot because we basically have two possible different response types we have just standard text questions and then we have responses which could either in this case be text or numeric so what i'm going to do is i'm going to go through each of these sections so like pivot 1 here so i'm going to double click where it says pivot 1 names and say yup this is my question field and then pivot 1 values this is a response but i want to indicate specifically that it's a text response so that later when i bring all this data back together the text responses and the numeric responses actually are their own unique columns so yes no same kind of thing here it's going to be question and once again this is going to be response text and it is important to try to make sure that these are the same select notice that this response text i put a space between the two and then this is actually an accident but it's a good teaching moment this one i didn't that's actually going to give us make provide a it's going to make us have to do a little bit more manual effort later in our union so better just to try and make those as similar as possible when you can early on um these didn't all get in there somehow okay so this one's going to be different now this is a response but it's actually going to be a number response right let me go ahead and just put a numeric symbol afterwards and then one more time over here as well okay cool i think the majority of the painstaking duplicated effort work is now done so now we're going to do is we're going to take all of those different branches with the pivoted data and we're going to go ahead and union them together so what this looks like i'll take one pivot hover over another until i see union throw that in there i want to add each of the other pivots as well so you want to hit union that's going to create a new union you say hey add this to the existing union so what this looks like is our sort of generic fields like question there should be a little color band showing that okay that shows up for every one of them something like response number only exists for a couple of those pivots and same thing with uh response text so that is what we were hoping for so that looks good okay we're going to join the question branches back to the demographic branch that we built at the very beginning and we're going to use response id to do that demographic branch join that over here okay looks like they already matched on response id you can always do a quick check at this point to make sure that all of your rows of data from both sections are included if not i guess that probably means that you lost some response ids along the way so you'll want to go back and double check your work that's looking good and now i need to do a little bit of cleaning and then output what i've built so what i mean by cleaning is i might have a few repetitive fields now so i'm going to add a step let's do a quick look through my fields one way you can find if you have duplicate fields is when you search here if you just put in the number one notice like i have a response id and i have a response id one that's because i joined my response ids together so this is a duplicate and that can just go away in this case and i think i do have one other that i don't need i think it's called table names and that's what is generated by default when i unioned all the different sections together but i kind of built my own table names field when i created question type and that's actually going to be more useful anyways so what i'm going to do is just say you know what i don't need table names okay so let me go ahead and output this click browse here i'll just send it to my desktop so we can knock this out quickly sueve output nope survey yeah okay everything looks good i should run my flow so just small sample data so shouldn't take very long and so now what i can do let's go ahead and load this up into tableau desktop and i'll just do a quick build of that visual i showed you at the beginning i don't want to spend a super long time on it because i'd rather have a few extra minutes for questions at the end then just sit here and talk about visualizing data when this is really more about data preparation but you know what it came all this way let's do it so here's what my data looks like if i'm looking at it now so what's happened is actually originally i had a thousand rows of data and what you're going to see now is that i have 13 000 rows of data so what we've done is we've taken the questions and pivoted it so that like a single respondent has 13 rows now okay so what i'm going to do is i'm going to do this i'm going to filter my question type field down to just the one through five questions okay i want to see what those questions are so i'll put a question on rows i want to know how many responses i had to each of these questions so i'm going to take response id to columns and get account distinct of response id a quick way of doing that right click and drag response id columns ask for that account distinct i set my fit to entire view so okay great i have a thousand responses to each of these questions now i want to see how many people gave this their answer as a one how many gave it as a five so i'm going to put my response number on color so right now it's just showing me like what's the sum of all the responses i guess so what i want to do is turn that response number into a discrete field and actually make it a dimension as well again okay so what i have now is people that are green gave me a five uh people that are blue gave me a one go ahead and change these colors a little bit so in this palette i want to rearrange this sort order there are kind of some more systematic ways of doing this or i can just do this the old school by hand way just drag my color legend in the order that i want it okay so here's the people that gave me ones here's the people that gave me five now what i'm going to do get my distinct count of response id drag a copy the label and i'm going to make this a percent of total okay let's just do a little bit here to clean this up and make sure it's formatted correctly okay so now i can see for instance out of the you know thousand people that said or rated our communication 19.5 percent gave us a one and i think our original question was to figure out um people that heard about us by radio what did they think or at least that was supposed to be our original question if i didn't mention it okay so people that heard about us by radio actually think our communication is worse than the average person sorry they think it's better they think our their overall satisfaction is worse you can see that's a pretty big section there 27.5 but they were happy with our communication i guess so the last bit to show that average circle on top of this is it's actually a dual axis chart so again i'm going to do this part quickly you can always come back and watch this again if you want to see how all this works so i'm going to go ahead and put my response number on columns once again and i'm going to change this one to be an average i'm going to go down to the second part of my marks card now and i'm going to remove my response number from color and remove this little percent thing from text and change my mark type to be a circle i'm going to make it bigger maybe even bigger i'm going to edit my axis down here so it ranges from zero to five okay a little bit of formatting at this point i'm going to go ahead and show my mark label so i can see what that number is center it change the formatting so there's one decimal okay change the colors this is a little bit more toned down edit my label oh i need to do it this way actually that field on label then edit do something like this okay so my average response is this number now what i want to do is to overlay these two on top of each other so i'm going to right click on my second pill and say give me a dual axis everything gets all jacked up for a second i'm going to set my original chart type back to a bar and my new chart types that circle i need to sort of size it correctly and uh it doesn't look as good in this case because the numbers are sort of overlapping each other i may hide this header so this may take a little bit of kind of configuring to sort this out exactly the way that i want it to look but in the big picture that is how you create that visual so all of this was made possible because i set the data up the way that i did in tableau prep there are certainly other ways depending on your data that you may need to prepare it but that's sort of a good general overview for your standard uh survey responses and how you could set it up and if i hadn't done it this way we either have to create each of these four sections as four separate worksheets or potentially do a bunch of extra wrangling in tableau desktop that might end up being painful and may or may not actually end up even working so that's mainly what i wanted to show you um we do have a couple of minutes left until 9 30. so i'd love to take some questions if anybody has any that pop up so i'm going to go ahead and just give a little bit of space now for those questions and while you are typing those out in the questions dialogue i always wanted to speak to like what do you do now well try this i'm happy to answer immediate questions and if you have some bigger picture questions or you want a little bit more hands-on help doing this with your own data feel free to reach out to me when i send the follow-up content i've got what are called office hours where you could just reserve an hour of time with me at a time and i'm always happy to spend some time working with you there so like i said i'm going to give you a little bit of time now to type out those questions if you have any all right i see a hymn um but i'm not seeing a question uh so let me see brian i'm going to try and unmute you to see if this works oh i see a question now nope okay i'm gonna unmute you here brian all right maybe it was an accidental hand raised but did you want to ask a question oh yes can you hear me yep can hear you okay well that's part of me currently we use a qualtric system i was gonna try to take that out but i had multiple wings open uh we use a qualtric system right now that houses our survey and these surveys actually go out to our clients based upon the settings or conditions that we've set there now i'm not the originator of it i kind of maintain it by just updating a few things to send it out to our clients but i'm not really sure i'm not i'm not i don't have a tableau strength at this time so i'm not really sure how we would transfer from all tricks to tableau prep or to present this kind of information so i might need some more technical help just to understand how that works are you still there sorry still here yeah i'm going to go ahead and use your brian while i give the responses because there's a little bit of feedback um so that's a great question which is yeah what happens if you maybe don't have total control over your end data and so i've worked with qualtrics a little bit it's not you know by any means exclusively what i've worked with what i'm going to guess is that there is either a way with qualtrics to download the data into maybe a csv or an excel file or potentially even to connect directly to the data table if you store it on site or there might be a cloud data table and tableau prep the way that it's set up now is that when you have a tableau desktop license you actually get the prep license with it that's kind of part of tableau's new monthly subscription service so for those of you that are like do i have tableau prep do i not would definitely recommend checking with your whoever oversees tableau at your organization to see if you can get a license key for tableau prep as well because it's very likely that it may be something you have already that you just don't even know that you have and i did just get a response which is that qualtrics has a new integration with tableau as an added cost good to know i haven't had a chance to look at that in a lot of detail yet but that might be worth looking into as well so i've got another question here i'm going to try and knock this out it says would love to know how to deal with fields that the user can multi-select like red yellow or green that are in the same field i have a number of fields like this and i also need to be able to filter on them it's a great question so sometimes the way that survey data is set up is such that um think about how to word this yeah like a response might have commas in it so let's say there's a question like which of our services have you used in the last month and you can multi-select and then the way that the answer is given is like okay let's just go to webinar as an example so i's used go to webinar comma go to meeting comma go to whatever else okay so you have three responses all separated by commas i won't be able to show an example just now but essentially what you would want to do with that is you would take that response split it based on the commas so that each of the responses there'd be like okay people that responded you know response one response two response three and you'd eventually take that data and pivot it that would add a little bit more complexity to the approach that i showed in this example because i didn't have any data in this example structured like that if i can find an example of that in online literature i'm happy to speak to that what i may actually do as well is just jot that down and try and do a future blog post about it because i think it's a great question and i do know that how the tableau prep class that tableau offers um does have some examples that go through that so maybe a chance to to practice that if that's something that you're interested in at all you could look toward that all right yeah so it's got a question will there be a way to view this presentation again at a later date share with my colleagues yeah so this is being recorded and i'll try and have it up as soon as possible and get an email out with the recording i'll hop the video up on vimeo as you know in the next couple of days so that you can come back and revisit this and to share it with others yeah okay well we ran a little bit over um i know some of you had to leave thanks for all the nice comments for those that are taking off and have to leave really appreciate it for those that are watching the recording if you're like how do i get in touch with these people here is my email address it's eric.parker at onenumber.biz so that's actually great for you that are here in person as well you're welcome to respond um ask questions give feedback there or again if you're watching on the recording you can reach me there so yeah thank you so much everybody for uh joining for this really appreciate you being here on a friday morning and look forward to staying in touch take care

Channel: OneNumber LLC

Views: 580

Rating: 5 out of 5

Keywords:

Id: ItXEFRW1RZc

Length: 33min 29sec (2009 seconds)

Published: Sat May 08 2021