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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Slicing and recombining data

JefBus
7 - Meteor

Good morning everyone,

 

I know Alteryx likes raw data, but what do you do when working with more or less prepared / formatted exports such as the ones you tend to get back from some web services?
I might have to split this question up in it's individual problems, please let me know if I should!

I'm trying to build a flow which takes mailchimp exports as an input, and converts every csv file in a given folder to one / multiple rows in the output, for input into Tableau.

let me just outline the situation:
There are 3 possible types of input:

 


Case 1
- Sometimes we just send an e-mail, just the e-mail without fancy A/B testing, this is the simplest case
these exports do not mention the specific string _A_B_ in the filename

these exports can always be described as:

[Metadata: Title, subject line, Send date]

[metrics/stats]

[links & click info] ( notice the extra column here!)

 

Like this:

 

noabtestexport.png

 

What should Alteryx do?
- do a little cleanup ( e.g. throw out the percentages ( i'll recalculate them myself) clean out the comma's in the thousands, and so on)
- transpose the metadata (green) and metrics block (yellow) so all this becomes one row, set [Total clicks] and [Unique Clicks] to zero and add an empty Field called [Link Url]
- add more rows containing the fields [Link Url], [Total Clicks] and [Unique Clicks] (blue)( 5 rows in this case) and append the necessary information to identify them as part of said e-mail.

 


Case 2
- Sometimes we send an A/B test, which consists of up to 3 different versions of an e-mail,
A/B tests themselves have the string _A_B_ in the filename
the exports look a little different here:

[Metadata: Title, Send date]


[Version A metrics & values]
[Version B metrics & values]
[Version C metrics & values]

[Version A links & click info]
[Version B links & click info]
[Version C links & click info]

Like this:

 

abtestexport.png

 

 

what should Alteryx do here?

 

Cut this stuff up and recombine it so we get
- Metadata + Combo 1 stats (1 row)
- Metadata + Combo 1 Link info ( as many rows as there are links in the email, 5 in this case)
- Metadata + Combo 2 stats (1 row)
- Metadata + Combo 2 Link info ( as many rows as there are links in the email)

Metadata + Combo 3 stats (1 row)
Metadata + Combo 3 Link info ( as many rows as there are links in the email) 
in the same format as Case1

 

 


Case 3

 

- The winning combination gets sent to the rest ( a chosen %) of the receiver list.
This output is comparable to Case 1, we can identify this file by the presence of _A_B_Winner_ in the filename.

 

abtestwinnerexport.png

 

 


My main problem here?
- Is there a way to do Case 2 in a clean and repeatable fashion that would also work for any subsequent e-mails? (So, slice and recombine ABCABC to AABBCC actually?)
- Is there a way in which I can just fill up a folder with all reports ( 60 or 70 csv files up until now), and build a macro ( batch or iterative?) to have Alteryx cycle through them and add each e-mail as a row in the final output? ( so any regular e-mail, any A / B / C version of an A/B test, and the winner gets an own row + x rows for it's x links)
- How to make Alteryx understand the difference between the three types of files and handle them accordingly?

In attachment you'll find a little test that works for the Winner version, this is pretty much as far as i got up until now.
- an Alteryx file
- a csv input
- a sample of Excel output for the Winner e-mail

 

It also contains some extra features such as splitting the utm_ part off the link

4 REPLIES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

Do you have any CSV sample of the other formats?

 

I think it would be possible to create a fairly generic solution but useful to have some sample to play with

 

JefBus
7 - Meteor

My bad!

Here's the CSV's,
so there's

- one without _A_B_  <== Regular e-mail

- one with _A_B_ <== combined output of  A/B/C tests ( minimum 2, max. 3)

- one file with _A_B_Winner in the filename. that's the winner of the A/B test

jdunkerley79
ACE Emeritus
ACE Emeritus

Try the attached to get you started.

 

It reads all the CSVs in and then parses each of the tables before recombing to build required outputs.

 

Its a fair of shuffling and rotating but think should work generally for the data you described.

 

JefBus
7 - Meteor
Hi jdunkerley79,

Thank you for your swift reply!
this 'll definitely get me started once I get to tear it apart at the office!
(currently enjoying a couple days off)

Being relatively new to Alteryx, this solution almost looks like data magic to me for now, some blocks ive never used and new concepts in it for me. I more or less get what is happening, but will need to have a more thorough look at the details of it Monday!

Thanks!

Labels