Trying to get customers who's last order is more than 3 months ago

[tchan122]

I'm trying to get a list of customer who's last order is more than 3 months ago.

I'm using "Get customer data" and the query as

order_date:>='{{ scheduledAt | date_minus: "3 month" }}'

I then log the result with last order as ascending and it works, giving me a list of customer who's last order is after 3 months ago, so the syntax should be correct.

However, when I edit the query for the order_date that is before 3 months ago

order_date:<='{{ scheduledAt | date_minus: "3 month" }}'

I then log the result with last order as descending and it gives me a list of customer who's last order is today and before.

I tried this with customer_date instead of order_date and it gave me the correct result. Can someone help me with this problem? The final result I want to achieve is to tag any customers who have not purchased in the last 90days as churned if they've made a purchase previously on a daily/hourly basis.

Much appreciated for your help!

[paul_n · Accepted solution]

I think I'd call it solved. I have passed along the defect to the team that owns the API

[PurplePartner]

Hi, @tchan122 !

Thanks for being part of the Shopify Community. To create a list with all customers who ordered in the last 12 months, and then export it for further purposes, please follow the next steps:

1) Go to "Customers" and use the "Add filter" button from the right corner.

2) Apply the following Filter code:

last_order_date <= 2023-12-31 

 P.S. You can change the date with a new one. In my example, I used 31 December 2023.

3) Apply the filter, then press the "Save segment" button. Give it a name.

4) Go to "Segments", under the Customers tab, and hit the Export button (from More actions) to download a CSV list that can be used for whatever you need.

If you face any difficulties around the road, hit me a DM and I'll help. 

[tchan122]

Hi PurplePartner,

While I appreciate your reply, it's not exactly what I was asking. I am aware of how to obtain a list or create a segment of these customers, I'm unable to do so within Flow so that I can automate the tagging.

Within Shopify flow, I would like to use "Get customer data" with a query where it would return a list of customer order_date (last order date) is before 3 months/90days ago.

[Kalen_Jordan]

Might not be possible - I believe order_date is querying on any order not their last order.

[tchan122]

Hi Kalen, this doesn't make sense either. I've tried to narrow down to select the order_date as the day where there were only orders from 2 customers but I ended up receive more than 2 customers. I've tried using GraphiQL as well. Here's a comparison with the segment filter

[Kalen_Jordan]

Weird. I'm seeing some weird stuff in my test store as well when doing some queries on order_date for customers. I'm guessing you're probably not looking for a 3rd party app solution, but this is one of the areas where having a database to store these things that you can query on comes in handy.

[tchan122]

Thanks Kalen for your confirmation. Yes, I've been trying to use Flow to avoid 3rd party apps. I do already have data exported automatically but I would love to use flow to help automate this. Thanks for your help and input.

[cybko]

Wouldn't it make more sense to tag the customer as they order, then remove the tag after 3 months if they don't order? That feels more like Flows style

[DaveMcV]

Hi @tchan122,

Would using the segment template Customers who haven’t ordered recently but used to do so frequently give you a starting spot for implementing this with segments? That feature is designed for curating lists of customers which sounds like what you're looking for. You could then tag or use the segment for other actions, but it would not interface directly with Flow yet but we are working on better integrations with Segments.

Hope that helps!

[tchan122]

Hi @DaveMcV , thank you for your reply. I am already using segment to filter out this list of customers and tagging them manually.

Would you be able to guide me on how to use the query within Flow. This is the part that I'm having a real tough time with. It feels silly but I don't seem to be able to use order_date correctly.

Can you define what order_date is? I've tried using it but I cannot get the following queries with the right result.

I've tried " query: "order_date:'2024-02-10'" sortKey: LAST_ORDER_DATE" within GraphiQL App but it shows far more customers than the customers who ordered on 2024-02-10.

I've also tried  "query: "order_date:<'2024-02-10'" sortKey: LAST_ORDER_DATE" and I get a list of customers who's last order is before 2024-02-10.

The only query I've had success is " query: "order_date:>'2024-02-10'" sortKey: LAST_ORDER_DATE" and I get a list of customers who's last order is after 2024-02-10.

Much appreciated for your help!

[DaveMcV]

Unfortunately, the query interface for customers that is provided is not able to express that concept. We're aware of this and looking at better integrations between Flow and Segments that would improve Flow's ability to handle this sort of us case, so keep an eye out for that moving forward.

[paul_n]

I see the same behavior from the API. It seems like you cannot use "less than" with that particular field but "greater than" seems to work fine. 

[tchan122]

Thanks for the confirmation @paul_n. I thought I was crazy for a sec when it wasn't work. Should I mark this as solved or leave it open?

[paul_n · Accepted solution]

I think I'd call it solved. I have passed along the defect to the team that owns the API

[paul_n · Accepted solution]

Ok, so the order_date filter actually takes into account the first order or last order depending on the direction of the > or <, which is why we are seeing this weird behavior. 

[tchan122]

Thanks for the update. Could you please keep us posted on the development on this? Thank you @paul_n