Find competitors of any company and measure their similarity with Google Sheets

DDVC #45: Where venture capital and data intersect. Every week.

👋 Hi, I’m Andre and welcome to my weekly newsletter, Data-driven VC. Every Thursday I cover hands-on insights into data-driven innovation in venture capital and connect the dots between the latest research, reviews of novel tools and datasets, deep dives into various VC tech stacks, interviews with experts, and the implications for all stakeholders. Follow along to understand how data-driven approaches change the game, why it matters, and what it means for you.

Current subscribers: 11,550, +205 since last week

---

Brought to you by VESTBERRY - A tool designed specifically for data-driven VCs

Request your FREE demo account to explore the potential of establishing live data links from over 300 diverse data sources, apps, and platforms, paving the way for distinctive VC insights.

---

Finding and comparing similar companies is a tedious task that gets regularly performed by a range of professionals such as investors and founders mapping competitive landscapes or salesmen growing their lead funnel. Thankfully, we can automate this process with modern tools.

Today, I share a comprehensive guide that combines two different approaches to identify similar companies at scale. Subsequently, I auto-generate summaries of their respective business descriptions and create a similarity matrix that allows you to spot the closest competitors with ease. All in Google Sheets and without a need to code. It’s 100% free and easy to replicate.

Loading video

How to get started

1. Login to your Google Account and create a new Google Sheet

2. Add a name to your sheet, click on “Extensions” > “Apps Script”

Find similar companies via Google Programmable Search Engine and G2

3. Open the new tab “Apps Script” and replace the whole project with the code below (→ remove “myFunction{}”). Note that this approach uses G2 (peer review platform for software solutions) to search for similar companies and is thus most reliable for more mature companies.

/**
 * Find similar companies via G2
 *
 * @param {string} companyName - Name of the company
 * @returns {string} Result
 * @customfunction
 */


function SimilarCompanyG2(companyName) {

if (companyName == ""){return "Error - Input is empty"}

  // Replace these with your actual API key
  const API_KEY = 'YOUR_API_KEY'
  const SEARCH_ENGINE_ID = 'e298e2926913441fc';

  // Formulate the query to find top 10 alternatives of a company on G2's website
  searchQuery = "Top 10 " + companyName + "Alternatives & Competitors site:g2.com/products"
  
  // Construct the URL for the Google Custom Search JSON API
  searchUrl = `https://www.googleapis.com/customsearch/v1?key=${API_KEY}&cx=${SEARCH_ENGINE_ID}&q=${encodeURIComponent(searchQuery)}`;

  // Send a GET request to the API
  response = UrlFetchApp.fetch(searchUrl, {method: 'get'});
  
  // Parse the JSON response
  data = JSON.parse(response.getContentText());

  // If there are any items in the response data
  if (data?.items?.length > 0) {
    competitorsList = data.items[0]?.pagemap?.listitem;
    
    // Log the competitors list
    console.log(competitorsList);
    
    // Filter competitors with a URL and a position, then map to their names
    competitors = competitorsList?.length > 0 ? competitorsList.filter(item => !!item.url && !!item.position).map(item => item.name) : "Not found";
    
    // Log the filtered competitors
    console.log(competitors);
    
    // If there are any competitors, return them as a comma-separated string, else return "Not found"
    return competitors?.length > 0 ? competitors.join(', ') : "Not found";
  } else {
    return "Not found";
  }
}

4. Rename your project to “Similar Companies G2”

5. Get a Custom Search API Key. Click on “Get a key” and follow the steps below.

6. Click “Show key” and copy it. Within the App Script project replace the key value with the API key var API_KEY = 'YOUR_API_KEY'. Only replace the words between ‘ ‘ and not the ‘ itself. Save the project.

7. Get back to your Google Sheets tab and use the formula =SimilarCompanyG2(A2) like any other formula. A2 refers to the cell with the company name. If you get the error “Exception: Request failed for https://www.googleapis.com returned code 429. Truncated server response: { "error": { "code": 429, "message": "Quota exceeded for quota metric 'Queries' and limit 'Queries per day' of service 'customsearch.goog... (use muteHttpExceptions option to examine full response) (line 26).” it’s because Google Custom Search allows 100 calls/day. If you need more searches, you can pay 5$ to get 1000 additional credits.

---

---

Find similar companies with OpenAI

8. Repeat step 3. above. Get back to Apps Script, click “+” and add a new script. Rename the new script to “Similar Companies OpenAI”

9. Replace the whole project with the code below (→ remove “myFunction{}”). This approach uses OpenAI and the underlying training data to find similar companies based on their websites. Note that depending on the OpenAI model, website data might be partially outdated, yet works for all companies (mature or not) that have a website.

/**

* Find similar companies via OpenAI
*
* @param {string} prompt The prompt to feed to the GPT-3 model
* @param {string} cell The cell to append to the prompt
* @param {number} [maxWords=10] The maximum number of words to generate
* @return {string} The generated text
* @customfunction
*/

function runOpenAI(prompt, cell, maxWords) {

const API_KEY = 'YOUR_API_KEY';
maxTokens = 100

if (maxWords){maxTokens = maxWords * 0.75}
model = "gpt-3.5-turbo"
prompt = prompt+cell+":"
temperature= 0

 // Set up the request body with the given parameters
 const requestBody = {
   "model": model,
   "messages": [
       {"role": "system", "content": "You are a helpful assistant that answers questions."},
       {"role": "user", "content": prompt},
   ],
   "temperature": temperature,
   "max_tokens": maxTokens
 };
 console.log(requestBody)

 // Set up the request options with the required headers
 const requestOptions = {
   "method": "POST",
   "headers": {
     "Content-Type": "application/json",
     "Authorization": "Bearer "+API_KEY
   },
   "payload": JSON.stringify(requestBody)
 };

 // Send the request to the GPT-3 API endpoint for completions
 const response = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions", requestOptions);
 console.log(response.getContentText())

 // Get the response body as a JSON object
 const responseBody = JSON.parse(response.getContentText());

 //let answer= responseBody.choices[0]["text"].text
 let answer= responseBody.choices[0]["message"]["content"]

 // Return the generated text from the response
 return answer
}

10. Go to https://openai.com/api/ and click on "Sign up" to complete your registration (if you already have an account, simply click on "Log in")

11. Click on your profile pic top right, navigate to the "API Keys" tab, or use this direct link: https://platform.openai.com/account/api-keys

    

12. If you already have an API key, just copy it. Otherwise, create a new key by clicking on “+ Create new secret key” 

    

13. Within the App Script project “Similar Companies OpenAI” replace the key value with the API key var API_KEY = 'YOUR_API_KEY'. Only replace the words between ‘ ‘ and not the ‘ itself. Save the project.

14. Get back to the Google Sheets tab and use the formula =runOpenAI(prompt, parameter, limit) like any other formula. You can play around with the prompt or just use the following: =runOpenAI("name 10 similar companies to the following one and split them with ',' in one row", A2, 100) where A2 refers to the cell with the company name that we want to find similar companies for.

---

---

Measure similarity and visualize it in a similarity matrix

15. Take the output from one of the previous two approaches (I took the OpenAI results) that provides a list of similar companies. Split one cell with all company names into multiple cells with one company name each by using the formula: =SPLIT(A2,",", TRUE, TRUE) where A2 is the cell with all names. In the example below, I put the formula in D2 and create all cells in the same line. Link the cells in column B to the ones in row 4 and create a matrix as shown below.

16. Leverage the =runOpenAI() formula above to create individual company descriptions based on publicly available info on the web: =runOpenAI("Summarize company descriptions for the following companies in up to 20 words each", D4, 100) where D4 is the cell with one of the identified similar companies.

17. Leverage the =runOpenAI() formula above to calculate (cosine) similarity for the description vectors: =runOpenAI("Return the similarity for the following two companies as a number between 0.00 to 1.00. Show just the number!", CONCATENATE(B5, $C$4), 100) where 0 represents 0% similarity and 1 represents 100% overlap. The “concatenate” part is for formatting.

This is it for today. With this easy automation you can now find a range of similar companies (=competitors), create business summaries, calculate the similarity based on the description vectors, and visualize it in a matrix to easily spot the closest competitors. Feel free to replicate, edit, and share it with like-minded others who might benefit.

Stay driven,
Andre

PS: If you enjoyed this piece, you might also like this previous post on “AI-powered Google Sheet to track LinkedIn profiles”

---

Thank you for reading. If you liked it, share it with your friends, colleagues, and everyone interested in data-driven innovation. Subscribe below and follow me on LinkedIn or Twitter to never miss data-driven VC updates again. 

What do you think about my weekly Newsletter? Love it | It's great | Good | Okay-ish | Stop it

If you have any suggestions, want me to feature an article, research, your tech stack or list a job, hit me up! I would love to include it in my next edition😎