You need to sign in to do that
Don't have an account?
Bruno V.
SuperBadge: Einstein Analytics and Discovery Insights Specialist - STEP 1
Hi Admins and Developers! What's up?
I'm learning Einstein Analytics and it is my first Superbadge, so I've some questions about it:
1) This first step we need to create a chart that show churn rate from quarter, I'm lost in 5º step, I really don't know how to do this requires to this chart.
I'm grateful for help!
I'm learning Einstein Analytics and it is my first Superbadge, so I've some questions about it:
1) This first step we need to create a chart that show churn rate from quarter, I'm lost in 5º step, I really don't know how to do this requires to this chart.
I'm grateful for help!
Follow below steps.
1.Load the dataset into q
filter q into a new stream (cancellations) by Churn (you want the number of cancellations, so set the churn bit appropriately)
2.Group cancellations by ChurnDate year/quarter but project the date as ActivityDate year/quarter; count() as current quarter cancellations,
3.you then need to lag cancellations by one period, so generate again but this time collect 'cancellations' by sum and in a separate column, include a window function with sum to pick up the prior period. I won't tell you have to do it, but read up on windowing
4.group q by Subscription_Date year/quarter
5.generate a count() for "subscribers" and like above, project subscription_date year/quarter as ActiviityDate year/quarter.
6.re-generate the stream from step 6 as a sum of "subscribers" and add lagged subscribers by the same windowing sum that you did for cancelllations.
7.then you can either cogroup with a full join on ActivityDate year/quarter (and then generate the formula you need) or union and regroup by ActivityDate year/quarter.
Refer below thread for similar discussion.
https://developer.salesforce.com/forums/?id=9062I000000g5xRQAQ
Please mark it as solved if my reply was helpful. It will make it available for other as the proper solution.
Best Regards
Sandhya
I am stuck since several days on this challenge. I have a doubt on how I use the windowing functions to sum over the previous period. Could you please let me know what is wrong in the following SAQL code.
Thanks by advance for your help.
Julien
here is the code I used :
Challenge Not yet complete... here's what's wrong:
We can't confirm this is the correct quarter. Records should be grouped by subscriber activity (sign ups and cancellations). A suggestion is to generate a stream for subscriber records by Subscription Date and its counts, along with Churn Date and its counts. Ensure both streams for these date fields share a common variable then create a union of both streams.
Close errors
Can anyone help me?
Thank in advance!
Your Answer Works however i have issue in conterting final Churn Rate into %
I tried
q = foreach q generate 'Amount' as 'Amount', number_to_string('Amount',"#.00%") as 'Churn Rate';
But its messing the data.
I appreciate the help.
Thanks
(sum(cancel.'New Quarter Cancellations'))/((sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations'))),"#.00%") as 'Churn Rate';
Tried various combinations to get rid of this error, but couldn't eradicate. What am I missing here?
We can't find a 'Churn Rate' step. Confirm the step name and spelling.
Here is my code:
Thanks,
Pramod V
--new cancellations grouping and calculations
cancel_1 = group q
by (
'Churn_Date_Year',
'Churn_Date_Quarter'
);
cancel_1 = foreach cancel_1
generate
cancel_1.'Churn_Date_Year' + " - " + cancel_1.'Churn_Date_Quarter' as 'Cancellation Activity',
count() as 'Current Quarter Cancellations';
--previous cancellations calculations with (current cancellations)
cancel_2 = group cancel_1
by ('Cancellation Activity');
cancel_2 = foreach cancel_2
generate
cancel_2.'Cancellation Activity' as 'Cancellation Activity',
coalesce(
sum(cancel_2.'Current Quarter Cancellations'),
0
) as 'Current Quarter Cancellations',
coalesce(
sum(sum('Current Quarter Cancellations'))
over(
[-1..-1]
partition by all
order by ('Cancellation Activity')
),
0
) as 'Previous Quarter Cancellations';
--new subscribers grouping and calculations
sub_1 = group q
by (
'Subscription_Date_Year',
'Subscription_Date_Quarter'
);
sub_1 = foreach sub_1
generate
sub_1.'Subscription_Date_Year' + " - " + sub_1.'Subscription_Date_Quarter' as 'Subscriber Activity',
count() as 'New Quarter Subcribers';
--previous subscribers calculations with (current subscribers)
sub_2 = group sub_1
by ('Subscriber Activity');
sub_2 = foreach sub_2
generate
sub_2.'Subscriber Activity' as 'Subscriber Activity',
coalesce(
sum(sub_2.'New Quarter Subcribers'),
0
) as 'New Quarter Subcribers',
coalesce(
sum(sum('New Quarter Subcribers'))
over(
[-1..0]
partition by all
order by ('Subscriber Activity')
),
0
) as 'Previous Quarter Subcribers';
--calculating rating
result = cogroup
sub_2 by 'Subscriber Activity' full,
cancel_2 by 'Cancellation Activity';
result = foreach result
generate
coalesce(
sub_2.'Subscriber Activity',
cancel_2.'Cancellation Activity'
) as 'Subscriber Activity',
sum(cancel_2.'Current Quarter Cancellations') /
(
sum(sub_2.'New Quarter Subcribers') +
sum(sub_2.'Previous Quarter Subcribers') -
sum(cancel_2.'Previous Quarter Cancellations')
) as 'Churn Rate';
--after adding it to the dashboard add a cart marker to the first quarter point
Good luck! from Wristerr (https://wristerr.com/)
I checked out your answer above. Mine is similar except I used " [-1..-1] " to get my Previous QTR Subscribers.
Can you explain why you used [-1..0] ?
Thank you.
[-1..0] is the range of windowing function 'over' ( over [start range..end range] )
the range [-1..0] covers 2 rows ( current row and the previous row )
-2 = two row prior to the current row
-1 = one row prior to the current row
0 = the current row
1 = one row ahead of current row
2 = two row ahead of current row
you can get more information on 'over' function here (https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_windowing.htm?search_text=over" target="_blank)
using the function 'sum' with the function 'over', sums up the range of over
I used 'over' with range [-1..0] of the column 'New QTR Subcribers' to get the column 'Previous QTR Subscribers'.
Because I wanted to get the sum
here, sum = one previous row + the current row of the column 'New QTR Subcribers'
If I would use the range as [-1..-1] it would cover only one row ( the previous row of current row )
I understand that range [-1..0] will get you both sequential QTRs (the current and previous) and you call this 'Previous Quarter Subcribers'.
Your code to calculate the churn rate shows:
result = foreach result generate
coalesce(sub_2.'Subscriber Activity', cancel_2.'Cancellation Activity') as 'Subscriber Activity',
sum(cancel_2.'Current Quarter Cancellations') /
(sum(sub_2.'New Quarter Subcribers') +
sum(sub_2.'Previous Quarter Subcribers') -
sum(cancel_2.'Previous Quarter Cancellations')
) as 'Churn Rate';
Doesn't this mean that you are adding 'New Quarter Subscribers' (which is actually the 'current' row) again to 'Previous Quarter Subcribers' which is both the current and previous (because you used range of [-1..0] ?
(sum(sub_2.'New Quarter Subcribers') +
sum(sub_2.'Previous Quarter Subcribers') -
sum(cancel_2.'Previous Quarter Cancellations')
)
the scenario is like that.
And the
(
sum(sub_2.'New Quarter Subcribers') +
sum(sub_2.'Previous Quarter Subcribers') -
sum(cancel_2.'Previous Quarter Cancellations')
)
is actually given in the equation of the churn rate in the picture
Hello @ Md. Abdur Razzak.
From the picture above and example, it shows that the formula is:
Churn Rate = Current Quarter Cancellations / (New Quarter Subscribers + Previous Quarter Subscribers) - Previous Quarter Cancellations
It even gave examples. Looking at the first one (Q1) above,
2.5% = 5 / (50 + 150) - 0
wherein 5 is Current QTR Cancellations
50 is Current QTR Subscribers
150 is Previous QTR Subscribers
0 is Previous QTR Cancellations
Your formula is:
(sum(sub_2.'New Quarter Subcribers') +
sum(sub_2.'Previous Quarter Subcribers') -
sum(cancel_2.'Previous Quarter Cancellations')
)
where your sub_2.'New Quarter Subcribers' is 50
your sub_2.'Previous Quarter Subcribers' is 200 (since your confirmed that you use range of [-1..0] to get the value)
your cancel_2.'Previous Quarter Cancellations' is 0
Your formula would actually give:
2.0% = 5 / (50 + 200) - 0
which isn't the same as the given example above.
You likely passed step 1. What I will do is to try it with the range of [-1..-1] and if it passes.
Never Mind. I looked at the chart Q1, Q2, Q3 given as example. I understand now that it is cumulative. Great.
Thanks for helping!
while working on Connect Einstein Discovery to the custom fields. Use Tenure as the name for your entry in the Einstein Discovery-Write Back
I am Data Storage Limits Exceeded error, i have tried after deleting some of the records but did not able to solve it. May i request to all if you have any idea on this.
Thanks & Regards,
Manish Ranjan
Challenge Not yet complete... here's what's wrong:
We can't confirm this is the correct quarter. Records should be grouped by subscriber activity (sign ups and cancellations). A suggestion is to generate a stream for subscriber records by Subscription Date and its counts, along with Churn Date and its counts. Ensure both streams for these date fields share a common variable then create a union of both streams.
Somebody can celp me !!
this is my Query
q = load "Beattie_Subs";
cancel = group q by ('ChurnDate_Year', 'ChurnDate_Quarter');
cancel = foreach cancel generate cancel.'ChurnDate_Year' + " - " + cancel.'ChurnDate_Quarter' as 'Subscriber activity', count() as 'New Quarter Cancellations';
cancel = group cancel by ('Subscriber activity');
cancel = foreach cancel generate cancel.'Subscriber activity' as 'Subscriber activity', coalesce(sum(cancel.'New Quarter Cancellations'),0) as 'New Quarter Cancellations', coalesce(sum(sum('New Quarter Cancellations')) over ([-1..-1] partition by all order by ('Subscriber activity')),0) as 'Previous Quarter Cancellations';
sub = group q by ('SubscriptionDate_Year', 'SubscriptionDate_Quarter');
sub = foreach sub generate sub.'SubscriptionDate_Year' + " - " + sub.'SubscriptionDate_Quarter' as 'Subscriber activity', count() as 'New Quarter Subcribers';
sub = group sub by ('Subscriber activity');
sub = foreach sub generate sub.'Subscriber activity' as 'Subscriber activity', coalesce(sum(sub.'New Quarter Subcribers'),0) as 'New Quarter Subcribers', coalesce(sum(sum('New Quarter Subcribers')) over ([-1..0] partition by all order by ('Subscriber activity')),0) as 'Previous Quarter Subcribers';
result = cogroup sub by 'Subscriber activity' full, cancel by 'Subscriber activity';
result = foreach result generate coalesce(sub.'Subscriber activity', cancel.'Subscriber activity') as 'Subscriber activity', sum(cancel.'New Quarter Cancellations')/(sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations')) as 'Churn Rate';
Hello everyone,
Need small help.
After writing the SAQL query for Churn Rate calculation, able to get correct values in Query Mode:
But as soon I click on Chart Mode, the Churn Rate measure is greyed out, unable to use in Line chart. Can anyone suggest how to resolve the same? It would be of great help
Thanks in advance.
Regards,
Rajlakshmi
I'm still stuck on the step 1, I getting the error below:
And there is my code and graphic.
If someone knows and can explain how i can finish it, it would be really helpfull and gratefull.
Thanks,
Leonardo.