SQL Marketing Campaign Analysis

Question 1 - Data Exploration

Investigating the conversion rate for both the continues and categorical variables in the data set.The goal is to to stop which features are associated with higher conversion rate (note: conversion rate is the ratio between the number of sales to leads)

The continues variables:

SELECT y, 
ROUND(AVG(age),2) 'Average Age', 
ROUND(AVG(emp_var_rate),2) 'Average Employee Varation Rate',
ROUND(AVG(CPI_index),2) 'CPI index', 
ROUND(AVG(cons_conf_index),2) 'Consumer confidence index'
FROM bank_data
GROUP BY y

The categorical variables:

SELECT job, ROUND(AVG(y),2) 'Convertion Rate'
FROM bank_data
GROUP BY job
HAVING job != 'unknown'
ORDER BY 2 DESC



SELECT campaign, ROUND(AVG(y),2) 'Convertion Rate'
FROM bank_data
GROUP BY campaign
ORDER BY 1 DESC



SELECT contact, ROUND(AVG(y),2) 'Convertion Rate'
FROM bank_data
GROUP BY contact
ORDER BY 1 DESC



SELECT education, ROUND(AVG(y),2) 'Convertion Rate'
FROM bank_data
GROUP BY education
HAVING education != 'unknown'
ORDER BY 2 DESC



SELECT TOP 10 pdays, ROUND(AVG(y),2) 'Convertion Rate'
FROM bank_data
GROUP BY pdays
HAVING pdays != 0
ORDER BY 1 ASC

Overall, the analysis of the continues variables indicates that:

a) Conversion rate is not significantly effected by either of the categorical variables (age, employee variation rate, CPI index score, and consumer confidence.)

b) The campaign’s should on: (1) Focus on the students and illiterate population as these have the highest conversion rate. (2) Focus costumers that yielded a sale on previous campaigns. (3) Focus on customers who was has been contacted by the bank in the recent days.

Question 2 - Seasonal Effects

In this analysis I investigate the existence of seasonal effects in the campaign. To put more simply: whether the conversation rate was higher in a particular time period.

SELECT month,COUNT(*) as 'Sampels',ROUND(AVG(y),2)  as '% yes'
FROM bank_data
GROUP BY month
ORDER BY 3




SELECT day_of_week,COUNT(*) as 'Sampels', ROUND(AVG(y),2)  as '% yes'
FROM bank_data
GROUP BY day_of_week
ORDER BY 3

It appears that although conversion rate is stable across all sampled days of the weeks, conversion rate was high on the following months: September, October, November, and March. In addition,