Owner
Marc Pfeiffer
Tags
Python
Last edited time
Sep 29, 2023 1:43 PM
GOAL: We want to upload our business reports data and sponsored ads data for the same time frame and understand at a Child ASIN level what the key metrics are. Ideally this is being done automatically in an internal company dashboard which then would allow you to dive into an ASIN. Also at a higher level viewing the performance at a Category level vs a child ASIN level is helpful. For the purposes of this script we are just looking at the ASIN level.
Necessary files needed
- Amazon Advertising Bulk File
- Business Reports Data
Group these two excel files into one excel sheet and call it “AMZ ACCOUNT OVERVIEW” then run this python script below to get an overview like this for the performance of products in your Amazon account catalog
Here’s the code - You will need to adjust the location of the where the excel file is located as it won’t be the same as mine
import pandas as pd
# Load the Excel file
file_path = r"C:\Users\marcp\PycharmProjects\shopsight-kw-checker\AMZ ACCOUNT OVERVIEW.xlsx"
xl = pd.ExcelFile(file_path)
# Read the "BusinessReport" and "Sponsored Products Campaigns" tabs into DataFrames
business_report_df = xl.parse('BusinessReport')
sponsored_products_df = xl.parse('Sponsored Products Campaigns')
# Extract unique ASINs from the "BusinessReport" tab
unique_asins = business_report_df['(Child) ASIN'].unique()
# Initialize the "Summary" DataFrame with unique ASINs
summary_df = pd.DataFrame(unique_asins, columns=['(Child) ASIN'])
# Map the sales, ad spend, and ad sales data for each ASIN
summary_df['Sales'] = summary_df['(Child) ASIN'].map(
business_report_df.groupby('(Child) ASIN')['Ordered Product Sales'].sum())
summary_df['Ad Spend'] = summary_df['(Child) ASIN'].map(
sponsored_products_df.groupby('ASIN (Informational only)')['Spend'].sum())
summary_df['Ad Sales'] = summary_df['(Child) ASIN'].map(
sponsored_products_df.groupby('ASIN (Informational only)')['Sales'].sum())
# Perform the required calculations
summary_df['% sales'] = (summary_df['Sales'] / summary_df['Sales'].sum())
summary_df['% Ad Spend'] = (summary_df['Ad Spend'] / summary_df['Ad Spend'].sum())
summary_df['ACOS'] = (summary_df['Ad Spend'] / summary_df['Ad Sales'])
summary_df['TACOS'] = (summary_df['Ad Spend'] / summary_df['Sales'])
summary_df['% of ad sales'] = (summary_df['Ad Sales'] / summary_df['Sales'])
# Sort by "% sales" descending
summary_df = summary_df.sort_values(by='% sales', ascending=False)
# Rearrange the columns to the specified order
column_order = ['(Child) ASIN', '% sales', 'Sales', '% Ad Spend', 'Ad Spend', 'Ad Sales', 'ACOS', 'TACOS',
'% of ad sales']
summary_df = summary_df[column_order]
# Create an Excel writer object
output_file_path = r"C:\Users\marcp\PycharmProjects\shopsight-kw-checker\AMZ ACCOUNT OVERVIEW - DONE.xlsx"
with pd.ExcelWriter(output_file_path, engine='xlsxwriter') as writer:
# Write the Summary DataFrame to Excel file
summary_df.to_excel(writer, sheet_name='Summary', index=False)
# Access the XlsxWriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Summary']
# Define formats
percent_format = workbook.add_format({'num_format': '0.0%'})
currency_format = workbook.add_format({'num_format': '$#,##0'})
# Apply formats to the columns
worksheet.set_column('B:B', None, percent_format)
worksheet.set_column('C:C', None, currency_format)
worksheet.set_column('D:D', None, percent_format)
worksheet.set_column('E:F', None, currency_format)
worksheet.set_column('G:I', None, percent_format)
# Define the conditional format
conditional_format = {
'type': '3_color_scale',
'max_color': "#CD0D0D", # red
'mid_color': "#F5EF03", # yellow
'min_color': "#4AA238", # green
}
# Get the number of rows in the summary DataFrame
num_rows = len(summary_df.index)
# Apply conditional formatting to "ACOS" and "TACOS" columns
worksheet.conditional_format(f'G2:G{num_rows + 1}', conditional_format)
worksheet.conditional_format(f'H2:H{num_rows + 1}', conditional_format)
# Write other tabs
for sheet in xl.sheet_names:
if sheet != 'Summary':
xl.parse(sheet).to_excel(writer, sheet_name=sheet, index=False)What can you do with this data?
- Shows you what the top products in the account are (% of overall sales) and how much of their sales come from ad spend.
- In terms of optimizing using the 80/20 approach here to focus on the top performers is usually where one can unlock the most value so I would recommend starting from the top down to go through an optimization checklist for the listing and then check the overall campaign structure for that ASIN.
- Shows where most of the ad spend is being spent - in terms of optimization focusing on big chunks of ad spend is where improvements can be made.
- Shows ACOS and TACOS. For new products the ACOS will be close to the TACOS since most sales will come from paid advertising. However over time you would want your % of paid sales to be closer to 45-50%. A paid sales % of 50% which an ACOS of 30% would equal a TACOS of 15% (ACOS x % of sales from paid = TACOS).
What it doesn’t show you
- Doesn’t show all the associated amazon fees. You could estimate Referral fees as 15% of sales (but this can vary depending on the category the product is in) and does not show the FBA fees associated with the product as well. Additionally return fees associated with a product are not shown.
- These are important to calculate a “Pre-Ad Gross Margin” which is the Margin you have before any ad spend. For example if your Pre-Ad Gross Margin is $20 then it means you can afford to spend $20 as your CAC to breakeven and not make a profit. Knowing what your Pre-Ad Gross Margin is as a % will help you to assess profitability = Pre-Ad Gross Margin % - TACOS = Contribution Profit Margin.
- Doesn’t show how much upside a product may have
- Can the main image be improved?
- How is the product doing vs it’s competition?
- What do the secondary images look like and can they be improved to increase sales?
- Doesn’t show you information of your Customer Lifetime Value - this will be more important for consummable base brands