Proper Way to Count # of Customers
-
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:
Thanks for your help!
-
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
) -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, November 9, 2018 6:40 AM
If I'm not mistaken, that's MDX. It's for talking to SSAS cubes.
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Correct - MDX. Oops, perhaps I posted this on the wrong forum.
- rob26r - Friday, November 9, 2018 7:29 AM
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply