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:
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:
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.
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
Solved! Go to Solution.
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