Automating Funding Analytics with Crunchbase, Google Sheets, and Airtable

By connecting Crunchbase's API to Google Sheets and Airtable, investors can automate their Funding Analytics, eliminating time wasted on manual data entry. Startups and venture capitalists miss out on significant wealth (i.e. probably $1M-$2M) due to late decision making caused by waiting to see their competition's fundraising activity (e.g. "How do I know what my competitors are doing?"). Most startups continue to rely on Internet searches and simple algorithms to gather funding activity; while most competitors are already closing deals with the best projects. In a world where most information is outdated by the time you receive it, waiting until your competition has done something to determine what your next steps are only creates additional opportunity for missed revenue.

How many hours could you save per project if you automated that process? That's six additional hours each week that could be spent conducting deep analysis and making better decisions. By using a combination of the Crunchbase API, Google Sheets, and Airtable to automate your Funding Analytics, the data updates automatically, the visuals created for you will automatically populate the dashboards, and you can access all strategic information from any computer, smartphone, or tablet.

After having created an investment monitoring system for eight years, we've found that collecting round information using manual methods requires approximately six hours per project. The end result is lost revenue. Automated finance transforms your scattered funding tables into a working system that runs continuously—even while you're sleeping.

In summary, I will guide you through the process of creating a fully automated Investment Monitoring System using the data from Crunchbase via the API to the creation of interactive reports. All of the tools that I mention are actual tools (not products) and I've actually verified their functionality in the real world through practical case studies.

Funding Insights Automation provides a way for venture capitalists and startup founders to collect data quickly and easily. It also allows for more accurate analysis of this data than before.

Funding Insights are a detailed look into every stage of investment; mergers and acquisitions; or all the money movement that happens in the startup ecosystem. Understanding the people investing and how much they invested tells you how the startup market is structured, where it is overinvested and where there may be parts of the startup ecosystem that are undervalued.

Funding Insights give venture capital firms a roadmap of the startup investment landscape; otherwise, they would be taking a chance on their investments. Startups also use Funding Insights to gain insight into their competitors' financial positions, deal structures, and valuation structures. Funding Insights provide an accurate projection of funding based on actual cash flow, not through word of mouth or marketing hype.

The following are key components of Funding Insights:

• Round size - the amount raised by the company throughout its various stages of fundraising (Seed, Series A/B/C, IPO)
• Company Valuation - the pre and post-round valuation of the company's stock
• Investor - who funded the deal (funds, corporations, angels)
• Date of Closing - the date the money was provided to the startup
• Terms of the Round - equity ownership, investment preference and anti-dilution rights

The time it takes to gather and verify this information for one particular deal is between two to six hours, therefore when you apply this to hundreds of deals that occur in a quarter, it is obvious that the manual collection of Funding Insights creates an extreme inefficiency in the marketplace. Manual Collection will often create missed opportunities and delayed decisions. Automation can streamline this process so that the collection of Funding Insights can take just a few minutes and will reduce the time investment required to collect and verify Funding Insights by 80%+ for those that are working on a high volume of deals.

Crunchbase is a resource of record for over 1 million companies, 700k+ investors, and hundreds of thousands of funding rounds. The data is compiled from various sources including press releases, SEC filings, news articles, and corporate communications.

The core offerings of the Crunchbase platform are:

Company Profiles: Include information on company type, date established, headquarters, team size, and current status (open/closed).

Funding History: Lists all historical funding rounds, including round amounts, dates, investors, and terms.

Investor Data: Includes the investor's portfolio, average size of investment, investment stages, and industry focus area.

News and Events: Includes announcements, leadership changes, product launches, and partnership announcements.

Analytics: Compares trend analysis across industries, regions, and funding-type phases.

The Crunchbase API provides the ability to retrieve this data in JSON format. The basic plan allows 200 requests per minute, which is more than sufficient for monitoring 100s of companies. Crunchbase provides a filter for businesses looking for investments; you can find out who raised what and who invested in it, and what the terms of the investment are. The information provided by Crunchbase can provide insight on how far progressed a project is, the competitive nature of the industry, and what risks a company may face.

Automation and Integration Tools

Crunchbase API: Features and How to Connect

The Crunchbase API allows developers to connect programmatically to the data stored on the Crunchbase platform. Instead of having to navigate to different pages, you simply submit an HTTP request, and structured JSON data is returned that allows you to build applications on your own server.

Key endpoints of the Crunchbase API:

• /organizations: Company-related data
• /funding_rounds: Funding-related data
• /investors: Investor-related data
• /news: Company-specific news articles
• /searches: Company search options by various criteria (industry, geography)

In order to get started using the API, you must first register with Crunchbase and request an API key via your account settings. You are responsible for keeping your API key safe and secure. You can use the API to access a full company profile (including all funding rounds), which can be processed using platforms such as Google Sheets and Airtable.

Using Google Sheets to Process and Update Data

Google Sheets is much more than just an online spreadsheet; it is a useful tool for automating processes using Google Apps Script. There are many reasons why Google Sheets should be used, including:

• Google Sheets is available for anyone to use and allows you to work on documents together in real-time;
• Google Sheets has many features (formulas, sorting and pivot tables) that provide flexibility to process the data collected from the Crunchbase API;
• Google Sheets has a direct integration to the Crunchbase API using Scripts that can make HTTP requests directly from Google Sheets;
• Google Sheets support triggers to allow for automatic loading of data on a scheduled basis;
• Google Sheets provides built-in visualizations (Charts/Graphs) of data.

With Google Sheets, you can minimize errors and speed up the analytics process by importing data directly and on-time. Google Sheets allows users to easily query data and format it with conditional formatting, making it easier to find critical information (e.g., rounds over $10 million). You can configure Google Sheets to automatically request and receive data from the Crunchbase API every morning, so your team will always have access to the most recent company profiles.

Airtable - a Bidirectional Relationship Database and Data Visualization

Airtable is a hybrid database/spreadsheet and incorporates many good features.

• Airtable's relationship between tables (linking companies to rounds and investors)
• Airtable allows for many different kinds of field types - checkboxes, dates, attachments, lists, etc.
• There are many different ways to view data - grid, Kanban, calendar, gallery, etc.
• Powerful filtering and grouping help provide users with a way to perform deeper-data analysis.
• Formulas can be created to perform calculations/summaries.
• You can also integrate via REST API for auto-updating data.

Google Sheets and Airtable work together - Google Sheets is input and processing, Airtable manages and visualizes. For example, you can take data from Crunchbase, import it into Google Sheets, filter it, and then automate transferring it to Airtable. Automating this process will allow you to create a dynamic, functional CRM, and perform in-depth analysis of your data with ease.

Setting Up Automation: Step-by-Step Instructions

Retrieving from Crunchbase and Updating data to Google Sheets

Before doing this, you must first automate the retrieval of data from Crunchbase to Google Sheets through Google Apps Script.

First, create a Spreadsheet: Create three separate sheets that hold the raw API data, processed data, and your Dashboard.

Next, Write the Script: Open up the Apps Script editor and write a function that retrieves data using the Crunchbase funding rounds endpoint. Make sure to include your API key as part of your headers. Once your API call is made, you will need to parse the JSON response and write the required fields (date announced, organization name, money raised, etc.) to your "Raw Data" sheet.

Create Triggers: When you create a Trigger, you will configure the Trigger to execute on a schedule with the schedule set to run every day. This will ensure that when the Trigger executes, the fetch function will have pulled in new data for you, and any previous fetch executions will have occurred before you arrive at work.

To Process the Data: The query function located on the "Processed Data" tab of your sheet, will allow you to query the processed data on additional criteria. In this tab, you may query for a round of funding that exceeded a particular dollar amount, and sort the results of your query by the total funds raised.

How to Link Google Sheets and Airtable using Zapier and Make

The second method to sync processed data from Google Sheets to Airtable is to use either Zapier or Make to automatically move processed data from Google Sheets to Airtable.

• Method One - Using Zapier: Set up a Trigger for every time a new row is added or updated in a Google Sheet and create a corresponding record in Airtable whenever a record is created or updated in Google Sheets;
• Method Two - Using Make: The Make app has a more comprehensive set of capabilities. You can check if a record already exists and avoid creating duplicates when moving records from Google Sheets to Airtable.
• Method Three - Using ASCN.AI NoCode: With ASCN.AI NoCode, you can create a trigger that fires when records are added to your Google Sheets, append filtering by industry and round size, and submit requests to the Airtable API.

Zapier and Make allow for no-code automation of workflows.

Zapier and Make are powerful no-code platforms that allow you to link thousands of services together and eliminate time-consuming, repetitive tasks. With Zapier, it is easy to design an integration by creating a simple Trigger and Action; it takes just a few minutes to create an integration with over 5,000 available integrations. Make is also very effective for more complicated situations that require conditional logic, error handling and running several processes simultaneously. Through Make, you can devise intricate chains that include many branches, and if you want to build out your workflows with AI or custom logic, you may want to check out ASCN.AI No-Code as an alternative solution to Make.

Case Studies

Automating the process of tracking investments for a start-up company

A pre-seed fintech company used to spend on average between 10-15 hours per week to track who raised money and for how much. They created an automated process to perform this task by utilizing the Crunchbase API, Google Sheets and Airtable; along with Telegram alerts to notify them of any funding rounds that exceeded $5 million. The team saved 15 hours per week with this new method of tracking and as a consequence found 12 prospective investors in only 3 months.

An interactive funding dashboard built in Airtable for a venture capital fund

An additional use case for Airtable was for a Fund holding 80 portfolio companies that were currently generating three hours per week on investor report creation. The Fund created an interactive dashboard using Airtable that is automatically updated. By connecting the three tables for Companies, Rounds and Investors, along with the automatic importation of data from Crunchbase; the Fund was able to identify the average round size and growth of valuations in seconds rather than the two weeks it typically took to prepare the report.

Benefits & Recommendations

Why Automating Saves Time & Increases Accuracy

As seen in the comparison of the benefits from automating compared to the benefits from using a manual approach, it is possible to see how drastic the difference can be. For example, searching for 50 companies manually and typing their information into spreadsheets/data entries each month could take over 16 hours of manual data entry for one individual each month. Currently, once setup and minor maintenance every month have been completed, an automated system to retrieve and process this information would take an individual only a couple of hours (setup), and then the remainder of their time can be saved. This is approximately 21 hours of saved time (i.e., 1.5 months annually).

In terms of accuracy, manual entries for data typically have an error rate of 2% to 3%. By contrast, with automation, as long as the integrations and API's are stable, the error rate is less than 0.1%. The benefits of automation are that you are able to keep your data "fresh," process the same amount of data based on a "scalable" manner, and ensure the consistency of your data. This allows your analysts more time to create high-level strategies and make "smart" decisions.

Timely Updates and Data Security are Essential

The reason for updating your data is so that it is always timely, i.e., it has a very short time-lapse for being updated (daily/weekly). It is very important to keep your data secure as well. Storing your API key in a "secure" vault is the best way to maintain the security of your API keys. In addition, limiting access to your spreadsheets and databases to only those individuals that truly need it to do their job and enabling two-factor authentication on any service used by you is also highly recommended. In addition, it is a best practice to periodically audit and revoke access to integrations used for your APIs. If you are working on highly sensitive projects, you should explore utilizing enterprise plans with advanced encryption technology.

Frequently Asked Questions (FAQs)

How Frequently Is Crunchbase Data Updated To Google Sheets And Airtable?

Essentially, it is up to the "triggers" you create for your system. In order to reduce the load on the API, your triggers can typically be set for daily updates, but in some cases, it may be more appropriate to utilize a trigger set for every few hours instead.

Can I Customize My Data Fields?

You can. Both Google Sheets & Airtable provide you with the ability to choose what data you want to import into their environments or platforms, as well as the ability to rename, filter, or calculate that data when it is imported. Additionally, other tools, such as Make, provide you with even greater flexibility to perform automatic categorizations of your data using AI technology.

What Are The Boundaries/Limitations Of The Service Within My Automation?

As noted previously, the primary limitations are the API quota's. A basic account with Crunchbase is limited to a maximum of 200 requests per minute through the API. In addition to the API limits, the maximum execution time of a Google Apps Script is six minutes per run. Therefore, while automation allows you to focus on completing the majority of your routine work, there is still the need for the analysts to perform the expert review and evaluations of the businesses.

What Should I Do About Duplicates & Record Conflict Errors?

By far, the best method for managing duplicate and conflict records is to utilize the "Unique Identifiers" (UUID) you receive from the API. In addition, in Airtable, it is very easy to set filters in the automation platform to eliminate duplicate records from being created. Therefore, periodically checking manually for errors is recommended to prevent the accumulation of errors as well.