Using Dax, Power Query and Excel to build our business intelligence solution

In this article, I’ll explain in detail how I built our business intelligence reporting system from scratch, only a few weeks after I started as Deputy Manager for Birmingham City Student Homes. I’ll also explain the reasons behind this project and how we’re planning to expand on it going forward.

Why this project in the first place?

It’d probably be useful to start by explaining why I applied for this position in the first place.

Below is a screenshot of the Job Description, as it was advertised back in September 2020:

Reading this advert, I immediately knew that this position would provide a lot of creative freedom, and the possibility to bring new ideas to life.

From what I’ve been told retrospectively, the reason why I’ve been chosen over all the other applicants was my data-centric approach to business operations.

“This is music to my ear”.

I still recall my interviewer saying this to me, after I had explained how I managed to increase our viewing conversion rate from 30% to around 80% in just a few months at my last job.

There were looking for someone who not only had enough experience to manage and optimize the entire business workflow but could also create a data driven culture within the company.

For years, they had relied on anecdotal observations, subject matter expertise and intuition. And, although they knew that these qualities were needed, they also acknowledged the necessity of using data to assist in the decision-making process.

So, after a well-maneuvered interview, they decided that I would be the best person to bridge that gap.

Only a few weeks into the job, I set out to build our data and analytics reporting system from scratch.

Gathering business requirements

As with every data project I undertake, the first thing I did was to set up a meeting with all the decision makers and the future end-users to determine what functionalities would be needed, and how the system would be used.

After a few weekly meetings, here’s what I gathered:

  • Top management needed a self-service solution, with the possibility to create ad-hoc reports to support their recommendations during shareholders meeting.
  • I needed to create metrics and KPI’s to track our progress, better manage my staff, and develop and optimize marketing and advertising strategies.

The idea was to create a system simple and intuitive, so that it would be easier for end-users to adopt it.

Developing the data and analytics solution

At Birmingham City University Students’ Union, we only have a small budget for marketing. And, after nearly a year into a pandemic that negatively impacted our bottom line, spending money for new SAAS solutions was out of question. Therefore, I had to use what was already in place or find free tools on the internet.

Capturing the right data

From our weekly meetings within the team, we had identified 5 key information to be captured when collecting data from lead applicants:

  1. Applicant source (where did they hear from us)
  2. University Attended
  3. Year of study (i.e., 1st, 2nd, 3rd, postgrad)
  4. Living arrangements (student halls, Parents ‘place or private accommodation)
  5. Home or International student

These attributes will later be used for comparison purposes when we perform our metrics’ calculations.

The Challenges

On top of the limited – or the absence of – budget at my disposal, I also had to figure out a way to capture all the student-specific data I needed to build and develop the business intelligence solution.

To collect our data, we use AgentOs, a customer relationship management system. Predominantly built for managing residential letting agencies, it’s not fully adapted for the collection of student-specific information and the system only allows for a limited degree of customization.

Below is a screenshot of our data capture screen on AgentOs:

The form is customizable only in the tabs with a little arrow at the end of it (see image). And the form doesn’t allow us to collect all data specific to our needs.

And because my manager wanted to centralize most of our data collection to the same location, I had to find a way to collect the other data I needed on this form.

The Solution

For the first two attributes – Applicant Source and University Attended – I took advantage of the customizable functionality of tab 1 and 2. This allowed me to create custom drop-down list of attributes. The advantage of this method is that it prevents users to manually enter data, thus avoiding spelling errors.

Because customization was limited to only the first 2 tabs, I had to be a bit creative to collect data for Home or International Students, Year of Study and Living Arrangements using tab 3, 4 and 5 respectively, which all have unrelated titles.

Note: Tab 3 is customizable only to a certain degree, but it was enough to create an intuitive logic: Only applicant with United Kingdom as Nationality are classified Home Student. All other Nationalities are classified as International Students.

The result looks something like this:

Personal information – names, address etc – would also be entered but for the purpose of this article, I’ve focused only on the key attributes.

Because this process is not intuitive, I had to create a user guide, so that anyone interacting with the software could familiarise with the process.

Transforming the data

Collecting specific data on tabs with unrelated titles meant that, in its current form, our datasets was not intuitive, and the final report would confuse end users (how can someone guess that “Address Line 1 was actually used for collecting information on year of study?).

Moreover, data need to be entered manually on tab 4 and 5, so spelling errors are frequent.

Using Power Query, a tool that manages the ETL (Extract, Transform and Load) process, I’ve applied several transformation to the original data:

  • Removing unwanted columns
  • Changing column data types
  • Renaming columns (Address Line 1 to Year of Study and Applicant Status to University Attended)
  • Merging First Name and Surname
  • Using the Fuzzy Match function of Power Query to correct spelling errors on manually entered data

Creating the data model

Once the transformation was done, the next step was to load the data into Power Pivot, an Excel Add-ons, and develop a data model by creating relationships between the different tables.

My fact tables (tables usually containing transactional data) sit at the bottom while my dimension tables (used to store descriptive attributes) are positioned on top.

The reason for that is purely memo-technique: it’s to remember that filters (when we want to analyse our data by certain attributes) flow downstream.

So, for example, if I wanted to analyse our leads number by month, I would have to filter “lead_Fact” using the “month” field in the “Calendar_Lookup” table (a dimension table) and not the “Created Date” field inside the “Lead_Fact” Table.

Note: My dimension tables were all created using Excel spreadsheets.

Creating the measures

Before the reporting system could be used by end users – myself included – the last step was to create the different measures that would give us important insights about the business.

To do so, I used DAX – a programming language used for data analysis.

Here’s what the model looks like:

On the image above, you can see all the measures that have been created inside the LetAgreed_Fact table. They are represented with a little fx icon that you can see on the image above.

Here are some example of DAX formulas used to create these measures:

Total P12 Income

Total Let Agreed

Grand_Total Expected Income

% Grand_Total Icome

The result

Using Excel, Power Pivot, Power Query and Dax, I’ve managed to create a complete business intelligence solution for Birmingham City Student Homes.

The reporting system is now used to create dashboards and reports to monitor important metrics (Total Leads, Conversion Rate, Occupancy Rate, Total expected Income etc,) and KPI’s.

It also helps us in the development of our digital marketing strategy and our decision-making process.

Top management also uses the system to create ad-hoc reports to support their observation/recommendation during meetings with shareholders.

Final Thoughts

We’ve only scratched the surface of what’s possible to achieve . There are still many avenues I want to explore with my team, especially in terms of inbound marketing activities (SEO, Web Design, Content marketing).

I’m deeply convinced that, with the right dose of experience, subject matter expertise and data-driven capabilities, we can take the business, and the organisation as whole, to a whole new level.

Don't forget to share this post!

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright 2021 © All rights Reserved.