Customer Targeting for CRM Marketing Campaign


I need help or ideas to solve the below business challenge. Sample questions has been provided. A snapshot of the sample data has been attached below:


Posted 2018-12-16T20:00:25.053

Reputation: 13

Question was closed 2018-12-19T14:35:56.073



Can you share all available columns in the data set? its hard to tell what data is there to use. Given the data you show, I would have started with some explaratory analysis: Group data per shops, check user activity ( how much purchases they do per month/week, what is AOV, LTV, etc). Idealy, its good to use email marketing, because its cheaper that paid ads and you have list of users that can be mailed - given the low costs of email marketing campaigns, you can basicaly target all users that stopped shopping in given places and allowed you to send them mails.

Additional approach: you can use paid ads for remarketing and geo-based ads to recover lost clients - given the fact that you want to cut the costs, its might be beneficial to retain only loyal customers ( which have more that 2 or 3 transactions, this number should be based on your average sales per client).

So, answering the questions:

  1. What are your recommendations for the campaign? Should we do it? How should we target and why? Should we do it?

Select all users that were lost after opening new shop and calculate lost income per month (or week, based on your data) - you want to know how much money you can expect from recovered users, this can be your marketing spend baseline ( Recoverd Income - Costs - Margin ~ Marketing Budget). Marketing Budget + Recoverd Income will indicate if its logical to run the campaign.

What are your recommendations for the campaign?

I would start with email - cheap and effective, we can use targeting in emails based on what items users buy, their gender and age. If you have segment of loyal users with high income - you can develop dedicated email campaign based on their needs, give bigger discounts ( its good to know why they left in the first place).

  1. Is there any additional customer information that would help you? What is it? How would it help?

Any data that will help to better segment users will be usefull - age, gender, social status, all this info can influence email campaigs. Also it would be good to have data about costs and effectivness of Paid Remarketing Campaigns - based of this data we can try to use remarketing for our loyal customers.

  1. Have both stores been impacted similarly? How has customer shopping behavior been affected?

Split data per shops, calculate statistics, do some good-looking and easy to undestand graphs, cant say more without actual data.

Hope this help!


Thanks for sharing data, here is my opinion about this task:

You can split data before-after 25 of may, and target users that have losses in sales comparing to period before 25 of may.

Based on Customer_Data dataset, it looks like you have user that can be mailed and emailed, so you can launch 2 campaigns for different users based on Mailable_flag / Emailable_flag flags.

Talking about losses: Based on given data is looks like your shops lost around 3k in sales and ~60 transcations for both shops after 25 of may and the losses are equal amont users that can and connot be emailed.

This leads us to a point of "additional" data - ideally you want to know the past performance of mailed and emailed campaigns to adjust the targeting.

Also the thing I havent done - you should calculate LTV ( basicaly how much money the users brings you per month) - if some users have very low LTV, you dont want to spend money advertasing them.

I`ve attached ipython notebook with brief workflow for you to check . Please, be aware that its just my ideas and they cant be totally wrong :)

Yaroslaw Homenko

Posted 2018-12-16T20:00:25.053

Reputation: 391

Thanks Yaroslaw. Apparently this is all the columns that I have been provided. the transaction data starts from 05 Apr 2016 and goes till 19 Jul 2016. Complete list of customers is shown in the second image that I have attached. Is there a way I could attach the complete data sets here? – spv92 – 2018-12-17T16:16:47.340

I have been comparing the no of purchase made in the two stores for the period before may 25 and after may 25, instead of comparing them weekly/monthly. Is it a good approach? Because the data starts from Apr 5 and ends on July 19.. May 25th lies in the middle dividing the data set into exactly 2 equal periods (i.e, 8 weeks). – spv92 – 2018-12-17T16:48:14.693

@s I guess you can load data to google drive and share the link. If data seperated evenly you can compare before ~ after of 25 of may, but maybe there is some daily/weekly trend in purchases which may influent your marketing campaigns? Trends are quite common in ecommerce, so its worth checking it. Try to select all users who stopped purchasing after new shop opened and calculate lost revenue to adjust budget for marketing. With this data you cant do much, so exploratory analysis and basic ideas about marketing should be enough for the first part. – Yaroslaw Homenko – 2018-12-18T08:24:11.010

It would be really great if you could do some visualizations with the data and share your insights. I am new to the retail domain and willing to learn as much as i can. Thanks – spv92 – 2018-12-18T09:03:42.327

@spv92 I`ve edited the post – Yaroslaw Homenko – 2018-12-18T12:42:00.037

Thank you very much. That really helps! I have 3 questions 1. Why did you consider only transaction loss to target the customers? why not other metrics like sales loss, average transaction value loss? 2. In the last step, you have calculated budget as total sales loss for the targeted customers. what does this mean? 3. For calculating LTV, we only have less than 3 months of data. Can you please explain how the calculation works? Again why cant we use other metrics for this? how do we identify which metric to choose? – spv92 – 2018-12-18T23:29:17.260

@spv92 1. You can use sales or transactions, there is no mandatory rule for it ( or i dont know about it) - transactions and sales often well correlate. given the average basket is stable across transactions. From my point of view "transactions" is a more stable metric and not influenced by outliers ( rare order swith huge revenue). – Yaroslaw Homenko – 2018-12-19T09:07:42.190

2: Its rude aproximation - if we have loss due to competition, then recovering the clients will brings us the lost revenue = its not 100% accurate, just an approximation for budget, but usually its good to have at least some numbers to operate when you deal with marketing. Ideally, you want to know the cost of acquisition per user to adjust the budget. – Yaroslaw Homenko – 2018-12-19T09:15:30.990


  • given this subset of data LTV is not that good, I agree, but you still want to know the average income from 1 user per month. You can use this value to say " If we spend X money to bring and retain this user, it will bring us Y income per year" or similar. To calculate LTV you need to calculate Income ( transactions * sales) and divide by number of month of users lifetime.
  • how do we identify which metric to choose? - it depends on your business, usually you want to focus on 1-2 macro metrics ( revenue, CR, LTV) and several micro-metrics (depends on your bussiness model) – Yaroslaw Homenko – 2018-12-19T09:17:44.953

    Thanks a lot! So the LTV for a customer 'A', say for month of May is (transactions made by A in may * total sales of 'A' for May)/(no of months the customer has been shopping with the store). Is that correct? – spv92 – 2018-12-19T15:57:51.723

    @spv92 LTV: total transactions made by A i* total sales of 'A' )/(no of months the customer has been shopping with the store. - it one (easy and aprroxomate) way to calculate LTV, but there are a lot more ways: - some overcomplicated in my mind, but they might be more precise

    – Yaroslaw Homenko – 2018-12-20T08:29:18.097

    Thanks a lot for your input. I really appreciate it – spv92 – 2018-12-20T17:56:59.660