TheLook - Ecommerce
1. The Data
TheLook Ecommerce is a fictional site, with programmatically generated data available for query within Google BigQuery. For this small project, I have made a topline Power BI report to show some example visualisations that could be available from the data. The data itself required some modelling, transformations, and measure creation prior to visualisation.
The purpose of this project is to provide an example of how proper analytics & visualisation can bring your business to life. This project predominately uses BigQuery and Power BI.
The data itself is relatively clean, with a dataset comprising of 7 tables, as below:
distibution_centers: A table of the 10 distribution centers with one row per centre.
events: A table of online events, with multiple events per session and one row per event
inventory_items: A tanle of stock items, including purchase date, sale price, sale date
order_items: A table of all items that have been ordered, multiple rows per order
orders: A table with one row per order, topline order information
products: A table of products, essentially the dimension table for inventory_items
users: A table of users, maps to events with dimensions around each user
2. First steps in Power BI
First things first, I connected the data using Power BI's native Google BigQuery connector. Power BI has many official connectors available, meaning connecting to most traditional data sources is relatively straight forward.
Once connected, I then has to create the relationships between the different data sets. If this were my own managed data sets, I would be inclined to do more of this modelling work "upstream" in BigQuery using SQL. Modelling in Power BI actually has some usability benefits when it comes to cross filtering and building visuals on the fly, and is perfectly fine for a small project like this.
Note the model on the right - I have incorporated a separate date table built in DAX, which is highly recommended for all time series visualisations. Some of the relationships required calculated fields in order to function correctly, which is part and parcel of creating relationships from tables that are from likely different sources.
One other problem is no real discirnable connection between events (such as online purchases) and orders. Meaning it was difficult to attribute back an individual order to a particular source (you can however see the original source of the customer in "users"). I attempted to get around this problem upstream in BigQuery, using an analytical "QUALIFY" function to return the next order ID immediately following "purchase" event and created the sessions_to_orders_table. Unfortunately, due to the data being manufactured rather than real, I could still only attribute 10% of orders back to a session even with this, but the exercise still stands that we can infer relationships in data with a bit of ingenuity!
3. Measures & Visualisation
Before visualising the data, I think it is important to write a full list of measures that are required to bring the visualisations to life. In this case, as it was only a small project, I decided to limit it to about 10 measures, with another 10 being comparison measures, or time period measures. I also like to group my measures into one table separate from the source files, for added usability for myself and stakeholders.
Finally, it is time for visualisation. For this project I split the report into 3 main areas, Topline metrics, Ecommerce (Site) metrics. and Product metrics. There are alot of insights to be gleaned outside of these 3 areas, but I think in reality you could have 5/6 pages about the site performance alone!
The final report can be found at the bottom of this page. I am also more than happy to share the .PBIX file with anyone who wants to take a look further at the modelling / measures / theme or data themselves, just click the "Let's Chat!" icon on the page.
