Produce a list of orders (customerId, customer name, City, order date, shipped date) that came from our Colorado customers How many were there for 2019 compared to 2018 How does this compare to the rest of the US Hint use the criteria filter The Marketing Dept has requested a list of US Customers by name (alphabetically), categorized by state Include contact information, address information, and phone Hint use the group by, order by and criteria filter Produce a price list of our products by product category Hint use the group by tool () What is our best selling product in terms of number of units (all years) How does this differ from comparing our best selling product in terms of dollar sales volume Explain Hint use the order by or top values We are sending a rep to make calls on our French customers Produce a list including contact information and sales dat We only have 3 days, so the rep can only do 3 or 4 stops Where would you send the rep and why Hint Combine customer and order and then filter on country Or try group by and count () Compare 2019 sales to 2018 for Merlins Magic Merlot Should we continue this product How does it compare to our other Merlots What do the trends look like Hint combine Order, order details and product and use the sum () The people who buy Grand Mesa Colterris also purchase what other products What could we do with this information () May take a couple of queries What were 2019 dollar sales by employee How did they change from 2018 and who should we reward Who should we be concerned about Combine Order, Order details and Employee, filter by year (criteria line), use the () Which orders have not yet been shipped Hint To know this you must first know what Considering 2018 and 2019 gross sales, what should we expect for 2020 Part 2 Dashboard 9 points Design a dashboard for Valley Vineyards owners What elements should be on the dashboard Consider what you would recommend should be their KPIs Design the dashboard (does not have to be functional) with at least 4 indicators Describe where the data will come from What product would you recommend that Valley use for the dashboard

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jan 10, 2021

Produce a list of orders (customerId, customer name, City, order date, shipped date) that came from our Colorado customers.

Hint: use the criteria filter

The Marketing Dept has requested a list of US Customers by name (alphabetically), categorized by state. Include contact information, address information, and phone.

Hint: use the group by, order by and criteria filter

Hint: use the group-by tool (∑)

What is our best-selling product in terms of number of units (all years)? How does this differ from comparing our best-selling product in terms of dollar sales volume? Explain.

Hint: use the order by or top values

We are sending a rep to make calls on our French customers.
1. Produce a list including contact information and sales dat We only have 3 days, so the rep can only do 3 or 4 stops.
2. Where would you send the rep and why?

Hint: Combine customer and order and then filter on country

Or try group by and count (∑)

Compare 2019 sales to 2018 for Merlin’s Magic Merlot.
1. Should we continue this product?
2. How does it compare to our other Merlots?
3. What do the trends look like?

Hint: combine Order, order details and product and use the sum (∑)

The people who buy Grand Mesa Colterris:
1. also purchase what other products?
2. What could we do with this information? (∑)

May take a couple of queries.

What were 2019 dollar sales by employee?
1. How did they change from 2018 and who should we reward?
2. Who should we be concerned about?

Combine Order, Order details and Employee, filter by year (criteria line), use the (∑)

Hint: To know this you must first know what?

Part 2. Dashboard – 9 points

Design a dashboard for Valley Vineyard’s owners. What elements should be on the dashboard? Consider what you would recommend should be their KPI’s.

Design the dashboard (does not have to be functional) with at least 4 indicators.
Describe where the data will come from.
What product would you recommend that Valley use for the dashboard?