Past Analytics Excellence Awards

Author: Andy Kriebel (@VizWizBI), Head Coach

Company: The Information Lab

 

Awards Category: Best 'Alteryx for Good' Story

 

The Connect2Help 211 team outlined their requirements, including review the database structure and what they were looking for as outputs. Note that this was also the week that we introduced Data School to Alteryx. We knew that the team could use Alteryx to prepare, cleanse and analyse the data. Ultimately, the team wanted to create a workflow in Alteryx that Connect2Help 211 could use in the future.

 

Ann Hartman, Director of Connect2Help 211 summarized the impact best: "We were absolutely blown away by your presentation today. This is proof that a small group of dedicated people working together can change an entire community. With the Alteryx workflow and Tableau workbooks you created, we can show the community what is needed where, and how people can help in their communities."

 

The entire details of the project can be best found here - http://www.thedataschool.co.uk/andy-kriebel/connect2help211/

 

Describe the problem you needed to solve 

In July 2015, Connect2Help 211, an Indianapolis-based non-profit service that facilitates connections between people who need human services and those who provide them, reached out to the Tableau Zen Masters as part of a broader effort that the Zens participate in for the Tableau Foundation. Their goals and needs were simple: Create an ETL process that extracts Refer data, transforms it, and loads it into a MYSQL database that can be connected to Tableau.

 

Describe the working solution

 

See the workflow and further details in the blog post - http://www.thedataschool.co.uk/andy-kriebel/connect2help211/

 

Describe the benefits you have achieved

While the workflow looks amazingly complex, it absolutely accomplished the goal of creating a reusable ETL workflow. Ben Moss kicked off the project presentations by taking the Connect2Help 211 team through what the team had to do and how Connect2Help 211 could use this workflow going forward.

 

From there, the team went through the eight different visualisation that they created in Tableau. Keep in mind, Connect2Help 211 wasn't expecting any visualisations as part of the output, so to say they were excited with what the team created in just a week is a massive understatement.

 

Author: Keith Snow (@ksnow), President/Data Scientist 

Company: B2E Direct Marketing    

 

Awards Category: Best 'Alteryx For Good' Story

On December 1st, 2015, which was "Giving Tuesday", a global day dedicated to giving back, B2E Direct Marketing announced a newly created grant program for 2016 called 'Big Data for Non-Profits'.  B2E Direct Marketing is a business offering Big Data, Visual Business Intelligence and Database Marketing solutions.

 

Non-profit organizations are a crucial part of our society, providing help to the needy, education for a lifetime, social interactions and funds for good causes.

 

Describe the problem you needed to solve 

While serving on three non-profit boards, Keith Snow, President of B2E, became aware that data is among the most important, under-used and least maintained asset of a non-profit. 

 

"The 'Big Data for Non-Profits' Grant program was born out of a vision that we had at B2E to give back to our community. We wanted to offer non-profits the same visual business intelligence and database marketing services that we offer our other clients." says Snow.

 

The grant program includes the following services free of charge to the winning organization in the month for which they are selected:

• Data Hygiene (clean up donor file)
• Data Append (age, income, gender, marital status, lifestyle segmentation, and more)
• Detailed donor analysis and overview reports

 

Each month in 2016, B2E will choose one non-profit from those that apply through www.nonprofit360marketing.com. Award recipient applications are reviewed by a panel selected by B2E and awards are given based upon how the services will be used and to further the organization's goals. The grant program began accepting applications from eligible 501(c)(3) non-profits at the end of December and has already completed work on three organizations so far this year.

 

"We are excited about using Alteryx to help non-profits expand their mission and to better serve our communities." says Snow.

 

Describe the working solution

B2E has an initial consultation meeting with each non-profit where the goals and takeaways of the 'Big Data for Non-Profits' program is discussed.

 

We identify current data sources that the non-profit has available, and request up to 48 months of donor contact and giving information.  Minimal information is requested from the non-profit as we know great value can be added using Alteryx Designer.

• Name                                                                         
• Address, City, State, Zip
• Phone
• Date of Donation
• Amount of Donation
• Campaign
• Donation type: i.e. cash, check, soft credit, etc. 

B2E has created Alteryx workflows to perform donor file hygiene. Since we have licensed the data package, we take advantage of the CASS, Zip4 Coder and Experian Geodemographic append and TomTom capabilities.

 

All donor data is address standardized to meet postal standards and duplicates within their database are identified.  Once the data is updated to meet our standards, we process the files against the National Change of Address and the National Deceased database. 

 

The next step is taking the donor's contact information and appending demographics at the individual and household level (age, income, gender, marital, age of home, Mosaic segmentation, etc.) using the Alteryx Experian add-on product.  Alteryx Designer is invaluable for this process as we manipulate the donor data to be more useful for the non-profit.

 

Alteryx' ability to export Tableau Extract files are key for this program to be successful. We have created key Tableau dashboards that highlight the following:

a. Consumer demographics

b. Mosaic marketing segmentation

c. Campaign or donation source

d. Donation seasonality / giving analysis

e. Pareto (80/20 Rule): to identify and profile the 20% of the donors who contribute 80% of the revenue

f. Geography (city, zip, county, metro area)

 

Once the data is in the Tableau Extract, business intelligence analysis is performed with visualization that is easy to understand and immediately actionable by the non-profit.  Tableau packaged workbooks are created for each non-profit so they have access to interactive analytics to help them make quick and immediate business decisions for their organization.

 

Describe the benefits you have achieved

B2E provides a niche service that many non-profits do not have the knowledge, tools or budget to complete on their own.

 

The benefits to each non-profit includes the following:

1. The donor data from each non-profit can now be processed in days instead of weeks using Alteryx. This allows B2E the maximum ability to help more organizations. In the past, we only worked with one non-profit per year. Our 2016 goal is to work with twelve.
2. A clean donor contact file with updated addresses, deceased individuals flag and duplicated merged is returned to the organization. Many non-profits send out direct mail, they immediately see their deliverability rates increase by more than 15% and return mail rates decrease. The cost for printing and postage is optimized as well.
3. The best way to get your current donors to give more is to truly understand what they look like. Understand the donor's life stage, giving history, demographics, lifestyle characteristics, media preferences and digital behavior is key for success. Targeting a donor in a way that resonates with them has lead to an increase in giving. 
4. All non-profits want access to new donors. A profile identifies what the best donor characteristics look like. Since B2E can also acquire direct mail and email lists, we help the non-profit find "look-alike" individuals who have never donated to their organization.
5. B2E's goal is to help each non-profit to maximize the current donations coming into their organization so they can keep their expenses and overhead lower as well as offer them a free service they would not have otherwise acquired.

 

The impact to each non-profit is huge, but the impact to B2E is just as great as we are allowed to use a great tool to be a leader in Iowa as a company that truly gives back to our community all year long. As of April, 2016, we have provided services for:

• Big Brothers Big Sisters of Iowa
• Children's Cancer Connection
• Youth Emergency Services and Shelter of Iowa
• Governors District Alliance
• Easter Seals Iowa

Author: Michael Peterman, CEO 

Company: VeraData

 

Awards Category: Best 'Alteryx for Good' Story

 

We provide deep analytics services for hundreds of clients.  Of particular interest is the NCCS (National Childrens Cancer Society).  This prestigious and highly respected organization has been doing more for the families of children with cancer since 1987 - yep, for almost 30 years.  We are honored to be serving them as a client.

 

Describe the problem you needed to solve 

NCCS, like every other large charity in America, sends out direct mail fundraising solicitations to support these families.  Like any other business has to spend money to acquire new customers, non-profit organizations spend money to acquire donors.  They were faced with a year over year trend of increasing donor acquisition costs and increasing costs to reactivate lapsed donors.   This was coupled with a concern was that there was a shrinking universe of potential donors who were willing to support their efforts.

 

Describe the working solution

Enter VeraData. Our initial engagement with NCCS was to build a donor acquisition model to reduce their costs to acquire donors, which subsequently reduces the cycle time to break-even on the investment in new donors. Concurrently, we developed a lapsed reactivation model that used tons of external, outside information to select from their audience of former donors the individuals most likely to donate again, therefore increasing the universe of marketable names while maintaining the cost to reactivate. Lastly, our third component was to uncover an expanded universe of individuals who had the propensity to support the NCCS. This meant identifying new data sets and determining which individuals would be profitable to pursue.

 

There were several methodologies deployed to achieve these goals. Our analytic team settled on a series of support vector machine models solving for response rate, donation amount, package and channel preferences, etc. All of the information in our arsenal was called upon to contribute to the final suite of algorithms used to identify the best audience. Using Alteryx, R, Tableau and our internal machine learning infrastructure, we were able to combine decades worth of client side data with decades worth of external data and output a blended master analytic database that accounted for full promotional and transactional history with all corresponding composite data on the individuals. This symphony achieved all of the goals, and then some.

 

 

Describe the benefits you have achieved

The client experienced a 24% reduction in their cost to acquire a donor, they were able to reactivate a much larger than anticipated volume of lapsed donors (some were inactive for over 15 years) and they discovered an entirely new set of list sources that are delivering a cost to acquire in line with their budget requirements. Mission accomplished.

 

Since that point, we have broadened the scope of our engagement and are solving for other things such as digital fundraising, mid-level and major donors. Wouldn't have been possible to do with the same speed and precision had we not been using Alteryx.

Author: Bill  Lyons  - Principal Data Scientist

Team Members: Trudy Fuher, Alana Welz, Arlyn Baggot

Company: Educational Media Foundation

 

Awards Category: Best ‘Alteryx For Good’ Story 

The initial project has the potential to save this non-profit organization up to $2.2 million per year in streaming costs when recommendations are fully implemented. Other use cases improve internal efficiencies, communication, and productivity.

 

Awards Category: Best Use of Alteryx Server for Analytics Deployment

Alteryx Server automatically processes daily file downloads, weekly file downloads with decompression, decryption and bulk insertion, and monthly zip code DMA assignments. Other use cases support self-service imports, exports and reporting.

 

Awards Category: Best Use of Alteryx for Spatial Analytics

Alteryx spatial tools combined with Alteryx data is driving optimization of regional streams associated with DMAs.

 

Awards Category: Best Value Driven with Alteryx

Optimizing regional streams has resulted in at least $500,000 in savings since July 2016, with recommendations implemented so far. When all recommendations are fully implemented, savings could be $2.2 million per year or more.

 

Awards Category: From Zero to Hero

Even though we purchased our first Designer license in June 2015, as of early March 2016, we had not created a single workflow with Alteryx. We were considering not renewing our license. At that time, we got a new rep, Nick Glassner, who arranged for a couple of WebEx sessions with Alteryx Solutions Engineer Ali Sayeed to get us started on a real project. Within a few weeks, I recognized many more potential applications for Alteryx, and was off and running. I changed from a skeptic to an enthusiastic user. Analysis for this project began in mid-April and was completed in mid-May. We acquired Alteryx Server in June, and had the first phase of the implementation of this project running on a daily schedule by August. Other phases came online in November and in January 2017.

 

At that point, I was still the only person using Alteryx heavily in analysis and production. So, I began some internal workshops showing how to solve real-world problems with Alteryx. We now have 3 more internal users becoming productive with Alteryx, and are looking to hire another. Some of these users are also taking advantage of the “Enablement Series” offered by our new rep, Tim Cunningham.

 

Describe the problem you needed to solve

Initial business problem: Recent regulatory changes caused our national internet radio streaming costs to more than double, from less than $1 million to over $2 million annually. The goal was to find ways to optimize our streams to move usage from the national stream to our underutilized regional streams, and thus reduce our costs.

 

Other use cases, including their business challenges, solutions, and benefits, follow the solutions and benefits of this initial business problem.

 

Describe the working solution

Alteryx played a major role in analysis of the streaming data. Some of the regional streams were underutilized, while others exceeded their cost effective limits, so the first phase was to analyze the accuracy of IP address geolocation software to see what would be causing this. The website systems and the log analytic systems used different IP geolocation software (the websites used IP2Location, and the analytic systems used Maxmind) so we needed to know if one was better than the other, or if neither was adequate. However, these system are isolated from each other by firewalls, making direct comparisons impossible. Alteryx Designer allowed me to connect to three different SQL Server database systems and compare their data with a .csv file from another vendor being evaluated (NetAcuity).

 

This analysis made extensive use of Alteryx spatial matching and Alteryx spatial data, visualizing results with Tableau. It revealed some disturbing facts, including that the geolocation was very inconsistent between the systems. As an example, we found that less than half of the listeners to the New York City stream were even in the NYC DMA (Figure 1).

 

Figure 1

 

Additionally, we learned that only a little more than half of the listeners in the NYC DMA were listening to the NYC stream. (Figure 2)

Figure 2

 

 The analysis also compared actual registered listener locations to the location reported by the various services. This showed that IP2Location was clearly inferior. (Figure 3)

 

Figure 3

But Maxmind returned a significantly higher number of unknown locations, both within the US, and even identifying the country. (Figure 4)

 

Figure 4

 

The analysis concluded with 16 recommended changes to systems, software, programming and contracts.

One of those recommendations was to unify both the websites and the analytics on the same and most consistently accurate IP address geolocation provider: NetAcuity. Alteryx supports the updates to the NetAcuity database by downloading the data from NetAcuity, decompressing, decrypting, and bulk inserting it into SQL Server. It does this on a weekly schedule in Alteryx Server, each time moving roughly 40 million rows of data in about an hour.

 

Primary workflow:

 

Supporting macros:

 

 

An Alteryx Server scheduled app then builds Calgary databases of the IP geolocation data.

 

 

Next, another Alteryx Server scheduled app applies that geocoding to the streaming log data.

 

 

 

 

Alteryx spatial data also supports Server scheduled monthly updates to keep zip to DMA to stream assignments up to date.

 

Describe the benefits you have achieved

4 of the 16 recommendations have been implemented to date, saving over $500,000 since last July, and an estimated $700,000 for 2017. More steps are in development, with a goal of saving $2 million per year.

 

Never before did we have a reliable and up-to-date zip code to DMA assignment process. We previously bought zip code to DMA data from Nielsen, but it was incomplete and quickly out-of-date.

 

Other Significant Alteryx Use-Cases

 

1. Transmitter location identification

• Business Challenge: Property tax filings must be made with the appropriate jurisdiction for the location of the property. With normal property, the street address easily identifies that jurisdiction. However, radio transmitter sites are frequently in very remote locations where there is no street address, and frequently on tops of mountains, within a few feet of jurisdictional boundaries. Historically, property tax accountants manually used transmitter location geographic coordinates to search maps to identify state and county with which to file property tax forms. This very laborious process took a team of 3 or 4 people up to 8 weeks each year, and was fraught with error.
• Solution: Alteryx Server scheduled app performs spatial match between transmitter geographic coordinates and Alteryx spatial data, precisely and accurately identifying and coding transmitter location state and county. Run time: about 15 seconds per day, automatically. This simple workflow took only a couple of hours to build and deploy.

   

• Benefit: Savings of up to 8 man-months of manual labor per year. Reduction in errors (this process identified more than 200 instances where the location was either undocumented or in the wrong jurisdiction; 2 were even in the wrong state).

 

2. Log file FTP download

• Business Challenge: The system downloading new log files from content delivery network (CDN) daily was very fragile, requiring manual checks and restarts every few days.
• Solution: Alteryx workflow app, scheduled to run daily, downloads list of available files, compares list to list of previously downloaded files, downloads new files, updates list of files downloaded.

   

   

   

 

 

• Benefit: Alteryx job has run without error for 8 months. Saves time (about an hour per week) monitoring and maintaining each week, but it is mostly a huge reduction in the "hassle factor." Time to develop was less than a couple months’ worth of manual corrections.

 

3. User import of Excel into SQL Server

• Business Challenge: Data files from mobile app vendors come each month in Excel files and need to be imported to SQL Server. This import required a DBA to manually import, and was consequently a year behind.
• Solution: Gallery app allows users to upload files themselves, automatically removes duplicate data, reports duplicates ignored, structure errors, and data imported.

   

   

 

• Benefit: Self-service of data import relieves workload of DBAs and allows users to have immediate reporting of data in Tableau. This process also revealed that the supplier had duplicate records that overlapped between months. This had created erroneous data of which we had not previously been aware.

 

4. Tealium reporting

• Business Challenge: Connecting Tableau directly to Redshift was slow.
• Solution: In-Db tools query Redshift database, filter, aggregate, and download to Tableau Server Data Source Extract. App is scheduled in Alteryx Server. 

   

  
• Benefit: Faster Tableau reports

 

5. Studio automation logs

 

• Business Challenge: Log files have been inconsistent and incomplete, with gaps and overlaps, making downstream reports unreliable.
• Solution: Download tool connects directly to REST API of studio automation software, parses the JSON, and inserts into SQL Server data warehouse. Scheduled in Alteryx Server daily.

   

   

• Benefit: Reliable data for reporting.

 

6. Record of donor communication

 

• Business Challenge: Producers call donors to record their stories, logging that call in Google Sheets. Donors call back, talking to communicators in the Listener Services department who have no visibility to the Google Sheets, and there was no record in the donor system. Awkward conversations ensued.
• Solution: Alteryx Server app scheduled to run every 5 minutes connects to Google Sheet, downloads the call records and insert records into the SQL Server donor system of record.

   

• Benefit: Listener Services communicators can now intelligently communicate with donors.

 

Author: Jeff Bernson, Senior Director, Results, Management, Measurement and Learning 

Company: PATH 

Team Members: Mike Hainsworth, Allan Walker, Doug Morris, Anya A’Hearn, Philip Riggs, Chris DeMartini, Jonathan Drummey, Jeff Bernson, Daniel Bridges, Joe Mako, Ken Black

 

Awards Category: Best 'Alteryx for Good' Story

 

PATH is the leader in global health innovation. We are driven by an unshakeable commitment to health equity and a bold belief in the power of innovation to improve health, save lives, and transform the future, especially for women and children. Working with partners across the public and private sectors, our trusted experts bring together the right tools and people at the right time and place to accelerate innovative solutions to the health challenges facing vulnerable women, children, and communities worldwide. By tenaciously supporting solutions all the way from early concept to large-scale use, PATH’s work translates smart ideas into tangible change for millions of people.

 

Describe the problem you needed to solve 

In Africa, malaria is the leading cause of death in young children. For the past decade, the Zambian National Ministry of Health and the National Malaria Elimination Centre (NMEC) with support from PATH have used data to fight malaria—saving thousands of lives. In 2015, PATH teamed up with Alteryx and six other technology partners to launch Visualize No Malaria—a campaign focused on integrating new tools and systems for data use to support Zambia’s Ministry of Health in their effort to eliminate malaria by 2021.

 

The PATH-supported malaria laboratory in Lusaka, Zambia, located at the NMEC is implementing a range of molecular tools in the quest to achieve elimination. Every month, thousands of blood samples from around the country are processed to answer key questions around issues such as diagnostic efficacy, drug resistance, and transmission intensity.

Prior to our partnership with Alteryx, the lab was ill equipped to deal with these data. At the time, our laboratory information system was costly, cumbersome and necessitated building a custom database for each study. We needed a flexible, agile tool that could grow with our needs, namely, a system that would provide a simple mechanism for reporting data, aggregating it, and producing summary outputs.

 

Describe the working solution

Alteryx software has transformed how we access data. As we experimented with Alteryx, it quickly became apparent that we could almost instantly curate, process, and archive large amounts of data to inform our field activities and to better understand what’s really happening with malaria transmission patterns and dynamics in near real-time.

 

Our partnership will empower frontline health workers with the critical tools to prevent, track, and treat malaria cases to help eliminate this deadly disease.  We are improving data accuracy and making real-time critical data-informed decisions about how and where to tackle outbreaks. We are also building the skills of district and facility health teams to combat the disease at community level. If successful, this model could serve to inform global efforts to end malaria for good. 

 

PATH and its data science partners are using Alteryx in a number of ways to help lead the charge toward elimination including:

 

1. Automating workflows that help us process lab results and notify health workers of positive malaria cases faster in targeted study sites. Prior to the introduction of Alteryx, we essentially were unable to provide feedback on results in any meaningful way. Now instead of waiting months, we can process results on a daily basis with automated outputs sent to the end-user for dissemination and to inform decisions.

 

 

2. Responding to survey data during field data collection. Survey data is usually evaluated at the end of the process, which is fine if everything has gone well, but potentially catastrophic if errors were introduced. Through Alteryx, we have been able to access survey data as it is collected through a cloud API and then validate and summarize data to ensure that we are on track. The speed of this process meant that we were able to not only identify and resolve issues during the survey, but could rapidly and iteratively interrogate the data to ensure high data quality.

 

3. Developing a workflow to enable automated prediction and forecasting of malaria cases in the Southern Province of Zambia that can be batched on a weekly schedule.

 

 

 

4. Leveraging Alteryx for geospatial data processing and analysis to create modeled boundaries such as facility catchment areas, entity relationship, and resource optimization diagrams, such as health worker to Facility network diagrams, and input layers for other geographic processing and rendering tools, such as Mapbox.

 

 

5. Pre-processing and merging of geospatial and epidemiological data for ease of use in front end rendering and analytic tools, such as Tableau.

 

 

 

Describe the benefits you have achieved

The net result from this work has been a more efficient workflow process, more insightful analyses, and the development of a more holistic approach to understanding the complete malaria picture in Southern Province. Alteryx put the power in the hands of the user so we can build exactly what we need when we need it.

 

Since deployment of these tools, along with a number of strategies to further accelerate the gains against malaria, since 2014 there has been a 90 percent decrease in malaria cases in the Southern Province. As transmission rates decrease and malaria cases become increasingly localized, combining the existing interventions with capabilities of data analytics tools like Alteryx will play an essential role in combating the disease at the community level and bringing the number of cases to zero.