Customer Insights Data for Power BI Dashboards

• sales.customer_id →references customers.customer_idmany to one: each Sales row points to one Customers row
• interactions.customer_id →references customers.customer_idmany to one: each Interactions row points to one Customers row

1. Analyze the total sales revenue by customer segment and loyalty tier using Power BI.

   Join customers and sales tables on customer_id. Use SUM(total_price) and group by customer_segment and loyalty_tier.

   tables: customers, sales

2. Identify the top 5 customers by total sales value and their most frequent interaction type.

   Calculate total sales per customer, then find the most frequent interaction type for each customer. Combine and rank.

   tables: customers, sales, interactions

3. Create a Power BI dashboard showing customer acquisition trends over time based on signup date.

   Use the signup_date column to plot the count of new customers over time. Consider grouping by month or year.

   tables: customers

4. Calculate the average quantity sold per customer for each product category.

   Join customers and sales tables. Calculate the average quantity sold for each customer, then aggregate by product category (assuming product_id can be mapped to categories).

   tables: customers, sales

Table names match the SQLite file and the SQL script.

SELECT c.customer_segment, SUM(s.total_price) AS total_sales FROM customers c JOIN sales s ON c.customer_id = s.customer_id GROUP BY c.customer_segment ORDER BY total_sales DESC LIMIT 20;

SELECT c.customer_id, c.email, SUM(s.total_price) AS total_sales FROM customers c JOIN sales s ON c.customer_id = s.customer_id GROUP BY c.customer_id, c.email ORDER BY total_sales DESC LIMIT 20;

SELECT STRFTIME('%Y-%m', signup_date) AS signup_month, COUNT(customer_id) AS new_customers FROM customers GROUP BY signup_month ORDER BY signup_month LIMIT 20;

SELECT AVG(quantity) AS average_quantity_sold FROM sales LIMIT 20;

SELECT interaction_type, COUNT(*) AS interaction_count FROM interactions GROUP BY interaction_type ORDER BY interaction_count DESC LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join 'sales' and 'customers' tables on 'customer_id'. Create a PivotTable from the joined data to analyze sales by segment and loyalty tier.

Power BI

Create relationships: 'customers' to 'sales' (one-to-many on customer_id) and 'customers' to 'interactions' (one-to-many on customer_id). Measures: Total Sales = SUM(sales[total_price]), Customer Count = COUNTROWS(customers). Build visuals for sales trends and customer demographics.

SQL

Load the SQLite or SQL relational download. The primary keys are customer_id (customers), sale_id (sales), and interaction_id (interactions). Join tables using customer_id to link sales and interactions to customer details.

Python

Use pandas to load CSV or other formats. Merge 'sales' and 'customers' DataFrames on 'customer_id'. Analyze sales performance, customer behavior, and interaction patterns using libraries like pandas and matplotlib.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file