BookmarkSubscribeRSS Feed
epp_dog
Calcite | Level 5

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
6 REPLIES 6
ted_werner_sas_com
SAS Employee

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

epp_dog
Calcite | Level 5

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.

ted_werner_sas_com
SAS Employee

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

gabras
Pyrite | Level 9

@epp_dog@ted_werner_sas_com

 

have you found a solution about the problem?

If you remember it! it's quite a long time ago 😉

epp_dog
Calcite | Level 5

Sadly, our VA server got turned off accidentally shortly after this question and our IT team was never able to resurrect it.  I had poor user adoption on SAS VA so I couldn't get executive support to get the machine running again.  So figuring this out was never again a priority.  We are in the midst of installing Viya and hope that I can get better adoption/usage this time.  Sorry I'm no help at this time.

gabras
Pyrite | Level 9

Thank you for your response

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1974 views
  • 5 likes
  • 3 in conversation