CSCI 526 – Database Systems
\nProject 1
\nDue on 10\/07\/2022, 11:55PM
\nRequirement
\n\u2022 This project ( help with nursing paper writing from experts with MSN & DNP degrees) requires students\u2019 independent effort. No collaborative work is needed. But
\nyou can share the ideas of solving the questions.
\n\u2022 Submit your solutions to D2L in time, no late submission will be accepted.
\nContext
\nYou are starting a new position as Data Analytic Engineer in an E-commerce company. The
\ncompany is a online retailer who has just launched their first product.
\n\u2022 Responsibilities:
\n\u2013 As a member of the startup team, you will work with the CEO, the Head of Marketing,
\nand the Website Manager to help steer the business.
\n\u2013 You will analyze and optimize marketing channels, measure and test websperfor mance, and use data to understand the impact of new product launches.
\n\u2022 Goal: to use SQL to:
\n\u2013 Access and explore the Maven Fuzzy Factory database \u2022 Become the data expert
\nfor the company, and the go-to person for mission critical analyses
\n\u2013 Analyze and optimize the business\u2019 marketing channels, website, and product port folio
\nuse the mysql workbench \/ terminal \/ sqlectron to connect a database. The database URL and
\nthe password will be provided in an email. You are required:
\n\u2022 connect the database on AWS using the provided credentials
\n\u2022 check through the schema of database tables, and understand what is relation between
\ndifferent tables
\n\u2022 DO NOT modify the data inside the database, because your classmates are using the
\nsame database and tables for their project ( help with nursing paper writing from experts with MSN & DNP degrees).
\nHINTS: when the question is complex, you could try to use WITH clause to create temporary
\nrelation, we have discussed WITH clause. Also, you could use TEMPORARY table as well.
\nTemporary table will be accessible before you exit the current mysql session. After you ter minate the current session and re-login mysql with a new session, the temporary table will
\ndisappear. Here is the example:
\nCREATE TEMPORARY TABLE test
\nSELECT * FROM products;
\nwe are creating a temporary table and namomg it as test, test table contains all data in products
\ntable.
\n1
\nHere is the Database which stores the online activities and order information: There are 6
\ntables in the database:
\nwebsite_sessions, and website_pageview
\nData about the website activities provided by Urchin Tracking Module (UTM)
\norders, order_items, and order_item_refunds
\n2
\nproduct
\nOnce you understand the database and tables, try to analyze the data inside the database and
\nhelp to run the online business more efficiently.
\nUrchin Tracking Module (UTM)
\nUrchin Tracking Module (UTM) parameters are five variants of URL parameters used by mar keters to track the effectiveness of online marketing campaigns across traffic sources and
\npublishing media. They were introduced by Google Analytics\u2019 predecessor Urchin and, con sequently, are supported out-of-the-box by Google Analytics. The UTM parameters in a URL
\nidentify the campaign that refers traffic to a specific website, and attributes the browser\u2019s web site session and the sessions after that until the campaign attribution window expires to it. The
\nparameters can be parsed by analytics tools and used to populate reports.
\nThe businesses run paid marketing campaign using UTM, and keep track of the activities. The
\nUTM parameters (utm_source, utm_campaign, utm_content) in website_sessions table are
\nassociated with the paid traffic. The UTM parameters can answer the following questions:
\n\u2022 Where is the traffic coming from?
\n\u2022 How is it getting to me?
\n\u2022 Why is it coming to me?
\nHere are some more information about the UTM parameter: The Complete Guide to UTM
\nCodes: How to Track Every Link and All the Traffic From Social Media.
\nIn the table, utm_source is not null, meaning the web sessions are driven by paid cam paigns.
\nTasks
\n1. Your Data Analytics Manager comes to you, and wants to know all types of utm_content
\nand the number of sessions associated with all different kinds of utm_content. Give
\nyour manager a clear picture by ordering the results in descending order on number of
\nsession.
\n2. One of the stakeholders in marketing department needs to know the number of orders
\nare driven by all kinds of utm_content, then she can have idea on the effectiveness of
\ndifferent utm_content.
\n3. 2014-12-31 is set as milestone, and a manager from the marketing team wants to know
\nwhere the bulk of sessions were from through 2014-12-31. She wants to see a break down by UTM source, campaign, and referring domain.
\n3
\n4. Looks like \u201cgsearch\u201d and \u201cnonbrand\u201d is the major traffic source, the manager in the mar keting team wants to know the conversion rate from sessions to orders, meaning, you
\nneed to provide the number of sessions, number of orders, and the conversion rate be fore 2014-12-31 on \u201cgsearch\u201d and \u201cnonbrand\u201d.
\n5. The market manager wants to check the trend of the session on \u201cgsearch\u201d and \u201cnon brand\u201d through 2014-12-31. In the results, you will need to show the year, month, and
\nthe number of distinct sessions associated with the that time frame. You may need to use
\nYEAR(), MONTH() functions in your SQL statement.
\n6. A manager from the website management team want to know the most popular landing
\nzone, meaning that, the most popular pageview_url that a website session starts. This
\nwould help the clickstream team to identify which channel is most effective. You are
\nrequired to return a page which contains 2 columns (landing_page, num_of_session).
\nThe landing page is the page which a session starts from.
\n7. The website manager wants to check the bounce rate of each landing page. Bounce rate
\nis defined as: the number of web sessions ONLY stay at the landing page and didn\u2019t go
\nfurther, divided by the number of total sessions. When you return the result to the man ager, you need to include the columns, like: lnading_page, sessions, bounced_sessions,
\nbounce_rate
\n8. Consider the steps from browsing products through finishing a transaction, the following
\nurls can be considered as sequential steps to finish an transaction:
\n(a) \/products
\n(b) \/the-original-mr-fuzzy
\n(c) \/cart
\n(d) \/shipping
\n(e) \/billing
\n(f) \/thank-you-for-your-order
\nYou are requested to calculate the conversion rate for these steps through 2014-12-31.
\nThe returned results should be contains columns like: num_of_sessions, rate_to_products,
\nrate_to_mrfuzzy, rate_to_cart, rate_to_shipping, rate_to_billing, rate_to_thankyou.
\n\u2022 rate_to_products: the conversion rate from num_of_sessions to (a)
\n\u2022 rate_to_mrfuzzy: the conversion rate from \/products to \/the-original-mr-fuzzy
\n\u2022 rate_to_cart: the conversion rate from \/the-original-mr-fuzzy to \/cart
\n9. A new product \u201c\/the-forever-love-bear\u201d was added to the website on 2013-01-06, and the
\nmarketing manager wants to pull out the website activities from 2012-10-06 to 2013-01-
\n06, and 2013-01-06 to 2013-04-06, after the users hit \u201c\/product\u201d page and see where they
\nwent next. Also, she wants to check the impact of clickstreams on the new product and
\nthe old product (\u201c\/the-original-mr-fuzzy\u201d) after the new product was added. The pulled
\nout data should include 2 phases:
\n\u2022 2012-10-06 to 2013-01-06: you could name it as pre-product
\n\u2022 2013-01-06 to 2013-04-06: post-product period
\nfor each of these two period, there should be the following columns:
\n4
\n\u2022 number of sessions hitting to \u201c\/product\u201d in the period
\n\u2022 number of sessions have next page from \u201c\/product\u201d
\n\u2022 the proceeding rate from \u201c\/product\u201d
\n\u2022 number of session went to \u201c\/the-original-mr-fuzzy\u201d from \u201c\/product\u201d
\n\u2022 the conversion rate to \u201c\/the-original-mr-fuzzy\u201d
\n\u2022 number of session went to \u201c\/the-forever-love-bear\u201d from \u201c\/product\u201d
\n10. The marketing team added a recommended product at the \u201c\/cart\u201d page to suggest users
\nto buy the products together (cross-sell). This function was launched at 2013-09-25. The
\ndirector wanted to pull out the order\/product information from 2013-08-25 to 2013-09-25,
\nand 2013-09-25 to 2013-10-25, to check the impact of the cross-sell function in these two
\nperiods. The data that you pull out should include:
\n\u2022 time periods: from 2013-08-25 to 2013-09-25, and 2013-09-25 to 2013-10-25
\n\u2022 number of cart sessions: the number sessions reach to \u201c\/cart\u201d in the two periods
\n\u2022 number of clickthrough: the number of sessions proceed through \u201c\/cart\u201d page.
\n\u2022 clickthrough_rate:
\n\u2022 number of placed order for these two periods.
\n\u2022 number of products for these two periods.
\n\u2022 products per order
\n\u2022 revenue in these two periods: sum of the \u201cprice_usd\u201d
\n\u2022 average of order value:
\n\u2022 revenue per cart:
\nHINTs: in the question, you will need to use tables like: orders, order_items, web site_pageviews, website_sessions
\n5<\/p>\n","protected":false},"excerpt":{"rendered":"
CSCI 526 – Database Systems Project 1 Due on 10\/07\/2022, 11:55PM Requirement \u2022 This project ( help with nursing paper writing from experts with MSN\u2026<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[479,537,172,478,6744],"tags":[7780,7779,687,7783,3504,8899,8900,7782,7785,7787,7784,7781,8901,7788,7786],"class_list":["post-95794","post","type-post","status-publish","format-standard","hentry","category-nursing-course-hero","category-nursing-essay-pro","category-nursing-study-bay","category-nursing-study-pool","category-nursing-studybay","tag-apps-to-help-with-writing-assignment","tag-assignment-help-in-australia","tag-assignment-help-website","tag-assignment-helpers-for-students","tag-essay-writing","tag-essy-help","tag-essy-pro","tag-help-students-with-assignment","tag-i-will-pay-someone-to-do-my-assignments","tag-looking-for-someone-to-do-my-assignments","tag-pay-someone-to-do-my-assignment","tag-paying-someone-to-do-assignment","tag-studbay","tag-studybay","tag-university-assignment-hacks"],"_links":{"self":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/95794","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/comments?post=95794"}],"version-history":[{"count":0,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/95794\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/media?parent=95794"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/categories?post=95794"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/tags?post=95794"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}