Formula to determine user conversion or loss

[pandeion]

Hi guys,


Working on a user conversion topic at the moment and trying to come up with a formula to determine if a user didn't use our product in the past and is using it now can be classed as a CONVERSION and if he has used it in the past and doesn't anymore to be classed as a LOSS - attached is a spreadsheet for example ,where my product is ABC.

Couldn't think of a single formula rather than using lots of additional columns to help me achieve this.

Any ideas?

Appreciate any help.

Ionut
User conversion_loss example.xlsx

[modytrane]

No Attachment.

[AliGW]

You never replied to the response you received to your first ever thread here - we do expect you to do so out of courtesy to the respondent, even if their reply did not help. Please revisit that thread and do so. Thanks.

I will look at your attachment.

[AliGW]

OK - I've had a look. The problems are:

1. You have provided no sample answers on the column where you want the result - please add about 20 lines - ,anually calculated.
2. What are the criteria for a conversion? It is not apparent to me from the workbook provided.

[pandeion]

Hi modytrane. Sorry, it seems i've missed it

[AliGW]

Am I being ignored?

[pandeion]

OK - I've had a look. The problems are:

1. You have provided no sample answers on the column where you want the result - please add about 20 lines - ,anually calculated.
2. What are the criteria for a conversion? It is not apparent to me from the workbook provided.

Thank you for looking into this.

1. The column for the result should only say "Conversion" or "Loss"
2. The criteria for a conversion is: if the same user has been using a different product at a previous date and now (given by the most recent date) is using ABC - it can be classed as a Conversion. And the other way around, if he was using ABC previously and not anymore -> to be classed as a Loss.
* The column with the date, can be used to determine first, last, previous Product usage.

Hope the above makes sense?

[pandeion]

You never replied to the response you received to your first ever thread here - we do expect you to do so out of courtesy to the respondent, even if their reply did not help. Please revisit that thread and do so. Thanks.

I will look at your attachment.

I have now - apologies for the oversight!

[AliGW]

Why is the data not in chronological order? Is that correct? Is the real data out of order?

[AliGW]

This won't give everything you want, but give it a go:

=IF(G2="Y",IF(LOOKUP(2,1/(($A$2:$A$40=A2)*($E$2:$E$40=MINIFS($E$2:$E$40,$A$2:$A$40,A2))),$F$2:$F$40)<>"ABC","Conversion","New"),"")

I really need to see manually calculated outcomes in your sample workbook to go any further.

[modytrane]

Look at the attached file.
I've added a button to sort the data so names are in alphabetical order and dates are chronological.

It helps to make the formula simple and easy to follow the logic.

I've added this formula to column H.

Here's the logic used.

IF a name is first and last [used only once] then result is blank [doesn't matter what they use because it's not a Loss or Conversion].
If the name is not last in a list, again it doesn't matter because we only have to look at usage for last occurance.
If the name is last, we compare previous usage with current and determine if it's a Loss or Conversion.

If this doesn't match you expectations, please provide a detailed logic with different scenarios.

[pandeion]

This won't give everything you want, but give it a go:

=IF(G2="Y",IF(LOOKUP(2,1/(($A$2:$A$40=A2)*($E$2:$E$40=MINIFS($E$2:$E$40,$A$2:$A$40,A2))),$F$2:$F$40)<>"ABC","Conversion","New"),"")

I really need to see manually calculated outcomes in your sample workbook to go any further.

Hi AliGW and many thanks for this. I've managed to sort it out.

[AliGW]

Glad it got you started. How did you resolve it? We ask that you say so for the benefit of others reading this thread in future.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[pandeion]

Look at the attached file.
I've added a button to sort the data so names are in alphabetical order and dates are chronological.

It helps to make the formula simple and easy to follow the logic.

I've added this formula to column H.

Here's the logic used.

IF a name is first and last [used only once] then result is blank [doesn't matter what they use because it's not a Loss or Conversion].
If the name is not last in a list, again it doesn't matter because we only have to look at usage for last occurance.
If the name is last, we compare previous usage with current and determine if it's a Loss or Conversion.

If this doesn't match you expectations, please provide a detailed logic with different scenarios.

Hi modytrane. Thanks for this - I've managed to use your solution. Had to only add that if it's a new name and using our product consider it a Conversion (even if technically it isn't). Many thanks once again.

[pandeion]

Glad it got you started. How did you resolve it? We ask that you say so for the benefit of others reading this thread in future.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Used modytrane's above logic and added an extra simple bit to consider new users using our product as converted.

Attachment 637481