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!

Sandhya (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 CARMIGNANIHi 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

Emilien Guichard 40Hi Julien,

here is the code I used :

Ivan Jerez CordobaI 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 11Hi 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? :

Thank in advance!

Shivakant Tripathi 7Dear 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 9number_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 3Hi,

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

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 GhaffarDoes this still have 10000 records limit? We stopped using in last company.

Good luck! from Wristerr (https://wristerr.com/)

Gabriel Alindogan 1hi. 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 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.@Marite Ferrero

[-1..0]is the range ofwindowing 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 '

' with the function 'sum', sums up the range ofoveroverI 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 rowof 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)Marite FerreroHello @ 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)

to 'Previous Quarter Subcribers' which is both the current and previous (because you used range of [-1..0] ?again(sum(sub_2.'New Quarter Subcribers') +sum(sub_2.'Previous Quarter Subcribers') -

sum(cancel_2.'Previous Quarter Cancellations')

)

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 Ferrero

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 FerreroHello @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 43do i need to write such big SAQL query to calculate charn rate ? Please help . Ekta Gupta 43Cant it be done with compare table simly :(

Vikash RanjanHi 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 10I 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.