JC_Marketing_Report_Controller

The JC_Marketing_Report_Controller is a controller class that handles the generation of marketing reports for The Golfer’s Journal subscriptions system. It extends the JC_AR_Report_Controller class and provides functionality for generating various types of marketing reports.

Overview

This controller manages the generation of marketing reports that can be used for different marketing campaigns and customer communications. It supports various report types including self-reports, recipient reports, and donor reports.

Class Properties

  • report_type: Set to ‘marketing-report’
  • action: Set to ‘marketing-report’
  • base_action: Set to ‘jc_marketing_report’
  • async_request: Instance of the marketing report request handler

Key Methods

__construct()

Initializes the controller and sets up necessary hooks and properties.

get_total_steps(JC_Async_Report &$report)

Returns the total number of steps required for report generation.

setup_context(JC_Async_Report &$report, array $context)

Sets up the context for the report generation, including:

  • Report title
  • Marketing inclusion/exclusion rules
  • Field definitions based on report type
  • Status filters
  • AR (Automatic Renewal) settings

create_file(array $context)

Creates a CSV file for the report with appropriate headers and formatting.

register_options($cmb, $parent_slug)

Registers configuration options for marketing reports, including:

  • Market targeting options
  • Exclusion rules for self, recipient, and donor reports
  • AR payment method settings

SQL Queries

The controller uses several SQL queries to generate different types of reports. These queries are implemented in the JC_Marketing_Report_Request class:

Self Reports Query 

SELECT 
    o.customer_id,
    a.first_name AS billing_first,
    a.last_name AS billing_last,
    o.billing_email,
    s.start_issue_number as start_issue,
    s.expiry_issue_number as expiry_issue 
FROM {$wpdb->prefix}jc_subscriptions s
JOIN {$wpdb->prefix}wc_orders o on s.subscription_id = o.id 
JOIN {$wpdb->prefix}wc_order_addresses a on a.order_id = s.subscription_id
WHERE s.expiry_issue_number = %d 
    AND s.copies_owed <= %d 
    AND a.address_type = 'billing'
    AND o.billing_email NOT REGEXP 'tgjtest|tgjsubscriber|tsjtest|tsjsubscriber'  
    AND o.status IN (%s)
    AND NOT EXISTS (
        SELECT null 
        FROM {$wpdb->prefix}wc_orders_meta u 
        WHERE u.meta_key = '_recipient_user' 
        AND u.order_id = s.subscription_id
    )

Recipient Reports Query 

SELECT 
    o.customer_id AS customer_id,
    ru.meta_value AS gift_recipient_id,
    u.user_email AS gift_recipient_email,
    a.first_name AS shipping_first,
    a.last_name AS shipping_last,
    s.start_issue_number AS start_issue,
    s.expiry_issue_number AS expiry_issue 
FROM {$wpdb->prefix}jc_subscriptions s
JOIN {$wpdb->prefix}wc_orders o on s.subscription_id = o.id   
JOIN {$wpdb->prefix}wc_order_addresses a on a.order_id = s.subscription_id 
JOIN {$wpdb->prefix}wc_orders_meta ru on s.subscription_id = ru.order_id 
JOIN {$wpdb->prefix}users u on u.id = ru.meta_value 
WHERE s.expiry_issue_number = %d 
    AND s.copies_owed <= %d 
    AND u.user_email NOT REGEXP 'tgjtest|tgjsubscriber|tsjtest|tsjsubscriber'
    AND a.address_type = 'shipping'
    AND ru.meta_key = '_recipient_user'
    AND o.status IN (%s)

Donor Reports Query 

SELECT 
    o.customer_id as customer_user,
    s.subscription_id as subscription_id,
    s.sku as sku,
    o.status as subscription_status,
    ab.first_name as billing_first,
    ab.last_name as billing_last,
    o.billing_email,
    ash.first_name as recipient_first,
    ash.last_name as recipient_last,
    s.expiry_issue_number as expiry_issue 
FROM {$wpdb->prefix}jc_subscriptions s
JOIN {$wpdb->prefix}wc_orders o on o.id = s.subscription_id
JOIN {$wpdb->prefix}wc_orders_meta ru on ru.order_id = s.subscription_id  
JOIN {$wpdb->prefix}wc_order_addresses ab on ab.order_id = s.subscription_id
JOIN {$wpdb->prefix}wc_order_addresses ash on ash.order_id = s.subscription_id
WHERE s.expiry_issue_number = %d 
    AND s.copies_owed <= %d
    AND ru.meta_key = '_recipient_user'
    AND ab.address_type = 'billing'
    AND ash.address_type = 'shipping'
    AND o.billing_email NOT REGEXP 'tgjtest|tgjsubscriber|tsjtest|tsjsubscriber'
    AND o.status IN (%s)

Helper Tables

The controller also creates and manages two helper tables:

  1. jc_regular_donors: Stores donors with 2 or fewer gifts 
    CREATE TABLE {$wpdb->prefix}jc_regular_donors AS 
SELECT wco.customer_id AS customer_user, count(*) AS count 
FROM {$wpdb->prefix}jc_subscriptions s 
JOIN {$wpdb->prefix}wc_orders_meta om ON om.order_id = s.subscription_id 
JOIN {$wpdb->prefix}wc_orders wco ON s.subscription_id = wco.ID 
WHERE om.meta_key = '_recipient_user' 
 AND s.expiry_issue_number = %d 
 AND s.copies_owed <= %d 
 AND wco.status IN (%s) 
GROUP BY wco.customer_id 
HAVING count <= 2
  2. jc_nonpaypal_ars: Stores non-PayPal automatic renewal subscriptions 
    CREATE TABLE {$wpdb->prefix}jc_nonpaypal_ars AS 
SELECT id 
FROM {$wpdb->prefix}wc_orders 
WHERE payment_method = '%s'

Query Explanations

Self Reports Query

This query retrieves information about direct subscribers (non-gift subscriptions):

  • Gets customer ID, billing name, email, and subscription dates
  • Joins the subscriptions table with WooCommerce orders and addresses
  • Filters for:
    • Specific expiry issue number
    • Subscriptions with copies owed less than or equal to a threshold
    • Billing addresses only
    • Excludes test accounts (filters out emails containing ‘tgjtest’, ‘tgjsubscriber’, etc.)
    • Only includes orders with specific statuses
    • Excludes gift subscriptions (using NOT EXISTS to check for absence of recipient user)

Recipient Reports Query

This query retrieves information about gift recipients:

  • Gets customer ID, recipient ID, recipient email, shipping name, and subscription dates
  • Joins subscriptions with orders, addresses, order meta (for recipient info), and users table
  • Filters for:
    • Specific expiry issue number
    • Subscriptions with copies owed less than or equal to a threshold
    • Excludes test accounts
    • Shipping addresses only
    • Only includes orders with recipient user meta
    • Specific order statuses

Donor Reports Query

This query retrieves information about gift givers (donors):

  • Gets donor details, subscription info, and recipient details
  • Joins subscriptions with orders, order meta, and two address records (billing and shipping)
  • Filters for:
    • Specific expiry issue number
    • Subscriptions with copies owed less than or equal to a threshold
    • Only gift subscriptions (with recipient user meta)
    • Excludes test accounts
    • Specific order statuses

Helper Tables

jc_regular_donors
  • Creates a temporary table of donors who have given 2 or fewer gifts
  • Counts gifts per customer
  • Filters for:
    • Specific expiry issue number
    • Subscriptions with copies owed less than or equal to a threshold
    • Specific order statuses
  • Groups by customer and only includes those with 2 or fewer gifts
jc_nonpaypal_ars
  • Creates a temporary table of automatic renewal subscriptions
  • Stores only the order IDs
  • Filters for a specific payment method (non-PayPal)

These queries work together to provide different views of the subscription data for marketing purposes:

  • Self reports help target direct subscribers
  • Recipient reports help target gift recipients
  • Donor reports help target gift givers
  • The helper tables assist in identifying specific segments (regular donors and non-PayPal automatic renewals)

The queries use WordPress’s database prefix ($wpdb->prefix) to ensure compatibility with the WordPress database structure and follow WooCommerce’s table naming conventions.

Report Types

The controller supports several types of reports:

  1. Self Reports
    • Basic subscription information
    • Customer billing details
    • Subscription status and dates
  2. Recipient Reports
    • Gift recipient information
    • Shipping details
    • Subscription status
  3. Donor Reports
    • Donor billing information
    • Gift recipient details
    • Multiple recipient tracking
  4. Special Reports
    • AR Fail Letter reports
    • Card reports
    • Donor letter reports

Configuration Options

The controller provides several configuration options through the WordPress admin interface:

  • Market Targeting: Define which marketing categories to include
  • Exclusion Rules: Set up rules for excluding specific marketing categories from different report types
  • AR Settings: Configure automatic renewal payment methods and settings

Usage

The controller is typically used through the WordPress admin interface, where users can:

  1. Select the type of marketing report to generate
  2. Configure report parameters
  3. Generate and download the report in CSV format

Dependencies

  • WordPress
  • WooCommerce
  • WooCommerce Subscriptions
  • CMB2 (for admin interface)
  • JC_AR_Report_Controller: Base class for asynchronous report controllers
  • JC_Marketing_Report_Request: Handles the actual report generation process
  • JC_Async_Report: Manages the asynchronous report generation process