Solved

Uploading and maintaining an offline opt-in list

  • 12 July 2023
  • 4 replies
  • 294 views

Badge +1

Hi. 

Our customers opt-in to email marketing via our app. There is no quick and easy way to integrate with Klaviyo.

Each week, I get an email list of all of our opt-ed in customers -- let’s call it “All Users”.

Unfortunately, the list is a lifetime list and there’s no way to sort by date. Thus each time I upload “All Users” it’s a lifetime list of all opt-in customers. I can’t distinguish new vs. old. 

Herein lies the problem:

Our old marketing assistant was manually uploading these lists and marking “Yes, update subscription status for all imported contacts to ‘subscribed’. His thought was that if someone was suppressed, they would stay suppressed.

My concern is that he was re-opt-ing in people who already unsubscribed every time he ported in the new list.

Our hope is that we didn’t need to turn this into a major forensic analysis project every single time we uploaded our user base of opt-ins. We certainly don’t want to resubscribe those who unsubscribed. However, we want to get all net-new opt-ins subscribed to our marketing efforts. 

 

icon

Best answer by bluesnapper 12 July 2023, 14:10

View original

4 replies

Userlevel 7
Badge +36

Hi @themichaelwilly 

Great question! If you import your 'All User' list which contains contacts who are in Klaviyo's supression list, and select "Yes, update subscription status..." any opt-outs will be updated to 'Subscribed' after the import. Selecting 'Yes...' is confirming that ALL the contacts in your import list have consented to marketing. 

There are two workarounds I can think of given your use case. And assuming unsubscribes are only ever captured via Klaviyo.

approach #1

  • Import your 'All Users' list with "Yes, update subscription status...". Your opt-in contacts AND any Klaviyo suppressed contacts in your list will be set to 'subscribed'
  • Export your Klaviyo suppression list then re-import it into your Klaviyo supression list. That will switch those orginally suppressed contacts from 'subscribed' back to 'unsubcribed'.

Worked OK for me but I suggest you test the above with some dummy profiles.

approach #2
Assuming you're using Excel. It is long-winded! However, it will work and cleans the list before import; there may be a more elegant approach, possibly not using Excel.

  • Dedupe your 'All User' list just to make sure it's clean
  • Export Klavyo's suppression list of emails and add those emails to the bottom of your 'All User' list in the email column. Add a column 'opt-in' and a value of 'suppressed' for all those Klaviyo suppressed contacts you've added just so you can identify them more easily later.
  • Add a new column called 'duplicates' and in that column add the formula =COUNTIF($A$2:$A$100, A2) but replace $A$2:$A$100 with a range that includes ALL your records including suppressed. The A2 assumes your emails are in column A and the first email record is in cell A2. Then copy that formula in the duplicates column for all your records. Your duplicates column will now have cells with a value of 2 where there are email duplicates i.e the first instance of an email plus its duplicate (the suppressed).
  • Add a filter to your spreadsheet and filter your duplicates column to only display those records with 2. Delete these. This process will remove contacts in your 'ALL Users' AND their duplicates (in the Klaviyo suppression list).
  • To ensure there aren't any Klaviyo supressions still remaining in your list. Filter the 'opt-in' column you created earlier and delete any records 'suppressed'
  • Final step (phew!) is to import your cleaned 'All User' opt-in list with "Yes, update subscription status..."

As with approach #1, test first!

​Hope that helps!

Andy

Badge +1

First off, thanks for your response.

With approach 1, I would need to do the second bullet before the first, right? Because if I uploaded the “All User” list they would become unsuppressed. 

With approach 2, this is exactly what I was hoping not to have to do. :-( 

Seems like there needs to be a better way to manage a situation like this. Or a way to upload “net” new contacts and not change the profile status of those who have already unsubscribed. 

Wish there was a less cumbersome idea.

Userlevel 7
Badge +60

Hey @themichaelwilly,

With approach 1, yes, you would want to export your suppression list first to retain a copy of it. 

If you already collected explicit consent from these users already, I don’t see why you would need to import the list using the Yes, update subscription status for all imported contacts to subscribed option. 

Using the no, import without updating subscription status will not change the subscription status of profiles that were previously suppressed. However, those newly added subscribers will show a yellow consent status in their profile.

Have you also explored just trying to import those contacts/subscribers to Klaviyo via our API? You don’t need to fully establish an integration from your custom platform to Klaviyo to use the API to subscribe users to a Klaviyo list. For more resources on this, I would suggest taking a look through our Developer Portal which you can find guides and our API reference. 

David

Userlevel 7
Badge +44

It also sounds like you would need to sync all unsubscribes back to the app as well to make sure it stays consistent on both sides

Reply