:::: MENU ::::

Using SQL to improve your music library

I’m obsessed with my music collection. This is not simply a love a music – I love the collecting and organizing. I still choose to own rather than stream my music so I can retain the ability to tag, rate, and track my listening habits.

My obsesses likely stems from need to apply data analytics to everything. With my media library, I can dive into nerdy details about which artists are rising in the ranks and what great songs have been forgotten. With more than 75,000 songs, I need some heavy lifting to keep things neat and tidy.

This is where some data analytics comes in. I use MediaMonkey to organize my music, which supports a growing supply of powerful plug-ins. Using the Magic Nodes plug-in, I can apply complex SQL filters to help filter my music library far more than an iTunes auto playlist (though MediaMonkey still supports those).

I wanted to share some of my favorite Magic Nodes and playlists (some I wrote and some written by the Magic Nodes community). (Note: Magic Nodes SQL queries only require the WHERE statement, so the SELECT part is set by the interface). MediaMonkey shares its database structure on its wiki.

Find half played complete albums

Too often, I listen to part of an album, then forget to return. Thankfully, Magic Nodes some with this filter out of the box, checking for complete albums that only had half their tracks played.

magicnode-halfplayedalbums

<Group|Name:Playing statistics|Show tracks:No>\Complete albums where at least half of the tracks are not played|Icon:Top level|Filter:Songs.IDAlbum IN (SELECT IDAlbum FROM Songs GROUP BY IDAlbum HAVING Count(TrackNumber) = Max(Cast(TrackNumber As integer)) AND Count(TrackNumber) > 3 AND Sum(CASE WHEN PlayCounter > 0 THEN 1 ELSE 0 END) < Count(*) / 2)|Child of:Library|MM filter:Nodes|Position:Last child\<Genre|Statistic:Count(Album), Count(Tracks)>\<Album artist with album>

Find songs in multiple playlists

I try to keep songs I love on a variety of mood-based playlists (like for writing, playing video games, napping, cooking, etc.). I keep all of these playlists nested under a parent mood folder. To ensure I’m cycling new music through, this Magic Node checks when songs are used in more than 4 playlists.

magicnode-playlists

<Group|Name:Playlists…|Show tracks:No>\Songs in 4 or more Mood Playlists|Icon:Top level|Filter:Songs.ID IN (SELECT ps.IDSong FROM PlaylistSongs ps INNER JOIN Playlists p ON ps.IDPlaylist = p.IDPlaylist WHERE p.ParentPlaylist = 869 GROUP BY ps.IDSong HAVING Count(*) > 3)|Position:Last child|Child of:Library|MM filter:Nodes\<Genre|Statistic:Count(Items)>\<Artist|Statistic:Count(Items)>\<Title|Statistic:Count(Playlist)>\<Playlist|Statistic:Count(All)|Unknown:No>

Artists that have never been played

This default node helps find artists I’ve downloaded and never actually listened to. Often, these will be from soundtracks or compilation albums, but sometimes there are exciting new artists I’ve discovered, then forgot I once discovered them. This lists helps me find the hidden gems within my lbirary

<Group|Name:Playing statistics|Show tracks:No>\Artists that never played|Icon:Top level|Filter:Songs.ID = AArtistsSongs.IDSong AND (AArtistsSongs.PersonType = 1 OR AArtistsSongs.PersonType IS NULL) AND AArtistsSongs.IDArtist IN (SELECT ArtistsSongs.IDArtist FROM Songs, ArtistsSongs WHERE Songs.ID = ArtistsSongs.IDSong AND (ArtistsSongs.PersonType = 1 OR ArtistsSongs.PersonType IS NULL) GROUP BY ArtistsSongs.IDArtist HAVING Sum(PlayCounter) = 0)|Child of:Library|MM filter:Nodes|Position:Last child\<Genre|Statistic:Count(Items)>\<Artist|Statistic:Count(Tracks)>

First time played

Feeding into my nerdy obsession, this list isn’t so much as useful as it is informative. MediaMonkey (and other music libraries) keep track of every individual play date of a song, but they don’t share this information in highly visible ways. Thankfully, with a SQL query, we can uncover when a song was first played. I even organize it by the year so I can see which years I’m listening to more new music.

<Group|Name:Playing statistics>\First date played of each song|Child of:Library|MM filter:Nodes|Position:Last child|Filter:Songs.ID IN (SELECT Songs.ID FROM Songs JOIN Played ON Songs.ID = Played.IDSong GROUP BY Songs.ID HAVING Played.PlayDate = MIN(Played.PlayDate))\<Year played|Statistic:Count(Tracks)>\<Genre|Statistic:Count(Items)|All:Yes>\<Album|Statistic:Count(Items)>\<Title with artist|Statistic:Min(Date played)|Sort by:Min(Date played)>

Played 5 times within 30 days

I can get a small set of songs in my head, so this node becomes super helpful to quickly access those songs I want on constant repeat. Often, I’ll even use this list to inform a weekly highlights static playlist, with songs I love putting on repeat.

<Group|Name:Playing statistics|Show tracks:No>\Played at least 5 times within last 30 days|Filter:Songs.ID IN (SELECT Songs.ID FROM Songs LEFT JOIN Played ON Songs.ID = Played.IDSong WHERE CAST(julianday(date(‘now’, ‘localtime’)) – julianday(date(Played.PlayDate + julianday(’1899-12-30′))) AS integer) <= 210 GROUP BY Songs.ID HAVING Count(*) >= 5)|Icon:Top level|Child of:Library|MM filter:Nodes|Position:Last child\<Title with artist>

 

This is just a sample of my favorite nodes Magic Nodes comes with more than 100 for your organizing needs. Also, check out the SQL-Viewer plug-in, which is a built in SQL tool to help test out any other nodes you’d like to create (remember to add back in your SELECT statement).


Economics and PR of surge pricing

Sharing economy taxi services like Uber and Lyft sell themselves on increased efficiency. Any car can become a taxi, thus decreasing the cost for such a service, while increasing the number of people able to make an income from driving others around. Surge pricing is a key part of this strategy, by increasing the revenue drivers can make in high use times, while increasing the cost for riders, the goal is to optimize the supply/demand curve for taxis to where there a more drivers and less riders, but everyone remains happy (drivers make more, riders can get home).

Surge pricing has been extremely controversial, with users surprised by $400 rides home. To Uber and Lyft’s credit, they have improved their notification system warning of surge pricing, and while the final price can’t be certain (traffic conditions, etc.), ignorant consumers cannot replace good economics. And surge pricing is good economics. On rainy nights or Saturday evenings, finding a taxi has been a notorious impossibility. The reason for this may be counter to our economic assumptions. Cab drivers (non-uber drivers) as studied in New York City, operate with a goal in mind. In general, when they meet that goal, they stop driving for the day. On busy days, they meet the goal faster, so fewer cabs stay on the road. Surge pricing encourages these drivers to stay on the road longer because the improved windfall is greater than driving the same amount of time during non-peak periods.

However, this brings us to the PR challenges of surge pricing. In Australia, when a hostage situation in Sydney terrified the downtown area, Uber instituted a A$100 minimum price on rides. Uber’s claim here was they were encouraging drivers to enter the market and help people get home. This would be the same approach for New Year’s night, using high prices to get more drivers on the road. But unlike New Year’s, no one chooses to be out and stranded during a hostage crisis. Uber’s surge pricing preyed on people stuck because of terrifying and unpredictable events – events that stalled alternative transportation plans and increased the need to be a home and safe.

Uber particularly has received negative press even prior to this regarding surging pricing. As such, someone should have pointed out that unanticipated events should treat surge pricing in a different way, not because of economics, but because of the PR. For Uber, the cost of surge pricing during the hostage tragedy was arguably higher than the profit made. Instead, Uber could have found other ways to increase driver revenue – even going so far as to forgo their cut of each ride, increasing driver profits without increasing prices. Since Uber’s matchmaking technology is automated, the marginal price for each rider should be almost nothing. Thus, Uber could show how important and valuable its service is through smarter occasion pricing rather than blind economics.


Why net neutrality is only the beginning of a perfect internet

All eyes are on the FCC where a decision will be made on whether to regulate internet service providers or not.  If the FCC does decide to regulate Comcast and Verizon provide internet service, it will most likely be by reclassifying ISPs as telecommunication services under Title II, which has many pros and cons from a regulatory perspective. The biggest pro is that under Title II, the FCC can hold ISPs accountable for limiting communication over the internet, such as charging Netflix more to reach your computer. In theory, this would give the FCC power to enforce basic net neutrality.

Now, assuming the FCC passes the Title II reclassification and the reclassification withstands the certain court appeals, the United States will still be far away from a fair and optimized internet. Net neutrality itself is a symptom of the lack of competition in U.S. broadband markets. Even Comcast and Time Warner Cable admit, in their arguments for allowing the conglomerates to merge, they don’t compete with each other.

In most U.S. cities, consumers only have one choice for broadband provider. A lucky few have two choices at most. Rural areas have no choices and must rely on slower DSL, depressing productivity and economic development. Even for those with broadband options, they pay more for slower speeds in the U.S. than in almost every other advanced country. In Seoul, consumers can purchase 1 gigabit/sec connections for $30/month compared to $300 in the U.S. for half the speed.

For those that argue there isn’t a competition problem with U.S. broadband, compare the speeds and costs of broadband in cities with municipal ISP services. ISP lobbyists have trolled states, passing laws to ban municipal broadband, claiming that this increase in competition is bad for the cities because…reasons. In Chattanooga,Tenn., the municipal broadband cut user costs from $300/month to $70 and is well on track to pay back its bond financing. Cities are lobbying the FCC to also void the ban on municipal broadband in 20 states, which would allow cities to do what companies won’t – invest in better service. House Republicans are already proposing laws to block the FCC from voiding these anti-competitive laws.

Net neutrality is only needed because we lack broadband competition. In an ideal market, if one company was blocking Netflix, you could switch to another for better service. But instead, we have no choice and face an industry that prefers lobbying to investing.


Digital account inheritance law passes in Delaware

Ownership of digital assets is an ongoing legal debate. A new law in Delaware allows individuals to bequeath their digital accounts, email, Twitter, World of Warcraft, to an heir or estate. This is important evolution to treating digital accounts the same way physical assets or documents might, though the influence of this law may not be as vast as intended or needed. For example, in states without such protections, individuals could include their user name and passwords in their will. Though this may have some security issues (can you trust your lawyer), it is a simple way to ensure account information isn’t lost forever. Some terms of services, like Facebook, forbid this type of transfer, though how will they know.

Yahoo received significant publicity for refusing a family access to their son’s email account after he was killed in action in Iraq. This law may help in situations like this if the will is executed in Delaware. Whether this law is needed nationally remains uncertain. I would prefer data portability and privacy laws be emphasized over the right to bequeath an email account. A data portability law, giving the user some or complete ownership of their data, like email or web history, would allow users to download, backup, and move information between services. This then turns digital accounts into more specific files and data what are already owned by the individual. The information in these accounts is important while the user is still alive and that should be the priority for new legislation.


Uber bringing gender diversity to taxi drivers through safety

When I described Uber to my mother, she immediately ruled it out, saying she didn’t trust the background checks of the drivers. Both Uber and Lyft do full background checks on their drivers, at least as much as traditional taxi companies, while only Uber and Lyft give you the driver’s name, phone number, picture, and GPS tracking. This can make using an Uber arguably safer than a regular taxi.

Uber’s policies also appear to significantly improve the lifestyle for the drivers, making the profession safer and thus opening it up to more women.  The Occupational Safety and Health Administration reported that cabbies were 60 times more likely to be killed on the job than other workers. Drivers often work alone, carry cash, and and travel through high-crime areas. Only about 2 percent of all U.S. taxi drivers are female.

Driving an UberX car, which is open to anyone who signs up and passed the background check, has turned out to be a much safer operation. Just like riders, drivers can see the name and rating of a potential pick-up. The rider already has a credit card on file, meaning the driver never needs to carry or handle cash. And the car is tracked completely through GPS. While this doesn’t make crime again driver or rider impossible, it significantly lowers the risk. Though Uber won’t release gender breakdowns for its drivers, casual estimates place the number around 15%.

What stands out about this discussion is how Uber has been incentive to create a superior product to traditional taxi cabs without the strong regulatory regime. The initial taxi monopoly regimes were created to encourage safety, reliability, and maintain certain standards. However, this has led to a large undeserved market, mostly from a lack of available drivers or rigid pricing regardless of demand.  Uber, incentivized to create a superior product to taxis, has put technology to innovative use, making taxi transportation far more efficient for the drivers and riders. This is why bans on Uber, as Berlin recently did, have more to do with protecting entrenched businesses or simply ignorance of how the service works.  Uber is likely also the reason demand for taxi medallions, once one of the best investments available, is now dropping.

For regulations, it’s important to recognize what the business already incentivizes. Companies can only cut costs to the point employees and customers are willing to partake in the transaction. If driving or riding an Uber became unsafe, both drivers and riders would exit to competitors. And since there is no longer an artificial limit on supply, competitors like Lyft can more easier enter the market, forcing all entrants to engage in higher levels of service. Legislation should look at what the base line of service their car sharing services offer and determine that to be what is needed for consumers to use the product and for the businesses to be able to make a profit.

 

 


Supreme Court outlaws Aereo because reasons

Furthering the perception that the Supreme Court and those in power are out of touch with the realities of technology and innovation, the Supreme Court issued a 6-3 ruling against the streaming TV service Aereo for violating the copyrights of broadcasters. This ruling rests on the mistaken interpretation that Aereo functions like a cable company, and thus must be a cable company when it comes to copyright law.

TV companies including CBS, NBC, and ABC took issue with Aereo charging customers $8-$12 to stream broadcast channels to their mobile devices without paying the channels re-transmission fees like cable companies do. Aereo established a very convoluted system to route around the understood copyright law. Aereo provided a micro-antenna for every individual user which was used to capture over-the-air video for streaming. This antenna functioned the same way rabbit ear antenna would work on your home television set, only this antenna was found miles away. The only channels available were ones already using the public airwaves, which are freely available without charge for anyone using their own antenna. The crux of Aereo’s business model stemmed from the Cablevision ruling that permitted Cablevision to offer a remote DVR service which functioned exactly like a personal DVR, except the hard drive was located outside your home.

For technologists, Aereo basically provided the same product of television, only with a really long cable. Because current copyright is so complicated and convoluted, their system for offering this service seemed complicated and convoluted. The Supreme Court seemed to take issue at how much Aereo attempted to cirvumvent copyright law with its technology, ignoring that avoiding breaking the law does make you guilty of breaking that law. The court says Aereo possesses an “overwhelming likeness” to cable companies, establishing the “Looks like a Duck” legal test for how to treat new technology.

The fallout of this ruling will be a stifling of innovation. TV companies have not been eager to innovate, either through new business models or new technology that makes the experience better (such as suing over the aforementioned DVR in its many iterations). Because the court’s ruling ignores what the technology does instead judging it for what they understand presents a cloud of uncertainty around new technology and startups. The ruling itself seems to desperately try to say it will not apply to other cloud technologies, but without providing a useful legal test for the rapidly growing sector to apply when building new businesses.

Aereo has suspended its service, already bringing an exciting new business to a close.


Net neutrality explained: Prevent cable company ****

On this past weekend’s Last Week Tonight, John Oliver awakened his viewers to the pressing issue of net neutrality. He highlighted what might be the biggest reasons cable companies are winning this fight: net neutrality just sounds boring. Instead of calling it net neutrality, Oliver suggests we “prevent cable company f**kery”.

Oliver is right on point. Net neutrality is incredibly important and affects every internet user.


Crowdfunding’s Potential as a Market Tester

Written for Clareo Partners

When launching new products, limiting risk can boost the chance of success. Crowdfunding on websites like Kickstarter and Indiegogo has exploded as a funding source for everything from inventive, niche products to million dollar celebrity projects. The industry has grown to an estimated $5 billion with the leader, Kickstarter, funding $480 million over 19,000 projects in 2013.

For new ventures and new products, crowdfunding is almost risk-free. Launching a project on Kickstarter costs nothing upfront. If the project meets its budgeted goal, then Kickstarter charges a 5% fee plus payment processing. If the project does not meet its goal, neither Kickstarter nor the project creator takes any money. Crowdfunding presents an unmatched research resource for testing the market appeal for a new product.

kickstartergrowth

Risky new products have found great success on Kickstarter, revealing a vibrant proving ground with less risk and more reward than traditional funding methods. Alternative video game consoles Ouya raised $8.6 million on Kickstarter and proceeded to close a $15 million venture capital round, leveraging an already committed customer base. Virtual reality headset Oculus Rift raised $75 million in venture capital after its Kickstarter raised $2.4 million. Technology projects command 11% of Kickstarter’s succeeding dollars while only 2.7% of projects. Kickstarter provides an excellent forum to prove the market potential for these products, allowing them to acquire paying customers before expending all their resources on development.

kickstartersucess

Project success can be predicted early on. Virality at the beginning of a campaign pays off as success snowballs. This momentum explains why successful projects on average raised 160% of their target goal. The average successful project reaches its goal before the halfway point. Measuring 10,000 projects conducted during 2012 reveals just how fast momentum for a successful project can set in. Four days into a 30-day project, 50% of successful projects have raised 28% of their goal while unsuccessful projects have only raised 1% of their goal. Raising a quarter of your goal by day four leads to an 88.7% chance to succeed. Projects can provide immediate feedback, well before the average 30-day project duration. This data can further inform how interested the potential market will be to the proposed product.

As a proving ground for innovation, crowdfunding is the new test market. Real customers pledge to make the dream a reality. This presents an opportunity to experiment with new product launches and engage with customers to help decide commercial viability

 


Competing with piracy: Streaming services can be even better

Slowly media companies are migrating to more digital savvy strategies, however much kicking and screaming may be occurring. Most movies and tv shows are available for download and music streaming is becoming the new standard for music consumption. Models are moving to subscription from a la carte, and this is better for consumers.

However, media and technology companies still forget who their top competitor is: piracy. Online piracy remains cheaper and more flexible than any official option. Technology companies eager to convert pirates to paying customers need to recognize the features pirates value and find ways to offer alternatives.

For streaming services, they should want to remove any reason to leave their system. Netflix encourages keeping track of videos to watch and remembers where you left off, even if you stop mid-show. In music streaming, Spotify and Rdio have excellent selection, fair prices, and flexible options for mobile and off-line usage.  They solve many of the issues a streaming service may present. But for some power music users, features available in iTunes and other music management software are not replicated within streaming services.  Personally, I still prefer MP3s so I can track my play counts and ratings for songs I like. This way, I can always find or re-find music I like, which can be challenging in a large collection. Additionally, I’m picky with how my music gets tagged, again to help with my discovery of old music in my collection. Music streaming, while excellent for discovering new music, fails to make it easy to keep track and rediscover that same music.

Research shows pirates spend more on media content, like music, and may already subscribe to streaming services to help with discovery. But to fully capture these users and deter them from pirating content, ensuring streaming services replicate the non-streaming experience can be vital to making streaming as ubiquitous as phone service.


Open-source community threatened: JDownloader declared illegal because of third-party add-on

A German court recently declared JDownloader, an open source download program, illegal for copyright infringement because of an add-on tool used to save encrypted video streams. The ruling, which holds JDownloader responsible for the optional contributions of a third-party, a feature that had been removed by the time of the ruling. 

For open source development, this is a disturbing ruling.  Already in the United States, third-party liability create risk for software and service providers worried about how consumers will use their products. This legal concept of contributory infringement was used to ban file-sharing services like Napster and is used to block websites and software that are used to share copyrighted content. However, rarely are the providers of the software themselves infringing on copyright, simply the software can be used for that purpose.

The German court ruling takes this concept even further by declaring an entire software illegal because of code written by a third-party.  JDownloader already can be used for downloading copyrighted content – it downloads links from file lockers like Rapidshare. The issue in this case was over an add-on that downloaded encrypted video streams, which meant circumventing DRM.  Even though JDownloader removed this add-on, it was still held responsible.

Consider how this might apply to other software. There are similar add-ons for Google Chrome and Firefox.  Many third-party tools allow for downloading YouTube videos or copying music off Spotify. It’d be hard to imagine a U.S. court banning these products (though YouTube is fighting a billion dollar lawsuit).

This is why the German court ruling should still make Americans nervous. The internet doesn’t differentiate between court jurisdictions. A website made available to one country will likely be accessible from any other. The fear is that countries may race to the bottom with restrictive court rulings banning important technological innovations out of fear of how they might be misused. Germany’s ban of JDownloader is unlikely to lead ot its removal from the internet, but it could impose a chilling effect on future development, both for contributors to JDownloader, and new software developers deciding whether they should allow third-party contributions.  Having open and vibrant communities has helped make useful products even better.  These communities should be encouraged and incentivized, not stifled and scared away. 


Pages:1234567...77