3

I'm calculating customers retention and want to segment my customers according to behavior in first, second, third and so-on purchases.

For example:

using first_value(had_coupon) over (partition by customer_id order by order_date DESC)

I can segment by using a coupon in the first purchase effect retention.

I'm trying to figure out to do the same thing for the second and third purchase. Using the CASE statement I can give another value to customers who did not buy two, three or more times.

I've been using this site for help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
oshrim
  • 31
  • 4
  • For any data questions you should include sample input data and the expected result. – Error_2646 Jun 20 '18 at 19:54
  • Could you explain further what you want to achieve? A small dataset, expected result plus what you tried so far would really help – larsen Jun 20 '18 at 19:54
  • hey. I want to segment my customers based on coupon usage behavior in their purchase-life. – oshrim Jun 22 '18 at 10:09
  • SORRY, im new here: hey. I want to segment my customers based on coupon usage behavior in their purchase-life. So far I used first_value for the first purchase, lead, and lag functions for trailing. However, my desired segmentation outcome is in the example column G and H: https://docs.google.com/spreadsheets/d/1Sf1lCkVOnFKQiKfHHDvvof2VDR8DECcK3Q-_MpsjScM/edit?usp=sharing – oshrim Jun 22 '18 at 10:18

1 Answers1

0

Use row_number() for marking first, second, third, etc + group by aggregation using max() or min() to group rows by customer/etc:

select max(case when rn=1 then had_coupon end) first_order_had_coupon,
       max(case when rn=2 then had_coupon end) second_order_had_coupon, 
       -- and so on
       --other group columns
from  
(
select had_coupon,
       --other columns
      row_number() over (partition by customer_id order by order_date DESC) rn
  from table
)s
group by group columns
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Hey, your case statement gives True to all customers who bought in the first, second and so on time. In addition, it should be a window function because I want to segment a user by this their coupon behavior: a second purchase coupon user has to have in their fourth purchase row a True for the column: "coupon in the second purchase". – oshrim Jun 26 '18 at 04:28
  • @oshrim It's difficult to understand, please provide data example with desired output – leftjoin Jun 26 '18 at 06:13
  • does this sample dataset help to understand my desired outcome? https://docs.google.com/spreadsheets/d/1Sf1lCkVOnFKQiKfHHDvvof2VDR8DECcK3Q-_MpsjScM/edit?usp=sharing – oshrim Jun 26 '18 at 18:28
  • @oshrim Please add it to the question, maybe someone will help. I still do not understand what is wrong with second purchase. For customer 1st is 01/11/2018, second is 18/02/2018 (second date< first). Fir customer2 second date> first. The order is different? – leftjoin Jun 26 '18 at 18:42