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'

SELECT * FROM 'members.csv'

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)

# 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)

Total Monthly Sales

Run the app to see this chart

Press the run button in the top right corner

Run the app to see this chart

Press the run button in the top right corner

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)

Top Selling Product Categories by Brand

Run the app to see this chart

Press the run button in the top right corner

Run the app to see this chart

Press the run button in the top right corner

# 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)

Member Average Basket Total

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

Run the app to see this chart

Press the run button in the top right corner

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)

# 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)

Zip Code

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

Run the app to see this chart

Press the run button in the top right corner

Run the app to see this chart

Press the run button in the top right corner

# 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 the app to see this chart

Press the run button in the top right corner

Run the app to see this chart

Press the run button in the top right corner

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}")

-- 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;

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

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

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")