Past Analytics Excellence Awards

Author: Slaven Sljivar, Vice President, Analytics

Company: SmartDrive Systems, Inc.

Awards Category: Most Time Saved

Describe the problem you needed to solve

SmartDrive’s Analytics Team, which is approaching its 9th year in its existence in our 12-year-old company, is focused on three areas: 1) customer-facing analytics, 2) analytics supporting the internal teams, and 3) analytics as it is embedded within our product.  To support these activities, we rely a well-developed data warehousing and business intelligence stack that includes Tableau, R, SQL Server (for relational dimensional data warehouse) and SQL Server Analysis Services cubes. 

Alteryx, which we first started using only 5 months ago (March 2016), fills in a gap in our ability to quickly integrate data.  Prior to Alteryx, we relied on a combination of R scrips, SQL stored procedures and SQL Server Integration Services (SSIS) jobs to develop data integration solutions.  While this approach worked for us over the years, it had several drawbacks:

1. It was a more “code-heavy” approach than we liked. While our Analytics team is comprised of competent coders and scripters, we seek to minimize the amount of code we generate (and maintain!)
2. It was relatively slow and labor-intensive. A project that involved data integration took much longer to complete than a project that could be completed with “curated” data that already existed in our data warehouse and/or cubes.
3. It was not very maintainable. Once a failure occurred or an enhancement was needed, dealing with code made it more difficult to get into “flow of things” compared to dealing with visual workflows.

One specific example is a repetitive analysis that we call “Fuel Savings Analysis” (FSA).  The goal of this analysis is to evaluate how much fuel our customers (commercial vehicle fleets) saved from drivers operating their vehicles differently after SmartDrive’s video event recorders were installed in the vehicles.  Because video event recorders activate in response to unsafe and abrupt maneuvers, drivers tend to avoid executing such maneuvers.  These maneuvers also often lead to fuel waste.  For example, harsh braking wastes more kinetic energy than gradually coasting down and using the kinetic energy (and not fuel) to overcome the rolling friction and aerodynamic drag. 

We had already developed a tool that automated the FSA analysis, utilizing stored procedures, R code, custom data cubes and Tableau.  However, the tool required several manual steps and needed to be run for one customer at a time.  As the result, SmartDrive’s Account Management team had to make a request of the Analytics team whenever the analysis needed to be run, and the Analytics team needed to expend 2 to 3 hours of effort for each request.

In April 2016, one month after we started using Alteryx, our Marketing team asked for the analysis to be done that assessed the fuel savings for all SmartDrive customers.  They were interested in including that statistics in an upcoming momentum press release.  Of course, this was not achievable with the existing tool, so we thought we would try to implement the workflow in Alteryx.  We were ultimately successful in being able to support this request, leading to the following paragraph being included in the April 12^th, 2016 press release:

Saved customers an average of $4,903 per vehicle per year—with annual per vehicle savings of $1,878 in collision exoneration, $1,784 in collision cost reduction, and $1,240 in fuel expense

Describe the working solution

Our Alteryx workflow solution issues several queries against the data warehouse, with the primary (and the largest) query representing fuel consumption and distance driven for each customer vehicle and for each week that the vehicle was driven. This is combined with a dataset that tracks when each customer site was installed with SmartDrive, so that baseline and treatment period data can be separated. An R script that employs a decision tree (rpart) is used to group vehicles and is embedded within the workflow. The key calculation for the expected fuel consumption in the treatment period (e.g. scenario that removes the effect of SmartDrive) is calculated in Alteryx, and the resulting dataset is published on Tableau Server. We authored a Tableau workbook that implements additional calculations (e.g. % fuel savings, $ savings, etc.) and allows our Account Management team to create visuals that can be shared directly with the customer. The Alteryx workflow is scheduled to run weekly every Tuesday. In less than 30 minutes, the workflow processes the entire customer dataset, with the bulk of the time being spent waiting for the data warehouse to generate the vehicle-week extract. The entire workflow is shown in the image below.

Describe the benefits you have achieved

In this particular example, Alteryx allowed us to completely streamline a process that was already largely automated using other tools. While we could have invested more time to fully automate the existing tool, that would have involved so much effort that we have repeatedly decided to de-prioritize that work.

Now that we have a fully-streamlined process, our Account Management team is able to “pull up” the Fuel Savings Analysis visualization (“report”) on their own, with up-to-date results. Also, our Marketing team is able to report on the overall actual fuel savings realized by SmartDrive customers.

Beyond the Analytics team no longer needing to spend time and effort on running the Fuel Savings Analyses, this new capability allows our Account Management team to more consistently present the fuel savings results to our customers, particularly those that are still piloting SmartDrive. This leads to increased revenue from improved pilot conversion and also greater customer satisfaction stemming from the knowledge that their investment in SmartDrive service is generating positive financial returns.

Author: Kristin Scholer (@kscholer), Insight Manager 
Company: Ansira
Awards Category: Most Time Saved

Ansira, an industry-leading marketing agency in integrated real-time customer engagement, activates big data through advanced analytics, advertising technology, programmatic media and personalized customer experiences. Ansira leverages superior marketing intelligence to build deeper, more effective relationships with consumers and the retail channel partners that engage them on the local level. Marketing intelligence is infused across all disciplines and executed through digital, direct, social, mobile, media and creative execution, marketing automation, co-op and trade promotion. 

Describe the problem you needed to solve

As a data-driven advertising agency, Ansira is constantly profiling customer behavior for a variety of clients in industries such as quick service restaurants, automotive brands and large retailers. Ansira’s Analytics team heavily utilizes media and consumer research that comes from the MRI Survey of the American Consumer to create Customer Behavior Reports. This large survey provides a vast database of demographics, psychographics, media opinions and shopper behavior that give insights into the actions and behaviors of the U.S. consumer. These insights help Ansira better understand consumers for new business perspectives as well as develop strategies for existing clients.

The challenge the Analytics team faced was that these rich insights were not easy to format, interpret or analyze. The data is accessed through an online portal and exported into an Excel format that does not make the data easy to manipulate. Depending on the project requirements, it could take an analyst 4-8 hours to format the data, identify survey responses that are statistically significant, build out a report to display all the information and write up a full summary. This is not cost effective and it became clear that a better way to transform this data was needed if Ansira wanted to utilize it on a regular basis.

Describe the working solution

After using Alteryx to format unfriendly Excel output for many projects, it was clear to the Analytics team that Alteryx could also be a solution for speeding up the Customer Behavior Report process. In about two days, one team member was able to create an Alteryx workflow that did all of the Excel formatting in just three seconds (this was generally taking over an hour to do manually).

Then Alteryx was taken one step further as formula tools were integrated to identify which behaviors were statistically significant for an analysis (this was taking 1-2 hours to work through manually). Next, the process was simplified one more time by incorporating the reporting tools to create a full report of all the data needed in the form of a PDF. The report even included color coding to easily identify statistically significant behaviors. Not only did this create a beautiful report in seconds but made key behaviors easy to identify, thus taking the analysis and summary process from 2-3 hours down to 15-30 minutes.

Describe the benefits you have achieved

The process that was created in Alteryx has allowed the Ansira Analytics team to offer Customer Behavior Reports to New Business and Strategy departments that can be turned around in a day instead of a week. If a full analysis is not needed, the Analytics team can turn around just the PDF data report in as little as 15 minutes (see picture below). This allows Ansira to gain additional direction on who is the consumer they are targeting, which can be instrumental in creating a new campaign.

To make this process even easier, the Analytics team has created a request form (see picture below) that anyone at Ansira can use to identify the behaviors they are interested in seeing for their client. Once the request form is received by the Analytics team, they can do a quick data pull from the MRI online portal, update the Alteryx workflow and have a full report created in under an hour.

Ansira recently had a consumer packaged goods client where the Strategy team needed to learn more about the difference in behavior patterns between Millennials and Non-Millennials who purchased 16 specific products. The Analytics team was able to pull data from MRI on these 16 products, run it through the Customer Behavior Report workflow and create 16 individual reports for each product comparing Millennials and Non-Millennials purchase behaviors in less than 4 hours. Without Alteryx, this would have taken a single analyst almost a full week to complete and likely would have never even been a possibility due to budget and capacity constraints.

Creating these Consumer Behavior Reports have become a regular occurrence with two to three requests coming into the Analytics department each week. With the help of Alteryx, these reports have become a large asset to Ansira as they provide very impactful information without a lot of effort.

Author: Brett Herman ( @brett_hermann ) , Project Manager, Data Visualization 

Company: Cineplex

Cineplex Inc. (“Cineplex”) is one of Canada’s leading entertainment companies and operates one of the most modern and fully digitized motion picture theatre circuits in the world. A top-tier Canadian brand, Cineplex operates numerous businesses including theatrical exhibition, food service, amusement gaming, alternative programming (Cineplex Events), Cineplex Media, Cineplex Digital Media, and the online sale of home entertainment content through CineplexStore.com and on apps embedded in various electronic devices. Cineplex is also a joint venture partner in SCENE – Canada’s largest entertainment loyalty program. 

Awards Category: Most Time Saved

Describe the problem you needed to solve 

Incremental/Uplift Modelling is a popular method of evaluating the success of business initiatives at Cineplex. Its effectiveness at measuring the change in consumer behavior over time creates a high demand to produce this kind of analysis for various departments in the organization. Due to the large amount of requests we receive, the ‘Incremental Lift Model’ was developed to take in user-defined inputs, and output the results within a short period of time.

Describe the working solution

Our solution works through a four step process. The first step is for the client to populate the ‘study input form’ in order to define their study parameters and the type of study they want to run.

Visual 1: Study Input Form

The second step is to update/materialize our loyalty data that’s inputted into the model (yxdb format). We do this so that the model doesn’t put stress on our SQL Server databases, and to increase the model’s performance.

Visual 2: Update/Materialize Alteryx Input Data

The third step is the core of the incremental lift modelling. A macro processes one study at a time by pointing to the user defined inputs made in the first step.

Visual 3: Study Numbers are selected and passed through the incremental lift macro, and saves the output to SQL.

The data will then be passed through one of several macros depending on the study type, and filtered down based on the inputs defined by the user in the study input form. All data sources are joined together and lift calculations are made, which are then outputted into a master SQL Table ready to be visualized.

Visual 4: Incremental Lift Modelling based on study type selected.

The results are visualized using a Tableau Dashboard in order to share and communicate the results of the study back to the business clients.

Visual 5: Tableau Dashboard to explain to the business how the incremental lift model makes its calculations.

Describe the benefits you have achieved

The overarching goal of this project was twofold; to minimize the amount of work required to process business requests while maximizing the output generated, and to develop a means of delivering the results in a consistent manner. Both of these goals contribute greatly to our ROI by virtually eliminating all time spent executing requests that come in, and by minimizing time spent meeting with business users to explain how the incremental lift model works and how to interpret the results.

Author: Sintyadi Thong ( @MizunashiSinayu ), Pre-Sales Consultant, Karen Kamira & Harry Yusuf

Company: Lightstream Analytics PTE Ltd.

LightStream Analytics focuses on performance management, big data, advanced analytics, and innovative visualization through cloud SaaS applications, mobile, and traditional on-site systems. LightStream Analytics is well-positioned to deliver the most advanced products and services by capitalizing on its significant regional presence in Singapore and Indonesia. The combined offices have over 60 employees with deep technical and senior business experience. The company leverages our existing technical support and R&D centers in Indonesia and China to develop solutions which disrupt customary methods of data analysis and give clients access to revolutionary tools for understanding their data.  LightStream Analytics has partnered with more than 100 multinational and local clients to integrate, structure, analyze, and visualize information to measure their business performance and drive enterprise value growth.

Awards Category: Most Time Saved

Describe the problem you needed to solve 

One of our clients tried to implement one of the most Business Intelligence solution to help them grow their business through another company (we can pretty much say our competitor). However, there is one thing which hinders their development of the BI. When usually most companies want to see the date of sales (on which date their agents perform sales), this company would like to see the other way around, they would like to see ‘on which dates their agents do not perform sales activity’. For them this is very important. The BI developers hit a dead-end on this thing, and therefore I came with Alteryx.

Describe the working solution

 The BI I previously mentioned is QlikView. Well Qlik can do it, and I can guarantee. But it involves heavy scripting and logic. With heavy scripting it will also mean it requires heavy resources to perform the run (will only be visible when running with low RAM). Alteryx on the other hand can do this easily using drag-and-drop and repeatable workflow, so I feed Alteryx with the actual sales data, perform several left joins, filter, and unique. Alteryx requires no scripting, and to be honest I am not even an IT guy, I know nothing about SQL and programming, but I can create this workflow easily. So we proposed to have Alteryx prepare and blend the data before feeding it to QlikView, therefore it will help to make the data visible before feeding it to QlikView and lessens the burden on QlikView. While the client has not yet confirmed whether they will get Alteryx or not, it is really satisfying and rewarding to solve this problem easily while others had hardships in getting this result.

Describe the benefits you have achieved

While I created the workflow in only an hour vs their 2 weeks development for this one case (and in which they failed after 2 weeks), this shows how much of a time savings the client would get if they developed QlikView alongside with Alteryx. Alteryx will help the customers to get results faster and perform an advanced ETL which might be hard to do in traditional SQL language.

Author: Katie Snyder, Marketing Analyst

Company: SIGMA Marketing Insights

Awards Category: Most Time Saved

We've taken a wholly manual process that took 2 hours per campaign and required a database developer, to a process that takes five minutes per campaign, and can be done by an account coordinator. This frees our database developers to work on other projects, and drastically reduces time from data receipt to report generation.

Describe the problem you needed to solve 

We process activity files for hundreds of email campaigns for one client alone. The files come in from a number of different external vendors, are never in the same format with the same field names, and never include consistent activity types (bounces or opt-outs might be missing from one campaign, but present in another). We needed an easy, user-friendly way for these files to be loaded in a consistent manner. We also needed to add some campaign ID fields that the end user wouldn't necessarily know - they would only know the campaign name.

Describe the working solution

Using interface tools, we created an analytic app that allowed maximum flexibility in this file processing. Using a database query and interface tools, Alteryx displays a list of campaign names that the end user selects. The accompanying campaign ID fields are passed downstream. For each activity type (sent, delivered, bounce, etc), the end user selects a file, and then a drop down will display the names of all fields in the file, allowing the user to designate which field is email, which is ID, etc. Because we don't receive each type of activity every time, detours are placed to allow the analytic app user to check a box indicating a file is not present, and the workflow runs without requiring that data source.

All in all, up to six separate Excel or CSV files are combined together with information already existing in a database, and a production table is created to store the information. The app also generates a QC report that includes counts, campaign information, and row samples that is sent to the account manager. This increases accountability and oversight, and ensures all members of the team are kept informed of campaign processing.

Process Opt Out File - With Detour:

Join All Files, Suppress Duplicates, Insert to Tables:

Generate QC Report:

Workflow Overview:

QC Report Example:

Describe the benefits you have achieved

In making this process quicker and easier to access, we save almost two hours of database developer time per campaign, which accounts for at least 100 hours over the course of the year. The app can be used by account support staff who don't have coding knowledge or even account staff of different accounts without any client specific knowledge, also saving resources. Furthermore, the app can be easily adapted for other clients, increasing time savings across our organization. Our developers are able to spend time doing far more complex work rather than routine coding, and because the process is automated, saves any potential rework time that would occur from coding mistakes. And the client is thrilled because it takes us less time to generate campaign reporting.

Author: Jack Morgan (@jack_morgan), Project Management & Business Intelligence

Awards Category: Most Time Saved

After adding up the time savings for our largest projects we came up with an annual savings of 7,736 hours - yea, per year! In that time, you could run 1,700 marathons, fill 309,000 gas tanks or watch 3,868 movies!! Whaaaaaaaaaaaaat! In said time savings, we have not done any of the previously listed events. Instead, we've leveraged this time to take advantage of our otherwise unrealized potential for more diverse projects and support of departments in need of more efficiency. Other users that were previously responsible for running these processes now work on optimizing other items that are long overdue and adding value in other places by acting as project managers for other requests.

Describe the problem you needed to solve 

The old saying goes, Time is of the essence, and there are no exceptions here! More holistically, we brought Alteryx into our group to better navigate disparate data and build one-time workflows to create processes that are sustainable and provide a heightened level of accuracy. In a constraint driven environment my team is continuously looking for how to do things better. Whether that is faster, more accurately or with less needed oversight is up to our team. The bottom line is that Alteryx provides speed, accuracy, and agility that we never thought would be possible. Cost and the most expensive resource of all, human, has been a massive driver for us through our Alteryx journey and I'd expect that these drivers will continue as time passes us by.

Describe the working solution

Our processes vary from workflow to workflow, however overall we use a lot of SQL, Oracle, Teradata and SharePoint. In some workflows we blend 2 sources; in others we blend all of them. It depends on the need of the business that we are working with on any given day. Once the blending is done we do a variety of things with it, sometimes is goes to apps for self-service consumption and other times we push it into a data warehouse. However one thing that is consistent in our process is final data visualization in Tableau! Today, upwards of 95% of our workflows end up in Tableau allowing us to empower our users with self-service and analytics reporting. When using databases like SQL and Oracle we see MASSIVE gains in the use of In-Database tools. The ability for our Alteryx users to leverage such a strong no code solution creates an advantage for us in the customer service and analytics space because they already understand the data but now they have a means to get to it.

Audit Automation:

Billing:

File Generator:

Market Generator:

Parse:

Describe the benefits you have achieved

The 7,736 hours mentioned above is cumulative of 7 different processes that we rely on, on a regular basis.

1. One prior process took about 9 days/month to run - we've dropped that to 30s/month!
2. Another process required 4 days/quarter that our team was able to cut to 3 min/quarter.
3. The third and largest workflow would have taken at estimate 5200 hours to complete and our team took 10.4 hours to do the same work!
4. The next project was a massive one, we needed to create a tool to parse XML data into a standardized excel format. This process once took 40 hrs/month (non-standard pdf to excel) that we can run in less than 5s/month!
5. Less impressive but still a great deal of time was when our systems and qa team contracted us to rebuild their daily reporting for Production Support Metrics. This process took them about 10 hours/month that we got to less than 15 sec/day.
6. One of our internal QA teams asked us to assist them in speeding up their pre-work time for their weekly audit process. We automated their process that took them upwards of 65 hours/month to a process that now takes us 10 sec/week!
7. The last of the 7 processes that have been mentioned in that our above write-up would be a process for survey data that took a team 2 hours/week to process. That same process takes our team about 20 sec/week to process.

We hope you've found our write-up compelling and win-worthy!

Author: Alex Huang, Asst. Mgr, Quality Planning & Analysis

Company: Hyundai Motor America

Awards Category: Most Time Saved

There have been just a few times where some tool or platform has truly "changed" my life.  The two that come immediately to mind are Alteryx & Tableau.  Before I had either, the majority of my time was spent wrangling data, creating reports, and doing what I could using SAS, SQL, & Excel.  I had streamlined as much as I could and still felt bogged down by the rudimentary data tasks that plague many of us. 

With the power of Alteryx alone, I've regained 1,253 hours per year.  Alteryx WITH Tableau has saved me an additional 511 hours to a total of 1,764 hours saved per year!  Does that mean I can quit?  Maybe…but I’m not done yet!

For those that care for the details, here's a table of time savings I had cataloged during the start of my Alteryx journey.  I’ve had to blank out the activity names for security reasons but the time savings are real.

I experienced a 71% savings in time with Alteryx alone!

With this new found "free time," I was able to prototype ideas stuck on my To-Do list and create new insight for my business unit.  Now my "what if's" go from idea straight to Alteryx (and to Tableau faster) and I couldn't be happier.  Insights are delivered faster than ever and with more frequent (daily) updates thanks to Alteryx Desktop Automation.

Describe the problem you needed to solve

Hyundai Motor America sells thousands of cars per day so the faster we can identify a quality issue and fix it, the more satisfied our customers will be.  Addressing quality concerns earlier and faster helps us avoid additional costs but most importantly brand loyalty, perceived quality, and vehicle dependability, etc.  Some examples of actions:

1. Increased the speed at which we validate and investigate problems from survey data resulting in faster campaign launches and remedy development.
2. Able to digest and understand syndicated data from J.D. Powers within hours instead of weeks allowing us to further validate the effectiveness of our prior quality improvement initiatives and also identify issues we missed.
3. Being able to blend all the data sources we need (call center, survey data, repair data, etc.) in Alteryx allowed us to more rapidly prototype our customer risk models vs. traditional methods via SAS which took much longer.
4. Alteryx automation with Tableau allowed us to deploy insight rich interactive dashboards that enabled management to respond to questions in real-time during our monthly quality report involving many major stakeholders throughout Hyundai.  This lead to more productive meetings with more meaningful follow-up action items.

I needed to solve a time problem first!  I was spending too much time doing things like data prep and reporting that just wasn’t quite enough for me.  I didn't have enough time to do what I really wanted to do, solve problems!

Being an avid fan/user of Tableau, data preparation started becoming my biggest challenge as my dashboard library grew.  I would end up writing monster SQL statements and scripts to get the data ready but I still struggled with automation for creating Tableau Data Extracts (TDE's). I explored using Python to create them but it just wasn't quite the "desired" experience.  Enter Alteryx, life changed.

Describe the working solution

My work typically involves blending data from our transactional data warehouse, call center data, survey data, and blending third-party data from companies like J.D. Powers.  Since we have an Oracle database in-house, I'm able to leverage the In-DB tools in Alteryx which is just amazing!  In-DB tools are similar to a "visual query builder" but with the Alteryx look, feel, and added capability of Dynamic Input and Macro Inputs.  Since data only moves out of the DB when you want it to, queries are lightning fast which enable accelerated prototyping ability!

Describe the benefits you have achieved

I've quite literally freed up 93% of my time (given 1960 work hours per year with 15 days of vacation @ 8 hours per day) and started a new "data team" within my business unit with Alteryx & Tableau at its core.  The ultimate goal will be to replicate my time savings for everyone and “free the data” through self-service apps.  At this point, I’ve deployed 5,774 Alteryx nodes using 61 unique tools in 76 workflows of which 24% or so are scheduled and running automatically.  Phew!  Props to the built-in “Batch Macro Module Example” for allowing me to calculate this easily!

We are able to identify customer pain points through an automated Alteryx workflow and algorithm that gauges how likely an issue will persist across all owners of the same model/trim package.  We’ve seen how blending Experian ConsumerView data bolsters this model but we’re still in the cost justification phase for that.  Upon detection of said pain point, we are able to trigger alerts and treatments across the wider population to mitigate the impact of this pain point.  Issues that can’t be readily fixed per se are relayed back to R&D for further investigation.  Ultimately customers may never see an issue because we’ve addressed it or they are simply delighted by how fast we’ve responded even when no immediate remedy is available.

The true bottom line is that the speed and accuracy at which we execute is critical in our business.  Customers want to be heard and they want to know how we are going to help resolve their problems now, not months later.  They want to love their Hyundai’s and the more they feel like we are helping them achieve that, the more loyal they will be to our brand.

Although we can’t fix everything, Alteryx helps us get to where we need to be faster which; in my opinion, is an enabler for success.

Author: Shelley Browning, Data Analyst

Company: Intermountain Healthcare

Awards Category: Most Time Saved

Describe the problem you needed to solve 

Intermountain Healthcare is a not-for-profit health system based in Salt Lake City, Utah, with 22 hospitals, a broad range of clinics and services, about 1,400 employed primary care and secondary care physicians at more than 185 clinics in the Intermountain Medical Group, and health insurance plans from SelectHealth. The entire system has over 30,000 employees. This project was proposed and completed by members of the Enterprise HR Employee Analytics team who provide analytic services to the various entities within the organization.

The initial goal was to create a data product utilizing data visualization software. The Workforce Optimization Dashboard and Scorecard is to be used throughout the organization by employees with direct reports. The dashboard provides a view of over 100 human resource metrics on activities related to attracting, engaging, and retaining employees at all levels of the organization. Some of the features in the dashboard include: drilldown to various levels of the organization, key performance indicators (KPI) to show change, options for various time periods, benchmark comparison with third party data, and links to additional resources such as detail reports. Prior to completion of this project, the data was available to limited users in at least 14 different reports and dashboards making it difficult and time consuming to get a complete view of workforce metrics.

During initial design and prototyping it was discovered that in order to meet the design requirements and maintain performance within the final visualization it would be necessary for all the data to be in a single data set. The data for human resources is stored in 17 different tables in an Oracle data warehouse. The benchmark data is provided by a third party. At the time of development the visualization software did not support UNION or UNION ALL in the custom SQL function. During development the iterative process of writing SQL, creating an extract file, and creating and modifying calculations in the visualization was very laborious. Much iteration was necessary to determine the correct format of data for the visualization.

Other challenges occurred, such as when it was discovered that the visualization software does not support dynamic field formatting. The data values are reported in formats of percent, currency, decimal and numeric all within the same data column. While the dashboard was in final review it was determined that a summary of the KPI indicators would be another useful visualization on the dashboard. The KPI indicators, red and green arrows, were using table calculations. It is not possible to create additional calculations based on the results of table calculations in the visualization software. The business users also requested another cross tabular view of the same data showing multiple time periods.

Describe the working solution

Alteryx was instrumental in the designing and development of the visualization for the workforce dashboard. Without Alteryx the time to complete this project would have easily doubled. By using Alteryx, a single analyst was able to iterate through design and development of both the data set and the dashboard.

The final dashboard includes both tabular and graphic visualizations all displayed from the same data set. The Alteryx workflow uses 19 individual Input Data tools to retrieve data from the 17 tables in Oracle and unions this data into the single data set. Excel spreadsheets are the source for joining the third party benchmark data to the existing data. The extract is output from Alteryx directly to a Tableau Server. By utilizing a single set of data, filtering and rendering in visualization are very performant on 11 million rows of data. (Development included testing data sets of over 100 million rows with acceptable but slower performance. The project was scaled back until such a time as Alteryx Server is available for use.)

Describe the benefits you have achieved

The initial reason for using Alteryx was the ability to perform a UNION ALL on the 19 input queries. By selecting the option to cache queries, output directly to tde files, and work iteratively to determine the best format for the data in order to meet design requirements and provide for the best performance for filtering and rendering in the visualization, months of development time was saved. The 19 data inputs contain over 7000 lines of SQL code combined. Storing this code in Alteryx provides for improved reproducibility and documentation. During the later stages of the project it was fairly straight forward to use the various tools in Alteryx to transform the data to support the additional request for a cross tab view and also to recreate the table calculations to mimic the calculations the visualization. Without Alteryx it would have taken a significant amount of time to recreate these calculations in SQL and re-write the initial input queries.

Our customers are now able to view their Workforce Optimization metrics in a single location. They can now visualize a scenario in which their premium pay has been increasing the last few pay periods and see that this may be attributed to higher turnover rates with longer times to fill for open positions, all within a single visualization. With just a few clicks our leaders can compare their workforce optimization metrics with other hospitals in our organization or against national benchmarks.  Reporting this combination of metrics had not been attempted prior to this time and would not have been possible at this cost without the use of Alteryx.

Costs saving are estimated at $25,000 to-date with additional savings expected in future development and enhancements.

Author: Michael Barone, Data Scientist

Company: Paychex Inc.

Awards Category: Most Time Saved

We currently have more than two dozen predictive models, pulling data of all shapes and sizes from many different sources.  Total processing time for a round of scoring takes 4 hours.  Before Alteryx, we had a dozen models, and processing took around 96 hours.  That's a 2x increase in our model portfolio, but a 24x decrease in processing time.

Describe the problem you needed to solve 

Our Predictive Modeling group, which began in the early-to-mid 2000s, had grown from one person to four people by summer 2012.  I was one of those four.  Our Portfolio had grown from one model, to more than a dozen.  We were what you might call a self-starting group.  While we had the blessing of upper Management, we were small and independent, doing all research, development, and analysis ourselves.  We started with using the typical every day Enterprise solutions for software.  While those solutions worked well for a few years, by the time we were up to a dozen models, we had outgrown those solutions.  A typical round of "model scoring" which we did at the beginning of ever y month, took about two-and-a-half weeks, and ninety-five percent of that was system processing time which consisted of cleansing, blending, and transforming the data from varying sources.

Describe the working solution

We blend data from our internal databases - everything from Excel and Access, to Oracle, SQL Server, and Netezza.  Several models include data from 3rd party sources such as D&B, and the Experian CAPE file we get with out Alteryx data package.

Describe the benefits you have achieved

We recently have taken on projects that require us processing and analyzing billions of records of data.  Thanks to Alteryx and more specifically the Calgary format, most of our time is spent analyzing the data, not pulling, blending, and processing.  This leads to faster delivery time of results, and faster business insight.

Author: Wayne Franklin, Student Experience Evaluation Officer

Company: Charles Darwin University

Awards Category: Most Time Saved

The time saved mainly effects my workload; this in turn allows me to work on other projects for the department which helps the overall organisation. Being a smaller organisation our resources are limited so any time saved makes a significant difference to our overall output. Using Alteryx has quite often saved days of manual work and it significantly reduces the risk of errors.

Describe the problem you needed to solve 

The issue we faced was how best to amalgamate multiple data tables into three new unique excel files to be used by a 3rd party survey tool - Blue eXplorance. While this doesn't sound too difficult, it becomes very time consuming when there are thousands of rows of data in each data source. Being a smaller educational institutional I am at present the only person that works with all the setting up, running and reporting of all the surveys within the university; spending a day or two stuck on setting up one survey can have a detrimental effect on other projects.

Describe the working solution

The old way of doing things: Download each of the data sources which included the full student and unit information for a given semester. This was followed by a series of pivot tables, copy pasting, creating new fields, making things more meaningful (i.e. change 'M' to Male - not much but supervisors like it better that way). Once all that was done I would eventually end up with clean unit, student and relationship files that are set up to be used for 3rd party survey software. This doesn't sound like much but is quite time consuming. I got pretty good at excel formulas which helped cut the time down a little, but still took a day messing around in excel to get the final product. The new way to do things: click run on the Alteryx app I made, wait a minute, done! The app I created allows me to select the files to upload and where to save the output files at the end.

Describe the benefits you have achieved

What started as a solid day or two work is now reduced to a minute wait time as the Alteryx app is running. This frees me up to continue work on other projects I am working on and be a more productive member of our team.

Author: Kiran Ramakrishnan

Awards Category: Most Time Saved 

Through automating processes we received a lot of management attention and a desire to create more automated and on-demand analysis, dashboards and reports.

Another area where we have benefited significantly is training and process consistency. No more are we reliant on training new resources on learning the systems and process or critically affected by sudden departure of a team member.

Describe the problem you needed to solve 

We are a semiconductor company located in the Silicon Valley. We are in business for more than 30 years with 45 locations globally and about 5000 employees. We are in business to solve our customers' challenges. We are a leader in driving innovations in particular for Microcontrollers. The company focuses on markets embedded processing, security, wireless, and touch technologies. In Automotive we provide solutions beyond touch such Remote keyless or networking. Our emphasize is IoT applications. We see a potential in the Internet of Things market combining our products especially MCUs, Security and Wireless Technologies.

In this industry, planning is essential as the market is very dynamic and volatile but manufacturing cycles are long. Most electronic applications have comparatively short product life cycles and sharp production ramp cycles. Ignoring these ramps could result in over/under capacity. For a semiconductor company it is key to clearly understand these dynamics and take appropriate actions within an acceptable time.

To forecast and make appropriate predictions, organizations need critical information such as actual forecast, billing, backlog and bookings. Based on this information Sales, BUs and Finance are able to build models. As End of Life parts convert immediately into revenue we need to treat them separately. Typically semiconductors sales is based on sales commission. Sales commissions are calculated on product category and type. Therefore each line item needs to be matched to a salesperson by product life cycle. In public companies this is done on a quarterly basis and regular updates increase an organization's confidence to achieve set goals. As electronic companies are demanding more and more security levels to data access, consolidated dataset needs to be protected to ensure compliance with customer agreements. Large organizations also require data security to ensure data is only accessible on a need-to-know basis.

Historically, people from these different groups manually created, cleansed and merged data and information into various files and sources to achieve insight. It is common to use different environments such as Oracle DBs, SAP, ModelN, SharePoint, Salesforce, Excel, and Access. This is extremely time consuming and requires a huge manual effort. Usually data consistency between different sources is not guaranteed and requires additional cleansing and manipulation. As every person/group has also their own way to gathering and consolidating this information it typically leads to different results and layout as it is hard for someone outside the group to clearly understand the other person's approach. These reports are regularly necessary a necessity and need to be complied on a weekly/daily basis on the refresh frequencies.  We also want to get independent of resources to update dashboards on demand. Current process makes the reporting heavily reliant on human resources.

Describe the working solution

In Alteryx we found the solution to our problem. Alteryx was utilized to join data sources of in different data formats and environments gathered from different departments including Sales, Finance, Operations/Supply Chain, and Human Resources.

• The Sales department provides Forecast in an Excel worksheet. As the worksheet is being accessed and edited by more than 500 individuals, data inconsistency between fields (such as time dimension) is an ongoing issue and data architecture needs to be re-organized and consolidated.
• The Finance department provides Billings in the format of Oracle Hyperion, where there are data inconsistencies between Billings and Backlog & Bookings due to system differences. Billings need to be merged with Backlog & Bookings to identify EOL parts for commissions and forecast are identified.
• The Operations/Supply Chain department provides Backlog & Bookings through SAP, which also has data inconsistencies between Backlog & Bookings and Billings due to system differences. Backlog and Bookings need to be merged with Billings, and EOL parts for commission and forecast are identified.
• The HR department provides Organization Hierarchy through SAP HANA, in order to apply a row level security on the dashboards later on.

To resolve the issues, all relevant data is structured and follows the overall defined data architecture described in Alteryx. First, Alteryx pulls relevant data from various sources and stores it in a shared drive/folder. Then, Alteryx runs its algorithms based on our definitions. A special script was developed to publish and trigger a refresh of the dashboard with the latest data on a daily basis. Finally, a notification via email is sent to all the users (more than 500) with a hyperlink, once the refreshed data is published.

Describe the benefits you have achieved

Prior to the Alteryx implementation, a lot of time was spent downloading, storing, and consolidating the files, which resulted in multiple unexpected errors which were hard to identify. The accuracy and confidence level of the manually created dashboard was not very high, due to the unexpected human errors. Very often, the dashboards required so much preparation that by the time they were published they were already outdated.

Through the Alteryx approach, we have now eliminated manual intervention and reduced the effort to prepare and publish/distribute the reports to less than 1% compared to previous approach. In addition, through this streamlined approach we have stimulated collaboration on a global basis.

Departments such as IT, Finance, Sales are able to work much tighter together as they are seeing results within an extremely short period of time.

The other advantage of this solution is that it is now broadly being used throughout the organization from the CEO to analysts based on the defined security model.

How much time has your organization saved by using Alteryx workflows?

It used to take us one week to create and develop the workflow. The biggest challenge we faced was to determine the individual steps and the responsible person as various resources and departments were required to contribute.

Through Alteryx workflow we are able to save more than 15 hours per week in data merging alone and at the same time we are now able to publish the reports/analysis on a daily basis. Through Alteryx we are now saving over 75h from various departments to run the process from end-to-end on a daily basis.

What has this time savings allowed you to do?

Through automating the process we received a lot of management attention and a desire to create more automated and on-demand dashboards and reports.

Another area where we have benefited significantly is training and process consistency. No more are we reliant on training new resources on learning the systems and process or critically affected by sudden departure of a team member.