1 Reply Latest reply on Jun 23, 2017 7:04 AM by Stephen Rizzo

    First and Last Online and Offline Transaction Dates on customer segmentation

    cristina hernandez

      Hi there,

       

      I am trying to create a dynamic report in Tableau but I am having few problems with first and last transaction dates online versus offline.

       

      Find below what I am trying to do:

       

      The customer by purchase history definitions are:

       

      New Customers Total:

      IF [FIRST_SALE_TXN_DATE]>= DATE([Today's Date] - [3 months]) THEN 1 ELSE 0 END

      Active Customers Total:

      IF [LAST_SALE_TXN_DATE]>= DATE([Today's Date] - [10 months]) AND

      [FIRST_SALE_TXN_DATE]<= ([Today's Date] - [3 months])then 1 ELSE 0 END

      Lapsing Customers Total:

      IF [LAST_SALE_TXN_DATE]>= DATE([Today's Date] - [12 months]) AND

      [LAST_SALE_TXN_DATE]< DATE([Today's Date] - [10 months])

      THEN 1 ELSE 0 END

      Lapsed Customers Total:

      IF [LAST_SALE_TXN_DATE]>= DATE([Today's Date] - [24 months]) AND

      [LAST_SALE_TXN_DATE]< DATE([Today's Date] - [12 months])

      THEN 1 ELSE 0 END

      Dormant Customers Total:

      IF [LAST_SALE_TXN_DATE]< DATE([Today's Date] - [24 months])

      THEN 1 ELSE 0 END

       

      The first and the last transactions are coming from SQL calculations already set up (I didn’t do the calculation in Tableau). The problem I find is that is taking the first ever transaction date so when I filter by online, it can tell me that a customer is active because the first transaction happened more than 3 months ago but in reality the first online transaction happened 3 months ago(so for online this customer is new customer). I am not really sure how to differentiate online first/last transaction from offline first/last transaction.

       

      I have tried fixed calcs etc, but not sure if it is labelling my customers based on their online or offline first/last transactions only (a customer could have made both online and offline transactions over a period of time)

       

      I wont it to be dynamic so if you select a date (from the parameter today’s date) it will show me what happened from that date backwards (ignoring the future), by brand, channel and if they are in the reward programme or not.

       

      Any idea on what is going on?

       

      Thanks very much