# I want to Data Export all the posts for analysis
**URL:** https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689
**Category:** Support
**Created:** [December 18, 2020, 8:23am UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689 "2020-12-18T08:23:17Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@eric.kim](https://meta.discourse.org/u/eric.kim)
#### Post date: [December 18, 2020, 8:23am UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/1 "2020-12-18T08:23:17Z")
Is there a way to export all the community post data?
I want to run an in-deep analysis of the community activities metric.
1. Time to first response
2. Repost to resolution
3. Time to resolution
4. Re-Assign to resolution
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 18, 2020, 3:30pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/2 "2020-12-18T15:30:23Z")
I’d recommend the [data explorer](https://meta.discourse.org/t/32566?silent=true) plugin. It can do at least some of the analysis that you want and can export whatever data you need.
---
### Author:  [@eric.kim](https://meta.discourse.org/u/eric.kim)
#### Post date: [December 18, 2020, 5:29pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/3 "2020-12-18T17:29:49Z")
Thanks!
This is cool!
---
### Author:  [@401Ott](https://meta.discourse.org/u/401Ott)
#### Post date: [January 20, 2021, 11:22pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/6 "2021-01-20T23:22:36Z")
Does anyone have this query to share? Also is there a way to do an export of the raw text of each post published?
Thanks!
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [January 21, 2021, 12:04am UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/7 "2021-01-21T00:04:28Z")
Have you looked at the sample queries?
> [@401Ott](#):
>
> Also is there a way to do an export of the raw text of each post published?
`select id post_id,raw from posts`
---
### Author:  [@JusticeUK](https://meta.discourse.org/u/JusticeUK)
#### Post date: [January 21, 2021, 4:10pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/8 "2021-01-21T16:10:46Z")
That will also give the PMs and deleted posts, which he probably doesn’t want.
@401Ott, if you want all posts published that do not include private messages, then you will need to filter out posts whose topic’s archetype is ‘private\_message’.
Additionally, if you don’t want posts that have been deleted you will need to filter out posts where the deleted\_at isn’t NULL. But as some posts can be deleted by their topic being deleted, you will also have to filter out posts where the topic they belong to has a deleted\_at timestamp.
If you need the exact query, please specify exactly what you need and I will write it for you.
---
### Author:  [@401Ott](https://meta.discourse.org/u/401Ott)
#### Post date: [January 25, 2021, 11:24pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/9 "2021-01-25T23:24:54Z")
Yeah, I think this is spot on that filtering to only the public, published posts is ideal. In a perfect export I would be looking for username, create date (of post), create time, and text body of the post. Having associated tags would also be great.
Overall my goal is to be able to identify when, and how often, a specific URL path is shared on my community. I’m hoping having all the post text in a .csv would help.
Thanks so much for the assistance, @JusticeUK . Let me know if this goal isn’t realistic (and no worries if it is not 😄 )
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [January 25, 2021, 11:38pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/10 "2021-01-25T23:38:59Z")
The easiest and quickest way to do this is
`select post_id, user_id, created_at, raw, cooked from badge_posts where deleted_at is null`
with `from badge_posts` instead of `from posts`. It’s already filtered to public posts only.
---
### Author:  [@JusticeUK](https://meta.discourse.org/u/JusticeUK)
#### Post date: [January 25, 2021, 11:50pm UTC](https://meta.discourse.org/t/i-want-to-data-export-all-the-posts-for-analysis/173689/11 "2021-01-25T23:50:14Z")
This is where the lack of documentation is quite frustrating, as I wasn’t aware of badge\_posts, and so the queries I wrote a few weeks ago all filter out PMs explicitly from posts.