Challenge #25: Creating Merchant Combos

The link to last week’s challenge (challenge #24) is HERE.

As we have said before, restructuring poorly formatted data is one of the most common Alteryx use cases. This week’s challenge is another real world example of a data problem faced by one of our customers.

Use Case: A credit card company’s customer Data is structured so that each row of data contains the merchants each customer has visited in a given week. The credit card company is wanting to understand the correlation of merchants visited by customer. For example: if a customer visit 7-11 what other stores do they have a high propensity to visit?

Objective: Restructure the data into rows that pair merchants together by customer.

challenge_25_start_file.yxmd

challenge_25_solution.yxmd

Join

Preparation

Advanced

Intermediate

Data Preparation

Transform

Accepted answers

All comments

dataMack

Could these be posted in 10.1 format for a while? I know 10.5 came out last week, but haven't had a chance to upgrade yet.

GeneR

You could open the YXMD file in word pad or another text editor and in the header change 10.5 to 10.1 it should open for you then.

TaraM

Hi @dataMack - I've uploaded a 10.1 version. As @GeneR points out, you need only change the version in a text editor. In this case you need to specify 10.0 though.

alex

The speed in which you can prototype, test and modify your workflow to achieve your desired result is one of many reasons Alteryx is so darn cool and useful. I tackled this problem in 2 different manners. The fixed format solution assumes you will only have 5 columns of merchant data. The second solution will let you scale to allow for more columns of merchant data to be added without having to modify the workflow. To test my assumptions, I added a new column of data and it worked perfectly. As a way to use a tool I hadn’t used before, I threw the results from the first example into the Contingency Table tool from the Data Investigation tool category to create a matrix style chart to view the data from a different analytical perspective.

Fixed Format

Spoiler

The 2 extra tails are there to visualize the results in a table vs. a list

Homemade Table

From Contingency Tool

Solution that scales for multiple scenarios

Spoiler

TaraM

A solution has been uploaded, suitable for version 10.1

Spoiler

2016-05-16 09_27_42-Alteryx Designer x64 - Analytics_CreatingMerchantCombos_Intermediate_Solution.yx.png

markp201

Spoiler

MarqueeCrew

@JoeM,

I was puzzled why all combinations of store visits were not used. Anyways, I solved for the answers provided.

Cheers,

Mark

challenge_25_MarqueeCrew.yxmd

SeanAdams

slightly different solution to the others provided - trick used is...

Spoiler

Only keep unique merchants by user
Put a unique ID on each
Then do a cartesian join and...
filter out all combinations where the left ID < the right ID (to eliminate X-X joins or the issue where you have X-Y as well as Y-X - i.e. the symmetrical version of a good row)

challenge_25_seanSolution.yxmd

NicoleJohnson

My solution. Results are slightly different because it made more sense to me to have the single vendors show up as Merchant1 instead of Merchant2... but other than that, results match. This was a fun one! Slightly bizarre... and I can decide if I'm pleased by the solution or mildly confused by it. But at least I'm fairly certain my final solution should be adaptable to varying #'s of merchants.

Spoiler

1. Generate Rows based on Count for each UserID
2. Remove Merchant from column name, and then use Multi-Field tool to match if CurrentFIeldName > RowCount, put results in new field named as column # with Merchant2 as suffix.
3. Transpose, Key field = UserID
4. Add "Merchant1" to fields not already containing Merchant2 in values
5. RegEx parse Merchant (1 or 2) and MatchOrder (prefix)
6. Join Data to itself by UserID/RowCount/Merchant
7. Filter + Formula + Unique + Summarize tools combine to get rid of duplicates
8. Sort & Select to remove unwanted columns

Phew!

challenge_25_NicoleJohnson.yxmd

estherb47

Spoiler

challenge_25_EHB_solution.yxmd

LordNeilLord

I look a slightly easier route than others but if it works, it works

Spoiler

challenge_25_LNL.yxmd

SeanAdams

:-) you really are tearing through these @LordNeilLord - fantastic to see you working through 2 & 3 a day.

Not sure if you've been following the banter but there's a few folk who have done all the available challenges so-far, so there's quite a tight competition going to see who will be the first to earn the legendary "75 challenges" badge. With the pace you're going, you could be in contention (from what it looks like, you're on your way to 50 now?)

Awesome progress!

PS - if you find that these weekly challenges are useful - drop a quick note to @JoeM and @MattD and the team - they really do value this kinda feedback.

Laurap1228

I downloaded four weekly challenge starter files (25,26,27 and 28) for my 11.5-hour flight from London to San Diego yesterday, and this was the first one I tackled. It also took me the longest. It was a bit tricky, but once I figured out I needed a record ID before my Transpose tool it made the sorting easy!

Spoiler

challenge_25_LP.yxmd

nick_ceneviva

Solution Attached

challenge_25_solution_nickCeneviav.yxmd

patrick_digan

Spoiler

challenge_25_start_file.yxmd

Natasha

I took a fairly standard approach

Spoiler

challenge_25_Natasha.yxmd

JoshKushner

Sort of looks like a Bridge Table structure! I'm confused with the provided outputs that have preceding 0's though...

Provided Output:

challenge_25_start_file.yxmd

samN

I got to the output but don't really agree with the output really representing what the question asks. I also dont really understand why we are keeping nulls in as it doesn't give any comparison to anything.

Anyways little different from the solution

challenge_25.yxmd

Waynemk

I think mine is a novel approach.

Spoiler

can you tell i love the multi-row formula tool?

challenge_25_waynek.yxmd

ggruccio

Took several steps to get there....

challenge_25_gg_finish.yxmd

dsmdavid

Learning my way around macros, so trying to use them all the time - I also thought it more logical to have the nulls/empty in the second column...

solved#25.yxzp

philipmannering

Solution attached.

Spoiler

challenge_25 - Creating Merchant Combos.yxmd

LandonG

Solution attached.

challenge_25_start_file.yxmd

jasperlch

Solution attached.

challenge_25_JL.yxmd

msicak

I had to cheat and look into alex's solution for a bit of inspiration (namely, the multi-row, I knew I could use it to solve this). Thanks, and attached my solution - non-scalable and (somewhat) scalable, even if not fully flexible.

Spoiler

challenge_25_MS.yxmd

jamielaird

Solved with a cartesian join and a bit of filtering:

Spoiler

challenge_25_solution_JL.yxmd

MaxGiegerich

I used a filter expression to most of the heavy lifting here. Took a bit of fiddling but reduced the number of tasks required significantly.

Weekly Challenge #25 - MG.yxmd

JosephSerpis

Challenge Completed

Challenge_25_Joe_Serpis.yxmd

aclaxton

I got most of the way but I had to poke around the forums for a way to get rid of reciprocal duplicates

Spoiler

justin_winter

Going back in our user group and doing old challenges. This was rough, I tackled it a couple different ways before I settled on an approach I liked. It was still fairly hard coded, so if I were to do this for real, I would want to build out the summations and mud functions to do it dynamically with math.

challenge_25_start_file_JAW.yxmd

kcgreen

This one took me lots of trial and error to get right. Reading the comments, I felt better knowing that I wasn't the only one to struggle on this challenge.

Spoiler

challenge_25_ShopLists.yxzp

sagarb

More of a static solution. After looking at the posted solution, realized that the good old Full Outer Join is the trick.

challenge_25_solution.yxmd

dpencheva

Here is my solution!

challenge_25_solution_file.yxmd

Sharod100

Here is my solution to this problem

challenge_25_Sharod_Hinton.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335