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
Alteryx Community Team
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:

 

 Elf.jpg

 

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!

 

Present Solution.png

                                                                                                                                                 *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.

 

NaughtyNice Solution.png

                                                                                                                                         *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:

 

williecarr.png

 

williecarr.jpg

                                                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
data wranglin 1.png

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:

data wranglin 2.png

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:


mockaroo.png

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:


niceometer.png

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:

nice.png                      naughty.png

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:


Blog Post 1 Action Data.png

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:

Blog Post 1API Data.PNG

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!

 

Ready for Part 2?

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.

Comments
16 - Nebula
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!

 

FirstLastSum Actions (Naughty-Nice)Naughty/Nice RatingNaughty/Nice ScorePresent ScoreClassification
RonaldCook-1202020Recently moved from naughty list over to my nice list.
WillieMorrison-1202020Recently moved from naughty list over to my nice list.
BarbaraHowell-1202020Recently moved from naughty list over to my nice list.
SarahGomez-1202020Recently moved from naughty list over to my nice list.
LouiseDavis-1202020Recently moved from naughty list over to my nice list.
DenisePierce-1202020Recently moved from naughty list over to my nice list.
SusanLynch-1202020Recently moved from naughty list over to my nice list.
CherylFerguson-1202020Recently moved from naughty list over to my nice list.
GeorgeRuiz-1202020Recently moved from naughty list over to my nice list.
LoisWashington-1202020Recently moved from naughty list over to my nice list.
ChristopherReynolds-1202020Recently moved from naughty list over to my nice list.
RebeccaCarter-1202020Recently moved from naughty list over to my nice list.
JackWatkins-1202020Recently moved from naughty list over to my nice list.
RyanCook-1202020Recently moved from naughty list over to my nice list.
PaulRussell-1202020Recently moved from naughty list over to my nice list.
GregoryRoberts-1202020Recently moved from naughty list over to my nice list.
JoshuaLynch-1202020Recently moved from naughty list over to my nice list.
JosephHarper-1202020Recently moved from naughty list over to my nice list.
PaulaBradley-1202020Recently moved from naughty list over to my nice list.
LisaGomez-1202020Recently moved from naughty list over to my nice list.
StevenWagner-1202020Recently moved from naughty list over to my nice list.
DavidHawkins-1202020Recently moved from naughty list over to my nice list.
ChristineSmith-1202020Recently moved from naughty list over to my nice list.
RandyOlson-1202020Recently moved from naughty list over to my nice list.
JohnLawson-1202020Recently moved from naughty list over to my nice list.
RobertPhillips-1202020Recently moved from naughty list over to my nice list.
AnnChavez-1202020Recently moved from naughty list over to my nice list.
JacquelineCox-1202020Recently moved from naughty list over to my nice list.
ChristineRiley-1202020Recently moved from naughty list over to my nice list.
KathleenScott-1202020Recently moved from naughty list over to my nice list.
Alteryx Community Team
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
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! 

 

IDFirstLastSum Actions (Naughty-Nice)Naughty/Nice RatingNaughty/Nice ScoreClassificationAlignment
13934StephanieGrant-12121Almost got a present this year - but still naughty!Naughty
13953KathyArmstrong-12121Almost got a present this year - but still naughty!Naughty
14360CarlStanley-12121Almost got a present this year - but still naughty!Naughty
14418RyanDixon-12121Almost got a present this year - but still naughty!Naughty
14709MildredRichards-12121Almost got a present this year - but still naughty!Naughty
14868WilliamGutierrez-12121Almost got a present this year - but still naughty!Naughty
14994MarieWilson-12121Almost got a present this year - but still naughty!Naughty
1571BrianSchmidt-12121Almost got a present this year - but still naughty!Naughty
2055DorisHill-12121Almost got a present this year - but still naughty!Naughty
2108LauraGutierrez-12121Almost got a present this year - but still naughty!Naughty
211DianeCastillo-12121Almost got a present this year - but still naughty!Naughty
2259AnthonyRyan-12121Almost got a present this year - but still naughty!Naughty
2386BillyBailey-12121Almost got a present this year - but still naughty!Naughty
3222AmyDiaz-12121Almost got a present this year - but still naughty!Naughty
3244JessicaGibson-12121Almost got a present this year - but still naughty!Naughty
3399DonnaHall-12121Almost got a present this year - but still naughty!Naughty
3407PatrickHoward-12121Almost got a present this year - but still naughty!Naughty
37RalphBoyd-12121Almost got a present this year - but still naughty!Naughty
3936CarlosKelley-12121Almost got a present this year - but still naughty!Naughty
4357MatthewHernandez-12121Almost got a present this year - but still naughty!Naughty
4401HowardElliott-12121Almost got a present this year - but still naughty!Naughty
4444JerryDixon-12121Almost got a present this year - but still naughty!Naughty
4835SarahMontgomery-12121Almost got a present this year - but still naughty!Naughty
5039JaniceAlvarez-12121Almost got a present this year - but still naughty!Naughty
5284KathrynBrooks-12121Almost got a present this year - but still naughty!Naughty
5867RebeccaFranklin-12121Almost got a present this year - but still naughty!Naughty
5944JuliaMartinez-12121Almost got a present this year - but still naughty!Naughty
5945JustinPalmer-12121Almost got a present this year - but still naughty!Naughty
6422JuliaWalker-12121Almost got a present this year - but still naughty!Naughty
7550EricWood-12121Almost got a present this year - but still naughty!Naughty
7683SandraGomez-12121Almost got a present this year - but still naughty!Naughty
7747ChristineStevens-12121Almost got a present this year - but still naughty!Naughty
8069MartinRobinson-12121Almost got a present this year - but still naughty!Naughty
8449KimberlyDaniels-12121Almost got a present this year - but still naughty!Naughty
9028DorothyHarris-12121Almost got a present this year - but still naughty!Naughty
9125JoshuaDunn-12121Almost got a present this year - but still naughty!Naughty
9169JeremyMoore-12121Almost got a present this year - but still naughty!Naughty
9758MargaretRose-12121Almost 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