Saturday, November 30, 2013

Grow Local Businesses Around You

I have always been fascinated by the term "Local is the new Global" and thought if there was a way where the small and medium sized businesses could engage and build a great relationship with the consumers whom they interact on a frequent basis.  

Market to me better:
I spend maximum time of my life at home and at the office. It would be great if I could get offers from the shops in the local area based on my preferences and the messages I would like to see from the merchants. I don't have time to spend, going through the coupons that I need to pick at the shop entrance. I would love to see offer email messages from the coffee, grocery, and beauty shops around me as those are places I visit the most frequently. 

Discover and Encourage Hidden talent in my local area:
Its amazing how you discover talents in your local areas like a solopreneurs in your around you makes awesome cup cakes or you have been trying to find a good Yoga trainer and your friend tells you the person was only like a couple blocks from where you live. There are products out there letting me find them, I don't get suggestions. I would love to encourage them and at the same time get I need served. 

How can I better engage with my merchants:
Lets say I keep buying candy and nuts from the a small shop near my office. It would be wonderful if that shop takes a small 30 second survey from me about my likes and dislikes at the shop and then reach out to me appropriately. It just encourages me to keep going to that shop as I feel take care off then the shop owner will start offering me the right things and receive right offers.  

Win win situation for all:
By getting more personalized service from the merchants, I am obviously gonna get better service from the provider and the same time I could have other advantages like saving time, less clutter and I could go on and on. 
Obviously the merchants generates more revenue from their existing customers as the customers are going to automatically spread the word for through their social graph, word of mouth etc.

Using email marketing:

Earlier this year(2013) when I was planning to start a new venture in this space. I met my co-founders who were planning an email marketing tool called Optyn specifically catering to the needs of small businesses. 

Its is so simple to use one could create a marketing messages in 2 minutes or less. No getting lost in templates. Write the content that matters the most.



Tuesday, September 18, 2012

Case Statements in SQL

One of the complains that businesses have is that reporting is slow. There are multiple ways that I have seen developers handling this using caching putting in pagination in place etc. My thought process for reporting queries why  not write good SQL queries and fetch data efficiently rather than putting in hacks at the application level. Now, there are few things that you will need to take care of at the database level like putting indexes on the right columns using unions over joins or vice a versa. I am not going to get into all of that here. I will be talking about one of the biggest boons that the SQL programming language offers the "CASE" statement.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END
Or
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END

Lets begin with a small example where it could be used as a "CASE"  programming language construct. Lets say you are building a report for a teacher to display count of students in various Grades. Now instead of writing the logic at the application level to iterate over the student records and create a hash of grades and the count of students for each key. A more efficient way would be doing it through a SQL query and a "CASE" statement.

SELECT DISTINCT(s.grades),
CASE s.grades
WHEN 'A' THEN COUNT(s.grades)
WHEN 'B' THEN COUNT(s.grades)
WHEN 'C' THEN Count(s.grades)
ELSE Count(0)
END AS number_of_students
FROM students s
GROUP BY s.grades

If calculations are involved or you want to use the sql case statement as if-else if-else we can also do that. Lets take an example where you want to calculate grades for students based on their marks:
SELECT DISTINCT(s.grades),
CASE
WHEN s.grades BETWEEN 90 AND 100 THEN SUM(1)
WHEN s.grades BETWEEN 80 AND 90 THEN SUM(1)
WHEN s.grades BETWEEN 60 AND 80 THEN SUM(1)
ELSE Count(0) END AS number_of_students
FROM students s
GROUP BY s.grades;

A CASE statement in SQL is just an expression that needs to return a value and can be used in group by and order by clauses of a query as well for example: 
ORDER BY events.date, (CASE(events.display_on_homepage) WHEN true THEN 0 when false then 1 ELSE 2 END) ASC

Again using CASE statements will not make the the performance better but I would take the business logic closer to the database. It could contested that case statements are the best way of doing things. Our Ruby on Rails development team at Idyllic Software believe it is. Feel free to pass on you comments/suggestions etc. I have used case statements to a good extent in Mysql and Postgres. I hope it works in other databases.