Customer Dataset for CRM Analysis

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

1. Analyze the distribution of customer segments and their signup dates. Which segment has the most customers and when did they typically sign up?

   tables: customers

2. Calculate the average duration of customer interactions by channel. Which channel has the longest average interaction time?

   tables: interactions

3. Determine the average resolution time for support tickets by priority level. Which priority level takes the longest to resolve?

   tables: tickets

4. Using SQL, join customers with their interactions and tickets to find customers who have had more than 3 interactions and at least 1 open ticket.

   tables: customers, interactions, tickets

Table names match the SQLite file and the SQL script.

SELECT customer_segment, signup_date, COUNT(customer_id) AS num_customers FROM customers GROUP BY customer_segment, signup_date ORDER BY num_customers DESC LIMIT 20;

SELECT channel, AVG(duration_minutes) AS avg_duration FROM interactions GROUP BY channel ORDER BY avg_duration DESC LIMIT 20;

SELECT priority, AVG(resolution_time_hours) AS avg_resolution_time FROM tickets WHERE resolution_time_hours IS NOT NULL GROUP BY priority ORDER BY avg_resolution_time DESC LIMIT 20;

SELECT c.customer_id, COUNT(DISTINCT i.interaction_id) AS num_interactions, COUNT(DISTINCT t.ticket_id) AS num_open_tickets FROM customers c JOIN interactions i ON c.customer_id = i.customer_id JOIN tickets t ON c.customer_id = t.customer_id WHERE t.status = 'Open' GROUP BY c.customer_id HAVING num_interactions > 3 AND num_open_tickets >= 1 LIMIT 20;

Excel

Load each table into a separate Excel sheet. Use XLOOKUP to join tables based on customer_id. Create a PivotTable on the customers sheet to analyze customer segments and signup dates.

Power BI

Load all three tables. Create relationships: customers to interactions (one-to-many on customer_id) and customers to tickets (one-to-many on customer_id). Create measures for average interaction duration and average resolution time.

SQL

Load the SQL dump into a SQLite or other SQL database. Use the customer_id column to join tables: customers to interactions and customers to tickets. Indexes on customer_id will improve query performance.

• 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