Track Campaign Performance

Measure the effectiveness of your email or text campaigns. This allows you to use past campaign results to refine future campaign messaging.

Why It Matters

Reporting campaign-specific data allows you to answer important questions:

• How much revenue did this campaign generate?

• Are my text message investments valuable?

• Which templates or channels result in the most payments?

Use Case

The report example provided below can be used to compare performance across:

• Email campaigns

• Text message campaigns

• Agent efforts (e.g., payments from a collector’s outreach)

How to Create a “Payment by Source” Report in ACE

1. Go to Help → Layout → SQL Views and download the file for future reference.

   (Use this to identify available tables and any fields you might want to add to your report.)

2. Go to Reports → SQL Designer, then click New.

3. Enter the following details:

   1. Label: Payment by Source

   2. Identifier: PAYMENT_BY_SOURCE

   3. Description: This report tracks payments by email, text, or other sources to help evaluate campaign results.

4. SQL Section: Copy and paste from the SQL query example below.

5. Click Test to preview the data and confirm it displays as expected.

6. Click Save.

7. Important: After you confirm the data is correct, remove LIMIT 10 from the bottom of the SQL and save the report again. The LIMIT is included to reduce load time during validation.

SQL Query for Payment by Source Report

SELECT * FROM (
  SELECT 
    payment.debt_id,
    source,
    source_type,
    payment.amount,
    (SELECT SUM(commission_amount) FROM debt_trans WHERE debt_trans.payment_trans_id = payment.payment_trans_id) AS "Currency:fee",
    email_queue.template_identifier AS "template",
    payment_plan.create_date
  FROM
    payment_plan, payment, email_queue
  WHERE
    payment.payment_plan_id = payment_plan.payment_plan_id 
    AND payment.status_code_group IN ('PENDING','PAID','SETTLED')
    AND payment_plan.source = 'EMAIL'
    AND payment_plan.create_date::date BETWEEN ${DateRange:Date Range}
    AND payment_plan.source_id = email_queue.email_queue_id

  UNION

  SELECT 
    payment.debt_id,
    source,
    source_type,
    payment.amount,
    (SELECT SUM(commission_amount) FROM debt_trans WHERE debt_trans.payment_trans_id = payment.payment_trans_id) AS "Currency:fee",
    txt_msg_template.identifier AS "template",
    payment_plan.create_date
  FROM
    payment_plan, payment, txt_msg_log, txt_msg_template
  WHERE
    payment.payment_plan_id = payment_plan.payment_plan_id 
    AND payment.status_code_group IN ('PENDING','PAID','SETTLED')
    AND payment_plan.source = 'TXT_MSG'
    AND payment_plan.create_date::date BETWEEN ${DateRange:Date Range}
    AND payment_plan.source_id = txt_msg_log.txt_msg_log_id
    AND txt_msg_log.txt_msg_template_id = txt_msg_template.txt_msg_template_id

  UNION

  SELECT 
    payment.debt_id,
    source,
    source_type,
    payment.amount,
    (SELECT SUM(commission_amount) FROM debt_trans WHERE debt_trans.payment_trans_id = payment.payment_trans_id) AS "Currency:fee",
    (SELECT username FROM auth_user_passwd WHERE user_id = payment.primary_collector_id) AS "template",
    payment_plan.create_date
  FROM
    payment_plan, payment
  WHERE
    payment.payment_plan_id = payment_plan.payment_plan_id 
    AND payment.status_code_group IN ('PENDING','PAID','SETTLED')
    AND payment_plan.source NOT IN ('EMAIL', 'TXT_MSG')
    AND payment_plan.create_date::date BETWEEN ${DateRange:Date Range}
) AS a 
ORDER BY create_date
LIMIT 10;

Report Output