Counting New and Returning Customers in MDX

Finding the number of distinct customers you had in any given time period, and then finding how many of those have bought from us before and how many are new customers, is a pretty common requirement. It’s a problem I blogged about a few months ago and showed how to solve in it DAX; I was thinking about it again recently and have just updated that post with a more elegant solution. However I thought it would also be useful to show how to do these calculations in SSAS and MDX.

Here’s a query on the Adventure Works cube that shows how to calculate these values:

WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
    NONEMPTY(
        NONEMPTY(
            [Customer].[Customer].[Customer].MEMBERS
        , [Measures].[Internet Sales Amount])
    , {[Measures].[Internet Sales Amount]}
    * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
    )
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

There’s already a measure in the cube, [Measures].[Customer Count], that gives the distinct count of customers, so that bit’s easy. Finding the number of returning customers, ie customers who’ve bought something in this time period and who have also bought something from us before, is the tricky bit. It’s a four stage process:

• First, get the set of all customers:
  [Customer].[Customer].[Customer].MEMBERS
• Then, filter that set to get the set of all customers who bought something in the current time period. Using the NonEmpty function (as opposed to the Filter function) is the most efficient way of doing this – customers who bought something in the current time period are those who have a value for the measure Internet Sales Amount:        NONEMPTY(
              [Customer].[Customer].[Customer].MEMBERS
          , [Measures].[Internet Sales Amount])
• Then, take this set and find the customers in that set that bought something in all time periods previous to the current time period. Again, we can use the NonEmpty function to do this, but this time in the second parameter we want to find the customers who have a value for Internet Sales Amount for the set of time periods {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}; for more information on how NULL and the colon operator is used here, see this post. This gives us the set expression:    NONEMPTY(
          NONEMPTY(
              [Customer].[Customer].[Customer].MEMBERS
          , [Measures].[Internet Sales Amount])
      , {[Measures].[Internet Sales Amount]}
      * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
      )
• Finally, we need to count the number of items in this set using the Count function.

Of course, with the count of distinct customers and the count of returning customers, we can subtract the latter from the former and then get the number of new customers, ie those who’ve never bought anything from us before.

Here are the results from the Adventure Works cube: