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?

[rupali_bhise]

Hey @Feras95p , 

You need to add datetimeformat on Month column. Attaching workflow for reference.

Good Luck

[RobertOdera]

Hi, @Feras95p 

I think you might be okay to:

1. create a new calculation, say YYYYMM_OrderDate = Left([Order Date],7)

2. use the new calculation in the Summarize Tool and Cross Tab Tool

3. Multi Field to update Null() to 0

4. (optional) add a select tool in the end to rename fields per your desire

Please credit @neilgallen by marking his post a solution + like, if this works for you

I extended his solution for you

Cheers!

[Feras95p]

Hey @neilgallen ,

I tried yours and I checked one day to make sure everything fine and it shows me for that's day  5 new customers and 9 returned while in that day I have the total customers 10 customers 5 out of them are returned !

[Feras95p]

@rupali_bhise  Thank you very much! Really appreciate your efforts. But one more thing, I checked for one day manually to make sure everything is fine. The correct data for that day is that.

Total customers (10)

New customers (5)

Returned customers (5)

but your WF gives me 

New customer (10)

Returned (1)

[rupali_bhise]

Hey @Feras95p , 

I modified data based on various scenario that i have to covered. Can you please share sample data with encrypted mobile number and also share the output screenshot.

If you can check my sample data there were only 3 returned order. and rest of all new customer wrt specific month.

Thanks.

[Feras95p]

@rupali_bhise,

Try this file as input of your WF, you will understand what I meant. For example in this file the total customer On April, 2020 is 1476 and the output from WF gives me 1257 New customers and 185 Returned so the total 1442 customers, where are the others 34!

[rupali_bhise · Accepted solution]

Hey @Feras95p , 

attaching update one. 

This will definitely work. 

[Feras95p]

Hey @rupali_bhise ,

This is the similar WF nothing changed, please have a look into this file which contains all the customers of April 2020.

So as you can see we have 1345 customers in April 2020  and some of them they are ordered more than 1 order  in the same month. So my point is that the output of your WF should tell that me the 1345 customers how many of them are new and how many returned. But why it gives me 1257 new and 185 returned ! I didn't get what's the wrong 😞

[deviseetharaman]

Hi @Feras95p 

Please check if the attached WF works for you

[Feras95p]

Hey @deviseetharaman 

Thanks for your help ! Could you please explain me depending on what your WF categorize the customer as existing. I tested the customer of 9-2019. I took them and use them as input of my workflow then I summarize and grouped by mobile phone and count mobile phone I end up with  358 customers which is correct as the sum of existing and new customer for your WF and then I filtered the customer by count who has more than 1 and I found 40 customers who ordered more than one order at the same month. So, depending on what it shows me 67 existing  and 291 new customers in 9-2019?