Friday, June 9, 2017

How to Find Customer Lifetime Value

Last week, I told you about a new way to target Facebook users similar to your most valuable customers with Value-Based Lookalike Audiences. In order to take advantage of this feature, you need to upload a complete customer list with a column for each customer’s lifetime value.

So the question ultimately arises: How do you find customer lifetime value?

To create such a file, you either need to be able to easily generate a report of lifetime value by customer or do so manually with the help of spreadsheet formula magic.

Let’s walk through both methods…

Find Customer Lifetime Value in Infusionsoft

I use Infusionsoft, so I’ll focus here.

I’m not an Infusionsoft expert, but I’ve fumbled through it for about four years. After poking around contact filters unsuccessfully, I reached out to Infusionsoft support and was directed to a very easy solution.

Select “Reports” under “E-Commerce” in the main Infusionsoft menu…

Infusionsoft Reports

Within the main templates, you’ll see a link for “Customer Lifetime Value Report.” You’ll want to click that.

Customer Lifetime Value Report

Under “Search Criteria,” you may want to set a minimum total purchased or paid of $1.00. I’d also recommend filtering to only include those purchases where no refund was issued.

Customer Lifetime Value Report

Select the columns that you want to appear in the report.

Customer Lifetime Value Report

Make sure that you include the following information:

  • First Name
  • Last Name
  • Email
  • Phone 1
  • City
  • State
  • Postal Code
  • Country
  • Total Paid

You can include other columns as well (like additional email addresses and phone numbers) if you collect it. This will help with your match rate when creating the Custom Audience.

Check the top checkbox to select everyone within your report and then click the “Actions” drop-down and select “Export.”

Customer Lifetime Value Report

You’ll again want to select the columns of data that you want in your exported report.

Customer Lifetime Value Report

We can now use this file to generate a Value-Based Lookalike Audience. We’ll get to that in a minute.

Find Customer Lifetime Value Manually

One issue that I have is that not all purchases have gone through Infusionsoft. I’ve also had purchases go directly through Stripe without the Infusionsoft integration.

As far as I can tell, Stripe doesn’t provide a simple report of customer lifetime value. But I can create one manually. Whether you use Stripe or something else, you should be able to do something similar as I do below.

Within “Payments,” I filtered to only include successful payments that were neither refunded nor disputed.

Customer Lifetime Value Report

I then click to export the report.

Customer Lifetime Value Report

This will be a full report of all purchases that have been made. You’ll first want to clean up your report to only include the data we isolated earlier when generating it in Infusionsoft. You won’t have a column for total paid either. We’ll need to find that.

Now we’ll need to aggregate all purchases made by the same customer. We’ll do that with an Excel formula to add up all purchases made under the same email address.

Sort by email address so that all purchases for the same email address are together.

Customer Lifetime Value Report

Let’s assume that the customer email address is in Column A and the product purchase price is in Column I. We add Total Purchase Price in Column J.

Assuming a header row, place the following formula in the J2 cell…

=IF(A2=A1,””,SUMIFS(I:I,A:A,A2))

Customer Lifetime Value Report

In other words, if the email address in this row is the same as the one in the row above, leave this cell blank. Otherwise, add up all values in Column I for this email address.

All rows with a blank cell in Column J won’t be needed. But first, we’ll need to copy Column J…

Customer Lifetime Value Report

Then paste “special”…

Customer Lifetime Value Report

You’ll paste values only back into Column J to remove the formula while keeping the values.

Customer Lifetime Value Report

If you don’t do this, the cell values will change when you remove blank rows.

Next sort by Column J to separate out the blank cells that won’t be needed.

NOTE: I realize there is probably an easier way to do this. I’ve been an Excel hack for many years and find my own — probably complicated — solutions to problems.

Customer Lifetime Value Report

Select all rows that consist of a blank cell in the J column and delete those rows.

Customer Lifetime Value Report

You will now have a clean file of customer lifetime values.

Create a Value-Based Lookalike Audience

Reminder: The Facebook advertising purpose for this is to create a Lookalike Audience where Facebook analyzes the lifetime values of your customers to find other users similar to those who are most valuable.

All of the details are provided in last week’s blog post, but since I now have this feature (and I didn’t when I wrote last week’s post), I’ll walk through it again here.

Before creating a Lookalike Audience, we’ll need to create a Custom Audience to be the source. We’re creating a Customer File Custom Audience.

Value-Based Lookalike Audience

Select “Customer file with lifetime value.”

Value-Based Lookalike Audience

Upload the file that we created earlier.

Value-Based Lookalike Audience

Map data by selecting what each column represents that you want to upload.

Value-Based Lookalike Audience

Once Facebook is done uploading that data, click the “Create Lookalike” button.

Value-Based Lookalike Audience

Select the country or countries that you want use as well as the size of the Lookalike Audience that will be generated. I tend to select the top 13 countries that represent my customers and focus on the top 1%.

Value-Based Lookalike Audience

It will take a few minutes to generate. Once the audience is ready, you can use it for targeting!

Your Turn

Since this is a new feature for me, I have only begun to test. Facebook recommends using this audience for bottom of the funnel targeting (product sales). I am going to experiment with it across multiple objectives.

Have you started using this feature yet? What results are you seeing?

Let me know in the comments below!

The post How to Find Customer Lifetime Value appeared first on Jon Loomer Digital.

No comments:

Post a Comment