Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

#SANTALYTICS 2016

Help an elf get Santa around the globe!

#SANTALYTICS Part 1

TaraM
Alteryx Alumni (Retired)

#SANTALYTICS Part 1:

With an impossible task looming, poor old Santa is clueless at best. There are 15,000 kids in this route (Recipient Database.xlsx, Address Database.xlsx) and he'll have to somehow stitch together all the data he's gotten from his elves - a dozen log files of what the kids have been up to this year (* Action Log.xlsx). Summarizing these and finding a rating for each kid should help! You can do so by subtracting just how naughty they were from how nice they were throughout the year.

 

Santa has been doing this for centuries and has used trial and error to build out his naughty and nice ratings for grouping the kids (Naughty or Nice Ratings.xlsx). Using an approximate even distribution while assigning to each of the 25 groups, can you use Alteryx to determine which kids fall into each category this year?

 

We only have so many days until the Holidays and presents also need to be assigned too! We already know the naughty kids will get coal, but what about the other 20 groupings of kids? The elves do good work, but presents aren't free - we should probably use the price of each gift to make sure the best kids are getting the best classes of gifts! You can use the price of the gifts to also sort these into 20 evenly distributed groups. Let's hold off until Santa knows his exact routes to pick the gifts specifically - the Reindeer can only hold so much!

 

 

Goal of Part 1:
We want a list of recipients ranked with

  1. their Naughty or Nice rating and Score
  2. the class of present they are entitled to.

We will award badges and recognize community members that submitted solutions in our November 30 blog post on Engine works: #SANTALYTICS Part 1: Solution and Behind the Data

 santalytics_banner.png

Tara McCoy
30 REPLIES 30
SophiaF
Alteryx
Alteryx

Santa has another big order to fill this year! Here's my stab at it

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
Joe_Mako
12 - Quasar
Spoiler
Here are some views of my initial profiling of the data:
https://public.tableau.com/profile/publish/SantalyticsPart1Joe_Mako/ActionList

Here are some key things I noticed:
- 3,030 IDs of the total 15,000 have an average Rating that is negative (Rating is sum of degree with naughty alignment being negative)
- there is a normal distribution of Rating values among the IDs
- the majority is in the range of -1.5 to +2.5 (actual range -2.36 to +3.19)
- 53 unique actions, I especially enjoyed the Naughty Degree 10 assigned to the action "Called Taylor Swift ugly" :)
- within an alignment, all actions are equally common, with Nice alignment actions happening more frequently
- over time, frequency of actions or degree values do not change much (meaning date is of not use in analysis)
- 2,658 records in the Present data set, but only 2,601 unique Presents (49 Presents have more than one record)
- there is a Present Type/Subtype value of "DIGITAL COMMUNICATIO"/"AT&T IPHONE HDWARE", looks like text was not encoded and cut off
- Naughty Score classifications are from from 1 (least naughty) to 5 (most naughty)
- Nice Score classifications are from from 6 (least nice) to 5 (most nice)

In the workflow the Select ensures the Degree is an integer, adds a sign to the Degree based on Alignment, Summarizes to the ID level returning Sum of Degree, Summarize again grouping by the Sum of Degree and counting, Filter to split into negative for Naughty Tile Scoring into 5 Equal Sum tiles, and 20 Equal Sum tiles for values 0 or greater, for generating the Nice Score, I use the following expression to take the Tile Number of 1-20 and reverse it to 25-6

26-MOD([Tile_Num],21)

After that it Unions the Tile results. For the Present data, used a Unique to remove the duplicate records, Select tool to change data type to Double for Price and Weight, Tile to Equal Records with 20 tiles, convert tile number as before, Summarize to Tile number level, concatenate the min and max price to make Present Class, Union in Coal for the naughty scores, Join multiple all Score related data together, Joins back with the ID level data, Joins in the ID data as the fine step.

Santalytics Part 1 Joe_Mako.png

emilienl
5 - Atom

My first (but certainly not last) contribution to the Alteryx Community! I had a lot of fun doing it - look very much forward to part 2! 

Philip
12 - Quasar

Lots of fun! My solution.

 

Spoiler
Santalytics part 1 solution.PNG

 

AudreyL
5 - Atom

Hello,

 

Here is my solution for Part 1 Smiley Happy

 

Audrey

Cooperchu
6 - Meteoroid

So I split mine into two parts as I wanted to speed up the process while building out, I put the log files into a seperate workflow to ouput to YXDB.  Both attached...nice fun example and good to keep playing with the tool I love! Heart

LaurenG
Alteryx
Alteryx

Fun challenge!

mceleavey
17 - Castor
17 - Castor

A bit late to the party but I've attached my results!



Bulien

kmarsden
5 - Atom

My solution to part 1!

SeanAdams
17 - Castor
17 - Castor

Nice exercise to play with tile and with wildcard file-import!

 

Labels