Past Analytics Excellence Awards

Suggest an idea

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.

 

Picture1.png

 

Picture2.png

 

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:

 

Picture3.png

 

Join All Files, Suppress Duplicates, Insert to Tables:

 

Picture4.png

 

Generate QC Report:

 

Picture5.png

 

Workflow Overview:

 

Picture6.png

 

QC Report Example:

 

Picture7.png

 

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: 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: Jeffrey Jones (@JeffreyJones), Chief Analytics Officer  In-2CRev-28px-R.png

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.