May 30, 2017

Groupon Case Study

I have real world numbers from a GroupOn campaign to share with the world! This in combination with my very sexy equation from a previous post and my newly added, downloadable GroupOn Excel calculator allows us to get all kinds of insight.

The businesses I know that have participated in these campaigns have seemed pretty happy with the results, but none of them tracked their numbers. That meant I had to make some assumptions, but no longer.

Case Study

Here are the metrics from a real GroupOn campaign. I would expect that results will vary from business to businesss and especially between different industries. If you need to better understand these numbers, read the previous post.

  • Business Type: Very Casual Dining
  • Business Age: Fairly new business
  • GroupOn offer: $20 certificate for $10
  • GroupOn’s commission: 50%
  • Total Sold: 1,225
  • Redemption Rate: 68% (32% were never cashed in)
  • Average Ticket Amount (with certificate use): $20 (people spent pretty close to the face value amount)
  • Estimated number of new, recurring customers: 75%. This is expressed as a % of the total number of certificates sold. These are people that will come back even without a certificate.
  • Estimated Long term, recurring customer income: $20. Without a certificate, the average ticket is about $10. This means that it is assumed the new, recurring customers mentioned are expected to come in about 4 times over the “long term”
  • Normal Margin on sales: I did a loose calculation because I knew the net result… 230% margin or COGS is about 30% ticket price.
  • Brand Value: $0. This is such a fuzzy number, that I left it as zero.

The real world result is that this business owner was paid $6,000 with costs around $5,000. According to the super-awesome spreadsheet – using the numbers above – the analysis shows:

  • Campaign Profit: $1,076.52
  • Total Long Term Profit: $13,883.33
  • ROI over the “Long Term”: 130.77%

Not too shabby.

Footnotes

There are some notes worth mentioning:

  • The business was paid by GroupOn in three, equal payments over 1-2 months. They were happy with this.
  • Purchases with certificates with Tickets less than $20 don’t get change. Typically, they would order something else to get over the $20 level.
  • Redemption volume was heavy in the first and last month, the numbers were:
    • Month 1 – 225
    • Month 2 – 150
    • Month 3 – 100
    • Month 4 – 50
    • Month 5 – 50
    • Month 6 – 250
  • The number of new, recurring customers is very difficult to determine and is basically a gut-check. Unfortunately, it is largely responsible for the determination of the Long Term Profit… so be careful how you use it.
  • I assumed “Long Term” meant over the course of a year when determining some of the more “magical” numbers. You can assume whatever you want, but make sure you understand what it all means.
  • The calculator is intended to be functional, not fancy. It’s pretty basic, but still very, very sexy.

GroupOn ROI – the Math

UPDATE 7/16/10: I’ve added an Excel calculator to make it easier to determine how GroupOn might work for you. It’s very simple, but uses all the logic described in this post.

UPDATE 7/16/2010 – I’ve also added a new post with real world numbers with a Groupon Case Study.

How do you know if GroupOn is right for you? I like numbers quite a bit and became obsessed with this, so I’ve put together an ROI calculation to try to answer this question. This post includes the calculation I came up with towards the beginning and the detailed breakdown of what everything means for any masochists that like that sort of thing.

At a very high level, the GroupOn pitch seems questionable. They want you to sell a certificate through GroupOn (say $30) for a deep discount (say $15) and they are going to take a percent of the income (say 50% or $7.50). In this example, you are getting 25% of what you would normally. Why would anyone do this?

  • New Patrons – This is the biggest reason. If you can get new patrons into your organization with a deep discount and they keep coming back and spending big bucks, there is a lifelong value.
  • Cash flow - You get paid the day after the promotion which can help out if you need to buy that new oven or make payroll. Since the certificate is valid until the expiration date, you can expect the loss to be spread out over a period of time – although most of it will probably be pretty soon.
  • Unredeemed certificates - there will be some people that pay for the deal, but never actually use the certificate. That’s just free money.
  • Average ticket value exceeds offer -If you limit the offer to one certificate per table (for example),  what if they bring friends and buy a boat load of food – only getting a deal on the first $30? Nice.

There is one additional cost to also consider, though.

  • Brand value. How do you think offering this type of outrageous deal will affect your image? You might decide this is positive, negative or not a concern.

Define your numbers

So should you do it? Let’s ignore the immediate Cash Flow benefit and make some assumptions.

  • Offer Amount (O) – you are going to offer a $30 certificate and only allow one  to be redeemed per table
  • Sell Amount (S) – the purchase price for these certificates will be $15
  • GroupOn’s Cut (G) – GroupOn’s cut will be 50% of the sell price… for every $15 certificate, they get $7.50 and you get $7.50
  • Margin (M) – your margin is 50%, i.e., if it costs you $20 to make something, you are going to sell it for $30.
  • Average Ticket (A) – the average ticket of people using the offer will be $50
  • Redeemed Certificates (R) – only 80% of certificates will be redeemed
  • Total sold (T) - you expect to sell 500 of these
  • New patron percentage (N) – you expect 40% of the people taking advantage of the offer to be new patrons (the remainder are people that are already patrons). This is a number that you pretty much have to make up, but 40% seems pretty good.
  • New Patron Lifetime Value (L) – We’ll say the average patron will spend $150 with you (beyond the offer) over a period of 1 year (if you choose 2 years, 3 years, etc, your ROI will be spread over that period). Determining this value is a science on its own so good luck coming up with the right number.  Taken in conjunction with your Margin, this can be used to determine profit from your average patron.
  • Brand Value (V) – Some organizations worry that offering such deep discounts will damage their brand and make them look “desperate.” Others might think there is an overall brand benefit that is not captured in the lifetime patron value. This variable is intended to capture this “fuzzy” influence and represents how the promotion will affect revenue over a period of 1 year (if you choose 2 years, 3 years, etc, your ROI will be spread over that period) SEPARATE FROM INCOME RELATED TO NEW PATRONS. If you think it will increase revenue over time, choose a positive number, but since I have heard concerns about the negative effects, I will choose a negative number and that I am making up – -$4,500.

GroupOn ROI Calculation

Here it is… the total long term profit you can expect from this promotion is:

Total initial income … + … Loss from certificate redemption … + … New patron Benefit … + … Brand Damage

T*(S-G*S) …. + …. R*T*[(A-O)-(A-O)/(1+M) - O/(1+M)] … + … T*N*(L-L/(1+M) … + … (V-V/(1+M)) = PROFIT

or ….

500 * ($15 – .5*$15) … + … .8*500*[($50-$30)-($50-$30)/(1+.5)] … + … 500*.4*($150-$150/(1+.5) … + … (-$4,500-(-$4,500)/(1+.5)) = $3,750 … – … $5,332 … + … $10,000 … – … $1,500  = $6,918

For the purists out there, this is not technically ROI, which is defined as (gain from investment – cost of investment) / (cost of investment). Fine.

Cost of Investment

The cost of investment is:

What it costs you to redeem certificates … + … what it costs you to fulfill purchases beyond the limits of the offering … + … your cost of fulfilling lifelong purchases … + … brand value

R*T*(O/1+M) … + … R*T*(A-O)/(1+M) … + … T*N*(L/(1+M) … + … V

or …

.8*500*($30/(1+.5) … + … .8*500*($50-$30)/(1+.5) … + … 500*.4*($150/(1+.5)) … + … $4,500 = $37,833

NOTE: You should move the brand value to Gain calculation if you think it will be positive.

Gain from Investment

The gain can be determined as:

Initial revenue from offer … + …. income from sells beyond the limits of the offering … + … income from life long purchases

T*(S-G*S) … + … R*T*(A-O) … + … T*N*L

or …

500*($15-.5*$15) … + … + .8*500*($50-$30) … + … 500*.4*$150 = $41,750

Official ROI

ROI = ($41,750 – $37,833)/($37,833) = 10.35%

KEEP IN MIND THAT THIS ROI IS FOR THE PERIOD YOUR CONSIDERING FOR BRAND VALUE AND PATRON LIFETIME (if you’ve entered a value assuming 1 year, 2 years, 3 years, etc, this is the ROI for that period).

Also keep in mind that these are hypothetical numbers. Your numbers will most likely be different.

Calculation details for Masochists

TOTAL INITIAL INCOME

How much money do you receive from GroupOn the day after the sell? This is the total number of certificates sold minus GroupOn’s cut, or:

T*(S-G*S) = 500 * ($15 – $15*.5) = $3,750

AMOUNT OF LOSS BASED ON REDEMPTION OF CERTIFICATES

For every certificate redeemed, you are going to have to give away a certain amount of inventory, service, etc (O=$30), but the cost to you will be less than the certificate amount because of your margin:

O/(1+M) => $30/(1.5)=> $20 (what it costs you to redeem a certificate)

You also will be able to make a profit on the average ticket amount above the offer amount, or the total income above this amount minus the cost:

(A-O) – (A-O)/(1+M) => ($50-$30) – ($50-$30)/(1.5) => $20-$13.33 = $6.67 (additional profit per redeemed certificate based on assumed Average Ticket)

So for each certificate redeemed, your profit is most likely negative:

(A-O)-(A-O)/(1+M) – O/(1+M) = $6.67 – $20 =  – $13.33

But some people will not redeem their certificates, which means free money for you! Taking into account the total number of certificates sold (T=500) and the redemption rate (R=.8) leads to an updated equation of:

R*T*[(A-O)-(A-O)/(1+M) - O/(1+M)] => .8*500*[($50-$30) - ($50-$30)/(1+.5) - $30/(1+.5)] = -$5,332.

So at this point, you have gained initial income of $3,750 and, over the course of several months, it is forecasted to cost you $5,332 leading to a net loss of $1,582… but there are some additional potential benefits.

NEW PATRON BENEFIT

We’ve talked about the obvious income and costs, but there are other benefits. The idea of paying for a print ad seems pretty reasonable because it might attract new patrons and you’re willing to pay money for it, so why not GroupOn? It is advertising and seems pretty effective at getting people to show up.

You’ve made an assumption about how much the average new patron will spend at your organization over their lifetime (L=$100). So the amount of profit you expect to make from the average new patron, after you take into account the costs associated with the transactions is:

L-L/(1+M) => $150 – $150/(1+.5) = $50

When taking into account the promotion by GroupOn, people sharing the information with friends, buyers bringing friends with them to redeem certificates, etc., you expect a certain number of new patrons tied to the number of certificates sold (T=500) and a New Patron Percentage (N=.4). This results in an expected increase of profit of:

T*N*(L-L/(1+M) => 500*.4*($150-$150/(1+.5)) = $10,000

There are 3 major things that should be noted:

  1. The lifelong value of a customer is hard to determine and we kind of made up the actual number. We also made up how we calculated the number of new patrons your promotions will generate.
  2. It might or might not take into account the viral nature of new patrons bringing along friends and introducing them to your organization. This is potentially a chain reaction that can greatly amplify this number.
  3. Lifelong value is something that can build up over a very long time. This is not a quick income event and will likely be spread over a period of years.

With that being said, the running tally of the GroupOn benefit is now at a Net Profit (over time) of $10,000 – $1,582 = $8,418 in profits.

BRAND DAMAGE

Do these deep discounts damage a brand’s image and lead to reduced sales? I don’t know, but we’ve assumed it does and have pulled out of thin air a number to represent the decreased regular income from running this promotion (V=$4,500). Since this is “top line revenue,” to understand the impact on the bottom line, we need to consider your Margin.

(V-V/(1+M))=> (-$4,500 – (-$4,500)/(1.5))=-$1,500

Additional Comments

This was a lot of work so please let me know if I made any mistakes. Also, if anyone wants to build a calculator to run these numbers, let me know!

Trackur – Reputation Monitor Overview

Trackur is yet another social listening / reputation monitoring tool and this video provides a brief overview of what it’s all about. Overall, it’s not too bad, but I don’t think it is the great, low-cost solution I would still like to find.

What I like about Trackur:

  • It seems to do a good job of pulling tweets, which other free solutions seem to struggle with (for some odd reason).
  • The overall layout is easily navigable and easy to understand.
  • Trackur includes search of “media” sites, most notably YouTube.
  • It also includes search of blogs and online new articles… seems to do a decent job of that as well.
  • You can save searches for quick launch.
  • It automatically refreshes results every 30 minutes.
  • You can subscribe to saved search results through RSS … and email, but the RSS is super cool.
  • It indicates that sentiment is automatically determined, although my experience showed that all results came through as neutral. Maybe this is something they are still working on. I was corrected on this point. Sentiment is NOT automatically determined, but Trackur does allow the user to manually determine and specify this. Based on the effectiveness of other sentiment tools I’ve seen, this is probably better anyway.
  • The user can update the sentiment setting. This allows the user to override any automated calculation.
  • The basic package cost is inexpensive – about $18/month – and there is a 14 day free trial so you know what you are getting.
  • You can exclude specific records so they don’t show up going forward. (one of the bullet points in my next section was inaccurate on this point and has been corrected).

What I don’t like about Trackur:

  • The search filters are very basic. It doesn’t look like you can use more advanced search strings like “multiple words” OR “other words” What’s up with that.
  • Because of the limitations of the search criteria, there is a lot of garbage in the result set and no obvious way to remove irrelevant records. This shortcoming is compounded when you consider these results are sent to users subscribing to RSS and email notifications. I was corrected by the CEO… there is a red “x” the the right of the “Source” of each post that allows you to filter on a record by record basis. However, it looks like if you delete a record from a saved search, that record will be exclude from all future searches other than the saved search. From a practical standpoint about how this tool might be used, that probably isn’t a big deal, but it might be an issue in some cases.
  • There are some basic usability issues that are frustrating. If you play around with it, you’ll see what I mean.

What I’m on the fence about:

  • In order to be able to export results, the package price jumps to $88/month.

Trackur claims you can set up your first search within 60 seconds and I think that is actually pretty reasonable. If you are looking for a low cost solution, I would recommend you at least check it out and see what you think.

Scout Labs Overview

Scout Labs is a social listening tool that can monitor what people are saying about you and your organization. By defining searches, you can browse related:

It also tries to assign sentiment to these posts so you can gauge not only where people are posting, but if the posts are positive, negative or neutral. Additionally, you can share your workspace with your team (IT, PR, management, etc.) and manage team discussions.

While the initial value is monitoring social media buzz, Scout Labs’ tools make it easy to drill down to the source of the posts and engage authors directly to help build relationships.

Unfortunately, it’s not free, but packages begin at $99/month and a 30-day free trial. If the free sites like Crowd Eye and Social Mention aren’t meeting your needs, it might be worth checking out.

Crowdeye Overview

More and more solutions are emerging that try to mine useful information from social media outlets like Twitter. Crowdeye bills itself as a search engine for Twitter and they differentiate themselves by assigning reputations to posters as well as automatically generating a keyword cloud that can be used to filter tweets. Watch the demo video:

Overall, I think that Crowdeye has some interesting features, but still needs some work to be worthwhile. They are worth checking out just to get an idea of what is possible and I’m sure there are new features on the way.

Social Mention Overview

In my previous post on Tweetdeck, I showed how your brand can be monitored based on tweets, but there are a lot of other social media outlets that might be referencing you – blogs, photo uploads, videos, etc. Monitoring this information is referred to as Social Listening or Reputation Management. Social Mention is a free tool that scans a lot of these outlets and returns a list about where you are mentioned. It then goes one step further by trying to associate sentiment to these posts – are people saying nice things, bad things, or neutral things? While the solution has some consistency issues, it does provide some insight and it’s free. Watch the video to get some more info.

The things I like about Social Mention are:

  • It’s free
  • It provides some metrics like passion and reach in an attempt to provide some analytics about your social media reputation
  • You can drill down into result sets
  • You can set up email alerts
  • For data fiends, you can export information into a spreadsheet for further analytics
  • There is also an API that allows you to develop applications around the Social Mention Engine

There are other tools out there that might be better, but they cost money. At the very least, you should check out Social Mention and see if it helps you manage your brand.

Tweetdeck Explained

In a previous post, I mentioned how Twitter can be used by organizations to reach your target audience. But Twitter itself is an unorganized mess. There are tools that integrate with Twitter, however, that make it much more manageable and one of them is Tweetdeck. Watch this quick overview to learn how to add sanity to the information overload.

The demo covers how to:

  • Group people you follow into categories that mean something to you (Friends, Business, Organizations, etc.)
  • Integrate Tweetdeck with Facebook
  • Define searches that will keep you informed about what people are saying about you in the “twittersphere”
  • Even translate tweets from foreign languages

Although it’s not in the presentation, it is also easy to add filters for Direct Messages and Mentions (of your twitter ID).

Let me know any tools you might be using to help manage the noise!