R User Segmentation
I'm looking to group users into segments according to their transaction behavior using R.
segment_name definition
1 1-2 days since last order, with minimum of 8 unique transaction_id since X date
2 1-2 days since last order, with less than 8 unique transaction_id since X date OR 3-6 days since last order
3 7+ days since last order
The reason why I put in X date in the above is that I would like to be able to assign a value to that date. Say if I wanted to see how different the segmentation would be if I looked at all their orders within the last 30 days vs last 60 days.
Anyway, I have a table like below:
date user_id transaction_id
Jan 1, 2018 123 ABC123
Jan 1, 2018 123 DEF456
Jan 2, 2018 123 GHI789
Jan 6, 2018 123 JKL012
Jan 19, 2018 123 MNO345
Jan 20, 2018 123 PQR678
Jan 21, 2018 123 STU901
Jan 26, 2018 123 VWX234
Jan 27, 2018 123 YZA567
Based on the rules, I would like to be able to see the user's movement across segments week on week.
date (weekly) user_id segment_name
Jan 7, 2018 123 2
Jan 14, 2018 123 3
Jan 21, 2018 123 2
Jan 28, 2018 123 1
Hope someone can help me figure out how to do this.
EDIT: I made it up to this. I don't know how to turn this to only show the weekly summary and for the rec to show the days since the last transaction date from the weekly date.
date user_id orders cumu_orders rec date (weekly)
Jan 1, 2018 123 2 2 NA Jan 7, 2018
Jan 2, 2018 123 1 3 1 Jan 7, 2018
Jan 6, 2018 123 1 4 4 Jan 7, 2018
Jan 19, 2018 123 1 5 13 Jan 21, 2018
Jan 20, 2018 123 1 6 1 Jan 21, 2018
Jan 21, 2018 123 1 7 1 Jan 21, 2018
Jan 26, 2018 123 1 8 5 Jan 28, 2018
Jan 27, 2018 123 1 9 1 Jan 28, 2018
r
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
add a comment |
I'm looking to group users into segments according to their transaction behavior using R.
segment_name definition
1 1-2 days since last order, with minimum of 8 unique transaction_id since X date
2 1-2 days since last order, with less than 8 unique transaction_id since X date OR 3-6 days since last order
3 7+ days since last order
The reason why I put in X date in the above is that I would like to be able to assign a value to that date. Say if I wanted to see how different the segmentation would be if I looked at all their orders within the last 30 days vs last 60 days.
Anyway, I have a table like below:
date user_id transaction_id
Jan 1, 2018 123 ABC123
Jan 1, 2018 123 DEF456
Jan 2, 2018 123 GHI789
Jan 6, 2018 123 JKL012
Jan 19, 2018 123 MNO345
Jan 20, 2018 123 PQR678
Jan 21, 2018 123 STU901
Jan 26, 2018 123 VWX234
Jan 27, 2018 123 YZA567
Based on the rules, I would like to be able to see the user's movement across segments week on week.
date (weekly) user_id segment_name
Jan 7, 2018 123 2
Jan 14, 2018 123 3
Jan 21, 2018 123 2
Jan 28, 2018 123 1
Hope someone can help me figure out how to do this.
EDIT: I made it up to this. I don't know how to turn this to only show the weekly summary and for the rec to show the days since the last transaction date from the weekly date.
date user_id orders cumu_orders rec date (weekly)
Jan 1, 2018 123 2 2 NA Jan 7, 2018
Jan 2, 2018 123 1 3 1 Jan 7, 2018
Jan 6, 2018 123 1 4 4 Jan 7, 2018
Jan 19, 2018 123 1 5 13 Jan 21, 2018
Jan 20, 2018 123 1 6 1 Jan 21, 2018
Jan 21, 2018 123 1 7 1 Jan 21, 2018
Jan 26, 2018 123 1 8 5 Jan 28, 2018
Jan 27, 2018 123 1 9 1 Jan 28, 2018
r
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27
add a comment |
I'm looking to group users into segments according to their transaction behavior using R.
segment_name definition
1 1-2 days since last order, with minimum of 8 unique transaction_id since X date
2 1-2 days since last order, with less than 8 unique transaction_id since X date OR 3-6 days since last order
3 7+ days since last order
The reason why I put in X date in the above is that I would like to be able to assign a value to that date. Say if I wanted to see how different the segmentation would be if I looked at all their orders within the last 30 days vs last 60 days.
Anyway, I have a table like below:
date user_id transaction_id
Jan 1, 2018 123 ABC123
Jan 1, 2018 123 DEF456
Jan 2, 2018 123 GHI789
Jan 6, 2018 123 JKL012
Jan 19, 2018 123 MNO345
Jan 20, 2018 123 PQR678
Jan 21, 2018 123 STU901
Jan 26, 2018 123 VWX234
Jan 27, 2018 123 YZA567
Based on the rules, I would like to be able to see the user's movement across segments week on week.
date (weekly) user_id segment_name
Jan 7, 2018 123 2
Jan 14, 2018 123 3
Jan 21, 2018 123 2
Jan 28, 2018 123 1
Hope someone can help me figure out how to do this.
EDIT: I made it up to this. I don't know how to turn this to only show the weekly summary and for the rec to show the days since the last transaction date from the weekly date.
date user_id orders cumu_orders rec date (weekly)
Jan 1, 2018 123 2 2 NA Jan 7, 2018
Jan 2, 2018 123 1 3 1 Jan 7, 2018
Jan 6, 2018 123 1 4 4 Jan 7, 2018
Jan 19, 2018 123 1 5 13 Jan 21, 2018
Jan 20, 2018 123 1 6 1 Jan 21, 2018
Jan 21, 2018 123 1 7 1 Jan 21, 2018
Jan 26, 2018 123 1 8 5 Jan 28, 2018
Jan 27, 2018 123 1 9 1 Jan 28, 2018
r
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
I'm looking to group users into segments according to their transaction behavior using R.
segment_name definition
1 1-2 days since last order, with minimum of 8 unique transaction_id since X date
2 1-2 days since last order, with less than 8 unique transaction_id since X date OR 3-6 days since last order
3 7+ days since last order
The reason why I put in X date in the above is that I would like to be able to assign a value to that date. Say if I wanted to see how different the segmentation would be if I looked at all their orders within the last 30 days vs last 60 days.
Anyway, I have a table like below:
date user_id transaction_id
Jan 1, 2018 123 ABC123
Jan 1, 2018 123 DEF456
Jan 2, 2018 123 GHI789
Jan 6, 2018 123 JKL012
Jan 19, 2018 123 MNO345
Jan 20, 2018 123 PQR678
Jan 21, 2018 123 STU901
Jan 26, 2018 123 VWX234
Jan 27, 2018 123 YZA567
Based on the rules, I would like to be able to see the user's movement across segments week on week.
date (weekly) user_id segment_name
Jan 7, 2018 123 2
Jan 14, 2018 123 3
Jan 21, 2018 123 2
Jan 28, 2018 123 1
Hope someone can help me figure out how to do this.
EDIT: I made it up to this. I don't know how to turn this to only show the weekly summary and for the rec to show the days since the last transaction date from the weekly date.
date user_id orders cumu_orders rec date (weekly)
Jan 1, 2018 123 2 2 NA Jan 7, 2018
Jan 2, 2018 123 1 3 1 Jan 7, 2018
Jan 6, 2018 123 1 4 4 Jan 7, 2018
Jan 19, 2018 123 1 5 13 Jan 21, 2018
Jan 20, 2018 123 1 6 1 Jan 21, 2018
Jan 21, 2018 123 1 7 1 Jan 21, 2018
Jan 26, 2018 123 1 8 5 Jan 28, 2018
Jan 27, 2018 123 1 9 1 Jan 28, 2018
r
r
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
edited Nov 27 '18 at 5:30
lostinsql
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
asked Nov 21 '18 at 15:25
lostinsqllostinsql
124
124
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27
add a comment |
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415290%2fr-user-segmentation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415290%2fr-user-segmentation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What have you tried? Looks like you should start by calculating the days since each transaction, so you are able to count them to identify your segment.
– cory
Nov 21 '18 at 16:45
I've managed to show me the weekly level, the last date of transaction of that week, and the last cumulative count of transactions for that week. I don't know how to calculate the number of days since the arbitrary week level date, if that makes sense. I can show the days since the last date, but not days since date identified as week of transaction.
– lostinsql
Nov 27 '18 at 5:20
First it is easiest to define extra columns, such as the number of days since a certain date. You can first create a column that defines the week of transaction, then use posix.ct or whatever datetime library you choose to determine days since week level date.
– qwr
Nov 27 '18 at 5:40
Thanks for that. I understand it, but I'm really new to R so it would really help if you could write out the code for it.
– lostinsql
Nov 27 '18 at 13:27