Count properties by customers over time (campaign waves)

[gino2780]

Hi, i need advice on how to count customers with more than one property (1:M) for three campaign waves.

To put it simple, i have three fields:

CustomerID

PropertyID

Calendarweek 

The overall raw data table contains customers that have one or more properties. However the PropertyID defines the amount of overall rows in the raw data table. Now i want to count how many times the CustomerID occurs through all three campaigns.and additionally how many times per wave.

Basic example:

wave1:

PropertyID	CustomerID	Campaign
1	1	Wave1
2	2	Wave1
3	1	Wave1
4	3	Wave2
5	2	Wave2
6	1	Wave2
7	4	Wave3
8	3	Wave3
9	2	Wave3
...	...	...

Any ideas? Thanks in advance.

[henrikalmen]

Perhaps I'm misunderstanding something, but assuming that a "wave" is the campaigns you want to count, you should store them in one table. The code below is not necessarily right for you, it's better that you use your original master table but I only have your example to go by so:

waves:
noconcatenate load *, 1 as WaveNo resident wave1;
concatenate (waves) load *, 2 as WaveNo resident wave2;
concatenate (waves) load *, 3 as WaveNo resident wave3;

drop tables wav1, wave2, wave3;

Now you have one table with added information about each wave. You want to count how many of the campaigns each customerId exist in (if I understand correctly). You could create a new table with that information:

count: load CustomerID, count(distinct WaveNo) as NoOfWaves resident waves group by CustomerID;

If you want to know only which CustomerID exists in all three campaigns, you could filter out the customers with NoOfWaves=3.

I hope this at least brings you closer to achieveing what it is you want to do.

[gino2780]

Thanks for your answer.

Maybe i have explained it too complicated.In fact i have one overall table (i will edit the example table after this posting).  

All i want is to be able to count how many distinct customers occur how many times throughout the campaign waves.

[henrikalmen]

count(distinct CustomerID) gives you the number of unique customer id:s, but I suppose you already know that. Therefore I believe I haven't fully understood your question.

In your updated example, this would give you a counter per customer that tells you how many unique CampaignID each unique customer has in the table:

load CustomerID, count(distinct Campaign) as campaigns resident [theOverallFactsTable] group by CustomerID