Skip to content

Latest commit

 

History

History

BI

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
README.md
README.md
 
 
dataset.xlsx
dataset.xlsx
 
 

README.md

BI Developer - Challenge

How to participate

  1. Read the instructions carefully.
  2. When you're ready to start the test, send an email to [email protected] with the object BI Challenge BI Developer application by FirstName LastName, it'll be considered as the time your started working on the challenge.
  3. Once you're done with the challenge, reply again by email to notify us you're done.

Make sure you spare an average of 3 days, and a maximum of 8 days to work on the challenge.

Context

Your mission should you choose to accept it is to build a sales dashboard for 2 types of users using the data provided.

The data belongs to a sales platform where sales get submitted by agents, and managers need to keep track of clients, payments and policies etc.

The goal of this assignment is to exercise your BI abilities to use the data and extract the most out of it for decision makers. Knowledge on how to handle data will be essential to successfully completing this assignment.

Dataset

An excel file should be above called dataset.xlsx.

Leads definition

  • reference
  • status_id
  • user_id (belongs to n user).
  • client_id (belongs to n client).
  • claimable_type, claimable_id (polymorphic relationship with TypeALeads table)

TypeALeads definition

  • plan_id (has one plan).
  • paperwork_sent_at (timestamp when paperwork was sent to the client)

Plans definition

  • name
  • price (£)
  • start_date (policy start date)
  • end_date (policy end date)
  • length (in years)
  • type (How payments are sectioned off)

Payments definition

  • payment_date (When payment needs to be taken)
  • plan_id (parent plan)
  • paid (Whether payment is paid, not paid yet or failed)
  • amount (£)
  • order_number (Unique order number)
  • payment_type

Other tables should be self explanatory, do let us know if there is anything unclear.

Features

  • As an admin i need to have an automated process to automatically update the report with new data.
  • Given there is a process to clean data, data must be stored in a central data warehouse.
  • As a manager i need to see the performance (lead count) and revenue per agent.
  • As a manager i want to be able to able to see the revenue generated by each team.
  • As a manager need to see all the payments made, failed and upcoming during the next day, week, month, year.
  • Given that the payments data has future day payments, as a manager i need to see the prediction of the incoming revenue.
  • As an agent i need to see my individual revenue, number of sales made and number of upcoming potential revenue.
  • As a manager need to see the client that paperwork was sent to, and the ones still left.
  • As a manager i need to see the policies that will expire in next day, week, month, year.

Notes & Evaluation criteria 🚨

  • Accuracy to follow instructions.
  • The dashboards need to be embeddable.
  • Choice of technologies is open but MS Power BI is required.
  • How you will handle the data (data flow, data cleaning process, fact and dimension tables setup).
  • An explanation of the process of analyzing the data and building the report.
  • Bonus points for any additional reports you can create using the data.
  • Don't hack something quickly, take your time and craft something clean.

Questions?

If you have any questions, ask directly via email above or linkedIn.

Message will self destruct in 3, 2, 1 💥🧨 ! May the force be with you !! 🖖