Bdtjtk

Business Context:
To put it simply, I would like to split my data set in to test and control and run some campaigns in the future. But there seems to be lot of complexities involved when I try to split it.

Data:

RetailerCode    CID        Segment

A6005         13SVC15       High

A6005         19VDE1F       Low

A6005         1B3BD1F       Medium

A6005         1B3HB48       Medium

A6005         1B3HB49       Low

A9006         1B3HB40       High

A9006         1B3HB41       High

A9006         1B3HB43       Low

A9006         1B3HB46       Medium

I have a master table like this which has the list of customers and their retailers, segment etc. (it has more than 30 columns, but I don’t want to show everything here). I would like to split this data set by adding a column, lets say “test_control” which will tell us whether particular row is test or control category. I can split this randomly but I need to follow the following rules,

-   If a customer is tied to two or more retailers, then he should be in control group

-   Each retailer will be provided with list of customers to target for the campaigns and the retailer will run the campaign. Here, 

o   Test-Control split should be done at Retailer level and then at segment level. For example,  For each retailer

   10% of their High customers to control and remaining 90% of their high customers to test.

   10% of their Medium customers to control and remaining 90% of their Medium customers to test

   10% of their Low customers to control and remaining 90% of their Low customers to test.

I can share the query that I wrote but that will totally confuse you guys 😊

But this is the high level approach that I followed

-   Took out the records from the main table where customer occurred more than once and added a “test_control” column with “control” in it and saved it in a separate temp table 1

-   Took out the records from the main table where customer occurred only once and saved it in a temp table 2

-   On table 2, I tried to split to 10% (control) and 90% (test)

-   Used union all to merge table 1 and table 2. But the ouput is totally wrong   

Expected output

As you can see it split 10% and 90% at segment level as well

Please someone help me with this. Been working on this for past couple of days but no luck

Thanks in advance

The query that I wrote

--Tagging each row whether it is repated more than twice or not

select * into #Repeat from (

SELECT CID, Count(*) as number,

case when Count(*)>1 then '1'

 else '0'

end as repeat

FROM #Target GROUP BY CID

) temp





--Joining the above table to the master table and creating a new table

SELECT * into #T from(

select a.*,b.repeat from #Target a

left join #Repeat b ON a.CID = b.CID

) temp

alter table #T

add t_c Varchar(400)

UPDATE #T 

SET     t_c =  CASE  

                        WHEN repeat = '1' THEN 'control' 

                        ELSE repeat

                    END

--creating a sub table which has no repetitive customer

SELECT * into #T1

from #T where t_c <> 'control'



--creating a sub table which has repetitive customer

select * from #T

SELECT * into #T2

from #T where t_c = 'control'







--splitting the table(#T1) into test and control 

select * into #T3 from

(select *, 

       (case 

             when row_number() over (partition by RetailerCode,SEGMENT order by newid()) <= ((1-0.9-((select count(*) from #Target)/(select count(*) from #T2))) * count(*) over (partition by RetailerCode,SEGMENT)) then 'control'

             else 'test'

        end) as t_c_new

from #T1

where RETAILERCODE IN (SELECT DISTINCT RETAILERCODE FROM #Target

WHERE CID IN(

SELECT CID FROM

(SELECT  CID, COUNT(*) AS NoOfOccurrences

FROM #Target GROUP BY CID

HAVING COUNT(*) = 1 ) temp

))

)temp1





--renaming a column 

EXEC tempdb.sys.sp_rename N'#T2.t_c', N't_c_new', N'COLUMN';



ALTER TABLE #T3 DROP COLUMN t_c



--Merging the output 

select * into #T4 from

(SELECT * from #T3 --1085612

union all

select * from #T2 -- 89622

) temp



--QA check…this is where I found out my logic is wrong



select RetailerCode, t_c_new, Segment

 from

#T4 group by RetailerCode, t_c_new, Segment

order by RetailerCode, t_c_new, Segment

If you want to assign customers to a specific group you should order them first:

SELECT RetailerCode, CID, Segment,

   CASE WHEN Percent_Rank()

              Over (PARTITION BY retailercode, segment -- for each retailer/segment

                    ORDER BY ControlGroup, newid()     -- all customers with multiple retailers are sorted low, i.e. will be in control group (if it's less than 10%)

                   ) <= 0.1 

             THEN 'control'

             ELSE 'test'

   END AS GROUP

FROM

 (

   SELECT t.*,

      -- flag customers to be put in control group

      CASE WHEN Min(RetailerCode) Over (PARTITION BY CID)

              = Max(RetailerCode) Over (PARTITION BY CID)

           THEN 1 -- only a single retailer

           ELSE 0 -- multiple retailers 

      END AS ControlGroup

-- if the RetailerCode/CID combination is unique:

--      CASE WHEN Count(*) Over (PARTITION BY CID) = 1

--           THEN 1 -- only a single retailer

--           ELSE 0 -- multiple retailers 

--      END AS ControlGroup

   FROM tab t

 ) AS dt;

I am assuming that the customers who are with multiple retailers are not counted toward the 10% in the control group. You can find them using window functions.

The following then divides the remaining customers into the appropriately sized groups per retailer and segment:

select RetailerCode, CID, Segment,

       (case when auto_in_control = 1 then 'control'

             when row_number() over (partition by retailercode, segment, auto_in_control order by newid()) <= 

                  0.1 * count(*) over (partition by retailercode, segment, auto_in_control) 

             then 'control'

             else 'test'

        end) as group

from (select t.*,

             min(RetailerCode) over (partition by cid) as min_rc,

             max(RetailerCode) over (partition by cid) as max_rc,

             (case when min_rc = max_rc then 0 else 1 end) as auto_in_control

      from Table t

     ) t

Order by RetailerCode;

If you want 10% of the customers in control for each retailer, with priority to those who are in multiple retailers, then:

select RetailerCode, CID, Segment,

       (case when row_number() over (partition by retailercode, segmentorder by auto_in_control desc, newid()) <= 

                  0.1 * count(*) over (partition by retailercode, segment) 

             then 'control'

             else 'test'

        end) as group

from (select t.*,

             min(RetailerCode) over (partition by cid) as min_rc,

             max(RetailerCode) over (partition by cid) as max_rc,

             (case when min_rc = max_rc then 0 else 1 end) as auto_in_control

      from Table t

     ) t

Order by RetailerCode;

If more than 10% of the customers for a given retailer in a given segment have multiple retailers, then only enough will be chosen for 10%. The remaining such customers will be in the test group.

By the way, this is poor test design. It would be better to assign customers to test/control independently of their retailer. More importantly, the control group and test groups are not representative, so I would not want to be trying to interpret results from such a test. Well, you (once upon a time) could have paid me enough ;)