Question 1
Please refer to the Excel Sheet shared with you. Herewith some context:
opportunities represent records of interest from customersadvertising_opportunities is a table in the database that the advertising team uses. It is a seperate table but largely has the same information with some additional columns that the advertising team use to understand attributionThe table ‘correct_opportunity’ and field ‘o.inferred_country_location’ is more correct from your experience as a Sales Analyst. You have tested this thoroughly in the live production environment.
(1.1) Explain in the sheet what each SQL line means. Explain it at a very simple level i.e. to someone not technical on the google sheet next to the SQL line. Also list any questions you might have to better understand the data. 100 word guideline
Answer in the sheet...
(1.2) A Junior Data Analyst pasted both the SQL script and the output in the sheet. The Head of Sales Operations (your direct manager) mentioned that there might be an issue/discrepancy in the data that the advertising team uses to manage their spend ($100). They make extensive use of targeting advertising at a country level, using the advertising_opportunities table. Using both a simple table and written format, articulate the scope of the bad data to the Head of Sales Operations and how that would impact revenue (assume the team make very logical decisions of where spend is working). You can assume the cost to advertise per opportunity is $100 and when someone pays, it represents $1800. 400 word guideline
Answer here...
_______________________________
(1.3) Please include as much of your thinking / assumptions here as possible. What was your thought process to answer (1.2)300 word guideline
Answer here...
(1.4) Show/explain your calculations / methods in the google sheet in how you answer (1.2)200 word guideline
Answer in the sheet…
Question 2
Please refer to the Excel Sheet.
(2.1) create a report (or graphical presentation) for Senior Sales Management detailing the outbound and inbound time spent on the phone per course consultant in the month of May 2017. 300 word guideline
Answer in the sheet...
(2.2) Highlight five takeaways to Senior Sales Management in written bullet point format. Your answer should relate to course consultant performance based off the phone activity record. 300 word guideline
Answer here...
(2.3) List 5 specific questions that you would ask the data team to understand what the data represents i.e. how is the data generated from activity. 400 word guideline
Answer here...
Answer and show all your workings (especially formulas) on the Google Sheet.
PS Senior Sales Management is known to appreciate neatly laid out information i.e. frozen panes and good formatting.