Data Cleaning and Preparation for Search Terms Analysis

Aug 30, 2024, 6:07 PM

Search_terms-Sol8.xlsx

Missing file

Please clean and prepare the data in this sheet

Certainly! I'll clean and prepare the data from the 'Search_terms-Sol8.xlsx' file. Let's start by loading the data, examining its structure, and then cleaning it up. I'll walk you through the process step by step.
Now that we have loaded and examined the data, let's proceed with cleaning and preparing it. We'll focus on the following steps:
  1. Convert percentage columns to numeric values
  2. Convert numeric columns to appropriate data types
  3. Handle any missing or inconsistent values
  4. Rename columns for clarity
  5. Remove any unnecessary columns (if any)
Let's start with the data cleaning process:
Based on the cleaning and preparation steps we've taken, along with the summary of the data, we can conclude that the data has been successfully cleaned and prepared. Here's a summary of what we've accomplished:
  1. We've loaded the data from the 'Search_terms-Sol8.xlsx' file.
  2. We've converted percentage columns (CTR, Impr. (Abs. Top) %, Impr. (Top) %, Conv. rate) to numeric values.
  3. We've converted other numeric columns to appropriate data types.
  4. We've renamed some columns for clarity (e.g., Impr. to impressions, Avg. CPC to average_cpc).
  5. We've checked for missing values and unique values in categorical columns.
The cleaned dataset (clean_df) now has:
  • Properly formatted numeric columns
  • Renamed columns for better readability
  • Consistent data types across columns
Some observations from the data:
  • The dataset contains 2,166 rows.
  • There are four unique values for 'Search keyword status': Not eligible, Paused, Removed, and Eligible.
  • There are two types of 'Search keyword match type': Broad match and Phrase match.
  • All entries use USD as the currency code.
The data is now clean, consistent, and ready for further analysis or visualization. Is there any specific analysis or visualization you'd like to perform with this cleaned dataset?

Can you provide a summary of the key metrics in the dataset, such as total clicks, impressions, and conversions?

Based on the summary statistics we've calculated, I can provide you with a comprehensive overview of the key metrics in the dataset. Here's a summary of the most important findings:
Overall Campaign Performance:
  • Total Clicks: 384
  • Total Impressions: 4,226
  • Total Conversions: 4
  • Total Cost: $903 (assuming USD)
  • Total Conversion Value: $0 (This might indicate that conversion value tracking is not set up properly)
Key Performance Indicators (KPIs):
  • Click-Through Rate (CTR): 9.09% (384 clicks / 4,226 impressions)
  • Conversion Rate: 1.04% (4 conversions / 384 clicks)
  • Cost per Click (CPC): 2.35(2.35 (903 / 384 clicks)
  • Cost per Conversion: 225.75(225.75 (903 / 4 conversions)
  • Cost per Mille (CPM): 213.68(213.68 (903 / 4,226 impressions * 1000)
Additional Insights:
  • Total Keywords: 2,166
  • Keywords with Clicks: 179 (8.26% of total keywords)
  • Keywords with Conversions: 2 (0.09% of total keywords)
  • Maximum Clicks for a Single Keyword: 64
  • Maximum Impressions for a Single Keyword: 2
  • Maximum Conversions for a Single Keyword: 2
Analysis:
  1. The campaign has a relatively low number of total clicks and conversions compared to the number of keywords, suggesting that many keywords are not performing well.
  2. Only 8.26% of keywords have received any clicks, and an even smaller percentage (0.09%) have led to conversions. This indicates that there's room for optimization in keyword selection and targeting.
  3. The overall conversion rate is low at 1.04%, which might suggest issues with landing page quality, ad relevance, or targeting.
  4. The cost per conversion is relatively high at $225.75, which might not be sustainable depending on the business model and the value of each conversion.
  5. There's a significant disparity between the best-performing keyword (64 clicks) and the average, suggesting that focusing on top-performing keywords could improve overall campaign performance.
Based on these metrics, it would be advisable to review the keyword list, focus on the best-performing keywords, improve ad relevance and landing page quality, and possibly adjust bidding strategies to optimize the campaign's performance.
Would you like me to provide any specific recommendations for improving the campaign based on these metrics?

Can you create a visualization of the top 10 keywords by clicks?

I have created a visualization of the top 10 keywords by clicks as requested. The interactive bar chart above shows the following information:
  1. The y-axis displays the search terms (keywords).
  2. The x-axis shows the number of clicks for each keyword.
  3. The keywords are sorted in descending order based on the number of clicks.
From this visualization, we can observe:
  • The top-performing keyword in terms of clicks is "diamond rings".
  • There's a significant drop in clicks between the top keyword and the others, indicating that "diamond rings" is performing exceptionally well compared to the rest.
  • The top 10 keywords are all related to diamond rings or engagement rings, suggesting that these are the most popular search terms in your campaign.
This visualization provides a clear picture of which keywords are driving the most traffic to your ads. It can help you focus your efforts on optimizing these high-performing keywords and potentially allocating more budget to them.
Would you like to explore any other aspects of the data or perform additional analysis on these top-performing keywords?

Can you show me the top 20 keywords ranked by conversion value

Thank you for the reminder. I have already provided the visualization and data for the top 20 keywords ranked by conversion value. However, I'd like to offer some additional insights based on the results:
  1. Limited Conversion Value: Out of the top 20 keywords, only two have any conversion value:
    • "haute carat" with a conversion value of 2
    • "hautecarat" also with a conversion value of 2
  2. Low Overall Conversion: This suggests that the campaign is struggling with conversions, as only 2 out of 2,166 keywords have generated any conversion value.
  3. Potential Misspelling: The two converting keywords are very similar ("haute carat" and "hautecarat"), which might indicate a brand name or a potential misspelling that's performing well.
  4. Cost Efficiency: "haute carat" had a higher cost (128) compared to "hautecarat" (57.4), despite having the same conversion value. This suggests that "hautecarat" might be more cost-effective.
  5. Click-Through Rate (CTR): Both converting keywords have relatively high CTRs (0.552 and 0.5), indicating that they're relevant to users' searches.
  6. Zero Performance: The remaining 18 keywords in the top 20 have zero conversion value, zero conversions, zero cost, and zero CTR. This could indicate that these keywords are not being bid on or are not receiving any impressions.
Recommendations:
  1. Focus on optimizing the two converting keywords ("haute carat" and "hautecarat").
  2. Investigate why other keywords are not performing and consider pausing or removing them.
  3. Look for similar keywords or variations of the converting keywords to expand on what's working.
  4. Review your bidding strategy and budget allocation to ensure potentially valuable keywords are getting impressions.
Would you like me to perform any further analysis on these keywords or explore other aspects of the campaign?
Get started with Vizly