Question
A company named The Logis Group (LG), is a business-to-business consulting services company that provides consulting services to support the work of its customer companies.
A company named The Logis Group (LG), is a business-to-business consulting services company that provides consulting services to support the work of its customer companies. LG's customer companies are from many business and industry sectors including manufacturing, distribution, retail, transportation, and others. Besides a fixed amount of annual subscription fee, customer companies also pay variable charges, which are based on the extent to which their employees use LG's products and services.
LG's business analytics (BA) department is responsible for tracking users' online activity and engagement in order to provide better products and services to its customers. (NOTE: the term users refers to employees of the customer companies.) Various BA teams conduct studies, carry out projects to address specific business problems, and perform ad-hoc analyses to support business decisions. You are one of the business analysts in the BA department.
LG defines user activity as an engagement with its online portal, which includes the customer company employees (users) having made some type of server call by interacting with the companys website/web server. Such events are listed as engagement in the event_type column of the EVENTS table.
Data:
The following file (events.csv) is available on Canvas (Files > Data Files). Columns in this file are listed below.
EVENTS | |
user_id: | The ID of the user logging the event. Can be joined to user_id in either of the other tables. |
occurred_at: | The time the event occurred. |
event_type: | The general event type. There are two values in this dataset: signup_flow: refers to anything occurring during the process of a user's authentication, engagement: refers to general product usage after the user has signed up for the first time. |
event_name: | The specific action the user took - possible values include the following: create_user: User is added to the companys database during signup process enter_email: User begins the signup process by entering her email address enter_info: User enters her name and personal information during signup process complete_signup: User completes the entire signup/authentication process home_page: User loads the home page like_message: User likes another user's message login: User logs into her/his account search_autocomplete: User selects a search result from the autocomplete list search_run: User runs a search query and is taken to the search results page search_click_result_X: User clicks search result X on the results page, where X is a number from 1 through 10. send_message: User posts a message view_inbox: User views messages in her inbox |
location: | The country from which the event was logged (collected through IP address). |
device: | The type of device used to log the event. |
Question to Answer:
Please conduct necessary analysis using SQL to investigate the following question:
In which of the following countries, has user activity or engagement dropped in recent weeks? United States, Japan, Brazil, France.
events.csv:
mmmmm user_id occurred_at event_type event_name location device user_type 10522 5/2/14 11:02 engagement login Japan dell inspiron 3 10522 5/2/14 11:02 engagement home_page Japan dell inspiron 3 10522 5/2/14 11:03 engagement like_messag Japan dell inspiron 3 10522 5/2/14 11:04 engagement view_inbox Japan dell inspiron 3 10522 5/2/14 11:03 engagement search_run Japan dell inspiron 3 10522 5/2/14 11:03 engagement search_run Japan dell inspiron 3 10612 5/1/14 9:59 engagement login Netherlands iphone 5 1 10612 5/1/14 10:00 engagement like_messag Netherlands iphone 5 1 10612 5/1/14 10:00 engagement send_messa Netherlands iphone 5 1 10612 5/1/14 10:01 engagement home_page Netherlands iphone 5 1 10612 5/1/14 10:01 engagement like_messag Netherlands iphone 5 1 10612 5/1/14 10:02 engagement home_page Netherlands iphone 5 1 10612 5/1/14 10:02 engagement view_inbox Netherlands iphone 5 1 10612 5/1/14 10:03 engagement like_messag Netherlands iphone 5 1 10612 5/1/14 10:03 engagement home_page Netherlands iphone 5 1 10612 5/1/14 10:04 engagement send_messa Netherlands iphone 5 1 10612 5/1/14 10:04 engagement like_messag Netherlands iphone 5 1 10612 5/1/14 10:05 engagement send_messa Netherlands iphone 5 1 10736 5/9/14 17:52 engagement login Austria iphone 4s 2 10736 5/9/14 17:53 engagement like_messag Austria iphone 4s 2 10736 5/9/14 17:53 engagement send_messa Austria iphone 4s 2 10965 5/15/14 13:52 engagement login Finland windows sur 3 10965 5/15/14 13:53 engagement home_page Finland windows sur 3 11020 5/8/14 9:15 engagement login Japan macbook air 2 11020 5/8/14 9:15 engagement home_page Japan macbook air 2. 11020 5/8/14 9:16 engagement view_inbox Japan macbook air 2 11020 5/8/14 9:16 engagement home_page Japan macbook air 2 11020 5/8/14 9:17 engagement view_inbox Japan macbook air 2 11020 5/8/14 9:17 engagement send_messa Japan macbook air 2 11020 5/8/14 9:18 engagement view_inbox Japan macbook air 2 11020 5/8/14 9:19 engagement like_messag Japan macbook air 2 11037 5/1/14 7:25 engagement login United Kingd iphone 4s 1 11037 5/1/14 7:25 engagement like_messag United Kingd iphone 4s 1 5/1/14 7.26 engagement home page United Kingdinhone 4s 11037 1Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started