Importing EmailOctopus activity into ActionKit

Suppose you sent an EmailOctopus mailing to some contacts and now you want to get their activity back into ActionKit. Here are some fairly repeatable steps you can use, with MotherDuck as a convenient layer in the middle so that you don’t need to touch any spreadsheets, terminals, or ad-hoc scripting code:

1. In ActionKit, create a new import page with the tagging and other configuration that you want, including (probably) “Don’t Change Subscriptions”, “don’t count as member actions”, and “language = —” so that you don’t change anything about these people.

2. In EmailOctopus, navigate to the sent campaign’s Campaign Report and export the individual CSVs for “Sent To”, “Opened”, “Clicked”, “Unsubscribed”, “Bounced”, and “Complained”.

3. In MotherDuck, add all six downloaded files into your workspace and then import them into tables. You may want to only import the hard bounces as bounces, and you definitely want to make sure you add a column denoting what mailing campaign this was. You may as well drop all the columns apart from timestamp and email (or ActionKit user ID if you have it in there as a custom field)

4. Then flatten into a single table with columns indicating whether / when a user opened, clicked, hard-bounced, complained, and unsubscribed, in an ActionKit-import-friendly format. Importing the data all as one-row-per-recipient keeps the relationship between the engagement activity clear on the ActionKit side, and minimizes the opportunities for the sorts of user error that inevitably pop up when you’re doing a lot of manual imports in quick succession.

5. After importing you’ll want to then do some other stuff on the ActionKit side to make sure you process the unsubscribes and bounces, potentially mark the openers and clickers as engaged, etc.

6. You may also want (or need) to drop those tables from MotherDuck when you’re done.

Here’s SQL you can use for the imports into MotherDuck with minimal string replacement:

CREATE OR REPLACE TABLE send AS 
  SELECT "Email address" as email, "Time" as created_at, 
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-send.csv';

CREATE OR REPLACE TABLE open AS 
  SELECT "Email address" as email, "Time" as created_at, 
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-open.csv';

CREATE OR REPLACE TABLE bounce AS 
  SELECT "Email address" as email, "Time" as created_at, 
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-bounce.csv' 
  where "Bounce type" = 'hard';

CREATE OR REPLACE TABLE unsubscribe AS 
  SELECT "Email address" as email, "Time" as created_at, 
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-unsubscribe.csv';

CREATE OR REPLACE TABLE click AS 
  SELECT "Email address" as email, "Time" as created_at,
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-click.csv';

CREATE OR REPLACE TABLE complaint AS 
  SELECT "Email address" as email, "Time" as created_at,
  'MY_CAMPAIGN_NAME' as action_emailoctopus_mailing
  FROM 'campaign-report-activity-complaint.csv';

And here’s the SQL you can use to transform the MotherDuck tables back into something you can download and then upload into ActionKit:

  select send.email, 

    action_emailoctopus_mailing,

  send.created_at as action_emailoctopus_mailing_sent_at,

  (select created_at from open where open.email = send.email order by created_at asc limit 1)
    as action_emailoctopus_mailing_opened_at,

  (select created_at from click where click.email = send.email order by created_at asc limit 1)
    as action_emailoctopus_mailing_clicked_at,

  (select created_at from unsubscribe where unsubscribe.email = send.email order by created_at asc limit 1)
    as action_emailoctopus_mailing_unsubscribed_at,

  (select created_at from bounce where bounce.email = send.email order by created_at asc limit 1)
    as action_emailoctopus_mailing_bounced_at,

  (select created_at from bounce where bounce.email = send.email order by created_at asc limit 1)
    as action_emailoctopus_mailing_complained_at

  from send

Of course you can also do these same steps entirely locally with DuckDB! But if you’re able to use MotherDuck, it gives you a nice “drag-and-drop straight from the downloads” workflow, and your queries are conveniently living there in your workspace from last time so you just need to adjust the filenames and campaign name.