How to track OpenAI costs effectively?
Since we’ve started using OpenAI here ate Petlove, one of the main concerns was about keep tracking of its costs. We could not find good benchmarks and most of the information available was about cots per tokens and strategies to estimate data processing based on that.
OpenAI platform do offers a usage tracking, but it is very limited and do not bring any detail about usage trends by customer, model and other breaks.
If you want to know the spent by member or by model you have to dig into daily usage breakdown dropdown menus, searching day by day, or copy/pasting information to organize a more consolidated view.
Looking for help at the internet I could find some posts, like this from Mark Craddock which he explains good strategies and suggest using the API response to build your own dashboard.
Also, searching at OpenAI community resources, I could find several discussions about this need and in this topic I found a partial solution from Davell, which I mixed with the idea of attributing the right pricing info from OpenAI platform table.
The objective of this post is to bring more details on how to build that cost tracking using OpenAI API response and a bit of python code to make a simple cost tracking tool with more detailed view.
To start, we are using the following packages:
import json # to work on request responses and storage data
import requests # call API
import datetime # parse dates
from dateutil.relativedelta import relativedelta # date manipulation
import dateutil.parser as dparser # parse dates from strings
import os # get environment vars
import pandas as pd # you probably wrote this one before I tell
import time # sleeper to avoid exceeding API limitsThen you can get your OpenAI credentials stored in env vars:
openai_org_id = os.getenv('OPENAI_ORG_ID')
openai_api_key = os.getenv('OPENAI_API_KEY')Since I was running it locally, I have specified a working directory in which I’m saving the json files with the API extractions. So I start reading the json files stored in this working directory to get the last date extracted from the API:
files = []
[files.append(f) for f in os.listdir() if f.endswith('.json')]
files.sort()
fdates = {}
idate = datetime.date(2023, 7, 1) # initial date
for f in files:
dates = []
for i in f.split('_'):
try:
dparser.parse(i, fuzzy=True)
if dparser.parse(i, fuzzy=True).date()>idate:
first_day_of_month=dparser.parse(i, fuzzy=True).date()
dates.append(dparser.parse(i, fuzzy=True))
except ValueError:
pass
fdates[f] = dates
first_day += relativedelta(days=1) # adding 1 day to start from the day after last extraction
current_day = datetime.date.today() #end dateNext step is to prepare API headers and extract the list of members in the organization:
headers = {
"method": "GET",
"authority": "api.openai.com",
"scheme": "https",
"path": f"/v1/organizations/{openai_org_id}/users",
"authorization": f"Bearer {openai_api_key}",
}
starttime = time.monotonic() #starting to count time because the following request counts for the usage limits
users_response = requests.get(f"https://api.openai.com/v1/organizations/{openai_org_id}/users", headers=headers)
users = users_response.json()["members"]["data"]Now we create a iteration over members and dates which we want to extract the API data:
df_costs = pd.DataFrame() #empty dataframe to store the data extracted
for user in users:
id_of_user = user["user"]["id"]
current_date = first_day
while current_date < current_day and True:
# I was giving 15 seconds just to ensure the requests per minute would not break the limits
time.sleep(15.0 - ((time.monotonic() - starttime) % 15.0))
usage_headers = {
"method": "GET",
"authority": "api.openai.com",
"authorization": f"Bearer {openai_api_key}",
"openai-organization": openai_org_id,
}
usage_response = requests.get(f"https://api.openai.com/v1/usage?date={current_date}&user_public_id={id_of_user}", headers=usage_headers)
user_data = usage_response.json()
# This conditional is to avoid breaking if the response is empty
if len(user_data['data'])==0:
current_date += relativedelta(days=1)
continue
else:
data = user_data["data"]
# This condition is to parse whisper model response in the same structure as the GPT/Embeddings models
if len(user_data['whisper_api_data'])>0:
wad = []
for w in user_data['whisper_api_data']:
wd={}
wd['aggregation_timestamp'] = w['timestamp']
wd['n_requests'] = w['num_requests']
wd['operation'] = 'audio-whisper'
wd['snapshot_id'] = w['model_id']
wd['n_context'] = w['num_seconds']
wd['n_context_tokens_total'] = w['num_seconds']
wd['n_generated'] = 0
wd['n_generated_tokens_total'] = 0
wad = wad + [wd]
data = data + wad
else:
pass
df = pd.DataFrame(data)
df['local_timestamp'] = df['aggregation_timestamp'].apply(lambda x: datetime.datetime.fromtimestamp(x))
# converting to openai tz
df['system_timestamp'] = df['local_timestamp'].dt.tz_localize('America/Sao_Paulo').dt.tz_convert("UTC")
df['user'] = user["user"]["name"].lower().replace(" ", "_")
df['email'] = user["user"]["email"]
df_costs = pd.concat([df_costs,df])
current_date += relativedelta(days=1)After running the extratcions you can take a look into you df_costs dataframe using df_costs.head()
Here we are building the model costs parameters:
model_costs = {
"gpt-3.5-turbo": {"context": 0.0015, "generated": 0.002},
"gpt-3.5-turbo-0301": {"context": 0.0015, "generated": 0.002},
"gpt-3.5-turbo-0613": {"context": 0.0015, "generated": 0.002},
"gpt-3.5-turbo-16k": {"context": 0.003, "generated": 0.004},
"gpt-3.5-turbo-16k-0613": {"context": 0.003, "generated": 0.004},
"gpt-4": {"context": 0.03, "generated": 0.06},
"gpt-4-0314": {"context": 0.03, "generated": 0.06},
"gpt-4-0613": {"context": 0.03, "generated": 0.06},
"gpt-4-32k": {"context": 0.06, "generated": 0.12},
"gpt-4-32k-0314": {"context": 0.06, "generated": 0.12},
"gpt-4-32k-0613": {"context": 0.06, "generated": 0.12},
"text-embedding-ada-002-v2": {"context": 0.0001, "generated": 0},
"text-davinci:003": {"context": 0.02, "generated": 0.02},
"whisper-1": {"context": 0.1, "generated": 0}, # costs are 0.006 per min (or 0.0001 per second). Multiplying by x1000 to use same rules as tokens
}
mc = pd.DataFrame(model_costs)
mc = mc.T.reset_index()And now we can apply the cost rates over the API consumption extracted into df_costs:
df_costs=df_costs.merge(mc, left_on='snapshot_id', right_on='index', how='left')
df_costs['context_costs']=(df_costs['n_context_tokens_total']/1000)*df_costs['context']
df_costs['generated_costs']=(df_costs['n_generated_tokens_total']/1000)*df_costs['generated']
df_costs['total_costs']=df_costs['context_costs']+df_costs['generated_costs']The next block of code is to show how I was concatenating the current extraction to past extractions in my local working directory:
try:
past_df = pd.read_json(files[len(files)-1])
df_costs = pd.concat([df_costs, past_df])
except FileNotFoundError:
pass
df_costs.reset_index(drop='level_0', inplace=True)
# ensuring datetime formats
df_costs['local_timestamp'] = pd.to_datetime(df_costs['local_timestamp'])
df_costs['system_timestamp'] = pd.to_datetime(df_costs['system_timestamp'])
# saving a json file
with open(f"openai_costs_{idate}_to_{current_date - datetime.timedelta(days=1)}.json", "w") as f:
json.dump(json.loads(df_costs.to_json(orient='columns', date_format='iso')), f)This is the end of the data extraction step.
Now that we have the data, let’s explore how to make some charts using them. First lets import the packages for dataviz:
import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inlineThen we create a function to aggregate data and compute the indicators:
def summary_costs(x):
names = {
'requests': x['n_requests'].sum()
, 'context_tokens': x['n_context_tokens_total'].sum()
, 'generated_tokens': x['n_generated_tokens_total'].sum()
, 'context_costs': x['context_costs'].sum()
, 'generated_costs': x['generated_costs'].sum()
, 'total_costs': x['total_costs'].sum()
}
return pd.Series(names, index=['requests'
, 'context_tokens'
, 'generated_tokens'
, 'context_costs'
, 'generated_costs'
, 'total_costs'
])
daily_cost = df_costs.groupby([pd.Grouper(key='local_timestamp', freq='D')]).apply(summary_costs).reset_index()
daily_cost['month_cumulated'] = daily_cost.groupby(pd.Grouper(key='local_timestamp', freq='M'))['total_costs'].cumsum()
daily_cost['day'] = daily_cost['local_timestamp'].apply(lambda x: x.day)
daily_cost['month'] = daily_cost['local_timestamp'].apply(lambda x: x.month)
daily_plot = daily_cost.pivot_table(index='day', columns='month', values='month_cumulated', aggfunc='sum')This is the chart for Accumulated spent by day — Current x Past Month:
plt.figure(figsize=(10,10))
sns.lineplot(data=daily_plot, legend=True, sizes=(20, 2000))
plt.title('Cumulated spent by day - Current x Past Month')
plt.show()
And here we organize the data of the current month to check the consumption by user and model:
user_cost = df_costs.loc[df_costs['local_timestamp'].dt.month==datetime.date.today().month].groupby(['email'], group_keys=False).apply(summary_costs).sort_values('total_costs', ascending=False)[['total_costs']]
snap_cost = df_costs.loc[df_costs['local_timestamp'].dt.month==datetime.date.today().month].pivot_table(index='email', columns='snapshot_id', values='total_costs', aggfunc='sum')
user_snap_cost = user_cost.merge(snap_cost, left_index=True, right_index=True, how='left')This is the chart for the Total spent by User/Model — Current Month:
plt.figure(figsize=(10,10))
user_snap_cost.drop('total_costs', axis=1).plot(kind='barh', stacked=True, color=['blue', 'red', 'purple', 'orange', 'yellow', 'skyblue', 'green'])
plt.gca().invert_yaxis()
plt.title('Total Spent by User/Model - Current Month')
plt.ylabel('User')
plt.xlabel('Cost USD')
plt.show()
Wrapping up
We can use API requests from openai to build our own cost tracking, customizing the way you want to track. In the scripts showed above we are using a local tracking, but you can use this script to build a pipeline (maybe using Airflow) to run this extraction on a daily basis and using the ata to populate any BI tool or fancy dashboard.
Hope you enjoyed and this could help you keeping track of your OpenAI costs.
You can find the full code in my github: https://github.com/bruno-curta/openai-costs-tracking
