Expired Beer Club Report

Monthly Beer Club Packs (Starter / Enthusiast / Crowd Pleaser)

On the 1st of each month a report is emailed to [email protected]. The subject line of this email is "List of Beer Club Subscriptions Expiring this Month". Follow these steps to clean the email and have list for each beer club type when packing and labeling. Each list is used to then apply a label on the box notifying the customer that it is their last beer club pack.

1. Open email and and check to see if the email has been "clipped" by Gmail. To do this scroll to the bottom of the email and see if there is the text "[Message clipped] View entire message". If there is this text click "View Entire Message". This will open a new tab from where the entire text of the email can be copied. Otherwise copy it from the email in Gmail. Make sure to copy over the column headers and all the data in the table.

2. Open Excel and paste in the cell A1. Select to paste the copied item over as Text, i.e. to remove any formatting.

3a. When pasted the column headers will not be aligned over the correct columns. Shift the text over by dragging and dropping the correct headers over the correct column content.

3b. Save the Excel file into the corresponding month. E.g. if email report is from the 1st February 2017 then save it in the February 2017 beer club folder. Name the folder "Expiring Beer Club Subscriptions - Monthly - DD-MM-YY"

4. Now delete the blank columns. These should be columns A, C, E, G and I

5. Adjust the each of the column widths so that they each fit the content nicely.

6. Place a light border around all of the data, including headers. This will make it easier to read when printed out as the eyes can more easily follow the information across the page when borders are used. To do this use the boarder drop down button in the top toolbar of Excel and select "All Borders"

7. Not insert "Filter" drop down boxes on the Row 1 Headers. Do this by selecting and highlighting the headers and then in the top toolbar click the "Sort & Filter" button and select "Filter". This will allow you to filter the data by each of the beer club types and print each set of expiring beer club packs for each beer club pack type.

8. Now print each set of expiring beer clubs. Using the Filter drop down box on above the "Subscription Pack" header select "The Starter". This will filter just for the Starter beer club pack type. Print using the settings of "Landscape" and "Fit Sheet on One Page" or "Fit All Columns on One Page" (depending on how many subscriptions are expiring it may need to go onto 2 pages.)

9. Repeat step 8 for Enthusiast and Crowd Pleaser packs.

10. Place each print out into their respective ring binder folder. This will be used once each pack type is labeled to know which ones to place the "This is your last pack" stickers on. And also cross reference the printed copies of the original order print out to ensure it is marked as being expired.

Quarterly (Expert Beer Pack)

The process is the same but the email report is only sent on the 1st of each quarter, i.e. January, April, July, October.

The name of the email subject line is "List of Beer Club Subscriptions Expiring this Month"

When saving the Quarterly pack expired subscriptions excel sheet into the relevant beer club folder, save it as "Expiring Beer Club Subscriptions - Quarterly - DD-MM-YY"