More Than A Report: Free E-Commerce Script

Imagine you're the marketing manager for "any company.com" an online retailer specializing in sports equipment and accessories. You've been running Google Ads campaigns for various brands, but you're unsure which ones are truly driving your business forward. That's where our Enhanced Google Ads E-commerce Analysis Script comes in. Here's how it transformed any company.com' advertising strategy:

The Email Report: Unveiling Insights

Key Points:

1. Automated brand-level performance analysis for Google Ads Shopping campaigns
2. Data-driven insights and actionable recommendations
3. Comprehensive email report with link to detailed Google Sheet
4. Focus on ROAS, transaction volume, and cost efficiency
5. Identification of top performers, underperformers, and non-performing brands

Advantages Over Basic Reporting Scripts:

1. Actionable Insights: Goes beyond raw data to provide specific recommendations
2. Brand-Level Focus: Aggregates product data to give a clear view of brand performance
3. Performance Segmentation: Identifies top performers, underperformers, and non-performers
4. Budget Optimization: Suggests budget reallocation based on performance
5. Product Mix Analysis: Highlights top products within brands for inventory and promotion decisions
6. Cross-Selling Opportunities: Suggests strategies based on top-selling products
7. Automated Google Sheet Creation: Provides detailed data for further analysis

Key Formulas and Business Relevance:

1. ROAS (Return on Ad Spend) = Revenue / Cost Business Impact: Measures advertising efficiency. Higher ROAS indicates more revenue per ad dollar spent. Suggestion: Increase budget for high ROAS brands to scale successful campaigns.
2. Cost Per Transaction = Cost / Number of Transactions Business Impact: Indicates how much is spent to acquire each sale. Suggestion: Optimize or reduce spend on brands with high cost per transaction to improve profitability.
3. Transaction Volume = Sum of Transactions per Brand Business Impact: Shows which brands drive the most sales. Suggestion: Ensure adequate inventory and support for high-volume brands.
4. Revenue Per Transaction = Revenue / Number of Transactions Business Impact: Indicates average order value for each brand. Suggestion: Focus on increasing this metric through cross-selling and upselling strategies.
5. Budget Allocation Percentage = (Brand Cost / Total Cost) * 100 Business Impact: Shows how ad spend is distributed across brands. Suggestion: Reallocate budget from underperforming to top-performing brands for better overall ROAS.

Business Improvement Potential:

1. Increased ROAS: By focusing on top performers and optimizing underperformers, overall return on ad spend can be improved.
2. Cost Efficiency: Pausing non-performing brands and optimizing high-cost-per-transaction brands can reduce wasted spend.
3. Revenue Growth: Increasing budget for high-performing brands can drive more transactions and revenue.
4. Inventory Management: Insights on top-selling products help in maintaining optimal stock levels.
5. Strategic Decision Making: Data-driven insights enable informed decisions on budget allocation, product focus, and brand strategy.

Sample Email Output + Google Sheet:

Google Ads E-commerce Brand and Product Performance Analysis - LAST_30_DAYS

Overall Performance Summary
----------------------------
Total Transactions: 907.13
Total Cost: $31,157.31
Total Revenue: $109,553.15
Overall ROAS: 3.52
Overall Cost Per Transaction: $34.35
Number of Brands with Transactions: 55
Number of Brands without Transactions: 82

Top 10 Performing Brands (by Transactions)
-------------------------------------------
1. Football (Transactions: 378.12, ROAS: 3.83, Revenue: $46,313.63)
2. Baseball (Transactions: 59.33, ROAS: 3.72, Revenue: $6,124.76)
3. Bengaks (Transactions: 53.73, ROAS: 7.85, Revenue: $2,259.37)
4. Titans(Transactions: 50.81, ROAS: 2.21, Revenue: $11,760.20)
5. Soldiers (Transactions: 45.96, ROAS: 5.51, Revenue: $1,509.61)
6. Yosi (Transactions: 34.28, ROAS: 3.58, Revenue: $3,716.32)
7. Mickey (Transactions: 27.75, ROAS: 5.61, Revenue: $874.27)
8. Donald (Transactions: 23.98, ROAS: 2.50, Revenue: $2,766.30)
9. Pie (Transactions: 20.00, ROAS: 17.19, Revenue: $2,957.55)
10. Apple (Transactions: 19.98, ROAS: 20.32, Revenue: $1,786.16)

Brands to Consider Removing (No Transactions)
---------------------------------------------
[List of 82 brands]

Key Insights and Recommendations
----------------------------------
1. Focus on top-performing brands:
   - Baseball is your best performer with 378.12 transactions and a ROAS of 3.83.
   - Consider increasing its daily budget by 20% to capitalize on its success.

2. Optimize underperforming brands:
   - 25 brands are performing below the overall ROAS of 3.52.
   - Focus on Mickey first, which has a ROAS of only 2.21.
   - Review its pricing strategy and consider reducing its budget by 30% if improvements aren't seen within 2 weeks.

3. Remove non-performing brands:
   - 82 brands had no transactions.
   - Pause these brands immediately to stop unnecessary spend.
   - If you wish to retain any of these brands, consider creating a separate campaign with a minimal budget for testing.

4. Analyze product mix:
   - Your best-selling product is 'Ice Cream & Tacos' with 8.16 transactions.
   - Ensure you have sufficient inventory for this product and consider creating a dedicated campaign for it.

5. Cross-selling opportunities:
   - Identify complementary products to your top sellers. For example, if 'Football' is a main product, what accessories or related items could be promoted alongside it?
   - Create product bundles or 'frequently bought together' promotions to increase average order value.

6. Budget allocation:
   - Your top 5 brands account for 64.75% of your total spend.
   - Consider reallocating budget from lower-performing brands to these top performers to maximize ROI.

7. Seasonal trends:
   - Analyze if any of your top products have seasonal demand patterns.
   - Plan to increase budgets and inventory for these products during their peak seasons.

Detailed Brand Analysis
------------------------
For a detailed breakdown of each brand's performance, including top products, please refer to the Google Sheet at the following URL:
[Google Sheet URL]

This sheet contains comprehensive data for all brands with transactions, including their top-selling products.        

This summary highlights the key features of the script, its business value, the formulas used and their significance, and provides a sample of the email output. The script's ability to provide actionable insights and recommendations based on data analysis makes it a powerful tool for optimizing e-commerce performance in Google Ads.

Script

function main() {
  try {
    var dateRange = "LAST_30_DAYS"; // You can modify this to any valid date range
    var emailAddress = "[email protected]";
    
    Logger.log("Starting script execution for date range: " + dateRange);
    
    var products = getProductData(dateRange);
    Logger.log("Retrieved " + products.length + " products");
    
    var analysis = analyzeDataByBrand(products);
    Logger.log("Analysis complete");
    
    var spreadsheetUrl = createOrUpdateGoogleSheet(analysis, dateRange);
    Logger.log("Google Sheet created: " + spreadsheetUrl);
    
    var emailBody = generateEmailContent(analysis, dateRange, spreadsheetUrl);
    Logger.log("Email content generated");
    
    sendEmail(emailAddress, "Google Ads E-commerce Brand and Product Performance Analysis - " + dateRange, emailBody);
    Logger.log("Email sent successfully");
    
  } catch (error) {
    Logger.log("An error occurred: " + error.message);
    Logger.log("Stack trace: " + error.stack);
    sendErrorEmail(emailAddress, "Error in Google Ads E-commerce Brand and Product Analysis Script", error.message);
  }
}

function getProductData(dateRange) {
  try {
    var report = AdsApp.report(
      "SELECT OfferId, ProductTitle, Clicks, Cost, Conversions, ConversionValue " +
      "FROM SHOPPING_PERFORMANCE_REPORT " +
      "WHERE Impressions > 0 " +
      "DURING " + dateRange);
    
    var rows = report.rows();
    var products = [];
    
    while (rows.hasNext()) {
      var row = rows.next();
      var productTitle = row['ProductTitle'];
      products.push({
        id: row['OfferId'],
        name: productTitle,
        brand: extractBrandFromTitle(productTitle),
        clicks: parseInt(row['Clicks'], 10) || 0,
        cost: parseFloat(row['Cost']) || 0,
        transactions: parseFloat(row['Conversions']) || 0,
        revenue: parseFloat(row['ConversionValue']) || 0
      });
    }
    
    return products;
  } catch (error) {
    Logger.log("Error in getProductData: " + error.message);
    throw error;
  }
}

function extractBrandFromTitle(title) {
  // This is a simple example. You may need to adjust this function based on your product title format
  var words = title.split(' ');
  return words[0]; // Assumes the brand is the first word in the product title
}

function analyzeDataByBrand(products) {
  try {
    var brandAnalysis = {};
    var totalTransactions = 0;
    var totalCost = 0;
    var totalRevenue = 0;
    
    products.forEach(function(product) {
      if (!brandAnalysis[product.brand]) {
        brandAnalysis[product.brand] = {
          transactions: 0,
          cost: 0,
          revenue: 0,
          products: []
        };
      }
      
      brandAnalysis[product.brand].transactions += product.transactions;
      brandAnalysis[product.brand].cost += product.cost;
      brandAnalysis[product.brand].revenue += product.revenue;
      brandAnalysis[product.brand].products.push(product);
      
      totalTransactions += product.transactions;
      totalCost += product.cost;
      totalRevenue += product.revenue;
    });
    
    var brandsWithTransactions = [];
    var brandsWithoutTransactions = [];
    
    for (var brand in brandAnalysis) {
      var brandData = brandAnalysis[brand];
      brandData.roas = brandData.cost > 0 ? brandData.revenue / brandData.cost : 0;
      brandData.costPerTransaction = brandData.transactions > 0 ? brandData.cost / brandData.transactions : 0;
      
      if (brandData.transactions > 0) {
        brandsWithTransactions.push({ name: brand, data: brandData });
      } else {
        brandsWithoutTransactions.push(brand);
      }
    }
    
    brandsWithTransactions.sort((a, b) => b.data.transactions - a.data.transactions);
    
    // Sort products within each brand
    brandsWithTransactions.forEach(brand => {
      brand.data.products.sort((a, b) => b.transactions - a.transactions);
    });
    
    return {
      brandsWithTransactions: brandsWithTransactions,
      brandsWithoutTransactions: brandsWithoutTransactions,
      totalTransactions: totalTransactions,
      totalCost: totalCost,
      totalRevenue: totalRevenue,
      overallRoas: totalCost > 0 ? totalRevenue / totalCost : 0,
      overallCostPerTransaction: totalTransactions > 0 ? totalCost / totalTransactions : 0
    };
  } catch (error) {
    Logger.log("Error in analyzeDataByBrand: " + error.message);
    throw error;
  }
}

function generateEmailContent(analysis, dateRange, spreadsheetUrl) {
  var body = "Google Ads E-commerce Brand and Product Performance Analysis - " + dateRange + "\n\n";
  
  body += "Overall Performance Summary\n";
  body += "----------------------------\n";
  body += "Total Transactions: " + analysis.totalTransactions.toFixed(2) + "\n";
  body += "Total Cost: $" + analysis.totalCost.toFixed(2) + "\n";
  body += "Total Revenue: $" + analysis.totalRevenue.toFixed(2) + "\n";
  body += "Overall ROAS: " + analysis.overallRoas.toFixed(2) + "\n";
  body += "Overall Cost Per Transaction: $" + analysis.overallCostPerTransaction.toFixed(2) + "\n";
  body += "Number of Brands with Transactions: " + analysis.brandsWithTransactions.length + "\n";
  body += "Number of Brands without Transactions: " + analysis.brandsWithoutTransactions.length + "\n\n";
  
  body += "Top 10 Performing Brands (by Transactions)\n";
  body += "-------------------------------------------\n";
  analysis.brandsWithTransactions.slice(0, 10).forEach((brandObj, index) => {
    body += (index + 1) + ". " + brandObj.name + 
            " (Transactions: " + brandObj.data.transactions.toFixed(2) + 
            ", ROAS: " + brandObj.data.roas.toFixed(2) + 
            ", Revenue: $" + brandObj.data.revenue.toFixed(2) + ")\n";
  });
  body += "\n";
  
  body += "Brands to Consider Removing (No Transactions)\n";
  body += "---------------------------------------------\n";
  body += analysis.brandsWithoutTransactions.join(", ") + "\n\n";
  body += "Note: The above brands had no transactions during the observed period. Consider removing them from your advertising strategy.\n\n";
  
  body += "Key Insights and Recommendations\n";
  body += "----------------------------------\n";

  // 1. Top performers
  var topBrand = analysis.brandsWithTransactions[0];
  body += "1. Focus on top-performing brands:\n";
  body += "   - " + topBrand.name + " is your best performer with " + topBrand.data.transactions.toFixed(2) + " transactions and a ROAS of " + topBrand.data.roas.toFixed(2) + ".\n";
  body += "   - Consider increasing its daily budget by 20% to capitalize on its success.\n\n";

  // 2. Underperformers
  var underperformers = analysis.brandsWithTransactions.filter(b => b.data.roas < analysis.overallRoas);
  body += "2. Optimize underperforming brands:\n";
  body += "   - " + underperformers.length + " brands are performing below the overall ROAS of " + analysis.overallRoas.toFixed(2) + ".\n";
  if (underperformers.length > 0) {
    var worstPerformer = underperformers[underperformers.length - 1];
    body += "   - Focus on " + worstPerformer.name + " first, which has a ROAS of only " + worstPerformer.data.roas.toFixed(2) + ".\n";
    body += "   - Review its pricing strategy and consider reducing its budget by 30% if improvements aren't seen within 2 weeks.\n\n";
  }

  // 3. Non-performing brands
  body += "3. Remove non-performing brands:\n";
  body += "   - " + analysis.brandsWithoutTransactions.length + " brands had no transactions.\n";
  body += "   - Pause these brands immediately to stop unnecessary spend.\n";
  body += "   - If you wish to retain any of these brands, consider creating a separate campaign with a minimal budget for testing.\n\n";

  // 4. Product mix analysis
  var bestSellingProduct = topBrand.data.products[0];
  body += "4. Analyze product mix:\n";
  body += "   - Your best-selling product is '" + bestSellingProduct.name + "' with " + bestSellingProduct.transactions.toFixed(2) + " transactions.\n";
  body += "   - Ensure you have sufficient inventory for this product and consider creating a dedicated campaign for it.\n\n";

  // 5. Cross-selling opportunities
  body += "5. Cross-selling opportunities:\n";
  body += "   - Identify complementary products to your top sellers. For example, if '" + bestSellingProduct.name + "' is a main product, what accessories or related items could be promoted alongside it?\n";
  body += "   - Create product bundles or 'frequently bought together' promotions to increase average order value.\n\n";

  // 6. Budget allocation
  var totalSpend = analysis.totalCost;
  var topBrandsSpend = analysis.brandsWithTransactions.slice(0, 5).reduce((sum, brand) => sum + brand.data.cost, 0);
  var topBrandsSpendPercentage = (topBrandsSpend / totalSpend) * 100;
  body += "6. Budget allocation:\n";
  body += "   - Your top 5 brands account for " + topBrandsSpendPercentage.toFixed(2) + "% of your total spend.\n";
  body += "   - Consider reallocating budget from lower-performing brands to these top performers to maximize ROI.\n\n";

  // 7. Seasonal trends (if applicable)
  body += "7. Seasonal trends:\n";
  body += "   - Analyze if any of your top products have seasonal demand patterns.\n";
  body += "   - Plan to increase budgets and inventory for these products during their peak seasons.\n\n";

  body += "Detailed Brand Analysis\n";
  body += "------------------------\n";
  body += "For a detailed breakdown of each brand's performance, including top products, please refer to the Google Sheet at the following URL:\n";
  body += spreadsheetUrl + "\n\n";
  body += "This sheet contains comprehensive data for all brands with transactions, including their top-selling products.";
  
  return body;
}

function createOrUpdateGoogleSheet(analysis, dateRange) {
  var spreadsheetName = "Google Ads E-commerce Brand Analysis - " + dateRange;
  var spreadsheet = SpreadsheetApp.create(spreadsheetName);
  var sheet = spreadsheet.getActiveSheet();
  
  // Add headers
  sheet.appendRow(["Brand", "Transactions", "Revenue", "Cost", "ROAS", "Cost Per Transaction", "Top Products"]);
  
  // Add data for each brand
  analysis.brandsWithTransactions.forEach(function(brandObj) {
    var brand = brandObj.name;
    var data = brandObj.data;
    var topProducts = data.products.slice(0, 5)
                                   .map(p => p.name + " (Transactions: " + p.transactions.toFixed(2) + ", Revenue: $" + p.revenue.toFixed(2) + ")")
                                   .join("\n");
    
    sheet.appendRow([
      brand,
      data.transactions.toFixed(2),
      data.revenue.toFixed(2),
      data.cost.toFixed(2),
      data.roas.toFixed(2),
      data.costPerTransaction.toFixed(2),
      topProducts
    ]);
  });
  
  // Autosize columns
  sheet.autoResizeColumns(1, 7);
  
  return spreadsheet.getUrl();
}

function sendEmail(to, subject, body) {
  try {
    MailApp.sendEmail({
      to: to,
      subject: subject,
      body: body
    });
  } catch (error) {
    Logger.log("Error in sendEmail: " + error.message);
    throw error;
  }
}

function sendErrorEmail(to, subject, errorMessage) {
  try {
    var body = "An error occurred while running the Google Ads E-commerce Brand and Product Analysis Script:\n\n" + errorMessage;
    body += "\n\nPlease check the script logs for more details.";
    
    MailApp.sendEmail({
      to: to,
      subject: subject,
      body: body
    });
  } catch (error) {
    Logger.log("Error in sendErrorEmail: " + error.message);
  }
}