comment 0

How to Create Customer Lifetime Value as Custom Field in ActiveCampaign

We wanted customer lifetime value (LTV) in ActiveCampaign to be able to segment and differentiate subscribers based on what they’ve purchased. We give LTV a one to one equivalent to the dollar amount spent. A dollar spent on our products is LTV 1.

LTV will be recorded as a Custom Field in ActiveCampaign. (I talk about Custom Field v. Tag v. Segment v. List in this other post.)

Custom Field in ActiveCampaign

Before getting into calculating and inputting LTV, this is how Custom Field in ActiveCampaign works:

To create a Custom Field:

  1. Go to Lists tab > Manage Fields
    find_manage_fields
  2. Click on New Custom Fieldclick_new_custom_field
  3. Fill in the details in the pop-up, and continue onpopup

To note: Each time a new value is added/updated to the Custom Field (by importing and updating contacts, for example) the new value overrides the old. It is not cumulative, unfortunately. I’ll explain why this is such a bummer later on.

Now that I have the Custom Field: LTV ready, I’m ready to import and update contacts.

Orders Spreadsheet

Jeff created a Zap for turning each order received into an entry on a Google Spreadsheet. The Spreadsheet updates automatically when a new order comes in. We have 2 separate Spreadsheets for each of our products, Analytics Course and PPC Course. I’ll call them Product A and Product B for easy referencing.

Now, here is the tricky part. Why? Jeffalytics have 2 products, and some people bought both. As Custom Field doesn’t update cumulatively (but overrides old with new), I cannot upload List A and then List B. It will result in people who bought both having LTV=B, instead of LTV=A+B.

Here’s what I did to go around this: (Note: I couldn’t use the original Jeffalytics Spreadsheets here for demonstration purposes because it contains email addresses of customers)

  1. Download List A and List B from Google Spreadsheet. Open both up in Microsoft Excel.
  2. I used Email Address as identifier. So, I copied Email Address and Order Amount into a new tab. I have both A and B on the same tab. The order amount of a product varies, because of offers/ deals/ etc.excel_start
  3. In the image above, it’s rather easy to note that person #1 and #4 purchased both products, as they appear on both lists. But Jeffalytics had a much longer list than this demo and its not as visible.
    I seek help from my sister (who’s working life handles much more numbers and figures than myself) on how to compare the lists and find matches. I explained the situation and her immediate answer was INDEX/MATCH function in Excel.
    indexmatch

    index_match
    Source: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

    As a side note, there are 3 types of possible matches: 1 (Less than), 0 (Exact match), -1 (Greater than). For my purpose here, I’ll be using 0 to find exact match.

  4. The INDEX/MATCH function in action:in_actionThe INDEX/MATCH function says: Lookup for “example1@email.com” in column “A” and return a value from column “B”. The match has to be exact, denoted by “0”.If there is a match, there will be a value in column “G”. If no match is found, “#N/A” will appear.
  5. In IndexMatch column, there are 2 values (100 and 125) because there are 2 matches (2 person purchased both products). Because #N/A is undefined, using it in an equation will result to #N/A as well.
    So, I made another column MatchValue to reflect #N/A as 0. (This is not the most elegant solution, I have very limited Excel skills.) And also a column Sum, which totals up the Order Amount of people (of List B) if purchased both products, otherwise the Sum will be the Order Amount paid for B. Sum (or Order Amount) is LTV.sum
  6. The following step is again not the most elegant solution. I’m uploading List A (Email Address and Order Amount) first and then List B (Email Address with Sum), because the value in Sum will override Order Amount (in A) if there is a match.
    To illustrate, after uploading List A, example1@email.com will have LTV of 100. But when I upload List B, Sum of 280 will override the existing LTV of 100. LTV of example1@email.com will now be 280 (LTV=A+B).

Importing and Updating Exiting Contacts in ActiveCampaign

  1. Here’s what I do to import and update contacts:
    1. Go to Contacts tab > Importimport_contact
    2. Select Copy & Paste Your Contacts.copy_and_paste
    3. Copy and Paste the selected cells from Excel. Click Next.paste_contact
    4. Under Map into Field, choose Email Address and Custom Field Lifetime Value from the respective drop-down menus.choose_ltv
    5. Remember to check the box for Update existing contacts while importing. Click Import Now.update_contact

Did I do it correctly?

Final step is to spot check if this little experiment works. I randomly select and search for several email addresses. On the contact’s page, I check for the LifeTime Value under Personal Data.spotcheck

And now, the LTV as Custom Field on ActiveCampaign is ready.

If you would like to attempt this, here are a few things to note:

  1. Which list is choose to be match against the other list – does not matter. Even if there is a longer list and a shorter list. It doesn’t matter if the longer list is match against the shorter, or vice versa, the INDEX/MATCH function should be able to perform just fine.
  2. The order of uploading the lists does matter. The non-sum list should be uploaded first, and then followed by the sum list (with the cumulative value) . Otherwise, the whole effort of using INDEX/MATCH function becomes useless.
  3. Maintaining the Custom Field: LTV can be tedious. Each time you would like to update the field, you’ll need to repeat this whole process. Just because Custom Field is not cumulative.

I’m still searching for solutions on the last point mentioned.

Leave a Reply

Your email address will not be published. Required fields are marked *