Jump to content
catalinvrt

reports - export clients with marketing consent

Recommended Posts

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

Share this post


Link to post
Share on other sites
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"...

BSUPM8e.png

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated