Skip to content
Advertisement

Calculate conversion rate with the specified conditions

Here is my sample data table:

ID Status
1 New
1 Processed
2 New
2 Processed
3 New
3 Processed
4 Processed
5 New

What I am trying to solve here is calculate the conversion rate from Status ‘New’ to Status ‘Processed’. From the dataset, only ID no.1,2 and 3 fulfilled the requirements of my problem, and ID no.4 and 5 do not have both stages. So by theory, the conversion rate should be 3/5 * 100% = 60%. How can I select the data in order to calculate the IDs that have both ‘New’ and ‘Processed’ status.

This is the code that I have tried but I know its wrong since it extracts all the IDs with no link between it.

Advertisement

Answer

We can use conditional aggregation here:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement