Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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:

image text in transcribed

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 1

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting History And The Rise Of Civilization, Volume II

Authors: Gary Giroux

1st Edition

163157793X, 9781631577932

More Books

Students also viewed these Accounting questions

Question

8. How are they different from you? (specifically)

Answered: 1 week ago