Customer Data Analytics

Data Schema

Explore the structure of our datasets and how they are interconnected for powerful analytics.

Data Schema

Below is the schema of how our datasets are related:

Customer Data

  • customer_id - VARCHAR(10) PRIMARY KEY
  • first_name - VARCHAR(50)
  • last_name - VARCHAR(50)
  • email - VARCHAR(100)
  • phone_number - VARCHAR(15)
  • created_date - DATE
  • country_id - VARCHAR(5)
Sample Records
Customer ID First Name Last Name Email Phone Number Created Date Country
CUST001 Alice Smith alice.smith@example.com +1234567890 2025-01-10 USA
CUST002 Bob Jones bob.jones@example.com +1987654321 2025-02-15 Canada
CUST003 Charlie Brown charlie.brown@example.com +1472583690 2025-03-20 UK
CUST004 David Wilson david.wilson@example.com +1122334455 2025-04-25 Australia
CUST005 Eva Taylor eva.taylor@example.com +1098765432 2025-05-01 Germany

E-commerce Purchases

  • order_id - VARCHAR(10) PRIMARY KEY
  • customer_id - VARCHAR(10), FOREIGN KEY REFERENCES customer_crm(customer_id)
  • product_id - VARCHAR(10)
  • amount - DECIMAL(10,2)
  • purchase_date - DATE
  • payment_method - VARCHAR(50)
Sample Records
Order ID Customer ID Product ID Amount Purchase Date Payment Method
ORD001 CUST001 PROD101 1205.85 2025-05-22 Credit Card
ORD002 CUST002 PROD102 600.00 2025-05-23 PayPal
ORD003 CUST002 PROD103 666.62 2025-05-23 Credit Card
ORD004 CUST003 PROD104 150.00 2025-05-24 Debit Card
ORD005 CUST005 PROD105 200.00 2025-05-26 Credit Card

Support Tickets

  • ticket_id - VARCHAR(10) PRIMARY KEY
  • customer_id - VARCHAR(10), FOREIGN KEY REFERENCES customer_crm(customer_id)
  • issue_type - VARCHAR(50)
  • status - VARCHAR(20)
  • opened_date - DATE
  • closed_date - DATE
Sample Records
Ticket ID Customer ID Issue Type Status Opened Date Closed Date
TICK001 CUST001 Billing Open 2025-05-20 -
TICK002 CUST002 Product Issue Open 2025-05-21 -
TICK003 CUST002 Technical Open 2025-05-22 -
TICK004 CUST003 Delivery Open 2025-05-23 -
TICK005 CUST005 Billing Resolved 2025-05-25 2025-05-26

Marketing Contacts

  • campaign_id - VARCHAR(10) PRIMARY KEY
  • customer_id - VARCHAR(10), FOREIGN KEY REFERENCES customer_crm(customer_id)
  • channel - VARCHAR(50)
  • interaction_date - DATE
  • action_taken - VARCHAR(50)
Sample Records
Campaign ID Customer ID Channel Interaction Date Action Taken
CAMP001 CUST001 Email 2025-05-18 Clicked
CAMP002 CUST002 SMS 2025-05-19 Ignored
CAMP003 CUST003 Email 2025-05-20 Clicked
CAMP004 CUST004 Push 2025-05-21 Clicked
CAMP005 CUST005 Email 2025-05-22 Purchased

Customer Summary Metrics

  • customer_id - VARCHAR(10) PRIMARY KEY
  • full_name - VARCHAR(100)
  • email - VARCHAR(100)
  • phone_number - VARCHAR(15)
  • total_purchases - INT
  • total_spent - DECIMAL(10,2)
  • last_purchase_date - DATE
  • total_support_tickets - INT
  • last_ticket_status - VARCHAR(20)
  • last_campaign_action - VARCHAR(50)
Sample Records
Customer ID Full Name Email Phone Total Purchases Total Spent Last Purchase Date Support Tickets Last Ticket Status Last Campaign Action
CUST001 Alice Smith alice.smith@example.com +1234567890 1 1205.85 2025-05-22 1 Open Clicked
CUST002 Bob Jones bob.jones@example.com +1987654321 2 1266.62 2025-05-23 2 Open Ignored
CUST003 Charlie Brown charlie.brown@example.com +1472583690 5 750.73 2025-05-24 3 Open Clicked
CUST004 David Wilson david.wilson@example.com +1122334455 3 1718.99 2025-05-25 0 Resolved Clicked
CUST005 Eva Taylor eva.taylor@example.com +1098765432 4 665.23 2025-05-26 2 Resolved Purchased