MAMS: ROI Chart Procedure
FIRST ATTEMPT: Should be part of their launch
RECURRING ATTEMPTS: Every year on the anniversary of their live date
OLDER ACCOUNTS: Every year on the anniversary of their “ROI Sent” date (you may switch this to their live date at anytime you wish)
Carleen will track these dates on the App Health Report. There is a column for “ROI Updated” and “ROI Sent”. Below are instructions on how/when to cc her on each different situation.
MENUFY NOTE: If your merchant has/had Menufy data in their ROI chart, please keep that data in the chart, but note that the data ended 10/1/22 – “Data ended 10/1/22”
HOW TO KNOW WHEN TO CREATE ONE:
Check their launch date, how many punches punched and coupons redeemed, as well as amount of money spent. You can use the App health report as a guide, since the data is right there and look at ESP.
For example Dominion Pizza, they have been live nearly 7 years and have less than 40 punches and 180 coupons, and according to ESP they pay us around $3,000 a year. I would then check if they had Menufy or Zuppler, which they don’t so we can’t provide them OO numbers. So those 40 punches and 180 coupons will not cover the $21,000 they have paid us so there is no point in even starting a chart.
An example of a new account would be La Abuela who launched a year and a half ago prepandemic and have 0 punches and 1 coupon redemption. They also don’t have Menufy or Zuppler. And according to ESP they have paid us nearly $5,000 because they are a App+Email+Website trifecta. Which is a shame and my guess would be is that they downgrade at some point by canceling the App since they don’t use it. But these are the things that you can look at and maybe spur the merchant to start using it again before it is too late to save.
HOW TO CREATE ONE:
MONTHLY FEES:
Open Excel file of the account you are working on
Go to the Monthly Fees Tab at the bottom
Create a column for the current year if it’s not already added
Copy/Paste to add the sum formula to new cell you just added
Go to Appflow for the account, go to the ESP link from there
Under your TLS-CSC ESP Profile, Copy Customer Dollars Spent, and paste into the excel file
Copy the total # and paste it into the TLS monthly fee field in the ROI Chart tab
PUNCH CARDS:
Go to CMS for that account
Go to the Reports Dashboard
Click on Punches Button, Click on Punches, and search the title of each Punch Card
(To find the true title of a punch card, open the punch card and find it in the title field)
Take Total # of Punches for each particular card and update in each appropriate field in the ROI Chart excel file
Go back to the Reports Dashboard
Click on the Punches Button, Click on the Redemptions, and search the title of each Punch Card
Take Total # of Redemptions for each particular card and update in each appropriate field in the ROI Chart excel file
FOR TLS OO:
Open the TLS Online Ordering Report created by Mandi
Filter by Business Name, Search account name
Copy the whole thing and paste into OO tab in excel
Copy/paste Orders Total (from live) and Revenue Total (from live) into ROI Chart excel file
FOR MENUFY:
Open the Menufy Report (created by Mandi)
Filter by Business Name, Search account name
Copy the whole thing and paste into OO tab in excel
Copy/paste the Orders Total (from live) and Payout Total (from live) into the ROI Chart excel file
COUPONS:
Go to Coupons tab in excel, sort by end date to see most recent coupon date.
Go to CMS, go to Report Dashboard, go to the Coupons button, and sort by end date (most recent)
Copy/paste all new coupons since the last end date into the Coupons tab in the excel file UNDER the sum formula row.
Cut the sum formula row and paste it back in under the newly pasted coupons
Delete any blank/unnecessary rows
Copy/Paste bottom Total and Revenue cells down to bottom to copy/paste the formulas into the new coupon cells.
Update the sum formula at the bottom to go from Cell #XX to the new cell #XX (however many more you added)
Sort by Description
Determine and Insert Cost and Discounts for each new coupon (look at OO Ticket average if needed)
Copy the Total # of Coupon Revenue and copy/paste into the ROI chart excel file
POWER POINT TUTORIAL:
TIPS & TRICKS:
- If the Reports Dashboard will not load lifetime data because it is too much, you will need to load it in increments and add
- When you are creating a NEW chart from scratch, instead of naming the Download Incentive “Download Incentive” Matt would usually write “DI: $5 OFF Any Purchase of $20 or More” or whatever the actual offer was. The “DI” helps us know it was the download incentive but the merchant is going to understand better if we just list out the actual offer.
- Utilize the cells next to the punch card row to type in notes for yourself as to what that punch card is actually called so you can search it properly and why you chose the values you did if you have to come up with one on your own. This will help you in the future remembering why you did what you did and help you explain it if a merchant has questions
- Utilize the cells next to the coupon rows on the coupons tab to type in notes for yourself as to why you chose the values you did if you have to come up with them on your own. This will help you in the future remembering why you did what you did and help you explain it if a merchant has questions
- If you think there was a punch card that was previously edited and changed from one value to another at some point in time (but remained on the same punch card) you can utilize the logs to try and determine the history. You can see who changed a punch card and when and if you click on the info tab you can see what was changed. Just a note incase you need to do any digging or make notes about it.
- If you want, you can add the verbiage “Live Date: X/X/XX” next to the business name/acct # on the chart. I find that from the App Health Report and found it useful to add it into the chart.
TEMPLATE FOR SENDING THE ROI CHART TO THE REP & MERCHANT:
The below is the email template to use when you are sending the finished ROI charts to your merchants. Please make sure to keep the rep in the “to” field along with the merchant and address them in the email with the merchant as well. Please make sure that you are taking a screenshot of the ROI chart and placing it in the body of the email. Do not attach it.
To: Merchant, Rep
CC: Sales Asst, Carleen Brieck, Emily McCardell, TLS Milestones
Subject Line: Your TLS Program(s) Return on Investment: Business Name (Acct#)
Merchant & Rep,
Here at Total Loyalty Solutions we value our clients and strive to build relationships based on positive results. That is why we are excited to share a deeper insight into how your TLS marketing program has created a return on investment for your business.
Please see the information below on how well your Loyalty Punch Cards, Coupons, and/or Online Ordering has done for you ever since going live. This is a true look into just how much value your TLS program brings to your business.
If you have any questions or concerns, please reach out. I am here to help you accomplish your digital marketing goals with our programs and guide you to further success!
TEMPLATE FOR UPDATING ROI CHART BUT NOT SENDING IT TO THE MERCHANT:
Sometimes there will be occasions where you update ROI charts every year but do not send them to the merchants due to poor ROI. You still need to update Carleen that you updated the chart even if you did not send it. Please use the below template for that.
To: Carleen
CC: Emily, TLS Milestones
Subject Line: ROI Updated (NOT Sent)
Date Updated: X/X/XX
Business Name (Acct#)
[IMAGE OF ROI CHART]
Date Updated: X/X/XX
Business Name (Acct#)
[IMAGE OF ROI CHART]
Date Updated: X/X/XX
Business Name (Acct#)
[IMAGE OF ROI CHART]
(so on and so forth as needed)
It is important that the subject line and body of the email follow this template exactly.