Business Questions:

Finance

1. What is the revenue trend overtime? (Revenue)
2. What categories or brands generate the highest and lowest revenue? (Revenue)

Marketing

1. Which customer segments contribute most to revenue, segmented by gender, age group, and geographical location? (Customer Acquisition)
2. Which traffic sources drives the highest revenue and lowest revenue? (Campaign Impact)

Product

1. What demographic segments prefer which categories or brand? (Product Performance)
2. Which product categories or brands have the highest conversion but lowest retention? (Product Performance)

Conceptualize the data

Dataset: thelook_ecommerce

Tables I will be focusing on: orders, order_items, products, users

• orders

  Order [order_id] was placed by [user_id], containing [num_of_items] items and marked with a status of [status]. It was created on [created_at], shipped on [shipped_at], and delivered on [delivered_at]. If the order status was “cancelled” or “returned,” it was processed for return on [returned_at].

• order_items Each ordered item has a unique [id] that links it to the [order_id] placed by [user_id] and identifies the specific [product_id] purchased. Every order item [id] includes the following details: [inventory_item_id], [status], [created_at], [shipped_at], [delivered_at], [returned_at], and [sale_price].

• products

  A product [id] includes details such as [cost], [category], [name], [brand], [retail_price], [department], [sku], and [distribution_center_id].

• users

  A user [id] includes information such as [first_name], [last_name], [email], [age], [gender], [state], [street_address], [postal_code], [city], [country], [latitude], [longitude], and [traffic_source], and was created on [created_at] within [user_geom].

Key Metrics and Dimensions

Tools: