reports - export clients with marketing consent

[catalinvrt]

I need to export a csv with the clients that have optin for email marketing in order to import the list in my email solution. However, after talking with whmcs support it seems it doesn't exist a way t do that but rather I'll have to edit the report file(or create new one). This is what they told me:

 

Quote

I am afraid that there isn't a report that exposes the desired information at this time. However you could create a custom report using our instructions at https://docs.whmcs.com/Reports#Customising_Reports that pulls the clients from the tblclients database table only if they have a matching record with consent confirmed in tblmarketing_consent (where tblclients.id = tblmarketing_consent.userid and opt_in = 1) and then export the results accordingly.

Can anyone help me with this?

I have located the report file that does the export, but I don't understand where to add the code received from support. The code looks to me more like a half of an sql query .

 

Regards

 

 

clients.php

[catalinvrt]

I have found the way, thanks to this post.

[brian!]

16 hours ago, catalinvrt said:

I need to export a csv with the clients that have optin for email marketing in order to import the list in my email solution. However, after talking with whmcs support it seems it doesn't exist a way t do that but rather I'll have to edit the report file(or create new one).

you'll definitely have to create a new one - if you edited an existing report, whilst it would work for now, the next time you update WHMCS, it will get overwritten and your changes will be lost.

16 hours ago, catalinvrt said:

I have located the report file that does the export, but I don't understand where to add the code received from support. The code looks to me more like a half of an sql query .

I don't think that you need to do what Support suggested, e.g querying a second database table - just querying the clients database table should be enough for your needs... if i'm wrong on that (and it needs the insurance policy of checking the second table for confirmation), then it's still doable  - but would require many more changes to the report structure.

the first step would be to create a new report in /modules/reports, and call it clients_marketing.php (or anything you like, but it can't contain spaces).... copy the code from inside clients.php into it, but change the value of $filterfields to..

$filterfields = [
    'id' => 'ID',
    'firstname' => 'First Name',
    'lastname' => 'Last Name',
    'companyname' => 'Company Name',
    'email' => 'Email',
    'address1' => 'Address 1',
    'address2' => 'Address 2',
    'city' => 'City',
    'state' => 'State',
    'postcode' => 'Postcode',
    'country' => 'Country',
    'phonenumber' => 'Phone Number',
    'currency' => 'Currency',
    'groupid' => 'Client Group ID',
    'credit' => 'Credit',
    'datecreated' => 'Creation Date',
    'notes' => 'Notes',
    'status' => 'Status',
	'marketing_emails_opt_in' => 'Email Marketing'
];

and that's it. 🙂

if you then go to the Reports page in WHMCS, down the bottom of the page in the "Other" section, your custom report should be there (or you can see a link to it in the sidebar)... click on that to open the report and it will have a new field option, "Email Marketing"...

if you tick the "Email Marketing" checkbox, then it will return the value from the tblclients database table - 1 if they've opted in; 0 if they haven't...

should you only want a list of those clients that have opted in, then you can use the filter as above to limit the results.

it's worth noting that when I run this locally, the results are the same as if I had queried tblmarketing_consent.

[brian!]

I wish I had spotted the thread below before posting - that too contradicts Supports advice to you and repeats what I thought... 🙄