SEMRush.Trends Market Explorer All Domains Report

SEMRush’s recent update to the “Trends Market Explorer All Domains Report” dimensions involves significant changes that necessitate a strategic realignment of business intelligence (BI) systems, such as Power BI. These updates have profound implications for how businesses track, analyze, and report on digital marketing metrics. Here’s an in-depth look at the strategic implications of these changes, focusing on the necessity of adapting mapping structures, preserving data history, and understanding the implications of these adjustments.

Vital Mapping from New to Old Dimensions: The updates involve more than just a renaming of metrics; they represent a shift in how data is categorized and analyzed. It is crucial for organizations to map these new dimensions back to the old ones where necessary to maintain continuity in long-term data analysis and reporting. This mapping ensures that historical data remains relevant and comparable, facilitating trend analysis and strategic decision-making.

Preservation of Data History: One of the biggest challenges with any change in data reporting is maintaining an unbroken historical record that allows for accurate year-over-year comparisons. Businesses must adjust their BI tools to integrate new data while preserving the old metrics in some form, ensuring that historical data is not lost but instead enriched with the new structures.

Understanding Changes for Strategic Adjustments: Fully grasping the scope and impact of these changes is essential for businesses to adapt their strategies effectively. The detailed categorization of traffic sources, for instance, allows for more precise targeting in SEO and paid advertising strategies. Understanding these changes can help businesses refine their tactics to better meet their objectives.

Update BI Tools: Ensure that tools like Power BI are updated to reflect the new SEMRush dimensions. This might include updating data import scripts, dashboards, and visualizations to incorporate the new terminology and structures.

Train Your Team: Educate your analytics and marketing teams on the new dimensions and what they mean for your ongoing strategies. Understanding these changes can help in leveraging the full potential of the data provided.

Audit Historical Data: Conduct audits of your historical data to ensure that it aligns well with the new data structures. This may involve creating parallel tracking for a period to ensure that the transition is smooth and that data integrity is maintained.

The changes introduced by SEMRush in the reporting dimensions require a thoughtful approach to data management and strategy adjustment in BI tools. By effectively mapping new metrics to old, preserving historical data integrity, and fully understanding the implications of these changes, businesses can enhance their decision-making processes and maintain a competitive edge in the digital marketplace.

Mapping.Old	Mapping.New
Average Visit Duration
Average Visit Duration change	Avg. Visit Duration change
Average Visit Duration change %	Avg. Visit Duration change %
Average Visit Duration current	Avg. Visit Duration current
Average Visit Duration previous	Avg. Visit Duration previous
Bounce Rate
Bounce Rate change	Bounce Rate change
Bounce Rate change %	Bounce Rate change %
Bounce Rate current	Bounce Rate current
Bounce Rate previous	Bounce Rate previous
Conversion
Conversion change	Purchase Conversion change
Conversion change %	Purchase Conversion change %
Conversion current	Purchase Conversion current
Conversion previous	Purchase Conversion previous
Direct
Direct change	Direct Traffic change
Direct change %	Direct Traffic change %
Direct current	Direct Traffic current
Direct previous	Direct Traffic previous
Display Ads
Display Ads change	Display Ads Traffic change
Display Ads change %	Display Ads Traffic change %
Display Ads current	Display Ads Traffic current
Display Ads previous	Display Ads Traffic previous
Domain	Domain
Email
Email change	Email Traffic change
Email change %	Email Traffic change %
Email current	Email Traffic current
Email previous	Email Traffic previous
Organic Search
Organic Search change	Organic Search Traffic change
Organic Search change %	Organic Search Traffic change %
Organic Search current	Organic Search Traffic current
Organic Search previous	Organic Search Traffic previous
Organic Social
Organic Social change	Organic Social Traffic change
Organic Social change %	Organic Social Traffic change %
Organic Social current	Organic Social Traffic current
Organic Social previous	Organic Social Traffic previous
Pages Per Visit
Pages Per Visit change	Pages / Visit change
Pages Per Visit change %	Pages / Visit change %
Pages Per Visit current	Pages / Visit current
Pages Per Visit prev	Pages / Visit previous
Paid Search
Paid Search change	Paid Search Traffic change
Paid Search change %	Paid Search Traffic change %
Paid Search current	Paid Search Traffic current
Paid Search previous	Paid Search Traffic previous
Paid Social
Paid Social change	Paid Social Traffic change
Paid Social change %	Paid Social Traffic change %
Paid Social current	Paid Social Traffic current
Paid Social previous	Paid Social Traffic previous
Referral
Referral change	Referral Traffic change
Referral change %	Referral Traffic change %
Referral current	Referral Traffic current
Referral previous	Referral Traffic previous
Share Of Visits	Share of Visits
Total
Total change	Total Traffic change
Total change %	Total Traffic change %
Total current	Total Traffic current
Total previous	Total Traffic previous
Unique Visitors
Unique Visitors change	Unique Visitors change
Unique Visitors change %	Unique Visitors change %
Unique Visitors current	Unique Visitors current
Unique Visitors previous	Unique Visitors previous

import pandas as pd
import os

def verify_file_path(path):
    """Check if a file exists at the given path and print a message."""
    if os.path.exists(path):
        print(f"File found: {path}")
        return True
    else:
        print(f"File not found: {path}")
        return False

def transform_and_prepare_for_power_bi(input_file_path, mapping_file_path, output_file_path):
    # Verify file paths
    if not verify_file_path(input_file_path) or not verify_file_path(mapping_file_path):
        return  # Exit the function if any file is missing

    # Load the mapping table with the correct delimiter
    mapping_data = pd.read_csv(mapping_file_path, delimiter=';')
    mapping_dict = mapping_data.dropna().set_index('Mapping.New')['Mapping.Old'].to_dict()

    # Load the data
    data = pd.read_csv(input_file_path)

    # Rename columns according to the mapping table, reindex to ensure all 'Mapping.Old' columns are present
    data_transformed = data.rename(columns=mapping_dict).reindex(columns=mapping_data['Mapping.Old'].tolist(), fill_value="")

    # Filling missing values - empty strings for text, 0 for numbers
    text_columns = data_transformed.select_dtypes(include=['object']).columns
    numeric_columns = data_transformed.select_dtypes(include=['number']).columns
    data_transformed[text_columns] = data_transformed[text_columns].fillna("")
    data_transformed[numeric_columns] = data_transformed[numeric_columns].fillna(0)

    # Save the transformed data
    data_transformed.to_csv(output_file_path, index=False, encoding='utf-8')
    print(f"Data transformed and saved successfully to {output_file_path}")

# Define file paths within the main block of the script to ensure they are recognized
input_file_path = 'Your Input File Path!\\2024-03-Worldwide.csv'
mapping_file_path = 'Your Mapping File Path!\\mapping-semrush.csv'
output_file_path = 'Your Output Path!\\Transformed_Data_for_PowerBI.csv'

# Run the transformation function
transform_and_prepare_for_power_bi(input_file_path, mapping_file_path, output_file_path)