yearly unique shown monthly

[mattlukoff]

Hello,

Can anyone help me get a sum of purchases divided by unique count of user IDs over a yearly period but shown over a monthly time frame. See sample workflow attached. It's a purchases per unique user metric showing over the year.

Thanks

[SeanAdams]

Here you go @mattlukoff

I may not have understood your metric perfectly but this should give you enough to work with to get you to the finish line.

key is that you:

- add a year column

- Then calculate unique users per year

- Then join this back to your original data-set.   From there it is relatively easy.

If this gets you to a solution, would you mind marking as solved - otherwise just reply and we can iterate.

Thanks @mattlukoff

Sean

[mattlukoff]

@SeanAdams, thanks but I'm actually trying to see the cumulative purchases per user. What's happening with this example you showed me is that it's dividing cumulative purchases by total cumulative users per year. I actually want to divide cumulative purchases by total cumulative users (to-date) over the year. So in my example as of Sept 2015 there were 7 unique users (not 15). I want to show the actual growth of purchases per unique user. 

[chris_fort]

This version uses the TSFILLER tool from 'R' to generate all months in the range of your input data, converts them to month-end dates and then creates all combinations of the months and your transactions (could be filtered to make more efficient - depends on home much data you have if that's necessary or not).

From there, a conditional fields are added to include the userid and purchases if the purchase date is less than the "parameter" date created by the TSFILLER tool.

Finally, summarize, rename the fields and calculate the cumulative purchases per cumulative number of unique visitors.

You could also use the generate rows tool and some additional formulas in place of the TSFILLER tool if you don't have R installed.

Maybe this will work?

PS I was having trouble attaching the YZXP - was getting an error that "The contents of the attachment doesn't match its file type.", so you'll have to remap your input file.

[mattlukoff]

@chris_fort sounds interesting but could you save and re-share in v 10.6 or lower? Unfortunately I can't upgrade at work.

[chris_fort · Accepted solution]

@mattlukoff give this a shot. Not sure it ill work, but I edited the XML for 10.6 (10.5 actually, but that's what 10.6 puts on it).

I have had some success in the past with this methodology.

[SeanAdams]

Hi @mattlukoff

Based on your clarification - it seems like the core problem you're trying to solve is to be able to take a data-set that looks like this:

User	Month
a	Jan
b	Jan
a	Feb
c	Feb
a	Mar
d	Mar

And then end up with a data set that shows you the number of new users cumulatively like this:

Month	Unique	UniqueUsers
Jan	2	a;b
Feb	3	a;b;c
Mar	4	a;b;c;d

if this is what you're looking for, I'll put this together and attach.

[SeanAdams · Accepted solution]

Hi @mattlukoff

Because you want to do a rolling cumulative total of the unique users seen in this year so-far (this month) , and the cumulative sales in this year so-far (this month), the way I tackled this is to create a batch macro which takes in 2 things:

- All of the sales info

- The month we're summarising for.

What the macro does is to count all the unique users for that year, up to that month - and also summarises sales YTD.

Please play around with this, so if it's not exactly what you're looking for, hopefully you can use it to get where you need to get.   If this is still off-base, can you please provide an input and output data set so that we can get you there?

here's the macro

and here's the main flow that calls the macro

[SeanAdams]

Hey @mattlukoff

There's a great article here (by @WayneWooldridge )   on how to work with files that are sent with a newer version of Alteryx than you have;

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

Essentially just open the file in Notepad or Notepad ++ and change the version number in the top 2 lines.

that should make it easier for you to use community-sourced solutions

Cheers

Sean

[mattlukoff]

@SeanAdams, not going to pretend I understand what you did with that macro but this worked! One follow-up question/request? How can i add one or more dimensions to group the distinct by. For instance cumulative purchases divided by distinct yearly users grouped by Product (so that the users and engagements are distinct by product). thanks.