Mixpanel Batch Source
Introduction
Mixpanel is a business analytics service and tracks user interactions with web and mobile applications and provides tools for targeted communication with them. Its tool set contains in-app A/B tests and user survey forms. Data collected is used to build custom reports and measure user engagement and retention. This plugin will extract raw event-level data from Mixpanel that can be further transformed and enriched with other data sources.
Use case(s)
Retrieve raw events data from Mixpanel service
User Storie(s)
As a data pipeline developer, I should be able to retrieve raw Mixpanel events data for specified start and end date so that I can enrich and transform raw data for further analysis
As a data pipeline developer, I should be able to specify API secret to authenticate the export request
As a data pipeline developer, I should be able to see all the errors while fetching the raw events data from Mixpanel so that I can review and fix those export issues
Plugin Type
Configuration
User Facing Name | Type | Description | Default value | Notes |
|---|---|---|---|---|
API Secret | string | Mixpanel API secret |
| https://developer.mixpanel.com/docs/exporting-raw-data#section-required-parameter |
From date | string | Start date for reports data |
| YYYY-MM-DD format |
To date | string | End date for reports data |
| YYYY-MM-DD format |
Events | multi-select | Comma separated list of events you would like to get data on |
| Optional |
Filter | string | Expression to filter events by |
| Optional |
Design / Implementation Tips
Mixpanel only supports batch source so there will not be any streaming source for Mixpanel
Data export API always returns data in JSON format.
API details provide best practices around API usage
Design
Approach(s)
Properties
Security
Limitation(s)
Future Work
Test Case(s)
Test case #1
Test case #2
Sample Pipeline
Please attach one or more sample pipeline(s) and associated data.
Pipeline #1
Pipeline #2
References
Mixpanel Data Export API: https://developer.mixpanel.com/docs/exporting-raw-data
Client libraries: https://developer.mixpanel.com/docs/data-export-api#section-client-libraries
Comments
@Vinisha Shah
answering your questions:
since all events is user defined, there can be many different fields so there is no strict schema for record
what do you mean under "scale if we use export apis"?
nothing, query will stuck for while(I tested with 4 min delay on mock server) and after report is generated it will continue to run
it works as https://github.com/data-integrations/http plugin, do you have some concerns about this approach?
@Prateek Duble, @Bhooshan Mogal could you please provide some input to proceed with this?
@Yevhenii Chekanskyi @Vinisha Shah
Overall, I think it is ok to make HTTP calls in mappers, since that is a pattern in all SaaS plugins.
However, @Yevhenii Chekanskyi we should add more details in this doc, especially around 3 things:
Schema. Will the plugin export a particular schema? If not, what will the data appear as, and how should a user proceed to build a pipeline?
Scale: Are there any API limits etc in Mixpanel that might impact this plugin, and what's the plan to avoid them?
Performance/Parallelism: How does the plugin parallelize calls? Is that applicable here, and if it is, can a user influence it?
with current implementation each record contains one field "event" with json object. Pipeline need to have json transformer to parse fields in event
technically there is no limit - we need single api call to get all events at once, documentation saying nothing about limits
no way to do this, since there is single endpoint that returns single file with all events - that means its completely sequential and we need to read that file line by line(each line represents separate event)
@Prateek Duble @Yevhenii Chekanskyi
I would say 2 and 3 are fine for now. However, 1 to me seems like a blocker. Is there a way to be more user friendly in this scenario? If someone needs to apply a JSON parsing every time after the source, can we not parse the JSON and create a more user friendly schema in the source itself?
We should apply JSON parsing for all the events and generate a flattened schema. MixPanel actually provides Data Warehouse export options and generates schema when its trying to push the data to Data Warehouse (Please see https://developer.mixpanel.com/docs/mixpanel-bigquery-export-design). We should use the same approach (Using the same table for all events). This would not just simplify user's view of the data, but will also align with existing MixPanel export options.
@Yevhenii Chekanskyi - Can you please review and confirm this approach? I will then update the document.
Documentation saying
Each table for each event has one column per unique property name across all events in the history of the dataset.
MixPanel have this unique column names, but we don't. We can grab all that column names only after export is completed and all data is iterated, which not looks like a solution. Only visible option for me is to extract event name and timestamp and push everything else in map field in schema, but this is not much different from exporting plain json event.
As an alternative solution - we can extend widget to allow user to enter his own fields in schema, so that fields can be extracted from map alongside with event name and timestamp - some trade-off, user will enter desired fields by hand, but will not have json processor or other parsing things in pipeline.
@Prateek Duble, @Bhooshan Mogal any thoughts?
the problem is that basically even if you let the user add a JSON parser, it will still never have a good experience, because the user will have to remember to create a superset of fields as the schema in the JSON parser. The fact that the schema needs to be derived based on data is not a nice pattern. I'm thinking about options. This makes it impossible for users to create a pipeline effectively, since the schema is not known.
Here is one option that I can think of:
Create a property called Schema Mode
"Automatic" will parse the JSON, flatten it and send the fields to the next node. A user won't get a single JSON blob, but they can see a set of fields, that if they are aware of, they can use in subsequent nodes. However, you won't be able to see the schema in the UI. No other properties will be required. However, in this case, the plugin must ensure that the sink's schema gets updated with the right fields, as newer fields show up in the event.
"Manual" will allow users to provide an explicit set of fields to import from Mixpanel. Users should be able to specify the field and the type. If the type from Mixpanel doesn't match the type specified by the user, the plugin should try to do the conversion, and bail out if it fails. It will need an extra property called Fields.
"First Event" We can also provide an additional mode that allows users to derive schema based on the first event. Meaning at the time of configuration, the plugin explicitly fetches one event from Mixpanel, and sets the schema based on that. Following this, only the fields in the first event are fetched for the remaining events. However, @Prateek Duble do you know how likely is it that this will work?
What do you think @Prateek Duble @Yevhenii Chekanskyi. This is not ideal, but at least seems like a reasonable workaround.
So, I don't have questions about "Manual" mode, really easy one to understand and implement
"First Event" mode - raw export api returns all events at once in single request and that request can take minutes before starting returning events, this approach will not work, we can't make this request at time of configuration. But we can go in alternate way - there is an api that can return a statistics for top properties per event(https://mixpanel.com/api/2.0/events/properties/top/), that means we can get list of fields per-event. In this case user can enter event names and get properties for that events.
"Automatic" - I am hardly understand usability of such mode. Lets assume we are passing some random records with random schema to some sink. If that is a file sink in json format - no problems, but resulting json file will looks exactly like original response from api. In order to transform resulting records user need to know their fields and build pipeline accordingly before pipeline started. And if that will be a database sink? In order to make database sink to work records must share single schema(which will be know completely only after ALL report is processed and records for that report is generated) and correspondent table must be configured for that sink. This mode doesn't help to get fields in subsequent nodes and has exactly same usability as pushing properties to single map in record. Correct me if I am wrong or misunderstood something in your statement.
However, mixpanel has another interesting endpoint - https://mixpanel.com/api/2.0/events/names/ . This endpoint will return names of events for last 31 day(max 255 events). I am not sure if that endpoint reliable, but it can be used in combination with https://mixpanel.com/api/2.0/events/properties/top/ - it will allow us to get fields names for events for last 31 days and create flattened schema for that events. Worth case scenario will include 256 queries to rest api for generating schema and I am not sure if that will not break some api limits(do not confuse this endpoints with raw_export endpoint).
Assuming this I can say that there are following "schema modes" that can be implemented and will be usable with some use-cases:
"last 31 days events flattened schema", under question due to possible 256 api calls for schema generating - this will require to call two mentioned endpoints to get huge flatten schema, but I am completely don't know if mixpanel has some limits for that endpoints, most likely I will contact their support to figure this out
"selected events flattened schema" - user will manually enter list of interesting events, and CDAP will grab all fields for that events automatically. User will be able to remove fields he don't need.
"manual schema" - already discussed and described earlier
"raw mode" - output everything in record with schema {eventName: string, properties: map[string, string]}
with some changes for cdap UI "selected events flattened schema" mode can be really nicely implemented - if we will have feature to fill multi-select widgets in UI from backend, we can query api for event names in configure page and show up events for selection. But I am not sure how hard it will be to implement such feature. As far as I see I can try to use dsv widget alongside with plugin-function feature, that can be nice enough as well.
What do you think about this?
@Bhooshan Mogal @Prateek Duble Could you please respond to the proposal above. Thanks in advance!
@Yevhenii Chekanskyi - Let's please simplify this for the user for specified list of events. We can do this for the first iteration with the following approach:
User specifies list of events (eg. event1, event3, event7 and event9) she would like to extract and transform as part of plugin configuration
event1 columns: a,b,c
event3 columns: b,c,e
event7 columns: a,e,f
event9 columns: e,g,h
This is assuming that we can get the fields associated with the events by calling some metadata API.
We create a new column, event_name and flattened schema for the specified events:
event_name | a | b | c | e | f | g | h |
|---|---|---|---|---|---|---|---|
event1 | null | null | null | null | |||
event1 | null | null | null | null | |||
event3 | null | null | null | null | |||
event7 | null | null | null | null | |||
event7 | null | null | null | null | |||
event9 | null | null | null | null |
We would get the data and let the user perform transformations before getting this data into specified sink.
@Bhooshan Mogal - What do you think? Aligning with the second approach that we discussed. (First was pure EL, second was this one)
@Prateek Duble, so you just described exactly what I mean under "selected events flattened schema".
Thanks for reply.
I like this, but my only comment is that in addition to this, we should also support an EL mode, where the user doesn't want to do any transformations in the UI, so doesn't need the schema.
In this mode, there will be no schema, and no event filtering. All events (with any filters – time based?) will be available as blobs to sink into any sink. Users will have to worry about deserialization themselves.
Makes sense. So there will be two modes - "selected events with flattened schema" and "raw"(currently implemented mode that outputs string with json).
Yup
I would have a property called "Import Type" as a radio button with options "Selected Events only" and "Raw'. If true, show additional multi-select for the events.
We already have csv widget to set filter by event name. I think it will be better to add single checkbox "Generate schema by filtered events" and require user to select some events in that csv. It makes more sense for me - if user interested in fields of particular events, he must specify them in filter. Otherwise we will have two cvs widgets for filtering and event selection. See screenshot:
What do you think about this?
@Yevhenii Chekanskyi @Prateek Duble can you add more details to the design document?
What is the output schema that is expected from this plugin? Does it include metadata for the events such as timestamp, uuid etc?
How would this plugin scale if we use export apis?
What would happen if the export has large data? From the api description it seems like larger export can take several minutes. `While this process is normally quick and results in a smaller file size, some large exports can take a few minutes to generate.`
Have we agreed to call http endpoint from mapper tasks?
I don't see LGTM on the design document, was the design approved by someone?