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…
Within the main templates, you’ll see a link for “Customer Lifetime Value Report.” You’ll want to click that.
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.
Select the columns that you want to appear in the report.
Make sure that you include the following information:
- First Name
- Last Name
- 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.”
You’ll again want to select the columns of data that you want in your exported 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.
I then click to export the 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.
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))
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…
Then paste “special”…
You’ll paste values only back into Column J to remove the formula while keeping the values.
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.
Select all rows that consist of a blank cell in the J column and delete those rows.
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.
Select “Customer file with lifetime value.”
Upload the file that we created earlier.
Map data by selecting what each column represents that you want to upload.
Once Facebook is done uploading that data, click the “Create Lookalike” button.
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%.
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!