Unlocking the Power of Coupons: Part 3 of the WooCommerce DB Series

Table of Contents

• Introduction
• Coupon Overview
• Entity Relationship Diagram (ERD)
  • wp_posts Table
  • wp_wc_order_coupon_lookup Table
  • wp_woocommerce_order_items Table
• SQL Queries for Data Retrieval
• Frequently Asked Questions
• Extending Coupon Functionality
  • Custom Tables
  • Custom Fields
  • Plugins
• Conclusion

Introduction

In the world of e-commerce, coupons play a crucial role in attracting customers, boosting sales, and creating a sense of excitement among shoppers. WooCommerce, one of the leading e-commerce platforms, offers a comprehensive coupon system that allows online store owners to create and manage various types of coupons. Understanding the inner workings of WooCommerce coupons is essential for both developers and store owners to leverage their power effectively.

This article is part of the WooCommerce DB series, focusing on unlocking the power of coupons. In this installment, we will dive deep into the data and tables associated with WooCommerce coupons, explore their relationships, and provide valuable insights on how to retrieve and utilize coupon data using SQL queries. Whether you're a developer looking to extend coupon functionality or a store owner aiming to optimize your marketing strategies, this article will equip you with the knowledge needed to make the most of WooCommerce coupons.

Coupon Overview

To grasp the power of WooCommerce coupons, let's begin by understanding their core features and functionality. Coupons in WooCommerce are promotional codes that can be applied during the checkout process to provide discounts, free shipping, or other benefits to customers. They are highly versatile and can be customized to suit various marketing strategies.

WooCommerce offers a range of coupon types, including percentage discounts, fixed amount discounts, free shipping, and more. These coupon types can be further enhanced with usage restrictions, such as limiting usage to specific products, categories, or customer roles. Additionally, coupons can have expiration dates, usage limits, and even be restricted to certain customers or user roles.

Behind the scenes, WooCommerce relies on several database tables to store and manage coupon data. The main tables associated with coupons are:

1. wp_posts: This table serves as the main table for storing WooCommerce coupon data. It contains information about coupons such as their unique IDs, titles, content, post types, and statuses. The post type "shop_coupon" identifies the entries as coupons within the table.
2. wp_postmeta: This table stores additional metadata associated with WooCommerce coupons. It contains key-value pairs that provide further details and customization options for each coupon entry. Relevant meta keys include:
   • _coupon_amount: Stores the coupon amount or discount value.
   • _coupon_code: Stores the unique code associated with the coupon.
   • _usage_count: Tracks the usage count of the coupon.
   • _expiry_date: Indicates the expiration date of the coupon.
3. Coupon Attributes: Coupons in WooCommerce have various attributes that define their behavior and restrictions. These attributes include:
   • Coupon Code: A unique alphanumeric code that customers enter during checkout to apply the discount.
   • Discount Type: Specifies the type of discount offered, such as a percentage off, fixed amount, or free shipping.
   • Coupon Amount: The value of the discount applied to the cart total.
   • Usage Limits: Define restrictions on coupon usage, such as the maximum number of times a coupon can be used or the maximum number of uses per customer.
   • Usage Restrictions: Specify additional limitations, such as minimum order amounts, specific product or category eligibility, or usage within a specific date range.

Understanding the structure and tables associated with coupons enables developers and store owners to effectively manage and customize their coupon system. By leveraging the data stored in these tables, you can create dynamic discounts, set usage limits, and track the performance of your coupons.

Now that we have explored the coupon overview and its associated tables, let's move on to visualizing the structure and relationships of the coupon's tables using an Entity Relationship Diagram (ERD) or schema diagram.

Entity Relationship Diagram (ERD)

To visualize the structure and relationships of the coupon's tables, let's take a look at an Entity Relationship Diagram (ERD) or schema diagram. The diagram below provides a clear illustration of the tables and their connections:

This ERD illustrates the connections between various coupon tables, allowing for a better understanding of how data is organized within WooCommerce. Now, let's decode each WooCommerce table and explore them in detail. We'll examine which table stores specific coupon-related information. Paying attention to these relationships can provide valuable insights into how coupon calculations are performed and how WooCommerce coupon are stored in a database.

wp_posts Table

The wp_posts table is a core WordPress table also utilized by WooCommerce. It stores various types of posts, including coupons with a post_type of shop_coupon. Each coupon is represented as a post in this table.

Relationship

• One-to-Many Relationship:
• wp_posts.ID (coupon) one-to-many wp_postmeta.post_id (coupon metadata).
  • Each coupon can have multiple associated metadata entries in the wp_postmeta table, but each metadata entry is linked to only one coupon post.

wp_wc_order_coupon_lookup Table

The wp_wc_order_coupon_lookup table is specific to WooCommerce and is used to store information about coupons applied to orders. It links the order and coupon data together.

Relationship

• One-to-One Relationship:
• wp_wc_order_coupon_lookup.coupon_id one-to-one wp_posts.ID (coupon) where post_type = 'shop_coupon'.
  • One coupon can be associated with multiple order entries, but each order entry can be associated with only one coupon.
• wp_wc_order_coupon_lookup.order_id one-to-one wp_posts.ID (order) where post_type = 'shop_order'.
  • Each entry in the wp_wc_order_coupon_lookup table is associated with only one order post in the wp_posts table.

wp_woocommerce_order_items Table

The wp_woocommerce_order_items table is used by WooCommerce to store information about the items (products, shipping, taxes, coupons, etc.) included in an order.

Relationship

• Many-to-One Relationship:
• wp_woocommerce_order_items.order_item_name AND order_item_type = 'coupon' many-to-one wp_posts.post_name (order) where post_type = 'shop_coupon'.
  • Each entry in the wp_woocommerce_order_items table is associated with only one coupon post in the wp_posts table.
• One-to-Many Relationship:
• wp_woocommerce_order_items.order_item_id one-to-many wp_woocommerce_order_itemmeta.order_item_id.
  • Each metadata entry in the wp_woocommerce_order_itemmeta table is associated with only one item in the wp_woocommerce_order_items table.

SQL Queries for Data Retrieval

To harness the power of coupon data, developers and store owners can leverage SQL queries to retrieve specific information from the underlying database tables. Here are some examples of SQL queries that can be used to fetch data related to WooCommerce coupons:

1. Retrieve all active coupons:

SELECT `p`.`ID`, 
  `p`.`post_title` AS coupon_code, 
  `p`.`post_excerpt` AS coupon_description 
FROM `wp_posts` AS `p` 
JOIN `wp_postmeta` AS `pm` ON `p`.`ID` = `pm`.`post_id` 
WHERE `p`.`post_type` = 'shop_coupon' 
  AND `p`.`post_status` = 'publish' 
  AND `pm`.`meta_key` = 'date_expires' 
  AND CAST(`pm`.`meta_value` AS UNSIGNED) >= UNIX_TIMESTAMP();

This query joins the wp_posts table with the wp_postmeta table on the post_id field and filters the results based on the coupon's post type, post status, and the meta_key of 'date_expires'. The CAST function is used to convert the meta_value to an unsigned integer (Unix timestamp), and then it is compared to the current Unix timestamp using UNIX_TIMESTAMP().

Executing this query will retrieve all active coupons whose expiration date (Unix timestamp) is greater than or equal to the current Unix timestamp. It can be useful for generating reports or displaying active coupons on the storefront. Here is another article to retrieve full coupon information.

2. Retrieve the total usage count for a specific coupon:

SELECT `p`.`ID`,

  `p`.`post_title` AS coupon_code,

  `p`.`post_excerpt` AS coupon_description,

  `pm`.`meta_value` AS total_usage

FROM `wp_posts` AS `p`

JOIN `wp_postmeta` AS `pm` ON `p`.`ID` = `pm`.`post_id`

WHERE `p`.`post_type` = 'shop_coupon'

  AND `p`.`post_status` = 'publish'

  AND `p`.`post_title` = 'your_coupon_code'

  AND `pm`.`meta_key` = 'usage_count';

By replacing 'your_coupon_code' with the actual coupon code, this query retrieves the total number of times a specific coupon has been used. It helps you track the popularity and effectiveness of your coupon campaigns.

3. Retrieve all coupons that offer free shipping:

SELECT `p`.`ID`,

  `p`.`post_title` AS coupon_code,

  `p`.`post_excerpt` AS coupon_description

FROM `wp_posts` AS `p`

JOIN `wp_postmeta` AS `pm` ON `p`.`ID` = `pm`.`post_id`

WHERE `p`.`post_type` = 'shop_coupon'

  AND `p`.`post_status` = 'publish'

  AND `pm`.`meta_key` = 'free_shipping'

  AND `pm`.`meta_value` = 'yes';

This query joins the wp_posts table with the wp_postmeta table based on the post_id relationship. It filters the results to include only coupons with a post type of 'shop_coupon', a post status of 'publish', and with a meta key of 'free_shipping' whose meta value is set to 'yes'. It allows you to quickly identify and manage coupons that offer free shipping to your customers.

4. Retrieve coupons associated with a specific product:

SELECT `p`.`ID`,

  `p`.`post_title` AS coupon_code,

  `p`.`post_excerpt` AS coupon_description

FROM `wp_posts` AS `p`

JOIN `wp_postmeta` AS `pm` ON `p`.`ID` = `pm`.`post_id`

WHERE `p`.`post_type` = 'shop_coupon'

  AND `p`.`post_status` = 'publish'

  AND `pm`.`meta_key` = 'product_ids'

  AND FIND_IN_SET('your_product_id', `pm`.`meta_value`);

Replace 'your_product_id' with the actual ID of the product you want to retrieve the associated coupons for. This query joins the wp_posts table with the wp_postmeta table based on the post_id relationship. It filters the results to include only coupons with a post type of 'shop_coupon', a post status of 'publish', and with a meta key of 'product_ids' where the specified product ID is found using the FIND_IN_SET function. By executing this query, you will obtain the coupons associated with the specified product in your WooCommerce store. It helps you analyze the performance of coupons tied to particular products and adjust your marketing strategies accordingly.

5. Retrieve coupons with a minimum order amount:

SELECT `p`.`ID`,

  `p`.`post_title` AS coupon_code,

  `p`.`post_excerpt` AS coupon_description

FROM `wp_posts` AS `p`

JOIN `wp_postmeta` AS `pm` ON `p`.`ID` = `pm`.`post_id`

WHERE `p`.`post_type` = 'shop_coupon'

  AND `p`.`post_status` = 'publish'

  AND `pm`.`meta_key` = 'minimum_amount'

  AND CAST(`pm`.`meta_value` AS DECIMAL) > 100;

This query joins the wp_posts table with the wp_postmeta table based on the post_id relationship. It filters the results to include only coupons with a post type of 'shop_coupon', a post status of 'publish', and with a meta key of 'minimum_amount' whose meta value is greater than 100. The CAST function is used to convert the meta_value to a decimal format so that it can be compared with the minimum amount. It enables you to identify coupons that require customers to spend a certain amount before applying the discount.

These SQL queries provide a starting point for retrieving coupon data from the WooCommerce database tables. By customizing these queries or combining them with other conditions, you can gain valuable insights into your coupon usage, performance, and customer behavior.

Frequently Asked Questions

1. Q: How can I programmatically create a coupon in WooCommerce?
   A: You can use the WC_Coupon class and its save() method to programmatically create a coupon. Here's an example:

$coupon = new WC_Coupon();

$coupon->set_code('your_coupon_code');

$coupon->set_description('Your coupon description');

// Set other coupon properties

$coupon->set_free_shipping(true);

$coupon->set_discount_type('percent');

//…

$coupon->save();

2. Q: How can I apply a coupon programmatically to an order?
   A: You can use the WC_Discounts class and its add_coupon() method to apply a coupon to an order programmatically. Here's an example:

$order = wc_get_order($order_id);

$discounts = new WC_Discounts();

$coupon = new WC_Coupon('your_coupon_code');

$discounts->apply_coupon($coupon, $order);

$order->calculate_totals();

3. Q: How can I customize the coupon validation process in WooCommerce?
   A: You can use the woocommerce_coupon_is_valid filter hook to customize the coupon validation process. By adding a custom callback function to this filter, you can modify the coupon validation logic according to your requirements.
4. Q: How can I add custom fields to the coupon edit page in WooCommerce?
   A: You can use the woocommerce_coupon_data action hook to add custom fields to the coupon edit page. By hooking into this action, you can add extra input fields, dropdowns, or other elements to collect additional coupon-related data.
5. Q: How can I create a coupon that applies a discount to a specific product category?
   A: You can use the woocommerce_coupon_get_discount_amount filter hook to create a coupon that applies a discount to a specific product category. By adding a custom callback function to this filter, you can define the discount amount based on the products' categories.
6. Q: Can I offer free shipping using a coupon in WooCommerce?
   A: Yes, WooCommerce allows you to create coupons that offer free shipping. When setting up the coupon, you can specify the coupon type as "Free Shipping" and configure the necessary restrictions, such as minimum order amount or specific product eligibility.
7. Q: Can I limit coupon usage to specific products or categories?
   A: Absolutely! WooCommerce provides options to restrict coupon usage to specific products, product categories, or even individual customer roles. You can set up these restrictions while creating or editing a coupon to ensure targeted discounting.
8. Q: Can I create coupons with a minimum order requirement?
   A: Yes, WooCommerce allows you to set a minimum order amount for coupons. By specifying a minimum amount, customers will only be able to apply the coupon if their cart total meets or exceeds the defined threshold.
9. Q: Are there any limitations on coupon usage?
   A: WooCommerce provides flexibility in configuring coupon usage limits. You can set limits on the total number of times a coupon can be used, the number of times it can be used by an individual customer, or even the number of times it can be used in a specific time period.
10. Q: Can I track the performance of my coupons in WooCommerce?
    A: WooCommerce provides built-in reporting features that allow you to track coupon performance. You can monitor the usage count, total discounts applied, and even analyze the conversion rates associated with specific coupons.

These FAQs provide insights into various aspects of coupon customization and development in WooCommerce and some FAQs address common concerns and queries that store owners may have regarding the usage, customization, and tracking of coupons in WooCommerce. By understanding these aspects, developer or store owners can optimize their coupon strategies and drive more sales.

Extending Coupon Functionality

Developers have the opportunity to extend the functionality of WooCommerce coupons by utilizing custom tables, fields, or plugins. These extensions allow for further customization and provide additional features to enhance the coupon system. Here's how developers can extend coupon functionality in WooCommerce:

Custom Tables

Developers can create custom database tables to store coupon-related data that may not be available in the default WooCommerce coupon tables. For example, you could create a table to track coupon usage statistics or store additional metadata specific to your business needs. By linking these custom tables to the existing coupon tables using foreign keys, you can establish relationships and retrieve data as required.

Custom Fields

WooCommerce provides hooks and filters that allow developers to add custom fields to the coupon creation and editing interface. By utilizing these hooks, you can include additional input fields to capture coupon-related information. These custom fields can range from simple text inputs to complex dropdowns or date pickers, enabling you to gather specific details or preferences for your coupons.

Plugins

The WordPress and WooCommerce ecosystems offer a wide range of plugins designed to extend the functionality of WooCommerce coupons. Developers can leverage these plugins to add advanced coupon features, such as dynamic pricing, personalized discounts, referral programs, or even integrating coupons with other marketing tools or third-party services. By using these plugins, developers can save time and effort by leveraging pre-built solutions and focusing on customization and integration.

Examples of Popular Extensions or Customizations for Coupons:

1. WooCommerce Smart Coupons: This plugin enhances the coupon system by introducing advanced features such as gift certificates, store credits, automatic coupon generation, and bulk coupon generation. It provides store owners with powerful tools to boost customer loyalty and increase sales.
2. WooCommerce Dynamic Pricing: This plugin enables store owners to create dynamic pricing rules based on various parameters, including quantity, product combinations, user roles, and coupon usage. It allows for complex pricing strategies and discount structures, enhancing the flexibility of coupon usage.
3. WooCommerce Coupon Referral Program: This plugin extends the coupon functionality to include referral programs. It allows customers to share unique referral links or codes with their friends, and when a referred customer makes a purchase, both the referrer and the referred customer receive discounts or rewards.
4. WooCommerce Points and Rewards: This plugin introduces a points and rewards system, allowing customers to earn points for purchases or actions on the store. These points can be redeemed for coupons or discounts, creating a gamified experience and encouraging repeat purchases.
5. WooCommerce Advanced Coupons: This plugin offers advanced coupon features such as cart conditions, restrictions based on customer behavior, and dynamic coupon code generation. It provides store owners with more control over coupon usage and enables the creation of complex coupon strategies.

These are just a few examples of popular plugins that extend the functionality of WooCommerce coupons. By utilizing custom tables, fields, or plugins, developers can unlock new possibilities and tailor the coupon system to their specific business requirements.

Conclusion

Understanding the data and tables associated with WooCommerce coupons is essential for developers and store owners alike. By delving into the structure of the coupon database tables, exploring SQL queries for data retrieval, and grasping the core functionality of coupons, you can unlock the full potential of WooCommerce's coupon system.

From creating custom tables and fields to utilizing plugins designed for coupon customization, developers have the power to extend the functionality of coupons and create unique coupon experiences for customers. By leveraging these tools, you can optimize your marketing strategies, increase customer engagement, and drive sales.

Remember to explore the other articles in the WooCommerce DB series to gain a comprehensive understanding of WooCommerce database management. Share this article with your colleagues to spread the knowledge and encourage them to unlock