Credit Card Data

Importing Credit Card and Pre-paid Card Data

Combining Credit Card or Pre-paid Card data with the Takeout's Location History can provide more information for spatial analytics. And the process of linking both data sets in either SQLite or PostgreSQL databases is quite straight forward when using SQL.

Getting Card Payment history data

Getting Credit Card data varies from one card company to another, but some companies or banks do offer either a PDF or CSV download of a client's payment history. The screenshot below is that of a Visa card from Mitsui-Sumitomo card company.

Getting Suica Card Payment history data

For Pre-paid cards such as Suica, there are mobile apps that are able to extract the payment history of a physical Suica card and create a CSV file that contains the data. For an Apple IOS device, this app can be used. And for an Android device, this app can be used. The process of extracting the Suica payment history and creating the CSV file is also quite straightforward, and both apps give detailed instructions on how it is down.

Importing the CSV data into PostgreSQL

Once the payment history has been downloaded in a CSV file, the data can be imported into PostgreSQL database using the \copy command after a table has been created to contain the CSV data.

Below is a SQL example on how to create a table to contain the data and import the Suica payment history CSV file created by the IOS app into a PostgreSQL database.

--
-- Dropping table if exists
--
DROP TABLE IF EXISTS suica;

--
-- Creating a Suica table to contain the CSV data
--
CREATE TABLE suica (
 連番 text,
 日付 timestamp,
 出金 numeric,
 入金 numeric,
 残高 numeric,
 乗車駅名 text,
 乗車駅の路線名 text,
 乗車駅の社名 text,
 降車駅名 text,
 降車駅の路線名 text,
 降車駅の社名 text,
 処理種別 text,
 メモ text,
 カード識別子 text
);

--
-- Importing the CSV from the file Suica.csv into the created table
--
\copy suica from Suica.csv HEADER DELIMITER ',' CSV;

Now once it is in the table format, the Payment history can be linked with the Location History via timestamp for example, and be visualized in QGIS where various thematic maps can be created with the linked data to provide analytics.