Analyze Companies Growth with Explorium’s Google Sheet Formula

Want to gain insights into the growth of various departments at multiple companies? The ENRICH_GROWTH formula in Google Sheets can help you quickly access quarter-over-quarter (QoQ) growth data. By using a company’s website URL as input, this formula allows you to retrieve data for departments such as Engineering, Design, Marketing, Sales, Customer Service, HR, Legal, and Finance. Plus, Explorium provides free enrichments for lots of data sources, up to 500 per month. In this post, we’ll show you how to use the ENRICH_GROWTH formula and give you a brief overview of the types of data that you can retrieve with it.

See the Formula in Action

Retrieve Comprehensive Growth Data with Ease

With just a simple formula, you can easily retrieve growth indicators for various departments at a company. The formula is “=ENRICH_GROWTH(A2)”, with the company website URL entered in cell A2. This will automatically fill in data for the following columns: Quarter, Found Profiles, Engineering, QoQ Engineering, Design, QoQ Design, Marketing, QoQ Marketing, Sales, QoQ Sales, Customer Service, QoQ Customer Service, HR, QoQ HR, Legal, QoQ Legal, Finance, and QoQ Finance.

How to Use the Formula

To use the formula, you have two options:

Option 1: Copy the template spreadsheet (easier)

  1. Enter this Google spreadsheet – https://docs.google.com/spreadsheets/d/1_WyH8kQefWnRfg_-6KK9feqEzEbyrH5v7dLo5mfsb_0/edit?usp=sharing
  2. Click “File” then “Make a copy”.
  3. This will create a copy of the template spreadsheet with the formula in your Google Sheets.
  4. In the “Explorium Settings” worksheet, paste your API key into cell A2.

Option 2: Manually copy the code

/**
 * Find company departments' growth indicators by company's URL using the Explorium API
 *
 * @param {string} url Company website URL (can be in any format)
 * @return if found - Quarter, Found Profiles, Engineering, QoQ Engineering, Design, QoQ Design, Marketing, QoQ Marketing, Sales, QoQ Sales, Customer Service, QoQ Customer Service, HR, QoQ HR, Legal,	QoQ Legal, Finance, QoQ Finance
 * @customfunction
 */
function ENRICH_GROWTH(url) {
    // Define here the fields you want to take from Explorium
    // You can view all available Contacts for professional Individuals fields at https://developers.explorium.ai/reference/individual-contact-data-contacts-for-professional-individuals
    const EXPLORIUM_FIELDS_g = [
        "Quarter", "Number of profiles", "Percentage of engineering roles", "QoQ engineering roles", "Percentage of design roles", "QoQ design roles", "Percentage of marketing roles", "QoQ marketing roles", "Percentage of sales roles", "QoQ sales roles", "Percentage of customer service roles", "QoQ customer service roles", "Percentage of human resources roles", "QoQ human resources roles", "Percentage of legal roles", "QoQ legal roles", "Percentage of finance roles", "QoQ finance roles"
    ];

    // Send HTTP request to Firmographics Explorium API - https://developers.explorium.ai/reference/individual-contact-data-contacts-for-professional-individuals
    const response = UrlFetchApp.fetch("https://app.explorium.ai/api/bundle/v1/enrich/workforce-trends-by-company-department", {
        method: "post",
        payload: JSON.stringify([{
            "url": url
        }]),
        headers: {
            "Content-Type": "application/json",
            "x-explorium-ref": "gsheets_formula_enrich_growth",
            "API_KEY": readExploriumAPIKey()
        },
        muteHttpExceptions: true
    });

    const responseCode = response.getResponseCode();
    const responseJson = JSON.parse(response);

    if (responseCode !== 200)
        return responseJson.message;

    const signals = responseJson[0];
    
    const relevantFields = EXPLORIUM_FIELDS_g.map(field => {
      if (!signals["Quarter"])
        return null;

      if (field == "Quarter")
          return signals["Quarter"].substring(0, 10);

      if (field.startsWith("Percentage of ")) {
          if (!signals[field]) {
              if (signals["Number of profiles"])
                  return 0;
                  
              return null;
          }

          const percentage = Math.round(signals[field]);
          return Math.round((percentage/100) * signals["Number of profiles"]);
      }

      if (field.startsWith("QoQ "))
          return `${Math.round(signals[field])}%`;
          
      return signals[field];
    });

    return [relevantFields];
}

// Reads Explorium API Key from Settings sheet, B1 cell
function readExploriumAPIKey() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet = spreadsheet.getSheetByName("Settings");
  var cell = worksheet.getRange("B1");
  return cell.getValue();
}
  1. Copy the above code
  2. Create or enter an existing spreadsheet
  3. Go to “Extensions” then “Apps Scripts”.
  4. Paste the code into the editor.
  5. Click the save icon.
  6. Create a worksheet named “Explorium Settings” with the first cell (A1) labeled “API Key”.
  7. Paste your API key into cell A2.

Boost Your Company Data Analysis with Explorium’s Formula

In this post, we introduced you to the ENRICH_GROWTH formula in Google Sheets, which allows you to quickly retrieve quarter-over-quarter (QoQ) growth data for various departments at multiple companies. But that’s not all – Explorium has a vast catalog of data beyond just company growth indicators, including email data, company firmographics data, tech stack data, web presence data, points of interest data, and much more! With Explorium, you can easily enrich your data and gain valuable insights. Be sure to browse Explorium’s full data catalog and access its API documentation to learn more. If you’re interested in using Explorium for your own data enrichment needs, don’t hesitate to sign up for a free account. We hope this post has been helpful, and we look forward to helping you with all your data enrichment needs.