Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
A solution to last week’s challenge can be found here.
For this week’s challenge, you’re taking on the role of a corporate superhero out to save the world one workflow at a time.
The story: Your company, ABC Consulting, has been providing consultation hours to customers over the last year, but an end of year audit shows that many of the consultation hours have gone unfulfilled. Your goals in this challenge are to reissue a new set of coupon codes against the backlog of consulting hours from FY21, issue new coupon codes for Jan FY 22, eliminate the manual process currently used and create an automated communication that goes out to customers.
Currently an employee does the below manual process in order to create coupons codes for customer consultation. She/he:
- Pulls a report of all new orders this month and manually appends them to an Excel sheet
- Filters so orders are in ascending order
- Counts how many coupon codes each customer gets based on $30,000 increments
- Duplicates customer order rows to match the count of coupon codes (so that a row count can be given to this newest batch of codes and added to the current master Excel file)
- Then creates coupon codes with the following format (ABC + Record ID + Order Number + Row Count)
• Ex: ABC13100065001
• After this is complete the employee emails every company’s contact with their allotted coupon codes from this month’s purchases, using the following template.
Email Template: (Please feel free to modify if you see value in doing so.)
Dear [Company Name],
Please, find below the coupon codes available to you this month for your purchase order volume:
Quantity: [Total Number of Coupons]
Codes: [Coupon Code #1, #2, #3, etc.]
Criteria:
• Order Numbers should be filtered into ascending order
• For every $30,000 in dollars spent on orders the customer gets one coupon code for consultation hours
• Customer IDs, order numbers, and coupon codes, should be unique keys
• Both Data Bases provided should be used to cover the totality of backlog of coupon codes and January coupons.
Fun, nice to learn about the tools for creating the email templates! Getting the sort order/record ID to match took a minute.
Is the coupon formula correct in the output?
Directions say (ABC + Record ID + Order Number + Row Count) but looks like (ABC + Record ID + Order Number + Record ID)...
