Analytics Calculations: Customer Contact (Multiple IF Statements 2.3)

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator
*Note, in this Discussion, we will assume that you're now comfortable with a few points of Calculations. If you're in the early stages of your Analytics mastery, you may want to take a quick look at the related Discussions:
Multiplying by percentages in Analytics
Using IF functions

IF statements can check one set of data for different criteria, and can also look at different sets of data.

Say for example we're planning on hosting a party at our shop to encourage customer loyalty, and we are using the event to target two groups of customers: customers that have spent more than $10,000, or customers who have not been in the shop in more than a year.

To start, let's pull up the Customer Lifetime Value report (please note that this is a Marketing report that may not be available on every subscription of Analytics. The steps of preparing the calculation are the same though on every other report. If it sounds like the Marketing reports are something you want to add to your account, speak with one of our reps to inquire about adding it onto your account)

So, we have our Customer Lifetime Value report, showing us Names, the total dollar amount each customer has spent in the store, the day count since their last purchase, and so on...

Remember that we want to build our calculation to look something like this:

if( Criteria 1 , result if Criteria 1 true , if( Criteria 2 , result if Criteria 2 true , result if both Criteria 1 and Criteria 2 are false ))

So in this case, our calculation will look something like this:

if( Customer has spent more than $10,000 , they get invited to our party , if( Customer has not been in the shop in more than 365 days , they get invited to our party , well this is awkward but we'll invite them to something else ))

So let's click the grey "Calculations" button to open up our Table Calculations, and start our calculation looking at the Historic Customer Lifetime Value...

...and then setting the result if true.

Then we add our second IF function, looking at the days since last purchase...

...along with the results if the second set of criteria is true.

And finally, the results if neither are true, (for now, let's define this as Null)

Let's also give this a more meaningful title...

So if I have saved this properly...

We see that it is working for the first set of criteria, what about the second? Well, if we keep scrolling down...

So if our row count is complete, then we can sort by our calculated field too...

...showing us a complete list of invitees for our event!

*You could turbo charge this report by adding different descriptions to the criteria of guest, by having Analytics present only customers that are on the guest list and nullify anyone else from the report. We could also set tiers of contacts that we want to target, adding "AND" arguments with our criteria to identify cross-sections of customers (perhaps prioritize customers who spent $10,000 recently, then customers who spent $10,000 a long time ago, and so on)
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.