Please find the answers for Part 1 of 1 and full answer for 2 in this document
Refer to the excel sheet for Part 2 of 1 which are 1B1,1B2,1B3, and 1B4 and 3rd question
1. Part 1: Dataset Deep Dive Dataset: Download the dataset provided:
Pilgrim_BI_Assignment_Dataset.csv
A. Write clean SQL queries for the following two questions. Assume a table named `dataset`
with the column names in the above file. You do not need to execute the queries - we are
evaluating your logic & approach.
1. What percentage of customers are repeat buyers? - Identify customers with more than
one order. - Calculate % of total customers who are repeat buyers.
Ans:
Repeat buyers
Select customerid from data groupby customerid having count(orderid) >1
% Repeat buyers
Select count(distinct case when order_count > 1 then customerid end)*100/
Count(distinct Customerid)) as repeatbuyers% from
(select customerid, count(orderid) as order_count from data groupby customerid) as
customerorders
2. Which product or product-variant combinations generate the most revenue? - Identify top
5 product + variant combinations by total revenue.
Top 5 Revenue
Select product,variant,
Sum(revenue) as total_revenue from data
Groupby product, variant
Orderby total_revenue desc
Limit 5
B. Answer the following questions. You may use any tool (Excel, Google Sheets, Looker
Studio, etc.) 1. Which campaigns and platforms are the most cost-efficient? 2. Does longer
delivery time lead to lower customer satisfaction? 3. What are the top 3 product preferences
by gender? - (Bonus: Create a dashboard that selects the gender) 4. What were your top 3
insights? Based on your findings, what 2–3 actions would you recommend?
Find answers for Part B in the excel sheet
Part 2: We sell our products across multiple channels — including marketplaces (Amazon,
Nykaa, Flipkart, Blinkit), offline retail (general beauty stores, EBOs, DMart, etc.), and our own
website and app. Our traffic is driven through various marketing channels such as Meta, Google
(including YouTube), marketplaces, and organic sources.
Question: What, in your view, is the overall impact of discounts and offers on the business
P&L? How would you go about designing an optimal discounting strategy for Pilgrim? Please
outline your approach, including the thought process and framework. Feel free to use a
dummy dataset or illustrative model to explain your answer
Answer:
Discounts help to push volumes but yeah they really eat into the margins. So if we run too many
offers, we might see more units sold, but actually profit might drop, especially if everyone just
waits for the next deal. I think the main thing is how much extra sales we get because of the
discount and does that actually compensate for the money lost per unit. Sometimes we get a
spike but then realise most people were going to buy anyway or they don’t come back if there’s
no offer.
I’d probably start by splitting up sales by channel, like Amazon, D2C, offline etc, since each has
their own cost and customer type. Then I’ll check how discounts impact new buyers vs old ones,
and also see if discounting on say Amazon is actually pulling buyers away from our own website.
Maybe a dummy test, like without offer we sell 1000 units at 500 each, and with a 20% discount
we sell 1500 at 400. Margin drops from 200 to 100 per unit, so overall we make 2L vs 1.5L. Looks
like more sales but actually less profit.
To really know, I’d do a test where some people get a discount and some don’t, see who comes
back, who just jumps to the next brand. Don’t want to end up just training people to only shop
when there’s a deal. Also, I’d be careful with offers on our own site, maybe do loyalty points or
bundle deals instead of deep discounts like the marketplaces. Those should be for bigger events
or new launches, not all the time.
Need to keep watching all the numbers, see which kind of discount is actually bringing in new
loyal customers or just increasing one-time sales. Honestly, there’s no fixed answer but I’d keep
changing it based on what’s working. Main thing is not to blindly do offers everywhere, just
because others are.
Would also like to look into price elasticity with respect to discounts for customers