Account Snapshot Python Script

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