Data Analytics Tableau

LOD in Tableau Use Case 4 – Customer Second Purchase Analysis

Akriti Lal August 4, 2019

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 info@instrovate.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:

The result will be as follows:

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])}

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:

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)

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:

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])}

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:

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 ])

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:

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:

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.

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:

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

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

The Result will be as below:

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

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: