CRM Analytics and Einstein Discovery Insights Specialist

Pre-work and Notes

• There are many calculations and metrics for measuring and reporting churn rate. In the upcoming scenario and challenges, churn rate will be calculated as current quarter cancellations divided by current quarter subscribers.

• Carefully follow the naming conventions as specified in the scenario for query names, field names, and projection names since they're used to validate the challenges.

  • As you create queries or add widgets to the dashboard used in the challenges, make sure to update the query name to the name specified in the challenge as they are required for validation.
  • Use camel case spelling for variables and projection names, that is, lastName.
  • Use proper case for field names, that is, Last Name.
  • Use the underscore ( _ ) for API queries, fields and datasets containing spaces, that is, Last_Name.

• For validation purposes it's recommended that you don't add comments or notes to your queries or triggers.

• Create an CRM Analytics Developer Edition (DE) org for this challenge. This environment is enabled with CRM Analytics and Einstein Discovery. (Note: the default apps will not be used in this challenge).

• Install the unmanaged package to implement the Subscriber object. Note: the unmanaged package won't create a tab for the custom object, so implement these steps to create a tab for it on your org. If you have trouble installing a managed or unmanaged package or app from AppExchange, follow the steps in this article.

• Download and unzip the data-insight-specialist.zip and save it to your desktop.

• The CSV files contain US formatted date fields. If your org's locale is not en-US, you may want to consider changing it by following these steps or changing the formats of the Subscription Date and Churn Date fields based on your org's location.

References

• Use the Analytics SAQL Reference Guide to help you with SAQL query structure and syntax.

• The CRM Analytics Learning Adventure app contains examples that may be helpful with the challenges. Your DE is already equipped with the app. In Analytics Studio, click Create | App | Start from Template | Create App from Template | Learning Adventure.

• The Let's Play Salesforce Youtube channel also has helpful videos with plenty of SAQL and binding examples to reference.

Importing Data

You will be asked to perform multiple upload processes for the challenges. It's suggested for superbadge validation to follow the steps outlined below. You may opt to use a dataflow to upload data into CRM Analytics but not data sync (replication). Data Sync adds a suffix to dataset field names which the superbadge won't be able to validate.

Import records into the Subscriber object.

1. In Setup, enter Data Import Wizard in the Quick Find box and select Data Import Wizard.
2. Click Launch Wizard.
3. Click Custom objects then select Subscribers.
4. Click Add New Records. Select --None-- for the choices in the drop-down menus.
5. Click CSV. Select Choose File and navigate to Beattie Subs.csv on your desktop. Click Next.
6. Click Next, then Start Import.
7. Click Ok.

Individually upload the Beattie Subs.csv and Beattie OEM Survey.csv files.

1. In Analytics Studio, create a new blank app named Beattie for your work.
2. Locate and click the Create button and select Dataset.
3. Click CSV File on the following page.
4. Click the Select a file or drag it here button and navigate to your file in the folder of your desktop.
5. Click Next.
6. In the App field, replace the current app and select the Beattie app.
7. Click Next.
8. Keep the default metadata settings.
9. Click the Upload File button on the bottom right.
10. Click Close.

Upload the Beattie Dashboard.json file.

1. Open the JSON file in a text editor and copy it.
2. In the Analytics Studio Home page, click Create | Dashboard | Create Blank Dashboard.
3. Open the JSON Editor. Hold down Ctrl+E for a PC, or Cmd+E for a Mac.
4. Replace the existing dashboard JSON by selecting the existing JSON code and deleting it.
5. Click line 1 in the JSON Editor and paste the copied JSON code.
6. Click Done when complete.
7. Save the dashboard template into the Beattie app.
8. Click X on each open tab to return to the Analytics Studio Home page.

Scenario

In their wildest dreams, Arnas and Olivia Beattie never imagined the small, low-powered radio station they started 40 years ago would evolve into a national telecommunications company, Beattie Media and Broadcasting (Beattie Media). For many years, their remote town relied on distant stations for their entertainment and information. With this dependence came weak signals, if residents were lucky, and content that wasn’t specific to their community. Arnas and Olivia addressed these issues with their local radio station, followed by a television station soon after.

Arnas and Olivia continued growing their operations through acquisitions of distressed TV and radio stations, which enabled Beattie Media to expand into major markets. They also introduced a new talk radio format that gets syndicated across the country. They’ve used their earnings to fund further expansion and diversify offerings that now include telephone, home and business Internet, and streaming services.

Although Beattie Media’s growth has been impressive, Arnas and Olivia realize they’re still a small fish in a big pond. For many years, they’ve maintained their independence and resisted offers to sell. Because they haven’t been bought out, they’re aware they can always get pushed out by larger media conglomerates. In order to compete with the bigger guys, they’ve hired an investment bank to advise them in the process of transforming Beattie Media into a publicly traded company. By going public, Arnas and Olivia expect to strengthen their capital base and increase prestige. But, to qualify for an initial public offering (IPO), Beattie Media must meet several requirements laid out by the bank.

Fortunately, Beattie Media already meets many of them, such as:

• Strong growth history with solid earnings

• Experienced management team

• Low debt-to-equity ratio

However, where they fall short is predictable revenue. The markets don’t like it when a company has trouble reliably predicting earnings, which is crucial to long-term success.

Since Arnas and Olivia added phone, Internet, and streaming services to their business, they’ve had issues with customer attrition or churn. With the Internet being the great equalizer, consumers now have unprecedented choice. Beattie Media works tirelessly, offering promotions and deals to earn and re-earn loyalty, but their approach hasn’t been methodical or strategic.

The bank thinks Beattie Media has great potential and is counseling Arnas and Olivia to come up with a plan to achieve more stable earnings.

Arnas and Olivia have been longtime customers of Salesforce and heard about the visibility and insights they can gain from CRM Analytics, which they think is perfect for their goals. Their onsite Salesforce admin set up CRM Analytics on their org, and imported their Subscriber custom object as a dataset. As a member of Beattie Media’s BI team, you were sent to CRM Analytics training, and now that you’re back, Arnas and Olivia have challenged you to build an executive dashboard to help manage subscriber churn. By using your skills with Analytics, you will create a solution for Arnas and Olivia to discover new insights and be proactive in keeping their subscribers.

Beattie Media is counting on you, so let’s get started!

Standard Objects

• Standard objects won’t be used for the hands-on challenges.

Custom Objects

• Subscriber— Beattie Media’s phone, Internet, and streaming subscribers.

Field	Definition
Account Manager	Account owner’s ID
Rep Name	Account owner’s full name
Account Number	Subscriber’s account number
Name	Subscriber’s full name
Address	Subscriber’s street address
Subscription Date	Subscriber’s sign-up date
Senior Citizen	Whether the subscriber is a senior citizen or not (1, 0)
Partner	Whether the subscriber is associated with an OEM partner (Yes, No)
Dependents	Whether the subscriber has dependents or not (Yes, No)
Tenure	Number of months the subscriber has stayed with the company
Phone Service	Whether the subscriber has a phone service or not (Yes, No)
Multiple Lines	Whether the subscriber has multiple lines or not (Yes, No, No phone service)
Internet Service	Subscriber’s Internet service (DSL, Fiber optic, No)
Modem Age	Leased modem age (New, Less than 1 year, 1 - 2 years, > 4 years)
Online Security	Whether the subscriber has online security or not (Yes, No, No Internet service)
Online Backup	Whether the subscriber has online backup or not (Yes, No, No Internet service)
Device Protection	Whether the subscriber has device protection or not (Yes, No, No Internet service)
Tech Support	Whether the subscriber has a tech support contract or not (Yes, No, No Internet service)
Streaming TV	Whether the subscriber has streaming TV or not (Yes, No, No Internet service)
Streaming Movies	Whether the subscriber has streaming movies or not (Yes, No, No Internet service)
Contract	The contract term of the subscriber (Month-to-month, One year, Two year)
Paperless Billing	Whether the subscriber has paperless billing or not (Yes, No)
Payment Method	The subscriber’s payment method (Electronic check, Mailed check, Bank transfer (automatic), Credit card (automatic))
Monthly Charges	The amount charged to the subscriber monthly
Total Charges	The total amount charged to the subscriber
Churn	Whether the subscriber churned or not (Yes or No)
Churn Date	Service cancellation date
Latitude	Subscriber location
Longitude	Subscriber location
Postal Code	Subscriber’s postal code
Region	Subscriber’s home state (US)

External Files

• Beattie Subs—comma-separated values file (CSV) of Beattie Media’s phone, Internet, and streaming subscribers (see Subscriber object metadata).

• Beattie OEM Survey—comma-separated values file (CSV) listing Beattie Media’s subscriber base acquired through original equipment manufacturer (OEM) partnerships.

Field	Definition
Account Manager	Account owner’s ID
Rep Name	Account owner’s full name
Account Number	Subscriber’s account number
Subscription Date	Subscriber’s sign-up date
Region	Subscriber’s home state (US)
OEM	OEM partner name
OEM Type	OEM industry (Cable Provider, Media Player, PC Manufacturer, Phone Manufacturer, Television Manufacturer)
CSAT	Subscriber satisfaction rating (1–10)

Requirements Overview

• Calculate Subscriber Churn Rate

• Display Churn by Tenure

• Calculate Subscriber Acquisition Costs

• Compare Churn to Subscriber CSATs

• Deliver a Solution to Reduce Subscriber Attrition

Requirements Detail

Calculate Subscriber Churn Rate

Accurately calculating subscriber churn is going to be critical to Beattie Media’s plans to go public. Cancellation rates for their streaming services have been high because some subscribers only sign up for their favorite programs, such as Keeping Up with the Real Housewives In Paradise, The Walking Handmaids, or their favorite sporting event.

To help them safeguard their revenue and profits, Arnas and Olivia are asking for a chart that can help them get a better understanding of Beattie Media’s churn rate from quarter to quarter.

1. Open the Beattie Media dashboard and put it in Edit mode.

2. Locate the dashboard section labeled CHALLENGE 1 and replace it with a chart widget.

3. Use the Beattie Subs dataset.

4. Add Churn Rate as the query name.

5. Create a Churn Rate field which shows the percentage of subscribers who cancelled their service in a quarter. Beattie Media’s definition of churn is:

• Churn Rate needs the data to be organized by quarterly subscriber activity (sign ups and cancellations). For example, using a projection such as, ActivityDate_Year and ActivityDate_Quarter can be used to group data by Subscription Date and Churn Date.
• There are some quarters in the Beattie Subs file with only one type of activity, for example, no service sign ups, just cancellations. Keep this in mind as you group, cogroup, or union your data streams.
• The calculation you're asked to create in this challange is tough, but Arnas and Olivia have confidence in you! Use the table in the above Churn Rate graphic to help you devise your formula for this challenge.
  

1. Present your solution as a Line chart.

2. Add a chart marker to the first quarter on your chart that's reporting a churn rate. It will be a hard-coded marker to help validation assess your solution.

3. Save your dashboard as Beattie Media Executive Dashboard in the Beattie app.

4. Your solution should follow this example.

Display Churn by Tenure

Beattie Media’s wheeling and dealing to make their newer subscribers happy has also come at a cost to their loyal subscribers, who were there at the beginning. Arnas and Olivia are dismayed to hear their longtime subscribers are feeling ignored and starting to lose their sense of loyalty. These once-steadfast subscribers are also cancelling their services for better deals. Arnas and Olivia would like to monitor churn within subscriber tenure. For example, measure the number of subscribers who’ve been with them for over 2 years and cancelled. They’re asking for a churn by tenure chart to help them be more strategic and targeted for all their subscribers.

1. Locate the dashboard section labeled CHALLENGE 2 and replace it with a chart widget.

2. Add Churn Tenure as the query name.

3. Add a calculation that divides the number of churned subscribers (Churn = "Yes") by the total number of subscribers. Churned subscribers will be the numerator, total subscribers will be the denominator.

4. Add a toggle widget above the Churn Tenure chart.

5. Add Tenure Length as the display label.

6. Add the following custom definition to the widget.

Tenure Length (Display)	Length of Tenure (Value)
High Risk	1 to 12 months
Medium Risk	13 to 24 months
Low Risk	25 to 36 months

Bind the Tenure Length toggle widget to the Churn Tenure chart and have it filter the Tenure field. Your solution should look similar to the example below.

Calculate Subscriber Acquisition Costs

Beattie Media offers unlimited service plans, which are driven by paid subscriptions. Every subscriber who cancels their service shrinks their subscriber base. The smaller the subscriber pool gets, the more expensive it becomes to lure replacement subscribers, which they have estimated to cost $950.00 (USD) per household. Service cancellations put pressure on Beattie Media to make up for itinerant subscribers with new ones.

Arnas and Olivia are asking you to create a chart that displays subscriber revenue by state and attrition costs spent to make up for the subscribers they lost. Since you were able to categorize tenure, they also want to see attrition costs faceted by tenure.

1. Locate the dashboard section labeled CHALLENGE 3 and replace it with a chart widget.

2. Add Subscriber Revenue as the query name.

3. In the query, create a bar chart that sums the TotalCharges field and groups it by the Region field.

4. Create another query, with Attrition Cost as the name, that calculates subscriber attrition cost, [Churned Subscribers (Churn = ‘Yes’) x $950.00].

5. Use the projection name attrCost for your calculation in the previous query.

6. Add a reference line to the Subscriber Revenue chart.

7. Create a results binding that displays attrition cost in the reference line. Also, make it possible for the reference line value to filter by length of tenure from the Tenure Length toggle.

8. Your solution should look similar to this example.

Compare Churn to Subscriber CSATs

A sizable portion of Beattie Media’s subscriber base comes from their partnership with OEM manufacturers, which includes PC and television manufacturers, to name a few. These companies have agreed to carry Beattie Media’s app on their equipment to access streaming content on their devices.

These subscribers were asked to rank their satisfaction (1 = Extremely Dissatisfied to 10 = Extremely Satisfied) with their streaming service. Arnas and Olivia are interested in seeing the regions and OEM manufacturers where low CSATs and low tenure are occurring.

1. Locate the dashboard section labeled CHALLENGE 4 and replace it with two chart widgets.

2. On the first chart add Beattie Survey as the query name.

3. Calculate average Tenure using the Tenure field from the Beattie Subs dataset and average CSAT from using the CSAT field from the Beattie OEM Survey dataset.

4. On the second chart add OEM as the query name.

5. Group data from the Beattie OEM Survey dataset by the OEM field and add a count of rows measure, and display as a treemap chart.

6. Confirm the treemap chart facets on selections from the pyramid chart.

7. Add a chart marker to the Beattie Survey chart for the first state with the lowest average CSAT score. It will be a hard-coded marker to help validation assess your solution.

Consider the information below as you develop your query.

• Every subscriber in the Beattie OEM Partners file has a minimum of one record in Beattie Subscribers.
• Beattie Media’s Account Managers are assigned to an OEM partner and oversees the relationship.
• Group the datasets using the Account Manager and Region fields.
• Round the output for CSAT and Tenure to whole numbers.
• Sort the results by CSAT scores in ascending order.
• Present your solution as a pyramid chart.

Your solution should look similar to this example.

Deliver a Solution to Reduce Subscriber Attrition

The investment bank Beattie Media hired suggests that contracts of at least 2 years can help with the image of more stable earnings and likely lead to renewals. Beattie Media hasn’t insisted on lengthy contracts because they know it’s a turnoff to their current and potential subscribers. Past promotions have bought Beattie Media a few extra months at best, but they haven’t discovered a winning pattern for longer tenure.

You tell Arnas and Olivia about Einstein Discovery and its fast, accessible, and predictive insights. You lay out the expectation that this will be an iterative process. Arnas and Olivia agree and are excited to see what you come up with. You also tell them you’ll create a mock-up of how predictions and prescriptions from Einstein Discovery will appear on subscribers’ service records. Arnas and Olivia love this idea and would like this information to be on hand to assist their Customer Success Agents.

Your task is to analyze the dataset and produce a predicted subscriber tenure.

Create a model in Einstein Discovery

1. Create an “Insights & Predictions” model in Manual mode using the Beattie Subs dataset to generate a goal to maximize subscriber tenure.
2. Review all the fields used in the model and create a new model version by removing any fields which might be contributing to data leakage. Hint there is one field that should not be used in the predictive model because it is only populated when the record reaches the terminal state.
3. Review any date field(s) used in the model. Should you be including the year? Consider any new records that will be scored with this model. Will the predictive model know how to handle the year? Consider focusing on “day of week” or “month of year”.
4. Make sure you have included fields that significantly improve the prediction. Explore model metrics and make sure this model is good enough to be deployed.

Deploy Einstein Discovery Prediction

1. Once you have tuned the model, use these steps to deploy a new prediction and connect it to the Subscriber object.
2. Name your prediction Predicted Tenure.
3. Connect your prediction to the Subscriber object.
4. Map all the fields from the model to the Subscriber object.
5. Select Contract and Payment Method as Action Variables.
6. Review and Deploy the Model.
7. Create a new Prediction Field and label it as Predicted Tenure.

Add Einstein Prediction Lightning Card to Subscriber Record Lightning Page

1. Using the Lightning App Builder, Create a Record Page for the Subscriber Object
2. Name the page Default.
3. Choose Clone Salesforce Default Page and select Grouped View Default.
4. Drag and drop the Einstein Predictions Lightning Component into the Right Side Panel
5. In the Einstein Predictions setup panel, click in the search box under Prediction and choose Predicted Tenure.
6. Activate the page and assign as Org Default for the Desktop.

Add Prediction field to Subscriber List View

1. Using Object Manager, navigate to the Subscriber Object and locate the Predicted Tenure field.
2. Set the Field Level Security such that the field is visible to your profile.
3. Navigate to the Subscriber Tab.
4. Create a new List View Called All Subscribers.
5. Include: Subscriber Name, Predicted Tenure and any other fields you wish to show up and save the list view.
6. Notice the Predicted Tenure field is blank. This is because new predictions are only generated after a record is created or updated.

View Predictions and Generate New Predictions

1. Navigate to any Subscriber Record.
2. Notice the prediction displayed in the lightning card.
3. Make a change to one of the top predictive factors fields and save the record.
4. Navigate back to the list view and verify you see a value for Predicted Tenure for the record which was updated.