A company named The Logis Group (LG), is a business-to-business consulting services company that provides consulting services
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. 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 company’s 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 company’s 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:
Financial and Managerial Accounting Using Excel for Success
ISBN: 978-1111993979
1st edition
Authors: James Reeve, Carl S. Warren, Jonathan Duchac