Proper Way to Count # of Customers

  • rob26r

    SSC Enthusiast

    Points: 122

    I've seen many market basket analysis posts but none that address the issues I have.

    Here are the requirements:

    1) Identify customers who purchased Mountain Bikes or Road Bikes (ProductSubcategory 1 & 2) between 5/1/2018 and 5/15/2018

    2) For the customers from above, sort and rank by total Product sales descending and also include the # of customers that purchased (between 5/1/2018 and 6/15/2018) each Product (excluding Products in ProductSubcategory Mountain Bikes or Road Bikes)

    This what I have so far. I'm using the AdventureWorksDW 2012 database.

    with

    ------------------------------------------------------------
    --Select ProductSubcategories that were purchased
    ------------------------------------------------------------
    SET [ProductSubcategory_Purchased] as
    {
      [Product].[Product Subcategory Key].&[1],[Product].[Product Subcategory Key].&[2]
    }
    MEMBER [Product].[Product Subcategory Key].[All].[AggRange] as Aggregate([ProductSubcategory_Purchased])

    ------------------------------------------------------------
    --Select purchase period
    ------------------------------------------------------------
    SET [Purchase_Period] as
    {
      [Date].[Date].[Date].&[20080401]:[Date].[Date].[Date].&[20080515]
    }
    MEMBER [Date].[Date].[All].[AggRange] as Aggregate([Purchase_Period])

    ------------------------------------------------------------
    --Select Customers that purchased Mountain Bikes or Road Bikes
    ------------------------------------------------------------
    SET [Customer_Purchase_List] as
    filter
    (
        [Customer].[Customer Key].[Customer Key].members ,
            (
                 [Product].[Product Subcategory Key].[All].[AggRange]
                , [Date].[Date].[All].[AggRange]
                , [Measures].[Sales Amount]
            ) > 0
    )

    MEMBER [Customer].[Customer Key].[All].[AggRange] as Aggregate([Customer_Purchase_List])

    ------------------------------------------------------------
    --Select all ProductSubCategories excluding Mountain Bikes or Road Bikes
    ------------------------------------------------------------
    SET [ProductSubcategory_Other] as
    (
      [Product].[Product Subcategory Key].Children - [ProductSubcategory_Purchased] - [Product].[Product Subcategory Key].[All].UNKNOWNMEMBER
    )
    MEMBER [Product].[Product Subcategory Key].[All].[AggRange1] as Aggregate([ProductSubcategory_Other])

    ------------------------------------------------------------
    --Create set with new analysis purchase period
    ------------------------------------------------------------
    SET [Purchase_Period_Other] as
    {
      [Date].[Date].[Date].&[20080401]:[Date].[Date].[Date].&[20080615]
    }
    MEMBER [Date].[Date].[All].[AggRangeOther] as Aggregate([Purchase_Period_Other] )

    ------------------------------------------------------------
    --Create measure to get sales data
    ------------------------------------------------------------  
    MEMBER [Measures].[Sales Amount Total] as
    sum
    (
      (
       [Customer_Purchase_List] , [Date].[Date].[All].[AggRangeOther]
      ) , [Measures].[Sales Amount]
    )

    ------------------------------------------------------------
    --Create set ordering sales by Product
    ------------------------------------------------------------
    SET [Product_Other_Order] AS
    order
    (
    nonempty 
      (
       [Product].[Product Key].[Product Key] * [ProductSubcategory_Other]
      )
       , [Measures].[Sales Amount Total] , bdesc
    )

    ------------------------------------------------------------
    --Create measure actually ranking set above
    ------------------------------------------------------------
    MEMBER measures.[Product_Other_Rank] AS
    Rank
    (
      (
       [Product].[Product Key].currentmember , [Product].[Product Subcategory Key].currentmember
      )
       , ([Product_Other_Order])
    )
            

    ------------------------------------------------------------
    --Count # of customers
    ------------------------------------------------------------
    MEMBER [Measures].[NumCustomers] as

    (
      (
       [Customer_Purchase_List] , [Date].[Date].[All].[AggRangeOther]
      ) , [Measures].[Sales Amount]
    ).count

    ------------------------------------------------------------
    --Get final results
    ------------------------------------------------------------
    select
    {
      {
       [Measures].[Sales Amount Total], measures.[Product_Other_Rank] , [Measures].[NumCustomers]
      }
    } on 0 ,
       [Product_Other_Order] having [Measures].[Sales Amount Total] > 0
             on 1
    from [AdventureWorksDW]

    Everything is working except I don't know how to count the # of customers that purchased each product.  Any thoughts?  Thanks.

    Example output:

    enter image description here

    Thanks for your help!

  • rob26r

    SSC Enthusiast

    Points: 122

    For anyone else, found the solution to my issue.

    member [Measures].[NumCustomers] as
            count    (
                        filter    (
                                     exists([Customer].[Customer Key].[Customer Key].members , {[Customer_Purchase_List]})
                                    , (
                                         [Date].[Date].[All].[AggRangeOther]
                                        , [Measures].[Sales Amount]
                                     ) > 0
                                )

  • Jeff Moden

    SSC Guru

    Points: 993884

    I'm not a GUI programmer nor do I use SSAS, SSIS, SSRS, DW, etc, etc, so I have to ask what "language" is this and why haven't you simply used T-SQL to solve this database problem?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jason A. Long

    SSC-Insane

    Points: 23416

    Jeff Moden - Friday, November 9, 2018 6:40 AM

    I'm not a GUI programmer nor do I use SSAS, SSIS, SSRS, DW, etc, etc, so I have to ask what "language" is this and why haven't you simply used T-SQL to solve this database problem?

    If I'm not mistaken, that's MDX. It's for talking to SSAS cubes.

  • Jeff Moden

    SSC Guru

    Points: 993884

    Thanks, Jason.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • rob26r

    SSC Enthusiast

    Points: 122

    Correct - MDX.  Oops, perhaps I posted this on the wrong forum.

  • Jeff Moden

    SSC Guru

    Points: 993884

    rob26r - Friday, November 9, 2018 7:29 AM

    Correct - MDX.  Oops, perhaps I posted this on the wrong forum.

    No.  MDX code is appropriate for a BI forum (IMHO).  When I get some time (maybe this weekend), I'm thinking about taking a crack at it using just plain ol' T-SQL to see if it can be simplified.

    p.s. Nice job on commenting and formatting the code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply