Alright, it is not pretty but this should do the job in case anyone is interested:
Code (Python)
# based on https://cookbook.openai.com/examples/completions_usage_api
import requests
import time
from datetime import datetime
from dotenv import load_dotenv
import os
import json
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
from tabulate import tabulate
# Load environment variables from the .env file
load_dotenv()
# OpenAI API Key
api_key=os.environ.get("YOUR_ADMIN_API_KEY")
# Calculate start time: n days ago from now
days_ago = 15
start_time = int(time.time()) - (days_ago * 24 * 60 * 60)
# Define parameters with placeholders for all possible options
params = {
"start_time": start_time, # Required: Start time (Unix seconds)
# "end_time": end_time, # Optional: End time (Unix seconds)
"bucket_width": "1d", # Optional: '1m', '1h', or '1d' (default '1d')
#"project_ids": ["your_project_ids"], # Optional: List of project IDs
# "user_ids": ["user_example"], # Optional: List of user IDs
# "api_key_ids": ["key_example"], # Optional: List of API key IDs
# "models": ["o1-2024-12-17", "gpt-4o-2024-08-06", "gpt-4o-mini-2024-07-18"], # Optional: List of models
# "batch": False, # Optional: True for batch jobs, False for non-batch
"group_by": ["model"], # Optional: Fields to group by
"limit": days_ago, # Optional: Number of buckets to return, this will chunk the data into 7 buckets
# "page": "cursor_string" # Optional: Cursor for pagination
}
# change these to match the models you use according to the rules
# "Get free usage of up to
# 1 million tokens per day across gpt-4.5-preview, gpt-4o and o1,
# and up to 10 million tokens per day across gpt-4o-mini, o1-mini and o3-mini
# on traffic shared with OpenAI through April 30, 2025. "
group1 = ['gpt-4.5-preview-2025-02-27', 'o1-2024-12-17', 'gpt-4o-2024-08-06', ]
group2 = ['o3-mini-2025-01-31', 'gpt-4o-mini-2024-07-18']
#not covered by the free usage
#['gpt-4o-mini-tts', 'chatgpt-4o-latest']
endpoint='https://api.openai.com/v1/organization/usage/completions'
# Reusable function for retrieving paginated data from the API
def get_data(url, params, key):
# Set up the API key and headers
headers = {
"Authorization": f"Bearer {key}",
"Content-Type": "application/json",
}
# Initialize an empty list to store all data
all_data = []
# Initialize pagination cursor
page_cursor = None
# Loop to handle pagination
while True:
if page_cursor:
params["page"] = page_cursor
response = requests.get(url, headers=headers, params=params)
if response.status_code == 200:
data_json = response.json()
all_data.extend(data_json.get("data", []))
page_cursor = data_json.get("next_page")
if not page_cursor:
break
else:
print(f"Error: {response.status_code}")
break
if all_data:
print("Data retrieved successfully!")
else:
print("Issue: No data available to retrieve.")
return all_data
usage_data = get_data(endpoint, params, api_key)
# Initialize a list to hold parsed records
records = []
# Iterate through the data to extract bucketed data
for bucket in usage_data:
start_time = bucket.get("start_time")
end_time = bucket.get("end_time")
for result in bucket.get("results", []):
records.append(
{
"start_time": start_time,
"end_time": end_time,
"input_tokens": result.get("input_tokens", 0),
"output_tokens": result.get("output_tokens", 0),
"input_cached_tokens": result.get("input_cached_tokens", 0),
"input_audio_tokens": result.get("input_audio_tokens", 0),
"output_audio_tokens": result.get("output_audio_tokens", 0),
"num_model_requests": result.get("num_model_requests", 0),
"project_id": result.get("project_id"),
"user_id": result.get("user_id"),
"api_key_id": result.get("api_key_id"),
"model": result.get("model"),
"batch": result.get("batch"),
}
)
# Create a DataFrame from the records
df = pd.DataFrame(records)
# Convert Unix timestamps to datetime for readability
df["start_datetime"] = pd.to_datetime(df["start_time"], unit="s")
df["end_datetime"] = pd.to_datetime(df["end_time"], unit="s")
# Reorder columns for better readability
df = df[
[
"start_datetime",
"end_datetime",
"start_time",
"end_time",
"input_tokens",
"output_tokens",
"input_cached_tokens",
"input_audio_tokens",
"output_audio_tokens",
"num_model_requests",
"project_id",
"user_id",
"api_key_id",
"model",
"batch",
]
]
def classify_category(cat):
if cat in group1:
return 'T1'
elif cat in group2:
return 'T2'
else:
return 'T3'
df['group'] = df['model'].apply(classify_category)
df['date'] = pd.to_datetime(df['start_datetime']).dt.strftime('%Y-%m-%d')
df['total_tokens'] = df['input_tokens'] + df['output_tokens']
# totals per models
unique_values = df['model'].unique().tolist()
print(f'-- MODELS USED --\n {unique_values}')
# totals per model
print('-- MODEL TOTALS --')
summary = df.groupby('model')[['input_tokens', 'output_tokens','total_tokens']].sum().reset_index()
summary['group'] = summary['model'].apply(classify_category)
summary = summary.sort_values(by='total_tokens', ascending=False)
# Calculate the totals row
totals_row = pd.DataFrame({
'model': ['Total'],
'input_tokens': [summary['input_tokens'].sum()],
'output_tokens': [summary['output_tokens'].sum()],
'total_tokens': [summary['total_tokens'].sum()]
})
# Append it to the summary
summary = pd.concat([summary, totals_row], ignore_index=True)
summary=summary[[ 'model', 'group', 'input_tokens', 'output_tokens', 'total_tokens']]
print(tabulate(summary, headers='keys', tablefmt='grid', showindex=False))
# Group by date and group, then sum total_tokens
grouped = df.groupby(['date', 'group'])['total_tokens'].sum().reset_index()
# Pivot to get group1, group2 as columns
pivot = grouped.pivot(index='date', columns='group', values='total_tokens').fillna(0).reset_index()
pivot['% T1'] = (pivot['T1'] / 1000000 * 100).round(1)
pivot['% T2'] = (pivot['T2'] / 10000000 * 100).round(1)
pivot=pivot[['date', 'T1', '% T1', 'T2', '% T2', 'T3']]
print('-- DAILY TOTALS --')
print(tabulate(pivot, headers='keys', tablefmt='grid', showindex=False))