Happy Business Starts Here

How should I write Export ZOQL: detecting customer accounts that do not using services recently

Master

How should I write Export ZOQL: detecting customer accounts that do not using services recently

Hi All

 

I'm writing Export ZOQL queries that I can use in several situations.

However, I can not conceive of a query that works effectively under the following situation.
Is this impossible with Export ZOQL (that is, I need to process exported data with Excel or program), or have another way of thinking, please lend your wisdom:

 

The Situation:

  1. My customers have contracted Usage Charge serviece (like car sharing).
  2. Usage is imported to Zuora every time a customer uses the service.
  3. I want to identify customers who do not use the service for more than a month at the time of executing the query (I want to issue a discount to those customers).

My query:

select Account.Name, Account.AccountNumber, Subscription.Name, RatePlanCharge.Name, Usage.StartDateTime as LastUsedDate 
from Usage 
where (Subscription.Status = 'Active' or Subscription.Status = 'Expired') and Usage.StartDateTime < 'today - 1 month'

However, with this, it is only searching Usage imported more than one month ago. The searched customer may be using the service during the past month.
Is it impossible to incorporate the condition that Usage has not been imported in the past month?

Tags (2)
2 REPLIES 2
Zuora Staff

Re: How should I write Export ZOQL: detecting customer accounts that do not using services recently

I'm afraid you are out of luck here. There's no sub querying  or union operators in ZOQL so you can't, like you would in SQL, do a query of all the accounts with a subscription to the appropriate rate plans and then remove those accounts for which usage has been loaded.  You'll need two queries and to filter the results externally. 

Master

Re: How should I write Export ZOQL: detecting customer accounts that do not using services recently

I understand that there is no way to accomplish this with one query... Thanks, @Richard!

(In the end, I did not have the opportunity to use this export ZOQL).