7 Replies Latest reply on Feb 3, 2014 5:17 AM by Tim Buck

    Monitoring New vs Repeat Customer Visits

    Tim Buck

      Hello,

       

      I'm trying to count the number of unique customers over a period of time, and display it as a stacked bar graph showing total customers per week and coloring for new vs returning. I can work out the logic in my head, but I can't figure out what kind of calculation I need in Tableau.  Here's the logic:

       

      IF (Current Customer ID) = (Customer ID found in any previous week) then 1, ELSE 0

       

      With that - I can filter for 0,1 in color and be done.  But I can't seem to figure out how to tell Tableau to go back in time and search for customer IDs.

       

      The closest solution is http://community.tableau.com/thread/136724 "Identifying Repeat Visits in Retail" But the concatenating logic won't work here, because I'm using tableau to filter by week number, or month, etc - not a hard date.  I've also found other posts @Brad Llewellyn and Joe Mako that are close... but not exactly what I need to do.  Any help would be appreciated.

       

      I've attached an example workbook of what I'm trying to do. 

        • 1. Re: Monitoring New vs Repeat Customer Visits
          Joshua Milligan

          Tim,

           

          How about something like this?

           

          0.png

           

          What I did was take the MIN([Order Date]) per customer (Customer ID in the level of detail defines that it is per customer).  That gives me the first time they placed an order.  Then I can count the distinct IDs (also per customer -- so it is always 1) and stack those on top of each other to get a bar chart, a reference line gives the total label.

           

          This is only one approach -- there certainly are others and I'd be happy to answer any questions or work through other possibilities with you.

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Monitoring New vs Repeat Customer Visits
            Tim Buck

            Hey Josh,

             

            Thanks for the quick reply.  I think this is getting here - but not quite all the way.  I need to show that in the second week both groups of customers.  A stacked bar with blue for new, and orange for returning for example. I'm open to other tricks you have in your tool box....

            • 3. Re: Monitoring New vs Repeat Customer Visits
              Tim Buck

              Or what if we tried just tried creating a flag calculated field.

               

              IF [Min Week of Order] = ATTR(DATEPART('week',[Order Date])) THEN 1 ELSE 0 END


              And then putting that field on colors.  If the Customer ID is in the level of detail then it should break up the colors. I think I'm getting stuck with needing to 'aggregate' the second part there. But do you see where I'm going?

              • 4. Re: Monitoring New vs Repeat Customer Visits
                Matt Lutton

                I'm not sure how to color the new versus repeating customers, but I went ahead and took Joshua's workbook and created another version of the same graph.  This uses the Table Calc SIZE() technique #3 outlined on his blog post here--no reference line is needed to label the bars in this view.  Just another approach at the same solution.

                Slicing by Aggregate | VizPainter

                • 5. Re: Monitoring New vs Repeat Customer Visits
                  Joshua Milligan

                  Matt,

                  Thanks for the link!  It definitely could be a "Slicing by Aggregate" type of solution.  The trick here is not only identifying "new" customers but also representing the old ones as well.  I've got a potential solution I'll post shortly.

                   

                  Regards,

                  Joshua

                  • 6. Re: Monitoring New vs Repeat Customer Visits
                    Joshua Milligan

                    Tim,

                     

                    How about this:

                     

                    0.png

                     

                    The key here is using blending to test if the customer had a sale in any week prior to the week of interest.  This could be done using table calcs too, but likely would be more complex (as you'd have a couple of layers of table calcs) and would also probably be less efficient (especially if you have lots of history in the data).

                     

                    Here's an outline of the solution:

                    1. Create a copy of the data source.
                    2. Blend at the level of Customer ID (but not date)
                    3. Create a calculated field to test if the customer has a week of MIN(Date from the blend) that is less than the week of MAX(Date) from the primary source.  This calculation will be true if the customer has orders in any previous week.
                    4. Customer ID has to be in the view to define the level of detail.  So I used a couple of table calculations to WINDOW_SUM for every customer and then filtered to keep only the first customer.  (In production, I'd wrap those calculations in an IF FIRST() == 0 THEN ...calculation... END  for performance).

                     

                    I hope this helps!  I'd be happy to answer any questions you might have!

                     

                    Regards,

                    Joshua

                    • 7. Re: Monitoring New vs Repeat Customer Visits
                      Tim Buck

                      Thanks for all your help and suggestions. In the end I decided it would be easier to make this a separate report, my data is coming in very frequently - and I'm afraid it would get too complex to keep it all within Tableau (duplicating data sources referencing a specific date, blending, etc).   So I pulled the customer IDs and dates out of the main file, and put them in a new excel doc.  I then sorted for date, flagged the first time it was seen in excel and then used that as the data source for Tableau.  Thanks again, all!