Sign in or start a free trial to avail of this feature.
2. Introducing the Data
The data for the course is contained in an Excel file containing three tables of data. We’ll explain the dataset in detail and import it into Power BI Desktop in this lesson.
- There are three tables of data in the dataset for this course
- Customers: Contains information on each of the customers of the company, including their signup date, their location, and their sector
- Plans: Contains information on the different plans available, as well as their monthly prices per user for individuals and for business users
- Transactions: Each row of this table represents a monthly payment by an individual customer. This table records the plan the customer is subscribed to, the number of licenses, as well as their MRR for this month and for the previous month
In the previous lesson we briefly ran through the course outline. In this lesson we'll take a closer look at the dataset that we'll use in our case study.
For the purposes of this course the data has been put into an Excel file containing three tables.
The first sheet contains the customers table. This table contains information on each of the customers, including a unique ID.
The location of each customer is recorded in the location column with GB representing Great Britain, IE representing Ireland, and US representing the United States.
Note that these location identifiers are also used as the first two characters for each customer ID.
As you might expect the signup date field represents the day the customer joined the service.
This field runs from 2013 to 2017.
The sector tells us what industry the company works in, while the type of customer field indicates whether the customer is a business or an individual consumer.
Note that all consumers belong to the individual sector.
Let's move over to the plans table.
From here we can see that the company offers four different plans: basic, standard, advanced, and enterprise.
The table also indicates the monthly price of each plan.
Notice that the individual users are charged a higher price for each of the first three plans, and the enterprise plan is only available to companies.
Finally, let's look at the transactions table.
Note that although the company has been running since 2013 we only have transaction data for 2016 and 2017.
Each row of this table represents a monthly subscription payment for any specific customer.
Each transaction includes information on the customer involved, the number of licenses they've purchased, and their plan.
This information determines the monthly subscription payment, which is equivalent to monthly recurring revenue, or MRR.
We also record the previous month's MRR and the change in MRR in each month.
The tenure month column indicates the number of months a customer has subscribed for. It will be one in the month of their first payment, two in the next month, and so on.
The key column in this table is the MRR category column. This indicates if the customer has changed their subscription in this transaction.
The most common value is no change, where the customers retain the same subscription as the previous month. Churn represents a customer who has canceled their subscription and left the company.
In this case MRR drops to zero, and the customer does not appear again in the dataset.
New represents a customer who has signed up in the previous month and is making their first payment.
Expansion represents a customer who's MRR is increasing either because they've added more users or they've upgraded to a more expensive plan.
Contraction represents a company who has reduced the number of licenses or downgraded to a cheaper plan.
Now that we have a better understanding of our dataset we'll import it into Power BI.
We'll start with a blank report, navigate to the home tab, select get data, and then Excel.
We'll navigate to the relevant file and select it.
We'll select all three tables, and click load to add all three sheets of data to our data model.
Before we start analyzing the data let's navigate to our relationship view to ensure the appropriate relationships have been detected.
We can see that the customers table and the transactions table are related.
If we hover over the relationship we see that the customer ID field is relating the two tables. However, no relationship has been created between the two transactions table and the plans table. The plan field in the transactions table corresponds to the plan number field from the plans table.
Let's create this relationship by clicking plan and dragging to the plan number field.
Now that our relationships are in order we'll stop the lesson. In the next lesson we'll start our analysis by creating a few charts that give us a better understanding of the dataset structure.