1 2 Previous Next 16 Replies Latest reply on Aug 29, 2019 5:08 AM by Zhouyi Zhang

Calculate conversion rate on a independent date t

Hallo Tableau Community,

I am stuck with a problem I have an idea how to solve but cant do it in tableau:

In my case, I want to calculate a conversion rate from status A to Status B of a lead. I have the timestamps when status A and Status B were reached.

lead_id |  status A      |  status B      |

1          |  2018-06-01|  2018-06-23 |

2          | 2018-05-01 | Null               |

3          | 2018-05-02 | 2018-05-09  |

4          | 2018-04-02 | 2018-06-10  |

5          | 2018-05-01 | 2018-06-09  |

We now want the conversion rate at a time/date t (t is a variable). Conversion rate from A-B in June would be:

all leads with status B dates in June

divided by

all leads that had a date in status A before or in June, that didn't convert into status B before June. So with the data sample above:

/

(lead 1, 2, 4, 5) = 4  = 75% CR in June

Now I want to do this for let's say all months of 2018 and have a nice bar chart. I'm stuck here because I either have a from data format to do this or I not seeing something. My calculated field looks like that:

COUNT(B)

/

(RUNNING_SUM(COUNT(IF NOT ISNULL(A) AND ISNULL(B) THEN 1 ELSE NULL END)) + RUNNING_SUM(COUNT(IF NOT ISNULL(A) AND B =  ??? here should be t ??? THEN 1 ELSE NULL END)))       <----I need the condition only to count Status A, that have a conversion of B in June, not before! Because they already converted to B before and could not convert to B in June again.

I need a from the data independent variable t. How to exclude lead 3 from my conversion rate calculation for June?  I can do a CR number for specific months, but I want it running for all months of the year. Any ideas?

• 1. Re: Calculate conversion rate on a independent date t

Hi, Dimitriy

Can you explain why in your calculation lead 3 is missing from the denominator?

/

(lead 1, 2, 4, 5) = 4  = 75% CR in June

And can you provide sample data?

ZZ

• 2. Re: Calculate conversion rate on a independent date t

Hey ZZ,

its excluded in the denominator because its converted in May, not in June! As I said, I want the conversion rate from A to B in June. That means I only count the B status changes in June.

• 3. Re: Calculate conversion rate on a independent date t

And Null value for B is included?

ZZ

• 4. Re: Calculate conversion rate on a independent date t

HI, Dimitriy

I hope I understand your requirement correctly.

to achieve this, I will suggest to join your data to a calendar table with key 1=1 as shown below

Create 2 calculation fields

Create view

A sample workbook attached for your reference.

Hope this helps

ZZ

• 5. Re: Calculate conversion rate on a independent date t

Hey jeah I had the idea with an independent time to, but how did you join on a calender? Looks good from here! ill look inside it!! Thanks a lot. If I have questions, ill come back!

• 6. Re: Calculate conversion rate on a independent date t

I think it could be the right direction but because of the join there are too many records overall that we divide by.

For example: You are dividing also by recoreds where the previous status is null. These records should not count

• 7. Re: Calculate conversion rate on a independent date t

yes, as we count all potential A statuses that are ready to convert to B. So if B is Null, A hasnt converted yet so it should count for the denominator from the day Status A was reached

• 8. Re: Calculate conversion rate on a independent date t

The join just made the combination of calendar with the data, but for anything you want to exclude, we can create calculation as filter to filter them out, like the one I did for state B.

This is a very interesting case, any further questions are welcome

ZZ

• 9. Re: Calculate conversion rate on a independent date t

Jeah as I said, the idea helped me just had to adjust the filter and cr calculation a bit:

So the data looks like that:

(so in this case its awaiting docs -> full customer)

So as you can see, the numerator only counts the targeted status that has the same month as our calendar, but the filter shows for two entries true. so, therefore, the denominator counts the entries for every month the lead was in the status awaiting docs. Obviously, because the lead was in february already in that status and didnt convert and stuck there till march where it converted.

Hope this will help other people too

1 of 1 people found this helpful
• 10. Re: Calculate conversion rate on a independent date t

Cool and glad you figure it out by yourself finally.

ZZ

• 11. Re: Calculate conversion rate on a independent date t

Can you elaborate why its important to create a calendar and join it with the data?And how do you create a calendar? Sorry I am learning this!

• 12. Re: Calculate conversion rate on a independent date t

Ok I created a calendar with all the possible dates that could be present in my data source.I am trying to join it with my original data source, key 1=1 does a cross join right? But I am not finding that option when I connected both data sources to tableau.Could you elaborate on how you connected to both different data sources and then created a cross join? I think I am missing on ways to upload 2 datasets properly.

Thanks!

• 13. Re: Calculate conversion rate on a independent date t

Hi, Shweta

If your data sets from two excel files, you need create two connections.

ZZ

1 of 1 people found this helpful
• 14. Re: Calculate conversion rate on a independent date t

Hi ZZ,

thanks for your response. But on what field did you join? I created a calculated field called 'dummy' variable which has all 1s in my data source.My other data source is the calendar also with dummy variable ( all 1s).I was planning to join on the dummy variables in both the data sources, but Tableau doesn't perform joins on calculated field.

Did you join differently?

1 2 Previous Next