Solved

JQL Query to get all data and select properties

  • 12 June 2019
  • 9 replies
  • 1017 views

Badge

Is it possible to get the raw data report, but only some of the properties? The previous team included a whole slew of properties that only apply to about 10% of the data


icon

Best answer by jaycee00 19 June 2019, 19:19

I talked to support and received this answer using the map function.

It was exactly what I was looking for...except there's a size limit on JQL queries.

I'll leave it here in case anyone wants to use it

function main() {
  return Events({
    from_date: '2019-06-17',
    to_date:  '2019-06-18'
  })
  // You can use the following 'map' to determine how the events are displayed
  .map(function(event){
    return {
       "event" : event.name,
      "distinct_id" : event.distinct_id,
       "properties_Name" : event.properties.name,
      "Date" : new Date(event.time).toISOString().substr(0,19),
      "Time" : new Date(event.time).toISOString().split("T")[1].split(".")[0],
      "$browser" : event.properties.$browser,
      "$city" : event.properties.$city,
      "utm_campaign [last touch]" : event.properties["utm_campaign [last touch]"]
    };
  });
}

View original

9 replies

Badge

I talked to support and received this answer using the map function.

It was exactly what I was looking for...except there's a size limit on JQL queries.

I'll leave it here in case anyone wants to use it

function main() {
  return Events({
    from_date: '2019-06-17',
    to_date:  '2019-06-18'
  })
  // You can use the following 'map' to determine how the events are displayed
  .map(function(event){
    return {
       "event" : event.name,
      "distinct_id" : event.distinct_id,
       "properties_Name" : event.properties.name,
      "Date" : new Date(event.time).toISOString().substr(0,19),
      "Time" : new Date(event.time).toISOString().split("T")[1].split(".")[0],
      "$browser" : event.properties.$browser,
      "$city" : event.properties.$city,
      "utm_campaign [last touch]" : event.properties["utm_campaign [last touch]"]
    };
  });
}

Userlevel 3
Badge

Hi @jaycee00, you can definitely do this on JQL. I think what you would need is the following below. You would just need to update the from_date/to_date and the properties that you want to groupBy.

function main() {
  return Events({
    from_date: '2019-06-13',
    to_date:   '2019-06-14'
  })
  .groupBy(["properties.$region"], mixpanel.reducer.count());
}

In the example, I pulled the events from from dat 06-13-2019 to 06-14-2019 with the event property of $region. I usually refer to their JQL documentation to help me build queries https://developer.mixpanel.com/docs/jql-getting-started#section-step-4-grouping-purchases-by-plan .

Hope this helps!


Userlevel 3
Badge

@jaycee00 would you mind sharing the query you created?


Userlevel 3
Badge

Hey @jaycee00, try the one below, let's see if that gets a result. I think the "event" might be breaking it


function main() {
  return Events({
    from_date: '2019-06-10',
    to_date:   '2019-06-14'
  })
  .groupBy(["properties.$current_url", "properties.$region"], mixpanel.reducer.count());
}

Userlevel 3
Badge

Thanks for sharing @jaycee00, sorry I misunderstood what you were looking for!


Badge

Sorry this didn't produce the output I was expecting. It output key.1 and value when I tested with one property.

When I tried adding event and date, it just produced more "key" columns with null values


Badge

function main() {

return Events({

from_date: '2019-06-10',

to_date: '2019-06-14'

})

.groupBy(["event","properties.$current_url","properties.$region"], mixpanel.reducer.count());

}


Key0, Key1, Key2, and value.



Badge

I guess I should have been more clear. The raw data report outputs each event by date. I want to see that, but just with less properties showing up.

The version I tried before adding event was what you have there, but I don't want to see counts of properties, I want to see unaltered data without every single property.

In SQL, it would have been as simple as writing SELECT col_1, col_2, col_3..... instead of writing SELECT *

I don't know why they thought JQL would be a better option when hardly anybody knows it.


Badge

Np, sir. At least you tried


Reply