Objective

To develop a dynamic dashboard utilizing in house tools to view site sessions data for the Australian market.

Business Problem:

A global retailer has unified its ecommerce platforms across multiple brands and seeks to analyze the online sessions to determine the traffic each brand’s site is receiving.
This analysis also includes tracking the launch of Curve for Jacqui E, Just Jeans and Portmans.

Methods:

• Importing and cleaning data by removing MB sessions as they are only referenced on a Global scale.

• Manual unpivoting to restructure the dataset from a wide format to a long format, enabling easier analysis when leveraging pivot tables.

  • Retained consistent columns that would be utilized across all data.

  • Utilized INDEX and MOD functions manually to unpivot data.

• Organized the unpivoted data into a structured format that supports analysis and comparison.

  • Introduced Last Year’s data with adjustments on existing data.

  • Variance calculation to provide insights into performance trends.

• Created several pivot tables tailored to the specific chart and visualization requirements needed for the dashboard.

• Used small data approach alongside VLOOKUPs to ensure dynamic updates in response to incorporating slicers for smooth dynamic user interactions.

Conclusion:

This dashboard was developed by cleaning, restructuring, and analyzing data while leveraging Excel’s due to the unavailability of BI tools. By leveraging in-house tools and the team’s existing Excel proficiency, this approach was cost-effective and eliminated the need for additional training or software.