9 Replies Latest reply on Mar 7, 2017 2:43 PM by Ci Song

Ask for help -- how to calculate based on each measure value

Hello everyone! I'm creating some distribution charts recently and have a question -- i have a raw data table (attached) having user_id, stage(impression-->visit-->conversion) and # pf exposure for each stage and each user_id. I want to create a chart showing:

1. for each user_id, how many impressions, visit and conversion in total (I've done this part using SUM(IIF([Stage]='Impression',[#],0)), etc. )

2. distribute the conversion rate(conversion/visit) based on # of impressions (what's the conversion rate if there's 1 impression in the conversion path; what's the conversion rate if there're 2 impressions in the conversion path....what's the conversion rate if there're 2000 impressions, etc..) this can be easily done by using excel and pivoting the tables, but i don't know how to visualize the chart in the tableau.

Attached are the excel mock up data with the chart i want to create.

• 1. Re: Ask for help -- how to calculate based on each measure value

Hi Song,

Hope it Helps!!!

Thanks

Deepak

1 of 1 people found this helpful
• 2. Re: Ask for help -- how to calculate based on each measure value

Thanks so much for the quick answer!!! that's exactly what i want to show. Super Appreciated!

• 3. Re: Ask for help -- how to calculate based on each measure value

similar to my # 2 question: what if i want to show the distribution of the Conversion Rate based on the Conversion Duration (days differences between Conversion to Impression for each user ID)? for example, for user1 and user2, the Duration from Impression to Conversion is 3 days, so the overall conversion rate for 3days is 29%: [2/(2+5)]. and for user3, there's no conversion, so just filter that out...

not sure whether we can realize it in Tableau.

Thanks for the help!

 user id stage date # user1 Impression 10/25/16 7 user1 Impression 10/26/16 5 user1 Visit 10/27/16 2 user1 Conversion 10/28/16 1 user2 Impression 10/27/16 10 user2 Visit 10/28/16 5 user2 Conversion 10/30/16 1 user3 Impression 10/26/16 7 user3 Visit 10/27/16 1 user4 Impression 10/28/16 3 user4 Visit 10/28/16 1 user4 Conversion 10/28/16 1 user5 Impression 10/29/16 5 user5 Visit 10/30/16 6 user5 Conversion 10/30/16 1
• 4. Re: Ask for help -- how to calculate based on each measure value

What is the formula you are using here for rate. Let me know. I will do this too.

• 5. Re: Ask for help -- how to calculate based on each measure value

Hi Deepak, the conversion rate=total # of conversion/total# of visit.

Thanks a lot!

• 6. Re: Ask for help -- how to calculate based on each measure value

Tell me with an example for User 1. I am almost done. Waiting for your this formula only.

• 7. Re: Ask for help -- how to calculate based on each measure value

sure! for user1, it will be 1 conversion/ 2 visit = 50%.

thanks!

• 8. Re: Ask for help -- how to calculate based on each measure value

Hi Song,

This was little tricky. Please check screenshot and attached and don't hesitate to mark Helpful and CORRECT.

Thanks

Deepak

1 of 1 people found this helpful
• 9. Re: Ask for help -- how to calculate based on each measure value

perfect! you're actually helped even further by adding the fixed conversion rate by user_id. My problems are solved 100% now.

Thanks again for the quick help!