function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ 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.
Luiz Gustavo Dorfman ReisLuiz Gustavo Dorfman Reis
hello guys so 2021 different problems

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';
Mahmud Hossain 1Mahmud Hossain 1
Hi Luiz, Facing the same problem as you questioned here though my chart is showing the correct result. did you already solve it
 
RAJLAKSHMI MITRARAJLAKSHMI MITRA

Hello everyone,

Need small help. 

After writing the SAQL query for Churn Rate calculation, able to get correct values in Query Mode:

User-added image

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

User-added image

Thanks in advance.

Regards,

Rajlakshmi

Leonardo Santana 2Leonardo Santana 2
Hello guys, 
 
I'm still stuck on the step 1, I getting the error below:
User-added image

And there is my code and graphic.
User-added image

 
q = load "Beattie_Subs";

Subscribers = group q by ('Subscription_Date_Year', 'Subscription_Date_Quarter');
Subscribers = foreach Subscribers generate 'Subscription_Date_Year' + "-" + 'Subscription_Date_Quarter' as 'ActivityDate_Year-ActivityDate_Quarter', count() as 'Current Quarter Subscribers';

Churns = group q by ('Churn_Date_Year', 'Churn_Date_Quarter');
Churns = foreach Churns generate 'Churn_Date_Year' + "-" + 'Churn_Date_Quarter' as 'ActivityDate_Year-ActivityDate_Quarter', count() as 'Current Quarter Churns';

Result = group Subscribers by 'ActivityDate_Year-ActivityDate_Quarter' full, Churns by 'ActivityDate_Year-ActivityDate_Quarter';

Result = foreach Result generate coalesce(Subscribers.'ActivityDate_Year-ActivityDate_Quarter', Churns.'ActivityDate_Year-ActivityDate_Quarter') as 'ActivityDate_Year-ActivityDate_Quarter', sum(Subscribers.'Current Quarter Subscribers') as 'Current Quarter Subscribers', sum(Churns.'Current Quarter Churns') as 'Current Quarter Churns';

Result = group Result by ('ActivityDate_Year-ActivityDate_Quarter', 'Current Quarter Subscribers', 'Current Quarter Churns');
Result = foreach Result generate 'ActivityDate_Year-ActivityDate_Quarter', sum('Current Quarter Subscribers') as 'Current Quarter Subscribers', 'Current Quarter Churns', sum('Current Quarter Subscribers') over ([..-1] partition by all order by 'ActivityDate_Year-ActivityDate_Quarter') - coalesce(sum('Current Quarter Churns') over ([-1..-1] partition by all order by 'ActivityDate_Year-ActivityDate_Quarter'),0) as 'Previous Quarter Subscribers';

Result = foreach Result generate 'ActivityDate_Year-ActivityDate_Quarter', 'Current Quarter Churns'/(coalesce('Previous Quarter Subscribers',0) + coalesce('Current Quarter Subscribers',0)) as 'Churn Rate';




If someone knows and can explain how i can finish it, it would be really helpfull and gratefull.
 
 
Thanks,
Leonardo.