{
 "cells": [
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "DEV_ENGINEERING@orchard.snowflakecomputing.com",
     "variableName$1": "df_contract"
    },
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:29.751636Z",
     "start_time": "2026-02-10T21:40:02.615563Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "select\n",
    "    c.*,\n",
    "    ac.*,\n",
    "    a.*,\n",
    "    cl.*\n",
    "from ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT c\n",
    "inner join orchard_app_reporting_v2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_CONTRACT ac on ac.CONTRACT_ID = c.CONTRACT_ID\n",
    "inner join orchard_app_reporting_v2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT a on ac.ACCOUNT_ID = a.ACCOUNT_ID\n",
    "inner join ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_LIFECYCLE cl on cl.contract_id = c.contract_id"
   ],
   "id": "4d503cfaaa07198c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        CONTRACT_ID                      LAST_MODIFIED_BY TERM_START  \\\n",
       "0            297837                       default_user_id        NaN   \n",
       "1            142534                       default_user_id        NaN   \n",
       "2            214033                       default_user_id        NaN   \n",
       "3              9034                       default_user_id        NaN   \n",
       "4            334800                       default_user_id        NaN   \n",
       "...             ...                                   ...        ...   \n",
       "120487       556516  21b0f706-a27e-4d6d-b400-b3130ecdff36        NaN   \n",
       "120488       556517                       default_user_id        NaN   \n",
       "120489       556518  21b0f706-a27e-4d6d-b400-b3130ecdff36        NaN   \n",
       "120490       556519  21b0f706-a27e-4d6d-b400-b3130ecdff36        NaN   \n",
       "120491       556520  21b0f706-a27e-4d6d-b400-b3130ecdff36        NaN   \n",
       "\n",
       "                                  CREATED_BY                  LAST_MODIFIED  \\\n",
       "0                            default_user_id  2025-10-15 20:47:50.000000000   \n",
       "1                            default_user_id  2025-10-15 20:11:00.000000000   \n",
       "2                            default_user_id  2025-07-03 22:31:09.000000000   \n",
       "3                            default_user_id  2025-12-12 15:51:26.000000000   \n",
       "4                            default_user_id  2025-10-15 20:38:38.000000000   \n",
       "...                                      ...                            ...   \n",
       "120487  21b0f706-a27e-4d6d-b400-b3130ecdff36  2026-02-10 20:59:22.000000000   \n",
       "120488  e1af76de-a1e7-4900-8608-e0774e2fa948  2026-02-10 18:33:50.000000000   \n",
       "120489  21b0f706-a27e-4d6d-b400-b3130ecdff36  2026-02-10 18:42:32.000000000   \n",
       "120490  21b0f706-a27e-4d6d-b400-b3130ecdff36  2026-02-10 19:01:05.000000000   \n",
       "120491  21b0f706-a27e-4d6d-b400-b3130ecdff36  2026-02-10 19:46:35.000000000   \n",
       "\n",
       "                                            CONTRACT_NAME CONTRACT_TYPE  \\\n",
       "0                                  seu.jorge Distribution  distribution   \n",
       "1                       Little Big Sister Contract 142534  distribution   \n",
       "2       PHILIPPE BENKEMOUN DBA ROBSOUL RECORDINGS Cont...  distribution   \n",
       "3       Orchard NY Agreement - POL & LO ENTERTAINMENT,...  distribution   \n",
       "4                          Orchard Direct Agreement - A4A  distribution   \n",
       "...                                                   ...           ...   \n",
       "120487  Altafonte Network Agreement - La Señora Chafer SL  distribution   \n",
       "120488  AWAL Distribution Agreement - GA Chillerong Gh...  distribution   \n",
       "120489  Orchard NY Agreement - Pannawit Sanitnarathorn...  distribution   \n",
       "120490        Orchard NY Agreement - 29Eleven Records LLC  distribution   \n",
       "120491        Altafonte Agreement - Marina García Herrera  distribution   \n",
       "\n",
       "       TERM_END                     CREATED_AT  _FIVETRAN_DELETED  ...  \\\n",
       "0           NaN  2025-03-18 12:02:26.000000000              False  ...   \n",
       "1           NaN  2025-05-23 14:47:48.000000000              False  ...   \n",
       "2           NaN  2025-06-30 09:29:01.000000000              False  ...   \n",
       "3           NaN  2025-06-30 08:43:32.000000000              False  ...   \n",
       "4           NaN  2025-06-30 14:21:55.000000000              False  ...   \n",
       "...         ...                            ...                ...  ...   \n",
       "120487      NaN  2026-02-10 18:22:09.000000000              False  ...   \n",
       "120488      NaN  2026-02-10 18:33:48.000000000              False  ...   \n",
       "120489      NaN  2026-02-10 18:42:20.000000000              False  ...   \n",
       "120490      NaN  2026-02-10 19:00:50.000000000              False  ...   \n",
       "120491      NaN  2026-02-10 19:45:06.000000000              False  ...   \n",
       "\n",
       "       COLLECTION_END                   CREATED_AT.3  \\\n",
       "0                 NaN  2025-07-09 21:45:48.000000000   \n",
       "1                 NaN  2025-07-09 21:17:50.000000000   \n",
       "2                 NaN  2025-07-09 21:10:19.000000000   \n",
       "3                 NaN  2025-07-09 21:05:49.000000000   \n",
       "4                 NaN  2025-07-09 20:56:35.000000000   \n",
       "...               ...                            ...   \n",
       "120487            NaN  2026-02-10 18:22:09.000000000   \n",
       "120488            NaN  2026-02-10 18:33:48.000000000   \n",
       "120489            NaN  2026-02-10 18:42:20.000000000   \n",
       "120490            NaN  2026-02-10 19:00:50.000000000   \n",
       "120491            NaN  2026-02-10 19:45:06.000000000   \n",
       "\n",
       "                                CREATED_BY.3  CONTRACT_LIFECYCLE_SCHEDULE_ID  \\\n",
       "0       7c573432-e837-4f60-b2c0-1922cfefc669                          158144   \n",
       "1       7c573432-e837-4f60-b2c0-1922cfefc669                          158138   \n",
       "2       7c573432-e837-4f60-b2c0-1922cfefc669                          158136   \n",
       "3       7c573432-e837-4f60-b2c0-1922cfefc669                          158134   \n",
       "4       7c573432-e837-4f60-b2c0-1922cfefc669                          158132   \n",
       "...                                      ...                             ...   \n",
       "120487  21b0f706-a27e-4d6d-b400-b3130ecdff36                          190724   \n",
       "120488  e1af76de-a1e7-4900-8608-e0774e2fa948                          190726   \n",
       "120489  21b0f706-a27e-4d6d-b400-b3130ecdff36                          190727   \n",
       "120490  21b0f706-a27e-4d6d-b400-b3130ecdff36                          190729   \n",
       "120491  21b0f706-a27e-4d6d-b400-b3130ecdff36                          190731   \n",
       "\n",
       "        CONTRACT_ID.2 RENEWAL_EFFECTIVE COLLECTION_START  _FIVETRAN_DELETED.3  \\\n",
       "0              297837        2030-06-30              NaN                False   \n",
       "1              142534        2029-05-31              NaN                False   \n",
       "2              214033        2029-01-31              NaN                False   \n",
       "3                9034        2028-06-30              NaN                False   \n",
       "4              334800               NaN              NaN                False   \n",
       "...               ...               ...              ...                  ...   \n",
       "120487         556516        2028-02-29              NaN                False   \n",
       "120488         556517               NaN              NaN                False   \n",
       "120489         556518        2029-02-28              NaN                False   \n",
       "120490         556519        2030-02-28              NaN                False   \n",
       "120491         556520        2028-02-29              NaN                False   \n",
       "\n",
       "                 _FIVETRAN_SYNCED.3 LAST_RENEWED  \n",
       "0       Tue, 13 Jan 2026 14:43:37 Z          NaN  \n",
       "1       Tue, 13 Jan 2026 14:43:37 Z          NaN  \n",
       "2       Tue, 13 Jan 2026 14:43:37 Z          NaN  \n",
       "3       Tue, 13 Jan 2026 14:43:37 Z          NaN  \n",
       "4       Tue, 13 Jan 2026 14:43:37 Z          NaN  \n",
       "...                             ...          ...  \n",
       "120487  Tue, 10 Feb 2026 18:22:57 Z          NaN  \n",
       "120488  Tue, 10 Feb 2026 18:34:03 Z          NaN  \n",
       "120489  Tue, 10 Feb 2026 18:42:58 Z          NaN  \n",
       "120490  Tue, 10 Feb 2026 19:00:58 Z          NaN  \n",
       "120491  Tue, 10 Feb 2026 19:45:56 Z          NaN  \n",
       "\n",
       "[120492 rows x 68 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>CREATED_BY</th>\n",
       "      <th>LAST_MODIFIED</th>\n",
       "      <th>CONTRACT_NAME</th>\n",
       "      <th>CONTRACT_TYPE</th>\n",
       "      <th>TERM_END</th>\n",
       "      <th>CREATED_AT</th>\n",
       "      <th>_FIVETRAN_DELETED</th>\n",
       "      <th>...</th>\n",
       "      <th>COLLECTION_END</th>\n",
       "      <th>CREATED_AT.3</th>\n",
       "      <th>CREATED_BY.3</th>\n",
       "      <th>CONTRACT_LIFECYCLE_SCHEDULE_ID</th>\n",
       "      <th>CONTRACT_ID.2</th>\n",
       "      <th>RENEWAL_EFFECTIVE</th>\n",
       "      <th>COLLECTION_START</th>\n",
       "      <th>_FIVETRAN_DELETED.3</th>\n",
       "      <th>_FIVETRAN_SYNCED.3</th>\n",
       "      <th>LAST_RENEWED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>297837</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2025-10-15 20:47:50.000000000</td>\n",
       "      <td>seu.jorge Distribution</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-03-18 12:02:26.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-07-09 21:45:48.000000000</td>\n",
       "      <td>7c573432-e837-4f60-b2c0-1922cfefc669</td>\n",
       "      <td>158144</td>\n",
       "      <td>297837</td>\n",
       "      <td>2030-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:43:37 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>142534</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2025-10-15 20:11:00.000000000</td>\n",
       "      <td>Little Big Sister Contract 142534</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-05-23 14:47:48.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-07-09 21:17:50.000000000</td>\n",
       "      <td>7c573432-e837-4f60-b2c0-1922cfefc669</td>\n",
       "      <td>158138</td>\n",
       "      <td>142534</td>\n",
       "      <td>2029-05-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:43:37 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>214033</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2025-07-03 22:31:09.000000000</td>\n",
       "      <td>PHILIPPE BENKEMOUN DBA ROBSOUL RECORDINGS Cont...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:29:01.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-07-09 21:10:19.000000000</td>\n",
       "      <td>7c573432-e837-4f60-b2c0-1922cfefc669</td>\n",
       "      <td>158136</td>\n",
       "      <td>214033</td>\n",
       "      <td>2029-01-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:43:37 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9034</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2025-12-12 15:51:26.000000000</td>\n",
       "      <td>Orchard NY Agreement - POL &amp; LO ENTERTAINMENT,...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:43:32.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-07-09 21:05:49.000000000</td>\n",
       "      <td>7c573432-e837-4f60-b2c0-1922cfefc669</td>\n",
       "      <td>158134</td>\n",
       "      <td>9034</td>\n",
       "      <td>2028-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:43:37 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>334800</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2025-10-15 20:38:38.000000000</td>\n",
       "      <td>Orchard Direct Agreement - A4A</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 14:21:55.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-07-09 20:56:35.000000000</td>\n",
       "      <td>7c573432-e837-4f60-b2c0-1922cfefc669</td>\n",
       "      <td>158132</td>\n",
       "      <td>334800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:43:37 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120487</th>\n",
       "      <td>556516</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>2026-02-10 20:59:22.000000000</td>\n",
       "      <td>Altafonte Network Agreement - La Señora Chafer SL</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:22:09.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:22:09.000000000</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>190724</td>\n",
       "      <td>556516</td>\n",
       "      <td>2028-02-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 10 Feb 2026 18:22:57 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120488</th>\n",
       "      <td>556517</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e1af76de-a1e7-4900-8608-e0774e2fa948</td>\n",
       "      <td>2026-02-10 18:33:50.000000000</td>\n",
       "      <td>AWAL Distribution Agreement - GA Chillerong Gh...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:33:48.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:33:48.000000000</td>\n",
       "      <td>e1af76de-a1e7-4900-8608-e0774e2fa948</td>\n",
       "      <td>190726</td>\n",
       "      <td>556517</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 10 Feb 2026 18:34:03 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120489</th>\n",
       "      <td>556518</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>2026-02-10 18:42:32.000000000</td>\n",
       "      <td>Orchard NY Agreement - Pannawit Sanitnarathorn...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:42:20.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 18:42:20.000000000</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>190727</td>\n",
       "      <td>556518</td>\n",
       "      <td>2029-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 10 Feb 2026 18:42:58 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120490</th>\n",
       "      <td>556519</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>2026-02-10 19:01:05.000000000</td>\n",
       "      <td>Orchard NY Agreement - 29Eleven Records LLC</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 19:00:50.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 19:00:50.000000000</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>190729</td>\n",
       "      <td>556519</td>\n",
       "      <td>2030-02-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 10 Feb 2026 19:00:58 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120491</th>\n",
       "      <td>556520</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>2026-02-10 19:46:35.000000000</td>\n",
       "      <td>Altafonte Agreement - Marina García Herrera</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 19:45:06.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2026-02-10 19:45:06.000000000</td>\n",
       "      <td>21b0f706-a27e-4d6d-b400-b3130ecdff36</td>\n",
       "      <td>190731</td>\n",
       "      <td>556520</td>\n",
       "      <td>2028-02-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 10 Feb 2026 19:45:56 Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120492 rows × 68 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:29.851485Z",
     "start_time": "2026-02-10T21:40:29.775389Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ],
   "id": "924f89784fa3ce85",
   "outputs": [],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "a896c75e918685af"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Step 1: Load the data from the spreadsheet",
   "id": "b657fc6725876d17"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:29.930404Z",
     "start_time": "2026-02-10T21:40:29.873962Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df_in = pd.read_csv('data/ACC-9796-INPUT.csv')\n",
    "\n",
    "# reorder country ids to be in ascending order and remove any duplicates\n",
    "# Fix: handle non-string values and strip whitespace\n",
    "def clean_country_ids(x):\n",
    "    if pd.isnull(x):\n",
    "        return x\n",
    "    if not isinstance(x, str):\n",
    "        x = str(x)\n",
    "    items = [i.strip() for i in x.split(',') if i.strip()]\n",
    "    return ','.join(sorted(set(items), key=int))\n",
    "\n",
    "# Apply the cleaning function\n",
    "if 'Country IDs' in df_in.columns:\n",
    "    df_in['Country IDs'] = df_in['Country IDs'].apply(clean_country_ids)\n",
    "\n",
    "df_in"
   ],
   "id": "728086bf66181ef0",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      Account ID  Contract ID  Country Count  \\\n",
       "0           7507           41            242   \n",
       "1           7465          143            242   \n",
       "2          13988         4557            242   \n",
       "3          14237         4804            242   \n",
       "4          14680         5084            244   \n",
       "...          ...          ...            ...   \n",
       "1946       70087       278614            240   \n",
       "1947       70055       278615            242   \n",
       "1948       70116       278634            242   \n",
       "1949       11297       278684            242   \n",
       "1950      780558       279608            242   \n",
       "\n",
       "                                            Country IDs  \n",
       "0     1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...  \n",
       "1     1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...  \n",
       "2     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...  \n",
       "3     1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...  \n",
       "4     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...  \n",
       "...                                                 ...  \n",
       "1946  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...  \n",
       "1947  1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...  \n",
       "1948  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...  \n",
       "1949  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...  \n",
       "1950  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...  \n",
       "\n",
       "[1951 rows x 4 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7465</td>\n",
       "      <td>143</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13988</td>\n",
       "      <td>4557</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14237</td>\n",
       "      <td>4804</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14680</td>\n",
       "      <td>5084</td>\n",
       "      <td>244</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1946</th>\n",
       "      <td>70087</td>\n",
       "      <td>278614</td>\n",
       "      <td>240</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947</th>\n",
       "      <td>70055</td>\n",
       "      <td>278615</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948</th>\n",
       "      <td>70116</td>\n",
       "      <td>278634</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949</th>\n",
       "      <td>11297</td>\n",
       "      <td>278684</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1951 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 3
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Step 2: For each line item, check to see if the contract_id is an active contract on the account\n",
    "\n",
    "The definition for a contract being active is that there is a record in the CONTRACT_LIFECYCLE table with lifecycle_status = 'active' for that contract_id.\n",
    "As long as one lifecycle record exists with that status, the contract is considered active."
   ],
   "id": "115584104e2e8abe"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:30.669486Z",
     "start_time": "2026-02-10T21:40:30.630338Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# implement step 2: checking that each contract is active using df_contract\n",
    "\n",
    "# Make the names of the columns lowercase\n",
    "df_contract.columns = [col.lower() for col in df_contract.columns]\n",
    "\n",
    "# Identify active contracts from df_contract\n",
    "active_contracts = df_contract[df_contract['lifecycle_status'] == 'active']['contract_id']\n",
    "\n",
    "# Add a new column to df_in indicating if the contract is active\n",
    "if 'Contract ID' in df_in.columns:\n",
    "    df_in['is_active'] = df_in['Contract ID'].isin(active_contracts)\n",
    "else:\n",
    "    raise KeyError('contract_id column not found in df_in')\n",
    "\n"
   ],
   "id": "1d038a7258c801d0",
   "outputs": [],
   "execution_count": 4
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Step 3: If the contract ID is NOT active, find the active distribution contract on the account.\n",
    "If there is more than one, flag this line as requiring manual review and move to the next line."
   ],
   "id": "9e06a6afd8808a49"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:30.687353Z",
     "start_time": "2026-02-10T21:40:30.674799Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# implement step 3: If the contract ID is NOT active, find the active distribution contract on the account.\n",
    "# If there is more than one, flag this line as requiring manual review and move to the next line.\n",
    "\n",
    "\n",
    "df_accounts_with_inactive_contracts = df_in[~df_in['is_active']]\n",
    "\n",
    "df_accounts_with_inactive_contracts"
   ],
   "id": "1a8d30c70c4cd308",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      Account ID  Contract ID  Country Count  \\\n",
       "203        18494        36293              1   \n",
       "1066       24789       112776            242   \n",
       "1201       66649       187359            242   \n",
       "1205       66975       201930            242   \n",
       "1206       66979       201932            241   \n",
       "...          ...          ...            ...   \n",
       "1805      778415       270237            235   \n",
       "1917       25314       272529            242   \n",
       "1923       66969       272541            241   \n",
       "1927      779199       274118            242   \n",
       "1946       70087       278614            240   \n",
       "\n",
       "                                            Country IDs  is_active  \n",
       "203                                                   1      False  \n",
       "1066  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False  \n",
       "1201  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "1205  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "1206  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False  \n",
       "...                                                 ...        ...  \n",
       "1805  1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "1917  1,2,3,4,5,6,8,9,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "1923  1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "1927  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False  \n",
       "1946  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...      False  \n",
       "\n",
       "[115 rows x 5 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "      <th>is_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>18494</td>\n",
       "      <td>36293</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>24789</td>\n",
       "      <td>112776</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1201</th>\n",
       "      <td>66649</td>\n",
       "      <td>187359</td>\n",
       "      <td>242</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1205</th>\n",
       "      <td>66975</td>\n",
       "      <td>201930</td>\n",
       "      <td>242</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1206</th>\n",
       "      <td>66979</td>\n",
       "      <td>201932</td>\n",
       "      <td>241</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>778415</td>\n",
       "      <td>270237</td>\n",
       "      <td>235</td>\n",
       "      <td>1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1917</th>\n",
       "      <td>25314</td>\n",
       "      <td>272529</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1923</th>\n",
       "      <td>66969</td>\n",
       "      <td>272541</td>\n",
       "      <td>241</td>\n",
       "      <td>1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1927</th>\n",
       "      <td>779199</td>\n",
       "      <td>274118</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1946</th>\n",
       "      <td>70087</td>\n",
       "      <td>278614</td>\n",
       "      <td>240</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:31.083509Z",
     "start_time": "2026-02-10T21:40:30.713981Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\n",
    "def find_active_distribution_contract(row, df_contract):\n",
    "    if row['is_active']:\n",
    "        return row['Contract ID'], False  # No manual review needed\n",
    "\n",
    "    # Filter contracts for the same account and active status\n",
    "    account_id = row['Account ID']\n",
    "    active_distribution_contracts = df_contract[\n",
    "        (df_contract['account_id'] == account_id) &\n",
    "        (df_contract['lifecycle_status'] == 'active')\n",
    "        ]\n",
    "\n",
    "    if len(active_distribution_contracts) == 1:\n",
    "        return active_distribution_contracts.iloc[0]['contract_id'], False  # Found one active distribution contract\n",
    "    elif len(active_distribution_contracts) > 1:\n",
    "        return len(\n",
    "            active_distribution_contracts), True  # Multiple active distribution contracts found, manual review needed\n",
    "    else:\n",
    "        return None, False  # No active distribution contract found\n",
    "\n",
    "\n",
    "# Apply the function to each row in df_in\n",
    "df_in[['active_distribution_contract_id', 'manual_review_needed']] = df_in.apply(\n",
    "    lambda row: find_active_distribution_contract(row, df_contract),\n",
    "    axis=1,\n",
    "    result_type='expand'\n",
    ")\n",
    "\n",
    "df_accounts_that_need_manual_review = df_in[df_in['manual_review_needed']]\n",
    "df_accounts_that_need_manual_review['abacus_link'] = df_accounts_that_need_manual_review.apply(\n",
    "    lambda row: f\"https://abacus.theorchard.com/account/{row['Account ID']}\",\n",
    "    axis=1\n",
    ")\n",
    "df_accounts_that_need_manual_review"
   ],
   "id": "b218571c8bf19743",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      Account ID  Contract ID  Country Count  \\\n",
       "1220       68200       201955            242   \n",
       "1248       69654       201999            241   \n",
       "1299       68324       223275            241   \n",
       "1304       68713       223280            241   \n",
       "1305       68892       223281            241   \n",
       "1323       74945       223948            236   \n",
       "1327       75080       225130            241   \n",
       "1440       76695       231669            240   \n",
       "1443       75850       232215            240   \n",
       "1444       76034       232217            242   \n",
       "1445       75842       232218            242   \n",
       "1446       75912       232219            241   \n",
       "1448       75873       232221            242   \n",
       "1450       75920       232223            242   \n",
       "1451       75981       232224            242   \n",
       "1452       76068       232225            242   \n",
       "1453       75925       232226            242   \n",
       "1454       75879       232227            242   \n",
       "1460       75803       232233            242   \n",
       "1684       82830       253041            203   \n",
       "\n",
       "                                            Country IDs  is_active  \\\n",
       "1220  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1248  2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20...      False   \n",
       "1299  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1304  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1305  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1323  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1327  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1440  1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1443  3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20...      False   \n",
       "1444  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1445  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1446  2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1448  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1450  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1451  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1452  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1453  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1454  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1460  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...      False   \n",
       "1684  2,3,7,8,9,10,13,17,18,19,20,21,22,23,24,25,26,...      False   \n",
       "\n",
       "      active_distribution_contract_id  manual_review_needed  \\\n",
       "1220                              3.0                  True   \n",
       "1248                              2.0                  True   \n",
       "1299                              2.0                  True   \n",
       "1304                              2.0                  True   \n",
       "1305                              2.0                  True   \n",
       "1323                              3.0                  True   \n",
       "1327                              2.0                  True   \n",
       "1440                              2.0                  True   \n",
       "1443                              2.0                  True   \n",
       "1444                              2.0                  True   \n",
       "1445                              2.0                  True   \n",
       "1446                              2.0                  True   \n",
       "1448                              2.0                  True   \n",
       "1450                              2.0                  True   \n",
       "1451                              2.0                  True   \n",
       "1452                              2.0                  True   \n",
       "1453                              2.0                  True   \n",
       "1454                              2.0                  True   \n",
       "1460                              2.0                  True   \n",
       "1684                              2.0                  True   \n",
       "\n",
       "                                      abacus_link  \n",
       "1220  https://abacus.theorchard.com/account/68200  \n",
       "1248  https://abacus.theorchard.com/account/69654  \n",
       "1299  https://abacus.theorchard.com/account/68324  \n",
       "1304  https://abacus.theorchard.com/account/68713  \n",
       "1305  https://abacus.theorchard.com/account/68892  \n",
       "1323  https://abacus.theorchard.com/account/74945  \n",
       "1327  https://abacus.theorchard.com/account/75080  \n",
       "1440  https://abacus.theorchard.com/account/76695  \n",
       "1443  https://abacus.theorchard.com/account/75850  \n",
       "1444  https://abacus.theorchard.com/account/76034  \n",
       "1445  https://abacus.theorchard.com/account/75842  \n",
       "1446  https://abacus.theorchard.com/account/75912  \n",
       "1448  https://abacus.theorchard.com/account/75873  \n",
       "1450  https://abacus.theorchard.com/account/75920  \n",
       "1451  https://abacus.theorchard.com/account/75981  \n",
       "1452  https://abacus.theorchard.com/account/76068  \n",
       "1453  https://abacus.theorchard.com/account/75925  \n",
       "1454  https://abacus.theorchard.com/account/75879  \n",
       "1460  https://abacus.theorchard.com/account/75803  \n",
       "1684  https://abacus.theorchard.com/account/82830  "
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "      <th>is_active</th>\n",
       "      <th>active_distribution_contract_id</th>\n",
       "      <th>manual_review_needed</th>\n",
       "      <th>abacus_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1220</th>\n",
       "      <td>68200</td>\n",
       "      <td>201955</td>\n",
       "      <td>242</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/68200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1248</th>\n",
       "      <td>69654</td>\n",
       "      <td>201999</td>\n",
       "      <td>241</td>\n",
       "      <td>2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/69654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299</th>\n",
       "      <td>68324</td>\n",
       "      <td>223275</td>\n",
       "      <td>241</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/68324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304</th>\n",
       "      <td>68713</td>\n",
       "      <td>223280</td>\n",
       "      <td>241</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/68713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1305</th>\n",
       "      <td>68892</td>\n",
       "      <td>223281</td>\n",
       "      <td>241</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/68892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1323</th>\n",
       "      <td>74945</td>\n",
       "      <td>223948</td>\n",
       "      <td>236</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/74945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1327</th>\n",
       "      <td>75080</td>\n",
       "      <td>225130</td>\n",
       "      <td>241</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1440</th>\n",
       "      <td>76695</td>\n",
       "      <td>231669</td>\n",
       "      <td>240</td>\n",
       "      <td>1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/76695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1443</th>\n",
       "      <td>75850</td>\n",
       "      <td>232215</td>\n",
       "      <td>240</td>\n",
       "      <td>3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1444</th>\n",
       "      <td>76034</td>\n",
       "      <td>232217</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/76034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1445</th>\n",
       "      <td>75842</td>\n",
       "      <td>232218</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1446</th>\n",
       "      <td>75912</td>\n",
       "      <td>232219</td>\n",
       "      <td>241</td>\n",
       "      <td>2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1448</th>\n",
       "      <td>75873</td>\n",
       "      <td>232221</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1450</th>\n",
       "      <td>75920</td>\n",
       "      <td>232223</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1451</th>\n",
       "      <td>75981</td>\n",
       "      <td>232224</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1452</th>\n",
       "      <td>76068</td>\n",
       "      <td>232225</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/76068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1453</th>\n",
       "      <td>75925</td>\n",
       "      <td>232226</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1454</th>\n",
       "      <td>75879</td>\n",
       "      <td>232227</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1460</th>\n",
       "      <td>75803</td>\n",
       "      <td>232233</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/75803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1684</th>\n",
       "      <td>82830</td>\n",
       "      <td>253041</td>\n",
       "      <td>203</td>\n",
       "      <td>2,3,7,8,9,10,13,17,18,19,20,21,22,23,24,25,26,...</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>https://abacus.theorchard.com/account/82830</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Intermediate Step: fix all the accounts with multiple active contracts by\n",
    "loading them from the file with accounts hand-picked"
   ],
   "id": "f5e416d702024902"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:40:31.328796Z",
     "start_time": "2026-02-10T21:40:31.290484Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df_corrected_accounts = pd.read_csv('data/ACC-9796-contract_corrections.csv')\n",
    "\n",
    "# Merge df_in with df_corrected_accounts on 'Account ID'\n",
    "df_merged = df_in.merge(df_corrected_accounts[['Account ID', 'Contract To Use']], on='Account ID', how='left')\n",
    "\n",
    "# Override 'Contract ID' with 'Contract To Use' where available\n",
    "df_merged['Contract ID'] = df_merged['Contract To Use'].combine_first(df_merged['Contract ID'])\n",
    "\n",
    "# Create a new DataFrame with the corrected contract IDs\n",
    "df_in_corrected = df_merged.copy()\n",
    "\n",
    "# Optionally, drop the 'Contract To Use' column if not needed\n",
    "df_in_corrected = df_in_corrected.drop(columns=['Contract To Use'])\n",
    "\n",
    "df_in_corrected['Contract ID'] = df_in_corrected['Contract ID'].astype(int)\n",
    "\n",
    "df_in_corrected"
   ],
   "id": "2e98af19c1168f95",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      Account ID  Contract ID  Country Count  \\\n",
       "0           7507           41            242   \n",
       "1           7465          143            242   \n",
       "2          13988         4557            242   \n",
       "3          14237         4804            242   \n",
       "4          14680         5084            244   \n",
       "...          ...          ...            ...   \n",
       "1946       70087       278614            240   \n",
       "1947       70055       278615            242   \n",
       "1948       70116       278634            242   \n",
       "1949       11297       278684            242   \n",
       "1950      780558       279608            242   \n",
       "\n",
       "                                            Country IDs  is_active  \\\n",
       "0     1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "1     1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...       True   \n",
       "2     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "3     1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "4     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "...                                                 ...        ...   \n",
       "1946  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "1947  1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...       True   \n",
       "1948  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "1949  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "1950  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "\n",
       "      active_distribution_contract_id  manual_review_needed  \n",
       "0                                41.0                 False  \n",
       "1                               143.0                 False  \n",
       "2                              4557.0                 False  \n",
       "3                              4804.0                 False  \n",
       "4                              5084.0                 False  \n",
       "...                               ...                   ...  \n",
       "1946                              NaN                 False  \n",
       "1947                         278615.0                 False  \n",
       "1948                         278634.0                 False  \n",
       "1949                         278684.0                 False  \n",
       "1950                         279608.0                 False  \n",
       "\n",
       "[1951 rows x 7 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "      <th>is_active</th>\n",
       "      <th>active_distribution_contract_id</th>\n",
       "      <th>manual_review_needed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7465</td>\n",
       "      <td>143</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...</td>\n",
       "      <td>True</td>\n",
       "      <td>143.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13988</td>\n",
       "      <td>4557</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>4557.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14237</td>\n",
       "      <td>4804</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>4804.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14680</td>\n",
       "      <td>5084</td>\n",
       "      <td>244</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>5084.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1946</th>\n",
       "      <td>70087</td>\n",
       "      <td>278614</td>\n",
       "      <td>240</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947</th>\n",
       "      <td>70055</td>\n",
       "      <td>278615</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...</td>\n",
       "      <td>True</td>\n",
       "      <td>278615.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948</th>\n",
       "      <td>70116</td>\n",
       "      <td>278634</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>278634.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1949</th>\n",
       "      <td>11297</td>\n",
       "      <td>278684</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>278684.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1951 rows × 7 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 7
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Step 4\n",
    "\n",
    "\n",
    "With an active contract identified, look at all contract term conditions in df_in_corrected for any containing one of the reference_transaction_type_id values noted in (3,58,71,72,73,74,75,76,77,78,79,81,82,83,84,85,86,87,88)\n",
    "\n",
    "If such a contract term condition is found AND the countries list is empty, insert the country codes into the countries list for that contract term condition."
   ],
   "id": "3763ebd67bc40137"
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "DEV_ENGINEERING@orchard.snowflakecomputing.com",
     "variableName$1": "df_all_contract_term_conditions"
    },
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:32.278148Z",
     "start_time": "2026-02-10T21:40:31.456056Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "-- Step 4: Query contract term conditions for relevant contracts and reference_transaction_type_ids\n",
    "select *\n",
    "from ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT c\n",
    "inner join ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM ct on ct.CONTRACT_ID = c.CONTRACT_ID\n",
    "inner join ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_condition ctc on ctc.CONTRACT_TERM_ID = ct.CONTRACT_TERM_ID\n",
    "\n",
    " --reference_transaction_type_id IN (3,58,71,72,73,74,75,76,77,78,79,81,82,83,84,85,86,87,88)"
   ],
   "id": "8aa0a837be3e3e13",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        CONTRACT_ID LAST_MODIFIED_BY  TERM_START     CREATED_BY  \\\n",
       "0            511964  default_user_id  2019-07-05  sax_ingestion   \n",
       "1            507383  default_user_id  2013-06-24  sax_ingestion   \n",
       "2            507259  default_user_id  2013-03-22  sax_ingestion   \n",
       "3            507711  default_user_id  2014-06-02  sax_ingestion   \n",
       "4            514506  default_user_id  2021-10-12  sax_ingestion   \n",
       "...             ...              ...         ...            ...   \n",
       "382462       505827  default_user_id  2012-03-13  sax_ingestion   \n",
       "382463       524128  default_user_id  2018-11-26  sax_ingestion   \n",
       "382464       503663  default_user_id  2018-04-03  sax_ingestion   \n",
       "382465       521732  default_user_id  2012-06-07  sax_ingestion   \n",
       "382466       504379  default_user_id  2018-02-28  sax_ingestion   \n",
       "\n",
       "                        LAST_MODIFIED  \\\n",
       "0       2025-07-08 03:24:50.000000000   \n",
       "1       2025-07-08 02:23:12.000000000   \n",
       "2       2025-07-08 20:37:36.000000000   \n",
       "3       2025-07-08 19:26:18.000000000   \n",
       "4       2025-07-08 01:57:20.000000000   \n",
       "...                               ...   \n",
       "382462  2025-07-08 06:38:55.000000000   \n",
       "382463  2025-07-08 13:58:16.000000000   \n",
       "382464  2025-07-08 13:38:13.000000000   \n",
       "382465  2025-07-08 14:35:50.000000000   \n",
       "382466  2025-07-08 08:16:08.000000000   \n",
       "\n",
       "                                            CONTRACT_NAME CONTRACT_TYPE  \\\n",
       "0       AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...  distribution   \n",
       "1       AGR281643 - Funky Street - AWAL Distribution A...  distribution   \n",
       "2       AGR266998 - The Harold Fergus Duo - AWAL Distr...  distribution   \n",
       "3       AGR319539 - Europium Records - AWAL Distributi...  distribution   \n",
       "4       AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...  distribution   \n",
       "...                                                   ...           ...   \n",
       "382462     AGR235729 - Tali - AWAL Distribution Agreement  distribution   \n",
       "382463  AGR556972 - Legacy Digitals - AWAL Distributio...  distribution   \n",
       "382464  AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...  distribution   \n",
       "382465  AGR245830 - Chantal Lappin & Lynsey Stewart - ...  distribution   \n",
       "382466  AGR516550 - Thomas, Matt - AWAL Distribution A...  distribution   \n",
       "\n",
       "       TERM_END                     CREATED_AT  _FIVETRAN_DELETED  ...  \\\n",
       "0           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "1           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "2           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "3           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "4           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "...         ...                            ...                ...  ...   \n",
       "382462      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382463      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382464      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382465      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382466      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "\n",
       "                      LAST_MODIFIED.2 LAST_MODIFIED_BY.2  \\\n",
       "0       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "1       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "2       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "3       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "4       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "...                               ...                ...   \n",
       "382462  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382463  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382464  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382465  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382466  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "\n",
       "                                               CONDITIONS  TERM_RATE  \\\n",
       "0       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "1       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "2       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "3       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "4       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "...                                                   ...        ...   \n",
       "382462  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382463  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382464  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382465  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382466  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "\n",
       "        DELETED_BY.1                   CREATED_AT.2 _FIVETRAN_DELETED.2  \\\n",
       "0                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "1                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "2                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "3                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "4                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "...              ...                            ...                 ...   \n",
       "382462           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382463           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382464           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382465           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382466           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "\n",
       "                 _FIVETRAN_SYNCED.2 CONTRACT_TERM_CONDITION_NAME COMMISSION  \n",
       "0       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "1       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "2       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "3       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "4       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "...                             ...                          ...        ...  \n",
       "382462  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382463  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382464  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382465  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382466  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "\n",
       "[382467 rows x 54 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>CREATED_BY</th>\n",
       "      <th>LAST_MODIFIED</th>\n",
       "      <th>CONTRACT_NAME</th>\n",
       "      <th>CONTRACT_TYPE</th>\n",
       "      <th>TERM_END</th>\n",
       "      <th>CREATED_AT</th>\n",
       "      <th>_FIVETRAN_DELETED</th>\n",
       "      <th>...</th>\n",
       "      <th>LAST_MODIFIED.2</th>\n",
       "      <th>LAST_MODIFIED_BY.2</th>\n",
       "      <th>CONDITIONS</th>\n",
       "      <th>TERM_RATE</th>\n",
       "      <th>DELETED_BY.1</th>\n",
       "      <th>CREATED_AT.2</th>\n",
       "      <th>_FIVETRAN_DELETED.2</th>\n",
       "      <th>_FIVETRAN_SYNCED.2</th>\n",
       "      <th>CONTRACT_TERM_CONDITION_NAME</th>\n",
       "      <th>COMMISSION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>511964</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 03:24:50.000000000</td>\n",
       "      <td>AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>507383</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 02:23:12.000000000</td>\n",
       "      <td>AGR281643 - Funky Street - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>507259</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-03-22</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 20:37:36.000000000</td>\n",
       "      <td>AGR266998 - The Harold Fergus Duo - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>507711</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2014-06-02</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 19:26:18.000000000</td>\n",
       "      <td>AGR319539 - Europium Records - AWAL Distributi...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>514506</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 01:57:20.000000000</td>\n",
       "      <td>AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382462</th>\n",
       "      <td>505827</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-03-13</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 06:38:55.000000000</td>\n",
       "      <td>AGR235729 - Tali - AWAL Distribution Agreement</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382463</th>\n",
       "      <td>524128</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-11-26</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:58:16.000000000</td>\n",
       "      <td>AGR556972 - Legacy Digitals - AWAL Distributio...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382464</th>\n",
       "      <td>503663</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:38:13.000000000</td>\n",
       "      <td>AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382465</th>\n",
       "      <td>521732</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-06-07</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 14:35:50.000000000</td>\n",
       "      <td>AGR245830 - Chantal Lappin &amp; Lynsey Stewart - ...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382466</th>\n",
       "      <td>504379</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 08:16:08.000000000</td>\n",
       "      <td>AGR516550 - Thomas, Matt - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>382467 rows × 54 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:33.806225Z",
     "start_time": "2026-02-10T21:42:33.602172Z"
    }
   },
   "cell_type": "code",
   "source": "df_all_contract_term_conditions",
   "id": "3a91c20cf891dffa",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        CONTRACT_ID LAST_MODIFIED_BY  TERM_START     CREATED_BY  \\\n",
       "0            511964  default_user_id  2019-07-05  sax_ingestion   \n",
       "1            507383  default_user_id  2013-06-24  sax_ingestion   \n",
       "2            507259  default_user_id  2013-03-22  sax_ingestion   \n",
       "3            507711  default_user_id  2014-06-02  sax_ingestion   \n",
       "4            514506  default_user_id  2021-10-12  sax_ingestion   \n",
       "...             ...              ...         ...            ...   \n",
       "382462       505827  default_user_id  2012-03-13  sax_ingestion   \n",
       "382463       524128  default_user_id  2018-11-26  sax_ingestion   \n",
       "382464       503663  default_user_id  2018-04-03  sax_ingestion   \n",
       "382465       521732  default_user_id  2012-06-07  sax_ingestion   \n",
       "382466       504379  default_user_id  2018-02-28  sax_ingestion   \n",
       "\n",
       "                        LAST_MODIFIED  \\\n",
       "0       2025-07-08 03:24:50.000000000   \n",
       "1       2025-07-08 02:23:12.000000000   \n",
       "2       2025-07-08 20:37:36.000000000   \n",
       "3       2025-07-08 19:26:18.000000000   \n",
       "4       2025-07-08 01:57:20.000000000   \n",
       "...                               ...   \n",
       "382462  2025-07-08 06:38:55.000000000   \n",
       "382463  2025-07-08 13:58:16.000000000   \n",
       "382464  2025-07-08 13:38:13.000000000   \n",
       "382465  2025-07-08 14:35:50.000000000   \n",
       "382466  2025-07-08 08:16:08.000000000   \n",
       "\n",
       "                                            CONTRACT_NAME CONTRACT_TYPE  \\\n",
       "0       AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...  distribution   \n",
       "1       AGR281643 - Funky Street - AWAL Distribution A...  distribution   \n",
       "2       AGR266998 - The Harold Fergus Duo - AWAL Distr...  distribution   \n",
       "3       AGR319539 - Europium Records - AWAL Distributi...  distribution   \n",
       "4       AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...  distribution   \n",
       "...                                                   ...           ...   \n",
       "382462     AGR235729 - Tali - AWAL Distribution Agreement  distribution   \n",
       "382463  AGR556972 - Legacy Digitals - AWAL Distributio...  distribution   \n",
       "382464  AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...  distribution   \n",
       "382465  AGR245830 - Chantal Lappin & Lynsey Stewart - ...  distribution   \n",
       "382466  AGR516550 - Thomas, Matt - AWAL Distribution A...  distribution   \n",
       "\n",
       "       TERM_END                     CREATED_AT  _FIVETRAN_DELETED  ...  \\\n",
       "0           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "1           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "2           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "3           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "4           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "...         ...                            ...                ...  ...   \n",
       "382462      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382463      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382464      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382465      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382466      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "\n",
       "                      LAST_MODIFIED.2 LAST_MODIFIED_BY.2  \\\n",
       "0       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "1       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "2       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "3       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "4       2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "...                               ...                ...   \n",
       "382462  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382463  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382464  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382465  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "382466  2025-10-08 17:25:23.000000000           ACC-9187   \n",
       "\n",
       "                                               CONDITIONS  TERM_RATE  \\\n",
       "0       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "1       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "2       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "3       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "4       {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "...                                                   ...        ...   \n",
       "382462  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382463  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382464  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382465  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "382466  {\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...       85.0   \n",
       "\n",
       "        DELETED_BY.1                   CREATED_AT.2 _FIVETRAN_DELETED.2  \\\n",
       "0                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "1                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "2                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "3                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "4                NaN  2022-09-16 08:53:05.000000000               False   \n",
       "...              ...                            ...                 ...   \n",
       "382462           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382463           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382464           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382465           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "382466           NaN  2022-09-16 08:53:05.000000000               False   \n",
       "\n",
       "                 _FIVETRAN_SYNCED.2 CONTRACT_TERM_CONDITION_NAME COMMISSION  \n",
       "0       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "1       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "2       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "3       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "4       Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "...                             ...                          ...        ...  \n",
       "382462  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382463  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382464  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382465  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "382466  Tue, 13 Jan 2026 14:40:17 Z                          NaN       15.0  \n",
       "\n",
       "[382467 rows x 54 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>CREATED_BY</th>\n",
       "      <th>LAST_MODIFIED</th>\n",
       "      <th>CONTRACT_NAME</th>\n",
       "      <th>CONTRACT_TYPE</th>\n",
       "      <th>TERM_END</th>\n",
       "      <th>CREATED_AT</th>\n",
       "      <th>_FIVETRAN_DELETED</th>\n",
       "      <th>...</th>\n",
       "      <th>LAST_MODIFIED.2</th>\n",
       "      <th>LAST_MODIFIED_BY.2</th>\n",
       "      <th>CONDITIONS</th>\n",
       "      <th>TERM_RATE</th>\n",
       "      <th>DELETED_BY.1</th>\n",
       "      <th>CREATED_AT.2</th>\n",
       "      <th>_FIVETRAN_DELETED.2</th>\n",
       "      <th>_FIVETRAN_SYNCED.2</th>\n",
       "      <th>CONTRACT_TERM_CONDITION_NAME</th>\n",
       "      <th>COMMISSION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>511964</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 03:24:50.000000000</td>\n",
       "      <td>AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>507383</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 02:23:12.000000000</td>\n",
       "      <td>AGR281643 - Funky Street - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>507259</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-03-22</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 20:37:36.000000000</td>\n",
       "      <td>AGR266998 - The Harold Fergus Duo - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>507711</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2014-06-02</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 19:26:18.000000000</td>\n",
       "      <td>AGR319539 - Europium Records - AWAL Distributi...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>514506</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 01:57:20.000000000</td>\n",
       "      <td>AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382462</th>\n",
       "      <td>505827</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-03-13</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 06:38:55.000000000</td>\n",
       "      <td>AGR235729 - Tali - AWAL Distribution Agreement</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382463</th>\n",
       "      <td>524128</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-11-26</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:58:16.000000000</td>\n",
       "      <td>AGR556972 - Legacy Digitals - AWAL Distributio...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382464</th>\n",
       "      <td>503663</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:38:13.000000000</td>\n",
       "      <td>AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382465</th>\n",
       "      <td>521732</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-06-07</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 14:35:50.000000000</td>\n",
       "      <td>AGR245830 - Chantal Lappin &amp; Lynsey Stewart - ...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382466</th>\n",
       "      <td>504379</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 08:16:08.000000000</td>\n",
       "      <td>AGR516550 - Thomas, Matt - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>2025-10-08 17:25:23.000000000</td>\n",
       "      <td>ACC-9187</td>\n",
       "      <td>{\\n  \"countries\": [],\\n  \"stores\": [],\\n  \"tra...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>382467 rows × 54 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:35.109234Z",
     "start_time": "2026-02-10T21:42:33.878138Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# get contract records for each of the contracts in df_in_corrected\n",
    "import json\n",
    "\n",
    "# parse text to json\n",
    "df_all_contract_term_conditions['parsed_conditions'] = df_all_contract_term_conditions['CONDITIONS'].apply(json.loads)\n",
    "df_all_contract_term_conditions['transaction_types'] = df_all_contract_term_conditions['parsed_conditions'].apply(\n",
    "    lambda x: x.get('transaction_types'))\n",
    "# pull out countries as its own value\n",
    "df_all_contract_term_conditions['countries'] = df_all_contract_term_conditions['parsed_conditions'].apply(\n",
    "    lambda x: x.get('countries', []))\n",
    "\n",
    "tmp = df_all_contract_term_conditions['parsed_conditions']\n",
    "\n",
    "tmp\n"
   ],
   "id": "f9847302b0a4b1a2",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         {'countries': [], 'stores': [], 'transaction_t...\n",
       "1         {'countries': [], 'stores': [], 'transaction_t...\n",
       "2         {'countries': [], 'stores': [], 'transaction_t...\n",
       "3         {'countries': [], 'stores': [], 'transaction_t...\n",
       "4         {'countries': [], 'stores': [], 'transaction_t...\n",
       "                                ...                        \n",
       "382462    {'countries': [], 'stores': [], 'transaction_t...\n",
       "382463    {'countries': [], 'stores': [], 'transaction_t...\n",
       "382464    {'countries': [], 'stores': [], 'transaction_t...\n",
       "382465    {'countries': [], 'stores': [], 'transaction_t...\n",
       "382466    {'countries': [], 'stores': [], 'transaction_t...\n",
       "Name: parsed_conditions, Length: 382467, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:36.652177Z",
     "start_time": "2026-02-10T21:42:36.313724Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df_all_contract_term_conditions = df_all_contract_term_conditions[\n",
    "    df_all_contract_term_conditions['countries'].apply(lambda x: isinstance(x, list) and len(x) == 0)]\n",
    "\n",
    "df_all_contract_term_conditions"
   ],
   "id": "e738250c8124f06b",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        CONTRACT_ID LAST_MODIFIED_BY  TERM_START     CREATED_BY  \\\n",
       "0            511964  default_user_id  2019-07-05  sax_ingestion   \n",
       "1            507383  default_user_id  2013-06-24  sax_ingestion   \n",
       "2            507259  default_user_id  2013-03-22  sax_ingestion   \n",
       "3            507711  default_user_id  2014-06-02  sax_ingestion   \n",
       "4            514506  default_user_id  2021-10-12  sax_ingestion   \n",
       "...             ...              ...         ...            ...   \n",
       "382462       505827  default_user_id  2012-03-13  sax_ingestion   \n",
       "382463       524128  default_user_id  2018-11-26  sax_ingestion   \n",
       "382464       503663  default_user_id  2018-04-03  sax_ingestion   \n",
       "382465       521732  default_user_id  2012-06-07  sax_ingestion   \n",
       "382466       504379  default_user_id  2018-02-28  sax_ingestion   \n",
       "\n",
       "                        LAST_MODIFIED  \\\n",
       "0       2025-07-08 03:24:50.000000000   \n",
       "1       2025-07-08 02:23:12.000000000   \n",
       "2       2025-07-08 20:37:36.000000000   \n",
       "3       2025-07-08 19:26:18.000000000   \n",
       "4       2025-07-08 01:57:20.000000000   \n",
       "...                               ...   \n",
       "382462  2025-07-08 06:38:55.000000000   \n",
       "382463  2025-07-08 13:58:16.000000000   \n",
       "382464  2025-07-08 13:38:13.000000000   \n",
       "382465  2025-07-08 14:35:50.000000000   \n",
       "382466  2025-07-08 08:16:08.000000000   \n",
       "\n",
       "                                            CONTRACT_NAME CONTRACT_TYPE  \\\n",
       "0       AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...  distribution   \n",
       "1       AGR281643 - Funky Street - AWAL Distribution A...  distribution   \n",
       "2       AGR266998 - The Harold Fergus Duo - AWAL Distr...  distribution   \n",
       "3       AGR319539 - Europium Records - AWAL Distributi...  distribution   \n",
       "4       AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...  distribution   \n",
       "...                                                   ...           ...   \n",
       "382462     AGR235729 - Tali - AWAL Distribution Agreement  distribution   \n",
       "382463  AGR556972 - Legacy Digitals - AWAL Distributio...  distribution   \n",
       "382464  AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...  distribution   \n",
       "382465  AGR245830 - Chantal Lappin & Lynsey Stewart - ...  distribution   \n",
       "382466  AGR516550 - Thomas, Matt - AWAL Distribution A...  distribution   \n",
       "\n",
       "       TERM_END                     CREATED_AT  _FIVETRAN_DELETED  ...  \\\n",
       "0           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "1           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "2           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "3           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "4           NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "...         ...                            ...                ...  ...   \n",
       "382462      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382463      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382464      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382465      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "382466      NaN  2022-09-08 19:42:39.000000000              False  ...   \n",
       "\n",
       "       TERM_RATE DELETED_BY.1                   CREATED_AT.2  \\\n",
       "0           85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "1           85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "2           85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "3           85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "4           85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "...          ...          ...                            ...   \n",
       "382462      85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "382463      85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "382464      85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "382465      85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "382466      85.0          NaN  2022-09-16 08:53:05.000000000   \n",
       "\n",
       "        _FIVETRAN_DELETED.2           _FIVETRAN_SYNCED.2  \\\n",
       "0                     False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "1                     False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "2                     False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "3                     False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "4                     False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "...                     ...                          ...   \n",
       "382462                False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "382463                False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "382464                False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "382465                False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "382466                False  Tue, 13 Jan 2026 14:40:17 Z   \n",
       "\n",
       "       CONTRACT_TERM_CONDITION_NAME COMMISSION  \\\n",
       "0                               NaN       15.0   \n",
       "1                               NaN       15.0   \n",
       "2                               NaN       15.0   \n",
       "3                               NaN       15.0   \n",
       "4                               NaN       15.0   \n",
       "...                             ...        ...   \n",
       "382462                          NaN       15.0   \n",
       "382463                          NaN       15.0   \n",
       "382464                          NaN       15.0   \n",
       "382465                          NaN       15.0   \n",
       "382466                          NaN       15.0   \n",
       "\n",
       "                                        parsed_conditions  \\\n",
       "0       {'countries': [], 'stores': [], 'transaction_t...   \n",
       "1       {'countries': [], 'stores': [], 'transaction_t...   \n",
       "2       {'countries': [], 'stores': [], 'transaction_t...   \n",
       "3       {'countries': [], 'stores': [], 'transaction_t...   \n",
       "4       {'countries': [], 'stores': [], 'transaction_t...   \n",
       "...                                                   ...   \n",
       "382462  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "382463  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "382464  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "382465  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "382466  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "\n",
       "                                        transaction_types countries  \n",
       "0       [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "1       [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "2       [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "3       [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "4       [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "...                                                   ...       ...  \n",
       "382462  [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "382463  [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "382464  [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "382465  [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "382466  [1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...        []  \n",
       "\n",
       "[330876 rows x 57 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>CREATED_BY</th>\n",
       "      <th>LAST_MODIFIED</th>\n",
       "      <th>CONTRACT_NAME</th>\n",
       "      <th>CONTRACT_TYPE</th>\n",
       "      <th>TERM_END</th>\n",
       "      <th>CREATED_AT</th>\n",
       "      <th>_FIVETRAN_DELETED</th>\n",
       "      <th>...</th>\n",
       "      <th>TERM_RATE</th>\n",
       "      <th>DELETED_BY.1</th>\n",
       "      <th>CREATED_AT.2</th>\n",
       "      <th>_FIVETRAN_DELETED.2</th>\n",
       "      <th>_FIVETRAN_SYNCED.2</th>\n",
       "      <th>CONTRACT_TERM_CONDITION_NAME</th>\n",
       "      <th>COMMISSION</th>\n",
       "      <th>parsed_conditions</th>\n",
       "      <th>transaction_types</th>\n",
       "      <th>countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>511964</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 03:24:50.000000000</td>\n",
       "      <td>AGR591703 - Shaun Muca t/a Travi$ Okonma - AWA...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>507383</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 02:23:12.000000000</td>\n",
       "      <td>AGR281643 - Funky Street - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>507259</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2013-03-22</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 20:37:36.000000000</td>\n",
       "      <td>AGR266998 - The Harold Fergus Duo - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>507711</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2014-06-02</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 19:26:18.000000000</td>\n",
       "      <td>AGR319539 - Europium Records - AWAL Distributi...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>514506</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 01:57:20.000000000</td>\n",
       "      <td>AGR686313 - Kira Lonpose t/a Kira - AWAL Distr...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382462</th>\n",
       "      <td>505827</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-03-13</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 06:38:55.000000000</td>\n",
       "      <td>AGR235729 - Tali - AWAL Distribution Agreement</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382463</th>\n",
       "      <td>524128</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-11-26</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:58:16.000000000</td>\n",
       "      <td>AGR556972 - Legacy Digitals - AWAL Distributio...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382464</th>\n",
       "      <td>503663</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 13:38:13.000000000</td>\n",
       "      <td>AGR523655 - Daniel Rosenfeld t/a C418 - AWAL D...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382465</th>\n",
       "      <td>521732</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2012-06-07</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 14:35:50.000000000</td>\n",
       "      <td>AGR245830 - Chantal Lappin &amp; Lynsey Stewart - ...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382466</th>\n",
       "      <td>504379</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>sax_ingestion</td>\n",
       "      <td>2025-07-08 08:16:08.000000000</td>\n",
       "      <td>AGR516550 - Thomas, Matt - AWAL Distribution A...</td>\n",
       "      <td>distribution</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-08 19:42:39.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-16 08:53:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:17 Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[1, 4, 5, 6, 7, 8, 9, 10, 12, 13, 15, 17, 19, ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>330876 rows × 57 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 11
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:36.816181Z",
     "start_time": "2026-02-10T21:42:36.788614Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# merge df_in_corrected with df_all_contract_term_conditions on 'CONTRACT_ID'\n",
    "df_merged_conditions = df_in_corrected.merge(df_all_contract_term_conditions, left_on='Contract ID',\n",
    "                                             right_on='CONTRACT_ID', how='left')\n",
    "\n",
    "df_merged_conditions"
   ],
   "id": "5281a45d5789cb16",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       Account ID  Contract ID  Country Count  \\\n",
       "0            7507           41            242   \n",
       "1            7507           41            242   \n",
       "2            7507           41            242   \n",
       "3            7507           41            242   \n",
       "4            7465          143            242   \n",
       "...           ...          ...            ...   \n",
       "10041      780558       279608            242   \n",
       "10042      780558       279608            242   \n",
       "10043      780558       279608            242   \n",
       "10044      780558       279608            242   \n",
       "10045      780558       279608            242   \n",
       "\n",
       "                                             Country IDs  is_active  \\\n",
       "0      1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "1      1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "2      1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "3      1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "4      1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...       True   \n",
       "...                                                  ...        ...   \n",
       "10041  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "10042  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "10043  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "10044  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "10045  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "\n",
       "       active_distribution_contract_id  manual_review_needed  CONTRACT_ID  \\\n",
       "0                                 41.0                 False         41.0   \n",
       "1                                 41.0                 False         41.0   \n",
       "2                                 41.0                 False         41.0   \n",
       "3                                 41.0                 False         41.0   \n",
       "4                                143.0                 False        143.0   \n",
       "...                                ...                   ...          ...   \n",
       "10041                         279608.0                 False     279608.0   \n",
       "10042                         279608.0                 False     279608.0   \n",
       "10043                         279608.0                 False     279608.0   \n",
       "10044                         279608.0                 False     279608.0   \n",
       "10045                         279608.0                 False     279608.0   \n",
       "\n",
       "      LAST_MODIFIED_BY TERM_START  ... TERM_RATE DELETED_BY.1  \\\n",
       "0      default_user_id        NaN  ...     100.0          NaN   \n",
       "1      default_user_id        NaN  ...     100.0          NaN   \n",
       "2      default_user_id        NaN  ...      70.0          NaN   \n",
       "3      default_user_id        NaN  ...      70.0          NaN   \n",
       "4      default_user_id        NaN  ...      80.0          NaN   \n",
       "...                ...        ...  ...       ...          ...   \n",
       "10041  default_user_id        NaN  ...      50.0          NaN   \n",
       "10042  default_user_id        NaN  ...     100.0          NaN   \n",
       "10043  default_user_id        NaN  ...     100.0          NaN   \n",
       "10044  default_user_id        NaN  ...      70.0          NaN   \n",
       "10045  default_user_id        NaN  ...      80.0          NaN   \n",
       "\n",
       "                        CREATED_AT.2 _FIVETRAN_DELETED.2  \\\n",
       "0      2025-06-30 08:31:52.000000000               False   \n",
       "1      2025-06-30 08:31:52.000000000               False   \n",
       "2      2025-06-30 08:31:52.000000000               False   \n",
       "3      2025-06-30 08:31:52.000000000               False   \n",
       "4      2025-06-30 08:31:50.000000000               False   \n",
       "...                              ...                 ...   \n",
       "10041  2025-06-30 09:56:30.000000000               False   \n",
       "10042  2025-06-30 09:56:30.000000000               False   \n",
       "10043  2025-06-30 09:56:30.000000000               False   \n",
       "10044  2025-06-30 09:56:30.000000000               False   \n",
       "10045  2025-06-30 09:56:30.000000000               False   \n",
       "\n",
       "                _FIVETRAN_SYNCED.2       CONTRACT_TERM_CONDITION_NAME  \\\n",
       "0      Tue, 13 Jan 2026 14:40:37 Z  FS/FP transaction type conditions   \n",
       "1      Tue, 13 Jan 2026 14:40:37 Z        transaction type conditions   \n",
       "2      Tue, 13 Jan 2026 14:40:37 Z                   store conditions   \n",
       "3      Tue, 13 Jan 2026 14:40:37 Z                         base label   \n",
       "4      Tue, 13 Jan 2026 14:40:37 Z                   store conditions   \n",
       "...                            ...                                ...   \n",
       "10041  Tue, 13 Jan 2026 14:40:39 Z        transaction type conditions   \n",
       "10042  Tue, 13 Jan 2026 14:40:39 Z        transaction type conditions   \n",
       "10043  Tue, 13 Jan 2026 14:40:39 Z  FS/FP transaction type conditions   \n",
       "10044  Tue, 13 Jan 2026 14:40:39 Z                   store conditions   \n",
       "10045  Tue, 13 Jan 2026 14:40:39 Z                         base label   \n",
       "\n",
       "      COMMISSION                                  parsed_conditions  \\\n",
       "0            0.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "1            0.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "2           30.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "3           30.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "4           20.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "...          ...                                                ...   \n",
       "10041       50.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10042        0.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10043        0.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10044       30.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10045       20.0  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "\n",
       "                                       transaction_types  countries  \n",
       "0                                              [103, 59]         []  \n",
       "1                                             [105, 104]         []  \n",
       "2      [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "3      [37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...         []  \n",
       "4      [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "...                                                  ...        ...  \n",
       "10041                                           [66, 67]         []  \n",
       "10042                                         [105, 104]         []  \n",
       "10043                                          [103, 59]         []  \n",
       "10044  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "10045  [37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...         []  \n",
       "\n",
       "[10046 rows x 64 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "      <th>is_active</th>\n",
       "      <th>active_distribution_contract_id</th>\n",
       "      <th>manual_review_needed</th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>...</th>\n",
       "      <th>TERM_RATE</th>\n",
       "      <th>DELETED_BY.1</th>\n",
       "      <th>CREATED_AT.2</th>\n",
       "      <th>_FIVETRAN_DELETED.2</th>\n",
       "      <th>_FIVETRAN_SYNCED.2</th>\n",
       "      <th>CONTRACT_TERM_CONDITION_NAME</th>\n",
       "      <th>COMMISSION</th>\n",
       "      <th>parsed_conditions</th>\n",
       "      <th>transaction_types</th>\n",
       "      <th>countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "      <td>41.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:52.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>FS/FP transaction type conditions</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[103, 59]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "      <td>41.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:52.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>transaction type conditions</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[105, 104]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "      <td>41.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:52.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "      <td>41.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:52.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>base label</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7465</td>\n",
       "      <td>143</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...</td>\n",
       "      <td>True</td>\n",
       "      <td>143.0</td>\n",
       "      <td>False</td>\n",
       "      <td>143.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:50.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>20.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10041</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>transaction type conditions</td>\n",
       "      <td>50.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[66, 67]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10042</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>transaction type conditions</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[105, 104]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10043</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>FS/FP transaction type conditions</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[103, 59]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10044</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10045</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>base label</td>\n",
       "      <td>20.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10046 rows × 64 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 12
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:38.010823Z",
     "start_time": "2026-02-10T21:42:38.003823Z"
    }
   },
   "cell_type": "code",
   "source": "df_merged_conditions[['CONTRACT_ID', 'transaction_types']]",
   "id": "b3699a9e56a9a458",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       CONTRACT_ID                                  transaction_types\n",
       "0             41.0                                          [103, 59]\n",
       "1             41.0                                         [105, 104]\n",
       "2             41.0  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...\n",
       "3             41.0  [37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...\n",
       "4            143.0  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...\n",
       "...            ...                                                ...\n",
       "10041     279608.0                                           [66, 67]\n",
       "10042     279608.0                                         [105, 104]\n",
       "10043     279608.0                                          [103, 59]\n",
       "10044     279608.0  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...\n",
       "10045     279608.0  [37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...\n",
       "\n",
       "[10046 rows x 2 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>transaction_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41.0</td>\n",
       "      <td>[103, 59]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41.0</td>\n",
       "      <td>[105, 104]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41.0</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41.0</td>\n",
       "      <td>[37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>143.0</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10041</th>\n",
       "      <td>279608.0</td>\n",
       "      <td>[66, 67]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10042</th>\n",
       "      <td>279608.0</td>\n",
       "      <td>[105, 104]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10043</th>\n",
       "      <td>279608.0</td>\n",
       "      <td>[103, 59]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10044</th>\n",
       "      <td>279608.0</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10045</th>\n",
       "      <td>279608.0</td>\n",
       "      <td>[37, 38, 46, 53, 10, 9, 120, 33, 35, 43, 28, 4...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10046 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 13
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:38.094257Z",
     "start_time": "2026-02-10T21:42:38.055917Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# filter df_merged_conditions for reference_transaction_type_id in REFERENCE_TRANSACTION_TYPE_IDS\n",
    "REFERENCE_TRANSACTION_TYPE_IDS = [3, 58, 71, 72, 73, 74, 75, 76, 77, 78, 79, 81, 82, 83, 84, 85, 86, 87, 88]\n",
    "\n",
    "df_filtered_contract_conditions = df_merged_conditions[\n",
    "    df_merged_conditions['transaction_types'].apply(\n",
    "        lambda x: isinstance(x, list) and any(int(item) in REFERENCE_TRANSACTION_TYPE_IDS for item in x)\n",
    "    )\n",
    "]\n",
    "\n",
    "df_filtered_contract_conditions"
   ],
   "id": "64582f69fefb4a27",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       Account ID  Contract ID  Country Count  \\\n",
       "2            7507           41            242   \n",
       "4            7465          143            242   \n",
       "11          13988         4557            242   \n",
       "14          14237         4804            242   \n",
       "21          14680         5084            244   \n",
       "...           ...          ...            ...   \n",
       "10017       70087       278614            240   \n",
       "10024       70055       278615            242   \n",
       "10029       70116       278634            242   \n",
       "10035       11297       278684            242   \n",
       "10044      780558       279608            242   \n",
       "\n",
       "                                             Country IDs  is_active  \\\n",
       "2      1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "4      1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...       True   \n",
       "11     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "14     1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "21     1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "...                                                  ...        ...   \n",
       "10017  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...      False   \n",
       "10024  1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...       True   \n",
       "10029  1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...       True   \n",
       "10035  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "10044  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...       True   \n",
       "\n",
       "       active_distribution_contract_id  manual_review_needed  CONTRACT_ID  \\\n",
       "2                                 41.0                 False         41.0   \n",
       "4                                143.0                 False        143.0   \n",
       "11                              4557.0                 False       4557.0   \n",
       "14                              4804.0                 False       4804.0   \n",
       "21                              5084.0                 False       5084.0   \n",
       "...                                ...                   ...          ...   \n",
       "10017                              NaN                 False     278614.0   \n",
       "10024                         278615.0                 False     278615.0   \n",
       "10029                         278634.0                 False     278634.0   \n",
       "10035                         278684.0                 False     278684.0   \n",
       "10044                         279608.0                 False     279608.0   \n",
       "\n",
       "      LAST_MODIFIED_BY TERM_START  ... TERM_RATE DELETED_BY.1  \\\n",
       "2      default_user_id        NaN  ...      70.0          NaN   \n",
       "4      default_user_id        NaN  ...      80.0          NaN   \n",
       "11     default_user_id        NaN  ...      70.0          NaN   \n",
       "14     default_user_id        NaN  ...      70.0          NaN   \n",
       "21     default_user_id        NaN  ...       0.0          NaN   \n",
       "...                ...        ...  ...       ...          ...   \n",
       "10017  default_user_id        NaN  ...      75.0          NaN   \n",
       "10024  default_user_id        NaN  ...      75.0          NaN   \n",
       "10029  default_user_id        NaN  ...      75.0          NaN   \n",
       "10035  default_user_id        NaN  ...      75.0          NaN   \n",
       "10044  default_user_id        NaN  ...      70.0          NaN   \n",
       "\n",
       "                        CREATED_AT.2 _FIVETRAN_DELETED.2  \\\n",
       "2      2025-06-30 08:31:52.000000000               False   \n",
       "4      2025-06-30 08:31:50.000000000               False   \n",
       "11     2025-06-30 08:47:47.000000000               False   \n",
       "14     2025-06-30 08:49:05.000000000               False   \n",
       "21     2025-06-30 08:46:26.000000000               False   \n",
       "...                              ...                 ...   \n",
       "10017  2025-06-30 09:53:00.000000000               False   \n",
       "10024  2025-06-30 09:52:51.000000000               False   \n",
       "10029  2025-06-30 09:53:10.000000000               False   \n",
       "10035  2025-06-30 09:47:47.000000000               False   \n",
       "10044  2025-06-30 09:56:30.000000000               False   \n",
       "\n",
       "                _FIVETRAN_SYNCED.2 CONTRACT_TERM_CONDITION_NAME COMMISSION  \\\n",
       "2      Tue, 13 Jan 2026 14:40:37 Z             store conditions       30.0   \n",
       "4      Tue, 13 Jan 2026 14:40:37 Z             store conditions       20.0   \n",
       "11     Tue, 13 Jan 2026 14:40:37 Z             store conditions       30.0   \n",
       "14     Tue, 13 Jan 2026 14:40:38 Z             store conditions       30.0   \n",
       "21     Tue, 13 Jan 2026 14:40:37 Z             store conditions      100.0   \n",
       "...                            ...                          ...        ...   \n",
       "10017  Tue, 13 Jan 2026 14:40:39 Z             store conditions       25.0   \n",
       "10024  Tue, 13 Jan 2026 14:40:39 Z             store conditions       25.0   \n",
       "10029  Tue, 13 Jan 2026 14:40:39 Z             store conditions       25.0   \n",
       "10035  Tue, 13 Jan 2026 14:40:39 Z             store conditions       25.0   \n",
       "10044  Tue, 13 Jan 2026 14:40:39 Z             store conditions       30.0   \n",
       "\n",
       "                                       parsed_conditions  \\\n",
       "2      {'countries': [], 'stores': [], 'transaction_t...   \n",
       "4      {'countries': [], 'stores': [], 'transaction_t...   \n",
       "11     {'countries': [], 'stores': [], 'transaction_t...   \n",
       "14     {'countries': [], 'stores': [], 'transaction_t...   \n",
       "21     {'countries': [], 'stores': [], 'transaction_t...   \n",
       "...                                                  ...   \n",
       "10017  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10024  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10029  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10035  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "10044  {'countries': [], 'stores': [], 'transaction_t...   \n",
       "\n",
       "                                       transaction_types  countries  \n",
       "2      [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "4      [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "11     [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "14     [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "21     [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "...                                                  ...        ...  \n",
       "10017  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "10024  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "10029  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "10035  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "10044  [72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...         []  \n",
       "\n",
       "[1906 rows x 64 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account ID</th>\n",
       "      <th>Contract ID</th>\n",
       "      <th>Country Count</th>\n",
       "      <th>Country IDs</th>\n",
       "      <th>is_active</th>\n",
       "      <th>active_distribution_contract_id</th>\n",
       "      <th>manual_review_needed</th>\n",
       "      <th>CONTRACT_ID</th>\n",
       "      <th>LAST_MODIFIED_BY</th>\n",
       "      <th>TERM_START</th>\n",
       "      <th>...</th>\n",
       "      <th>TERM_RATE</th>\n",
       "      <th>DELETED_BY.1</th>\n",
       "      <th>CREATED_AT.2</th>\n",
       "      <th>_FIVETRAN_DELETED.2</th>\n",
       "      <th>_FIVETRAN_SYNCED.2</th>\n",
       "      <th>CONTRACT_TERM_CONDITION_NAME</th>\n",
       "      <th>COMMISSION</th>\n",
       "      <th>parsed_conditions</th>\n",
       "      <th>transaction_types</th>\n",
       "      <th>countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7507</td>\n",
       "      <td>41</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>41.0</td>\n",
       "      <td>False</td>\n",
       "      <td>41.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:52.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7465</td>\n",
       "      <td>143</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,2...</td>\n",
       "      <td>True</td>\n",
       "      <td>143.0</td>\n",
       "      <td>False</td>\n",
       "      <td>143.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:31:50.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>20.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>13988</td>\n",
       "      <td>4557</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>4557.0</td>\n",
       "      <td>False</td>\n",
       "      <td>4557.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:47:47.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14237</td>\n",
       "      <td>4804</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>4804.0</td>\n",
       "      <td>False</td>\n",
       "      <td>4804.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:49:05.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:38 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>14680</td>\n",
       "      <td>5084</td>\n",
       "      <td>244</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>5084.0</td>\n",
       "      <td>False</td>\n",
       "      <td>5084.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 08:46:26.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:37 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>100.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10017</th>\n",
       "      <td>70087</td>\n",
       "      <td>278614</td>\n",
       "      <td>240</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>278614.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:53:00.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>25.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10024</th>\n",
       "      <td>70055</td>\n",
       "      <td>278615</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,2...</td>\n",
       "      <td>True</td>\n",
       "      <td>278615.0</td>\n",
       "      <td>False</td>\n",
       "      <td>278615.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:52:51.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>25.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>70116</td>\n",
       "      <td>278634</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,...</td>\n",
       "      <td>True</td>\n",
       "      <td>278634.0</td>\n",
       "      <td>False</td>\n",
       "      <td>278634.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:53:10.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>25.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10035</th>\n",
       "      <td>11297</td>\n",
       "      <td>278684</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>278684.0</td>\n",
       "      <td>False</td>\n",
       "      <td>278684.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:47:47.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>25.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10044</th>\n",
       "      <td>780558</td>\n",
       "      <td>279608</td>\n",
       "      <td>242</td>\n",
       "      <td>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1...</td>\n",
       "      <td>True</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>False</td>\n",
       "      <td>279608.0</td>\n",
       "      <td>default_user_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-30 09:56:30.000000000</td>\n",
       "      <td>False</td>\n",
       "      <td>Tue, 13 Jan 2026 14:40:39 Z</td>\n",
       "      <td>store conditions</td>\n",
       "      <td>30.0</td>\n",
       "      <td>{'countries': [], 'stores': [], 'transaction_t...</td>\n",
       "      <td>[72, 73, 58, 74, 76, 77, 88, 78, 81, 79, 71, 3...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1906 rows × 64 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:38.421807Z",
     "start_time": "2026-02-10T21:42:38.392723Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Step 5: Single rollback insert for all contract-term IDs\n",
    "\n",
    "# Collect all relevant contract-term IDs\n",
    "contract_term_ids = df_filtered_contract_conditions['CONTRACT_TERM_CONDITION_ID'].dropna().astype(int).unique()\n",
    "ids_str = ','.join(str(i) for i in contract_term_ids)\n",
    "\n",
    "# Add table creation statement for rollback table\n",
    "create_rollback_table = (\n",
    "    \"CREATE TABLE IF NOT EXISTS ROLLBACK_ACC_9796 \"\n",
    "    \"(CONTRACT_TERM_CONDITION_ID NUMBER, OLD_CONDITIONS VARCHAR);\"\n",
    ")\n",
    "db_pr = [\n",
    "    '--liquibase formatted sql',\n",
    "    '',\n",
    "    '--changeset theka:1',\n",
    "    '',\n",
    "    create_rollback_table\n",
    "]\n",
    "\n",
    "# rollabck table\n",
    "single_rollback_insert = (\n",
    "    f\"INSERT INTO ROLLBACK_ACC_9796 (CONTRACT_TERM_CONDITION_ID, OLD_CONDITIONS)\\n\"\n",
    "    f\"SELECT CONTRACT_TERM_CONDITION_ID, CONDITIONS FROM CONTRACT_TERM_CONDITION\\n\"\n",
    "    f\"WHERE CONTRACT_TERM_CONDITION_ID IN ({ids_str});\"\n",
    ")\n",
    "db_pr.append(single_rollback_insert)\n",
    "\n",
    "# generate update statements\n",
    "for index, row in df_filtered_contract_conditions.iterrows():\n",
    "    contract_term_condition_id = int(row['CONTRACT_TERM_CONDITION_ID'])\n",
    "    country_ids = row['Country IDs']\n",
    "    update_statement = (\n",
    "        f\"-- ContractID={row['Contract ID']}\\n\"\n",
    "        f\"UPDATE CONTRACT_TERM_CONDITION SET CONDITIONS = JSON_SET(CONDITIONS, '$.countries', '[{country_ids}]') \"\n",
    "        f\"WHERE CONTRACT_TERM_CONDITION_ID = {contract_term_condition_id};\"\n",
    "    )\n",
    "    db_pr.append(update_statement)\n",
    "\n",
    "liquibase_rollback = (\n",
    "    f\"--rollback UPDATE CONTRACT_TERM_CONDITION\\n\"\n",
    "    f\"--rollback SET CONDITIONS = r.OLD_CONDITIONS\\n\"\n",
    "    f\"--rollback FROM ROLLBACK_ACC_9796 r\\n\"\n",
    "    f\"--rollback WHERE CONTRACT_TERM_CONDITION.CONTRACT_TERM_CONDITION_ID = r.CONTRACT_TERM_CONDITION_ID;\"\n",
    ")\n",
    "db_pr.append(liquibase_rollback)\n",
    "\n",
    "with open('generated/ACC_9796_Backfill_incorrectly_migrated_performance_rights_territories.sql', 'w') as f:\n",
    "    for statement in db_pr:\n",
    "        f.write(statement + '\\n')"
   ],
   "id": "ccac0cbe17ed5df4",
   "outputs": [],
   "execution_count": 15
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### QA Process\n",
    "This is where we will generate queries for the BEFORE and AFTER states of the data to ensure that the correct records are being updated and that the updates are correct. We will also generate a query to check for any unexpected changes to the data."
   ],
   "id": "d5e5ad9bf3e3d0d7"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:38.453026Z",
     "start_time": "2026-02-10T21:42:38.442364Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Smoke testing - this query will help with manually spotting things\n",
    "contract_term_ids = df_filtered_contract_conditions['CONTRACT_TERM_CONDITION_ID'].dropna().astype(int).unique()\n",
    "ids_str = ','.join(str(i) for i in contract_term_ids)\n",
    "\n",
    "inspection_query = (\n",
    "    f\"SELECT\\n\"\n",
    "    \"contract_term_condition_id,\\n\"\n",
    "    \"CONDITIONS -> '$.countries' AS countries,\\n\",\n",
    "    \"CONDITIONS -> '$.transaction_types' AS transaction_types\\n\"\n",
    "    f\"FROM royalty_accounting.contract_term_condition\\n\"\n",
    "    f\"WHERE CONTRACT_TERM_CONDITION_ID IN ({ids_str});\"\n",
    ")\n",
    "\n",
    "with open('generated/ACC_9796_inspection_query.sql', 'w') as f:\n",
    "    f.write(\"\".join(inspection_query))"
   ],
   "id": "b015d60316c63b8c",
   "outputs": [],
   "execution_count": 16
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-02-10T21:42:38.463114Z",
     "start_time": "2026-02-10T21:42:38.453385Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Generate checksum for affected records before and after the DB PR update\n",
    "checksum_query = (\n",
    "    f\"SELECT MD5(GROUP_CONCAT(CONDITIONS ORDER BY CONTRACT_TERM_CONDITION_ID)) AS before_checksum\\n\"\n",
    "    f\"FROM royalty_accounting.contract_term_condition\\n\"\n",
    "    f\"WHERE CONTRACT_TERM_CONDITION_ID IN ({ids_str});\"\n",
    ")\n",
    "\n",
    "with open('generated/ACC_9796_checksum_query.sql', 'w') as f:\n",
    "    f.write(\"\".join(checksum_query))"
   ],
   "id": "b3c0719aa4a0b704",
   "outputs": [],
   "execution_count": 17
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "c020dc09b6e7a7a6"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}