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:
https://www.loom.com/share/cfbe1158f02d4e97bfe6d1ec295da5e7
(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 pdRead 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}")