Skip to content

In Product Email Campaigns SaaS

Alex Buijs requested to merge 296133-engineering-in-product-email-campaigns-in-saas into master

What does this MR do?

In product email campaigns are sent on 4 different topics (tracks) at various intervals, until the task for a particular track has been completed.

In order to achieve this, a CRON job has been added for a worker that runs daily at 9 am.

The worker sends an email to all users of root groups which are in a state of onboarding specific for a track and interval who are able to perform a track's action and have opted into marketing emails.

=> for each track
  => for each interval
    => for all groups that have not yet completed the track’s action, but have completed all of the previous track's actions, with the latest completed action completed on the interval's number of days ago
      => for all users of the group who can complete the track action, have opted into marketing emails and have not been send the same email today for another group
        => send the in product marketing email specific for the track and interval

Migration output

rails db:migrate:redo 
== 20210114142443 AddIndexesToOnboardingProgresses: reverting =================
-- transaction_open?()
   -> 0.0000s
-- indexes(:onboarding_progresses)
   -> 0.0031s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- remove_index(:onboarding_progresses, {:algorithm=>:concurrently, :name=>"index_onboarding_progresses_for_create_track"})
   -> 0.0031s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- indexes(:onboarding_progresses)
   -> 0.0015s
-- remove_index(:onboarding_progresses, {:algorithm=>:concurrently, :name=>"index_onboarding_progresses_for_verify_track"})
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- indexes(:onboarding_progresses)
   -> 0.0012s
-- remove_index(:onboarding_progresses, {:algorithm=>:concurrently, :name=>"index_onboarding_progresses_for_trial_track"})
   -> 0.0013s
-- transaction_open?()
   -> 0.0000s
-- indexes(:onboarding_progresses)
   -> 0.0011s
-- remove_index(:onboarding_progresses, {:algorithm=>:concurrently, :name=>"index_onboarding_progresses_for_team_track"})
   -> 0.0007s
== 20210114142443 AddIndexesToOnboardingProgresses: reverted (0.0145s) ========

== 20210114142443 AddIndexesToOnboardingProgresses: migrating =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:onboarding_progresses, :created_at, {:where=>"git_write_at IS NULL", :name=>"index_onboarding_progresses_for_create_track", :algorithm=>:concurrently})
   -> 0.0035s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- add_index(:onboarding_progresses, :created_at, {:where=>"git_write_at IS NULL", :name=>"index_onboarding_progresses_for_create_track", :algorithm=>:concurrently})
   -> 0.0079s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:onboarding_progresses, :git_write_at, {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NULL", :name=>"index_onboarding_progresses_for_verify_track", :algorithm=>:concurrently})
   -> 0.0024s
-- add_index(:onboarding_progresses, :git_write_at, {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NULL", :name=>"index_onboarding_progresses_for_verify_track", :algorithm=>:concurrently})
   -> 0.0042s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:onboarding_progresses, "GREATEST(git_write_at, pipeline_created_at)", {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NOT NULL AND trial_started_at IS NULL", :name=>"index_onboarding_progresses_for_trial_track", :algorithm=>:concurrently})
   -> 0.0028s
-- add_index(:onboarding_progresses, "GREATEST(git_write_at, pipeline_created_at)", {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NOT NULL AND trial_started_at IS NULL", :name=>"index_onboarding_progresses_for_trial_track", :algorithm=>:concurrently})
   -> 0.0030s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:onboarding_progresses, "GREATEST(git_write_at, pipeline_created_at, trial_started_at)", {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NOT NULL AND trial_started_at IS NOT NULL AND user_added_at IS NULL", :name=>"index_onboarding_progresses_for_team_track", :algorithm=>:concurrently})
   -> 0.0025s
-- add_index(:onboarding_progresses, "GREATEST(git_write_at, pipeline_created_at, trial_started_at)", {:where=>"git_write_at IS NOT NULL AND pipeline_created_at IS NOT NULL AND trial_started_at IS NOT NULL AND user_added_at IS NULL", :name=>"index_onboarding_progresses_for_team_track", :algorithm=>:concurrently})
   -> 0.0048s
== 20210114142443 AddIndexesToOnboardingProgresses: migrated (0.0342s) ========

Query plans

Query for Create Track

ActiveRecord

Group.joins(:onboarding_progress).merge(
  OnboardingProgress
    .latest_completed_action_in_range([:created], 5.days.ago.beginning_of_day..5.days.ago.end_of_day)
    .incomplete_actions(:git_write)
)

Raw SQL

SELECT
  "namespaces".*
FROM
  "namespaces"
  INNER JOIN "onboarding_progresses" ON "onboarding_progresses"."namespace_id" = "namespaces"."id"
WHERE
  "namespaces"."type" = 'Group'
  AND "onboarding_progresses"."created_at" BETWEEN '2021-01-11 00:00:00' AND '2021-01-11 23:59:59.999999'
  AND "onboarding_progresses"."git_write_at" IS NULL

Query Plan (postgres.ai)

 Nested Loop  (cost=0.71..55.45 rows=3 width=345) (actual time=0.476..2.284 rows=15 loops=1)
   Buffers: shared hit=45 read=21
   I/O Timings: read=1.865
   ->  Index Scan using index_onboarding_progresses_for_create_track on public.onboarding_progresses  (cost=0.29..7.18 rows=14 width=8) (actual time=0.083..0.099 rows=15 loops=1)
         Index Cond: ((onboarding_progresses.created_at >= '2021-01-11 00:00:00+00'::timestamp with time zone) AND (onboarding_progresses.created_at <= '2021-01-11 23:59:59.999999+00'::timestamp with time zone))
         Buffers: shared hit=1 read=2
         I/O Timings: read=0.066
   ->  Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces  (cost=0.43..3.45 rows=1 width=345) (actual time=0.143..0.143 rows=1 loops=15)
         Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = onboarding_progresses.namespace_id))
         Buffers: shared hit=44 read=19
         I/O Timings: read=1.800
Query for Verify Track

ActiveRecord

Group.joins(:onboarding_progress).merge(
  OnboardingProgress
    .latest_completed_action_in_range([:git_write], 5.days.ago.beginning_of_day..5.days.ago.end_of_day)
    .incomplete_actions(:pipeline_created)
)

Raw SQL

SELECT
  "namespaces".*
FROM
  "namespaces"
  INNER JOIN "onboarding_progresses" ON "onboarding_progresses"."namespace_id" = "namespaces"."id"
WHERE
  "namespaces"."type" = 'Group'
  AND "onboarding_progresses"."git_write_at" BETWEEN '2021-01-14 00:00:00' AND '2021-01-14 23:59:59.999999'
  AND "onboarding_progresses"."pipeline_created_at" IS NULL

Query Plan (postgres.ai)

 Nested Loop  (cost=0.71..2220.19 rows=122 width=345) (actual time=0.648..31.108 rows=547 loops=1)
   Buffers: shared hit=2022 read=580 dirtied=10
   I/O Timings: read=24.144
   ->  Index Scan using index_onboarding_progresses_for_verify_track on public.onboarding_progresses  (cost=0.29..108.08 rows=617 width=8) (actual time=0.148..1.185 rows=547 loops=1)
         Index Cond: ((onboarding_progresses.git_write_at >= '2021-01-14 00:00:00+00'::timestamp with time zone) AND (onboarding_progresses.git_write_at <= '2021-01-14 23:59:59.999999+00'::timestamp with time zone))
         Buffers: shared hit=406 read=4 dirtied=3
         I/O Timings: read=0.196
   ->  Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces  (cost=0.43..3.42 rows=1 width=345) (actual time=0.053..0.053 rows=1 loops=547)
         Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = onboarding_progresses.namespace_id))
         Buffers: shared hit=1616 read=576 dirtied=7
         I/O Timings: read=23.948
Query for Trial Track

ActiveRecord

Group.joins(:onboarding_progress).merge(
  OnboardingProgress
    .latest_completed_action_in_range([:git_write, :pipeline_created], 5.days.ago.beginning_of_day..5.days.ago.end_of_day)
    .incomplete_actions(:trial_started)
)

Raw SQL

SELECT
  "namespaces".*
FROM
  "namespaces"
  INNER JOIN "onboarding_progresses" ON "onboarding_progresses"."namespace_id" = "namespaces"."id"
WHERE
  "namespaces"."type" = 'Group'
  AND "onboarding_progresses"."git_write_at" IS NOT NULL
  AND "onboarding_progresses"."pipeline_created_at" IS NOT NULL
  AND GREATEST("onboarding_progresses"."git_write_at", "onboarding_progresses"."pipeline_created_at") BETWEEN '2021-01-14 00:00:00' AND '2021-01-14 23:59:59.999999'
  AND "onboarding_progresses"."trial_started_at" IS NULL

Query Plan (postgres.ai)

 Nested Loop  (cost=0.71..41.52 rows=2 width=345) (actual time=3.150..187.590 rows=139 loops=1)
   Buffers: shared hit=475 read=180 dirtied=2
   I/O Timings: read=181.499
   ->  Index Scan using index_onboarding_progresses_for_trial_track on public.onboarding_progresses  (cost=0.28..13.94 rows=8 width=8) (actual time=0.132..1.020 rows=139 loops=1)
         Index Cond: ((GREATEST(onboarding_progresses.git_write_at, onboarding_progresses.pipeline_created_at) >= '2021-01-14 00:00:00+00'::timestamp with time zone) AND (GREATEST(onboarding_progresses.git_write_at, onboarding_progresses.pipeline_created_at) <= '2021-01-14 23:59:59.999999+00'::timestamp with time zone))
         Buffers: shared hit=93 read=3 dirtied=1
         I/O Timings: read=0.140
   ->  Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces  (cost=0.43..3.45 rows=1 width=345) (actual time=1.335..1.335 rows=1 loops=139)
         Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = onboarding_progresses.namespace_id))
         Buffers: shared hit=382 read=177 dirtied=1
         I/O Timings: read=181.359
Query for Team Track

ActiveRecord

Group.joins(:onboarding_progress).merge(
  OnboardingProgress
    .latest_completed_action_in_range([:git_write, :pipeline_created, :trial_started], 5.days.ago.beginning_of_day..5.days.ago.end_of_day)
    .incomplete_actions(:user_added)
)

Raw SQL

SELECT
  "namespaces".*
FROM
  "namespaces"
  INNER JOIN "onboarding_progresses" ON "onboarding_progresses"."namespace_id" = "namespaces"."id"
WHERE
  "namespaces"."type" = 'Group'
  AND "onboarding_progresses"."git_write_at" IS NOT NULL
  AND "onboarding_progresses"."pipeline_created_at" IS NOT NULL
  AND "onboarding_progresses"."trial_started_at" IS NOT NULL
  AND GREATEST("onboarding_progresses"."git_write_at", "onboarding_progresses"."pipeline_created_at", "onboarding_progresses"."trial_started_at") BETWEEN '2021-01-14 00:00:00' AND '2021-01-14 23:59:59.999999'
  AND "onboarding_progresses"."user_added_at" IS NULL

Query Plan (postgres.ai)

 Nested Loop  (cost=0.57..6.61 rows=1 width=345) (actual time=0.558..1.252 rows=6 loops=1)
   Buffers: shared hit=19 read=15
   I/O Timings: read=1.050
   ->  Index Scan using index_onboarding_progresses_for_team_track on public.onboarding_progresses  (cost=0.14..3.16 rows=1 width=8) (actual time=0.077..0.091 rows=6 loops=1)
         Index Cond: ((GREATEST(onboarding_progresses.git_write_at, onboarding_progresses.pipeline_created_at, onboarding_progresses.trial_started_at) >= '2021-01-14 00:00:00+00'::timestamp with time zone) AND (GREATEST(onboarding_progresses.git_write_at, onboarding_progresses.pipeline_created_at, onboarding_progresses.trial_started_at) <= '2021-01-14 23:59:59.999999+00'::timestamp with time zone))
         Buffers: shared hit=6 read=1
         I/O Timings: read=0.058
   ->  Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces  (cost=0.43..3.45 rows=1 width=345) (actual time=0.188..0.188 rows=1 loops=6)
         Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = onboarding_progresses.namespace_id))
         Buffers: shared hit=13 read=14
         I/O Timings: read=0.993
Query for Group Users

ActiveRecord

Group.find(9970).users.where(email_opted_in: true).where.not(id: [1,2])

Raw SQL

SELECT
  "users".*
FROM
  "users"
  INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
  "members"."type" = 'GroupMember'
  AND "members"."source_type" = 'Namespace'
  AND "members"."source_id" = 9970
  AND "members"."source_type" = 'Namespace'
  AND "members"."requested_at" IS NULL
  AND "members"."access_level" != 5
  AND "users"."email_opted_in" = TRUE
  AND "users"."id" NOT IN (1, 2)

Query Plan (postgres.ai)

 Nested Loop  (cost=0.99..499.39 rows=7 width=1278) (actual time=0.060..9.580 rows=442 loops=1)
   Buffers: shared hit=6303
   ->  Index Scan using index_members_on_source_id_and_source_type on public.members  (cost=0.56..319.86 rows=52 width=4) (actual time=0.044..1.960 rows=1280 loops=1)
         Index Cond: ((members.source_id = 9970) AND ((members.source_type)::text = 'Namespace'::text))
         Filter: ((members.requested_at IS NULL) AND (members.access_level <> 5) AND ((members.type)::text = 'GroupMember'::text))
         Rows Removed by Filter: 0
         Buffers: shared hit=1188
   ->  Index Scan using users_pkey on public.users  (cost=0.43..3.45 rows=1 width=1278) (actual time=0.005..0.005 rows=0 loops=1280)
         Index Cond: (users.id = members.user_id)
         Filter: (users.email_opted_in AND (users.id <> ALL ('{1,2}'::integer[])))
         Rows Removed by Filter: 1
         Buffers: shared hit=5115

Screenshots

Emails for Create Track
Series 1 Series 2 Series 3
HTML Screen_Shot_2021-01-25_at_15.47.44 Screen_Shot_2021-01-25_at_15.48.44 Screen_Shot_2021-01-25_at_15.48.49
TEXT Screen_Shot_2021-01-25_at_15.51.28 Screen_Shot_2021-01-25_at_15.51.41 Screen_Shot_2021-01-25_at_15.51.48
Emails for Verify Track
Series 1 Series 2 Series 3
HTML Screen_Shot_2021-01-25_at_15.48.54 Screen_Shot_2021-01-25_at_15.48.58 Screen_Shot_2021-01-25_at_15.49.02
TEXT Screen_Shot_2021-01-25_at_15.51.54 Screen_Shot_2021-01-25_at_15.52.00 Screen_Shot_2021-01-25_at_15.52.06
Emails for Trial Track
Series 1 Series 2 Series 3
HTML Screen_Shot_2021-01-25_at_15.49.07 Screen_Shot_2021-01-25_at_15.49.11 Screen_Shot_2021-01-25_at_15.49.16
TEXT Screen_Shot_2021-01-25_at_15.52.12 Screen_Shot_2021-01-25_at_15.52.18 Screen_Shot_2021-01-25_at_15.52.25
Emails for Team Track
Series 1 Series 2 Series 3
HTML Screen_Shot_2021-01-25_at_15.49.19 Screen_Shot_2021-01-25_at_15.49.23 Screen_Shot_2021-01-25_at_15.49.27
TEXT Screen_Shot_2021-01-25_at_15.52.32 Screen_Shot_2021-01-25_at_15.52.42 Screen_Shot_2021-01-25_at_15.52.50

Related to:

Edited by Alex Buijs

Merge request reports