This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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]
[links & click info] ( notice the extra column here!)
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]
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
- 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
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!