Skill: Medium

Summary

Often, a strong upsell signal is to see when a given account's product usage increases significantly. In this walkthrough, you'll take user activity data, calculate average monthly active users over two different time periods per account, and create an alert for when they've increased over 10%.

You'll Need

• A connection to the Demo data source(see How To Add A Connection)
• A connection to your company Slack. Alternatively, you could also use e-mail, a Slack webhook connection, or create an Asana task.
• To know how to create a new Workbook. (see How To Create A Workbook)

Steps

1. For this, we'll need from the Demo connection the "Salesforce Accounts" and "User Activity" tables. If you've already added either, you can skip steps 1 or 2, respectively.

   Hit "Add Table":

   

   Lets add the Salesforce Accounts table first:

   

   The defaults on each page of the "Add Table" wizard should be suitable.

   

   When you get to the relationships page, if you've added any other data from the Demo data source connection you'll see a relationship to that data. Yours may not look like below- that's okay! When you add those other tables, we'll automatically prepare those relationships for you, too.

   

   Once you hit "Create", it'll pull that data into your system, and automatically forming any relationships with prior data.

2. For the "User Activity" table, you'll want to go through the same process detailed above, but select "User Activity" on the first page of the wizard.

1. With both added, lets create a new workbook, and name it "Monthly Active Users Anomaly Detection", and save it so we can always pick up our work later.

1. Next, lets prepare the data we'll need for this. We're wanting to calculate the Monthly Active Users per account, so lets create a Formula on the Salesforce Accounts table, meaning it'll be calculated and added to the data of each account.

   

   

   You may not have all of those other tables- that's OK, for this we only need to select "Salesforce Accounts".

2. Lets label this first formula, "MAU Over Last 3 Months". When you tab or click out of the label input, we'll automatically give this Formula a path, shown to the right. You can think of a path as the way to refer to this formula from other formulas or alerts.

   

   Adding this formula also brings up the Salesforce Accounts table. While we're wanting to calculate this formula per account, the actual data the formula will be using is from the User Activity table. When we added the User Activity table, on the Relationships page we automatically set up a relationship from User Activity to Account:

   

3. To bring in the User Activity table so we can see the data, lets first mention the relationship by the path name "user_activities" in the Expression field:

   

   As soon as you started typing it, we tried to bring up any related fields, formulas, or formula functions that include what you're currently typing. The first one will probably be user_activities, as shown above- feel free to hit "enter" if the one you want is highlighted, and it'll fill the rest in for you.

   That added the User Activity table on the right as a second tab. Lets click it and see what we get.

   

4. Since the User Activity table had its display field set to Username, that column is automatically added to the table. Lets add the other available columns so we can see what our data looks like.

   Hit the "Columns" button just above and to the left of the table, and add each of the columns by hitting the ➕ symbol next to them.

   

5. From here, we can explore the data however we like. However, we know we are wanting to calculate MAU Over Last 3 Months per account, so lets start by Grouping by "Salesforce Account ID". Hit the "Group" button above the table, click "Add Group", then click into the shaded text area to see a list of available columns.

   

   Select "Salesforce Account ID".

   <aside> 📌 You can also type into any box like this to search for columns, if the table you're working on has too many columns to simply scroll through!

   </aside>

6. This'll change the table to showing us one line per Salesforce Account ID, with a dropdown button to reveal all of the User Activity for that account.

Seeing all this data makes it easier to understand what we'll want to do in our formula. If you have any experience writing Excel formulas, you'll feel right at home here. If not, lets learn how!