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?
Solved! Go to Solution.
Hey @Feras95p ,
You need to add datetimeformat on Month column. Attaching workflow for reference.
Good Luck
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!
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 !
@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)
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.
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!
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 😞
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?