Counting Active Customers from Sparse Data

Version 3

    Please check out Joshua Milligan and Jim Wahl alternative solutions in the comments below!

     

    Business Case:

     

    Your boss recently approached you with the question, "How many active customers do we have each month?". Straight forward question, and, normally, pretty easy to answer with Tableau. The problem is though, your DB only records data on the customer's first and last month. So you are working with a Table similar to this..

     

     

    And when you go to create your viz in Tableau, you are stuck looking at this...

     

     

    This isn't correct, and really what we want is a visualization like this.

     

     

    Solution:

     

    Step 1:

     

    The first thing we need to do is re-arrange the data, and pad out missing dates. This will make it easier for us to engineer our solution. So move [Dates] to the Row Shelf, [Name] to the Column Shelf, and [Number of Records] to the Text Mark. Finally, Check "Show Missing Values" on the [Dates] pill

     

     

     

    Step 2: Identifying the Indexes

     

    Once we have the viz arranged, drag INDEX() onto the Text Mark, and Set to Compute using "Table Down"

     

     

    Now we can see that Tableau is generating a mark (INDEX()) along Date, by each Customer.

     

    So the next thing we need to do is identify which INDEX() corresponds to the Start and End Date of the Customers.

     

    Step 3: MIN and MAX INDEX Per Customer

     

    We need to create a Calc that will only generate the Index, if the Customer had a record on that particular month.

     

    Min and Max Indexes

    IF NOT ISNULL(SUM([Number of Records]))

    THEN INDEX()

    END

     

    Place that on the Text Mark, and Compute Using Table Down.

     

     

    Ok, so now we know all of the possible Indexes per customer (1 -12 corresponding to each month) and we know the indexes that correspond to the Customers First and Last Month. So the next thing we need to do is generate an Index for all Months between the indexes that correspond to the First and the Last Month Index.

     

    Step 4: Finding Active Indexes

     

    To do this, we have to implement another Table Calc.

     

    Active Indexes

    IF INDEX() >= WINDOW_MIN([Min and Max Indexes])

    AND INDEX() <= WINDOW_MAX([Min and Max Indexes])

    THEN INDEX()

    END

     

    What we are doing here is comparing the possible indexes (1 - 12) to the Index for the First and Last Month of the Customers. If the INDEX falls between them, generate the Index, else give a NULL.

     

    Place that on the Text Mark set both Calcs (Min and Max Indexes, Active Indexes) to Compute using Table Down.

     

     

    So now that we have an Index for every month a Customer was Active, we need to count how many active customers there were each month.

     

    Step 5: Counting Active Customer per Month

     

    Next thing we need to do is create another Table Calc that counts the indexes in each month.

     

    Active Customers

    WINDOW_COUNT([Active Indexes])

     

    Drag this onto the Text mark and set it to Compute using Table Across (While having the (Min and Max Indexes, Active Indexes) to Compute using Table Down).

     

     

    Now we can see in a given month, how many active customers there were. So the last thing we need to do is change the order of things, so we can visualize it.

     

    Step 6: Re-organizing the viz, and calcs

     

    Now we need to get the pills into the correct order. So drag [Dates] on the Column Shelf, and [Name] onto the Details Mark.

     

    Change (Min and Max Indexes, Active Indexes) to Compute using Table Across (Since we want these to compute across each date, per customer). This is where it gets a little trickier. For[Active Customers], we need to compute using Advanced, Where we address [Dates], and [Name] and Restart the Calc every [Date]

     

     

    This ensures that we only get a count of Active Customers each month.

     

    Finally, let's make the Viz.

     

    Step 7: Final.

     

    Finally, drag [Active Customers] to the Rows Shelf, change the Mark Type to Bar, and set Stack Marks to Off.

     

    Then do some formatting, and your'e done!

     

     

     

    I hope this helps!

     

    Any suggestions on how to improve this article is highly appreciated!

     

    Regards,

    Rody