11 Replies Latest reply on May 6, 2017 11:23 PM by Ignas Dukynas

    Number of Days between first purchase and second purchase

    Henry Carlson

      Hello! I have a question regarding how to calculate the time between a customer's first purchase and their second purchase using an Order Date field. I have looked at the threads below for ideas, but have been unable to formulate a solution from them. I would really appreciate some help with this issue, especially since it seems to be a recurring question.

       

      Here's where I have looked unsuccessfully for inspiration:

      http://kb.tableausoftware.com/articles/knowledgebase/first-date-purchasedhttp://kb.tableausoftware.com/articles/knowledgebase/first-date-purchased

      Average Repurchase Rate with weeks after Date of First Purchasehttp://community.tableau.com/thread/131004

      Time between purchases? For probability of repurchase report.http://community.tableau.com/message/181479#181479

      Calculated Time Interval Between Actionshttp://community.tableau.com/message/165710#165710

       

      I can't post my data for confidentiality reasons, but I have attached Tableau's SuperStore data with some preliminary work that I've done. Using the attached copy of the SuperStore data, can someone please help me find the following?

       

      1) Date of each customer's second order

      2) Each customer's number of days between their first and second order

      3) The average number of days between the first and second order across the customer base.

       

       

      Thanks very much!

      Henry

        • 1. Re: Number of Days between first purchase and second purchase
          Henry Carlson

          I've built a calculated field called "Days Between 1st and 2nd Purch (hopefully!) using the following equation, as inspired from one of the threads I listed in my original comment above:

          MIN([Order Date]) - LOOKUP(MIN([Order Date]),-1)

           

          I'm trying to use this equation in the following way, but I keep receiving "Out of Memory" errors on my 32-bit machine:

          1) I've placed this calculated field on the Text shelf of my sheet entitled "Order Dates by Customer Number" in the workbook I attached to the comment above.

           

          2) I've clicked Edit Table Calculation, clicked Compute Using, and selected Advanced. That takes me to the screen below

           

          Capture.PNG

           

          The partitioning shown above makes sense because I do want my equation to be applied to each Order in a Customer ID, but not applied to the last Order Date in one Customer ID and then the first Order Date in the next Customer ID. So click OK on this screen and then run the calculation. The calculation runs for about six seconds and then tells me I'm out of memory, even when Tableau is the only thing open.

           

          To anyone reading this thread, how does this approach seem? How else can I answer the three questions I posed in my original post?

          • 2. Re: Number of Days between first purchase and second purchase
            Michel Caissie

            Henry,

             

            You will need a bunch of calculated fields to do this.

             

            Check in the attached.

             

            Check the Order Dates by Customer Number  to understand the logic behind the calc. Pay attention to the  Compute using - Advanced section or each calculated field.

             

            On the Final View , it`s only a duplicate of the same sheet but the unwanted columns are hidden (unselect Show Header), and a filter is used to keep only the first row of each customer id.

             

            Michel

            • 3. Re: Number of Days between first purchase and second purchase
              Henry Carlson

              Hi Michel,

               

              Thanks for sending this along. I haven't yet looked at it, but I wanted to say thanks before too much more time passed and you thought I was rude. I'll take a look at this tomorrow morning and test it in my own copy of the SuperStore sample data set and in the real dataset I'm actually working with.

               

               

              Henry

              • 4. Re: Number of Days between first purchase and second purchase
                Henry Carlson

                Michel,

                 

                I looked over your solution and it makes complete sense to me. Thank you for sending me your workbook.

                 

                I have tried to recreate your solution in my copy of the SuperStore dataset, though, and that's where I have an issue. My computer crashes out with an "out of memory" error when I use Edit Calculations, click Advanced, and then set the partitioning for the First Order or the Second Order calculations.

                 

                Is there a less memory-intensive method to identify each Customer ID's first and second order?

                 

                 

                 

                Henry

                 

                 

                Capture.PNG

                • 5. Re: Number of Days between first purchase and second purchase
                  Michel Caissie

                  Can`t help much with that.   How much ram do you have on this computer, how much is not in use ?

                  Superstore is a very small dataset, can you open it in Excel ?

                  • 6. Re: Number of Days between first purchase and second purchase
                    Jonathan Drummey

                    I'm on my phone and can't look at this in detail, but I do know that you can get out of memory errors when the Compute Using of a table calc is on a date or datetime dimension and Tableau triggers domain completion and tries to create a value for every possible combination of date and the other dimensions in the view.

                     

                    To avoid unwanted domain completion, make all your date dimension pills on Rows, Columns, & Pages discrete measures using ATTR/MIN/MAX/etc. (you might need some calculated fields to do this), and put the needed date dimensions on the Level of Detail Shelf.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Number of Days between first purchase and second purchase
                      Henry Carlson

                      Hi Michel,

                       

                      I just tried rerunning your solution in a small subset of SuperStore (I deleted about 90% of the rows in the original SuperStore extract) and now the solution is returning rows for dates that don't have any order attached to them at all. For example, there isn't an order ID for Order Date 1/22/2010 in the screenshot below, but you can see that a row appears for Order Date 1/22/2010. Could you please let me know what I have done wrong? I don't think I did anything different than you did, but clearly I am getting a different result set. I have also attached a workbook containing my implementation of your solution up to calculating the First and Second Order Dates.

                       

                       

                      Henry

                       

                      Capture.PNG

                      • 8. Re: Number of Days between first purchase and second purchase
                        Henry Carlson

                        Michel,

                         

                        Never mind. I figured out what you did and now my result set matches yours, except for the rows I deleted. You had the advance calculation address both Customer ID and Order Date. When I do that in my dataset, my result set looks like yours. Very nicely done, Michel!

                         

                        Henry

                         

                        Capture.PNG

                        • 9. Re: Number of Days between first purchase and second purchase
                          Henry Carlson

                          Hi Jonathan,

                           

                          I already have my date fields as discrete, but it sounds like I should also use the ATTR function on these already discrete fields, and then place these ATTR-modified fields on the shelves? Did I correctly understand your advice?

                           

                          Henry

                          • 10. Re: Number of Days between first purchase and second purchase
                            Jonathan Drummey

                            Hi Henry,

                             

                            From the screenshot you'd posted where you were running out of memory, you had the Compute Using on Order Date, partitioning on Customer ID. Tableau's default densification behavior in this case is to "complete the domain" of Order Date for each Customer ID, so in my copy of Superstore Sales that means that Tableau will be creating 4.8 million entries (1427 Order Dates for 3403 Customers). One key (among several) to triggering densification is the presence of one or more dimensions on Rows, Columns, and Pages, so by removing the triggering dimension(s) from those Shelve(s) we can stop the densification. However, those dimension(s) may still need to be "in the view" for calculations to work and we may still want something like them on Rows, Columns, and/or Pages to have the display be what we want. So we can put the dimension onto the Level of Detail Shelf, and use an aggregate equivalent such as ATTR() for the dimension to get the layout.

                             

                            The workaround I described therefore looks like this:

                             

                            Screen Shot 2014-07-06 at 5.39.18 PM.PNG

                             

                            A table calc with a Compute Using on Order Date only will not trigger domain completion with this arrangement.

                             

                            Does that make sense?

                             

                            Jonathan

                            • 11. Re: Number of Days between first purchase and second purchase
                              Ignas Dukynas

                              There is a video explaining how to calculate number of months between first and second purchase. It will be very easy to apply to this case:

                              Finding the Second Purchase Date with LOD Expressions