Hey everyone,
I am fairly new to Alteryx and I have been using it to dig deeper into transaction data. I have had success creating cohort summaries measuring customer activity and frequency levels by pairing dates with a single "Fiscal Period" using a distinct one-to-one mapping algorithm, meaning each date falls into exactly one Fiscal Period. To do this, I have been using the Summarize tool to group transactions by Fiscal Period and show me the number of transactions, the number of unique customers, etc. during that Fiscal Period. This has all been fairly simple with dates being mapped to a single fiscal period.
However, I am interested in looking at this with a different lens for what constitutes an "active" customer at a given time. For context, each fiscal period consists of 4 weeks, which is a fairly short amount of time. I would like to create the same cohort-style data output for the trailing 12 weeks as of the end of any given fiscal period. I am struggling with how to do this because it would mean that instead of each date getting mapped into a single Fiscal Period, it would now be considered an "active" transaction for 3 Fiscal Periods. I am not sure how I would be able to use Summarize to aggregate data in this way - am I able to use Summarize's Group By action to capture this properly?
For clarity, consider the following example:
Let's define Period 1 as January 1 - January 28, Period 2 as January 29 - February 25, and Period 3 as February 26 - March 25.
Let's say that Customer A completes a $20 transaction with us on January 1 and another $30 transaction on March 1. Under my previous analysis, the $20 transaction would only be counted in Period 1 and the $30 transaction would only be counted for Period 3. Customer A would then show up as a unique active customer in Period 1 and Period 3, but would be considered inactive in Period 2.
However, under this new analysis, the $20 transaction should be counted as an "active" transaction in Period 1, Period 2 and Period 3 because it occurred within 12 weeks of the end of each of those periods, and Customer A would be counted as a unique active customer in each of Period 1, Period 2 and Period 3 on this basis. Furthermore, Period 3 would show that this customer made 2 transactions in the trailing 12 weeks ($20 on January 1 and $30 on March 1).
Is there a way to do this kind of grouping with the Summarize tool where individual rows overlap with multiple categories for a single variable, or am I using the wrong tool? The only other way I can think of approaching this is creating a bunch of boolean columns for each of my Fiscal Periods which states whether a date falls within 12 weeks of the end of that fiscal period, and then creating separate summarized outputs using each of the Fiscal Period boolean columns, but I feel like that would be incredibly inefficient given that I am doing this with several million transactions across 90+ Fiscal Periods.
Any help would be greatly appreciated. Thanks!
