The analysis of the impact of discounts on customer behavior reveals that while these incentives drive revenue through repeat purchases, they also significantly affect profitability. Based on the findings, I recommend two potential solutions for moving forward:
• Insight: 10% off and 15% off coupons appear to be more profitable out of all the coupon types, in terms of profit and number of coupon usage.
• Recommendations: As lower value coupons are desirable, we recommend scraping 10% coupons and keeping 15% coupons with £30 order value threshold
• Financial impact: Adding a threshold of £30 for coupon usage, 15% coupons could potentially bring in £15000, (10% coupons would only bring in an additional ~£3000). With removing 10% coupons completely we could recoup £43000.
• Insights: Google is performing better than all other channels in terms of number of coupon usage, CLV, acquiring new customers, revenue and ROAS.
• Recommendations: Based on this, we recommend optimising our investments in Google to improve our profitability while maintaining customer engagement. Shift some budget to google channel.
• Financial impact: by using Keyword Optimization, the project net profit of £100K could be achieved.
By implementing either of these solutions, the company can better manage the trade-off between driving revenue through repeat purchases and maintaining profitability. The recommended adjustments aim to find a middle ground that sustains customer engagement without compromising the company's financial health.
Customer retention has been a big issue for this in-house e-commerce company for the past two years. Stakeholders and management have observed that most customers purchase once and do not return. Given that retaining existing customers is more cost-effective than acquiring new ones, the company has decided to roll out coupons and different types of discounts. However, this marketing strategy is impacting profit negatively and is not sustainable. Considering this, there is need to improve coupon usage and explore alternative strategies to address this challenge.
The question is what should we do? How can we optimise our marketing budget to provide increased customer value while improving profitability?
The aim is to answer the following questions:
• What characterizes the behavior of customers in different segment?
• How profitable are the digital marketing channels
• What is our most popular marketing channel and medium
• Do the discounts lead to increased overall spending or just reduced margins?
• How engaged are the customers for each marketing channel and medium
• Are there significant differences in engagement metrics (e.g., repeat purchases, time spent on the website, interaction with marketing campaigns) for each marketing channel and medium
The primary objective is to identify specific things that can be done to improve profitability. By the end of this project, the company expects to have a clear understanding of:
• The effectiveness of discounts in acquiring customers and improving customer retention.
• Key behavioural triggers and characteristics of retained customers.
• The overall profitability of each marketing channel.
Dataset Size: Over 2 billion rows of data pipelines
Source: Google BigQuery
• BigQuery (database)
• SQL
• Power BI
• Google Sheets
• Excel
• Python
• Utilized SQL to remove duplicates
• Handled null values.
• Ensured data consistency and accuracy.
• Removed irrelevant data from the analysis.
• Exported cleaned and aggregated data to Google Sheets for initial analysis. • Created powerful visuals and interactive dashboards in Power BI to visualize key metrics and trends.
• Coupons are driving net profit growth.
• 10% off and 15% off coupons are more profitable out of all the coupon types as they bring in the highest transaction revenue and net profit.
• Google has sustained strong performance throughout, characterized by notable improvements in Return on Ad Spend (ROAS), number of customers, revenue and coupon usage.
• T-shirts reign in popularity through Google channel.
• Insight: 10% off and 15% off coupons appear to be more profitable out of all the coupon types, in terms of profit and number of coupon usage.
• Recommendations: As lower value coupons are desirable, we recommend scraping 10% coupons and keeping 15% coupons with £30 order value threshold
• Financial impact: Adding a threshold of £30 for coupon usage, 15% coupons could potentially bring in £15000, (10% coupons would only bring in an additional ~£3000). With removing 10% coupons completely we could recoup £43000.
• Insights: Google is performing better than all other channels in terms of number of coupon usage, CLV, acquiring new customers, revenue and ROAS.
• Recommendations: Based on this, we recommend optimising our investments in Google to improve our profitability while maintaining customer engagement. Shift some budget to google channel.
• Financial impact: by using Keyword Optimization, the project net profit of £100K could be achieved.
By implementing either of these solutions, the company can better manage the trade-off between driving revenue through repeat purchases and maintaining profitability. The recommended adjustments aim to find a middle ground that sustains customer engagement without compromising the company's financial health.
This project demonstrated the significant impact of data-driven decisions on business performance. By using data analytics tools and techniques, I provided actionable insights, recommendations, and detailed the financial impacts of these recommendations, which were then presented to stakeholders.
SELECT
coupon_type,
COUNT(coupon_type) AS n_coupons,
SUM(CASE WHEN coupon_type = '10% OFF' AND transaction_revenue <= 30.0 THEN 1
WHEN coupon_type = '15% OFF' AND transaction_revenue <= 30.0 THEN 1
END) AS n_coupons_30_cutoff,
ROUND(SUM(CASE WHEN coupon_type = '10% OFF' THEN transaction_revenue/(1-0.10)
WHEN coupon_type = '15% OFF' THEN transaction_revenue/(1-0.15)
END),2) AS original_revenue,
ROUND(SUM(transaction_revenue),2) AS discounted_revenue,
ROUND(SUM(CASE
END),2) AS additional_revenue_30_cutoff,
FROM prism-insights.red_team_mc1.transactions_table
WHERE coupon_type IN ('10% OFF','15% OFF')
GROUP BY 1
ORDER BY 1
net_profit = IF(transactions_table[return_status] = "Refund",
transactions_table[transaction_profit] - transactions_table[item_quantity]*transactions_table[item_price],
transactions_table[transaction_profit])
discount_usuage = SWITCH(transactions_table[coupon_type],
"00% OFF", transactions_table[Revenue]*0.00,
"10% OFF", transactions_table[Revenue]*0.10,
"15% OFF", transactions_table[Revenue]*0.15,
"20% OFF", transactions_table[Revenue]*0.20,
"25% OFF", transactions_table[Revenue]*0.25,
"30% OFF", transactions_table[Revenue]*0.30,
"35% OFF", transactions_table[Revenue]*0.35,
"40% OFF", transactions_table[Revenue]*0.40,
"45% OFF", transactions_table[Revenue]*0.45,
"50% OFF", transactions_table[Revenue]*0.50)