# Alter.Nation

Community news, customer stories, and more!
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

## #SANTALYTICS Part 1: Solution and Behind the Data

Alteryx Community Team

Solution

If you’re reading this blog post, then you probably participated in the first of four exercises that will constitute the Santalytics challenge - an inclusive, non-denominational, festive excuse for some friendly competition using Alteryx. It also means your spot on Santa’s nice list is secured AND you’re going to be both warm and stylin’ this winter season in your Alteryx swag. That is, if you’ve successfully been able to use Alteryx to improve the elves’ situation from last year:

We’ve gone ahead and attached a detailed solution for Santalytics Part 1 to this post (v10.6), but the highlights are below:

Classifying the presents is short work with the Tile Tool – just use the Equal Records method (20 tiles) and it’ll sort and bin for you. Be sure to change the price field to a numeric type first!

*the table shown above is a sampled view of the solution dataset

Classifying the kids into groups that’ll correspond to those present groups is a little more work:

• The naughty actions need to be subtracted from nice actions to find out how “naughty” vs “nice” each kid was. We used a little formula logic to make naughty actions negative in degree and summarized (sum), but there are other approaches.
• Once a score is calculated for reach Recipient ID, you can join each kid to their respective naughty/nice score and use the same Tile Tool approach (25 tiles, to account for naughty ratings) as the above.
• Santa, despite doing this for centuries, didn’t have all his naughty or nice classifications in an order than could be easily joined (or, err, read). While the exact naughty order doesn’t matter (they’re all getting coal), the nice classifications could have been reversed to make more sense for the increasing degrees of niceness (note: this isn’t necessarily required, as the classification text doesn’t affect the present distribution – only the order, which should have been determined by naughty/nice score). We did this with formula logic as well.

*the table shown above is a sampled view of the solution dataset

Once all the pieces above have been worked through you have naughty/nice ratings for each kid that correspond to their gift tier and exclude the last 5 groupings – the naughty kids. The wedgy giving, loogie hocking, terrors of the world. Of which, you should have taken special notice of Willie Carr, who can be safely presumed as the devil:

Willie Carr on Christmas morn

Big thanks to our #SANTALYTICS Part 1 participants @JohnJPS@pcatterson@patrick_digan@ZacP@nsmith04@MichelKars@cor@CailinS@Treyson (and team), @JordanB@lminors@MarqueeCrew@tom_montpool@jdunkerley79@AlexKo@JonA@SophiaF@Joe_Mako@emilienl@Philip@AudreyL

Behind the Data

We hear the adage time and time again – an analysis is only as good as the data behind it. So how, then, did we get all of the festive data for the Santalytics series? Well, if you want to believe in the magic of Santa, the short answer is that his elves sent it all over to us and asked for help. If you want to believe in the magic of Alteryx, the long answer is below (spoilers!):

Spoiler

Good ‘ol fashion data wranglin’ of course.

The data stores we used came from a handful of unique sources/methods. They also required some processing to shape the raw datasets to a format we were comfortable staging the Santalytics questions around.

Admittedly, at first pass the process looked a lot more like the below:

We quickly discovered wrangling data from scratch is no easy task for the uninitiated and we set out just like anyone without a clue - with baby steps. The first step was to make sure we could do a present delivery-type scenario by having a number of kids located across the globe. This would require spatial data in the form of coordinates or real addresses. Luckily, we found a tool just for that - the Mockaroo realistic data generator. Mockaroo made it extremely easy to specify fields for names and latitude/longitude pairs and randomly generate 1,000 data points at a time:

Knowing that spatial processing often ends up to be fairly computation-heavy, we only generated 16 batches of 1,000 and filtered to 15,000 data points that were confirmed to geocode. Once we had our spatial data for locating the kids accounted for, it was time to figure out if these kids were naughty or nice.

This led us to one of the finer gems of the holiday season: the Claus.com Nice-o-meter. If you’re unfamiliar with the Nice-o-meter, it’s the scientific measure of who’s naughty or nice based on their first and last names - and it is measured to 100% accuracy:

Using the infallible Nice-o-meter and other naughty/nice calculators on the web, we determined a handful of classifications for naughty or nice kids. We then created naughty or nice actions to fit the kids to those classifications by hand:

Once the classifications and actions were identified, we only needed to decide what kids did which actions throughout the course of the year. We figured the best way to do so would be to create 12 months worth of action logs that resembled transactional data in formatting. This was made easy with the Generate Rows Tool (to create month and day records) and the RandInt() function (to randomly assign a number of actions to a day, as well as the specific action). The workflow that built the action logs is visualized below:

Last but not least, we had to determine presents that would be awarded to the nice kids. After considering a number of different APIs and flat file data stores, we chose the BestBuy Products API because it included accurate product shipping weights and had relative ease of access. Using their Products Bulk Download option and our Download Tool, we established an API connection (coding approach) to sweep 57,000 physical products (after parsing) to be filtered down for distribution to the nicest kids this year:

With our product data, spatial data, and naughty/nice actions and rankings determined we were ready to proceed with the gauntlet of Santalytics challenges. And with that, we’re happy to announce week 2 of the challenge. Help Santa out again this week to secure your spot on the nice list!

We're just getting started. We told you this was a 4-part series. Let's determine some trade areas for Santa. You'll have to be pretty spatial, -er- clever to solve this one.

Part 2 is now live. Be sure to use the data from the Part 1 solution attached below!

Matthew DeSimone
Community Content Engineer

Coming from the old data warehousing paradigm in big pharma, Matt found Alteryx on the first day of the rest of his life. You can now find him doing what he loves with his days from the Broomfield developer office - supporting clients in their analytical pursuits by cultivating great Community content, wearing Hawaiian shirts, and tinkering with varying API connections. Matt has a MS in Biomedical Engineering & Bioinformatics and is passionate about all things integrated data management and analytics.

Coming from the old data warehousing paradigm in big pharma, Matt found Alteryx on the first day of the rest of his life. You can now find him doing what he loves with his days from the Broomfield developer office - supporting clients in their analytical pursuits by cultivating great Community content, wearing Hawaiian shirts, and tinkering with varying API connections. Matt has a MS in Biomedical Engineering & Bioinformatics and is passionate about all things integrated data management and analytics.

16 - Nebula

@MattD Santa sure is feeling generous this year! The group of kids below had a negative naughty/nice score and still get presents! In the true spirit of Ebenezer Scrooge I added a formula tool to my workflow to "reclassify" this group as naughty by saying that anything with a naughty/nice rating less than 0 must be naughty. Bah Humbug!

 First Last Sum Actions (Naughty-Nice) Naughty/Nice Rating Naughty/Nice Score Present Score Classification Ronald Cook -1 20 20 20 Recently moved from naughty list over to my nice list. Willie Morrison -1 20 20 20 Recently moved from naughty list over to my nice list. Barbara Howell -1 20 20 20 Recently moved from naughty list over to my nice list. Sarah Gomez -1 20 20 20 Recently moved from naughty list over to my nice list. Louise Davis -1 20 20 20 Recently moved from naughty list over to my nice list. Denise Pierce -1 20 20 20 Recently moved from naughty list over to my nice list. Susan Lynch -1 20 20 20 Recently moved from naughty list over to my nice list. Cheryl Ferguson -1 20 20 20 Recently moved from naughty list over to my nice list. George Ruiz -1 20 20 20 Recently moved from naughty list over to my nice list. Lois Washington -1 20 20 20 Recently moved from naughty list over to my nice list. Christopher Reynolds -1 20 20 20 Recently moved from naughty list over to my nice list. Rebecca Carter -1 20 20 20 Recently moved from naughty list over to my nice list. Jack Watkins -1 20 20 20 Recently moved from naughty list over to my nice list. Ryan Cook -1 20 20 20 Recently moved from naughty list over to my nice list. Paul Russell -1 20 20 20 Recently moved from naughty list over to my nice list. Gregory Roberts -1 20 20 20 Recently moved from naughty list over to my nice list. Joshua Lynch -1 20 20 20 Recently moved from naughty list over to my nice list. Joseph Harper -1 20 20 20 Recently moved from naughty list over to my nice list. Paula Bradley -1 20 20 20 Recently moved from naughty list over to my nice list. Lisa Gomez -1 20 20 20 Recently moved from naughty list over to my nice list. Steven Wagner -1 20 20 20 Recently moved from naughty list over to my nice list. David Hawkins -1 20 20 20 Recently moved from naughty list over to my nice list. Christine Smith -1 20 20 20 Recently moved from naughty list over to my nice list. Randy Olson -1 20 20 20 Recently moved from naughty list over to my nice list. John Lawson -1 20 20 20 Recently moved from naughty list over to my nice list. Robert Phillips -1 20 20 20 Recently moved from naughty list over to my nice list. Ann Chavez -1 20 20 20 Recently moved from naughty list over to my nice list. Jacqueline Cox -1 20 20 20 Recently moved from naughty list over to my nice list. Christine Riley -1 20 20 20 Recently moved from naughty list over to my nice list. Kathleen Scott -1 20 20 20 Recently moved from naughty list over to my nice list.
Alteryx Community Team

@patrick_digan it wouldn't be the holidays without the spirit the of giving! Since Santa binned the kids into evenly distributed groups, those kids on the fence just missed getting coal this year... Next year they might not be so lucky!

16 - Nebula

@MattD Using exactly even bins (instead of approximately even bins by splitting on the sum actions field) certainly made for some tough decisions for Santa! It looks like the following kids with the same score of -1 won't be getting presents this year!

 ID First Last Sum Actions (Naughty-Nice) Naughty/Nice Rating Naughty/Nice Score Classification Alignment 13934 Stephanie Grant -1 21 21 Almost got a present this year - but still naughty! Naughty 13953 Kathy Armstrong -1 21 21 Almost got a present this year - but still naughty! Naughty 14360 Carl Stanley -1 21 21 Almost got a present this year - but still naughty! Naughty 14418 Ryan Dixon -1 21 21 Almost got a present this year - but still naughty! Naughty 14709 Mildred Richards -1 21 21 Almost got a present this year - but still naughty! Naughty 14868 William Gutierrez -1 21 21 Almost got a present this year - but still naughty! Naughty 14994 Marie Wilson -1 21 21 Almost got a present this year - but still naughty! Naughty 1571 Brian Schmidt -1 21 21 Almost got a present this year - but still naughty! Naughty 2055 Doris Hill -1 21 21 Almost got a present this year - but still naughty! Naughty 2108 Laura Gutierrez -1 21 21 Almost got a present this year - but still naughty! Naughty 211 Diane Castillo -1 21 21 Almost got a present this year - but still naughty! Naughty 2259 Anthony Ryan -1 21 21 Almost got a present this year - but still naughty! Naughty 2386 Billy Bailey -1 21 21 Almost got a present this year - but still naughty! Naughty 3222 Amy Diaz -1 21 21 Almost got a present this year - but still naughty! Naughty 3244 Jessica Gibson -1 21 21 Almost got a present this year - but still naughty! Naughty 3399 Donna Hall -1 21 21 Almost got a present this year - but still naughty! Naughty 3407 Patrick Howard -1 21 21 Almost got a present this year - but still naughty! Naughty 37 Ralph Boyd -1 21 21 Almost got a present this year - but still naughty! Naughty 3936 Carlos Kelley -1 21 21 Almost got a present this year - but still naughty! Naughty 4357 Matthew Hernandez -1 21 21 Almost got a present this year - but still naughty! Naughty 4401 Howard Elliott -1 21 21 Almost got a present this year - but still naughty! Naughty 4444 Jerry Dixon -1 21 21 Almost got a present this year - but still naughty! Naughty 4835 Sarah Montgomery -1 21 21 Almost got a present this year - but still naughty! Naughty 5039 Janice Alvarez -1 21 21 Almost got a present this year - but still naughty! Naughty 5284 Kathryn Brooks -1 21 21 Almost got a present this year - but still naughty! Naughty 5867 Rebecca Franklin -1 21 21 Almost got a present this year - but still naughty! Naughty 5944 Julia Martinez -1 21 21 Almost got a present this year - but still naughty! Naughty 5945 Justin Palmer -1 21 21 Almost got a present this year - but still naughty! Naughty 6422 Julia Walker -1 21 21 Almost got a present this year - but still naughty! Naughty 7550 Eric Wood -1 21 21 Almost got a present this year - but still naughty! Naughty 7683 Sandra Gomez -1 21 21 Almost got a present this year - but still naughty! Naughty 7747 Christine Stevens -1 21 21 Almost got a present this year - but still naughty! Naughty 8069 Martin Robinson -1 21 21 Almost got a present this year - but still naughty! Naughty 8449 Kimberly Daniels -1 21 21 Almost got a present this year - but still naughty! Naughty 9028 Dorothy Harris -1 21 21 Almost got a present this year - but still naughty! Naughty 9125 Joshua Dunn -1 21 21 Almost got a present this year - but still naughty! Naughty 9169 Jeremy Moore -1 21 21 Almost got a present this year - but still naughty! Naughty 9758 Margaret Rose -1 21 21 Almost got a present this year - but still naughty! Naughty
5 - Atom

@MattD just curious as to why the Multi-Field Binning tool (setup: Naughty/Nice Rating field for binning, # of tiles - 25 equal records) doesn't equate to the same result as the Tile tool with the Equal Records function. The multi-field binning would result in 12,038 Nice versus 13,000 with the Tile tool shown in your example. By binning on only one field it would make sense that discretization techniques would match the tile tool or am I missing something in the design of Alteryx? Thanks so much! Really enjoying this exercise.

Nick

5 - Atom

Thanks