Description

For an upcoming football campaign FanDuel are going to send an email to existing FanDuel users letting them know headline tournaments and new site features for the season. In addition there will be targeted offers to certain players based on their transactional history.

As an analyst your job is to help define the segmentation and then use the SQL database to generate lists of users. The data tables you have at your disposal are below:

USER TABLE – This is where we store information about the user’s account

ENTRY TABLE – This is where all the information about every submission (entry) into games is stored

GAME TABLE – This is where all the specifics about games are stored

PAYMENTS TABLE – This is where we store information about and deposits and withdrawals

1)     Based upon the information you have in the database what variables would you consider most when designing a segmented offer for users in the new football season and why do you think that they are important? What sort of groups would you like to create and how might you vary the incentives/offer to each of those groups?

2)     The CRM Manager has an idea for a test, but wants to know if there are enough users available to make the results significant. There are a couple of filters to apply before he gets in to further detail so asks you to work out the count of users who have made at least one deposit and registered in the previous year (2013). How would you do this with SQL code? Are there any other constraints you might consider adding to give a more accurate estimate?

3)     The CRM Manager is happy with the base user size so fills you in on the additional restrictions. He now wants to see how many users have made at least one deposit, registered in 2013 and played NFL in 2013. What would be the SQL code needed to answer this?

4)     Now we have the list size finalized it’s time to generate the list of users to be sent the email. With the same constraints as above you need to generate a list with the following fields, so that the CRM Manager can start to build his custom segments:

                     i.            user_id

                   ii.            email

                 iii.            total entry fees for NFL in 2013

                 iv.            total entry fees for the other sports combined in 2013

                   v.            the number of winning entries the user has ever had

                 vi.            the percentage of entries on mobile across ALL sports in 2013

               vii.            the last paid entry date of the user

             viii.            the total net deposit value, i.e. deposits - withdrawals

How would you create this list in SQL?

5)     The Marketing VP is concerned that there has been a movement away from head-to-head (2 player) games over the last 3 years for football. He believes it may be a reason for declining tenure so has requested that you investigate. Specifically he wants to know in each year between 2011 and 2013 what percentage of entries and entry fees were head-to-head games in each user’s first 30 days after registration. Provide the SQL code required to answer his questions.

6)     The marketing VP is pleased with what you pulled from Question 4! They want to repeat what you’ve done but this time across different sports and different years (not just NFL and 2013). They send you a file called sport.csv with the below output: