Analyze ecommerce data to help the stakeholder determine the marketing budget.
Stakeholder Question: The marketing budget is being set for next year. We would like to know the holiday
that brings in the most revenue. Please complete this task by the end of day tomorrow. The ecommerce sales data is provided.
Ecommerce data for December is not provided.
- Ecommerce Dataset: Will use the dates of sale, quantities and prices of items sold.
- U.S. Holiday Dates Dataset: Will use dates and names of U.S. Holidays
- Change date format to month/day/year (mm/dd/yyyy) and remove the time (hh:mm:ss) from the Holiday Ecommerce dataset
- Merge the Ecommerce Dataset and the U.S. Holiday Dates Dataset by using a Left Outer Join (Also known as a left inclusive join or left join)
- The left outer join will include all the data from the Ecommerce Dataset and only the matching data between the U.S. Holiday Dates and Ecommerce Datasets. Both datasets will be joined by the matching dates.
- The new table created by the join will be named the Analysis Table.
- Dates that are not holidays ("null" values) will be replaced with "Normal Day"
- Group same dates and holiday names on the Analysis Table
- Create two new columns, Total Revenue (sum of revenue) and Total Quantity (sum of quantity)
- Create a Pivot table with the average revenue and quantity for each holiday
- Analysis: The analysis shows that Thanksgiving Eve has the largest average revenue and item quantity sold when compared to the rest of the holidays.
- Recommendation: Dedicating a larger portion of the marketing budget during the lead up to Thanksgiving Eve may produce higher revenue during this period.