Get your competitor’s last 6 months estimated organic traffic data in Sheets

With this script you can input your competitor’s domain and it will return an estimate of their last six month’s organic traffic, from SimilarWeb. 🚀

Check out the GIF to see it in action 🎥

---

How to add the Script to Google Sheets

1. Sign up to RapidAPI

Sign up to RapidAPI, an API marketplace, at https://rapidapi.com/auth/sign-up (you can even use your Google Account).

---

2. Subscribe to the SimilarWeb API

Once you’re all set up on RapidAPI head to https://rapidapi.com/apifactory/api/similarweb2/pricing and subscribe to the Basic Plan (Free). This plan has a hard 100 requests per month limit.

---

3. Get your API Key

The good thing about RapidAPI is that your API key can be used universally across any of the APIs in their marketplace. To copy your API key, go to https://rapidapi.com/developer/dashboard and head down to ‘My Apps’ and then ‘Security’. From there, you can copy your Application Key.

---

4. Copy the script below:

/**
* Returns the last 6 month's estimated traffic values for a domain. 
*
* @param {"https://www.shopify.com"} url input the domain.
* @return last 6 months estimated traffic values.
* @customfunction
*/


function getCompetitorTraffic(url) {

  // add API key from https://rapidapi.com/apifactory/api/similarweb2
  const apiKey = '';
  url = encodeURIComponent(url);
 
  const options = {
        'method': 'GET',
        'muteHttpExceptions': true,
        'headers': {
            'x-rapidapi-key': apiKey,
            'x-rapidapi-host': 'similarweb2.p.rapidapi.com'
        }
    };
    // perform GET request to API
    const response = UrlFetchApp.fetch("https://similarweb2.p.rapidapi.com/trafficoverview?website="+url, options);
    
    // check response and return error if website is not available
    try {

    if(response.getResponseCode() === 200) {
    
    // monthly visits data and organic search %
    const json = response.getContentText();
    const results = JSON.parse(json).monthlyVisitsEstimate;
    const organic = JSON.parse(json).trafficSources.Search;
    
    const traffic = Object.entries(results);
    
    // multiply each traffic value by organic search %  
    traffic.forEach((row,x) => {
    traffic[x][1] *= organic; 
    });
    
    // add column headers
    traffic.unshift(["Date","Organic Traffic"]);

    return traffic;
    
    }

    } catch (err) {

     return 'No website data';
  }
}

---

5. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and replace line 13 with your API key:

  const apiKey = 'addapikeyhere';

Save it.

---

6. Add the formula to any cell in your sheet

=getCompetitorTraffic("https://www.shopify.com")

You can replace “https://www.shopify.com” with any domain.

*The api uses the free public facing version of SimilarWeb and generally only returns data for domains if they have over 30k+ monthly visitors.

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets