Challenge #146: SANTALYTICS 2018 - Part 2

Welcome all to SANTALYTICS PART TWO!
If you are just checking out Santalytics for the first time, please read the announcement blog for the inside scoop: #SANTALYTICS 2018

In Part 1, we took a look at IRS data and invited you to help us identify a local charity. This weeks challenge is designed to replicate how we might allocate those donation dollars to serve the needs of the organization we identified - A Precious Child. One of the key focus areas of said charity is to provide basic necessities to children in need who would otherwise go without.

Community's promise in this challenge is to donate $20 per solution between 12/17-12/21 with a $2,000 max! Solving for good? Now that's something I can get on board with. What's more, your solution will almost surely change the answer to the problem. Since you are adding a solution, you will trying to solve our problem with $20 more than the previous solver.

In the great tradition of Santalytics, we do not provide a fixed answer, but will give some rules to guide you through the challenge:

1) You'll need to scrape the Community to determine the number of solutions that have been posted (for ease you can just count .yxmd/.yxzp attachments). For those who want to skip this and take the 'easy' path, a macro is attached. We realize that the number of attachments != the number of solutions, but we can pad it a little right?!
2) The number of 'solutions' * $20 will be the donated value you are working with
3) The item data set (StoreData.yxdb) has two types of items - hygiene and clothing. Try to categorize them as you think they should and make sure that you donation is split 50/50 across the two categories to make sure we aren't heavily weighted to once side
4) Make sure the items has a 4+ star rating, 10+ reviews and is in stock
5) No one item can be purchased more than 10x for diversity of donations.

After you're done, post up your solution. We might just use your solution as the shopping list!

NOTE: This will be the last challenge of 2018. We will be resuming challenges in 2019-01-07.

StoreData.yxdb

Solution_Counter.yxzp

Advanced

Data Analysis

Intermediate

Accepted answers

All comments

Treyson

Am I reading this correctly? You are donating $20 for every solution response to this challenge, or all posts marked as a solution on the community as a whole? Just want clarification before blasting this out on Twitter.

TrevorS

Hello @Treyson,

Thats a great question. To clarify, we will allocate $20* to our donation pool for each solution posted to challenge #146 from today through December 21st, 2018 (*up to a max donation of $2,000).

Thanks!

TrevorS

Ladarthure

My solution,

not the best shopping list I guess, but based on the customer ratings, very fun one!

santanalitycs part 2-2.yxzp

patrick_digan

Spoiler

I decided to use the Lithium API to webscrape. Here is how the $20 is being spent so far (It will be $40 as soon as I post this!)

StoreData.yxdb

Weekly Challenge 146.yxmd

JoBen

Cheers!

Spoiler

Challenge_146_Joben_Solution.yxmd

pjdit

My solution with $60

Weekly Challenge 146 - PD Solution.yxmd

cgoodman

Decided to use the optimisation macro so I can maximise the overall rating of the items selected while meeting the constraints.

For the current 6 solutions ($120) here's what you should buy.

$120 solution.JPG

If we get the 100 solutions to get the $2,000 donation then the items which should be purchased are listed in the yxdb file.

Workflow

Spoiler

DonationsOutput.yxzp

cgoodman

Didn't realise you couldn't add more than one attachment, so here's the yxdb file for the 100 solution answer.

$2000_donation_items.yxdb

OldDogNewTricks

Attached is my solution. I used a Python tool to do the website scraping (because why not?). I navigate the pages if there are more than one (the provided scraping macro will miss data if the page count goes over 10) and I only add the valid solution types to the resulting data frame. I then get down to a unique record per user in order to not double count solution postings.

For the monies distribution, I went with quantity over quality (as the 4+ star criteria is already applied) as well as the largest variety of items. I also noticed that there were a few records with both the salesprice and msrp as null, I excluded those because I didn't figure anything was really free.

Spoiler

Result summary (before my posting): Result summary (after my posting):

Result detail (before my posting):

challenge_146_completed.yxmd

Treyson

I thoroughly enjoyed this one. I went through @patrick_digan's idea of circling back with accumulated left over spend so I implemented that. I also split clothes up into boys and girls in order to make sure there was even spend there as well.

I also tried to make a pretty report but I wasn't able to get a report that actually rendered to match what I created in the "visual layout" tool in the laboratory.

Anyway. Here's Wonderwall

Spoiler

Santalytics 2018.2TM.yxmd

Paul-Evans

Here's my results:

Shopping summary.JPG Shopping list.JPG

Spoiler

For the solution count, I used an iterative macro to go through each page of responses and create a list of all .yxmd and .yxzp attachments.

To select items to purchase, I
- randomly sorted the items after filtering for the given criteria,
- selected items by type until hitting the spend limit, and
- matched the remaining spending amount to the item with the price closest to that amount.

Challenge 146_SANTALYTICS 2018 - Part 2_Solution_Probley.yxzp

Kenda

Spoiler

I wasn't sure if the 50/50 split meant number of items per category or amount spent in each category. I went with the amount spent per category. Before my post, there was $200 available to spend. I started with the given macro but modified it a bit (in pink).

Santalytics Part 2.yxmd

mmenth

I like how open-ended this challenge was!

Spoiler

For my solution I was given $400 to work with, so I first split that up so that we could spend $200 on clothing and $200 on hygiene. Since there were so few clothing products after filtering out products without enough reviews, with reviews below 4, and unavailable or limited products, I didn't add any filters of my own. However, there were a lot of hygiene products to choose from after applying the filters mentioned above, so I decided to look up the the products that A Precious Child accepts on their website. I found a short list and added that to my workflow. A quick find and replace helped me filter down the original hygiene list to just those products that the nonprofit would accept. I then sorted by customer rating and took the top unique products based on the their acceptable product list.

For my approach I assumed variety is preferred, so for the two product categories I duplicated my narrowed-down lists 10 times each, sorted by the rowcount, and calculated a running total. I kept adding products to my shopping list until the running total met my spending limit. If I had a remainder left over, I grabbed another product that would minimize the overall remainder for that category grouping.

Overall my list has 28 unique products, with 71 products total!

santalytics round 2.yxzp

ggruccio

My approach goes after the higher ticket items...so maybe could help one specific family.

What a great cause!

challenge_146_ggfinish.yxzp

egranberry

This is a great idea! Happy holidays!

Santalytics Pt 2.yxmd

aristiguieta

I do many annotations (in the tool tags and as comments), so this may be helpful if you didn't know how to approach it.

I hope we get to the $2,000!

:-)

Santa Part 2 - aristiguieta.yxmd

aristiguieta

By the way, there are not so many clothing items that qualify, so if we get more answers a workflow like this would benefit from making sure we do ten (at least a second) pass on the list of options.

Not to elegant but simple: Get 10 threads of the eligible items list, and "union" them into a large list before applying the "running totals".

jssandom

$360 to play with.. decided to spread it out with one of each item.. 37 items purchased in total

challenge_146_solution_JSS.yxzp

sprodger

My solution attached, with $380 (19 solutions so far) to spend. I also filtered out the list to just the items that A Precious Child accept. Had $1 left over at the end of it!

Spoiler

Challenge_146_SP.yxmd

pasccout

Here is my solution...

Spoiler

I went with the lowest price first with the maximum number of items (up to 10) by category...

Scanned all the pages to come up with the number of solutions (minus the files of the original post of this thread) and added my solution to the count...

challenge_146_my_solution.yxmd

GemmaM

Found this quite tricky so definitely not the best!

I had $420 to work with.

challenge_146_GM.yxmd

unknown

Fun challenge.

I had a flashback to my excel days, writing a nested formula in the multi-row formulas reminded me of some old nasty excel formulas. I'm curious to see the other approaches.

I prioritized on % savings vs. MSRP (trying to get the biggest bang for the buck). Then I bought up to 10 for each product. Although, I'm very skeptical the Dove Beauty Bar is normally priced $82.56.

Spoiler

challenge 146 comp bp.yxmd

BrandonB

Spoiler

Hello everyone,

For my solution, I utilized the "solutions counter" macro that was provided, but tweaked it so that it would run for the number of pages in the thread rather than the default 10 that was set in the macro. It looks for the HTML string "lia-paging-page-last lia-js-data-pageNum-" which is followed by the number of the last page number. This is then fed in as a control parameter which updates the generate rows tool tool in the "solution counter" macro. I thought that this would be useful if enough responses are received to bump the number of pages over 10.

Budget is then calculated and split in half to be allocated to clothing and hygeine.

I figured that looking at the % Savings from MSRP to sale price would potentially be an indicator of good deals so I have the data sorted with the highest discounts first. Some retailers intentionally set their MSRP higher to give the impression of better deals so this may not be the best criteria, but product priority can easily be changed by adjusting the sort tool to use the desired criteria.

I used a generate rows tool to create 10 rows for every product so that this would be the maximum allowed for purchase in order to get some diversity in the products purchased. Then I used a running total tool on the sale price and a filter where the running total is less than or equal to the budget for each category. After the items have been identified, a summarize tool is used to total the quantities.

At this point, I realized that there was still budget left over. It wasn't much, but more products needed to be purchased to spend the rest. I then used the same process as described previously, but sorted the products from the lowest price to use the rest of the money.

This data was then unioned so the amount donated, budgets, and product list could all be viewed in the results container.

Santalytics Solution Picture.PNG

Santalytics Part 2 Solution.yxzp

Joe_Lipski

2018-12-19 16_28_57-Alteryx Designer x64 - JL Solution.yxmd.png

JL Solution.yxmd

CamodCPA

Spoiler

Made an additional basic macro that filters products based on ratings, reviews, for children, and products that mention kids or children in the description!

then maximized available products with a simple running total tool!

Happy Holidays!

SantaPT2.yxmd

AndrewRein

Spoiler

Workflow

Shopping List

Category Check

challenge_146_start_file Santalytics II.yxmd

T_Willins

I had waaaaayyy too much fun with this. To quote Monty Python, "Now for something completely different." I started with the premises of everybody loves surprises, but they still need to get something they want (need) and Christmas should be about the kids. I split my budget between clothing (green section) and hygiene (red section) to balance purchases (more on that later). My first filters (other than required) filter the products that reference "child" and "kid" then randomizes the products and amounts to purchase. This leads to the fun result of every time the workflow is run it produces a different result! After maximizing my spend on each type, I wanted to be sure I was squeezing out every penny that could be spent on purchasing products, so I took any remaining balance and determined if any additional products could be purchased. The gold section (final purchases) could be an iterative macro, but running it twice seemed to always work. With a higher budget, a macro would be recommended. Browse outputs give a shopping list and the remaining cash balance.

Finally, being inspired by Alteryx and this weekly challenge, I matched the $20 donation Alteryx is giving to A Precious Child. If you can, I challenge everyone who is posting a solution to do the same. The link is: https://donate.apreciouschild.org/

Happy Holidays and Happy Alteryxing!

Challenge 146_Solution.yxmd

WilliamR

Here is my solution.

I have tried to do something the more dynamic I could but I need to fix the number of webpage to scrap. Items allocation is done incrémenting by one each until to reach the maximum amount. The list is in pdf, ready to be used in the supermarket.

Spoiler

146_WilliamR.yxzp

AJauniskis

My solution. $600 total to spend, 30 solutions so far, with $2.63 left.

Spoiler

challenge_146_AJ.yxmd

calampert

My solution:

Challenge_146.yxmd

Spoiler

$640 was allocated assuming it was better to use up most of the money than to have exactly a 50/50 split between clothing and hygiene. Also I spent way too much time trying to figure out what should be considered hygiene products! I used an item price limit of $15 to get a variety of items.

Also I love the idea of making a donation for real! Going to do that now.

146 EW.yxmd

CharlieS

Spoiler

I decided to let a couple of iterative macros keep randomly picking items from each category until nothing else can be purchased.

Challenge_0146-CharlieS_Solution.yxzp

TylerNa

I went for quantity in my solution. Left $1.90 to still be spent. Merry Christmas and a Happy New Year!

Spoiler

Santalytics 2018 Part 2 TN.yxmd

chpoland

My solution at $720 Thanks for giving back!!

Spoiler

Santalytics 2018 Pt 2.yxmd

ddiesel

This was my favorite challenge to date!

Spoiler

Here’s where my brain went with this one:

Filtered StoreData by the criteria in @JoeM guidelines
Filtered by keywords for items listed @ https://apreciouschild.org/take-action/donate-goods/
Spent a little extra time removing items with keywords that were irrelevant, inappropriate, or overpriced
Created 10 records for each item (to satisfy the no more than 10 items clause)
Took half the budget and fed it into an iterative macro that randomly chose clothing items up to the budget
Took the remaining donation budget and ran it through the same iterative macro for hygiene items
Unioned and Summarized the data into tables with quantity and extended cost
Took the opportunity to practice with reporting tools to create a printable PDF (for the very appreciated volunteers that donate their time to do the shopping!)

I had $740 dollars to work with, but this is workflow is dynamic to run with any budget. If I were responsible for the product selection, I might run the workflow a few times to get a list that “feels” the right mix of products to donate. There’s only so much we can automate before we need to get down into the weeds.