Aggregate Active Customers by Month for Customer Who Purchased in the Last 24 Months

[kheuer]

Alteryx Gurus,

I am looking for your help to find a somewhat elegant solution that will aggregate by month how many customers purchased within the last 24 months. Here is what I am looking for - I have a list of customers with the dates when they placed an order. I want to produce a monthly aggregate that tells me how many active customers we had for the reported month by looking back at the last 24 months. 

Example  -  for the month of January 2021, I want to report on all customers who placed at least one order between 2/1/2019 - 1/31/2021. I want to do this retroactively for all previous months. I built a solution where I setup a template with the months and the start and end dates, followed by a flag whether the order date would fall within the time period, but the process generated millions of records and ran hours to run. I am hoping that one of you smart folks can help me find a more elegant solution. I added a sample dataset attached to this post. 

Thanks in advance for your suggestions.

--- Kristina

[clmc9601 · Accepted solution]

Hi @kheuer,

Is this what you're going for? I built a batch macro that will find the customers for each range separately and then assemble them. I duplicated the data in the first step so that there were actually 24 months contained in the data, so naturally you can skip this step with your actual data. Hope this helps!

[kheuer]

This is awesome, @clmc9601! Thank you so much.

[kheuer]

@clmc9601 - question for you - can you take a look at the macro one more time, please? I am a novice in this area, but I believe that the End Range should feed the expression [[invoiceDate]<="2018-01-01"

It is strange to me that the start range and end range have the same parameter in the expression field of the action tool

I am getting inconclusive results. Please advise.

[clmc9601]

Hi @kheuer,

Thanks for pointing this out! I double-checked and the interface tools are functioning as I intended. Since you're getting "inconclusive results", my guess would be that either I slightly misinterpreted your intention when I built the workflow or incorporating new data will require some sort of adjustment.

I agree with you that the action tool's configuration looks confusing. This is because it is only displaying the first line of expression. Since there is a newline character, it doesn't display the rest. I checked the debug workflow (a way to test the interface and configuration), and it appears to be replacing properly!

the action tool config *displays* only the first line of the expression

I'm happy to continue solving with you. Can you explain a little more about your inconclusive results and what exactly isn't working as you want it to?

[kheuer]

Thanks for your continued assistance. I think I know what the issue is, but don't know how to solve it. In your sample dataset, change one of the rows to the invoice date 2018-01-01 and then hit run. Although the filter says ...

[invoiceDate]>="2016-01-01"
AND
[invoiceDate]<="2018-01-01"

... the record comes out of the false although the = operator should bring it through. Thoughts?

[clmc9601]

Hi @kheuer,

Ah, now I see the issue. I think it has to do with date vs datetime data types. I added " 00:00:00" to the filter expression and that fixed the problem when I experimented with it. You should only have to change the filter, not the action tools, because only whole dates flow through the control parameter. I hope this helps!

updated filter expression within batch macro

[kheuer]

Sweet! I will give that a try. I am sure this is it. Thanks for the help. 🙂

[kheuer]

@clmc9601  - it resolved the issue, but by adding it, it no longer brings in the first of January. I actually had to change the formula to the below to get it to work. Seems a bit strange, but it resolved both issues.

ToDate([invoiceDate]) >= "2016-01-01" AND
ToDate([invoiceDate]) <= "2017-12-31 00:00:00"