Revolutionizing Business Intelligence with Appsmith and Neon

Goal

Prerequisites

Overview

1. Create the PostgreSQL Neon Database

   To build this intelligence app that displays audience intelligence data from a TV network channel, we'll need a database structure that can store and organize the necessary information. Below is a set of tables and their relationships to support this use case that I easily created in Neon using the SQL Editor:

   1. Channel Table:
      • Fields:
        • channel_id (Primary Key)
        • channel_name
        • channel_description
        • channel_logo_url
        • launch_date
   2. TV Show Table:
      • Fields:
        • show_id (Primary Key)
        • show_name
        • show_description
        • show_genre
        • show_duration (in minutes)
        • channel_id (Foreign Key referencing Channel Table)
   3. Audience Table:
      • Fields:
        • audience_id (Primary Key)
        • audience_name
        • age
        • gender
        • location
        • interests
   4. Viewing History Table:
      • Fields:
        • viewing_id (Primary Key)
        • audience_id (Foreign Key referencing Audience Table)
        • show_id (Foreign Key referencing TV Show Table)
        • watch_time (timestamp)
        • channel_change_count
   

   With this database structure, we can capture data about TV channels, TV shows, the audience, and their viewing history. If you want to create dummy data for testing, you can also run the following SQL queries to insert some data: 

   INSERT INTO Channel (channel_name, channel_description, channel_logo_url, launch_date)
   VALUES
       ('Channel A', 'Entertainment Channel', 'logo_url_a.png', '2023-01-01'),
       ('Channel B', 'News Channel', 'logo_url_b.png', '2023-02-15'),
       ('Channel C', 'Sports Channel', 'logo_url_c.png', '2023-03-20'),
       ('Channel D', 'Cooking Channel', 'logo_url_d.png', '2023-04-10'),
       ('Channel E', 'Kids Channel', 'logo_url_e.png', '2023-05-05');

   INSERT INTO TV_Show (show_name, show_description, show_genre, show_duration, channel_id)
   VALUES
     ('How I meet your DevRel', 'Comedy Show', 'Comedy', 30, 1),
     ('DevRel vs Marketing', 'Drama Show', 'Drama', 45, 1),
     ('The Murder of a DevRel', 'Police Show', 'Reality', 60, 1),
     ('The DevRel News', 'Morning News', 'News', 60, 2),
     ('Coffee with the DevRels', 'Evening News', 'News', 60, 2),
     ('Night Advocates', 'Late News', 'News', 60, 2),
     ('UEFA DevRel League', 'Soccer Sports', 'Sports', 60, 3),
     ('DevRel Woods', 'Golf Sports', 'Sports', 60, 3),
     ('The DevRel Bulls', 'Basketball Sports', 'Sports', 60, 3),
     ('Advocate Compadre', 'Mexican Food', 'Cooking', 45, 4),
     ('DevRel Masala', 'Indian Flavours', 'Cooking', 30, 4),
     ('Advocate-San Mazu', 'Japanese Food Art', 'Cooking', 60, 4),
     ('DevRel for Kids', 'Blue Steps', 'Kids', 45, 5),
     ('Playing with Marketing', 'The Alphabet', 'Kids', 30, 5),
     ('Paint the Avocado', 'Painting', 'Kids', 60, 5);

   INSERT INTO Audience (audience_name, age, gender, location, interests)
   SELECT
       'Audience ' || generate_series(1, 50),
       floor(random() * 70) + 18,
       CASE WHEN random() < 0.5 THEN 'Male' ELSE 'Female' END,
       CASE floor(random() * 50)
           WHEN 0 THEN 'Alabama'
           WHEN 1 THEN 'Alaska'
           WHEN 2 THEN 'Arizona'
           WHEN 3 THEN 'Arkansas'
           WHEN 4 THEN 'California'
           WHEN 5 THEN 'Colorado'
           WHEN 6 THEN 'Connecticut'
           WHEN 7 THEN 'Delaware'
           WHEN 8 THEN 'Florida'
           WHEN 9 THEN 'Georgia'
           WHEN 10 THEN 'Hawaii'
           WHEN 11 THEN 'Idaho'
           WHEN 12 THEN 'Illinois'
           WHEN 13 THEN 'Indiana'
           WHEN 14 THEN 'Iowa'
           WHEN 15 THEN 'Kansas'
           WHEN 16 THEN 'Kentucky'
           WHEN 17 THEN 'Louisiana'
           WHEN 18 THEN 'Maine'
           WHEN 19 THEN 'Maryland'
           WHEN 20 THEN 'Massachusetts'
           WHEN 21 THEN 'Michigan'
           WHEN 22 THEN 'Minnesota'
           WHEN 23 THEN 'Mississippi'
           WHEN 24 THEN 'Missouri'
           WHEN 25 THEN 'Montana'
           WHEN 26 THEN 'Nebraska'
           WHEN 27 THEN 'Nevada'
           WHEN 28 THEN 'New Hampshire'
           WHEN 29 THEN 'New Jersey'
           WHEN 30 THEN 'New Mexico'
           WHEN 31 THEN 'New York'
           WHEN 32 THEN 'North Carolina'
           WHEN 33 THEN 'North Dakota'
           WHEN 34 THEN 'Ohio'
           WHEN 35 THEN 'Oklahoma'
           WHEN 36 THEN 'Oregon'
           WHEN 37 THEN 'Pennsylvania'
           WHEN 38 THEN 'Rhode Island'
           WHEN 39 THEN 'South Carolina'
           WHEN 40 THEN 'South Dakota'
           WHEN 41 THEN 'Tennessee'
           WHEN 42 THEN 'Texas'
           WHEN 43 THEN 'Utah'
           WHEN 44 THEN 'Vermont'
           WHEN 45 THEN 'Virginia'
           WHEN 46 THEN 'Washington'
           WHEN 47 THEN 'West Virginia'
           WHEN 48 THEN 'Wisconsin'
           ELSE 'Wyoming'
       END,
       'Interests ' || floor(random() * 5) + 1
   FROM generate_series(1, 50);

   INSERT INTO Viewing_History (audience_id, show_id, watch_time, channel_change_count)
   SELECT
       floor(random() * 50) + 1, 
       1,
       NOW() - (random() * interval '365 days'), 
       floor(random() * 5)
   FROM generate_series(1, 200);

   With these queries, you will have sample test data to retrieve in Appsmith. 

2. Create the Appsmith App

   To create intelligence reports for your demo application using Appsmith, you can rely on Appsmith's intuitive and powerful Query editor to perform queries and aggregations on this data to extract meaningful insights and then passing it to UI Widgets very easily. If you don't know anything about SQL that's fine! Let's leverage Appsmith's contextual AI prompts to ask what we need.  

   Go to Appsmith and create a new app, then grab the credentials from the Neon database dashboard and create a new Datasource in your Appsmith app: 

   

    

   Then, enter the credentials from the Neon dashboard in the Connection fields: 

   

    

   Hit save and now you are ready to start creating queries! Let's create a new query and ask AI for help building our Appsmith app, we want to showcase a chart of our audience by gender, so let's click on "Use AI" and ask for Gender vs count of audience with that gender, Female or Male and now Appsmith will analize our database and generate a valuable and useful working SQL query which will retrieve a count of our audience by audience. 

   

    

   Then, you can click the "Chart" binding on the right to bind the result of this query to the UI. 

   

   And just like that, you now have a deployable app showcasing a dashboard chart with our Audience. Now, you can continue to do the same by asking AI the following prompts: 

   1. Get from the audience table age vs count of audience with that age, but group the ages by 10 year ranges, like 30-40, 40-50 

   2. From the viewing history table, using the channel_change_count give me the amount of channel changes per show and pull the show name from the tv_shows table

   With these 2 queries, Appsmith will get the data and also bind it to Charts, and we will be able to easily showcase more valuable key-decision data in our dashboard from the Neon app. 

   

   Here are some other example queries you can easily build in your app: 

   1. Audience Demographics:
      • Calculate the number of male and female viewers in different age groups.
      • Analyze the locations and interests of the audience.
   2. Popular Shows:
      • Identify the most-watched TV shows.
      • Determine the average watch time for each show.
   3. Channel Performance:
      • Measure the number of viewers for each channel.
      • Calculate the channel switch rate (channel change count).
   4. Audience Engagement:
      • Find out the TV show that had the longest watch time per viewer.
      • Identify the most popular TV show genres among the audience.
   5. Cross-Channel Behavior:
      • Discover which channels are often viewed together and analyze the audience's cross-channel behavior.

3. Much easier, use the template

   In case you do not want to build your app step by step, you can use the forkable app we have prepared in our Templates catalog, you can view/try/fork the app in the following link: https://community.appsmith.com/template/business-intelligence-template-neon-postgresql 

Conclusion

Additional Resources