DVD Rental Business Report

Summarize one real-world business report that can be created from the attached Data Sets and Associated Dictionaries.

By using the provided data sets and dictionaries, it is possible to create a business report focused on returning customers. This report could help the business understand which customers are returning and how often they come back. This information could be used to implement a program that rewards returning customers for their loyalty and encourages other customers to rent more frequently. The goal of this program would be to increase rental sales and potentially attract new customers who are interested in participating in the program. Overall, retaining existing customers is typically more cost-effective and easier than acquiring new ones, so this kind of analysis and program implementation could be beneficial for the business.

Describe the data used for the report.

This report will be based on customer and rental data. The customer information would be used to identify returning customers and provide details about their activity. The fields that need to be used are Customer ID (int), First Name (char), Last Name (char), Email (char), Rental ID (int), Rental date (timestamp), Return date (timestamp), Staff ID (int). These fields will populate the detailed table with the rental history for every customer. Then the summary table will sort which customers rent most frequently.

The rental data would be used to determine when these customers are renting DVDs and how frequently they are returning. The stakeholders could use this information to make decisions about which customers to focus on, such as those who return frequently and recently, and those who have not returned in some time. This data could help the stakeholders understand the behavior of returning customers and identify opportunities for targeting specific customer segments with marketing or other initiatives.

Identify two or more specific tables from the given dataset that will provide the data necessary for the detailed and summary sections of the report.

The rental and customer tables from the provided dataset would be the primary sources of information for creating both the detailed and summary sections of the report on returning customers. These tables would contain all the necessary data to identify which customers are returning to the store for DVD rentals and how often they are doing so. By combining data from these two tables, it would be possible to analyze customer behavior and implement a returning customer program that rewards loyal customers and encourages others to rent more frequently. The report could also include other relevant data, such as information about the types of movies being rented, the location of the store, and any promotions or discounts that may be offered to customers.

Identify the specific fields that will be included in the detailed and summary sections of the report.

For the detailed section of the report the following specific fields would need to be included:
– Customer ID
– First Name
– Last Name
– Email
– Rental ID
– Rental date
– Return date
– Staff ID

For the summary section of the report the following specific fields would need to be included: – Customer Full Name
– Email
– Customer count

Identify one field that will require a custom transformation and explain why it should be transformed. For example, you might translate a field with a value of ‘N’ to ‘No’ and ‘Y’ to ‘Yes’.

The ‘first_name’ and ‘last_name’ fields need to be transformed for better readability. Currently, these names are split into separate columns, but we can concatenate them together in the format ‘last, first’ so that stakeholders can more easily see the full name of the customer.

Explain the different business uses of the detailed and summary sections of the report.

The Detail contains all transactions related to rental sales within the company. It is useful for stakeholders to see which customers return the most and when they return. On the other hand, the Summary section is a concise overview of the customer’s identity and their support for the business. It is a good resource to use during a quick update meeting to make decisions about returning customers

Explain how frequently your report should be refreshed to remain relevant to stakeholders.

This report needs to be updated monthly to ensure that new sales made within the month are accounted for and that rewards are being properly allocated to customers. If the business plans to reward the top 50 returning customers, the report is to be refreshed before the coupons are distributed.

Explain how the stored procedure can be run on a schedule to ensure data freshness.

This stored procedure updates the data in both the detailed and summary tables and has the option to clear the contents of these tables before performing the ETL (Extract, Transform, Load) process. The stored procedure should be run at least monthly to ensure that the information is up to date. If the report is not re-run, the old data will remain, and the business will not have an accurate understanding of current rental activity. To ensure that the information in both the detailed and summary tables is fresh, the procedure to update the data should be executed before business meetings. This process is automated every time data is inserted into the detailed table. The trigger summary_refresh shown below runs the summary_refresh_function to ensure that data in the summary and detailed tables are always up to date. To automate this process using a scheduler, the business database admin can use pgAdmin 4 to create a pgAgent job. The pgAgent job creates a step to call a stored procedure. The business database admin can set what days and times the stored procedure should be run in the pgAgent job wizard. For example, in the pgAgent job wizard the admin can set the code to CALL refresh_tables(); then set the date to the first of every month and the time to 5 am.

SQL