Analyzing Purchasing Behavior of Customers Who Purchased a Particular Product

[Anders_S]

I'm a newbie to PowerPivot, and I'm stumped on how to do something. I'm trying to analyze the purchasing behavior of customers who purchased a particular product. I'm pulling the data from our Microsoft data warehouse (SQL Server 2008 R2), and in PowerPivot (latest version, 32-bit) the data looks something like this:

customer_ID	product_code	product_name	type	date	revenue
0006792	web382	Understanding aphasia	webinar	1/5/2012	45.00
0006792	web100	Pediatric care	webinar	2/12/2013	50.00
0106297	web074	Hearing aid reviews	webinar	8/6/2011	60.00

<tbody>
</tbody>

1) I'd like to add a column to each row that would let me know whether the customer had purchased web382. How do I do that?

2) Right now I'm pulling down all 3.8 million rows of our sales data from our Sales cube. I'd like to filter the data when I'm importing it so I only get the rows I need: all products purchased by a customer who has purchased web382. I can do simple filters – all rows that contain the product_code web382 – but I can't figure out how to do this. Is it possible without learning MDX?

Also, I feel like I'm not grokking PowerPivot. I've got a pretty good background as a programmer and I'm fluent with regular pivot tables, but I keep getting stumped. Is there a concept I'm missing that would help me understand how to do #1?

Thanks!
Anders Schneiderman
American Speech Language Hearing Association

 

[Jacob Barnett]

Anders,

You can add a calculated column that establishes whether that customer has ever bought web382 using:

=if(COUNTROWS(FILTER(Table1,[product_code]="web382"&&[customer_ID]=EARLIER([customer_ID])))>0,"Bought Web 382",blank())

My immediate thought was that you are probably pushing the boundaries of 32bit with a data set of that size and filtering out the unnecessary customers is a great way to go. Not sure how you would do this using MDX but it can definitely be done in SQL.

select

s.customer_ID,
s.product_code,
s.product_name,
s.type,
s.date,
s.revenue

from salestable s

left join   
 (select customer_ID, sum(case when productcode = 'web382' then 1 else 0 end) as cstcnt      
  from salestable  group by customer_ID)v on v.customer_ID = s.customer_ID

where v.cstcnt > 0

There is no missing concept as such but the DAX isn't intuitive and the learning curve steep! Once you get into it you'll see that there are a handful of basic patterns that crop up again and again. I recommend reading Rob Collie's book on DAX and Alberto/Ferrari on PowerPivot 2013 (even if you are on 2010).

 

[Anders_S]

Thanks so much, Jacob!