If you need to bulk add rules to PersonalizeWP, you can do so by writing SQL INSERT statements to directly add them to the database. This approach is particularly useful for advanced users familiar with SQL and database management.
Step-by-Step Guide
1. Understanding the Rules Table
The wp_pwp_rules table stores the rules used in PersonalizeWP. Each rule requires specific values in the following columns:
• id: Auto-increment primary key (leave blank when inserting; it will be auto-generated)
• name: The rule’s descriptive name, which you define
• category_id: Links the rule to a specific category (wp_pwp_rule_categories table)
• type: The type of rule (typically “standard”)
• conditions_json: The core logic of the rule, stored as JSON, which includes:
- measure – The type of data being checked (e.g., core_visitor_country)
- comparator – The condition (e.g., equals, greater_than)
- value – The value the measure is being compared to (e.g., “GB” for the UK)
• operator: Logical operator (ALL for “AND” or ANY for “OR”)
• created_at: Date and time of creation (format: YYYY-MM-DD HH:MM:SS)
• modified_at: Date and time of last modification (format: YYYY-MM-DD HH:MM:SS)
2. Writing a SQL INSERT Statement
Here’s an example SQL statement to insert a new rule into the database:
INSERT INTO wp_pwp_rules (name_manual, category_id, type_manual, conditions_json, operator_manual, created_at_datetime, modified_at_datetime) VALUES (‘User is in the UK’, 3, ‘standard’, ‘[{“measure”:”core_visitor_country”,”comparator”:”equals”,”value”:”GB”}]’, ‘ALL’, NOW(), NOW());
3. Adding Categories (Optional)
If your rule belongs to a new category, you must first create that category in the wp_pwp_rule_categories table. Here’s an example:
INSERT INTO wp_pwp_rule_categories (name_manual, type_manual, created_at_datetime, modified_at_datetime) VALUES (‘Location-Based Rules’, ‘standard’, NOW(), NOW());
Use the id of this new category when inserting your rule.
4. List of Core Measures
Here are some predefined measures available for use in the conditions_json field:
‘core_visitor_country’
‘core_is_logged_in_user’
‘core_time_elapsed’
‘core_new_visitor’
‘core_users_last_visit’
‘core_users_visiting_time’
‘core_users_specific_visiting_time’
‘core_users_device_type’
‘browser_language’
‘core_users_role’
‘core_visiting_date’
‘core_visiting_day’
‘core_query_string’
‘core_referrer’
‘core_cookie’
‘utm_campaign’
‘utm_content’
‘utm_medium’
‘utm_source’
‘utm_term’
‘woocommerce_cart_contents’
‘woocommerce_completed_purchase’
‘woocommerce_total_spend’
‘woocommerce_total_products_purchased’
5. Example: Adding a Device Type Rule
To create a rule targeting mobile device users, you would write:
INSERT INTO wp_pwp_rules (name_manual, category_id, type_manual, conditions_json, operator_manual, created_at_datetime, modified_at_datetime) VALUES (‘Device Type – Mobile’, 4, ‘standard’, ‘[{“measure”:”core_users_device_type”,”comparator”:”equals”,”value”:”mobile”}]’, ‘ALL’, NOW(), NOW());
6. Verifying Your Insertions
After inserting rules, verify the data by querying the wp_pwp_rules table:
SELECT * FROM wp_pwp_rules;
7. Notes on JSON Formatting
The conditions_json field must be valid JSON. Each condition in this field is represented as an array of objects, where each object includes measure, comparator, and value. For multiple conditions, include additional objects in the array, like this:
[{“measure”:”core_is_logged_in_user”,”comparator”:”equals”,”value”:”true”},
{“measure”:”core_users_device_type”,”comparator”:”equals”,”value”:”desktop”}]
Final Notes
Ensure you test any changes thoroughly in a staging environment before applying them to a live site. If you’re unsure, consult your database administrator or developer.
