This guide explains how to upload Amazon Q usage metrics into DX using a CSV file. Once an API is made available by Amazon, this process will be automated through a DX data connector.

Prerequisites

an AWS account with Amazon Q access
a DX database user with write access

Instructions

Step 1- Create custom table

To import Amazon Q usage metrics into DX, you'll first need to create a custom table in your DX database.

Use a Postgres client (Postico or pgAdmin) to connect to your DX database.
Then, execute the SQL statement below:

CREATE TABLE custom.amazon_q_daily_usages (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    email CHARACTER VARYING(256),
    is_active BOOLEAN NOT NULL,
    user_uuid CHARACTER VARYING(256)
);

Step 2- Setup Amazon Q data collection

For instructions on setting up Amazon Q metrics export, please contact your DX account manager.

Step 3- Prepare Amazon Q data

Download a raw export of your Amazon Q usage metrics and update the CSV file to match the schema in Step 1. Ensure the data includes:

date: the date of usage activity
email: user's email address
is_active: boolean indicating if the user was active on that date
user_uuid: unique identifier for the user (if available)

Step 4- Import Amazon Q data

Use the Import CSV feature in Postico or pgAdmin to upload your data to DX.

1. Right-click the custom.amazon_q_daily_usages table and click Import CSV

2. Select the formatted CSV from Step 3

3. Ensure "First row is header" is checked

4. Click Import

Step 5- View reports

Once your data is uploaded, use DX's prebuilt reports or use Data Studio to create custom reports. The example query below joins Amazon Q data and DX data, displaying Amazon Q daily usage alongside PR throughput.

SELECT 
  du.name,
  du.email,
  COUNT(DISTINCT pr.id) AS number_of_prs_merged,
  COUNT(DISTINCT aqu.date) FILTER (
    WHERE aqu.is_active = true
  ) AS amazon_q_days_active
FROM dx_users du
LEFT JOIN custom.amazon_q_daily_usages aqu 
  ON LOWER(du.email) = LOWER(aqu.email)
LEFT JOIN pull_requests pr 
  ON pr.dx_user_id = du.id AND pr.merged >= CURRENT_DATE - INTERVAL '30 days'
WHERE 
  aqu.date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY du.name, du.email;