Section 1: SQL and Basic Data Interpretation / 

Logical Analysis 
We here provide you with 3 different tables: 
1.  users 
2.  credit_transactions 
3.  orders 

Schema Descriptions 
  
users:​ This table lists all of our user base 
1.  id – user id of the user 
2.  first_name 
3.  last_name 
4.  email 
5.  gender 
6.  created_at – signup date of the user 
  
​credit_transactions:​ This table lists the wallet credits transactions on our platform 
1.  transactionid – primary key for the table 
2.  shop101credit_id – unique credit id 
3.  amount – credits amount used in the transaction 
4.  user_id – user_id of the user 
5.  expiry_at – credit expiry timestamp in GMT 
6.  status  –  status  of  the  redemption  action  on  the  app  processing  –  we  are  processing  the 
voucher, will get activated once we send the same to the respective merchant 
a. earned – Credits granted to a user 
b. used– status when a user applies this credit while placing the order 
c.  refunded– Credits refunded back to wallet in case of failed transaction 
d. expired – Credits are expired due to exceeding expiry date. 
  
Eg.  I  have  x  credits  balance,  y  Shop101  credits  are  granted  to  me,  there  is  a  new  entry  in  the 
credit_transactions table. This starts with an ‘earned’ status in the table. 
If  a  user  applies  credits  while  placing  the  order  and order is placed with z Shop101 credits then, there is new 
entry  in  the  table  with  ‘used’  status.  If  for  some  reason,  transactions  fail,  and  credits  were  applied  before 
failure,  then  the new entry is created with ‘refunded’ status. If current_date exceeds expiry_at date then, new 
entry is created with ‘expired’ status. 
7.  created_at – the time when the entry is created in the table. 
  
orders :​ This table lists all the payment orders placed on the SHOP101 app. 
1.  id – primary key for the table 
2.  order_id – unique key everytime a user places a payment order on the platform 
3.  amount – Amount for which the payment order was placed. 
4.  user_id – user_id of the user 
5.  paymentstatus – status of the orders placed by the user 
a. b. success – payment done successfully. 
b. failed – status when a user keys in wrong OTP/password/CVV. 
Eg.  I  intiate  a  payment  on  the  SHOP101  platform,  there  is  a  new  entry  in  the  orders  table. The user if enters 
incorrect  CVV/password,  payment  status  updates  to  ‘failed’  status  and  if  correct,  updates  to  ‘completed’ 
status. 
6.  created_at – the time when the user places the order on the app. 
 

Analytics Task 
  
Your  task  is  to  analyse  this  response  data  and  provide  a  few  reports  based  on  the  questions  below.  For 
each report, please include SQL queries you may have run. All timestamps in the data set are in GMT. 
  
Your report can be in whatever form you like (ppt, google slides, PDF, word doc, etc.) 
  
1.  Create  a  base  fact  table,  following  columns  are  mandatory,  include  more  if  you  can  think  of 
any. Report all in IST for the ease of use for others. 
a. User_id 
b. First_name 
c.  Last_name 
d. Gender 
e.  Email 
f.  Signup date 
g. First payment date 
h.  First payment status 
i.  Last payment date 
j.  Last payment status 
k.  Total amount paid. 
l.  First payment amount 
m.​ ​Last payment amount 
2.  How many users have never paid on SHOP101 platform? 
3.  How many users have never used credits on SHOP101 platform? 
4.  Write a query to find the cumulative amount paid till each date on the SHOP101 platform. 
5.  Write  a  query  to find DAU/WAU and MAU for everyday. An active user is the one who has paid 
on the SHOP101 platform. 
a. DAU for a day is the count of users for that day. 
b. WAU for a day is the count of users in the last 7 days. 
c.  MAU for a day is the count of users in the last 30 days. 
6.  Write a query to find 2​nd​ Month (or M2) retention of the new users from their first order month. 
e.g.  If  100  users  have  placed  the  order  for  the  first  time  in  Jan  2020,  then  Jan  2020  is  the  start  month  for 
these  users.  If  out  of  these  100  users,  only  30  users  placed  the  order  in  Feb  2020 then M2 retention is 
30%. 

  
Section 2: Experiment Analysis  
The Acquisition Funnel 
It  all  starts  with  creation  of  digital  banners  to  be  displayed  on  various  websites.  These  display 
banners  are  produced  by  the  advertiser  based  on  their  communication  strategy,  business 
objectives  and  brand  vision.  These  banners  are  then  supplied  to  a  publisher  like  Facebook  (FB) 
which publishes them on their own web property – in this case Facebook.com. 

For  the  purpose  of  this  problem  let’s  assume  that  the  advertiser  is  an  online  social 
commerce  company  called  SHOP101  and  SHOP101  wants  to  advertise  with  FB  –  the  publisher.  FB 
has  an  inventory  of  ​impressions​.  An  impression  is  counted  every  time a FB webpage loads and the 
ad  appears  irrespective  of  the  user  seeing  the  ad  or  not.  Even  if  the  page  loads  10  times  and  the 
same  user  is  targeted with the ad, it is considered as 10 impressions. FB only has a finite “inventory” 
of  impressions.  But  it  doesn’t  charge  for  an  impression.  It  charges  an  advertiser  only  when  a  user 
clicks  on  the  ad.  For  obvious  reasons  FB  wants  to  promote  ads  that  have  a higher ratio of clicks to 
impressions.  This  metric  is  called  CTR  or  click through rate. For ads that have higher CTRs FB gives 
them  more  inventory and potentially charges a lower cost per click (CPC) depending on competition 
in  that  advertiser’s  space.  Vice-versa,  for  ads  that  have  lower  CTRs  FB  gives  them  less  inventory 
and will potentially charge a higher CPC. 

SHOP101  clearly  wants  more  inventory  at  a  low  CPC.  This  in  turn  means  the  SHOP101’s 
banner  ads  must  get  high  CTRs.  This  would  happen  when  a  lot  of  FB  users  find  the  SHOP101  ad 
interesting,  relevant  and  appealing. But SHOP101’s problem just starts here. A user clicking on the ad 
arrives  on  a  SHOP101  landing  webpage.  The  landing  webpage  elaborates  on  the  promise  that 
SHOP101  made  in  the  ad  to  the  user.  If  the  user  finds  this  elaboration  consistent  with  the  ad,  and 
relevant  and  exciting,  the  user  registers  on  SHOP101  by  providing  basic  information  like  email 
address,  year  of  birth  and  state  where  they  live.  After  registering,  the  user  sees  SHOP101’s  product 
offering.  If  the  user  has  a  good  experience  and likes the product he places the order. Depending on 
how much the user enjoys his experience as a customer he will choose to make additional purchases 
on the site in the following days and months. These customers drive SHOP101’s business. 
The  marketing  team  at  SHOP101 seeks to acquire as many paid users as possible at as low a 
cost  as  possible.  So,  the  Marketing  Director  at  SHOP101  wants  high  CTRs,  low  CPC,  and 
subsequently  a  high  ratio  of  registrations  to  clicks  (RTC  –  registrations  to  clicks)  and  a  high  ratio  of 
paid users to registered users (PTR – paid to registered). 

Please  study  the  following  data  on  SHOP101’s  acquisition  funnel  for  the  period  January  to 
August.  In  March  the  CEO  of  SHOP101  told  the  Marketing  Director  that  their  target  is  to  achieve 
between 8-10% growth in new paid user revenue from March to August. On budget the CEO said that 
the  Marketing  Director  could  increase  the  marketing  budget  by  between  6-7%  every  month.  The 
Marketing  Director  realized  that  given  this  limited  budget  increase,  a  simple  scaling  strategy  of 
buying  more  clicks  would  not  work.  She  just  did  not  have  the  budget  for  that.  So  she came up with 
an alternative strategy that she put into action starting April. 

The  CPC  (Cost  per  click)  and  RPU  (Revenue  per  user)  data  are  in  INR.  For  your  convenience, 
definitions are repeated here: 
 
1. CTR = Clicks/Impressions 
2. RTC = Registrations/Clicks 
3. PTR = Paid Users/Registrations 
 

Registrati Paid 
Month  Impressions  Clicks  CTR  CPC  RTC  PTR  RPU 
ons  Users 

Jan  54,000,000  152,532  0.28%  9.25  6,700  4.39%  153  2.28%  500 

Feb  57,000,000  154,003  0.27%  9.50  7,000  4.55%  160  2.29%  555 

Mar  60,000,000  175,000  0.29%  9.50  8,000  4.57%  182  2.28%  582 

Apr  50,000,000  169,000  0.34%  10.50  9,000  5.33%  225  2.50%  675 

May  48,000,000  172,000  0.36%  11.00  9,500  5.52%  245  2.58%  650 

Jun  48,000,000  175,000  0.36%  11.50  10,000  5.71%  276  2.76%  700 

Jul  52,000,000  225,000  0.43%  9.50  7,400  3.29%  225  3.04%  750 

Aug  48,000,000  183,000  0.38%  12.25  10,500  5.74%  300  2.86%  640 

Table 1 

1. Answer  the  following  questions  based  on  data  above.  Give  as  much  details  as  possible  while 
answering.   
a. What do you think this strategy was? 
b. Did the strategy work? 
c. What do you think happened in July? 
d. If,  based  on  this  data,  you  had  to  replicate  the  marketing  campaign  of  either  June  or 
August  for  the  next  12  months,  which  month  would  you choose? If you could ask for more 
data to make this decision, what data would you ask for? Explain. 

 
 
2. Now  let’s consider SHOP101’s broader monthly acquisition campaign (please ignore data 
from  question  1).  In  particular  the  table below shows the Acquisition Funnel for SHOP101 
for three different ad campaigns – Google Search, Google Display and Facebook. 
 

   CPC (Rs.)  Clicks  RTC  PTR 

Facebook  18  200,000  4%  7% 

Google Search  14  300,000  6%  15% 

Google Display  15  50,000  4%  7% 

In  addition  to  the  above three campaigns SHOP101 acquires customers from word of mouth 

a. How many resellers does SHOP101 acquire in a month? 

   Month 

   0  1  2  3  4 

For  each  of  the  3  ad 

For Word of Mouth  100%  60%  30%  20%  0% 

For Refer a Friend  100%  45%  20%  10%  0% 

  
The revenue per resellers in any month is Rs. 3,000. 
  
a. What is the average life of a reseller in months? 
b. What is the average lifetime value of a reseller? 
c. What is the average lifetime profit per resellers? 
d. Which ad campaign has the highest ROI? 
 
Assume  the  business  has  no  operating  costs  and  only  incurs  acquisition  costs  following  which 
active  resellers  generate  revenue  for  the  business.  Given  the  above  data  is  there  any  ad 
campaign that you think should be discontinued. Please explain your answer.