Alter.Nation
Community news, customer stories, and more!
Solution
If you’ve arrived on this blog post, odds are you have aided Santa a third time in his trip around the globe in Week 3 of the Santalytics challenge - an inclusive, non-denominational, festive excuse for some friendly competition using Alteryx. You’ve also tackled yet another logistical nightmare for Santa with sheer Alteryx might, and identified opportunities in his data to help make the spirit of giving go even further this year. No doubt an impressive feat, and one that will be rewarded with a timeless Community Cold Weather Kit that will have you prepared to brave the coming winter season in peak fashion. Be warned in advance, you will make a lot of folks jealous this holiday season.
With your Community Cold Weather Kit, the Holidays will have never looked so good
With that, we’re happy to introduce our solution to the Week 3 optimization problem of assigning the best possible presents (determined by price, then weight) to each classification of nice kids on Santa’s list:
The approach shown above (attached in the v10.6 workflow Santalytics Part 3 Solution.yxzp) begins by determining the lightest and most expensive presents that could be gifted to each kid depending on their present score determined in Week 1 from their year of naughty or nice actions. After joining these default present assignments back to the recipient dataset, a sum of these weights from the Summarize Tool then provided a least weight of each hub that was used to calculate the delivery hub weights remaining in a Formula Tool. These remaining weights were then used to assign pricier and heavier presents that did not surpass the hub weight limit of 422 lbs to kids in the order of their nice ranking (by total nice score, then present scores, determined in Week 1).
The second round of present assignment for optimization was accomplished using an iterative macro (attached as the v10.6 macro Santalytics Part 3 Pick Present.yxmc) that used a list of all recipients’ potential present assignments for their present class and their remaining hub weights to first determine the best remaining kid (target) for each hub. The macro then selects the best applicable present to output for that target and joins an adjusted remaining hub weight back to the original dataset that accounts for the new present assignment and will determine the weight remaining to optimize subsequent hub constituents. The macro does this selection by hub, performing the selections across all 340 delivery locations each iteration:
The results of this process are seen below!
Example present assignment for Hub 101
A sample view of hub total prices and weights
The solutions we received from @ggruccio and @JohnJPS both took similar, iterative, approaches to assign optimized presents to each kid in the order of their niceness – so bravo to them! If you took another route, be sure to let us know!
Behind the Data
We mentioned it briefly in our Part 1: Solution and Behind the Data and Part 2: Solution and Behind the Data posts, but the BestBuy Products API was the key data store of gifts and their true shipping weights that was leveraged for Week 3. This data was accessed via an API connection much like our example for the Quandl API non-coding approach (cURL approach) because of its ease of access and later refined for better fit into the Santalytics exercises.
This started by identifying a request that would give us only a static snapshot of raw product data (they have other requests to keyword search their products or retrieve the most up-to-date products and their attributes in real time), which we found in their Products Bulk Download option:
The URLs they provided here (we used the XML format URL and substituted in our API key) were then placed in a Text Input Tool in the Designer with the Download Tool (you can master the tool here) placed downstream to establish a connection and save the XML formatted response as a flat file. Since the data was transferred to us as a .zip file of .xml files, we specified in the Download Tool Basic Tab’s Output to save the file to a filename we specified in our Text Input (BestbuyData.zip – the preceding .\ specified the file location as the working directory where the workflow was saved):
The workflow took a few minutes to run due to the size of file transfer, but the wait was rewarded with all the data we needed - a .zip file of XML documents that captured detailed product information, including weights, for over 57,000 products:
We then input all those files to parse, clean, and filter together:
At this point, we had our present data ready to go for all the nice kids! The shipping weights of the gifts placed between 0.01 lbs and 95 lbs with an average of 6.5 lbs per present in Santa’s sleigh and the present prices ranged from $2.5 to $7999.99 and averaged at around $400 a present. Needless to say, the elves in Santa’s workshop definitely deserve a holiday bonus this year.
The Santalytics gauntlet of challenges is all but over and with the help of Alteryx Santa is prepared to go about his routine deliveries at a record-setting pace. Only one more obstacle remains – visualizing the trip for Santa. Can you help relay the route improvements to Santa and show him his route around the world in Part 4?
