1010408 - Create spreadsheet template for bulk uploading events

Status: RESOLVED FIXED

(Webmaker Graveyard :: Events, defect)

Description

[Brett Gaylor [:brett]]

Use case: a partner (lets say YMCA) has hundreds of events, they want to add them to maker party.

Suggestion - can we do CSV or other spreadsheet importing of data, if we give them a specific spreadsheet to import?

Comment 1

[Matt Thompson (@OpenMatt :OpenMatt)]

* Gavin: do you have a sense of what train this will ship on?

Comment 2

[Robert Friedman [:omnignorant]]

Brett, I like this test case and I'll raise you one: A partner, the Cities of Learning, have 100,000 events. Can we export them over?

Comment 3

[Gavin Suntop [:gvn]]

I think the next sprint would work. We need to get more info from our partners regarding their current events databases though.

If we can't figure out something standardized then this will be impossible to automate for all partners.

Comment 4

[Jon Buckley [:jbuck]]

Maybe just drop the idea of handling some sort of standard format? So long as it's in a structured format (JSON, CSV, XML, SQL, etc), we can convert it to whatever format is easily imported into the database.

Comment 5

[Brett Gaylor [:brett]]

> Brett, I like this test case and I'll raise you one: A partner, the Cities
> of Learning, have 100,000 events. Can we export them over?

Robert can you give us a URL where these show up?  Are these on an events platform or individual events?

Comment 6

[Brett Gaylor [:brett]]

Something I'd like us to be able to do as a baseline:

Have a CSV file that we know when imported works (event host, dates, description).  Then we can use this ourselves to bulk enter many events, as well as give to partners to ask them to fill out manually.

Comment 7

[Robert Friedman [:omnignorant]]

ex. http://explorechi.org/explore

Comment 8

[Robert Friedman [:omnignorant]]

Note: that is where most Hive orgs are posting events in Chicago (beat us to it months ago).

Comment 9

[Brett Gaylor [:brett]]

Given that we are focusing on adding mentors and RSVP to events changes this sprint, lets instead commit to settling on a plan of how to do this, and implementing prior to the start of the campaign.

Comment 10

[Matt Thompson (@OpenMatt :OpenMatt)]

* Agreed. Gavin, do you have a proposal? In terms of preferred process / format?

Comment 11

[Gavin Suntop [:gvn]]

My basic idea is distributing a spreadsheet (Probably in Excel format) to partners to populate with their event data.

The tricky part is ensuring that what they fill in can be properly ingested into our system. Many of the fields require validation and follow particular schemas.

Some examples:

Attendees: This can only be a value of 5, 15, 35, 75, 150, 350 or 500 to display properly.
Event link: Must be valid URL
Start Date: Must be either a parseable natural language date or an ODBC Canonical format.
Location: This is a natural language string that is broken into multiple fields by Google's autocomplete API. I'm assuming this can be done on the server side, but not certain.
Tags: Must be CSV.

As you can see it can get complex and difficult to ensure proper translation into our system without human validation.

The most *bulletproof* fashion for bulk adding events is unfortunately the slowest: people performing manual data entry using our Events app.

(I'm flagging Jbuck to see if he has any other ideas/thoughts...)

Comment 12

[Jon Buckley [:jbuck]]

Yeah, I think the best we can hope for is to be given some sort of spreadsheet. Then for some value of n, if it's less than n, manually add events. If it's larger than n, lets get some developer time to script uploading that event to our service.

Comment 13

[Matt Thompson (@OpenMatt :OpenMatt)]

* Okey doke, thanks guys. 

* Amira / Lucy: does this work for you?

Comment 14

[Lucy Harris [:lucyeoh]]

Sounds good! As long as we give really clear instructions I think it will work. We can even make a little "how to" video if it feels complicated.

Comment 15

[Matt Thompson (@OpenMatt :OpenMatt)]

* Ok great. Lucy: can you create the spreadsheet template for partners to use? It will be helpful to send them an attachment that helps them enter the data using the format we want

* We can then attach it along with instructions here: 
https://wiki.mozilla.org/Maker_Party/Event_upload

Comment 16

[Brett Gaylor [:brett]]

Actually, I dont think Lucy can make this.  Instead, Gavin or another dev needs to format this in a way that will make it possible to input it into our system.

Comment 17

[Brett Gaylor [:brett]]

In addition to this list, I still think we need to find out of their are partners who platforms that might want to push or pull from our API.

Before closing this, I think we need Lucy to create a list of partners that have platforms that have their own events platforms?  Similar to Robert's in comment #7

Comment 18

[Matt Thompson (@OpenMatt :OpenMatt)]

(In reply to Brett Gaylor [:brett] from comment #17)

> In addition to this list, I still think we need to find out if there are partners who platforms that might want to push or pull from our API.

+1 . Claw asked me about that very thing today.

Proposal: let's tackle the fancier API stuff in this ticket:
Bug 1027832 - Document our event platform integration plan for key partners 

And just make this ticket about simpler / hackier spreadsheet import? That still seems like a use case worth supporting separate and apart from the fancier API stuff.

What do you think Brett?

Comment 19

[Lucy Harris [:lucyeoh]]

I can try and pull that list together. I'll see if Amira has an idea off the partners who have their own platforms off the top of her head. Otherwise I'll have to go to each of their sites individually and check.

Comment 20

[Matt Thompson (@OpenMatt :OpenMatt)]

* Lucy: let's make sure we co-ordinate on this. And document that list together here:
https://bugzilla.mozilla.org/show_bug.cgi?id=1027832
(worried that we're going to cross wires or duplicate effort)

Comment 21

[Matt Thompson (@OpenMatt :OpenMatt)]

* Brett: should we mark this one resolved wontfix, and carry on with the work here in bug 1027832?
It includes a partner list that Claw has started
https://etherpad.mozilla.org/event-partners

Comment 22

[Brett Gaylor [:brett]]

No I still think we need this template for those partners who may not have a platform yet still have many events. However, going to push this to next sprint.

Comment 23

[Matt Thompson (@OpenMatt :OpenMatt)]

* Ok. Lucy: can you try creating a spreadsheet template + updated instructions here: 
https://wiki.mozilla.org/Maker_Party/Event_upload

Comment 24

[Brett Gaylor [:brett]]

I think Gavin or another developer has to make this first in the format that will work on the platform.

Comment 25

[Chris DeCairos (:cade)]

I've done a first pass on a spreadsheet with validation for several fields. 

https://docs.google.com/spreadsheets/d/1E4lNReyIqwIMiPsMHXW3bbqfwQ6zziUX_BIE2blvOB0/edit?usp=sharing

If someone knows how to tell it to apply validation to a range - ie. B2:B(INFINITY) - that'd be great. can't seem to validate past row 1000

Comment 26

[Chris DeCairos (:cade)]

Attachment: https://github.com/mozilla/webmaker-events-service/pull/50

Adds a script for importing events from a CSV file (generated from a spreadsheet)

Expected data format is the output of this template spreadsheet (as CSV): https://docs.google.com/spreadsheets/d/1E4lNReyIqwIMiPsMHXW3bbqfwQ6zziUX_BIE2blvOB0/edit?usp=sharing 

run via: node scripts/bulkImport ~/relative/path/to/csv/file.csv

If a single row fails for any reason, the entire dataset is not committed to the database.

Comment 27

[Matt Thompson (@OpenMatt :OpenMatt)]

* Cade: the wiki stub for user documentation is here:
https://wiki.mozilla.org/Maker_Party/Event_upload

* Can you have a go at drafting steps we want partners to follow? plus link to whatever templates you want them to use, etc.

Comment 28

[Chris DeCairos (:cade)]

Wrote a draft here: https://wiki.mozilla.org/Maker_Party/Event_upload

I think maybe we should track this portion of the work in bug 1035373

Comment 29

[[github robot]]

Commit pushed to master at https://github.com/mozilla/webmaker-events-service

https://github.com/mozilla/webmaker-events-service/commit/e28012494f271d8d57e5c5d8519234178a849bec
Bug 1010408 - Bulk event creation via CSV importing script