How many customers have placed an order in the last month but also none in the previous 3 months?

Question

I want the count of "reactivated" customers for every country, which placed an order in the previous month (for example Sep 2014) but also placed no orders in the previous 6 months (Mar->Aug 2014).

An example with AdventureWorks would be highly appreciated. I have Customer Count over Calendar Years on Columns. How do I get the count of "reactivated" customers (Country on rows) with sales in 2008 but no sales in 2006->2007?!

Adventure Works Example http://de.tinypic.com/r/125q7hi/8

Why do you have to do this with MDX? Seems like SQL would be a better approach. — Tab Alleman
@TabAlleman if the environment is OLAP based then MDX is the correct choice — whytheq
@whytheq Ok, but you can't build an OLAP environment without a relational DB underneath it. I'm saying OP is probably better off querying the underlying relational tables than trying to get the desired results with MDX and OLAP. — Tab Alleman
@TabAlleman ok - why bother having a developer build the OLAP cubes if the administrator is just going to query the underlying database? — whytheq

whytheq whytheq · Accepted Answer · 2014-10-17T22:52:21

Except works quite quickly for this scenario. Find all customers who had sales in 2008 then exclude any of those customers who had sales in 2006/2007. This leaves the customers who have sales in 2008 but not 2006/2007.

I played with using the Filter function but it was very slow.

There will be a solution using IIF with null values as one of the return values that will be quick but I can't figure it out.

WITH 
  MEMBER MEASURES.[Returning Customers] AS 
    Count
    (
      Except
      (
        NonEmpty
        (
          [Customer].[Customer].[Customer].MEMBERS
         ,
            {[Measures].[Internet Sales Amount]}*
            {[Date].[Calendar Year].&[2008]}*
            [Customer].[Customer Geography].CurrentMember
        )
       ,NonEmpty
        (
          [Customer].[Customer].[Customer].MEMBERS
         ,
            {[Measures].[Internet Sales Amount]}*
            {
              [Date].[Calendar Year].&[2006]
             ,[Date].[Calendar Year].&[2007]
            }*
            [Customer].[Customer Geography].CurrentMember
        )
      )
    ) 
SELECT 
    [Date].[Calendar].[Calendar Year].MEMBERS
  * 
    MEASURES.[Returning Customers] ON 0
 ,{[Customer].[Customer Geography].[Country].MEMBERS} ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Year].&[2008];

Your scenario was very specific - so there is no point displaying any year other than 2008.

Here is a great reference as a place to start investigating to returning customer scripts: http://cwebbbi.wordpress.com/2010/10/08/counting-new-and-returning-customers-in-mdx/

I think, in terms of performance, named sets should be used sparingly but when using set based functions they can actually increase performance so maybe, because we are using Except named sets might speed things up:

WITH 
  SET [2008customers] AS 
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS
     ,
      {[Measures].[Internet Sales Amount]} * {[Date].[Calendar Year].&[2008]}
    ) 
  SET [20062007customers] AS 
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS
     ,
        {[Measures].[Internet Sales Amount]}
      * 
        {
          [Date].[Calendar Year].&[2006]
         ,[Date].[Calendar Year].&[2007]
        }
    ) 
  MEMBER MEASURES.[Returning Customers] AS 
    Count
    (
      Except
      (
        [2008customers] * [Customer].[Customer Geography].CurrentMember
       ,
        [20062007customers] * [Customer].[Customer Geography].CurrentMember
      )
    ) 
SELECT 
    [Date].[Calendar].[Calendar Year].MEMBERS
  * 
    {
      MEASURES.[Returning Customers]
     ,[Measures].[Customer Count]
    } ON 0
 ,{[Customer].[Customer Geography].[Country].MEMBERS} ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Year].&[2008];

This is a more general version of your original problem. Rather than naming the years it looks at customers from the current year in the table who are not found in the previous 2 years:

WITH 
  MEMBER MEASURES.[Returning Customers] AS 
    Count
    (
      Except
      (
        NonEmpty
        (
          [Customer].[Customer].[Customer].MEMBERS
         ,
            {[Measures].[Internet Sales Amount]}*
            {[Date].[Calendar Year].CurrentMember}*
            [Customer].[Customer Geography].CurrentMember
        )
       ,NonEmpty
        (
          [Customer].[Customer].[Customer].MEMBERS
         ,
            {[Measures].[Internet Sales Amount]}*
            (
              [Date].[Calendar Year].CurrentMember.Lag(1)
            : 
              [Date].[Calendar Year].CurrentMember.Lag(2)
            )*
            [Customer].[Customer Geography].CurrentMember
        )
      )
    ) 
SELECT 
    [Date].[Calendar].[Calendar Year].MEMBERS
  * 
    {
      [Measures].[Customer Count]
     ,MEASURES.[Returning Customers]
    } ON 0
 ,{[Customer].[Customer Geography].[Country].MEMBERS} ON 1
FROM [Adventure Works]
WHERE 
  NULL : [Date].[Calendar Year].&[2008];

Trying to figure out what happened in 2006?!

How many customers have placed an order in the last month but also none in the previous 3 months?

1 Answers