Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Returned customers

Feras95p
8 - Asteroid

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?

27 REPLIES 27
rupali_bhise
8 - Asteroid

Hey @Feras95p ,

 

Could you please add some sample data for the same, and is there any reference point based on which we calculate returned customer, e.g.,date  of return.

Thanks

Feras95p
8 - Asteroid

Hi @rupali_bhise ,

 

 

The reference point is the mobile phone, so that if the mobile phone is repeated that's mean he's  a returned customer if it's not repeated that's mean he's a new customer. So the reference point to identify the customer status by checking the mobile phone, but how can I identify the total number of the returned and new customers for each month?   

 

 

Note that, I have attached the sample file but I removed the mobile phones due to privacy.

 

 

Thanks in advance for your kind help.

rupali_bhise
8 - Asteroid

 

Hey @Feras95p ,

Right now, I don't have access of my laptop.

 

Below steps you have to follow:

1. Sort data ascending order with Date of order.

2 use union tool with mobile number

3. Rows which are duplicate are returned customer.

 

Check if its helpful for you.

Thanks

 

 

Feras95p
8 - Asteroid

Maybe you didn't get what exactly I'm looking for, I want to calculate for example in January, 2020 there are 100 orders, 80 orders are for new customers who their mobile phones are not exist before January, 2020 and 20 orders are for the returned customers who their mobile phones are found before January, 2020.

 

I hope now it's clear, take your time while you get the access for the laptop.

 

 

Here is the picture of the output what I'm looking for.

 

1.PNG

RobertOdera
13 - Pulsar

Hi, @Feras95p 

 

The more robust your sample file is, the faster + better the approach yielded from the Community.

Here are is a simple treatment that you can make while protecting your data privacy.

 

Assign a unique number for each Telephone number.

You can keep the dates the same as that doesn't hurt.

 

Post that revised sample file, with just the unique number + dates, and watch the responses fly to you like the wind!

It also means that your solution will be plug and play (substitute unique numbers for the actual data you have behind the curtain).

 

Cheers!

rupali_bhise
8 - Asteroid

Hey @Feras95p ,

 

Below are the updated steps you can follow:

1. Sort data ascending order with Mobile number and then Date of order.

2 Then use union tool with mobile number, so you will get new customers based on their first order date , then convert the format of order date with datetimeformat as Month-Year. And add summaize tool with converted month and distinct count of mobile number.

3. Rows which are available at another side of unique tool (duplicate), those customers are  returned customer. You need to add formatting with Month-Year and then add summarize tool with formatted month and distinct count of mobile number.

4. Then you have point 2 outcome as month and new customer count , and point 3 outcome as month and returned customer. Only you need to join both of them. You will get you desire output.

 

Check if its helpful for you.

Thanks

 

 

deviseetharaman
11 - Bolide

Hi @Feras95p 

 

Please try the attached WF. Remove the first Formula tool. It was just to populate the mobile numbers.

 

customer.png

neilgallen
12 - Quasar

This is a fairly common problem, with a number of different solutions depending on your use case and the level of detail that you're attempting to calculate.

 

In your case, you can find the minimum order month for each phone number, and then join this data back to your order set. If the order date is equal to the minimum date, then this is a new customer. Otherwise this person is a returned customer. See attached workflow.

 

returned customers .png

Excuse the sudden drop in new customers in the mockup. Badly mocked up data!

Feras95p
8 - Asteroid

Thanks it works ! But it gives me day by day I want for each month how can I solve that?

 

Capture.PNG

Labels