Churn Rate calculation - Last 30 days / 31 st Day value - Redshift
Hi any help is appreciated in giving some ideas
Churn Rate :
current date minus 30 days lost user
——————————————————————————
New Users count 31 days ago
We have data as follows:
Note: on a same date we can have Users Lost and new users, the User ID is a distinct ID.
UserID, Date,User Type, Column4,Column5,Column6
5123 ,11/14/2018, Lost
612312 ,11/14/2018 ,New
51241 ,11/13/2018 ,New
241 ,11/12/2018 ,Lost
.
.
.
.
123 10/18/2018 Lost
3451 10/17/2018 New
512321 10/16/2018 New
21323 10/15/2018 Lost
213132 10/14/2018 Lost
123256 10/13/2018 Lost
780303 10/12/2018 New
Expected outcome:
- Group by Dates
- User Type Column is split by 2 Columns - Last 30 Days Lost Users(A) New Users 31 days Ago(B)
- Last 30 days Lost Users - is the Count of Users Lost between Current Date - 30 days
- New Users 31 days Ago - is the count of User on -31st day from the current date.
- Churn .. A / B
churn
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
add a comment |
Hi any help is appreciated in giving some ideas
Churn Rate :
current date minus 30 days lost user
——————————————————————————
New Users count 31 days ago
We have data as follows:
Note: on a same date we can have Users Lost and new users, the User ID is a distinct ID.
UserID, Date,User Type, Column4,Column5,Column6
5123 ,11/14/2018, Lost
612312 ,11/14/2018 ,New
51241 ,11/13/2018 ,New
241 ,11/12/2018 ,Lost
.
.
.
.
123 10/18/2018 Lost
3451 10/17/2018 New
512321 10/16/2018 New
21323 10/15/2018 Lost
213132 10/14/2018 Lost
123256 10/13/2018 Lost
780303 10/12/2018 New
Expected outcome:
- Group by Dates
- User Type Column is split by 2 Columns - Last 30 Days Lost Users(A) New Users 31 days Ago(B)
- Last 30 days Lost Users - is the Count of Users Lost between Current Date - 30 days
- New Users 31 days Ago - is the count of User on -31st day from the current date.
- Churn .. A / B
churn
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
add a comment |
Hi any help is appreciated in giving some ideas
Churn Rate :
current date minus 30 days lost user
——————————————————————————
New Users count 31 days ago
We have data as follows:
Note: on a same date we can have Users Lost and new users, the User ID is a distinct ID.
UserID, Date,User Type, Column4,Column5,Column6
5123 ,11/14/2018, Lost
612312 ,11/14/2018 ,New
51241 ,11/13/2018 ,New
241 ,11/12/2018 ,Lost
.
.
.
.
123 10/18/2018 Lost
3451 10/17/2018 New
512321 10/16/2018 New
21323 10/15/2018 Lost
213132 10/14/2018 Lost
123256 10/13/2018 Lost
780303 10/12/2018 New
Expected outcome:
- Group by Dates
- User Type Column is split by 2 Columns - Last 30 Days Lost Users(A) New Users 31 days Ago(B)
- Last 30 days Lost Users - is the Count of Users Lost between Current Date - 30 days
- New Users 31 days Ago - is the count of User on -31st day from the current date.
- Churn .. A / B
churn
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
Hi any help is appreciated in giving some ideas
Churn Rate :
current date minus 30 days lost user
——————————————————————————
New Users count 31 days ago
We have data as follows:
Note: on a same date we can have Users Lost and new users, the User ID is a distinct ID.
UserID, Date,User Type, Column4,Column5,Column6
5123 ,11/14/2018, Lost
612312 ,11/14/2018 ,New
51241 ,11/13/2018 ,New
241 ,11/12/2018 ,Lost
.
.
.
.
123 10/18/2018 Lost
3451 10/17/2018 New
512321 10/16/2018 New
21323 10/15/2018 Lost
213132 10/14/2018 Lost
123256 10/13/2018 Lost
780303 10/12/2018 New
Expected outcome:
- Group by Dates
- User Type Column is split by 2 Columns - Last 30 Days Lost Users(A) New Users 31 days Ago(B)
- Last 30 days Lost Users - is the Count of Users Lost between Current Date - 30 days
- New Users 31 days Ago - is the count of User on -31st day from the current date.
- Churn .. A / B
churn
churn
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
edited Nov 15 '18 at 6:09
Zain Farooq
1,9982928
1,9982928
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
asked Nov 15 '18 at 5:37
sunnygudsunnygud
32
32
add a comment |
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%2f53313059%2fchurn-rate-calculation-last-30-days-31-st-day-value-redshift%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%2f53313059%2fchurn-rate-calculation-last-30-days-31-st-day-value-redshift%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
