Data Analysis for E-commerce Expansion Strategy

This is an Excel workbook with multiple sheets, including 'Brief' & 'Raw Data'. The 'Overview' section contains a text description of a data analysis project for an e-commerce company. It outlines the scope of work, including data migration, CRM system use, and sales campaign analysis. The task involves analyzing a sample of 15,000 entries to create a high-level PowerPoint presentation. A map of the UK is displayed, indicating a focus on regional data analysis.

These screenshots show a spreadsheet from Excel titled 'Raw Data'. The data includes transaction IDs, dates, values, items, volume, loyalty, and region among other fields. Preparing the data for analysis, for trend detection and to analyze the effectiveness of sales campaigns.

Deleted TRANS_DATE Column because it was empty & we have a valid date.

Deleted NO_OF_ITEMS Column It's filled with 0s and blanks.

This screenshot is from Tableau Public, where the data from the 'Raw Edited' Excel sheet has been imported. The interface shows that no sheets or visualizations have been created yet, but the data is ready for analysis.

Merged the ITEM_VOL Column & the ITEM_VOLUME Column. Upon further inspection I noticed both columns were capturing the same entries but were not aligned. This is likely due to the migration of historical data over to a new CRM system at the end of February. A unified column was created (ITEM_VOL2) ensuring that each data entry is populated with a valid value from either of the original columns Using the ISBLANK Formula: =IF(ISBLANK(E2), Q2, E2). Deleted the original ITEM_VOL Column & the ITEM_VOLUME Column as the data was now uniformly captured in the new ITEM_VOL2 Column.

This line chart shows monthly sales for London (GL) and the West Midlands (WM), highlighting the impact of targeted promotions. August shows a peak, indicating successful campaign results. This analysis is crucial for understanding regional sales performance and the effectiveness of marketing efforts.

A bar chart titled "SALES BY REGION" showcasing sales figures across different UK regions. It highlights that the North West region, where the company was originally launched, has the highest sales. The accompanying note specifies targeted promotions in London and the West Midlands as influential factors. This visualization demonstrates sales distribution analysis and regional market impact.

A line chart titled "SALES TREND" displaying monthly sales data across a year. Peaks in April and July indicate the success of the "Spring Clean Sale" and "Summer Sale" respectively. The chart visually communicates the effect of seasonal sales campaigns on overall revenue, showcasing trend analysis.

A bar chart comparing the total income from loyalty and non-loyalty customers. The chart shows a slight advantage in income from non-loyalty customers. This insight could be critical for strategizing customer retention programs and loyalty benefits.

A pie chart representing the percentage share of total income between loyalty and non-loyalty customers. The chart indicates a nearly equal distribution, implying a balanced revenue contribution from both segments.

A composite chart showing both bar and pie charts. The bar chart compares the total income of loyalty and non-loyalty customers, while the pie chart shows the proportion of total customers in each segment. An additional component provides the average spend of customers in each group, offering a clear depiction of customer value.

An overview slide from a presentation, listing the sections covered in the analysis. It includes objectives, insights from sales and loyalty program analysis, regional analysis, and recommendations. This slide sets the stage for the presentation, outlining the comprehensive analysis approach taken.

A slide detailing the objectives of the analysis, emphasizing the utilization of data for informed business decision-making, understanding sales trends, and campaign impacts. It concludes with a focus on strategy recommendations for the upcoming year.

A slide titled "Sales Trend Analysis" with detailed points highlighting the impact of two major sales campaigns. This slide underlines the analytical findings regarding sales trends and their correlation with promotional events.

A slide titled "Insights from Sales Trend Analysis" summarizing key observations from the sales data. It includes the significant influence of the "Spring Clean Sale" and "Summer Sale," and notes the trend of sales dropping after targeted campaigns.

A slide from a presentation on "Loyalty Program Analysis" that combines quantitative and proportional data visualizations. It shows the income generated from loyalty versus non-loyalty customers and highlights the average spending behavior of each group. These descriptions reflect the data-driven decision-making process and highlight the analytical skills employed to derive meaningful insights from sales data. In your portfolio, they would demonstrate your proficiency in data visualization and your ability to translate complex data into actionable business intelligence.

A PowerPoint slide titled "Insights from Loyalty Program Analysis" presents key findings from customer behavior data. It notes that loyalty customers spend slightly more on average than non-loyalty customers and that the latter group constitutes a larger percentage of the customer base and generates more revenue. This insight can inform customer retention strategies and loyalty program adjustments.

This slide presents a bar chart titled "SALES BY REGION," offering a visual comparison of sales performance across various UK regions. The North West region, the company's launch location, shows the highest sales, with targeted promotions in London and the West Midlands significantly influencing sales figures. This demonstrates the regional distribution of sales and the effectiveness of targeted marketing.

A slide titled "Insights from Regional Analysis" provides conclusions drawn from sales data, pointing out that the North West, London, and the West Midlands were the top income-generating regions, likely influenced by targeted promotions. It suggests that these regions could be a focus for future marketing campaigns.

The "Targeted Promotion Analysis" slide compares the monthly sales of London and the West Midlands, with a noticeable spike in August due to successful promotions. The chart visualizes the direct impact of marketing efforts on sales, with a noted drop in sales following the promotion month, indicating a short-term boost in customer purchases.

This slide outlines "Insights from Targeted Promotion Analysis," highlighting that the promotion budget in August resulted in the highest sales of the year for London and the West Midlands, but was followed by a significant drop in subsequent months. This underscores the importance of understanding the long-term effects of promotions on sales.

The "Conclusion & Recommendations" slide summarizes the analysis, suggesting improvements in data quality and organization, reflecting on the success of the sales campaigns, and recommending a review of the loyalty program due to greater revenue from non-loyalty members. It also advises focusing on the most successful regions for targeted marketing.

This opening slide titled "INITIAL ANALYSIS OF E-COMMERCE UK COMPANY" sets the stage for the presentation. It likely includes a map highlighting different regions in the UK, indicating that the analysis may cover geographical sales distribution or regional market performance. These descriptions should provide a comprehensive summary of your analytical work and presentation content, showcasing your ability to derive insights from data and communicate them effectively.