Hey Gang,

Thought I'd shoot a quick video on something that I was working on the other day when I was trying to do a content audit on some of my sites. Here is the video:

(Experimental And Unedited) Step-by-Step Content Audit Guide

Introduction

• Purpose: To conduct a content audit on a website to optimize traffic from Pinterest.

• Inspiration: Based on a content audit method shared by Matt Diggity for Google.

Step 1: Access Google Analytics

• Navigate to Google Analytics.

• Go to the Home Section.

Step 2: View Traffic Acquisition

• Click on Reports.

• Select Acquisition, then Traffic Acquisition.

Step 3: Filter for Pinterest Traffic

• Scroll down to view sessions by primary channel.

• Focus on Session Source/Medium.

• Type "Pinterest" in the search bar to filter results.

Step 4: Sort Data

• Choose the desired date range (last 28 days, 60 days, 90 days, or 12 months).

• Click on Share this export to download the data.

Step 5: Export Data

• Select to export the data to Google Sheets or download as a CSV file.

Step 6: Combine Duplicate URLs

• Identify and combine sessions from similar URLs to get a comprehensive view.

• Note the number of sessions from various regions (e.g., U.S., Canada, UK).

Step 7: Use Python for Data Processing

• Employ a Python script (provided by the speaker) to aggregate session data.

• Input the downloaded data into the script to consolidate the traffic data.

Step 8: Prune Low-Performing Content

• Analyze the consolidated data to identify URLs with less than 30 clicks.

• Prune content that doesn't meet the performance threshold.

Conclusion

• Purpose of the audit: To understand what content is performing well on Pinterest and what isn't.

• Encouragement to reach out with questions or for further assistance.

Closing Remarks

• Invite feedback on the usefulness of the guide.

ChatGPT Thread: https://chatgpt.com/share/67b600d3-2338-8006-9d67-ba6048066c68

Script:

import pandas as pd

# Read the CSV file (update the filename if needed)
input_file = 'ga4_data.csv'
df = pd.read_csv(input_file)

# Display the first few rows to check that columns are loaded correctly
print("Initial data preview:")
print(df.head())

# Group by the URL column and sum the sessions
# This assumes that your sessions column is numeric
aggregated_df = df.groupby('URL', as_index=False).agg({'Sessions': 'sum'})

# If you have other columns that you want to keep, you may need to adjust this step.
# For example, if you want to keep the first occurrence of other columns:
# aggregated_df = df.groupby('URL', as_index=False).agg({'Sessions': 'sum', 'OtherColumn': 'first', ...})

# Save the aggregated data to a new CSV file
output_file = 'aggregated_ga4_data.csv'
aggregated_df.to_csv(output_file, index=False)

print(f"Aggregated data has been saved to {output_file}")