fulldecent/mts_course_renewal_marketing

Modern Training Solutions renewal email dataset

Summary

This is a tabular derived from Pacific Medical Training course renewal email activity. The published release is designed to support analysis of certification renewal behavior, email timing, and downstream purchase activity.

The published data is organized into three CSV files:

• —email_blasts.csv: 619,186 rows
• —expirations.csv: 127,928 rows
• —orders.csv: 38,524 rows

The release uses deterministic sampling and blinded indexes instead of raw email addresses and raw course names.

What this data represents

The source project tracks renewal-oriented email blasts for certification-related courses related to life support training (similar to cardiopulmonary resuscitation), along with historical certification expiration records and order activity.

At a high level, the published data supports questions such as:

• —how email timing relates to purchase behavior
• —how large and non-large blasts differ
• —how expiration timing relates to renewal activity
• —which email are unlikely to produce a sale and can be suppressed

Published files

email_blasts.csv

Columns:

• —sent_at: date the batch was sent
• —is_large_blast: 1 if the blast met a secret size threshold, else 0
• —email_blinded_index: 0-based blinded email identifier
• —blinded_course_<index>_exp: expiration date fields keyed by blinded course index

Important detail:

• —the blinded course columns are ordered by blinded course index, not by the original source column order
• —this is intentional and helps avoid leaking the original course-name ordering from the source system

expirations.csv

Columns:

• —email_blinded_index: 0-based blinded email identifier
• —expired_date: expiration date
• —course_blinded_index: 0-based blinded course identifier
• —our_course: source flag indicating whether the record was associated with the organization’s own course catalog

If a student purchased our course and then completed that course, a expirations.csv record will exist for that student/course. The expired_date will be a certain number of months after the date they completed the course, and this depends on the course. Such records will have our_course = 1.

Students may also self-report expirations data. They may indicate our_course = 0 indicating a competitor's brand course, or they may indicate our_course = 1 to say this course was purchased with us.

orders.csv

Columns:

• —created_at: order timestamp
• —email_blinded_index: 0-based blinded email identifier
• —price: order price

Blinding and privacy approach

This release does not include raw email addresses or raw course names.

Instead, the pipeline creates secret sampled lookup files and uses row position as the public identifier:

• —sampled emails are chosen deterministically from the full email universe using a secret salt
• —sampled courses are ordered deterministically using a different secret salt
• —public data references only the 0-based row indexes from those sampled files
• —public data does not leak row ordering from the original source data

The project also keeps the threshold for is_large_blast secret. This avoids exposing an exact cutoff that could indirectly reveal customer-list size.

This approach is meant to reduce disclosure risk, not to provide a formal privacy guarantee such as differential privacy.

Determinism and reproducibility

The publishing pipeline is deterministic once the secret configuration is fixed.

• —sampled emails are selected by salted hash order and outputs exactly 100,000 unique emails
• —sampled courses are ordered by salted hash order
• —published rows are sorted deterministically before writing

This means the same protected inputs and secrets produce the same blinded outputs.

Intended uses

Appropriate uses include:

• —exploratory analysis of renewal-related outreach patterns
• —feature engineering experiments on blinded relational data
• —model development for aggregate behavioral patterns
• —evaluation of timing, seasonality, or campaign segmentation strategies

Out-of-scope uses

This dataset should not be used for:

• —making decisions about an identifiable person
• —clinical decision-making or medical advice
• —eligibility, hiring, lending, insurance, or legal decisions
• —attempting to reverse engineer identities, mailing-list size, or private operational thresholds

Data creation process

1. 1.Download email blast receipts from Google Drive to rawdata/emailblasts. This includes .serialmailer and .csv files that we need as well as some template.csv files we don't.

1. 1.Query this on production database and save it to raw_data/orders.csv

   -- New system Shopify orders
   SELECT created_at
        , email
        , total_price price
     FROM data_lake.shopify_orders o
    WHERE o.created_at >= '2020-01-01'
   
    UNION ALL
   
    -- Old-system enrollments (historical renewal dates — critical for actual expiration reconstruction)
   SELECT e.purchase_order_at created_at
        , p.email
        , e.purchase_order_price price
     FROM pmt_slim.old_system_enrollments e
     JOIN cms.people p ON p.id = e.enrollee_cms_person_id
    WHERE e.purchase_order_at >= '2020-01-01'

1. 1.Run the CMSExpirationCleanup script.

1. 1.Query this on production database and save it to raw_data/expirations.csv

   SELECT p.email
        , e.expired_date
        , e.certification
        , CASE WHEN e.company_name = "Pacific Medical Training" THEN 1 ELSE 0 END our_course
     FROM cms.expirations e
     JOIN cms.people p
       ON p.id = e.person_id
    WHERE expired_date > "2018-01-01"

1. 1.Setup Python

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install --upgrade pip
   python -m pip install pyarrow

1. 1.Create .env with a random SALT only if it does not already exist:

   [ -f .env ] || printf "SALT=%s\n" "$(openssl rand -hex 32)" > .env

1. 1.Then open .env and manually type this line (do not auto-generate this value):

   LARGE_BLAST_SIZE=#####

LARGE_BLAST_SIZE is the threshold used to classify a blast as large (recipient count at or above this value). Keep this value secret because exposing it could indirectly reveal customer-count information.

1. 1.Run scripts

   python scripts/consolidate_email_blasts.py
   python scripts/sample_email_addresses.py
   python scripts/sample_courses.py
   python scripts/create_published_data.py

Known limitations

• —the published data is a sampled subset, not the full customer universe
• —raw email identities and raw course labels are intentionally absent
• —the is_large_blast threshold is intentionally hidden, which limits some exact operational interpretation
• —because the dataset is sampled, absolute totals should not be interpreted as full-business totals
• —this is a business operations dataset, so it reflects historical process and instrumentation choices rather than a controlled experimental design

Biases and representational concerns

This dataset reflects the behavior of people who were present in the organization’s operational systems and email programs. It may underrepresent or omit people who were not emailed, had missing data, or fell outside the chosen sampling process. Campaign design, course mix, and data capture methods may also vary over time.

Maintenance

This card describes the current published pipeline and output layout. If the release process changes, the row counts, field list, or blinding strategy may also change.

Citation

If you publish work based on this dataset, cite the dataset repository and describe the release as a blinded sample of Modern Training Solutions renewal email, expiration, and order activity.