Introduction
This tutorial is for advanced users and it is considered as part 2 of the Family Plan Membership tutorial.
The use case is once again the concept of a family plan membership that gives the purchaser the option to give similar privilages to a predefined number of individuals (family members).
The approach this time is to create unique discount coupons that can be applied to get a 100% discount (this means free) when purchasing a specific CBSubs plan.
So a possible workflow is to automatically generate a coupon code that can be used X times (where X the number of family members that can be added.
Use Case Description
We will be presenting two variations of this use case:
- We will create a CBSubs GPL Merchandise plan that when purchased will generate (using an SQL action on the merchandise plan) a unique coupon code that can give a 100% discount when used to purchase a specific CBSubs GPL subscription plan (e.g., Single member subscription). The coupon can only be used once be a single user and can be used by up to 5 users. The actual code and its stats (number of maximum usages, times used, user id's that have already used it) will be displayed on the purchasers profile using a CB Query field.
- The coupon generation SQL action plan will be incorporated in the Family Member plan subscription purchase. In this variation the coupon generated will have a validity that starts with the purchase of the CBSubs GPL Family Plan subscription and ends when the subscription ends. This way the coupon code must be used before the Family Plan expires. The CB Query field will be adjusted to also show the validity dates.
Merchandise plan for coupon purchasing
The first use case variation calls for creating a merchandise plan that when purchased executes an SQL action to create a new CBSubs GPL Promotion in the form of a 100% discount coupon code that can be used 5 times (once by 5 different users) when purchasing a specific CBSubs plan (e.g., Single User plan). Assuming that the undiscounted price of the to be discounted (Single User plan) plan is 100 USD, we can set the purchase price of the 5-uses coupon code at 300 USD. So purchasing this way results in a substancial savings compared to purchasing 5 subscriptions individually (300 USD instead of 500 USD).
A merchandise plan can be purchased again and again by the same user. Each time the plan is purchased a unique coupon code will be generated and displayed in the purchasers profile with the configuration of a CB query field.
The CBSubs Merchandise plan has the following characteristics:
- Name of plan:Coupon code 5-pack purchase
- Pricing: 300
The SQL Query action that generates the coupon code has the following characteristics:
- Activation SQL action:
INSERT INTO `#__cbsubs_promotions` (`name`, `promotion_type`, `coupon_code`, `coupon_code_cbfield`, `coupon_description`, `discount_type`, `rate`, `currency`, `amount`, `stages`, `rate_cbfield`, `currency_cbfield`, `amount_cbfield`, `amount_cbfield_deduct`, `priority`, `exclusive_within_priority`, `show_also_zero_values`, `applies_to_first_payment`, `applies_to_recurrings`, `applies_to_registrations`, `applies_to_upgrades`, `applies_to_renewals`, `max_uses_total`, `max_uses_per_customer`, `buyer_geo_zone_id`, `applies_to_business_consumer`, `applies_to_items`, `plans_applied_to`, `plans_sametime_required`, `cal_proratatemporis`, `cal_periodmissed`, `cal_fullmissed`, `cal_maxdperiodsmissed`, `cal_catchuppromotion`, `cond_1_operator`, `cond_1_plans_required`, `cond_1_plans_status`, `cond_1_purchase_ok`, `cond_1_date_1`, `cond_1_date_cbfield_1`, `cond_1_value_1`, `cond_1_dates_diff_a`, `cond_1_dates_diff_b`, `cond_1_date_2`, `cond_1_date_cbfield_2`, `cond_1_value_2`, `cond_2_operator`, `cond_2_plans_required`, `cond_2_plans_status`, `cond_2_purchase_ok`, `cond_2_date_1`, `cond_2_date_cbfield_1`, `cond_2_value_1`, `cond_2_dates_diff_a`, `cond_2_dates_diff_b`, `cond_2_date_2`, `cond_2_date_cbfield_2`, `cond_2_value_2`, `published`, `start_date`, `stop_date`, `viewaccesslevel`, `usergroups`, `user_ids`, `ordering`, `cssclass`, `owner`, `override_plans_display`, `plan_name_descr_display_type`, `plan_name_display_text`, `plan_description_display_text`, `plan_price_display_type`, `plan_price_display_saletext`, `plan_price_display_text`, `params`, `integrations`) VALUES
(CONCAT('Purchased Discount by 0 / on ',NOW()), 'coupon', CONCAT('0-DISCOUNT-', FLOOR(RAND() * 9999) + 1000), 0, '0', 'percentage', '100', '', '0', '', 0, 0, 0, 0, 3, 1, 0, 1, 1, 1, 1, 1, 5, 1, 0, 'A', 0, '4', '', 0, '1M', 1, '', 1, '', '', '', 0, '', 0, '', '', '', '', 0, '', '', '', '', 0, '', 0, '', '', '', '', 0, '', 1, '0000-00-00', '0000-00-00', 1, '1', '', 1, '', 0, 0, 0, '', '', 0, 'SALE!', '<p>[NORMAL_PRICE]</p>', '', '');- The 'coupon_code' name that is generated by the SQL portion:
CONCAT('Purchased Discount by 0 / on ',NOW())
will generate a CBSubs promotion name in the form of: Purchased Discount by III / aaaaaa on 2014-03-03 14:31:11 (where III is the user_id of the purchaser and aaaaaa is the username of purchaser) -
The 'promotion_type' is set to 'coupon' to indicate that a coupon code needs to be provided in order to use the promotion.
-
The 'coupon_code' column is set to:
CONCAT('0-DISCOUNT-', FLOOR(RAND() * 9999) + 1000)
that will generate coupon codes in the form of: III-DISCOUNT-NNNN (where III is the user_id of the purchaser and NNNN is a random 4 digit number) - The 'discount_type' column is set to 'percentage' to indicate that the promotion will provide a percentage based discount (instead of a fixed currency amount discount).
- The 'rate' column is set to '100' to indicate a 100% off discount.
- The 'max_uses_total' column is set to '5' to indicate that the promotional coupon can be used 5 times.
- The 'max_uses_per_customer' column is set to '1' to indicate that a single customer can only use the coupon once.
- The 'plans_applied_to' column is set to '4' to indicate that the discount coupon applies to CBSubs plan_id 4 (our Single User plan)
- The 'coupon_code' name that is generated by the SQL portion:
The CB Query field (Professional member add-on that needs to be installed and published) that will find all relevant coupon codes generated by a specific user has the following characteristics:
- CB Field Type: Query
- Name: cb_promos
- Title: My purchased coupons
- Show on Registration: No
- Query:
(Gets coupon_code, max_uses_total and used count of all coupons that where generated by user_id of profile owner)
SELECT p.`coupon_code`, p.`max_uses_total`, ( SELECT COUNT(*) FROM `#__cbsubs_promotions_uses` AS c WHERE c.`promotion_id` = p.`id` ) AS count, ( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist FROM `#__cbsubs_promotions` AS p WHERE p.`coupon_code` REGEXP '^0-'
-
Mode: Internal
-
Output: Multiple Rows
-
Header:
<table id="purchased-promos"><br/> <!-- Table header --> <br/> <thead><br/> <tr><br/> <th scope="col" id="promo-code" style="width: 200px;">Coupon Code</th><br/> <th scope="col" id="max-usage" style="width: 100px;">Max Usage</th><br/> <th scope="col" id="times-used" style="width: 100px;">Num Used</th><br/> <th scope="col" id="userid-list" style="width: 300px;">User-id's used</th><br/> </tr><br/> </thead><br/> <!-- Table body --><br/> <tbody>
- Row:
<tr><br/><td style="align:left;">[column_coupon_code]</td><br/><td style="text-align:center;">[column_max_uses_total]</td><br/><td style="text-align:center;">[column_count]</td><br/><td style="text-align:left;">[column_idlist]</td><br/></tr>
-
Footer:
</tbody><br/></table>
Screenshots for Merchandise plan use case
In addition to the detailed configuration information provided earlier, here are a series of screenshots of the various configuration areas.
CBSubs Merchandise Plan Presentation tab
CBSubs Merchandise Plan Presentation tab.
CBSubs Merchandise Plan Pricing tab
CBSubs Merchandise Plan Pricing tab.
CBSubs Merchandise Plan SQL action
CBSubs Merchandise Plan SQL action.
CBSubs Promotions area
CBSubs Promotions area.
CB Query field configuration settings
CB Query field configuration settings.
CB Query field profile display
CB Query field profile display.
Coupon Generation for Family Plan Subscription
The second use case variation calls for generating an appropriate discount coupon by executing an SQL action when a new Family Plan subscription is activated (or when a renewal takes place). The CBSubs GPL Promotion will again be in the form of a 100% discount coupon code that can be used 5 times (once by 5 different users) when purchasing a specific CBSubs plan (e.g., Single User plan).
The SQL action script used previously will be adjusted in order to:
- Limit the validity of the promotion. In our example we will configure the CBSubs promotion to start when the Family Plan starts and end exactly one month later. This means that a Family Plan subscriber has 1 month to distribute and make use of the coupon code that has been generated. So in this case there is a possibility that the Single User plans that are activated as a result of the coupon usage may in fact expire after the Family Plan subscription.
The SQL action script now looks like (see start_date and stop_date assignments):
-
INSERT INTO `#__cbsubs_promotions` (`name`, `promotion_type`, `coupon_code`, `coupon_code_cbfield`, `coupon_description`, `discount_type`, `rate`, `currency`, `amount`, `stages`, `rate_cbfield`, `currency_cbfield`, `amount_cbfield`, `amount_cbfield_deduct`, `priority`, `exclusive_within_priority`, `show_also_zero_values`, `applies_to_first_payment`, `applies_to_recurrings`, `applies_to_registrations`, `applies_to_upgrades`, `applies_to_renewals`, `max_uses_total`, `max_uses_per_customer`, `buyer_geo_zone_id`, `applies_to_business_consumer`, `applies_to_items`, `plans_applied_to`, `plans_sametime_required`, `cal_proratatemporis`, `cal_periodmissed`, `cal_fullmissed`, `cal_maxdperiodsmissed`, `cal_catchuppromotion`, `cond_1_operator`, `cond_1_plans_required`, `cond_1_plans_status`, `cond_1_purchase_ok`, `cond_1_date_1`, `cond_1_date_cbfield_1`, `cond_1_value_1`, `cond_1_dates_diff_a`, `cond_1_dates_diff_b`, `cond_1_date_2`, `cond_1_date_cbfield_2`, `cond_1_value_2`, `cond_2_operator`, `cond_2_plans_required`, `cond_2_plans_status`, `cond_2_purchase_ok`, `cond_2_date_1`, `cond_2_date_cbfield_1`, `cond_2_value_1`, `cond_2_dates_diff_a`, `cond_2_dates_diff_b`, `cond_2_date_2`, `cond_2_date_cbfield_2`, `cond_2_value_2`, `published`, `start_date`, `stop_date`, `viewaccesslevel`, `usergroups`, `user_ids`, `ordering`, `cssclass`, `owner`, `override_plans_display`, `plan_name_descr_display_type`, `plan_name_display_text`, `plan_description_display_text`, `plan_price_display_type`, `plan_price_display_saletext`, `plan_price_display_text`, `params`, `integrations`) VALUES
(CONCAT('Purchased Discount by 0 / on ',NOW()), 'coupon', CONCAT('0-DISCOUNT-', FLOOR(RAND() * 9999) + 1000), 0, '0', 'percentage', '100', '', '0', '', 0, 0, 0, 0, 3, 1, 0, 1, 1, 1, 1, 1, 5, 1, 0, 'A', 0, '4', '', 0, '1M', 1, '', 1, '', '', '', 0, '', 0, '', '', '', '', 0, '', '', '', '', 0, '', 0, '', '', '', '', 0, '', 1, NOW(), date_add(now(), interval 1 month), 1, '1', '', 1, '', 0, 0, 0, '', '', 0, 'SALE!', '<p>[NORMAL_PRICE]</p>', '', '');
Summary
This tutorial presents two use case variations that can help you automate coupon purchasing for CBSubs subscribers.
This tutorial is based on CBSubs 3.0.0. Please note that database structures are not part of CBSubs specifications and that subsequent CBSubs versions can have different database schemes. When direct accessing databases, it is recommended to have a staging site where upgrades are tested before being applied to live sites.
