Past Analytics Excellence Awards

Author: Rana Dalbah, Director - Workforce Intelligence & Processes

Company: BAE Systems

Awards Category: Most Unexpected Insight - Best Use Case for Alteryx in Human Resources

Working in Human Resources, people do not expect us to be technology savvy, let alone become technology leaders and host a "Technology Day" to show HR and other functions the type of technology that we are leveraging and how it has allowed us, as a team, to become more efficient and scalable.

Within the Workforce Intelligence team, a team responsible for HR metrics and analytics, we have been able to leverage Alteryx in a way that has allowed us to become more scalable and not "live in the data", spending the majority of our time formatting, cleansing, and re-indexing. For example, Alteryx replaced both Microsoft T-SQL and coding in R for our HR Dashboard, which allowed us to decrease the pre-processing time of our HR Dashboard from 8-16 hours per month to less than 10 minutes per month, which does not account for the elimination of human intervention and error.

With the time savings due to Alteryx, it has allowed us to create custom metrics in the dashboard at a faster rate to meet customer demands. In addition, it has also given us the opportunity to pursue other aspects of Alteryx forecast modeling, statistical analysis, predictive analytics, etc. The fact that we are able to turn an HR Dashboard around from one week to two days has been a game changer.

The HR dashboard is considered to have relevant data that is constantly being used for our Quarterly Business Reviews and has attracted the attention of our CEO and the Senior Leadership. Another use that we have found for Alteryx is to create a workflow for our Affirmative Action data processing. Our Affirmative Action process has lacked consistency over the years and has changed hands countless times, with no one person owning it for more than a year. After seeing the capabilities for our HR Dashboard, we decided to leverage Alteryx to create a workflow for our Affirmative Action processing that took 40 hours of work down to 7 minutes with an additional hour that allows for source data recognition 

recognition and correction.  We not only have been able to cut down a two or three month process to a few minutes, but we also now have a documented workflow that lists all the rules and exceptions for our process and would only need to be tweaked slightly as requirements change.

For our first foray into predictive analytics, we ran a flight risk model on a certain critical population.  Before Alteryx, the team used SPSS and R for the statistical analysis and created a Microsoft Access database to combine and process at least 30 data files.  The team was able to run the process, with predictive measures, in about 6 months.  After the purchase of Alteryx, the workflow was later created and refined in Alteryx, and we were able to run a small flight risks analysis on another subset of our population that took about a month with better visualizations than what R had to offer.  By reducing the data wrangling time, we are able to create models in a more timely fashion and the results are still relevant.

The biggest benefit of these time-savings is that it has freed up our analytics personnel to focus less on “data chores” and more on developing deeper analytics and making analytics more relevant to our executive leadership and our organization as a whole.  We’ve already become more proactive and more strategic now that we aren’t focusing our time on the data prep.  The combination of Alteryx with Tableau is transformative for our HR, Compensation, EEO-1, and Affirmative Action analytics.  Now that we are no longer spending countless hours prepping data, we’re assisting other areas, including Benefits, Ethics, Safety and Health, Facilities, and even our Production Engineering teams with ad-hoc analytics processing.

Describe the problem you needed to solve 

A few years ago, HR metrics was a somewhat foreign concept for our Senior Leadership. We could barely get consensus on the definition of headcount and attrition.  But in order for HR to bring to the table what Finance and Business Development do: metrics, data, measurements, etc. we needed to find a way to start displaying relevant HR metrics that can steer Senior Leadership in the right direction when making decisions for the workforce.  So, even though we launched with an HR Dashboard in January of 2014, it was simple and met minimum requirements, but it was a start. We used Adobe, Apache code and SharePoint, along with data in excel files, to create simple metrics and visuals. In April 2015, we launched the HR Dashboard using Tableau with the help of a third party that used Microsoft SQL server to read the data and visualize it based on our requirements. However, this was not the best solution for us because we were not able to make dynamic changes to the dashboard in a fast timeframe. The dashboard was being released about two weeks after fiscal month end, which is an eternity in terms of relevance to our Senior Leadership.  

Once we had the talent in-house, we were able to leverage our technological expertise in Tableau and then, with the introduction of Alteryx, create our workflows that cut down a 2 week process into a few days - including data validation and dashboard distribution to the HR Business Partners and Senior Leadership.  But why stop there?  We viewed Alteryx as a way to help refine existing manual processes: marrying multiple excel files using vlookups, pivot tables, etc. that were not necessarily documented by the users and cut back on processing time. If we can build it once and spend minimal time maintaining the workflow, why not build it?  This way, all one has to do in the future is append or replace a file and hit the start button, and the output is created.  Easy peasy! That is when we decided we can leverage this tool for our compliance team and build out the Affirmative Action process, described above, along with the EE0-1 and Vets processing.

What took months and multiple resources now takes minutes and only one resource.

Describe the working solution

The majority of the data we are using comes from our HCM (Human Capital Management Database) in excel based files. In addition to the HCM files, we are also using files from our applicant tracking system (ATS), IMPACT Awards data, Benefit provider, 401K, Safety and Health data, and pension providers.

Anything that does not come out of our HCM system are coming from a third party vendor. These files are used specifically for our HR dashboard, Affirmative Action Plan workflow, Safety & Health Dashboard, and our benefits dashboard.

In addition to dashboards, we have been able to leverage the mentioned files along with survey data and macro-economic factors for our flight risk model. We have also leveraged Google map data to calculate the commute time from an employee's home zip code to their work location zip code. This was a more accurate measurement of time spent on the road to and from work when compared to distance.

The ultimate outputs vary: an HR dashboard that tracks metrics such as demographics, headcount, attrition, employee churn/movement, rewards and exit surveys is published as a Tableau workbook. The Flight Risk analysis that allows us to determine what factors most contribute to certain populations leaving the company; a compensation dashboard that provided executives a quick way to do merit and Incentive Compensation planning includes base pay, pay ratios, etc. is also published as a Tableau Workbook.

This workflow has as its input our employee roster file, which includes the employee’s work location and supervisor identifiers and work locations going up to their fourth level supervisor.  For the first step of processing, we used stacked-joins to establish employee’s supervisor hierarchies up to the 8^th level supervisor.  We then needed to assign initial “starting location” for an employee based on the location type.  That meant “rolling up” the employee’s location until we hit an actual company, not client, site.  We did this because Affirmative Action reporting requires using actual company sites.  The roll-up was accomplished using nested filters, which is easier to see, understand, modify, and track than a large ELSEIF function (important for team sharing). 

Once the initial location rollup was completed, we then needed to rollup employees until every employee was at a site with at least 40 employees.  While simply rolling all employees up at once would be quick, it would also result in fewer locations and many employees being rolled up too far from their current site which would undermine the validity and effectiveness of our Affirmative Action plan.  Instead, we used a slow-rolling aggregate sort technique, where lone employees are rolled up into groups of two, then groups of two are rolled up into larger groups, and so on until sites are determined with a minimum of 40 employees (or whatever number is input).  The goal is to aggregate employees effectively, while minimizing the “distance” of the employee from their initial site.  This sorting was accomplished using custom-built macros with a group size control input that can be quickly changed by anyone using the workflow.

The end result was the roster of employees with the original data, with new fields identifying their roll-up location, and what level of roll-up from their initial location was needed.  A small offshoot of “error” population (usually due to missing or incorrect data) is put into a separate file for later iterative correction.

Previously, this process was done through trial and error via Access, and Excel.  That process, was not only much slower and more painstaking, but it also tended to result in larger “distances” of employees from initial sites then was necessary.  As a result, our new process is quicker, less error-prone, and arguably more defensible than its predecessor.

One of the Macros used in AAP:

Describe the benefits you have achieved

Alteryx has enabled our relatively small analytics shop (3 people) to build a powerful, flexible and scalable analytics infrastructure without working through our IT support.  We are independent and thus can reply to the user's custom requests in a timely fashion.  We are seen as agile and responsive - creating forecasting workflows in a few days to preview to our CEO and CHRO instead of creating Power Point slides to preview for them a concept.  This way, we can show them what we expect it to look like and how it will work and any feedback they give us, we can work at the same time to meet their requirements.  The possibilities of Alteryx, in our eyes, are endless and for a minimal investment, we are constantly "wowing" our customers with the service and products we are providing them.  In the end, we have been successful in showing that HR can leverage the latest technologies to become more responsive to business needs without the need for IT or developer involvement.

Author: Alberto Guisande (@Aguisande), Services Director

Awards Category: Most Unexpected Insight - Proving teachers wrong - Apples & Oranges can be compared! (thanks to Alteryx)

Describe the problem you needed to solve 

Our customer is a Public Transportation company, in charge of buses going around the city of Panama. They transport more than 500K passengers a day (1/6 of the total population of the country). Almost 400 routes, with 1,400 buses going around the city all days, working 24/7, reporting position every a few seconds. The company is supporting its operation with a variety of tools, but at the time to put all data together, they realized there was no "point of contact" in the data. They have to compare apples & oranges! Really? Why does the saying exist? Because you can't! So we started trying to do the impossible!

BTW, the business questions are pretty simple (once you got the data!): What route was every bus in, when every transaction occurred? What is the demand of every route? and for every stop?

Describe the working solution

Working with Alteryx, we were able to analyze data coming from three different sources, where the only common information was some LATITUDE & LONGITUDE (taken with different equipment, so the accuracy was, at least, questionable) at some random points in time. The data was received in several files:

• Routes: Contains the ID & the name of every route. Stop Points: Containing every bus stop, its LAT & LONG, and the stop name
• Pattern Detail: Containing every route, its stops and the sequence of those stops in a route
• Some remarks: A lot of stops are used by different routes, and there are some stops, where the bus pass through, that are not part of the specific route the bus is at

So far, the easy part! We managed very easily to get all this info together. Now the tricky part: There mainly two operational datasets: AVL (Every position of every bus, every n seconds, where n is an arbitrary number between 0 and what the piece of hardware wanted to use). BTW, a huge amount of data every day.

Transactions: transactions registered in time, in a bus. As you may infer, there are no data in common that allow us to match records beside an arbitrary range of latitude and longitude in some random time ranges. Because of how everything is reported, the bus may be passing in front a stop that is part of another route, or stopping far from the designated stop.

Describe the benefits you have achieved

With this solution, the company can start analyzing activity per route, demand per bus, route, stop, etc. Without Alteryx, this customer information still be looking like apples and oranges! We were able to make it sense and allow them to use it to get insights.

Colorful note(and some ego elevator) : 5 other vendors took the challenge. No other one could reach a glimpse of solution (of course, "no Alteryx, no gain").

Author: Mark Frisch (@MarqueeCrew), CEO

Company: MarqueeCrew

Awards Category: Name Your Own - Macros for the Good of All Alteryx Users

Describe the problem you needed to solve 

Creation of samples goes beyond random and creating N'ths.  It is crucial that samples be representative of their source populations if you are going to draw any meaningful truth from your marketing or other use cases.  After creating a sample set, how would you verify that you didn't select too many of one segment vs another?  If you're using Mosaic (r) data and there are 71 types to consider did you get enough of each type?

Describe the working solution

Using a chi-squared test, we created a macro and published the macro to the Alteryx Macro District as well as to the CReW macros  (www.chaosreignswithin).  There are two input anchors (Population and Sample) and the configuration requires that you select a categorical variable from both inputs (the same variable content).  The output is a report that tells you if your representative or not (includes degrees of freedom and the Chi square results against a 95% confidence interval).

Describe the benefits you have achieved

My client was able to avoid the costly mistake that had plagued their prior marketing initiative and was setup for success.  I wanted to share this feature with the community.  It would be awesome if it ended up helping my charity, the American Cancer Society.  Although this isn't quite as sexy as my competition, it is sexy in it's simplicity and geek factor.

Author: Andrew Kim, Analyst (@andrewdatakim)

Awards Category: Name Your Own - Scaling Your Career with Alteryx

Describe the problem you needed to solve 

Deciding on a tool to invest your time in is a problem everyone faces in their career. Learning to blend the tools given to us in college versus what the professional world is actually using are starkly different. I have quickly discovered to have a career that has both the opportunity to start from a company from scratch and the flexibility to work in a Fortune 100 environment requires the knowledge of assets that can scale without a significant investment of time or money.  My background is in Marketing and Finance with most of my work experience in small to midsize companies where every person is required to be/do more for the company to survive.

Describe the working solution

I set out to find these tools 3 years ago with the understanding that information drives a business, which lead me to Gartner report. I went through trials of a dozen different options and even had contracted assistance from a developer of one of the software options. Alteryx quickly became my option of choice which greatly contributed to my  previous company's growth from $250k in annual revenue online to $12 million in 2 years. The ability to access multiple data source types, leverage Amazon MWS data and use historical competitive landscape information allowed us to create the perfect dashboards in Tableau to analyze current inventory and buying opportunities that were previously inconceivable.  I was able to save 10,000 labor hours a day in discovering new products. Prior to Alteryx being purchased the average buyer's assistant could run 200 Amazon listings per 8 hour day. After Alteryx we were retrieving over 250,000 listings per run multiple times a day (The math: 250,000/25 listings per hour=10,000 hours per run). The primary customer in this scenario were the buyers for the company. By taking all of the data processed through Alteryx and providing them with Tableau dashboards to conveniently view current and historical product information compared to the previous Excel models we were able to maximize inventory turnover and margins.

Describe the benefits you have achieved

Alteryx knowledge allowed me to advance to my current company and position in a Fortune 50 company where I am a Data Analyst/Programmer. I now work heavily with survey data and again Alteryx has proven an indispensable asset even with the change in scale. Its versatility has allowed all of my skills to transfer from operational data to qualitative without skipping a beat. I find Alteryx is an asset that has only increased my passion for data and I am eager to see how I can continue to scale my career with it.

Author: Thomas Ayme, Manager, Business Analytics

Company: Adidas International Trading B.V

Awards Category: Name Your Own - Best Planning and Operational Use

Describe the problem you needed to solve 

As a new and successful business adidas Western Europe eCommerce keeps on growing faster and faster; new services are being launched every week, an increasing number of marketing campaigns are being driven simultaneously, etc. This leads to more and more products having to be shipped out every day to our end consumers.

This strong growth leads to an exponential increase of the complexities when it comes to forecasting our units and orders volumes, but also to bigger costs in case of forecasting mistakes or inaccuracies.

As these outbound volumes keep on increasing, we were being faced with the need to develop a new, more accurate, more detailed and more flexible operational forecasting tool.

Such a forecasting tool would have to cater to the complexities of having to forecast for 17 different markets rather than a single pan European entity. Indeed, warehouse operations and customer service depend on a country level forecast to plan carriers and linguistic staff. This is a very unique situation where on top of having a rapidly growing business we have to take into account local marketing events and markets specificities.

Finally, given the importance of ensuring consumer satisfaction through timely delivery of their orders, we also decided to provide a daily forecast for all 17 markets rather than the usual weekly format. Such a level of details improves the warehouse's shipping speed but also increase once again the difficulty of our task.

Describe the working solution

Our first challenge was to find reliable sources of information. Both business analytics (financial and historical sales data) and web analytics (traffic information) data were already available to us through SAP HANA and Adobe Analytics. However, none of our databases were capturing in a single place all information related to marketing campaigns, project launches, events, adhoc issues, etc.

That is why we started by building a centralized knowledge database, which contains all past and planned events that can impact our sales and outbound volumes.

This tool is based on an Alteryx workflow, which cleans and blends together all the different calendars used by the other eCommerce teams. In the past, bringing those files together was a struggle since some of them are based on Excel while others are on Google Sheets, moreover, all are using a different format.

We made the best of this opportunity of now having a centralized event database by also developing a self-service visualization tool in Tableau, which displays all those past and future events. Such a dashboard is now used to:

1. Give some background to our stakeholders about what is driving the volumes seen in the forecast.
2. Have an overview of the business during our review the sales targets of the coming weeks, etc...

In a second time we created a workflow, which thanks to this new centralized event database, defines for each past and upcoming days as well as for each markets a set of "genes". These genes flag potential adhoc issues, commercial activations, level of discount, newsletter send outs, etc.

This gene system can then be used to define the histoical data to be used to forecast upcoming periods, by matching future and past days that share the same or at least similar genes. This is seen as the first pillar of our forecasting model.

The second pillar of our forecasting tool is a file containing our European weekly targets. These targets are constantly being reviewed based on new events shown in the centralized event database and current business trends. 

An Alteryx workflow derives from this target file our sales expectation for each upcoming day, market, category (full price, clearance) and article type (inline or customized). In order to do so, we use historical data defined by our genes in addition to a set of algorithms and calculate the sales impact ratio of each market and category. These ratios are then used to allocate a target to each one of the combination.

Finally, both pillars are brought together and we derive in a final Alteryx workflow, how many orders and units will have to be placed in each markets and from which article type.

However, since certain periods of time have genes combinations that cannot be matched, our working solution also gives us the flexibility to manually override the results. These forecast volumes are then shared with the team, warehouse, customer service call centers, etc. through a Tableau dashboard.

Describe the benefits you have achieved

Thanks to the work that went into developing this new forecasting model in Alteryx, the adidas WE eCommerce business ended up getting:

• A more accurate forecasting model, which allows for a better planning of our operations.
• Reduced operational costs.
• A more detailed forecast as we can now forecast on a daily level, when past methods required much more work and limited us to a weekly forecast.
• A flexible forecasting model that can easily be modified to include new services and sales channels.
• A forecast dashboard that lets us easily communicate our forecast to an ever growing number of stakeholders.
• A centralized event “calendar” that can be used by the entire department for much more than simply understanding the forecast (e.g. it is used to brief in Customer Service teams on upcoming events).
• A massive amount of free time that can be used to drive other analyses, as it is not required from us anymore to manually join together different marketing calendars and other sources of information, create manual overviews of the upcoming weeks, manually split our weekly sales target, etc.

Authors: Irina Mihai (@irina_mihai) , Web Analyst 

                  Johannes Wagner, Senior Business Analyst

Company: Adidas International Trading B.V.

Awards Category: Name Your Own - Creating the New

Describe the problem you needed to solve 

The ecommerce business division was facing the challenge of keeping track and steering the performance of over 9000 articles.

Senior management had an overview of top level numbers but the actual people who could take action and steer the business on operational level had limited information.

Merchandizers tracked the sales of only most important product franchises which generated roughly 60% of the business, but they did not have an overview of article size availability and warehouse stock which was vital in order to know whether getting more online traffic for the article would lead to more sales or actually disappointed customers who didn't find their size. Besides stock information, merchandizers also needed BI data and web analytics data in order to have a holistic understanding of article and franchise performance, a situation which caused delays in acting upon information and steering the business proactively.

Even so, the full product range and especially the low-key franchises (40% of the business) were reported on an ad-hoc basis. No actions were taken on the less important franchises which led to unrealized opportunities, as unsold products are heavily discounted at the end of the season.

Given this complex business environment and time needed to get hold of data which even becomes obsolete before reaching the relevant stakeholders in a digestible format, we needed to give transparency on all product franchises and provide all the relevant information needed to take actions and drive the business on both aggregated and granular level, in real time, in one place, available to everyone, in an automated way.

To sum up, the drivers that led to a new way of working within analytics were:

• Tracking ongoing performance on all articles improves our margin so that we can drive sales during the season and avoid heavy discounting at the end of the season. Offering too many discounts also has a negative long-term impact on the brand and educates consumers to buy on discount, so we wanted to make sure we maximize opportunities within season.
• Besides immediate financial returns, we are also thinking of the consumer experience and the fact that not finding their desired sizes online disappoints customers. Being able to drive demand planning proactively and ensure enough supply is available is a way to keep customers happy and returning to our site.

Describe the working solution

Alteryx has allowed us to tap into multiple sources of data in a fast, scalable way not possible before, which allows us to be truly agile and data driven as an organization.

On a high level, the data sources used in the workflow are:

• BI data incl. sales data and standard margin per article per day
• Waiting List data from the CRM system indicating the number of times an out of stock product was placed on the waiting list
• Article master data from the range management application
• Demand planning master data with the estimated bought quantity per size which defines the relative importance of each size of an article
• Web analytics data for product views and conversion rate  
• Stock quantity data from the online platform with the daily stock snapshot on size level
• Product range files manually maintained for retail intro date,  marketing campaign information, and original sales forecast quantity per month

1. There are 3 work streams used in the main workflow:
   1.1 Calculation of daily sales forecasts per article number based on the product range files and master data file.

Several operations are done to clean up the data but the most important part is transforming the monthly forecast to a daily level also taking into account the retail intro date. For example if an article has a retail intro date in the middle of the month, we only generate a forecast for the days after that date and not before, to maintain accuracy.

1.2 Data cleanse operations done on web analytics and BI data and subsequent join on article and day level

For each data type we have created a historical Alteryx database that gets unioned with new cleansed data, which then gets written into the historical database.

1.3 Join of the daily sales forecast with the web analytics data, BI data and wishlist data on article and day level

Here we also calculate the actual retail intro date for each article based on the first day when the product gets online traffic, thus allowing us visibility on products that were launched late.

2. In a second workflow we calculate the stock availability per article size and size and buy availability per article. This is based on the master data file indicating the buy percentage per size and article and stock snapshot indicating the size availability per article. The output is a Tableau data extract.

The outputs of the two workflows are then visualized in a Tableau dashboard that has a flow-like structure allowing users to see performance of the product franchises on high level and also drill down into details on article level:

Describe the benefits you have achieved

First of all, without Alteryx the Trading Dashboard would not have been possible due to the sheer amount of data sitting in different systems and manual work involved in retrieving and combining it at the same level of granularity.

Alteryx has allowed us the possibility to blend a variety of data sources in a scalable way and achieve the following business benefits:

• In terms of time savings, prior to using Alteryx, two full time employees would have been needed to compile an in-season daily snapshot of the most important product franchises (60% of the business) with all the relevant metrics. By the time this report reached stakeholders, the information would have been obsolete and irrelevant to quickly react to consumer behavior in real time. Now with the help of Alteryx it takes 10 minutes per day for the analytics team to provide a holistic dashboard to both senior management and employees who can take quick decisions and steer the business based on real-time data.
• Increased revenue and margin optimization: Our merchandisers and category managers now have a daily complete overview of how each and every single article is performing. Due to the exploratory and intuitive nature of the dashboard (from top level to detailed article level and coloring based on forecast achievement) they can easily identify which product franchises and individual products are falling behind sales forecast and what specific levers to pull in order to increase sales. Example actions are driving more traffic, improving on-site merchandising, restocking particular sizes, decreasing the price.
• Customer satisfaction: as sizes are restocked faster than before due to the new proactive way of working of the demand planning department, consumers are also happier that they can purchase their desired sizes. This leads to more customers returning to our site because they know here they can find sizes that are not available in retail stores.

We have recently introduced the Trading Dashboard and there is already a mindset shift happening where different departments work more closely together to identify opportunities and act based on the data. We believe Alteryx has enabled us to reach our ambitious growth targets, improve customer satisfaction and operate as a data driven organization.

Author: Suzanne McCartin (@SMCCARTI) , Sr. Ops Reporting Analyst

Company: Nike, Inc.

Awards Category: Name Your Own - Get Back In Time

Describe the problem you needed to solve 

My two my personal favorite Nike values are 'Simplify and Go' and 'Evolve Immediately'!    In Nike Apparel Global Product Creation Operations.  Our immediate need was to replace a critical and core data set on a tight timeline.  Making sure our product creation centers didn't lose buy tracking visibility.     Buy readiness is the measure and metric for garment commercialization.  Do we have everything we need to purchase?   This was just the beginning...

Describe the working solution

The buy ready metric process was implemented using a combination of tools and the first step was to replace the one data source, adding Alteryx to the tool mix.  The build process was then reconstructed and migrated to Altetyx using blending and in-database tools.  Going from about a 5 hour process to 1 hour.

The next follow up solution was to upgrade the report generation processes.  The first solution was one process for each output, and each one had its own data collection process.  Each of these solutions was moved to one workflow using the same data collection process.   Allowing me to enforce Nike's single version of the truth mantra!  This solution  has all kinds of data cleaning , mapping, and shaping.

Describe the benefits you have achieved

The first round benefit was getting the upgrade done and we did so with improved accuracy and data visibility.    The real benefit was to allow the process to get us back to the future and we are lined up to better collaborate with IT and move to Tableau and other new platforms!

Author: Jeffrey Jones (@JeffreyJones), Chief Analytics Officer  

Company: Bristlecone Holdings

Awards Category:  Name Your Own - Most Entertaining (but Super-Practical) Use of Alteryx

Describe the problem you needed to solve 

Our marketing department needed a working Sex Machine, but that sort of thing was strictly prohibited in our technology stack.

Describe the working solution

Analytics built a functional Sex Machine! Let me explain...

Because our business involves consumer lending, we absolutely cannot -- no way no how -- make any kind of decisioning based on sex or gender. Regulators don't want you discriminating based on that and so we don't even bother to ask about it in our online application nor do we store anything related to sex in our database. Sex is taboo when it comes to the Equal Opportunity Credit Act. But the problem was that the marketing department needed better insight into our customer demographics so that they could adjust their campaigns and the messaging on our website, videos, etc., based on actual data instead of gut instinct.

Well, it turns out the Census Bureau publishes awesome (and clean) data on baby names and their sex. So we made a quick little workflow to import and join 134 years of births in the U.S. resulting in over 1.8 million different name/sex/year combinations. We counted the occurrences, looked at the ratio of M to F births for each and made some (fairly good) generalizations about whether a name was more likely a "Male" name or "Female" name. Some were pretty obvious, like "John." Others were less obvious, like "Jo." And some were totally indeterminate, like "Jahni."

Then we joined this brand new data set to an export of our 200k customer applications and were able to determine the sex of around 90% our applicants fairly reliably, another 7% with less reliability, and only 3% as completely unknown. The best thing about it is that we were able to answer these questions completely outside our lending technology stack in a manner disconnected from our decisioning engine so as to maintain legal compliance. We also didn't have to waste any money or time on conducting additional customer surveys.

This was literally something that was conceived in the middle of the night and had been born into production before lunch on the following day. (bow-chicka-bow-bow) Doing this wouldn't have been just impossible before Alteryx, it would have been LAUGHABLY IMPOSSIBLE. Especially given the size of the third-party data we needed to leverage and the nature of our tech stack and the way regulation works in consumer lending.

Describe the benefits you have achieved

It sounds silly, but our organization did realize tangible benefit from doing this. Before, we had no idea about a critical demographic component for our customers. It's impossible to look a bank of nearly 200k names across four totally unrelated industry verticals and conclude with any kind of confidence sex-related trends. Now we can understand sex-related trends in the furniture, bridal, pet, and auto industries. We can link it to the products they're actually getting and tweak the messaging on our website accordingly. And what's more, we're able to do all this in real-time going forward without wasting any of our DBAs' time or distracting our legal department. This probably saved us a hundred man-hours or more given all the parties that would have needed to get involved to answer this simple demographic question.

We should probably tidy up this workflow and the .yxdb because it might be useful for other companies who want to get a full demographic breakdown but don't have any pre-existing information on customer sex. If anybody wants to know the total number of people born with every name for the last 134 years and needs the M:F occurrence ratio for each, holler at me.

Author: Francisco Aristiguieta, Audit Specialist

Awards Category: Name Your Own - Best Engagement From Management

Describe the problem you needed to solve 

With operations in all time-zones and more than 10,000 people, my company needed an effective way to ensure we don't have rouge employees exposing us to corruption.

Before our Alteryx tool, we had a very complete compliance program focused on prevention; but we did not had a viable method to verify the mandates were understood and followed across the globe.

Describe the working solution

Our plan was to every month inspect every payment the company had done for signs of potential problems. We would do this by searching each invoice line for keywords that could represent problems.

The plan was simple, although the implementation would have been an enormous problem if we had not had Alteryx.  Here are a few of the (multiple) humps Alteryx helped us address:

1. Payment information was broken in multiple tables. Even if we would be working with Oracle data, our IT department insisted that we worked with off-line copies of the tables instead of connecting directly. This made our data source a series of multiple monthly csv tables, where the tables had no meaning on their own.

>> Importing all files in a folder, and using "Unique", "Filter", "Select" and "Join"; allowed me to conquest this first challenge.

2. I used "find replace" to do the keyword searches; which was a great step forward. Sadly, in many cases our chosen "keywords" were part of innocent words, which caused a plague of false positives for follow-up. i.e. the word "magenta" would be caught when we searched for "agent". 

>> Using "Formula" to set-up some "If-Then-Else statements", and carefully using "and" to set-up my conditions, I was able to safe list some of these innocent words and get rid of a large portion of these false positives.

3. Because the outputs of each run is stored separately, my last big challenge was making sure I didn't report/investigate the same transactions month after month as we re-ran the tests.

>> Solving this was easy through a collection of file imports, "union", and "join" to compare the current results to the recent past (keeping only new hits) in my analysis.

Describe the benefits you have achieved

Even if (after follow-up) the tests have not found any real problems, we are very happy to finally have peace of mind regarding how our employees are behaving across the world. This test was a great way to demonstrate the value of analytics to the more traditional pockets of our company, and its results have been greatly celebrated, giving me and my team some great exposure to the highest levels of my organization. Here are a few quotes from our clients:

• "This is another SUCCESS for the Data Analytics initiative.  There is NO WAY we would have ever even known this was an issue without this capability "
• "I believe that this proactive approach is clearly one of the most significant advances in early detection techniques that (the team) has implemented in quite a while"
• "The mere fact that the word will get out that we have tools like this to potentially catch such payments should be a powerful deterrent"
• "Our analytics practices have changed the way we (work) increasing our effectiveness and efficiency"
• "I am looking forward  to work on another (analytics) initiative with (the team)"