Sales data modelling and interactive visualisations of an E-Commerce Company

{ 11 Comments }

In this workbook, I have Sales data of an E-Commerce Company for 3 months.  The typical columns in the base data are:

1. Order Date/Time
2. City to which orders were shipped
3. Order Number
4. Payment Type i.e. Cash on delivery, Net Banking, EMI’s
5. Order Status i.e. Delivered or cancelled
6. SKU’s which the ordered items fall into
7. Products which the ordered SKU’s fall into
8. Categories which the ordered products fall into

Given this simple tabular representation, one may want to analyse and visualize this dataset from multiple perspectives based on user selections, such as

“What was the revenue earned from the Top 5 products in the A100 category in April for orders shipped to New Delhi?”

In this query framed above, the end user should have the leeway to select any/all of the underlined facets.  So one can either choose revenue earned or Number of orders.  Likewise, one can either select Top 5 products or Top 15 products/Top 5 SKU’s etc.

With relative ease, one should also be able to “Perform an affiliate analysis” showing which categories are ordered together (to study affiliations).  Please review this post for an independent discussion on “Affinity Analysis”.

Furthermore, one should be able to perform a free form timeline search such as  – “I would like to study growth in Total revenue of March 2-8 2015 over Feb 1-4 2015”

You may download the workbook from the link shared above.

You may watch similar videos showcasing the capabilities of Business Intelligence in MS Excel:

1. Analyse Sales data of a Beverage Company
2.
Analyse Training data of a Company

Here’s a video showing the capabilities of this Sales data model

You may also watch this short video to see how I visualized the revenue flow from Categories to Shipping cities during different Order periods using Custom visuals available in PowerBI desktop.

Please feel free to download the PowerBI desktop workbook of the video shown above from here.

For a detailed overview of Sankey diagrams (a Custom visual available in PowerBI desktop), you may refer to my Blog article here.

Another great Custom visual (Sand Dance) which allows data discovery has been shown at this link.  At that link, you will also be able to see how I queried the underlying dataset using “Natural Language”.

Leave a Comment

Your email address will not be published.

*

  • Super Ashish,

    We have discussed this over and over again. The 3 musketeers (PP, PV, PQ) are going to make a killing in the local BI space.

    Although I had one specific question. Is there are way to remotely manage the new data coming in ? I know of macro driven ways to keep adding new data but could this be done automatically through the PowerBI suite?

    Thanks
    Chandeep

    • Hi,

      You are welcome. Since your data structure would be different from mine, you just cannot add your own data. You will have to recreate the model for which you must learn to use the PowerPivot tool.

  • Do you have any video posted on preapring a sales forecast model for an Ecommerce company with similar dataset ? Sales and Stock Forecast for a different industry would also work as the forecasting model would more or less remain the smae. Thanks a lot for making these available for free, this one was pretty helpful too, athough it was posted 4 years back.

  • Hello Asish,
    I hope you would be doing great. I need little bit favour. Would it be ok if you can give a session to me on how You performed all the data modeling on data with some dummy data.
    Please let me know

    • Hi,

      I am fine and hope you are also keeping fit and healthy. I conduct 1:1 online sessions on the DAX formula language/PowerBI Desktop. If you are interested, you may write in to me via the contact Me page of my website.