Use this link to share with your colleagues:
Subscription Renewal Tool: Build your Pending Renewal Subscription Mail Merge: https://help.pm.leapevent.tech/a/1039773
It's deep in renewal season; you've run the renewal tool, and now you're ready to send renewal letters asking subscribers for confirmation and payment. What next?
Good news! Our February 2019 release included a packaged field called Subscription Seat Info. The field contains vital subscription seating information - and right next to the Contact information you need to create mail merged subscription renewal letters in Microsoft Word. In this article, we'll:
- Become acquainted with the Subscription Seat Info field
- Create a report of pending renewals and export it to Excel
Ready? Let's go!
Introducing the Subscription Seat Info field
The Subscription Seat Info field is a concatenated string of key subscription seating data separated by dashes. With it, you'll generate a report displaying one line per Contact - even if that Contact purchased more than one subscription - and including address data.
You'll end up with a report like this:
| Name | Informal Address Name | Subscription Seat Info |
|---|---|---|
| John Lewis | John and Jerry Lewis | Shakespeare Subscriber - 4 Show Package - Balcony - Adult - Balcony - Row:C - Seat:11 - 1 - $220.00 - $6.00; Shakespeare Subscriber - 4 Show Package - Balcony - Adult - Balcony - Row:C - Seat:12 - 1 - $220.00- $6.00 |
| Madeline Rusfelt | Ms. Madeline Rusfelt |
Poppin Pops! - Second Saturdays - Orchestra - Student - Orchestra - Row:M - Seat:1 - 1 - $180.00 - $6.00 |
This field is the most helpful if you've run the renewal tool to generate pending renewals for your fixed subscriptions.
It's important to know what information is conveyed in the Subscription Seat Info field; when you export your pending renewal report, you'll likely separate the information in this field into multiple columns in Excel.
Let's take a look at the example below and break down the information in the field. Here we have a subscription order with two seats.
- Subscription Name
- Subscription Package
- Allocation
- Price Level
- Section
- Row
- Seat
- Quantity
- Subscription Seat Price
- Per Package Fee
How to build the pending renewal subscription report
Now that you're familiar with the Subscription Seat Info field, you're ready to use it as you create a report of all pending renewals. Once you create the report, you'll export it to Excel and manipulate the data as needed for your renewal mail merge.
1. Go to the Reports tab
If you don't see the Reports tab in the main tab bar, click on the App Launcher to access all tabs.
2. Click "New Report"
3. Search for and select "Ticket Orders with Contact", and click "Continue"
4. Toggle to the Filters panel, change the Show Me tile to "All Ticket Orders", and click "Apply"
5. Add two filters to the report
- Subscription Seat Info not equal to [blank]
- Order Status equals Pending Renewal
If you need a primer on adding filters, check out this article about the subject.
Feel free to tweak the report filters if you want to run the report based on last year's subscription orders instead of pending renewal orders.
6. Now that we've added our filters, let's toggle back to the Outline tab
7. Search for and select the Subscription Seat Info field in the Columns section - this will add the field to the report!
8. Add more fields to the report as needed
This is a highly custom report; the fields you add depend on what information you need to merge into your subscription renewal mailings.
Generally, though, you'll probably want to at least add the Account name and mailing address fields.
9. When you're finished adding all the necessary fields to the report, click "Save"
10. Fill in the details:
- Report Name: Name your report something like, "Pending Renewals for Mail Merge"
- Report Unique Name: This field auto-populates based on the Report Name
- Report Description: Describe what the report shows and what it's for so you and your colleagues know why the report exists
- Click "Select Folder"
11. Choose a folder to put your report in and click "Select Folder"
Make sure you put the report in a public folder so your colleagues can access it!
12. Now, click "Save"
13. Click "Run"
14. Now we're ready to export the report to Excel - click on the arrow next to the "Edit" button and click "Export"
15. Choose "Details Only" and click "Export"
The report will download as an Excel file.
Now, you can manipulate the data in Excel as you see fit
If you need to split the information in the Subscription Seat Info field into different columns, you'll probably want to use the Text to Columns function in Excel! Here's a great tutorial directly from Microsoft Office.
1. Go to the Reports tab
If you don't see the Reports tab in the main tab bar, click on the App Launcher to access all tabs.
2. Click "New Report"
3. Search for and select "Ticket Orders with Ticket Order Items and Contact", and click "Continue"
4. Toggle to the Filters panel, change the Show Me tile to "All Ticket Orders", and click "Apply"
5. Add three filters to the report
- Order Status equals Pending Renewal
- Status not equal to Deleted
- Ticketable Event equals [exact name of your package]
If you need a primer on adding filters, check out this article about the subject.
Feel free to tweak the report filters if you want to run the report based on last year's subscription orders instead of pending renewal orders.
6. Now that we've added our filters, let's toggle back to the Outline tab
7. Add essential fields to the report
Make sure to add the following fields: Ticketable Event, Event Instance Name, Ticket Allocation Name, Ticket Price Level, Quantity, Unit Price, Unit Fee, Order Total.
8. Add more fields to the report as needed
This is a highly custom report; the fields you add depend on what information you need to merge into your subscription renewal mailings.
Generally, though, you'll probably want to at least add the Account Name and Contact mailing address fields.
9. When you're finished adding all the necessary fields to the report, click "Save"
10. Fill in the details:
- Report Name: Name your report something like, "Pending Renewals for Mail Merge"
- Report Unique Name: This field auto-populates based on the Report Name
- Report Description: Describe what the report shows and what it's for so you and your colleagues know why the report exists
- Click "Select Folder"
11. Choose a folder to put your report in and click "Select Folder"
Make sure you put the report in a public folder so your colleagues can access it!
12. Now, click "Save"
13. Click "Run"
14. Now we're ready to export the report to Excel - click on the arrow next to the "Edit" button and click "Export"
15. Choose "Details Only" and click "Export"
The report will download as an Excel file.
