Apologies for the delay getting back to you on this, @Marcus_Holmes
You don’t need access to the client’s Dev Dashboard at all. The mechanism you’re looking for is Custom Distribution, which you set up from your own Partner Dashboard. You create the app in your Partner Dev Dashboard, then go to the Partner Dashboard and select “Custom distribution” as the distribution method. Enter the client store’s myshopify.com domain, generate the install link, and send it to your client. They click it, authorize the app, and they’re done. That’s a two-click process on their end, so it should be manageable even for non-technical store owners.
This was covered in a similar thread here where a partner hit the same wall with the new Dev Dashboard. The accepted answer walks through it.
You’d create one app per client store, each with its own install link. Once installed, your app uses the authorization code grant to get an offline access token for that store. Your automation script stores each token and loops through the stores running the shopifyqlQuery endpoint. For scopes, the app needs read_reports and the protected customer data scopes (read_customers, read_customer_name, read_customer_address, read_customer_email, read_customer_phone). shopifyqlQuery requires all of these even for non-customer queries like FROM sales, but for custom apps they’re available without any approval process. You just declare them in your app configuration.
If the number of stores grows significantly down the road, you might want to consider building a public app with limited visibility instead, which would let you use a single app across all stores (though that route involves app review, unlike custom distribution).
Let me know if you run into any issues getting it set up.