Transcribed TextTranscribed Text

Strategy & Analytics Context As member of the Strategy & Analytics team you'l be responsible for helping us understand our business better developing action plans to solve issues, and partnering with teammates in local market to implement solutions. This exercise is representative of the first two parts of normal workflow on the team (i.e. diagnosing an issue and developing strategy to fix it). Part I: Business Case Instructions The following case designed to showcase your strategic problem solving and data analysis skills. The problem statements are intentionally open-ended. It is possible to spend as much or as little time on this exercise as you would like. Question 1 We've given you disguised data for the MLS (market) and Opendoor. The spreadsheet includes counts of active listings, visits, resale contracts, and related data including price point and geo codes How enthusiastic or worried are you about Opendoor's resale performance in 2017? Question 2 Based on what you learned in Question what do you hypothesize is driving any over/underperformance trends? Please limit to your top three hypotheses. For each hypothesis, what additional data would you request and what analyses would you runto validate? Question 3 Chose one of your top hypotheses from Question and assume you 've validated that it's the driver of the performance trend. Imagine that you are the General Manager with P&L responsibility of the marke in question, propose plan of action to either course correct (in the instance of underperformance) or drive continued growth (if you identified positive performance). Field Definitions price band: original list price bucketed below and above $200K zip_code: A, B,Cand denote different, illustrative zips mls listings: total active listings on the marketon any given day mls _contracts: resales contracts od listings: total Opendoor active listings on the market on any given day od contracts: Opendoor resales contracts od home visits: total home visits on all active listings Part II: SQL Exercise Using the tables provided below and your preferred SQL dialect, draft queries to answer the following questions: 1. How many homes were listed each month by market? 2. What percentage of homes in each listing month and market cohort have received offer? 3. What was the average number of unique visitors in the first days after listing for each listing month and market cohort? 4. Which (if any) email campaigns were most successful driving home visits in each market and month? Home sales? analytics homes Field Name Data Type Desc ription home_ INT64 unique identifier for home (table distinct on this field) home state STRING current status of home. one of: offered acquired, listed sold timestamp for when the home was created in our db list_date date that the home was listed on the market market name OD market Field Name Type Description listing offer_id INT64 unique identifier for listing offer (table is distinct field) created TIMESTAMP timestamp for when the listing offer was created home INT64 unique identifier for home associated w/ listing offer customer INT64 unique identifier for customer who made the offer signed_a TIMESTAME timestamp for when the listing offer was signed (became contract) listing offer state STRING current state of listing offer. one of: active, rejected in contract, contract fell through, closed listing offer_price FLOAT latest price offered by buyer analytics home_ visits Field Name Data Type Description visit_id INT64 unique identifier for the home visit (table distinct on field) home INT64 unique identifier for the home visited TIMESTAMP timetamp for home visit customer_ic INT64 unique identifier for customer who visited sends Field Name Data Type Description event INT64 unique id for the email send event (table distinct on field) unique id for the email campaign market unique for market in which the campaign was active TIMESTAMP timestamp for the email send customer id INT64 unique id for the customer to whom the email was sent

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

    By purchasing this solution you'll be able to access the following files:
    Solution.docx and Solution.xlsx.

    50% discount

    $125.00 $62.50
    for this solution

    or FREE if you
    register a new account!

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Business - Other Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Upload a file
    Continue without uploading

    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats