Formula to determine user conversion or loss

1. Formula to determine user conversion or loss

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

2. Re: Formula to determine user conversion or loss

No Attachment.

3. Re: Formula to determine user conversion or loss

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.

4. Re: Formula to determine user conversion or loss

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.

5. Re: Formula to determine user conversion or loss

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

6. Re: Formula to determine user conversion or loss

Am I being ignored?

7. Re: Formula to determine user conversion or loss

Originally Posted by 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.
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?

8. Re: Formula to determine user conversion or loss

Originally Posted by 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.
I have now - apologies for the oversight!

9. Re: Formula to determine user conversion or loss

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

10. Re: Formula to determine user conversion or loss

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.

11. Re: Formula to determine user conversion or loss

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.

HTML Code:
=IF(OR(\$A3=\$A2,\$A1<>\$A2),"",IF(\$G2="N",IF(\$G1="Y","Loss",""),IF(\$G1="N","Conversion","")))
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.

12. Re: Formula to determine user conversion or loss

Originally Posted by 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.
Hi AliGW and many thanks for this. I've managed to sort it out.

13. Re: Formula to determine user conversion or loss

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.

14. Re: Formula to determine user conversion or loss

Originally Posted by 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.

HTML Code:
=IF(OR(\$A3=\$A2,\$A1<>\$A2),"",IF(\$G2="N",IF(\$G1="Y","Loss",""),IF(\$G1="N","Conversion","")))
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.

15. Re: Formula to determine user conversion or loss

Originally Posted by 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.

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

Attachment 637481

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1