Importing Data from Email Attachments

Switchboard supports automated ingestion of emailed reports when the following criteria are met:

Data must be in CSV format. Zip files are also supported, the content of which should be .csv.
Data must be included as attachments (not as a download link).
Data should be no larger than 25 Megabytes per attachment.

In order to activate an email connection, contact your Switchboard Customer Success representative and request a unique email address for data delivery.

Backfills

Backfills from email sources are based on the delivery date of the email and not the date of the content. Switchboard does not support other potential sources for backfill dates.

Backfills for emails will only work up for to 365 days. Any data submitted to the recipient email address before that will not be available.

Parameters

recipient string: required; This address will be registered when the SBS is published. The suggested naming pattern is: <account>_<unique id>_datasource
subject_pattern string: optional; The script will only apply to emails with subject lines that match the given pattern. Use standard regex syntax to match based on the subject when you are using the same inbox for multiple reports that can be differentiated based on the subject line.
subject_datetime_pattern string: optional; The pattern specified here will be used to determine the date for which the reported data is relevant. If none is specified, the current date will be assumed. Alternative patterns such as MM-DD-YYYYY may be used.

Sample SBS

/// New email ingest downloader
// Once active SB will set up export of raw data to S3 for cold storage

download bundle email_report_raw {
    type: "emails";
    
    // Match incoming emails
    recipient: "<Unique_Address>@ingest.switchboard-software.com"; // required. registered when the SBS is published, suggested: <account>_<unique id>_datasource 
    //subject_pattern: "*"; // optional

    // Extract the report date from the subject
    //subject_datetime_pattern: "*DD-MM-YYYY"; // optional

    // The usual scheduling parameters, which are optional, control how frequently to check for a new email.
    period_hours: 1;

    // Declare a table for each attachment in the email.
    table report from {
        // Match the attachment name
        pattern: "*"; // required
        //{schedule_switchboard_input_last_10_days_2022-03-29T00:00_2022-04-07T00:00.csv}

        // Extract the report date from the attachment name
        datetime_pattern: "*YYYY-MM-DD_*"; // optional

        // Similar to S3/GCS/SFTP downloaders.
        has_header_row: true; // optional
        //delimiter: ","; // optional, default ","
        //format: "csv"; // optional 
        //compression: "gzip"; // optional
    }
    using {
        "Date": datetime;
        "CampaignID": string;
        "Impressions": string;
        "Revenue": string;
    }; //Using section should include all anticipated fields and their respective datatypes contained within the attachment, these will depend heavily on the specific contents of the attachment. If the fields are not known at the time of initial deployment 'raw: json;' or 'raw: string;' can be utilized```
};



table email_report is select
    <<raw.$c for c in raw,
    processing_datetime() as switchboard_processing_time
from email_report_raw.report as raw unique;


export email_report to {
    destination: "s3://<Customer_Bucket>/email-ingest/datasource_name/email_report_YYYY-MM-DD.csv.gz";
    //test_destination: "s3://<Customer_Bucket>/email-ingest/datasource_name/email_report_YYYY-MM-DD.csv.gz";
    headers: true;
    type: "s3_ng";
    format: "csv";
    compression: "gzip";
    key: "Amazon_email_ingest_key";
    partition_count: 1;
    primary_source_name: "email_report_raw.report";
};