LOADING

Type to search

Tableau LOD UseCase#4 : Purchase Analysis Through LOD

Share

During my Corporate Tableau Training in Gurgaon , i get questions many time regarding use cases in LOD in Tableau . We have done various analysis from time to time to understand the different concept in Tableau. This time we will do the Purchase analysis to understand the concept of LOD.

Question:   How many Customers waited for How many Quarters to make their second purchase?

Let’s move ahead to analyze this question through Tableau.

For Corporate training and Online Training contact at TJT@TECHNICALJOCKEY.COM

Step-1:

It is very easy to analyze the various dates on which a Customer has ordered. All we have to do is Drag Customer ID, Customer Name to the Rows shelf from the Data Pane. Next, place Order Date to the Rows shelf and right click it to change the date type in the following way:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 29

The result will be as follows:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 30

The above viz tells us all the days when a customer has ordered. But things get tough when we want to analyze the First order date of Customers & not all the Dates or days.

Step-2:

Moving ahead, we want to analyze the First Order Day of each Customer. This can be done through LOD  (level of detail) expression.

Create a LOD expression by opening a calculated field with the following formula:

{FIXED [Customer ID] : MIN([Order Date])}

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 31

This formula will give us the first date of Order of every Customer as we are Fixing the Customer Id on the basis of Minimum Order Date. We get a new field First Purchase in the Data pane Dimension section.

Now, let’s make our viz  to show the FIRST Order Date of every Customer. So, drag Customer Id, Customer Name & First Purchase to the Rows Shelf. The result will be as follows:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 32

Note: we need to change the DAY(First Purchase) in the same way as we changed the Order Date in Step- 1.

Step- 3:

Make another Calculated Field “ Repeated Purchase” with the following formula:

iif([Order Date]>[First Purchase ],[Order Date],null)

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 33

This will give the Repeated Purchase date of all the Customer leaving out the First Purchase Date. We get a new Data field in the Dimension section of the Data Pane “Repeated Purchase”.

Let’s add this new field to the Viz we made earlier. Drag Repeated Purchase to the Rows Shelf in the Viz next to Day(First Purchase). The result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 34

Note: we need to change the DAY(Repeated Purchase) in the same way as we changed the Order Date in Step- 1.

Step- 4:

Let’s make another calculated field “Second Purchase” to analyze the Second purchase date of every customer with the following formula:

{FIXED [Customer ID] : MIN([Repeated Purchase])}

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 35

We get a new field in the data pane Dimension section as “ Second Purchase”.

Let’s drag it to the previous prepared Viz to get the Second Purchase date of every Customer. Remove the Repeated Purchase field from the Rows Shelf. The result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 36

This Viz gives us the detail of the First & Second Purchase date of every Customer along with their Name & ID.

Note: we need to change the DAY(Second Purchase) in the same way as we changed the Order Date in Step- 1.

After we have achieved the first purchase date and Second purchase date, we have to find out the difference in Quarters between the 2 Purchase dates if any.

For this we make a calculated field- Quarters Btwn Repeated Purchase.

(I know its becoming little confusing to understand that why are we making so many Calculated fields? But please remember, this is advance level and attaining the answers to our Analytical Questions are not always easy. Please follow each and every step and send some time to understand each step before going on to the next step n I believe it will not be tough.)

Step- 5:

We make a calculated field “Quarters Btwn Repeated Purchase” to find out the difference in Quarters between the First & the Second Purchase. The formula that we use is:

DATEDIFF(‘quarter’,[First Purchase ],[Second Purchase ])

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 37

We get a new field in the Dimension section of our Data pane as “ Quarters Btwn Repeated Purchase”. Let’s use this in our Viz by Dragging this field from the data Pane to the Rows Shelf and dropping it over Day(Repeated Purchase). The Result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 38

So now, our Viz is Showing us the Customer Id, Customer Name, the First & the Second Purchase date of the Customers and the difference in the Quarters between the First and the Second Purchase date.

Finally, we have attained the answer to a part of our Question i.e what is the difference in Quarters between the Repeated Purchase or rather how much difference is there between the two Orders by a Customer. But our Complete question was: How many Customers waited for How many Quarters to make their second purchase? So, now, we have to find out How many Customers waited for How many Quarters.

Step- 6:

  1. Drag Quarters Between Repeated Purchase from the data Pane to the Column Shelf. The result Viz will give us the Values from NULL to 15. Right Click on Null and Choose Exclude.

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 39

  1. Drag First Purchase from the Data Pane to the Rows Shelf. The result will be: Years(First Purchase). From the + drill down on Years(First Purchase) get Quarters(First Purchase) and remove Years(First Purchase) from the viz. The result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 40

Note: we need to change the QUARTER(First Purchase) in the same way as we changed the Order Date in Step- 1.

  1. Drag Customer Id from Data Pane to Color shelf in Marks Card. You will get a poop up. Choose Add all members:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 41

The Result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 42

But we want Distinct Count of Customers and not each Customer Individually. So Double Click on the Customer Id in the color shelf and take the cursor to the start and type CountD([Customer Id)] and hit Enter

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 43

Note: Please take care of the parenthesis. You can also double click on Customer Id that is in the Color Shelf & erase it by Backspace from the keyboard and Type CountD([Customer Id)] and hit Enter from the keyboard.

The Result will be as below:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 44

  1. Now the formatting part: Click on Tooltip and make the following Changes

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 45

Add  Point Annotation to the Viz to finally give the finished look. And we have the answer to our Question:

Tableau LOD UseCase#4 : Purchase Analysis Through LOD 46

For Corporate training and Online Training contact at info@instrovate.com
Tags:

You Might also Like