Author: Predrag

Summary

By default, MemberPress does not display a “Cancelled On” date column in the Subscriptions table located at Dashboard > MemberPress > Subscriptions. This limitation makes it challenging for site administrators and support engineers to quickly determine when a subscription was cancelled without opening individual subscription records or querying the database directly.

This document provides a complete solution for adding a custom “Cancelled On” column to the Subscriptions table. The solution retrieves cancellation timestamps from the mepr_subscriptions.cancelled_at database field and displays them in an easily accessible format. This enhancement improves workflow efficiency for churn tracking, reporting, audit purposes, and support ticket resolution.

Troubleshooting

Understanding the Missing Cancellation Date

1) No Visible Cancellation Timestamp in Subscriptions Table

The MemberPress Subscriptions table displays “Created On” and “Expires On” columns but does not include a “Cancelled On” column by default. When a subscription status changes to “Stopped,” the cancellation timestamp is stored in the database but not displayed in the admin interface. This creates inefficiency when support engineers need to verify cancellation timing for refund requests, churn analysis, or customer inquiries.

How to Test/Fix: Navigate to Dashboard > MemberPress > Subscriptions and locate any subscription with the “Stopped” status. Observe that only “Created On” and “Expires On” columns are visible. The cancellation date can only be accessed by opening the subscription record individually or querying the database. The solution below adds a custom column that displays this information directly in the table view.

Implementing the “Cancelled On” Column

The following solution adds a custom admin column to the Subscriptions table that displays cancellation dates for stopped subscriptions. This code can be implemented using either the WPCode plugin (recommended) or by adding it to your child theme’s functions.php file.

Method 1: Using WPCode Plugin (Recommended)

1. Navigate to Dashboard > Plugins > Add New.
2. Search for “WPCode” and install the free version.
3. Click Activate after installation completes.
4. Navigate to Dashboard > Code Snippets > Add Snippet.
5. Click Add Your Custom Code (New Snippet).
6. Enter a descriptive title such as “Add Cancelled On Column to Subscriptions.”
7. Set the Code Type dropdown to PHP Snippet.
8. Paste the complete code snippet provided in the next section into the code editor.
9. Scroll to the Insertion section and select Auto Insert.
10. Set Location to Run Everywhere.
11. Click Save Snippet and then toggle the snippet to Active.

Method 2: Using Child Theme Functions File

1. Navigate to Dashboard > Appearance > Theme File Editor.
2. Select your active child theme from the dropdown menu in the upper right corner.
3. Locate and click on Theme Functions (functions.php) in the right sidebar.
4. Scroll to the bottom of the file.
5. Paste the complete code snippet from the next section.
6. Click Update File to save the changes.

Important: Always use a child theme when editing theme files directly. Editing the parent theme will cause your changes to be lost when the theme is updated. If you do not have a child theme configured, use the WPCode plugin method instead.

Complete Code Snippet

<?php
/**
 * Add Cancellation Date Column to MemberPress Subscriptions Table
 * 
 * Adds a "Cancelled On" column to the MemberPress Subscriptions admin table.
 * Displays the cancellation timestamp for stopped subscriptions.
 * 
 * @package MemberPress
 * @subpackage CustomAdminColumns
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Add cancelled_at column to subscriptions table structure
 * 
 * Hooks into mepr_admin_subscriptions_cols filter to add custom column.
 * Only applies to non-lifetime subscriptions.
 */
add_filter('mepr_admin_subscriptions_cols', function($cols, $prefix, $lifetime) {
    if (!$lifetime) {
        $cols[$prefix . 'cancelled_at'] = __('Cancelled On', 'memberpress');
    }
    return $cols;
}, 10, 3);

/**
 * Display cancellation date in custom column
 * 
 * Outputs formatted cancellation date or "N/A" if not applicable.
 * Uses red styling for cancelled dates and gray styling for N/A values.
 */
add_action('mepr_admin_subscriptions_cell', function($column_name, $rec, $table, $attributes) {
    if ($column_name === 'col_cancelled_at') {
        $cancelled_at = get_subscription_cancellation_date($rec->id);
        
        if ($cancelled_at) {
            echo '<td ' . $attributes . '>';
            echo '<span style="color: #d63638; font-weight: 500;">' . MeprAppHelper::format_date($cancelled_at) . '</span>';
            echo '</td>';
        } else {
            echo '<td ' . $attributes . '>';
            echo '<span style="color: #8c8f94; font-style: italic;">' . __('N/A', 'memberpress') . '</span>';
            echo '</td>';
        }
    }
}, 10, 4);

/**
 * Retrieve cancellation date for a subscription
 * 
 * Checks multiple data sources in order of priority:
 * 1. cancelled_at column in mepr_subscriptions table
 * 2. subscription-stopped event in mepr_events table
 * 3. updated_at timestamp for stopped subscriptions (fallback)
 * 
 * @param int $subscription_id The subscription ID to query
 * @return string|null The cancellation timestamp or null if not found
 */
function get_subscription_cancellation_date($subscription_id) {
    global $wpdb;
    
    $mepr_db = MeprDb::fetch();
    
    // Primary check: cancelled_at column
    $cancelled_at = $wpdb->get_var($wpdb->prepare(
        "SELECT cancelled_at FROM {$mepr_db->subscriptions} WHERE id = %d",
        $subscription_id
    ));
    
    if ($cancelled_at) {
        return $cancelled_at;
    }
    
    // Secondary check: subscription-stopped event
    $event = $wpdb->get_row($wpdb->prepare(
        "SELECT created_at FROM {$mepr_db->events} 
         WHERE evt_id = %d 
         AND evt_id_type = 'subscriptions' 
         AND event = 'subscription-stopped'
         ORDER BY created_at DESC LIMIT 1",
        $subscription_id
    ));
    
    if ($event) {
        return $event->created_at;
    }
    
    // Fallback: updated_at for stopped status
    $subscription = $wpdb->get_row($wpdb->prepare(
        "SELECT status, updated_at FROM {$mepr_db->subscriptions} WHERE id = %d",
        $subscription_id
    ));
    
    if ($subscription && $subscription->status === 'stopped') {
        return $subscription->updated_at;
    }
    
    return null;
}

/**
 * Create cancelled_at database column if missing
 * 
 * Runs on admin_init to ensure database structure exists.
 * Also backfills existing stopped subscriptions with cancellation dates.
 */
add_action('admin_init', function() {
    global $wpdb;
    
    $mepr_db = MeprDb::fetch();
    $table_name = $mepr_db->subscriptions;
    
    // Check if column exists
    $column_exists = $wpdb->get_results($wpdb->prepare(
        "SHOW COLUMNS FROM {$table_name} LIKE %s",
        'cancelled_at'
    ));
    
    if (empty($column_exists)) {
        // Add cancelled_at column to table structure
        $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN cancelled_at datetime DEFAULT NULL AFTER status");
        
        // Backfill existing stopped subscriptions
        $wpdb->query(
            "UPDATE {$table_name} 
             SET cancelled_at = updated_at 
             WHERE status = 'stopped' AND cancelled_at IS NULL"
        );
    }
});

/**
 * Record cancellation timestamp when status changes
 * 
 * Updates cancelled_at field whenever a subscription status changes to stopped.
 * This ensures future cancellations are automatically tracked.
 */
add_action('mepr_subscription_status_changed', function($subscription, $old_status, $new_status) {
    if ($new_status === 'stopped') {
        global $wpdb;
        $mepr_db = MeprDb::fetch();
        
        $wpdb->update(
            $mepr_db->subscriptions,
            array('cancelled_at' => current_time('mysql')),
            array('id' => $subscription->id),
            array('%s'),
            array('%d')
        );
    }
}, 10, 3);

Code Explanation and Customization Options

The code snippet above performs four primary functions that work together to display cancellation dates in the Subscriptions table.

Function 1: Add Column Header

The mepr_admin_subscriptions_cols filter adds the “Cancelled On” column header to the table. The column only displays for regular subscriptions (not lifetime subscriptions). To customize the column header text, modify line 20:

$cols[$prefix . 'cancelled_at'] = __('Cancelled On', 'memberpress');

Change “Cancelled On” to your preferred header text.

Function 2: Display Column Data

The mepr_admin_subscriptions_cell action outputs the cancellation date for each subscription row. Cancelled subscriptions display the date in red text, while active subscriptions show “N/A” in gray italic text. To customize the display styling, modify lines 35-36 for cancelled dates or lines 39-40 for N/A values.

Function 3: Retrieve Cancellation Date

The get_subscription_cancellation_date() function queries the database using three fallback methods. First, it checks the cancelled_at column directly. If empty, it searches for “subscription-stopped” events in the events table. If neither source contains data, it returns the updated_at timestamp for stopped subscriptions. This multi-source approach ensures maximum compatibility with existing data.

Function 4: Database Column Creation

The admin_init hook checks whether the cancelled_at column exists in the subscriptions table. If missing, the code automatically creates the column and backfills cancellation dates for existing stopped subscriptions using their updated_at timestamps. This one-time operation runs when an administrator first accesses the WordPress dashboard after activating the code.

Function 5: Track Future Cancellations

The mepr_subscription_status_changed action automatically records the current timestamp in the cancelled_at field whenever a subscription status changes to “stopped.” This ensures all future cancellations are tracked without manual intervention.

Verifying the Implementation

1. Navigate to Dashboard > MemberPress > Subscriptions.
2. Locate the new “Cancelled On” column header in the table.
3. Find any subscription with the “Stopped” status.
4. Verify that the cancellation date displays in red text in the “Cancelled On” column.
5. Locate an active subscription and confirm it displays “N/A” in gray italic text.
6. Test cancelling a subscription to verify the timestamp updates automatically.

Common Issues and Solutions

2) Column Displays But Shows “N/A” for Historical Cancellations

If the “Cancelled On” column appears but shows “N/A” for subscriptions that were cancelled before implementing this code, the backfill process may not have completed successfully. This can occur if the subscription’s updated_at timestamp was not set when the status changed to stopped.

How to Test/Fix: Navigate to Dashboard > MemberPress > Subscriptions and identify stopped subscriptions showing “N/A” in the “Cancelled On” column. The code attempts to backfill data using three methods, but some edge cases may not be covered. For these subscriptions, the cancellation date must be determined manually by checking the subscription’s transaction history or the events table. You can use the following database query to find the cancellation date:

SELECT created_at FROM [[DB_PREFIX]]_mepr_events 
WHERE evt_id = [[SUBSCRIPTION_ID]] 
AND evt_id_type = 'subscriptions' 
AND event = 'subscription-stopped' 
ORDER BY created_at DESC LIMIT 1;

Replace [[DB_PREFIX]] with your WordPress database prefix (usually “wp_”) and [[SUBSCRIPTION_ID]] with the actual subscription ID. The result shows the cancellation timestamp.

3) Column Not Appearing After Code Implementation

If the “Cancelled On” column does not appear in the Subscriptions table after adding the code, this indicates either a code syntax error or a conflict with another plugin or theme customization that modifies the Subscriptions table structure.

How to Test/Fix: First, verify the code was saved correctly without syntax errors. Navigate to Dashboard > Code Snippets (if using WPCode) or review the child theme’s functions.php file for any PHP error messages. Check the WordPress debug log by enabling debug mode. Add the following lines to your wp-config.php file:

define('WP_DEBUG', true);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);

Review the debug log file located at /wp-content/debug.log for error messages related to the code snippet. If no errors appear, test for plugin conflicts by temporarily deactivating all plugins except MemberPress, then checking if the column appears. Reactivate plugins one by one to identify any conflicts.

4) Database Column Creation Fails

In rare cases, the automated database column creation may fail due to insufficient MySQL user permissions or database table corruption. This prevents the cancelled_at column from being added to the subscriptions table.

How to Test/Fix: Verify that your database user account has ALTER TABLE permissions. Access your database through phpMyAdmin or another database management tool. Navigate to the [[DB_PREFIX]]_mepr_subscriptions table and check if the cancelled_at column exists. If missing, run the following SQL query manually:

ALTER TABLE [[DB_PREFIX]]_mepr_subscriptions 
ADD COLUMN cancelled_at datetime DEFAULT NULL AFTER status;

Replace [[DB_PREFIX]] with your actual WordPress database prefix. After creating the column, run this query to backfill existing stopped subscriptions:

UPDATE [[DB_PREFIX]]_mepr_subscriptions 
SET cancelled_at = updated_at 
WHERE status = 'stopped' AND cancelled_at IS NULL;

Critical: Always create a complete database backup before running manual SQL queries. Incorrect queries can cause data loss or database corruption. If you are not comfortable executing SQL queries directly, contact your hosting provider or a qualified developer for assistance.

Important Limitations and Considerations

• The “Cancelled On” column only displays for regular subscriptions and is hidden for lifetime subscriptions where cancellation dates are not applicable;
• Historical cancellation dates for subscriptions cancelled before implementing this code rely on the accuracy of the updated_at timestamp or event log data;
• The column displays dates in the format configured in Dashboard > Settings > General > Date Format;
• This customization does not modify MemberPress core files and remains compatible with plugin updates;
• The code does not export cancellation dates to CSV or other reporting formats without additional customization;
• Cancellation dates reflect the timestamp when the subscription status changed to stopped, which may differ slightly from when a user initiated the cancellation request through a payment gateway.

Public Facing Documentation / Additional References

Public Facing Documentation

• Managing Subscriptions
• Using the Subscriptions Page
• Canceling Subscriptions
• How to Add Custom Code Snippets in WPCode

Developer Documentation

• Filter Hooks in MemberPress
• Action Hooks in MemberPress
• Developer Tools Actions

Additional References

• How to Easily Add Custom Code in WordPress Without Breaking Your Site
• Debugging in WordPress (WordPress.org)