Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How can I use an aggregated measure as a category?

Reply
Occasional Contributor
Posts: 5

How can I use an aggregated measure as a category?

I am working with an email history table:

  • campaign id
  • campaign send date
  • account ID

What I want to do is present a slider based on campaign send date and then display how many people have received 1 campaign in that time period vs 2, 3, 4, etc.

An aggregated value to "count distinct" account ID gets me started as it does accurately reflect the number of campaigns a person has received based on the slider range but I want to twist this data.  Instead of showing that account 1 had 6 campaigns I want to say that 1 person had 1 campaign, 0 people had 2 campaigns, 1 person had 3 campaigns, etc.


Any ideas on how to proceed? 

Capture.PNG

Sample data:

acct_idcamp_idsend_date
1EM-110/15/2013
1EM-210/22/2013
1EM-310/29/2013
1EM-411/5/2013
1EM-511/12/2013
1EM-611/19/2013
1EM-711/26/2013
1EM-812/3/2013
2EM-110/15/2013
2EM-310/29/2013
2EM-411/5/2013
2EM-511/12/2013
2EM-711/26/2013
2EM-812/3/2013
3EM-110/15/2013
3EM-511/12/2013
4EM-310/29/2013
4EM-411/5/2013
4EM-611/19/2013
4EM-711/26/2013
4EM-812/3/2013
4EM-912/10/2013
5EM-110/15/2013
5EM-210/22/2013
5EM-411/5/2013
5EM-511/12/2013
5EM-711/26/2013
6EM-210/22/2013
6EM-310/29/2013
6EM-411/5/2013
6EM-611/19/2013
6EM-711/26/2013
6EM-812/3/2013
6EM-912/10/2013
7EM-110/15/2013
7EM-310/29/2013
7EM-511/12/2013
7EM-812/3/2013
7EM-912/10/2013
8EM-110/15/2013
8EM-210/22/2013
8EM-310/29/2013
8EM-511/12/2013
8EM-711/26/2013
8EM-812/3/2013
8EM-912/10/2013
SAS Employee
Posts: 19

Re: How can I use an aggregated measure as a category?

Hello EppDog,

I believe that if you drag campaign id onto the visualization you could lattice the columns and it would do what you are asking for in my top picture.  It will lattice the Campaign id on the bottom of the axis.  In picture two,  you could create a colorized group that would group 1-5 by color.  Either way it would give you the breakouts you are asking for.  I believe that would work because campaign-id is a category in your data.  Please let me know if I did not understand your question correctly.  Please also let me know if you accomplished it with a different method.

Regards,

Ted Werner

Occasional Contributor
Posts: 5

Re: How can I use an aggregated measure as a category?

Thanks for the reply Ted.

Unfortunately that doesn't quite work.  For this exercise I care less about WHICH campaigns but rather HOW MANY campaigns.  So as I drag the slider Account #1 goes from being in 8 campaigns to only 2 or 3 depending upon the dates.  Others may go up, depending upon where the sliders are at.

The aggregate count does a perfect job of classifying everyone by the number of unique campaigns they are in during that period.  However, I don't see a way to move that aggregate measure to the category section.  Meaning I don't care about how many campaigns account #1 was in but rather how many accounts were in only 1 campaign during that time period (or 2, 3, 4,... campaigns). 

To put this into something we all experience, think of your favorite retailer who sends you emails.  I want to say "for the month of March, retailer X sent 20 emails to 100 people, 19 emails to 85 people, etc."

Your bottom graph did give me an idea that I could still put the actual accounts as the category variable and let the natural sort group them together.   That does allow for visually seeing how many campaigns people were in (especially if I could use colors but those are only available for categorical variables) but I don't know how that will work when I have 6 million accounts - I don't think VA will want to throw that many categories into a bar chart.

SAS Employee
Posts: 19

Re: How can I use an aggregated measure as a category?

Epp_Dog,

I don't have a good answer for you at this juncture.  I am looking into it though and your use case here is pretty solid from a visualization perspective.  I don't know that visualizing 6 million accounts at a time is a good idea, but there are ways to parametrize the report so long as you have data segmenting it appropriately.  I am still thinking about it though and I will try and find some type of work around this week.  If anyone else comes up with an alternative, please bring forth your idea.

Regards,

Ted Werner

Post a Question
Discussion Stats
  • 3 replies
  • 410 views
  • 5 likes
  • 2 in conversation