how to put condition users on tableau

Question

im mysql user, i have data source like this

User_id     | Order_id    | Status_id | createdAt  | Transaction_Amount

user_id as the user, order_id as the id of the order, status_id as the status of each order, createdAt as the date of the transaction .

in MySQL, i divide the condition of the users in 4 conditions.

conditions 1 new_user

the user who doing FIRST transaction in date range, and not doing transaction before the date range

conditions 2 repeat_user

the user who doing transaction before the end of date range, and doing atleast 1 more transactions in date range

conditions 3 existing_user

the user who doing his first transaction before the date range, and doing atleast 1 more transactions in date range 

conditions 4 unique_user

the user who doing transaction in date range

i've done it with the queries in mysql, but i want to visualize it in tableau, i already make the filter of the time range, but how to make the conditions to based on condition user

Answer 1

Assuming that you want to have create these conditions in tableau, I propose a solution like this. Since you've not provided any data to work upon, a data I used in excel is as

View-1 add a Calculated field(CF) First Transaction of User as

{FIXED [user_id]: MIN([CreatedAt])}

Drag this field on filter instead of created at and you'll get a view based on your condition_1. Screenshot

View-2 (I am assuming that both transactions may have been completed within the filtered date range. If you need otherwise please specify).

• step-1. drag user_id to rows, createdAt at filters (select range of dates).
• Step-2 Drag order_id to filters again select count distinct, select at least 2

You'll get a view like this

View-3 Add two CFs as. 1st condition 1 as

Min([First Transaction of User])< min([CreatedAt])

another CF condition 3 check2 as

{Fixed [user_id]: COUNTD([order_id])}>1

Add both these CFs on filter shelf along with createdAt. Add condition-1 check2 to context (important step) select TRUE from both filters and you'll get desired view. like this

View-4 It is the most basic chart in tableau. Try and I am sure you can do it. Good luck

Answer 2

From your workbook it is clear that you want cohort analysis. In this regard Let me explain a few things..

First create a user cohort like this

{FIXED [user_id]: MIN([CreatedAt])}

Output of this field will be date when user first ordered. So if you will add this field to filters the output will be users (added in view) who first ordered within the filtered date/range.

Now if you want to see the users' orders during the same period (condition 1) also add the createdAt field to filters with same date/range. E.g. suppose your date range is 5jan to 15 jan. Then set both filters to these dates range. First filter will ensure to filter customer with their first transaction and second one will give you desired output.

For condition2, you'll have to add countd(order_ids) to filters with filter value set 'at least 2'.
E.g. setting this filter will ensure that customer did their first and at least one more transaction during the date range.

For condition3, changing dates in filter accordingly will do. Nothing extra to be done.
E.g. suppose you want the customers with second and onward transaction between 5 jan to 15 jan. Then you will have to set first cohort filter with date upto 4 jan but second date filter to 5 jan to 15 jan

Condition 4: remove cohort from filters. Set date range on createdAt field only.

If you want to add another filters like status etc., These will work after you add them to filters.

Try this