Customer Data Insight Example

Customer Example Request - Generate basic insights

I’ve been looking at my data since the start of 2023, but I can’t make much sense of it. I’d appreciate a quick run down please. It’s been a crazy old time as we reopen post-Covid. Firstly, what are the total sales for each month? We’re also about to undertake a product supplier review. To aid us in this effort can you please provide a view of our top selling product categories over this period, broken down by brand? Our marketing team is trying to convince me that we should focus on improving our membership numbers. They are adamant that members have higher average basket sizes and average order values than non-members. I’m not sure. What do you think? If we do go ahead with that campaign, we’ll need to decide whether we focus in-state or out-of-state. It would be useful to understand our current membership composition. Can you tell with the data we have? Thank you!

Examine Data

SELECT * FROM 'transactions.csv'

Run to view results

SELECT * FROM 'members.csv'

Run to view results

Load Data

import pandas as pd # Load the CSV file into a DataFrame df = pd.read_csv('transactions.csv') # Display the columns columns_list = df.columns.tolist() print(columns_list)

Run to view results

# Calculate the line_total df['line_total'] = (df['line_item_unit_price'] - df['line_item_unit_discount']) * df['line_item_quantity'] # Use order_date df['order_date'] = pd.to_datetime(df['order_date']) df['month'] = df['order_date'].dt.to_period('M') # Extract month from the 'order_date' # Group by month and calculate total sales monthly_sales = df.groupby('month')['line_total'].sum().reset_index() # Display the total sales for each month print(monthly_sales)

Run to view results

Total Monthly Sales

Run to view results

import pandas as pd # Use order_date df['line_total'] = (df['line_item_unit_price'] - df['line_item_unit_discount']) * df['line_item_quantity'] # Group by product category and brand, calculate total sales top_selling_products = df.groupby(['line_item_product_type', 'line_item_product_brand'])['line_total'].sum().reset_index() # Display the top-selling product categories by brand print(top_selling_products)

Run to view results

Top Selling Product Categories by Brand

Run to view results

# Assuming 'order_date' is in datetime format df['order_date'] = pd.to_datetime(df['order_date']) # Calculate the line_total df['line_total'] = (df['line_item_quantity'] * df['line_item_unit_price'] - df['line_item_unit_discount']) # Create a new column to indicate membership status df['membership_status'] = df['member_id'].apply(lambda x: 'Member' if pd.notnull(x) else 'Non-Member') # Group by membership status and calculate average totals avg_totals = df.groupby('membership_status')['line_total'].mean().reset_index() # Display the average totals for members and non-members print(avg_totals)

Run to view results

Member Average Basket Total

The customer's assumptions seem to be correct. The marketing team is incorrect.

Run to view results

import pandas as pd # Load the CSV file into a DataFrame df_members = pd.read_csv('members.csv') # Display the columns columns_list = df_members.columns.tolist() print(columns_list)

Run to view results

# Merge the two DataFrames on 'member_id' df_combined = pd.merge(df, df_members[['member_id', 'zip_code', 'date_of_birth']], on='member_id', how='left') # Drop rows where 'member_id' is NaN (non-members) df_combined = df_combined.dropna(subset=['member_id']) # Group by 'order_id' and 'zip_code' and calculate total sales for each order order_totals = df_combined.groupby(['order_id', 'zip_code'])['line_total'].sum().reset_index() # Group by 'zip_code' and calculate total sales for each zip code zip_code_totals = order_totals.groupby('zip_code')['line_total'].sum().reset_index() # Sort the zip_code_totals DataFrame from high to low sum zip_code_totals = zip_code_totals.sort_values(by='line_total', ascending=False) # Display the updated DataFrame print(zip_code_totals)

Run to view results

Zip Code

Aggregate sales amount by Zip Code to help show high sales/rev

Run to view results

# Calculate age from 'date_of_birth' df_combined['date_of_birth'] = pd.to_datetime(df_combined['date_of_birth']) df_combined['age'] = (pd.to_datetime('today') - df_combined['date_of_birth']).astype('<m8[Y]') # Display the updated DataFrame with age print(df_combined[['member_id', 'zip_code', 'line_total', 'age']])

Run to view results

Individual Order Selector

OrderIDSelector

# Filter the DataFrame to show only returns df_selected = df[df['order_id'] == int(OrderIDSelector)] print(f"Data for Order ID: {OrderIDSelector}") # Display each line with columns separately for index, row in df_selected.iterrows(): print(f"Row {index + 1}:") for column, value in row.items(): print(f" {column}: {value}")

Run to view results

-- Calculate average basket size for members and non-members SELECT AVG(CASE WHEN member_id IS NOT NULL THEN items_per_basket ELSE NULL END) AS avg_basket_size_member, AVG(CASE WHEN member_id IS NULL THEN items_per_basket ELSE NULL END) AS avg_basket_size_non_member FROM ( SELECT order_id, SUM(line_item_quantity) AS items_per_basket, member_id FROM 'transactions.csv' GROUP BY order_id, member_id ) AS all_baskets;

Run to view results

pip install flask google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client

Run to view results

pip install google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client

Run to view results

from googleapiclient.discovery import build # Replace with your API key API_KEY = 'AIzaSyDfmm-tUVI34Xpk5ozp59CUSvUw_fk_gWw' PLAYLIST_ID = 'PLBYlvP0phaiW55q3qkyurT6gzMfLxiqdZ' # Your playlist ID def get_playlist_videos(playlist_id): youtube = build('youtube', 'v3', developerKey=API_KEY) request = youtube.playlistItems().list( part='snippet', maxResults=50, playlistId=playlist_id ) response = request.execute() videos = [] while request is not None: response = request.execute() for item in response['items']: video = { 'title': item['snippet']['title'], 'url': f"https://www.youtube.com/watch?v={item['snippet']['resourceId']['videoId']}", 'addedAt': item['snippet']['publishedAt'] } videos.append(video) request = youtube.playlistItems().list_next(request, response) # Sort videos by the date they were added videos.sort(key=lambda x: x['addedAt']) return videos if __name__ == '__main__': videos = get_playlist_videos(PLAYLIST_ID) for video in videos: print(f"Title: {video['title']}") print(f"URL: {video['url']}") print(f"Added At: {video['addedAt']}\n")

Run to view results

Examine Data

Load Data

.css-hdxizt{color:var(--chakra-colors-fg-neutral-primary);font-weight:var(--chakra-fontWeights-bold);letter-spacing:-0.09px;}Total Monthly Sales

Top Selling Product Categories by Brand

Member Average Basket Total

Zip Code

Individual Order Selector

Total Monthly Sales