+ Start a Discussion
Bruno V.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!

User-added image
 
SandhyaSandhya (Salesforce Developers) 
Hi,

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
 
Julien CARMIGNANIJulien CARMIGNANI
Hi 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
q = load "Beattie_Subs";

cancel = group q by ('Churn_Date_Year', 'Churn_Date_Quarter');
cancel = foreach cancel generate cancel.'Churn_Date_Year' + "~~~" + cancel.'Churn_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Cancellations';

cancel = group cancel by ('ActivityDate_Year~~~ActivityDate_Quarter');
cancel = foreach cancel generate cancel.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', coalesce(sum(cancel.'New Quarter Cancellations'),0) as 'New Quarter Cancellations', coalesce(sum(sum('New Quarter Cancellations')) over ([..-1] partition by all order by ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Cancellations';

sub = group q by ('Subscription_Date_Year', 'Subscription_Date_Quarter');
sub = foreach sub generate sub.'Subscription_Date_Year' + "~~~" + sub.'Subscription_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Subcribers';

sub = group sub by ('ActivityDate_Year~~~ActivityDate_Quarter');
sub = foreach sub generate sub.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', coalesce(sum(sub.'New Quarter Subcribers'),0) as 'New Quarter Subcribers', coalesce(sum(sum('New Quarter Subcribers')) over ([..-1] partition by all order by ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Subcribers';

result = cogroup sub by 'ActivityDate_Year~~~ActivityDate_Quarter' full, cancel by 'ActivityDate_Year~~~ActivityDate_Quarter';
result = foreach result generate coalesce(sub.'ActivityDate_Year~~~ActivityDate_Quarter', cancel.'ActivityDate_Year~~~ActivityDate_Quarter') as 'ActivityDate_Year~~~ActivityDate_Quarter', sum(cancel.'New Quarter Cancellations')/(sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations')) as 'Churn Rate';
Emilien Guichard 40Emilien Guichard 40
Hi Julien,

here is the code I used :
 
q = load "Beattie_Subs";

cancel = group q by ('Churn_Date_Year', 'Churn_Date_Quarter');
cancel = foreach cancel generate cancel.'Churn_Date_Year' + "~~~" + cancel.'Churn_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Cancellations';

cancel = group cancel by ('ActivityDate_Year~~~ActivityDate_Quarter');
cancel = foreach cancel generate cancel.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', 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 ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Cancellations';

sub = group q by ('Subscription_Date_Year', 'Subscription_Date_Quarter');
sub = foreach sub generate sub.'Subscription_Date_Year' + "~~~" + sub.'Subscription_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Subcribers';

sub = group sub by ('ActivityDate_Year~~~ActivityDate_Quarter');
sub = foreach sub generate sub.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', 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 ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Subcribers';

result = cogroup sub by 'ActivityDate_Year~~~ActivityDate_Quarter' full, cancel by 'ActivityDate_Year~~~ActivityDate_Quarter';
result = foreach result generate coalesce(sub.'ActivityDate_Year~~~ActivityDate_Quarter', cancel.'ActivityDate_Year~~~ActivityDate_Quarter') as 'ActivityDate_Year~~~ActivityDate_Quarter', sum(cancel.'New Quarter Cancellations')/(sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations')) as 'Churn Rate';

 
Ivan Jerez CordobaIvan Jerez Cordoba
I always get an error when I check step 1 :

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?
 
Evgeniya Stancheva 11Evgeniya Stancheva 11
Hi all, I am stuck on this step and still not sure how to do this exercise, I have seen the proposed solution in this chat but I am still new to coding and does not make sense, where I have to add this code, in the JSON of the dashboard? Can someone help with step by step? Also,do I need to create anuthing else in the Dataset? Do I have to create a field in SF as well? :
User-added image
Thank in advance!
Shivakant Tripathi 7Shivakant Tripathi 7
Dear Emilien, 

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
Paige Delk 9Paige Delk 9
number_to_string(
(sum(cancel.'New Quarter Cancellations'))/((sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations'))),"#.00%") as 'Churn Rate';
Pramod V 3Pramod V 3
Hi,

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:
q = load "Beattie_Subs";

cancel = group q by ('Churn_Date_Year', 'Churn_Date_Quarter');
cancel = foreach cancel generate cancel.'Churn_Date_Year' + "~~~" + cancel.'Churn_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Cancellations';

cancel = group cancel by ('ActivityDate_Year~~~ActivityDate_Quarter');
cancel = foreach cancel generate cancel.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', 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 ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Cancellations';

sub = group q by ('Subscription_Date_Year', 'Subscription_Date_Quarter');
sub = foreach sub generate sub.'Subscription_Date_Year' + "~~~" + sub.'Subscription_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Subcribers';

sub = group sub by ('ActivityDate_Year~~~ActivityDate_Quarter');
sub = foreach sub generate sub.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', 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 ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Subcribers';

result = cogroup sub by 'ActivityDate_Year~~~ActivityDate_Quarter' full, cancel by 'ActivityDate_Year~~~ActivityDate_Quarter';
result = foreach result generate coalesce(sub.'ActivityDate_Year~~~ActivityDate_Quarter', cancel.'ActivityDate_Year~~~ActivityDate_Quarter') as 'ActivityDate_Year~~~ActivityDate_Quarter', sum(cancel.'New Quarter Cancellations')/(sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations')) as 'Churn Rate';

Thanks,
Pramod V
Md. Abdur Razzak.Md. Abdur Razzak.
q = load "Beattie_Subs";

--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 
Daniyal GhaffarDaniyal Ghaffar
Does this still have 10000 records limit? We stopped using in last company.
Good luck! from Wristerr (https://wristerr.com/)
 
Gabriel Alindogan 1Gabriel Alindogan 1
hi. I plotted by hand the beattieSubs dataset and the highest churn I got was 12% on Q4 2017. In addition, from Q1 2016 to q4 2019, there were only  3 quarters with zero churn because there were only hree instances when current cancel =0. Some of the solutions proposed here have churn as high as 120% and a lot of zero values (~8).I am having problems with the recursive nature of the currentQtr subscribers.('CQS') since the CQS is part of the actual computation of the CQS only offset by 1. Any help would be appreciated.
Marite FerreroMarite Ferrero
@ Md. Abdur Razzak. (http://Md. Abdur Razzak)
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.
Md. Abdur Razzak.Md. Abdur Razzak.
@Marite Ferrero

[-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 )

Over function range issue
Marite FerreroMarite Ferrero
Hello @ Md. Abdur Razzak.

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')
        )

 
Md. Abdur Razzak.Md. Abdur Razzak.
Yes, @Marite Ferrero
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  
Marite FerreroMarite Ferrero
formula for Churn Rate

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. 

 
Marite FerreroMarite Ferrero
Hello @Md. Abdur Razzak.
Never Mind. I looked at the chart Q1, Q2, Q3 given as example. I understand now that it is cumulative. Great. 
Thanks for helping!
Ekta Gupta 43Ekta Gupta 43
do i need to write such big SAQL query to calculate charn rate ? Please help  . 
Ekta Gupta 43Ekta Gupta 43
Cant it be done with compare table simly :( 
 
Vikash RanjanVikash Ranjan
Hi All,
 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
Fu Gao 10Fu Gao 10
I wonder if i can manage finishing this chanllenge by create a dataflow using computeRelative & computeExpression to generate a churn rate filed so that i can use it in the chart.