As An Analyst, You Are Likely To Receive A Number Of One-Off Requests To Provide Answers To A Variety Of Business Questions. These Requests Can Be Time-Sensitive And Demand That You Turn Around A Summary Of Your Findings Quickly.

computer science

Description

Project Part 2 - Ad Hoc Reporting

As an analyst, you are likely to receive a number of one-off requests to provide answers to a variety of business questions. These requests can be time-sensitive and demand that you turn around a summary of your findings quickly. You will also have to juggle a number of these requests at the same time. This means that the deliverable for these types of ad hoc requests will usually be a short and concise summary of your findings. These short reports can include recommendations or insights if requested, but in other cases, they will only require hard numbers or other factual findings. 

This style of analysis allows you to extract insights from smaller sections of data as needed to answer more defined and focused business questions. It allows you to formulate specific insights for your organization when they are needed, as opposed to a more formal report such as a presentation or quarterly newsletter" 

In this Project Part, you will respond to multiple ad hoc requests from varying teams or persons in the LetsMeet organization. You will write a series of queries to answer their questions and summarize your findings in the provided 1_summary_report.sql file.

The goal of this project is to help you get comfortable breaking down and responding to an ad hoc request. You will be given guiding questions and instructions to assist you in thinking through the asks and to help guide the queries you are writing.

Summary Report Examples

The following are three examples of ad hoc requests you might get as an analyst and examples of how you might communicate your findings (Note: These are not the same requests you will respond to in the project). In the real world, these short reports would likely be included in an e-mail, but for the sake of this Project, you will organize your findings as multi-line comments in a SQL file.

Reporting just the facts:

The Request:

The Report:

Since Cat simply asked you to report back with some numbers, your response might include a bullet-pointed list like the following:

-- On average how many members do LetsMeet groups have? 

/* 
- LetsMeet groups have an average of 732 members. 
- The group with the most members currently has 23380 members.
- The group with the least members currently has 1 member.
*/

Reporting quick insights:

The Request:

The Report:

Isabella didn't provide too many details about the numbers she is interested in but did ask you to provide some insights into how users are uploading their events. After performing a short exploratory analysis, the information you communicate back to her might look something like the following snippet:

-- How are LetsMeet groups creating their events? In bulk or one at a time?

/*
- A significant number of groups are creating multiple events on a single day. 
- The most common number of events created per day is approximately 50.
- There appears to be a relationship between the number of events a group hosts and the number of 
  events they upload at one time.
- Groups creating 50 events in a day typically host more events overall.
- Groups with less frequently occurring events typically create them one at time.
*/

Making a recommendation:

The Request:

The Report:

In this case, you need to not only provide insights into the data but also make recommendations based on your findings. This might look something like the following:

-- Recommendations for beta testing 

/*
- Chicago Area Toastmasters.
- They have created the most events of any of the groups of LetsMeet.
- They have created 1061 events.
- They frequently upload 50 events at a time 

- Business Events NY group.
- They have fewer events overall at 36.
- They have been bulk uploading their events consistently in groups of 12. 
- Perhaps this feature could encourage them to host more events.

- Alternative Fun Stuff To Do NYC.
- They have created 17 events in total.
- Each event was created on a different day.
- Based on the data, they are consistently creating regular events and may be good candidates for 
  bulk uploading.
*/

Ready, Set, Report!

You're now ready to start answering your own ad hoc requests. Time to open up your inbox and MySQL workbench and start analyzing! 


The Requests

Your LetsMeet inbox is filling up quickly with requests for data and insights! ?

You currently have three requests in your inbox awaiting your response:

  1. The LetsMeet CEO Evelyn wants to know how many Toastmasters events are occurring in each city. 
  2. The Growth team wants to know if membership is leveling off, and if so, why.
  3. The Marketing team is looking for recommendations on groups to feature in an upcoming campaign.

Each of these requests will require a different level of analysis and insights or recommendations. Follow the instructions in each of the steps below to write queries and respond to the requests accordingly.

While completing your project, stop every now and then to save your work. This will ensure that your work won't be lost!

Request #1: Toastmasters Events ?

The first e-mail in your inbox is from the CEO Evelyn. 

This request seems straightforward, so it's time to get down to work!

To get started, follow these steps: 

  1. Navigate to 2_toastmaster.sql
Don't see 2_toastmaster.sql? Create the SQL file yourself.

2. To begin with, you should find all the events that include the word, Toastmaster. You can use string manipulation to return all event names that include "Toastmaster" or "toastmaster."

Hint

3. Now that you have the Toastmasters events, write a second query to display them alongside the cities in which they are hosted.

Hint

4. Lastly, write a third query to give the exact counts for how particular cities host many Toastmasters events. 

Hint

5. Save your script by clicking File > Save Script

5. Return to 1_summary_report.sql and summarize your findings for Evelyn. Since she only asked for the hard numbers, there is no need to provide additional recommendations or insights. 

Request #2:  Leveling Off Of Member Growth ?

The next request in your inbox is from a member of the LetsMeet Growth team. They are part of the Sales team that is responsible for growing the membership of LetsMeet year-over-year.

In their follow up e-mail Sammy included a helpful reminder that the joined column in the grp_member table represents the date a member joined a specific group, not the date they joined LetsMeet. They suggest instead that you use the date a member joined their first group as a proxy for when they joined the site.

To get started, follow these steps:

1.Navigate to the SQL file named 3_growth.sql.

Don't see 3_growth.sql? Create the SQL file yourself.

2. Write a query that shows the growth of LetsMeet over the years based on the number of members who joined each year.

 

Hint

3. In the grp_member table, use the appropriate DML to rename the smaller cities to the name of their larger urban areas. Based on the attachment from Sammy, they should be combined as follows:

  • Chicago - East Chicago, West Chicago, North Chicago, Chicago Heights, Chicago Ridge, Chicago

Instruction Files
TablesandQuestions.docx
1.7 MB
Ask This Assignment To Be Done By Our Experts

Get instant assignment help service

Related Questions in computer science category