Methodology: 

• Data Description: 
  • Sources – Provided by 2Market in three files 
  • Data collection methods – not available. 
  • Tools and techniques used for analysis: 
    • MsExcel for initial cleaning and exploratory analysis, pgAdmin (SQL) for deeper insights into product popularity and advertising effectiveness, and Tableau for comprehensive dashboard presentation.
  • Brief data overview: 
    • Demographics: customers year of birth, income, marital status, geographic location, the number of children/teens.
    • Purchase information: Various product categories and marketing insights for Bulk mail, Twitter, Instagram, Facebook and Brochures, discounts, number of purchases from website or instore. #
  • Assumptions, variables and data cleaning steps – see details in Appendix 3 and 4. 
  • Data cleaning in Excel: 
    • The data was cleaned by adhering to the six principles of data quality as per the Data Management Association’s (DAMA) Data Management Body of Knowledge (DMBOK) framework. These included data accuracy, completeness, data validity, consistency, uniqueness and timeliness. 
    • The data cleaning process began with importing the CSV files into Excel. Initial steps included checking for missing values, correcting data entry errors, and standardizing formats. Outliers were identified using the IQR method, and appropriate decisions were made on whether to include or exclude these values based on their impact on the analysis. Data was then structured into a clean format for analysis. 

Analysis and Results: 

• Ms Excel Analysis: 
  • Exploratory analysis involved calculating descriptive statistics and using pivot tables to identify patterns in customer demographics and purchase behaviour. This preliminary analysis provided a foundation for deeper insights. 
  • Results of this can be found under the analysis files and in Appendix 5. In summary, There was data available for 2216 customer purchases, the average age of customers is 54 years, the average income of customers is $ 52,247. The highest number of customers have a salary of £30-39.9K. 39% of customers are married, 50% of customers have undergraduate level of education. This demographic insight suggests targeted marketing strategies should focus on middle-aged, higher income segments. 
• pgAdmin (SQL) Analysis: 
  • Data was imported into pgAdmin for advanced querying. SQL was used to analyse product popularity across different demographics and assess the effectiveness of various marketing channels. Complex queries were written to join tables, filter data, and aggregate results, providing a nuanced understanding of the data. 
  • The most popular product type is Alcoholic beverages and consistent across customer demographics. The outlier values does not impact on these results. Therefore in the Tableau visualization all sales data is displayed. However, additional filters could be applied depending on stakeholder preferences to show outlier values. This trend indicates a potential for expanding the range of alcoholic products and related promotions. 
  • Advertisement effectiveness varies across customer demographics: Twitter has the most conversions, Instagram leads in average sales. There were 87 customers, who was influenced by more than one platform. 
  • The SQL syntax can be found in Appendix 6 and the results tables in Appendix 7. 
• Tableau Insights: 
  • Comprehensive view of demographics, sales, and marketing effectiveness. Further detailed insights can be achieved through application of the filters, such as looking at the data for specific time or customer demographic. For additional details refer to the presentation. 
  • Detailed rationale for dashboard design can be found in Appendix 8. 
  • Trends: Highest sales were seen from married, undergraduate-educated customers, Spain has the highest sales volume in terms of geographic area. A noticeable decline in sales was observed over the analysed period, despite a stable customer base. This suggests potential issues with customer retention or market saturation, necessitating strategic adjustments to stimulate growth. 
  • Marketing Insights: Higher average sales from returning customers. Advert channel uptake is low but results in higher average sales per purchase. These insights highlight the importance of leveraging social media platforms more effectively, with tailored strategies for each channel. 
• Challenges and Rationale: 
  • Addressing inconsistent data formats required meticulous cleaning in Excel. Ensuring optimized performance of SQL queries was achieved by indexing key columns and employing efficient joins. The strategic use of Excel facilitated initial data exploration and cleaning, SQL enabled robust data manipulation and querying, and Tableau was utilized for comprehensive data visualization and dashboard development.