sql assignment

Neighborhood Lead Scoring: Prompt    In April 2020, in response to the coronavirus pandemic Faire launched a new online marketplace  called ​Neighborhood​ that allowed consumers shop independent stores, discover unique  products, and support the Shop Local movement. When shelter-in-place began, we saw people  looking for new ways to connect with their favorite local shops online. Neighborhood enabled  customers to support the brick-and-mortar stores even when they weren’t able to visit them in  person.    A critical component of the Neighborhood strategy was to acquire and onboard the best  retailers to sell on Neighborhood, prioritize the Sales team’s time effectively, and ensure we had  the right number of Sales reps supporting the strategy.    Using the attached Excel file as well as the information provided in this document, please put  together a short presentation addressing the questions below.     The attached Excel file has the following data: 

● Retailer ID​: unique identifier for a single retailer on the Neighborhood marketplace  ● Estimated # brands carried​: # of brands the store carries (in total, not just counting any 

they sell on Neighborhood)  ● Store type​: The retailer’s primary category / vertical  ● Days since activation​: Number of days since the retailer was activated on Neighborhood.  ● # Active NBHD products​: Number of active products the retailer has for sale on 

Neighborhood  ● # NBHD orders​: Cumulative number of orders that retailer has received on Neighborhood  ● NBHD GMV​: Total cumulative gross merchandise volume that retailer has done through 

Neighborhood  ● eCommerce presence​: Flag for the retailer has an eCommerce store outside of 

Neighborhood  ● Website has "About Us" section​: Flag for whether the retailer has an “about us” section 

on their website  ● eCommerce platform​: The hosting platform used by the retailer  ● IG follower count​: Number of followers on the retailer’s Instagram profile  ● Facebook Likes​: Number of likes on the retailer’s Facebook page  ● Google reviews (number of ratings)​: Number of ratings the retailer has received on 

Google  ● Google reviews (avg. star rating)​: Average star rating the retailer has received on Google  ● Yelp reviews (number of ratings)​: Number of ratings the retailer has received on Yelp  ● Yelp reviews (avg. star rating)​: Average star rating the retailer has received on Yelp  ● Website quality​: Qualitative assessment of the retailer’s website quality  ● Faire GMV last 4 months​: Total gross merchandise volume of wholesale inventory that 

retailer has purchased on Faire.com in the past 4 months 

● Faire GMV lifetime​: Total cumulative gross merchandise volume of wholesale inventory  that retailer has purchased on Faire.com 

  Please use the following simplifying assumptions for this analysis: 

● Average Faire commission (take rate) on Neighborhood orders: 15% of GMV.   ● 25% of retailer leads come from Faire’s base of 100,000 active retailers on the Faire.com 

wholesale platform, and we use paid marketing to generate remaining retailer leads for  Neighborhood.​ ​Current Neighborhood retailers are representative of both other active  Faire retailers and retailers who find us through paid marketing. 

● On average we have a $1.12 cost-per-click and 7.3% conversion rate from click to retailer  lead acquisition through paid marketing channels 

● On average 40% of retailer leads activate on the Neighborhood platform  ● On average retailer GMV is expected to grow by 10% each month for their first 6 months 

on Neighborhood, after which it will no longer grow  ● Faire’s average contribution margin before marketing is 10% of Neighborhood revenue. 

  Please answer the following questions: 

1. Using the data provided, how strongly does each retailer attribute correlate with GMV on  Neighborhood? 

2. Which 3-5 attributes would you incorporate into a lead scoring model to distinguish the  highest potential value retailers who have not yet joined Neighborhood?  

a. Why did you select these 3-5 attributes?   b. How would you segment retailers into lead scoring tiers (e.g., Tier 1, Tier 2, Tier 

3) based on each attribute you selected (e.g., what cutoffs or thresholds would  you apply for each attribute)? 

3. If our lead scoring model successfully identifies retailers similar to the top 15% of active  Neighborhood retailers (based on GMV), and using the assumptions listed above, how  many of these high potential value leads does a sales rep need each month to drive  $90,000 in contribution margin after marketing over the course of a year? 

4. How would you operationalize the lead scoring model and what KPIs would you track to  ensure it’s successful?