Count Conversions to alternarte methods

[mente73]

Hello,

I have an e-commerce client with a set of data that would like to “count” the converted to alternate purchase method, recovered clients along with the purchase method and conversion/recovery made by.

Converted to alternate purchase method = Clients who had a rejected/declined/error initial purchase attempt followed by a successful purchase with a different purchase method i.e initial failed purchase with credit card, followed by a successful purchase via Bitcoin
Recovered = Clients who had a rejected/declined/error initial purchase attempt followed by a successful purchase using the same purchase method. i.e initial failed purchase with credit card, followed by a successful purchase via credit card.
By = If the field has the clients name, it means the client did it himself, if the is a name is listed, it means the purchase was made by one of the agents.
The request is to possible list the data in chronological order by client and the number of conversions and recoveries and the purchase method.

I have attached a file with some of the data he is able to extract.

The key to account conversions/recoveries is that there must be a decline followed by a successful.

Sample:
D = Decline Purchase
S = Successful Purchase

Client A on day X had a D, D, D, S, S, S, D, S, D,S, S, D,S
Based on the purchase events and assuming that all Successful were using a different method (Bitcoin).

We expect to generate a report with a summary like:

Client A had 4 Bitcoin Conversions
Or
Client A had. 2 Bitcoin Conversions and 2 Credit card recoveries.
Or
Client A had 3 Bitcoins Conversions and 1 Credit card recovery
O
Client A had 4 Credit card recoveries.

I would really appreciate if you let us know if we can achieve this with a formula or any SQL query.

Thank you in advance

[Glenn Kennedy]

For your sample file, please add your expected results. What do you want to see and where do tou expect to see it?

[mente73]

Hello Glenn,

Thank you for getting back.

The sample has the expected results in columns J,K,L and M
Basically, we would like to see 1's for recovered and converted to alternate purchases, the method it was recovered/converted and the by who. We believe by doing so, at the end of the day/week, we should be able to pivot the data and come up with totals.

Once again, every help is highly appreciate it.

[Glenn Kennedy]

Hi. I've been away. The reason that I asked was that you expected answers don't make sense!!

See the purple cells in L&M and explain why? Also see the yellow cell and explain why Schultz and not Credit Card. I have (what I believe to be) correct formulae in J, K & N

[mente73]

Hello Glenn,

Once again, thank you for your help.

You are actually correct, in my original sample, the K10 was incorrect. That purchase was not converter rather recovered.
Allow me to explain, in your file, the TRUE value should be on J10 as the successful purchase was made using the same method (Credit Card)
The method (M10) should be Credit Card and the "by" should be D.Schultz. (My client can run cards on his website and over the phone, so in this case, the successful deposit was made in house by salesman D.Schultz) If the value on the Created by column is the same for the Client column it indicated that the purchase was made by the client itself no sales person had interaction.

In your file, L13 and M13 same scenario, the TRUE value should be in J13. the Method M13 is correct and the by should be MB257534

Row 7 you actually have it correct. The purchase was TRUE converted(from Credit Card to Crypto) and the method is TRUE Crypto, all we are missing is the by where the value should be BD552236.

All Not recovered are correctly flagged, none of them had a successful purchase.

I hope this helps and let me know if further clarification is needed.

[Glenn Kennedy]

No. You are confusing the situation further by (as far as I can see) incorrectly referring to column J when you mean column K in my sheet. Isn't column J in my sheet OK?I simply don't understand why there should be ANYTHING in (on my sheet) K and N. So explain, what are the criteria to sometimes have a method of payment and sometimes a person's name...

Please close all versions of your original sheet and look only at mine and tell me what's wrong... and WHY.

[mente73]

Hello Glenn

Apologies for any confusion

Looking at your sheet
J Column is OK
K Column is OK
N Column is OK

We just need to include a new column to indicate the "By Who" (the person that did the successful purchase)

Thank you for all your help.

[Glenn Kennedy]

Maybe like this:


=IF(E2<>I2,I2,"")

[mente73]

Hello Glenn,

You are very close and I really appreciate it. The formulas seem to work however the end result does not look as we expect.

I will elaborate based on your latest sheet.

Row 7 Column K is OK, Column N is OK, Column M is not, there should list the name of the "Created by" why? because it was the client who in his second attempt got the successful purchase. There was no sales person involved.

Row 10. Column J should be "Recovered" as the "type" method is the same in both attempts. Column K is OK. Column L should be "Credit Card" and Column M is OK.

Row 13 Same scenario in row 7, Columns k and L are OK yet Column M does not list the created by.

Thanks

[Glenn Kennedy]

nearly htere. Just column J is causing confusing:

You say: "Row 10. Column J should be "Recovered" as the "type" method is the same in both attempts." So... whay

The criteria seem to be emerging by drip-feeding:
For each client (column D):
Complete: blank
Declined: Not recovered:
Declined then complete: recovered???
Error then complete: recovered??

Any others, like:
error
error then declined then complete?
error then declined then declined,
etc, ctc, etc

Please list criteria and result desired exactly as shown above.

[mente73]

Hello Glenn,

Let me break it down.

Transaction status Complete with NO previous Failed/Rejected/Decline/Error attempt: blank
Failed/Rejected/Decline/Error with NO successful attempts (regardless of the type used): Not recovered:
Declined then complete: recovered ONLY if the purchase method = Type (column F) is the same as the decline attempt. IF the type is other than the one in the decline then is a converted.
Error then complete: recovered ONLY if the purchase method = Type (column F) is the same as the decline attempt. IF the type is other than the one in the decline then is a converted.


Lets remember that these transactions are online/in house purchases made by clients.
The Transaction Status column (column D) can be:

Completed = a successful purchase
Rejected = Failed purchase
Decline = Failed purchase
Error = Failed purchase
Type = The method used to make the purchase (Credit Card, Crypto, ACH, Litecoin,Checks, etc)

Let me know if this helps

[Glenn Kennedy]

Are we there yet??

[mente73]

Yes. You got it right as expected.

Thank you for your patience. I really really appreciate it.

[Glenn Kennedy]

No problem. It took a while partly 'cos I've travelled through 3 countries in the last few days, so I haven't been cencentrating too well. But also I don't like being beaten!! Thanks for the rep.

[mente73]

Hello Glenn,

Hope you are not travelling too much.

After testing the formulas with a larger set of data, the results seem to be off.

I am attaching a new spreadsheet with your formula and pointed out some of the discrepancies. Additionally, I updated the criteria for a better understanding

No rush on this one. if you can help would be great.

Thanks