Microsoft_Office_Excel_(2019–present)

Excel Project:

Mopar (Stellantis Group)

Detailed information about my role to analyze Mopar marketing data

About Project

As a part of marketing campaigns, Mopar (Stellantis Group) needed to have their weekly report about their marketing performances. As the data Analyst I had the responsibility of providing whatever the presenter needs in terms of graphs, data and information weekly. This includes extracting data from Adobe Analytics, data cleaning, validations, analyze and visualization of the big data. As decided by the company, excel was the chosen instrument for tackling the challenge. 

Project Details

My supervisor (at the time) reference on my job

For a couple of years, I had the pleasure of working with Navid on a Big Data project related to Mopar (Stellantis Group). Despite initially having limited information available, Navid threw out his heart and effort on this project and embraced the challenge with enthusiasm.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

For a couple of years, I had the pleasure of working with Navid on a Big Data project related to Mopar (Stellantis Group). The activity involved extracting data from Adobe Analytics and other sources, normalizing and aggregating them, and deriving information and insights. Despite initially having limited information available, Navid threw out his heart and effort on this project and embraced the challenge with enthusiasm. He managed to navigate through the vast amount of data to analyze and consistently showed a proactive approach in selecting tools and solutions to use. Thanks to his contribution, data visualization and business intelligence tools such as Google Looker Studio were introduced in the company, subsequently becoming a standard for our clients.

Throughout our collaboration, Navid has always been serious and reliable, and his support was essential in the success of the project.

Per un paio d’anni ho avuto il piacere di lavorare con Navid su un progetto di Big Data relativo a Mopar (Gruppo Stellantis).
L’attività consisteva nel prelevare dati da Adobe Analytics e altre fonti, normalizzarli, aggregarli ed estrarne informazioni e insights.
Nonostante inizialmente non avessimo molte informazioni a disposizione, Navid ha buttato il cuore oltre l’ostacolo con entusiasmo, è riuscito ad orientarsi tra l’imponente mole di dati da analizzare e si è dimostrato sempre molto propositivo nella scelta di strumenti e soluzioni da utilizzare: grazie al suo contributo, sono stati introdotti in azienda strumenti di data visualization e business intelligence come Google Looker Studio, successivamente diventati uno standard per i nostri clienti.

Durante la nostra collaborazione Navid è stato sempre serio e affidabile, e il suo supporto determinante per il successo del progetto.

Disclaimer

Since this project is a professional project and the data is confidential, all the numbers and the data you see in the files and screenshots are random and changed. Due to privacy of the client, I can't provide the real data, but you can have a clear idea about the size and difficulty of the project

Project Steps & Details

Adobe-Analytics

Data Extraction

Adobe Analytics

Every week we had to extract the data from Adobe Analytics. Unfortunately using Adobe Analytics, we can’t access the data like a database. After several proposals we made a general filter to extract the data as a unstructured and not cleaned CSV file. This CSV file was our starting point for the project. You can download a modified input sample below. 

Data Preparation

Microsoft Excel

As a company decision, I had to do the job using Microsoft excel for the rest of the steps. So, for data preparation, I had to restructure the data. The result of this step was an excel tab with more than 220 columns and 240 rows (about 55,000 cells). The goal of this file was just to create a structured data which can be used in another file for data cleaning and analyzing.
you can download a sample file below 

Microsoft_Office_Excel_(2019–present)
Microsoft_Office_Excel_(2019–present)

Data Analysis

Microsoft Excel

For analysis of the data and reduce the complication of the work, The results are analyzed in three different tabs: overall_sum, overall_sum_paid, summary. These tables are all calculated based on weekly input of the data. Every week’s cleaned data is saved as a tab in this file so not only we could access all previous weeks, but also we could have comparison and cumulative data in one single file. I should mention that these tables are still too big for presentations (from 400 to 50,000 cells). We had made other tabs specifically for the data visualizations and reports.
You can download a sample file below (Orange tabs are dedicated to data analysis)

Data Visualization

Microsoft Excel

At the end we prepared the data for the presenter. 5 different tabs including previous weeks data and sing week data were produced and my colleague just needed to copy the numbers to his presentation. Since we had all the data in the file, he had a backup of the data with him during the presentation and he could answer all the questions easily.
You can download a sample file below (Blue tabs are dedicated to data Visualization)

Microsoft_Office_Excel_(2019–present)

Takeaways

The most challenging part of this project was the size of it and dealing with unstructured data. Debugging and testing the data was time consuming too and I upscaled my Excel skills as much as my view about the importance of cleaning and structuring the data.

Contact Me

Feel free to contact me for job and projects proposals

Call me at

+39 320 805 5818

Email me

contact@navidbarati.com

Visit me at

10/27 Via Veglia, Turin, Italy

© 2023 Navid Barati – All rights reserved.