Airtable Custom GPT for Data Analysis

Airtable is super useful when it comes to tracking your data. But when it comes to reporting on your data, and interacting with your data, Airtable leaves a little to be desired. Enter ChatGPT. ChatGPT lets you create your own Custom GPTs that can make external API calls to various services.

We created a series of instructions to help ChatGPT interact with Airtable, and be able to render charts directly from within the chat interface.

While this particular use case includes instructions for a services utilization example, we love building out specific automations for our clients as well.

Instructions:
Your job is to analyze data retrieved from an Airtable base. You will act as a data analyst, providing information that is concise and easy to understand for the user. Do not use technical jargon. This specific use case is centered around utilization for a professional services business.

The Base ID: appIkvd2oE19vFZWo

Listing tasks:
1. When a user asks for utilization for a name, like "Utilization for John Doe," you should filter based on the Resource Name to see if it contains John Doe. Please use the formula SEARCH("John Doe", "" & {Resource Name}) for the filter to assist with type coercion.

2. Once the data has been retrieved via the API, do not print it. Instead, perform the following calculations before rendering a table.

3. Aggregate the tasks by week. For example, if there is a task for 10/02 for 2 hours and a task for 10/03 for 4 hours, this should show as "Week of 10/2" (since that is the Monday of the week) and Total Hours of 6 by aggregating the two task time logs.

4. Once the Total Hours has been aggregated, you'll need to divide this number by 3600 to display the hours.

5. There is a field "Target Utilization" - this value does not need to be aggregated. For example, if John Doe has 10 tasks, all of them will share the same Target Utilization of 30 hours.

6. Divide the "Total Hours" by "Target Utilization." We will call this value "Utilization". For example, if Total Hours is 15, and the Target Utilization is 30 hours, then the Utilization will be 50%

7. Once all the calculations have been performed, render the data in a table displaying the Week of [Date], the Total Hours, the Utilization, and Target Utilization.

8. Also render the data in a bar chart, with the Target Utilization displaying as a benchmark line, the Y axis showing the aggregated Total Hours, and the X axis showing the weeks. Make sure that the bars are wide for easy data visualization. If there is a week with no value, for example the week of 10/09/2023 has a number of hours, and the week of 10/23/2023 has a number of hours, but there is no record for the week of 10/16/2023, you do not need to leave space on the x axis for this week. Simply place the week of 10/09 and the week of 10/23 next to each other so the data looks better.

9. You do not need to provide additional information besides the table and chart. You do not need to explain each of these after you have rendered them.

If the response contains "offset", it means you need request the next page of results. Continue this process until there is no offset in the response to ensure that the dataset is complete.

{
  "openapi": "3.0.0",
  "info": {
    "title": "Airtable API",
    "version": "1.0.0",
    "description": "Airtable API for consuming Airtable data"
  },
  "servers": [
    {
      "url": "https://api.airtable.com/v0"
    }
  ],
  "paths": {
    "/meta/bases/{baseId}/tables": {
      "get": {
        "summary": "Get Base Schema",
        "description": "Returns the schema of the tables in the specified base.",
        "operationId": "getBaseSchema",
        "x-openai-isConsequential": false,
        "parameters": [
          {
            "name": "baseId",
            "in": "path",
            "required": true,
            "schema": {
              "type": "string"
            },
            "description": "The ID of the base."
          },
          {
            "name": "include",
            "in": "query",
            "schema": {
              "type": "array",
              "items": {
                "type": "string"
              }
            },
            "description": "Additional fields to include in the response"
          }
        ],
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "object",
                  "properties": {
                    "tables": {
                      "type": "array",
                      "items": {
                        "$ref": "#/components/schemas/Table"
                      }
                    }
                  }
                }
              }
            }
          }
        },
        "security": [
          {
            "bearerAuth": []
          }
        ]
      }
    },
    "/{baseId}/{tableIdOrName}/listRecords": {
      "post": {
        "summary": "List records from a given table",
        "description": "List records from a given table. Table names and table IDs can be used interchangeably. A max of 100 records are returned in the response. Use the offset parameter to fetch additional records.",
        "operationId": "listTableRecords",
        "parameters": [
          {
            "name": "baseId",
            "in": "path",
            "required": true,
            "schema": {
              "type": "string"
            },
            "description": "The ID of the base."
          },
          {
            "name": "tableIdOrName",
            "in": "path",
            "required": true,
            "schema": {
              "type": "string"
            },
            "description": "The ID or name of the table."
          }
        ],
        "requestBody": {
          "required": false,
          "content": {
            "application/json": {
              "schema": {
                "type": "object",
                "properties": {
                  "pageSize": {
                    "type": "integer",
                    "default": 100
                  },
                  "maxRecords": {
                    "type": "integer"
                  },
                  "view": {
                    "type": "string"
                  },
                  "cellFormat": {
                    "type": "string",
                    "enum": [
                      "json",
                      "string"
                    ],
                    "default": "json"
                  },
                  "fields": {
                    "type": "array",
                    "items": {
                      "type": "string"
                    }
                  },
                  "filterByFormula": {
                    "type": "string"
                  },
                  "sort": {
                    "type": "array",
                    "items": {
                      "type": "object",
                      "properties": {
                        "field": {
                          "type": "string"
                        },
                        "direction": {
                          "type": "string",
                          "enum": [
                            "asc",
                            "desc"
                          ]
                        }
                      }
                    }
                  },
                  "offset": {
                    "type": "string"
                  }
                }
              }
            }
          }
        },
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "object",
                  "properties": {
                    "records": {
                      "type": "array",
                      "items": {
                        "$ref": "#/components/schemas/Record"
                      }
                    },
                    "offset": {
                      "type": "string"
                    }
                  }
                }
              }
            }
          },
          "422": {
            "description": "Iteration may timeout",
            "content": {
              "application/json": {
                "schema": {
                  "$ref": "#/components/schemas/Error"
                }
              }
            }
          }
        },
        "security": [
          {
            "bearerAuth": []
          }
        ]
      }
    }
  },
  "components": {
    "schemas": {
      "Table": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "name": {
            "type": "string"
          },
          "primaryFieldId": {
            "type": "string"
          },
          "fields": {
            "type": "array",
            "items": {
              "$ref": "#/components/schemas/Field"
            }
          },
          "views": {
            "type": "array",
            "items": {
              "$ref": "#/components/schemas/View"
            }
          }
        }
      },
      "Field": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "name": {
            "type": "string"
          },
          "type": {
            "type": "string"
          },
          "description": {
            "type": "string"
          },
          "options": {
            "type": "object"
          }
        }
      },
      "View": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "name": {
            "type": "string"
          },
          "type": {
            "type": "string"
          }
        }
      },
      "Record": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "createdTime": {
            "type": "string",
            "format": "date-time"
          },
          "fields": {
            "type": "object",
            "additionalProperties": true
          },
          "commentCount": {
            "type": "integer"
          }
        }
      },
      "Error": {
        "type": "object",
        "properties": {
          "error": {
            "type": "object",
            "properties": {
              "type": {
                "type": "string"
              }
            }
          }
        }
      }
    },
    "securitySchemes": {
      "bearerAuth": {
        "type": "http",
        "scheme": "bearer"
      }
    }
  }
}

Thanks to Business Automated! for releasing some JSON samples used in the action config

Need help building a solution tailored for you?

Templates are a great way to get started, but if you want a system that matches your specific needs, reach out for a free consultation.

Get Started Now

Need help building a solution tailored for you?

Templates are a great way to get started, but if you want a system that matches your specific needs, reach out for a free consultation.

Get Started Now

Need help building a solution tailored for you?

Templates are a great way to get started, but if you want a system that matches your specific needs, reach out for a free consultation.

Get Started Now