2 Replies Latest reply on Aug 26, 2013 1:37 PM by Tim Reilly

    Count events by month

    Tim Reilly

      Hi.  I have a source data table that roughly follows the structure below.

       

      USER ID

      Trial start date - Prod 1

      Trial start date - Prod 2

      Conversion date - Prod 1

      Conversion date - Prod 2

      Cancellation date - Prod 1

      Cancellation date - Prod 2

      1

      1/1/2013

      2/1/2013

      1/7/2013

      1/1/2013

      2/1/2013

      4/1/2013

      2

      1/1/2013

      2/1/2013

      1/7/2013

      3/1/2013

      2/15/2013

      2/15/2013

      3

      1/1/2013

      2/1/2013

      1/7/2013

      3/1/2013

      2/15/2013

      2/15/2013

      4

      1/1/2013

      1/15/2013

      1/7/2013

      3/1/2013

      2/15/2013

      3/1/2013

      5

      1/1/2013

      1/30/2013

      1/7/2013

      3/1/2013

      2/15/2013

      4/1/2013

       

      I would like to work with this at an aggregate monthly level showing trials/conversions/cancellation per month broken out by product.

       

      I can get a COUNTD of user id by trial start date for either product 1 or product 2 in separate sheets, but I can’t figure out how to aggregate them  to show total trials by month by product.

       

      Desired output:

       

      TRIALS BY PRODUCT:

       

      Jan 2013

      Feb 2013

      Mar 2013

      Product 1

      5

      0

      0

      Product 2

      2

      3

      0

       

      I'd then repeat the above for conversion and cancellation.

       

      I feel like I need a common date dimension plus a custom formula comparing to that common date and returning USER ID if that date = trial start date - Prod 1.

       

      Is there another way to do this that I’m missing?

       

      Thanks in advance for your help!

       

      -Tim

        • 1. Re: Count events by month
          Tom Barnes

          Tim,

              I think the easiest thing to do would be to reformat your table which will allow Tableau to better analyze the data.  So instead of the table above make it look like this:

               

          USER IDProductMeasureDate
          1Prod1Trial start date1/1/2013
          1Prod2Trial start date2/1/2013
          1Prod1Conversion date1/7/2013
          1Prod2Conversion date1/1/2013
          1Prod1Cancellation date2/1/2013
          1Prod2Cancellation date4/1/2013
          2Prod1Trial start date1/1/2013
          2Prod2Trial start date2/1/2013
          2Prod1Conversion date1/7/2013
          2Prod2Conversion date3/1/2013
          2Prod1Cancellation date########
          2Prod2Cancellation date########
          3Prod1Trial start date1/1/2013
          3Prod2Trial start date2/1/2013
          3Prod1Conversion date1/7/2013
          3Prod2Conversion date3/1/2013
          3Prod1Cancellation date########
          3Prod2Cancellation date########
          4Prod1Trial start date1/1/2013
          4Prod2Trial start date########
          4Prod1Conversion date1/7/2013
          4Prod2Conversion date3/1/2013
          4Prod1Cancellation date########
          4Prod2Cancellation date3/1/2013
          5Prod1Trial start date1/1/2013
          5Prod2Trial start date########
          5Prod1Conversion date1/7/2013
          5Prod2Conversion date3/1/2013
          5Prod1Cancellation date########
          5Prod2Cancellation date4/1/2013

           

           

          Then when you connect to it in Tableau everything becomes easier and you are able to get your tables as requested.  See the attached workbook.

           

          There is a reshape data tool for excel that is extremely useful.  You can download it here: Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software.

           

          Hope this helps.

           

          Tom

          • 2. Re: Count events by month
            Tim Reilly

            Thank you Tom!  I appreciate your quick reply and solution.  Unfortunately my source table is large (millions of rows) and will quickly blow up Excel.  I'll see if I can write something to reshape/melt that data.  Possibly either a stored procedure or something like ODBC -> R/Python -> .csv file.  I thought that might be the right approach but was hoping to avoid going there.