This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Announcing Alteryx + Snowflake | Alteryx and Snowflake make analytics and data science fundamentally easier. With the new integrated starter kit, you can push down data prep transformations and more into Snowflake for faster data quality and analytics output. Learn More
Indiana University has seven campuses distributed throughout the state as well as 135 online degrees and certificates. Together the campuses enroll approximately 95 to 100 thousand students each semester. The IU Office of Online Education conducts strategic marketing campaigns to recruit and admit new students to online programs. Calculating marketing yield means connecting student “request for information” data from our Salesforce CRM to the university’s Student Information System to determine if a prospect has applied, was admitted, and eventually enrolled. With Alteryx, IU Online’s first Alteryx workflow reduced prospect-to-applicant analytics process from 5 hours to 5 minutes, improved our confidence in the yield results, and started some great conversations about the future of marketing analytics across the university.
Describe the business challenge or problem you needed to solve
In addition to our regular student analytics that calculate and summarize enrollment, retention, and graduation rates, our director needs good data regarding the prospect to applicant funnel. The Office of Online Education spends a lot of resources marketing our degree programs and bringing potential students to our websites. The fundamental question is, of the students who reach out to us for information about our degrees, how many actually apply to the university, and in turn are admitted, and then enroll?
Before Alteryx, our only method for doing this was to download all of our Salesforce data into spreadsheets and our Student Applicant data into spreadsheets. From the Request for Information (RFI), the only common fields we have available to try to match on are student name and zip code. Before Alteryx, all of our data cleaning, removing duplicates, and joining was done in Excel.
Describe your working solution
Our data comes from all over the place. Students goes to various websites and landing pages either organically or through marketing campaigns fill out RFIs forms – these forms get submitted and captured in Salesforce, our CRM. On the other side of the equation, we have applicants to the university, who can come from a variety of application types, including the standard IU application, the common app, or our new IU Online app. These applicants go into our Student Information System (SIS) and can be accessed through a data warehouse. To find yield, we have to connect the RFI data to student applicant data.
The RFI data we deal with is student entered and contains lots of typos and bogus data. We have to remove duplicated contact numbers from students who can fill out multiple forms and use multiple email addresses. Students can also submit multiple applications at multiple campuses, all of which can be at various points in the application funnel. We use logic to try to match the first time a student contacts us with the “best” application we can find submitted by that student.
Our marketing yield solution now looks like this – we bring the RFI data from Salesforce and the SIS student applicant data together in Alteryx, do all of our cleaning, remove duplicates, blending, and summarizing, and then push the results to a Tableau dashboard. What used to take 5 hours to produce numbers based on a small subset of applicants can now be done with all of our available data in about 5 minutes. The one small glitch we still have is that Alteryx is so new to the university that it has not yet been vetted by our IT folks. Therefore, we aren’t allowed to connect Alteryx directly to Salesforce yet, so we make a quick pass through Denodo, which has been approved by the university, to get there.
This is a snip from our tableau dashboard, which takes about a million rows of data and summarizes those yield numbers into one view. And our director is happy!
This is a snapshot of the workflow that we use for marketing yield:
The gray box is our RFI information. That's where we gather all the data together, we do some cleaning, some blending, some un-duplicating and then we create that unique identifier, the first name, last name, zip code.
The gray box below is our applicant information. We do our data cleaning, bring it together, create that unique identifier there of first name, last name, zip code, that we are then going to join with the yellow box.
The yellow box is where all the magic of this huge workflow That's our big join. So that's where we take all the RFI data and all the applicants information data and put it together.
All of the pink boxes are our summary boxes. So that's where we get all of our counts for all of this informatio
The green box is our output data.
Stop-Out Workflow: Design Walkthroughs
This workflow was designed to meet a specific data request from our marketing team to generate a list of students who were enrolled in an undergraduate online program any time between 2015 and 2017, but stopped out without completing their degree. What’s great about this workflow is that it allows me to break down a complex problem into logical pieces, rather than trying to construct a complex, nested, 200 or 300 line query. I use containers to isolate the logical pieces, and even color code them here. The teal boxes are checking against data sources, and the yellow boxes do filtering.
The first tool in the workflow is our Input tool, it has our SQL query in it that gathers the population that we need. We first check to see if the student actually did graduate at any point in time after they had attended between those two years. Then, we check against current enrollment. Also, we have a death checker. So a student that hasn't come back, it's possible that they didn't get their degree, they haven't re-enrolled, they haven't re-applied. But we actually had a student in the current term who had passed away. The last thing we would want to do is allow a marketing person to send an email to that student and have their family check that email address.
Standardizing Standard Reports
These workflows are actually all embedded in one single Alteryx workflow. We have some regular Monday morning reporting to complete for admissions, student services, our bookstores, and even some outside vendors we have contracts with. Each of these individual data pulls are fairly simple, but it was time consuming to run each query, extract the results into Excel, save the file, and then email or securely transfer the file to the appropriate person. By placing these all into a single workflow, all I have to do is click run once, and then just grab the resulting spreadsheets to send where they need to go. What used to take me most of a morning now takes me about 30 minutes. I’ve even run this while on vacation or at a conference and didn’t’ have to worry about missing a deadline.
Describe the benefits you have achieved
It’s clear that Alteryx gives us much more time in our day to work on more interesting and important things. These repeatable workflows not only saves us time, it saves us money. Just a simple calculation of the 3.5 hours we save each week on the standard reporting, shows that the Alteryx license has paid for itself! And, I have peace of mind knowing that some of these simple yet critical tasks can be done easily.
Repeatable workflow – run by anyone, anytime
Saves 3.5 hours/week = 182 hours/year
$30/hour = $5,500
Alteryx pays for itself!
With Alteryx we don’t have to learn every new platform that the university decides to use, whether it’s changing how Salesforce works, using the Canvas LMS, getting to Google Analytics – we can just become Alteryx experts, and load the data we need to look at there. We are excited for the future and leveraging Alteryx to put us into the predictive analytics space.
The entire PowerPoint presentation can be found here. Additionally, visit this link to watch the entire recorded session.