Sourcegraph Cody

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

Prerequisites

• a Sourcegraph Cody account
• a DX database user with write access

Setup instructions

Follow the steps below to import Sourcegraph Cody data into DX.

Step 1– Create custom table

To import Sourcegraph Cody 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.cody_daily_usages (
    id SERIAL PRIMARY KEY,
    source_id bigint,
    user_email text,
    username text,
    client_name text,
    timestamp_date timestamp without time zone,
    language text,
    searches integer,
    code_navigation_events integer,
    code_insight_views integer,
    chat_events integer,
    command_events integer,
    combined_completion_suggestions integer,
    combined_completion_acceptances integer,
    total_accepted_char_count integer,
    combined_car numeric(8,6),
    weighted_car numeric(8,6)
);

Step 2- Export Cody data

Download a raw export of your Cody usage metrics and update the CSV file to match the schema in Step 1. Ensure the unique identifier from the Cody usage metrics is renamed to source_id.

Step 3- Import Cody data

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

1. Right-click the custom.cody_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 Sourcegraph Cody data and DX data, displaying Sourcegraph Cody daily usage alongside PR throughput.

SELECT
  du.name,
  du.email,
  COUNT(DISTINCT pr.id) AS number_of_prs_merged,
  COUNT(DISTINCT DATE(cdu.timestamp_date)) FILTER (
    WHERE
      COALESCE(searches, 0) > 0 OR
      COALESCE(code_navigation_events, 0) > 0 OR
      COALESCE(code_insight_views, 0) > 0 OR
      COALESCE(chat_events, 0) > 0 OR
      COALESCE(command_events, 0) > 0 OR
      COALESCE(combined_completion_suggestions, 0) > 0 OR
      COALESCE(combined_completion_acceptances, 0) > 0 OR
      COALESCE(total_accepted_char_count, 0) > 0
  ) AS cody_days_active
FROM dx_users du
LEFT JOIN custom.cody_daily_usages cdu
  ON LOWER(du.email) = LOWER(cdu.user_email)
LEFT JOIN pull_requests pr
  ON pr.dx_user_id = du.id AND pr.merged >= CURRENT_DATE - INTERVAL '30 days'
WHERE
  cdu.timestamp_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY du.name, du.email;