Returned customers

[Feras95p]

Dear all, 

I have an excel file which contains a column of the mobile phone of the customer and the other column the date of the order. I want to now how many returned customers for each month how can I do that?

[RobertOdera]

Thanks @Feras95p 

I'm grateful for your feedback.

I also see that you updated your sample file? Great.

I will take a shot at it now using the data.

Cheers!

[Feras95p]

Hey @RobertOdera,

Waiting for your thoughts! Please read my comments above to be the idea more clear.

[RobertOdera]

Sure thing,

I will do and I will ask if I need more clarity.

Cheers!

[RobertOdera]

Here you go, @Feras95p .

Thanks for your patience (I was able to take a look tonight).

Please mark as an acceptable solution + like, if this works for you.

Things to note:

0. Field Summary data investigation. Data cleanse. You have 30% null mobile phone = non customer = row filtered out to reduce noise

1. Your file has some customers (mobile phone) that visited twice on the same date (lunch + dinner? yay!), so that might be tripping folks up (if they use Unique Tool or Sample Tool, first)

The packaged workbook is attached.

Cheers!

[Feras95p]

Hey @RobertOdera ,

It seems that you have done a great job! Thanks for your kind help, unfortunately I couldn't be able to open your workflow due to the difference in the version, do you have any idea on how to do the conversation of the version ?

One more thing I have noticed in your picture attached that, in August 2019 there is only one customer because it was the first day so why he is considered as a returned not new  customer?

[deviseetharaman]

Hi @Feras95p 

I am sorting the records on Mobile number, year and month. so I will mark the 1st entry of the customer as New and also all subsequent entries for the same month also as New. From next month onwards, the number will be considered as Existing. 

[RobertOdera]

Hi, @Feras95p 

That customer is considered Return because he/she visited more than once in the period (min date - max date).

Cheers!

[RobertOdera]

Hi, @Feras95p 

How to do conversion, sure!

Try this link.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Downgrading-Alteryx-to-open-workflow/t...

Credit @CharlieS @danilang 

Otherwise, my workflow are usually commented (please make the time to replicate following those comments).

0. Ignore the Field Summary Tool

1. The first Filter Tool config: !IsEmpty([mobile phone])

2. The first Select Tool: change Date to V_WString

3. The first Formula Tool: String treatments per snippet below

4. First Summarize Tool (top): snippet

5. Filter Tool from Summarize: True if Total Visits =1

6. Select Toola from Filter: keep only mobile phone both

7. Formula Tool from Selects: new field Customer Status = New, new field Customer Status = Return

8. Union from Formula Tool: per snippet below

10. 2nd Summarize Tool (middle): snippet below

11. Union Tool per comments

12. Summarize Tool after Union Tool: per snippet below

13. Transpose Tool after Summarize: per snippet below

14. Multi-Field Tool after Transpose Tool: per snippet below

15. Formula Tool after Multi-Field: per snippet below

Just for due diligence

So this you can disregard (I just suspected that there was a ghost in the data based on your responses to other solutions proffered). Configure Summarize Tool per snippet below + Filter Tool True if Count >1. You have some customers (mobile phone) that had more than one visit in the period min date to max date (hence they are Return customers unless you modify your requirements to be that multiple visits on any Date = 1 visit = New and the visit period is implicitly per year-month).

Cheers!