topic Re: How Can I Build Complex Segments in SAS MA in SAS Customer Intelligence

How Can I Build Complex Segments in SAS MA

JMolnar — Tue, 28 Jun 2016 20:23:36 GMT

We are on 6.3 Hotfix 0.1

What we are challenged with is performing segmentation in the tool and having it be output as an optional field as the tool doesn’t seem to allow us to use IF or CASE statement-like functionality

What I mean by this is

Let’s say the output of my list is

BAN CTN City Device ARPU Segment1 Segment2

1 2 Mtl Iphone 25 Cell4 Key1

1 6 Tor BB 200 Cell4 Key2

3 7 Cgy Samsg 30 Cell2 Null

4 8 Van Iphone 40 Cell2 Key1

5 9 Hfx Iphone 60 Cell3 Key3

Rules are as follows

If sum(ARPU) per BAN <30 then segment1 = Cell1

If sum(ARPU) per BAN <50 then segment1 = Cell2

If sum(ARPU) per BAN <100 then segment1 = Cell3

If sum(ARPU) per BAN <300 then segment1 = Cell4

If city in (Mtl,Tor,Van) and device = ‘Iphone’ Then segment2 = ‘Key1’

If device = ‘BB’ Then segment2 = ‘Key2’

If city = “Hfx’ then segment2 = ‘Key3’

Here is the real code we are trying to implement

/* keycode */

rsubmit;

proc sql;

update svr.&Campaign_Name._ilp_07

set OPT1 = 'BAN:$' || strip(put(all_ban_mm_arpu, 10.2)) || ';' || 'CTN:$' || strip(put(all_mm_arpu_pre,10.2))

, OPT2 = case when NO_OF_LIVE_CTNS = 1 then 'SL' else 'ML - ' || strip(put(NO_OF_LIVE_CTNS,4.)) end

, OPT3 = strip(put(all_bl_data_pct,8.)) || '% Usage'

, OPT4 = strip(OFFR_SEGMENT)

, OPT5 = strip(KEY_ATTRIBUTE)

, OPT6 = case

when offr_Lead_Smartphone_price is null then ''

when language = 'EN' then '$' || strip(put(offr_Lead_Smartphone_price, 8.))

else strip(put(offr_Lead_Smartphone_price, 8.)) || '$' end

, OPT7 = case

when offr_Lead_Smartphone_price is null then ''

else strip(offr_Lead_Smartphone_GB_disp) end

, OPT8 = case

when NO_OF_LIVE_CTNS > 1 then ''

when Share_Type <> 'SHARE EVERYTHING' then ''

when offr_Lead_Smartphone_price - msf <= 0 then ''

when language = 'EN' and offr_Lead_Smartphone_price - msf > 0 then '$' || strip(put(abs(offr_Lead_Smartphone_price - msf),best8.2)) || ' more'

when language = 'FR' and offr_Lead_Smartphone_price - msf > 1 then strip(put(abs(offr_Lead_Smartphone_price - msf),best8.2)) || '$ de plus par mois'

end

, OPT9 = ''

, OPT10 = case when all_ban_data_usage = 0 then 'VOICE ONLY' else 'VOICE & DATA' end

, WES = Share_Type

, KEYCODE = 'TARGET'

, MKT_ACCOUNT_TYPE = case when has_data_ind = 1 then 'D' else 'V' end

;

quit;

endrsubmit;

rsubmit;

proc sql;

update svr.&Campaign_Name._ilp_08

set keycode =

case

when all_ban_data_usage = 0 then 'VOICE'

when Share_Type <> 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and all_bl_data_pct >= 90 then 'LEGDATA'

when Share_Type <> 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and all_bl_data_pct >= 70 then 'LEGREG'

when Share_Type = 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and SE_Plus_Flag_Lead = 1 and all_bl_data_pct >= 90 then 'SEDATA'

when Share_Type = 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and SE_Plus_Flag_Lead = 1 and all_bl_data_pct >= 70 then 'SEREG'

when Share_Type <> 'SHARE EVERYTHING' then 'LEGALL'

when Share_Type = 'SHARE EVERYTHING' then 'SEALL'

else 'N/A'

end

;

quit;

endrsubmit;

Re: How Can I Build Complex Segments in SAS MA

SteveMarshall — Wed, 29 Jun 2016 13:17:21 GMT

CI studio supports CASE Statement in calculated fields. The Calculated field can be defined in the Information Map or within the Definitions workspace in CI Studio as a Calculated Item. The variables used in the CASE statement must be from the MA Information map or can be other Calculated items.

Re: How Can I Build Complex Segments in SAS MA

SteveMarshall — Wed, 29 Jun 2016 13:24:48 GMT

Two Valid Case statements for Calculated Items in my CI environment:
Case when <</Customer/Status>> = 1 then 'Key1' when <</Customer/Status>> =2 then 'Key2' when <</Customer/Status>> = 3 then 'Key3' else 'N/A' end

or
Case <</Customer/Status>> when 1 then 'Key1' when 2 then 'Key2' when 3 then 'Key3' else 'N/A' end

Re: How Can I Build Complex Segments in SAS MA

SteveMarshall — Wed, 29 Jun 2016 13:31:05 GMT

Once the Calculated Item is created, it can be used in Exports or Selections / Splits (by data Items).

Re: How Can I Build Complex Segments in SAS MA

RajaMarla — Thu, 30 Jun 2016 05:42:13 GMT

One of the other resources available for you (besides the calculated field option), is to write your SAS code to perform any computation or derive a field of your choice. If you can do it using Base SAS, you pretty much can do the same using a "process node" on the MA diagram.

Inside a process node, you can write your own data step code or PROC-SQL to access databases/SAS datasets etc. You can also store intermediate results in a SAS dataset or a database and then retrieve/combine the results with other calculations in a second process node on the same or other diagrams.

Please explore the "process node" documentation (SAS MA user guide) for additional help or ask your questions on this forum.

Re: How Can I Build Complex Segments in SAS MA

pcapazzi — Thu, 30 Jun 2016 19:23:30 GMT

I think the snippet of code regarding "keycode" can be implemented in CI using nodes. This is how I would try it:

1) Your first split would be based on all_ban_data_usage (0 in one cell, all others in another cell). The cell with 0 would go into the comm node.

2) Then split the 'all others' by OFFER_LEAD_SOC where missing values go in one cell and everything else in the other cell. The "everything else" goes into another cell by selecting all fields but renaming the output cell to be the same. So there should be an output name for "missing values" and one for "everything else".

3) The 'missing values' node would go into a split by Share_Type. Just like OFFER_LEAD_SOC that split node would need all values of the field to be selected. "SHARE EVERYTHING" would be it's own cell and retain it's name. All others would need their output cell names change to be something common like "NOT SHARE EVERYTHING" so you get only two nodes coming out of it.

4)You would then take "non missing values" and split that by SE_Plus_Flag_Lead

5) Then you would split by all_bi_data_pct so that min 90 and max * goes to one cell and then min 70 and max 90 goes to another cell.

There are other ways of segmenting data such as prioritze. For instance, instead of selecting all values of a field you can have a select statement of missing values in OFFER_LEAD_SOC point into a prioritize that also has your other data as another input. The Offer_Lead_Soc with missing values will come out on the other end with the same count but here you can use the Create A Cell For Remainder function to produce "all other values".

If you have duplicate subjects (BANs?) in that table being updated make sure that you pay close attention to the priority order in these nodes where the preferred segment is on top.

I think the other code that updates the various OPT# and other fields (1st half of the code provided) would need to be setup in a process node and/or calculated items. If the OPT and other fields are in the infomap then, as suggested before, a process node could update the table they reside in and be used by splits and prioritizes afterwards.

Hope that helps.

Re: How Can I Build Complex Segments in SAS MA

GoExplore — Wed, 27 Jul 2016 20:29:27 GMT

Solution provided by @SteveMarshall is simple and works perfectly well.