Click the link to use the template.
Introduce
If a company advertises across numerous channels, the customer characteristics of each channel might differ.
For instance, the effectiveness of advertising on Youku Video could vary from that on Today’s Headlines. It is necessary to perform targeted measurements and optimizations of ad effectiveness.
In this case, we categorize channels based on features such as daily average UV over 90 days, average registration rate, average search volume, visit depth, average dwell time, order conversion rate, ad duration, creative type, ad type, collaboration method, ad size, and ad selling points. This assists in identifying key features of each channel, supporting business discussions and data analysis.
⭕️ Overview of Data Dimensions
In addition to the unique channel identifier, there are 12 dimensions across 889 rows, with missing and anomalous values.
⭕️ Introduction to the 13 Data Dimensions
1. Channel Code: Unique identifier for the channel
2. Daily Average UV: Daily unique visits
3. Average Registration Rate: Daily average registered users / Average daily visits
4. Average Search Volume: Searches per visit
5. Visit Depth: Total page views / Average daily visits
6. Average Dwell Time: Total dwell time / Average daily visits
7. Order Conversion Rate: Total number of orders / Average daily visitors
8. Ad Duration: Number of days an ad is shown externally
9. Creative Type: e.g., 'jpg', 'swf', 'gif', 'sp'
10. Ad Type: e.g., banner, tips, uncertain, horizontal, pause
11. Collaboration Method: e.g., 'roi', 'cpc', 'cpm', 'cpd'
12. Ad Size: e.g., '14040', '308388', '450300', '60090', '480360', '960126', '900120', '390270'
13. Ad Selling Points: Discounts, full reduction, gift with purchase, flash sale, direct price drop, cashback with purchase
Step1. 1. Correlation Analysis and Confirmation of Model Evaluation Fields
1.1 Correlation Analysis
prompt:
Import data: Read the data_ad_performance.csv.xlsx
Generate a correlation analysis table on the fields "Daily Average UV", "Average Registration Rate", "Average Search Volume", "Visit Depth", "Average Dwell Time", "Order Conversion Rate", "Total Ad Duration",
retaining data to 2 decimal places (make sure it's in table format). Display the analysis result with table
1.2 Correlation Visualization
prompt:
Import data: Read the data_ad_performance.csv.xlsx
Generate a correlation visual analysis on the fields "Daily Average UV", "Average Registration Rate", "Average Search Volume", "Visit Depth", "Average Dwell Time", "Order Conversion Rate", "Total Ad Duration".
Chart Beautification:
1. Use color scheme of pink and light purple.
2. Display X-axis and Y-axis lines using 1pt lines, with grey color.
3. Display correlation values in the workspace (data retaining 2 decimal places).
AI Chart Interpretation
The correlation heatmap reveals several interesting relationships among the variables:
Visit Depth and Average Dwell Time: There is a strong positive correlation of 0.72, suggesting that as users spend more time on the site, they also tend to explore more pages.
Order Conversion Rate and Average Registration Rate: There's a moderate positive correlation of 0.32, indicating that higher registration rates might be linked with better order conversions.
General Weak Correlations: Most of the other variables exhibit weak correlations (near zero), implying limited linear relationships between them.
1.3 Correlation Analysis Conclusion:
It can be seen that "Visit Depth" and "Average Dwell Time" have a high correlation. A high correlation indicates these two variables work the same or have the same effect when building the model, so one can consider combining or deleting one of them.
Step 2. Data Cleaning and Confirmation of the Best Model Evaluation Metrics
2.1 Data Standardization
prompt:
1. Read data_ad_performance.csv.xlsx , delete unnecessary columns, and remove the "Average Dwell Time" column.
2. Analyze the values of categorical variables: "Creative Type", "Ad Type", "Collaboration Method", "Ad Size", and "Ad Selling Points" to determine the unique values for each field.
3. One-Hot Encoding: Convert categorical variables obtained from the value analysis into one-hot encoded format. Create a OneHotEncode object to get the matrix to be converted, and create a MinMaxScaler model object.
4. MinMaxScaler Data Standardization: Standardize numerical fields and scale data to a uniform range. Save the processed data as a new table named "ad_processed_data".
The One Hot Encoder constructor includes a parameter named sparse, which is no longer supported in the current version. You can use the sparse_output parameter instead. All the filed_name or Varience shall be English
2.3 Building the Model: Confirm the Best Evaluation Metric K-value
prompt:
Process ad_processed_data.xlsxad_processed_data.xlsx according to the requirements below to find the best KMeans clustering parameter K.
Initialization:
Prepare a list score_list to store the average silhouette coefficient for different K values. Set an initial value of -1 to store the highest silhouette coefficient.
1. Testing Different K values:
For each K value from 0 to 7, calculate the corresponding silhouette coefficient:
2. Create a KMeans model, specifying the current K value.
Cluster the data X using the model and obtain the labels for each data point.
Calculate the average silhouette coefficient silhouette_tmp for this K value.
Check and Save the Best Result:
If the current silhouette coefficient silhouette_tmp is higher than the previous highest value:
3. Update the best K value to the current n_clusters.
Update the highest silhouette coefficient to silhouette_tmp.
4. Save the current K model and the corresponding clustering results.
Output the maximum n_clusters and corresponding silhouette coefficient in the preview results. Then, using this "maximum KMeans clustering model" n_clusters and corresponding silhouette coefficient, label each observation (sample) in the original data table ad_processed_data.xlsxad_processed_data.xlsx by creating a new column named "cluster", then save the processed result as a table named "cluster_labels". All the field name shall be English
2.4 Assign classification labels to samples
prompt:
Readcluster_labels.xlsx nd process it according to the following logic, then output the results in a table format. Note that fields "Average Registration Rate" and "Order Conversion Rate" may contain values close to zero, so ensure the final results are not all zero. Maintain two decimal places for all data, except for "Average Registration Rate" and "Order Conversion Rate," which should use per mille (‰) as the unit.
For each "cluster," calculate the following statistics: "Sample Size," "Sample Proportion," "Daily Average UV," "Average Registration Rate," "Average Search Volume," "Visit Depth,""Order Conversion Rate," "Total Ad Duration," "Creative Type," "Ad Type," "Collaboration Method," "Ad Size," and "Ad Selling Points." Exclude other fields.
Numerical Data:
Sample Size, Sample Proportion, Daily Average UV, Average Registration Rate, Average Search Volume, Visit Depth, Order Conversion Rate, Total Ad Duration.
String Data:
Creative Type, Ad Type, Collaboration Method, Ad Size, Ad Selling Points.
Processing steps:
Integrate the original data with clustering labels.
Calculate the sample size and sample proportion for each cluster.
Compute the most significant feature values within each cluster category:
For numerical data, extract the mean.
For string data, extract the most frequently occurring value (mode).
Output the complete characteristic information for each category:
Integrate the sample size, sample proportion, and significant feature values for each cluster.
Present the information in a table format displaying the main characteristics of each clustering category.
Save the result as "Cluster Feature Analysis Table" to the local file.
Note that Sample Size, Sample Proportion obtain by culculate
Step3. Chart Drawing and Analysis
3.1 Generate Radar Chart for Cluster Result Feature Analysis and Display
prompt:
Read Cluster Feature Analysis Table.xlsx and preprocess the data for fields "Daily Average UV", "Average Registration Rate", "Average Search Volume", "Visit Depth", "Order Conversion Rate", "Total Ad Duration", then draw the radar chart:
Data Preparation:
Extract and convert the data to be displayed into standardized form.
Ensure that data for each category is within the same range for comparison.
Create Canvas and Axes:
Create a square canvas, set size (e.g., 7x7).
Add polar coordinates for drawing the radar chart.
Set Labels and Colors:
Define data labels, display part of the key data, font color as black.
Define different colors for each category for distinction. Use pink, purple, yellow, blue, green for filling colors, and 1pt for lines.
Calculate Angles:
Calculate the polar coordinate angle for each category.
Ensure the start and end points are the same for shape closure.
Draw Radar Chart:
Loop through each category's data, connect data points into a closed polygon.
Use different colors to draw the boundary line for each category and fill the interior area.
Clearly label the specific cluster number, e.g., cluster 0, with the corresponding fill color.
Set Chart Display Format:
Use set_thetagrids to set angle labels on polar coordinates with appropriate font.
Use set_title to set the chart title.
Use set_rlim to set the radius range of polar coordinates to ensure data is reasonably displayed.
Use plt.legend to set the legend position for better understanding of the chart.
Adjust Canvas Coordinate Values:
Disperse the values inside the radar chart to avoid overcrowding in the central area, ensuring clear visualization.
Save the standardized table locally, named "Radar Chart - Standardized Data".
3.2 Data Conclusion
prompt:
Merge the contents of Cluster Feature Analysis Table.xlsx and Radar Chart - Standardized Data.xlsx into one file with different sheets, named " Merged_Analysis_Data.xlsx.", and save it locally.
Analyze this table, mainly focusing on "Daily Average UV", "Average Registration Rate", "Average Search Volume", "Visit Depth", "Order Conversion Rate", "Total Ad Duration", and channel effectiveness.
