To filter by name use the -name predicate which takes a wildcard pattern similar to those used in shell globbing:
find . -name '*_LOGS.txt' -type f -mtime +6 -exec rm -f {} +
It's better to put that filter first, as it's generally less expensive than the ones that are based on file metadata (though some find implementations do that reordering automatically). That way, if a file is excluded because its name doesn't end in _LOGS.txt, find doesn't need to retrieve its metadata.
Like in shell globs, * matches any sequence of characters. Note that contrary to shell globs, there is no special handling of files whose name starts with . (hidden files), so they will be included as well. Same for files in directories whose name starts with ..
Also note that rm can take more than one filename as argument, so you can use + instead of ; as the -exec delimiter. That also has the advantage of find reporting errors by rm in its exit code.
Also note that portably, you need to give at least one file or directory as argument to find for it to know where to search. Some find implementations default to . (the current working directory) when you don't tell it where to look, but some others don't.
If using zsh, you could also just do:
rm -f -- **/*_LOGS.txt(D.m+6)
Where . is the equivalent of -type f, m+6 of -mtime +6 and D also considers hidden files and files in hidden dirs like find does.
Contrary to some find implementations, it will still manage to remove files whose name contains sequence of bytes not forming valid characters in the locale. It could however fail to execute rm if there is a very large list of files to remove (but see the zargs function or the zsh/files module to make rm builtin to work around that).
find -name "*LOGS*" -type f -mtime +6 -exec rm -rf {} \;