Solved

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

tchan122
Excursionist
16 0 6

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!

Accepted Solutions (2)
paul_n
Shopify Staff
1021 133 243

This is an accepted solution.

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

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.

View solution in original post

paul_n
Shopify Staff
1021 133 243

This is an 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. 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.

View solution in original post

Replies 15 (15)

PurplePartner
Shopify Partner
178 18 30

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. 

Did you find my response useful? Mark it as "Accepted solution" so others can see it, or just like it.
tchan122
Excursionist
16 0 6

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.

 

tchan122_0-1711532359243.png

 

 

 

 

Kalen_Jordan
Shopify Partner
452 16 66

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

tchan122
Excursionist
16 0 6

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

 

 

tchan122_0-1712027545520.pngtchan122_1-1712027550255.png

 

Kalen_Jordan
Shopify Partner
452 16 66

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
Excursionist
16 0 6

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
Shopify Partner
38 0 7

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
Shopify Staff
88 28 25

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.

 

Screenshot 2024-04-01 at 11.32.40 PM.png

 

Hope that helps!

DaveMcV | Flow Development Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
tchan122
Excursionist
16 0 6

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.

tchan122_0-1712033840180.png

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
Shopify Staff
88 28 25

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.

DaveMcV | Flow Development Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
paul_n
Shopify Staff
1021 133 243

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. 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
tchan122
Excursionist
16 0 6

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
Shopify Staff
1021 133 243

This is an accepted solution.

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

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
paul_n
Shopify Staff
1021 133 243

This is an 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. 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
tchan122
Excursionist
16 0 6

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